Thread by @ghalimi

This thread introduced the SQL vs. Tabula discussion. This other thread will cover our SQL query designer. This tool will focus on @duckdb's SQL dialect, but it will be designed to work with virtually any SQL cloud database (@SnowflakeDB, @databricks, etc).

https://twitter.com/ghalimi/status/1614234050981658624

There are many ways to design a SQL query designer, and to design a good one, two critical questions must be answered first: who is the audience, and what is their working environment?

For #STOIC, we must serve two very different user groups:

1. Users who are new to SQL
2. Users who are SQL experts

These two users groups will have fundamentally different sets of needs. Therefore, our SQL designer will probably have two different modes, or will be used in two fundamentally different ways, by two fundamentally different groups of users.

But before we dive into that, let's review their working contexts: #STOIC is a fully integrated tool. Therefore, our query designer is not a tool aimed at designing arbitrary and standalone SQL queries.

Instead, it's a tool aimed at designing SQL queries tightly integrated with the dependency graph produced from a #STOIC workbook. That being said, while most of these queries will be executed by our embedded @duckdb engine, many will run upstream, on external cloud databases.

Another critical assumption is that such queries are executed either against external databases that have arbitrary schemas, or against #STOIC's own distributed database engine, using snowflake schemas with hierarchical denormalization. And the latter is our #1 priority.

Yet another critical assumption is that such queries will serve a large and diversified user base made of dozens of Creators, hundreds of Explorers, and thousands to millions of Viewers. As a result, caching is really, really important.

In other words, this SQL query designer must help our users take full advantage of the awesome power offered by our multi-level Reactive Caching Service (RCS). To a very large extent, this is its #1 raison d'être.

With that in mind, when designing this SQL query designer, we must embrace a very particular workflow, which I would describe as table-oriented: everything starts from a table of data, and everything is driven by the journey which this table embarks upon.

For example, what that means is that our user does not start from an Entity-Relationship Model (ERM). Instead, our user starts from a set of tables, then establishes relationships between them, by properly typing certain columns of these tables using our "relation" datatype.

This is a much more natural way of doing things, supported by the use of VLOOKUP by tens of millions of Excel users. And these relations allow us to produce this type of ERM automatically:

From this ERM, we can help our user adjust the schema of their multi-table dataset, bringing it closer and closer to a proper snowflake schema, and we can automate the process of denormalizing its tables in a hierarchical manner.

en.wikipedia.org/wiki/Snowflake…

Finally, we must remember that our SQL query designer must facilitate the distribution of SQL queries across clusters of EC2 VMs/Fargates and across a fleets of Lambda functions. And distributing SQL queries is, by far, the hardest problem that we will have to solve.

But one must keep in mind that this problem cannot be solved entirely. SQL is simply too rich. If queries become a bit too complex and are made against datasets that are a bit too large, there simply is no way to execute them efficiently on a distributed platform.

Therefore, the SQL query designer should steer users in a direction that will make this distribution as efficient as possible, for datasets that are as large as possible.

This will be achieved using many complementary techniques. Among them, denormalizing datasets whenever possible, partitioning large tables alongside multiple partitioning schemes, aligning partitions across multiple tables, and breaking queries into multiple ones.

With all that in mind, let's review what this tool will look like. First and foremost, it will have two complementary operating modes: graphical and textual. The graphical mode will be used by novice users, while the textual mode will be used by expert users.

Most importantly, this two modes will be available at all times, with full roundtrip engineering. What this means is that you will be able to go from graphical to textual and back to graphical, at any time, for any part of a query.

This is possible, because SQL is a relatively small and simple language from a syntax and structure standpoint. Its complexity applies at a topological level, unlike programming languages like C++ or Java.

For these languages, past experiences with UML have clearly demonstrated that roundtrip engineering does not work in the real world. It can work within certain environments for expert users demonstrating an unusual level of discipline, but it breaks down everywhere else.

SQL is different though. First, it has relatively few primitives, and these are assembled in relatively rigid ways. For example, a SELECT statement contains a single WHERE clause at the root.

Second, a SELECT statement is a relatively standalone entity, blessed with a very hierarchical structure. This is unlike traditional code for which you have many functions scattered across many different files, referencing each other through very complex dependency graphs.

For these reasons (and many others), roundtrip engineering with SQL is within reach, therefore should be taken advantage of. The graphical representation of a SELECT statement will be topologically isomorphic to its textual representation, which simplifies a ton of things.

At this point, I need to outline a critical design element: for SQL experts to be fully productive with the textual mode of this SQL query designer, proper auto-completion must be offered, across all clauses, for things like tables, columns, functions, and keywords.

But this auto-completion should not be confused with a fully graphical clause editor. For example, if we look at a FROM clause, the novice user will click on some "Add table" link to add a table, while the expert user will type a comma to do the same.

And yet both interactions should display the same table picker. This commonality of UI controls is what will make this tool truly progressive. Yet one should not consider clicking on "Add table" and typing a comma to be equivalent: to novice users, the latter is out of reach.

Of the two, the textual mode is obviously the easiest one to build. In a nutshell, all that is needed is a solid auto-completion feature. It is by no means a trivial one to implement well, but it is a relatively well-bounded effort. The graphical mode is where the challenge lies.

The graphical mode is complex because each clause has a different syntax. Therefore, the UI must take that into account, and generate a proper UI flow that won't get our user too confused too fast, especially when SELECT statements get deeply nested.

For that part, we should take advantage of a critical design element of the #STOIC UI: the use of Miller columns for the left panels. With our upcoming editor, the SELECT nested within the WHERE clause will be displayed within a separate Miller column.

en.wikipedia.org/wiki/Miller_co…

This design will provide a much better hierarchical structuring of complex queries, while giving us the ability to preview the results of nested SELECT statements through the table displayed on the right.

This will create some really interesting challenges when these nested SELECT statements reference columns defined by parent SELECT statements, but it will make it a lot easier to construct and debug complex queries with deep nesting and complex joins.

In the next thread, we will review all clauses one by one.

https://twitter.com/ghalimi/status/1614266907666055169