Home Business Intelligence The Greatest SQL Is the One You Do Not Have To Write

The Greatest SQL Is the One You Do Not Have To Write

The Greatest SQL Is the One You Do Not Have To Write


Are You Uninterested in Writing SQL?

SQL has been the lingua franca for information manipulation for over 40 years now. As we speak SQL is an establishment, one that only a few folks can think about may exchange. There isn’t a denying although that its limitations are beginning to present. Each skilled information engineer has reached a degree of desperation when having to jot down the identical SQL boilerplate code repeatedly, particularly within the space of analytics with dynamic enterprise necessities.

Let me provide you with a couple of examples of what I imply:

Count of flights that happened in the United States.
Rely of flights that occurred in the USA.
Count of flights by carrier OR by aircraft model manufacturer. The same metric (count of flights) in different contexts (Carrier / Aircraft model manufacturer) requires completely different queries.
Rely of flights by provider OR by plane mannequin producer. The identical metric (depend of flights) in several contexts (Provider / Plane mannequin producer) requires utterly totally different queries.
Percentage of flights for each carrier from overall flights. Windowing function approach on the left, JOIN of subselects approach on the right. These simple questions result in queries that are complicated, hard to write, and even harder to maintain.
Share of flights for every provider from general flights. Windowing perform strategy on the left, JOIN of subselects strategy on the fitting. These easy questions end in queries which can be sophisticated, onerous to jot down, and even more durable to take care of.

Easy SQL queries quickly develop in complexity when real-world enterprise necessities are utilized to them.

Can We Resolve It Extra Simply Utilizing One other Language?

Sure, with next-generation analytical languages we are able to! Take a look on the following instance, the place we condense a sophisticated SQL question into three strains of code.

Very simple metric definition in the left top corner, context (View by) is defined separately, the corresponding SQL is on the right side and the query result preview is in the bottom left corner.
Quite simple metric definition within the left high nook, context (View by) is outlined individually, the corresponding SQL is on the fitting facet and the question consequence preview is within the backside left nook.

However how is one thing like this potential? How does the engine know the way to join all entities collectively and generate the SQL?

The reply lies within the semantic mannequin.

Dataset relationships (arrows) enable platforms to generate SQL (JOINs).
Dataset relationships (arrows) allow platforms to generate SQL (JOINs).


In comparison with SQL, new analytical languages are:

  • A lot less complicated, extra readable, and simpler to take care of
  • Able to using metrics which can be reusable in a number of contexts (View/Slice by, filters, and so on.)
  • Able to fixing all vital analytics use circumstances

The disadvantage is that it’s important to make investments your time into studying a brand new language. Is the funding value it?

Let Me Consider Such Languages for You

I’ll look at two of probably the most superior languages:

Particularly, I’ll information you thru the next three phases:

You possibly can then strive it for your self, the supply code will be discovered right here.

Instance Mannequin

Earlier than we begin with the aforementioned three phases, let me briefly present you what the underlying database mannequin we’re going to make the most of seems like:

Federal Aviation Administration Model
Federal Aviation Administration Mannequin

Information collected from the Federal Aviation Administration.

Single “flights” reality desk will be damaged down by a number of dimensions — carriers, airports and aircrafts of assorted fashions.

Airports are enjoying two roles — origins and locations.

Credit score goes to builders from Malloy, who already ready corresponding information information, fashions, and analytics use circumstances. Thanks!

The Three Phases


We have already got the bodily information mannequin, so why create yet one more mannequin (a logical information mannequin on high of the bodily information mannequin)?

There are three causes:

  1. Allow extra customers to research information
    Create the mannequin as soon as, and reap the advantages without end.
    It’s a lot simpler to construct metrics and studies as soon as the information has been modeled correctly, and even enterprise customers can work with logical entities.
  2. Retailer extra semantic metadata
    Semantic properties like distinguishing info/attributes/date dimensions.
    Metadata will be utilized by numerous exterior methods striving for semantic data, for instance in pure language processing (NLP). My colleague Jan Kadlec lately wrote a associated article on NLP, I encourage you to learn it!
  3. Decouple analytics from bodily fashions
    Refactor the bodily mannequin with out refactoring all analytics objects (metrics, visualizations, dashboards, …)

What does the logical information mannequin appear like?

