Top SQL GUIs for Your Data Warehouse (Snowflake, BigQuery, Redshift)

This post was written with help from Kovid Rathee.

These days, most companies depend on one or several data warehouses, such as Snowflake, Google BigQuery, or Amazon Redshift, to store and analyze their data. While most offer a GUI (graphical user interface) to access, view, and edit data, there are also other options available which may be more powerful or useful and if you need to support the needs of other roles outside of engineering, such as data analysts, data scientists, or other business users.

This article will take you through some of the most popular SQL GUIs for data warehouses, cover their features in depth, as well as go over the pros and cons of each.

There are two categories of data warehouse GUIs :  those that come packaged with the data warehouse (built in) and the rest that are more generic and can be connected to different data sources. Let's quickly look at built-in SQL GUIs first before we focus on analyzing the external ones:

Data warehouses with built-in SQL GUIs

All the major cloud data warehouses, such as Snowflake, Firebolt, Azure Synapse Analytics, Amazon Redshift, and Google BigQuery offer native SQL GUIs to let you access the data from the cloud platform's console. Although it's great to have these GUIs tightly coupled with their data warehouse platforms, they don't always offer robust features and the cloud platform GUIs are meant for basic query and data discovery. Surprisingly, though the warehouses themselves are optimized for speed and performance, the GUIs are not necessarily purpose-built for consuming or processing large amounts of data on top of the data warehouse. For example, many companies choose to use Amazon QuickSight to empower their teams to query and analyze data within Amazon Redshift.

Moreover, the built-in GUIs are made only for the cloud platform you're using and their corresponding data warehousing service, which means that while they integrate with other services from the same cloud platform pretty well, they cannot incorporate or connect to different data warehouses and data sources. The GUIs are also pretty minimal, both in terms of UI, as well as feature set.

Here's what the built-in SQL GUI looks like in BigQuery:

And this is what it looks like in Snowflake:

The above are just two examples, but the same goes for other data warehouses like Azure Synapse Analytics, Amazon Redshift, Firebolt, and so on.

So let’s explore the external GUIs next. External GUIs are designed as general-purpose data applications that can connect with one or multiple data sources at the same time, which means if you’re using multiple data warehouses—or are considering a migration in the future—having a warehouse-agnostic GUI may come in handy.

External GUIs for data warehouses

Unlike built-in GUIs, external GUIs are meant for cross-platform work. They easily connect with many data sources, including various data warehouses, and provide a generic interface to interact with the data. In the context of multiple data warehouses or data sources, built-in GUIs might be just fine for engineers interacting with individual systems; however, other business users, such as data analysts, will prefer to use external GUIs to work more efficiently, and will benefit from using features like query libraries, data sharing and exports, and enriched technical metadata to enable better collaboration between teams. Below, we compare Preset, Metabase, Hex, and Retool by using a sample dataset and standard charts.

How we’ll compare the GUIs

To walk you through some of the most widely used external SQL GUIs, we'll be using the following simple SQL queries on top of a "vehicle sales" dataset, which you can find in this GitHub repo.

Num of orders in the last 7 days

-- Orders in the Last 7 Days
SELECT order_date::DATE, 
       SUM(quantity) quantity_ordered
  FROM public.sales_data_o 
 WHERE order_date::DATE > ('2022-09-30'::date - INTERVAL '7 day')
 GROUP BY order_date
 ORDER BY order_date DESC;

Top sold items

-- Top 5 Sold Items 
 SELECT product_line, 
       COUNT(*) quantity_sold 
  FROM public.sales_data_o
 GROUP BY product_line 
 ORDER BY COUNT(*) DESC 
 LIMIT 5;

Revenue chart based on the last 7 days

-- Revenue in the Last 7 Days
SELECT order_date::DATE, 
       SUM(order_value) order_value
  FROM public.sales_data_o 
 WHERE order_date::DATE > ('2022-09-30'::date - INTERVAL '7 day')
 GROUP BY order_date
 ORDER BY order_date DESC;

Note: Some of the mature SQL GUIs won't require you to write the queries at all; in those cases, you can use the GUIs to write a query that will get you the same output. Now that you have the sample sales dataset, let's head over to the GUIs and see how they perform.

Preset

Preset is based on one of the most widely used open source SQL GUIs, Superset. It's known for its UX design and ease of use. The GUI categorizes various actions you can take and its features under four broad categories: Dashboards, Charts, SQL Lab, and Data.

The interface structure is like a traditional SQL editor with the left panel containing database and schema information and the right part of the editor split into two horizontal halves, one for writing queries and the other for looking at the results, as shown in the image below.

To get started, you can add a data source, create a dataset, and head over to the Charts menu item to create something like the following:

