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 compares the most common (and some uncommon) approaches to the quintessential analyst journey from SQL query to chart.

1. Oldschool: Spreadsheets

In the past, 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 spreadsheet tool (like Excel).
  4. Build and customize the visualization or even add additional slice and dice processes on the data there.

Although Excel (and other similar applications) are extremely powerful, they do come with limitations that today’s data warehouses can overcome relatively easily.

Benefits

  • Everyone knows how to use Excel, so everyone can add additional analytics layers on the result
  • Easily shareable with multiple people

Downsides

  • Limitations on results size
  • In many cases it will require manual updates
  • Need to know SQL to get the data

Best for

  • Easy to start analytics. 

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

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.

 

The main difference between this method and the one above is the additional strength of the visualization tool. 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.
  • The majority of tools require minimum technical knowledge therefore it is accessible for more people.

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, and 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.

Get a free consultation with a data architect to see how to build a data warehouse in minutes.
Request Demo
Read more in:
Share this post:

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