Looker Founder Helps Create New Data Exploration Language, Malloy

(EvalCo/Shutterstock)

Lloyd Tabb is keeping busy since Looker was sold to Google back in 2019. The latest open source project for the prolific computer scientist and his Google team is Malloy, a new language that’s based on SQL but which Tabb says is more approachable and simpler to use.

Malloy is described on its GitHub page as “an experimental language for describing data relationships and transformations. It is both a semantic modeling language and a querying language that runs queries against a relational database.”

One of the key features of Malloy, which was introduced in mid-2021, is its ability to work with nested data sets, particularly those supported by BigQuery. The language also generates SQL compatible with Postgres.

The development team behind Malloy says that by combining the modeling and query language, Malloy simplifies much of the data work that is currently done in SQL, making computations modular, composable, reusable, and extendable “in ways that are consistent with modern programming paradigms.”

“SQL is complete but ugly,” the Malloy backers write on the GitHub readme file. “Everything is expressible, but nothing is reusable; simple ideas are complex to express; the language is verbose and lacks smart defaults. Malloy is immediately understandable by SQL users, and far easier to use and learn.”

The same query written in the Malloy syntax (top) versus SQL syntax (bottom)

Malloy is designed to be a language for anyone who works with SQL, including data analysts, data scientist, data engineers, or anyone else building a data application, the project backers write.

“If you know SQL, Malloy will feel familiar, while more powerful and efficient,” they write. “Malloy allows you to model as you go, so there is no heavy up-front work before you can start answering complex questions, and you’re never held back or restricted by the model.”

The initial GitHub commit for Malloy was made back in August 2021 by Tabb and his team at Google Cloud. The team previously built a Visual Studio Code extension called Malloy Workspace that allows users to build models, queries, metrics, and dashboards in Malloy.

The Malloy Workspace demo shows how a user can build a data model on the fly as they’re exploring data and writing queries. The demo, which is based on a sample of airport data, shows how Malloy enables the user to create a new data model or shape an existing one as they’re defining data relationships, writing queries, and aggregating and joining data.

Nested data, in particular, is easier to work with in Malloy than in standard SQL tools, the project backers say. A user can create nested data sets, which Malloy will run together in a single query against the database. “The fan and chasm traps are solved, making it possible to aggregate anything in one query and reducing need for fact tables and overly complex SQL,” the project states in the Malloy readme.

Lloyd Tabb’s latest project is the new Malloy langauge

This week, Tabb, who lists his title as “stuff @ Google,” tweeted about the launch of Malloy Composer, which is new application built for Malloy. A demo is available that shows how Mallow Composer (which appears to have more of a Google UI look and feel) can be used to explore data and create queries with an array of dimensions, filters, and nested data.

In some ways, Malloy appears to have some similarity with LookML, the modeling language developed by Looker as a core component of the Looker platform. LookML enabled users to create data relationships outside of SQL, and re-use them in a way that was not supported directly by SQL. Customers who took the time to define their data models using LookML had several advantages, including greater consistency in their analytics and the ability to quickly generate new data products atop the Looker platform.

However, Tabb says that Malloy is different than LoookML because it’s entirely language based. “[O]ur goal is to use Malloy all the places you would write a SQL SELECT,” he wrote on Twitter.

Tabb, who previously worked at Borland and Netscape, founded Looker in 2011. The Santa Cruz, California company grew quickly as a next-generation data analytics tool. Google acquired Looker for $2.6 billion in 2019. Tabb was a Datanami Person to Watch for 2018.

Malloy isn’t an official product, and there is no support for it from Google or anyone else. The Malloy VSCode extension can be downloaded from the VSCode marketplace. More information is available at the Looker GitHub repository at https://github.com/looker-open-source/malloy.

Related Items:

The Data Nerds Have Won

Looker’s Audacious Moonshot to Outgrow Oracle

How SQL++ Makes JSON More Queryable