Rather than using the SQL queries directly, you can also choose to compose the same queries visually, using a dialog box with columns, predicates, aggregates, and so on from drop downs, calendars, and other data selectors, as shown in the image below.

Once set up, you'll end up with the following dashboard with three components, showing three different cuts of the data:  the last seven days' revenue, the last seven days' total sales, and the top five selling products.

With its advanced security, reliability, and data governance features, Preset is a significant improvement over Superset. But note that while Superset is open source, Preset is not: it only comes in a fully-managed, cloud-based deployment option, and it costs $20 monthly for every user. Data teams prefer it for the ability to run a data exploration platform at scale and the advanced features. If you're looking for a mature GUI with a traditional SQL-editor interface, a proven track record, and a graphical query interface along with presentable visualization capabilities, Preset makes a good choice. However, if you're looking for a Jupyter-notebook-style workspace that allows you to write more inline SQL queries, Python scripts, and graphs, you might be better off choosing another tool from the list.

Metabase

Metabase is an open-source GUI for databases and warehouses. It categorizes its GUI features into four categories: Questions, SQL Queries, Dashboards, and Collections. Questions and SQL Queries are the same, the former being the visual, form-based method of writing SQL queries and the latter being more visual. Dashboards, similar to Preset, are collections of questions and SQL queries seen in a tabular or a chart format. Here's an example of a Question:

You can run the queries on the vehicle data sets using the SQL-editor window by creating a SQL query or a Question in Metabase:

You can list all your Questions and SQL Queries in a collection:

To visualize data, you can select a table from a data source and choose from the various visualization options:

Creating a Dashboard is trivial in Metabase. Collect all the different pieces, i.e., SQL Queries and Questions from your Collection, and organize them in a readable, presentable fashion. That will become your Dashboard. Here's an example Dashboard with the same three queries as mentioned at the beginning of this section:

Data engineers and business users choose Metabase due to the simplicity of its features. The tradeoff, however, is that you get fewer types of charts and graphs compared to other alternatives. If you want to expose your data to business users so they can write SQL queries and play around with a bit of visualization, Metabase is a good choice. However, if you need serious visualization work, there are better options for a similar price. Metabase is open source, which means you can take the source code and deploy and manage it yourself. An alternative to self-hosting and managing Metabase is to go for the managed Metabase option. Like Preset, Metabase charges a monthly subscription fee, but Metabase charges $85/month for up to five users ($17/user/month), and for every user after that, they charge only $5/month.

Hex

While Metabase and Preset both use an interface that resembles a traditional SQL editor, Hex is the most different and uses the notebook form factor. It supports a wide range of data sources and lets you interact with the data in several different ways:

Hex operates on the primitives of Logic and App. The Logic layer is where you build all the different components, such as filtered tables, charts, analysis, etc. In the App layer, those different components come together to enable insights, as shown below.

The Logic screen looks like this:

And the App screen looks like the following:

Hex is a comprehensive notebook-style editor that allows you to work with different data sources seamlessly. It's taken a unique approach in terms of design aesthetics and usability. It has well-designed navigation and a great built-in version control system for all your work.

Choose Hex if your team is a fan of the Jupyter- and Zeppelin-like notebook interface for data and analytics development. Hex can add value with features such as native support for multiple languages (e.g. Python, SQL, R), version control, tight integration with tools like dbt, out-of-the-box security, and native collaboration tools.

Hex might be a good choice if you're looking for more than just an SQL GUI. But it comes at a relatively high price as compared with the other tools in this list. While it does offer a free plan for up to three authors and five projects, the next offered plan costs $24/user/month, which allows you to create unlimited dashboards and other assets.

Retool

Retool is a different option to traditional data warehousing GUIs. It neither takes the SQL-editor route nor follows the Jupyter-notebook style completely. Instead, Retool connects with any data source and builds data applications with the help of their robust drag-and-drop visual editor.

With modules ranging from embeddable tables, charts, text boxes, input boxes, etc., Retool empowers you to build end-to-end data-based applications. Notice the modules on the right-hand panel:

The platform brings together data integrations, a SQL query editor, a UI builder, and a collection of modules along with other actions:

Working on the three queries with Retool gives us the following:

Retool combines the features of an SQL editor, a notebook, and a no-code application development platform, but note that these features might mean a steeper learning curve.

Retool offers a free account for up to five users. For more than five users, you can use the Team plan, which costs $10 per user per month.

A roundup of the SQL GUI tools

Here's a comparison table for all the four GUI tools, which also serves as a summary of some of the points discussed in the article.

Conclusion

Any choice of GUI tool will be based on your particular requirements, team composition, budget, etc. This article took you through some of the most widely used GUI tools for the major data warehouses and their pros and cons.