Malloy source (a logical dataset) on top of the flights table. Only primary key and references to other sources. Only two fields are renamed to simplify their usage.
Malloy supply (a logical dataset) on high of the flights desk. Solely major key and references to different sources. Solely two fields are renamed to simplify their utilization.
GoodData web UI for model visualization and editing.
GoodData net UI for mannequin visualization and modifying.
Declarative definition of GoodData dataset on top of the flights table. Primary key (grain) and references to other datasets. All entities must be declared, and attributes and facts must be distinguished. Datasets are mapped to tables, and attributes/facts to columns.
Declarative definition of GoodData dataset on high of the flights desk. Major key (grain) and references to different datasets. All entities should be declared, and attributes and info should be distinguished. Datasets are mapped to tables, and attributes/info to columns.

Modeling Comparability

I’ve put collectively a high-level overview of how logical information fashions are dealt with in GoodData and Malloy:

GoodData and Malloy Comparison
GoodData and Malloy Comparability

(1) Developer expertise — Is it handy for builders to handle the mannequin?

Malloy gives an excellent developer expertise in IDE (VS Code with a Malloy plugin). Builders can write the mannequin (and metrics, and studies, to be lined by the next chapters) in a single file and validate the answer (preview datasets/sources, execute studies). Furthermore, the IDE plugin gives the corresponding IntelliSense — it reads bodily information mannequin entities and suggests them in the fitting locations (for instance it affords corresponding column names when attempting to rename an entity to a greater enterprise identify).

GoodData is extra oriented in direction of UI expertise and enterprise customers, nevertheless it additionally permits builders to retailer declarative definitions of fashions (and metrics, and studies) into YAML information, manipulate them and ship them into any setting (CI/CD).

Moreover, GoodData gives two use circumstances — scanning the bodily information mannequin and producing a logical information mannequin from the bodily information mannequin. It applies numerous guidelines to detect dataset (desk) relationships, distinguish info/attributes, and so on. It’s not good however it may well considerably velocity up onboarding.

(2) Final-mile ETL — i.e. transformations wanted to organize the bodily mannequin to be appropriate with analytics use circumstances.

Some platforms require very advanced transformations, for instance, to denormalize all the pieces right into a single desk because of the practical and efficiency limitations of those platforms. Neither Malloy nor GoodData requires such transformations — normally, we advocate to implement such transformations solely on the dataset stage, for instance, to rework values 0 and 1 into Male and Feminine. Each platforms can work with Star/Snowflake schemas.

(3) Declare logical entities solely when wanted

You need to declare all entities and distinguish info from attributes in GoodData.

This requires a barely bigger preliminary time funding, nevertheless it improves the expertise for enterprise customers by permitting for a self-service drag&drop expertise within the UI. We plan to simplify this expertise even additional within the close to future.

(4) The idea of attribute labels in GoodData.

Attribute labels are a singular function available on the market. Customers can declare that an attribute has a number of labels. GROUP BY is all the time utilized to the column that’s mapped to the attribute, e.g. customer_id.

Customers can resolve to show a label by default, e.g. customer_name. Customers can outline different kinds of attributes offering particular capabilities, e.g. URL or geo-location.

We wish to talk about it with different distributors, together with Malloy, whether or not it’s a robust sufficient idea.

(5) Outer joins

Whereas Malloy does, GoodData doesn’t enable customers to declare a relationship between datasets as “outer”, as a result of we consider that customers usually use each inside and outer joins primarily based on a enterprise case applied by a metric or a report. That’s the reason we plan to quickly present an choice to specify “outer” in metrics and likewise in studies (“present empty values”).

(6) Periodical granularities

Malloy doesn’t assist periodical granularities like dayOfWeek, weekOfYear, …

(7) Superior modeling use circumstances

There are such a lot of and no analytics platform helps all of them. This can be a big alternative for dialogue!


  • Function-playing dimensions
    e.g. consumer -> creator/final updater
  • Combination consciousness
    single dataset mapped to a number of tables, e.g. aggregated by day, week, month, …
  • Additive/non-additive info
    impacts during which context info/metrics can be utilized


Why ought to customers spend money on writing metrics? As a result of they are often reused in a number of studies!

Additionally, metrics assist the one supply of fact paradigm — if you outline e.g. income metric as soon as and it’s reused throughout the group, it can’t occur that two enterprise departments deliver two totally different income values to the board assembly.

What do metrics appear like in Malloy and GoodData?

