Making complex SQL simple

Taylor Brownlow -

With exploding cells, your most complex SQL query or model just got a whole lot simpler.

TL;DR:

The status-quo

Whether you're a data engineer trying to understand a complex data model, or an analyst trying to make sense of a heavily-nested SQL query, your approach probably involves the same steps:

  • copy part of the query, paste into a tab in your IDE, run it against your database and repeat until you have more tabs than you can keep track of
  • screenshotting the results into a document so you can see them side by side
  • share this with your team when you need to explain the query/model to someone else

Aside from obviously being less than ideal, this process is too important to be left to a patchwork of tools with human glue. Being able to understand, communicate, and reuse complex logic can make the difference between a group of data people and an effective data team.

Break it down

One of the key features of Count from day one has been the ability to chain queries together through cells. This allowed us to build up complex pieces of logic step-by-step in the canvas, all the while making sure it was annotated and easy for others to follow.

With exploding cells, you can now do that same workflow - but in reverse.

Take any complex SQL query or model and explode the cell into its component subqueries:

To explode a query into a DAG, click the 💥 button on the cell menu.

Explore at all levels

With each subquery as its own cell, it means you can easily explore any part of your query.

  • Visualize the data to see patterns and distributions
  • Create new queries to further aggregate and interrogate the data
  • Add filters and parameters to see how each cell changes for different values
  • Add sticky notes to track questions, findings, and next steps

Share to scale

Being able to communicate all you've discovered in this complex query or model makes all your hard work worth it. That could be:

  • explaining to fellow data engineers why we need to update our key transformation model
  • documenting a key query so other analysts can quickly use it the next time they have a similar request
  • or, just being kind to your future self and leaving notes on why you did what you did

Sharing a decomposed query or model with annotations makes it easy for other people to follow your work, making it easier to get buy-in, and to make sure your work is reused later.

Exporting your new queries

And, of course, should you need to export your new complex SQL query back into your data modeling tool, or code management tool, you can always copy the full, compiled SQL of any cell back out:

For dbt users

If you use dbt for your data modeling, exploding cells allow you to quickly explore your models, uncover bugs faster, and communicate suggested changes to your team more effectively.

But this is just the beginning of what we're working on for you.

If you're still reading (wow, thank you) and would like to talk to us about what else we're building for the dbt workflow, please let me know (taylor[at]count[dot]co)!