Can You Use CTE in Tableau Custom SQL?

Trying to use a Common Table Expression (CTE) in Tableau's Custom SQL can feel like a game of chance - sometimes it works perfectly, and other times it throws an error that sends you searching for answers. The short answer is yes, you can often use a CTE, but its success depends entirely on your underlying database and its driver. This article will show you how to use CTEs in your Tableau connections, explain why they sometimes fail, and give you powerful workarounds for when they don't work.

First, What Are We Talking About?

Before we jump into the solution, let's quickly align on the two key concepts here: CTEs and Tableau Custom SQL.

What is a CTE (Common Table Expression)?

A CTE is a temporary, named result set that you can reference within a larger SQL query. You define it using the WITH clause. Think of it as a temporary table that exists only for the duration of your query. They are incredibly useful for:

Improving Readability: Breaking down long, complex queries into logical, easy-to-read blocks.
Recursion: Solving problems that involve hierarchical data, like organizational charts or product categories.
Modularity: Referencing the same resulting data multiple times in a query without having to rewrite the same subquery over and over.

In short, CTEs make your SQL cleaner, more organized, and easier to debug.

What Is Tableau Custom SQL?

When you connect to a data source in Tableau, you typically drag and drop tables onto the canvas and create joins visually. The Custom SQL option allows you to bypass that visual interface and instead write your own SQL query to define the data source. This is great for performing complex transformations, filtering, or aggregations before the data even gets to Tableau.

How to Use a CTE in Tableau Custom SQL (When It Works)

Modern databases like PostgreSQL, SQL Server, Snowflake, and BigQuery have excellent support for CTEs. When connecting to these sources, using a CTE is usually straightforward.

Let's use a practical example. Imagine you have two tables in your database: orders and customers. You want to create a data source that only includes customers and their very first order date.

Here are the step-by-step instructions:

1. Connect to Your Data

Open Tableau Desktop and connect to your database (e.g., PostgreSQL). In the data source pane, you will see a list of your tables.

2. Select "New Custom SQL"

Instead of dragging a table onto the canvas, find the "New Custom SQL" option and drag it over. This will open a text editor for your query.

3. Write Your Query with a CTE

In the editor, you can now write your SQL query using a WITH clause. For our example, we'll create a CTE called CustomerFirstOrder to find the minimum order date for each customer. Then, we'll join that result back to the orders and customers tables to get the details.

Here's what the code would look like:

4. Preview and Confirm

Click "OK." Tableau will execute the query against your database. If the syntax is correct and your database supports it, the "Update Now" button will populate the data preview grid. Now you can move on to your worksheet and start building vizzes with this pre-processed data source.

When CTEs Don't Work: The Common Problems and Solutions

The number one reason a CTE fails in Tableau Custom SQL isn't your SQL skills, it's how Tableau wraps your query. Some database drivers (notably older versions of MySQL) don't allow a WITH clause inside a subquery. When you write Custom SQL, Tableau doesn't just send your exact query. It often wraps it inside another query for its own purposes, like this:

If the database driver doesn't support a CTE in that position, you get a syntax error. It's frustrating, but there are several excellent workarounds.

Workaround 1: Use a Subquery (or Derived Table)

The simplest alternative to a CTE is a subquery, also known as a derived table. It accomplishes the same logical goal but can be less readable. You're essentially placing the CTE's logic directly into the FROM or JOIN clause of your main query.

Let's rewrite our previous example using a subquery:

This query is functionally identical to the CTE version and will almost always work in Tableau because it avoids the WITH keyword issue. The tradeoff is that as your logic gets more complex, nesting multiple subqueries can become messy and hard to maintain.

Workaround 2: Create a Database View (The Best Practice)

A far more robust and scalable solution is to move the complexity out of Tableau and into the database itself by creating a View.

A database view is a stored query that is treated like a virtual table. You can embed your complex logic - including one or more CTEs - into the view one time.

Using a SQL client connected to your database (like DBeaver, pgAdmin, or SQL Server Management Studio), you'd run a command like this:

Once the view is created, you can connect to it in Tableau just like any other table. You won't need to use Custom SQL at all! This is the superior method because:

It's Reusable: Anyone on your team can connect to this view without needing to understand or copy the underlying SQL.
It's Efficient: It centralizes the logic in one place. If the business rule for "first order" changes, you only update the view, and all connected workbooks update automatically.
Performance: It often gives Tableau more information to optimize its own internal queries, which can sometimes be faster than a generic Custom SQL block.

Workaround 3: Use Tableau Prep Builder

If you don't have permission to create views in your database or prefer a more visual approach, Tableau Prep Builder is an excellent alternative. Tableau Prep is designed for exactly this kind of data shaping.

In Prep, you'd replicate the CTE's logic using a visual flow:

Connect to your orders table.
Create an Aggregate step to group by customer_id and find the MIN(order_date).
Join that aggregated result back to the original orders table.
Join that result with the customers table.
Output the final, clean data source to either a static file (.hyper extract) or publish it directly to Tableau Server/Cloud.

You can then connect your Tableau Desktop workbook to that clean, published data source.

Best Practices for Custom SQL in Tableau

Whether you're using CTEs, subqueries, or simple selects, keep these tips in mind:

Test in a SQL Client First: Always write and test your query in a dedicated SQL tool before pasting it into Tableau. It's much faster to debug errors there.
Select Only What You Need: Avoid using SELECT *. Your query should only pull the columns and rows required for your analysis. This minimizes data transfer and improves performance.
Beware of Performance Hits: Custom SQL can sometimes prevent Tableau from performing its own query optimizations, like "query folding." If performance is slow, pushing the logic into a database view or using a Tableau extract is almost always a better option.

Final Thoughts

While Common Table Expressions offer a clean way to structure complex logic, their compatibility with Tableau Custom SQL depends on the database you're connected to. If they work, they're a great tool to have, but if they fail, don't get stuck. Switching to a subquery is a quick fix, while creating a materialized view in your database is a more robust and scalable long-term solution.

The manual process of writing, testing, and debugging complex SQL - whether with CTEs or subqueries - is exactly the kind of friction we built Graphed to eliminate. Instead of spending hours wrestling with syntax, our platform allows you to connect your data sources and create entire real-time dashboards using simple, natural language. You can just ask, "Show me first order details by customer and order total," and get a live, interactive visualization in seconds, no CTEs or subqueries required. If you're tired of being a full-time query writer, give Graphed a try and get back to finding insights.