Write SQL And Get Charts: Comparing SQL Visualization Tools

So much of the life of an analyst relies on the ability to quickly and reliably transform our SQL queries into impactful visualizations. While this may sound trivial to outsiders, analysts know this is often anything but straightforward. Every company, data team, and individual analyst has their own preferred method. 

This article walks compares the most common (and some uncommon) approaches to the quintessential analyst journey from SQL query to chart. 

1. The OG: Specialty tools (SQL IDE → data viz)

For most of the last decade, this would be the typical day of a data analyst:

  1. Craft and execute SQL query in a SQL IDE or editor.
  2. Download the data as a CSV or paste a query into a data visualization SQL editor.
  3. Import data to the data visualization tool.
  4. Build and customize the visualization.

Examples

SQL IDEs/editors   DBeaver is a popular SQL IDE used by analysts.   DataGrip is a popular SQL IDE.
Data viz tools   Tableau is a data viz tool known for its wide array of charts.   Power BI is a popular data viz tool from Microsoft.

 

This method allows for the most power in each step by using the most specialized tool for each job. But that level of specialization can come with a price. Because your SQL and visualization are spread across multiple tools, it can be the slowest of the options, especially if you are iterating through several tweaks to your query and visualization. 

Benefits

  • You get the most powerful SQL interface.
  • And the most powerful visualization tool.
  • Outputs are usually interactive so the business can use high-level filters or tool tips.

Downsides

  • Iteration is painful since you are switching between tools, usually several times before finalizing a chart.
  • Data visualization tools tend to be very expensive for editor licenses, so many organizations can’t afford to provide access to business users.
  • Reusing old work is difficult since it's split between separate tools, so this approach doesn’t scale well.

Best for

  • When you need to write complex queries and build highly-customized, and high-quality visualizations and cannot compromise on either.

2. The (too?) easy button: Self-service BI tools

Many BI tools classify themselves self-service, meaning they offer user-friendly ways of querying data that don't rely on code. The main benefit to analysts is that these tools allow you to go from SQL query to visualization in one tool. 

The tradeoff, however, is each of these options is slightly less fully-featured than specialty tools. This narrowing of capabilities for both the querying and the visualization means there are far fewer use cases where you can take advantage of this approach. For that reason, many analysts fall back to other options after giving these tools the college try.

Examples

Mode Analytics combines analyst-friendly SQL with business user-friendly data viz. Metabase is a popular analytical tool that mixes SQL and data viz.

 

Benefits

  • Tends to be faster since you aren't switching between tools.
  • Can provide access to more people since licenses are usually less expensive than specialized tools.

Downsides

  • SQL editors leave much to be desired so people often use a different SQL IDE then paste in completed queries.
  • These tools offer less customization for visualization so you may still have to do additional tweaking in other tools (like spreadsheets).
  • It can be hard to get business users to interact with the outputs.

Best for

  • When you need to get something out quickly, which requires a simple SQL query and an equally simple chart.

3. The generalist: Data science notebooks

Data science notebooks like Jupyter have gained a lot of popularity in the last few years. They are long-form development environments where a user can write code to query the database, build visuals, surround the chart with visuals and commentary before sending it to users. 

Since they are not built for SQL querying, notebooks can be a less-than ideal place to write SQL queries, leaving many of the comforts of SQL IDEs long behind you. However, the visual customization capabilities are unparalleled, making the visualization part of the SQL-to-chart journey the main reason they made this list. 

Examples 

jupyter-logo google-colab

 

Benefits

  • There’s no need to switch between tools.
  • You can add context so there's less pressure on the visual to communicate everything, which results in less iteration.
  • The tool is as powerful as the coding language you're using.
  • Notebooks are usually free.

Downsides

  • You don’t get the benefits of a SQL IDE.
  • Notebooks require some coding knowledge (Python, r).
  • Building visuals in code can be time-consuming and complex.

Best for

  • Answering complex questions where using analytical programming languages is required, or highly customized visuals.

4. The new kid: BI notebooks

BI notebooks are similar to data science notebooks in their format but have been optimized for the analyst’s journey of SQL to visualization. Practically, this means they integrate the power of a SQL IDE, the speed of data visualization tools, and the long-form environment of data science notebooks in one tool. 

With BI notebooks like Count, you get the workflow benefits of a self-service BI tool without sacrificing all the features of your specialized tools. It makes for a far more pragmatic solution to the ever-present challenge of transforming SQL to impactful graphs and charts.

ExamplesCount is a BI notebook that combines SQL and data viz.

Benefits

  • You don't have to switch between tools.
  • You only need to know SQL.
  • You can add context so there's less pressure on the visual to communicate everything, which results in less iteration.

Downsides

  • BI notebooks offer less visual customization than data science notebooks and some data visualization tools.

Best for

  • Answering complex and nuanced questions at speed that benefit from contextualized explanation.

Conclusion

Business demands more from data than ever before, and the analyst’s ability to swiftly and comprehensively meet that need can make the difference between success and failure. To do this well, we need to rethink the way we accomplish the most fundamental of data tasks: turning SQL into a chart. 

There are tons of options out there for writing queries and visualizing the data, but each comes with its own benefits and tradeoffs. The trick when weighing them against one another is to be mindful of your organization’s needs and skill sets to make sure you choose the right one for the task at hand.

SQL and charts work better when you fuel them with fresh data. Learn how Panoply makes it easy to keep your analysis up to date.
Get a Personalized Demo
Read more in:
Share this post:

Work smarter, better, and faster with monthly tips and how-tos.