Can I put a subquery in a dimension?

[maxcorbin]

Knowledge Drop

Last tested: Apr 29, 2019

Yes. A query in a dimension is called a correlated sub-query.

If the sub-query is based (FROM) on an UNNEST of an array. It is a great thing to do (BigQuery, Snowflake and Postgres all support this).

If the sub-query is based (FROM) on another table it is discouraged.

Why is it discouraged?

Because it is not performant. It's generally a bad idea to create a subquery in a dimension because that subquery will be rerun for every row being calculated. The recommendation is to use a derived table instead so that the subquery would only be run once.

OK, but it is possible?

In most databases, it's possible. The problem most users run into is that subqueries return multiple rows but every SELECT clause element only expects a single row. So if you add LIMIT 1 to your subquery, it will usually work.

Of course, this often defeats the purpose of your subquery, in which case you will want to create a derived table which has the subquery inside and join the derived table view back to the main explore.

Subqueries in dimensions are not possible in the following databases:

• Redshift does not even allow you to create correlated subqueries except for in the WHERE clause.
• MSSQL does not allow subqueries in the GROUP BY.

This content is subject to limited support.