Malloy calls metrics ‘measures’. Both simple and more complex measures can be declared. Measures must be defined as a part of a source (dataset).
Malloy calls metrics ‘measures’. Each easy and extra advanced measures will be declared. Measures should be outlined as part of a supply (dataset).
GoodData Metric UI editor. The metric definition is in the left top corner. “View by” enables you to put metrics into context (hereby Carrier nickname). The result preview is in the bottom left corner. The corresponding SQL statement is in the panel on the right.
GoodData Metric UI editor. The metric definition is within the left high nook. “View by” allows you to put metrics into context (hereby Provider nickname). The consequence preview is within the backside left nook. The corresponding SQL assertion is within the panel on the fitting.
Declarative definition of GoodData metrics. Both simple and more complex metrics can be declared. Besides metric ID and metric source code, additional semantic properties can be defined (format, description).
Declarative definition of GoodData metrics. Each easy and extra advanced metrics will be declared. Apart from metric ID and metric supply code, extra semantic properties will be outlined (format, description).

Metrics Comparability

Here’s a high-level overview of how metrics/measures are dealt with in GoodData and Malloy:

Metrics Comparison
Metrics Comparability

(1) Superior analytics use circumstances

No matter you think about you’ll be able to obtain in SQL (analytics use circumstances solely!), it is best to have the ability to obtain with Malloy/GoodData.


  • Calculate contribution, e.g. metropolis to area
  • Interval-over-period comparisons
  • Filter by advanced metric

(2) IntelliSense

Each GoodData and Malloy present an excellent IntelliSense.

In GoodData, we adopted the idea of language servers, and now we put it to use in our net IDE. We’ve applied a PoC of VS Code plugin as properly and we all know it’s possible. We consider that that is one thing all languages/platforms ought to present.

Furthermore, GoodData gives two extra superior use circumstances:

  • labelElements
    suggests attribute(label) values
  • validObjects
    suggests attributes/info/metrics primarily based on the context already present in a metric.
    Primarily based on the mannequin, we all know which entities will be appended right into a context.

(3) Outer joins

GoodData goes to supply an express language syntax to allow outer joins. Malloy gives outer be a part of by default, builders need to make the most of filters (the place the joined entity is just not empty) to implement inside be a part of.

That is an attention-grabbing distinction — what ought to be the default? Let’s talk about it!

(4) Metrics reusable in a number of contexts

The idea of shared dimensions is just not supported by Malloy, which means that Malloy metrics can’t make the most of entities from 2 or extra reality tables related by shared dimensions. See the shared dimension use case instance under.

(5) Language “completeness”

It looks like it’s potential to jot down any quantity of complexity into GoodData metrics, see an instance under. The Malloy measure definition is extra strict. The query is that if the identical complexity will be achieved in Malloy by nesting metrics.

Metrics Reusability — Shared Dimension Use Case

Metrics Reusability — Shared Dimension Use Case
Metrics Reusability — Shared Dimension Use Case

In Malloy, if we mannequin duties, exercises, and coronary heart charges as separate sources, we can’t construct metrics using entities from all these sources.

For instance, attempt to correlate exercises and coronary heart charges by time or by the consumer.

We must create a supply on high of the consumer and be a part of exercises and coronary heart charges. We couldn’t do it in any respect with the time dimensions (it’s digital).

That is potential with none limits in GoodData and evidently it’s potential in PowerBI(DAX) as properly.

Advanced MAQL Metric Instance in GoodData

Complex MAQL Metric Example in GoodData
Advanced MAQL Metric Instance in GoodData

In GoodData MAQL language, you’ll be able to mix any language constructs collectively, nest by “sub-selects”, even in filters.

Malloy requires a fairly strict type of measure definition. You possibly can implement advanced expressions, and you’ll outline a filter as part of a measure, however you can’t nest measures in such an easy method as is feasible in GoodData MAQL, or at the very least that’s my impression.

Studies (Queries, Visualizations)

Lastly, we get to the actual enterprise worth — studies (queries in Malloy, visualizations in GoodData). Studies are metrics (constructed within the above chapter) in context — seen/sliced by attributes, filtered, sorted, and so on.

Right here is how studies appear like in Malloy and GoodData:

Query (report) in Malloy is in the bottom left corner, putting three metrics into the Carrier nickname context (viewing / slicing it by Carrier nickname). The result preview is in the right panel, where developers can display the corresponding SQL statement as well.
Question (report) in Malloy is within the backside left nook, placing three metrics into the Provider nickname context (viewing / slicing it by Provider nickname). The consequence preview is in the fitting panel, the place builders can show the corresponding SQL assertion as properly.
GoodData metric editor also provides a way to prototype reports. Developers can put metrics into context with View by and see the corresponding result preview and SQL statement.
GoodData metric editor additionally gives a method to prototype studies. Builders can put metrics into context with View by and see the corresponding consequence preview and SQL assertion.
GoodData Analytical Designer for reports (visualizations) building. Drag&drop experience for business end users (drag&drop from the left panel containing facts/attributes/metrics).
GoodData Analytical Designer for studies (visualizations) constructing. Drag&drop expertise for enterprise finish customers (drag&drop from the left panel containing info/attributes/metrics).
GoodData declarative definition of the same report (called insight in GoodData). It contains three metrics and the Carrier nickname attribute.
GoodData declarative definition of the identical report (known as perception in GoodData). It accommodates three metrics and the Provider nickname attribute.

Studies Comparability

And at last, here’s a high-level overview of how studies are dealt with in GoodData and Malloy:

Reports Comparison
Studies Comparability

(1) Pure report object

The GoodData report object (visualization) additionally accommodates presentation properties like colours. There isn’t a pure report object which could possibly be reusable.

(2) Reuse studies in one other studies

Malloy gives an idea of pipelined queries, every with a full feature-set (group by, filters, …). Very highly effective! GoodData doesn’t present something like this.

(3) Ordering and limiting

GoodData studies can’t be ordered by a number of entities (attributes/metrics).

Then again, GoodData gives features like RANK, which may present extra flexibility, even inside metrics.

(4) Report nesting

We’re not satisfied it’s a good design to nest studies in different studies. Nonetheless, it may well save quite a lot of pings from purchasers to the server.

Report Nesting

Malloy nesting. Interesting concept, but we are not sure if it should not be only a feature of the visualization layers.
Malloy nesting. Attention-grabbing idea, however we aren’t certain if it shouldn’t be solely a function of the visualization layers.
The same report in GoodData. The visualization layer could display the result in the same way as Malloy.
The identical report in GoodData. The visualization layer may show the end in the identical approach as Malloy.

GoodData Pivoting

Table with pivoted FAA region attribute. Sub-totals and grand-totals are calculated as well. It is even possible to sort by the pivoted attributes.
Desk with pivoted FAA area attribute. Sub-totals and grand-totals are calculated as properly. It’s even potential to type by the pivoted attributes.
The same pivot (by FAA region) is displayed as a nice TreeMap.
The identical pivot (by FAA area) is displayed as a pleasant TreeMap.


Comply with the Malloy/MAQL Method

Each languages deliver vital added worth to (not solely) builders. They’re comparatively to select up and begin utilizing and supply a really easy-to-use and maintainable strategy to information analytics going ahead. Each languages are developer pleasant, although we at GoodData are going to study from Malloy on this entrance 😉

There are extra options to SQL, like dbt metrics, Microsoft DAX, and so on. You might count on follow-up articles.

It is best to take into account studying these languages, it’s value it!

Present Shortcomings of the Malloy/MAQL Method

Usually, Malloy is just not but prepared for a self-service expertise. Possibly a call will likely be made to port Malloy into Looker and make this expertise obtainable.

Malloy, by design, doesn’t assist metrics on high of a number of reality datasets (with shared dimensions). I wish to talk about this design determination with folks from Malloy, as there’s quite a lot of potential for progress on this space! By the way in which, PowerBI helps it. 😉

In GoodData, it’s not potential to jot down the logical information mannequin, metrics, and studies in a unified and programmatic approach and check it immediately in an IDE. We wish to give attention to this within the close to future. As an example, due to the idea of a language server, we already applied a PoC of VS Code plugin for MAQL.

Concerning the analytics function set: each languages cowl much more superior analytics use circumstances (contribution, interval over interval, …). As distributors of such languages, we should always take heed to developer wants and add assist for lacking use circumstances. If that’s accomplished, builders shouldn’t have to context-switch between these languages and SQL too usually (or hopefully, ultimately, by no means).

Let’s Be a part of Forces

The semantics are related in all these languages. We, as language suppliers, ought to begin speaking collectively. We should always discover a stability between specializing in builders and on the tip enterprise customers. We should always allow producing one language from one other.

What about open-sourcing the language interpreters underneath Apache 2.0 license?

May we converge to a brand new normal? Let’s strive it!

Attempt GoodData Your self!

My aforementioned demo that this text was primarily based on.

GoodData Cloud trial — cloud SaaS model.

GoodData Neighborhood Version — run GoodData in your laptop computer.



Please enter your comment!
Please enter your name here