Why am I seeing a `SELECT boundary AS boundary FROM (SELECT ...)` query in our DB? Dimension fill! (Community)

View the original community article here

Last tested: Sep 29, 2020
 

This query is being generated from Dimension Fill in explores using time based dimensions. The SQL here doesn't show on the SQL tab and gets run separately and added into the results after. This can be disabled on a field by field basis by setting allow_fill: no (documentation).

The exact SQL of this query will be dependent on the dialect and timeframe. Couple examples below.

Snowflake

SELECT boundary AS boundary FROM (SELECT TO_CHAR(DATE_TRUNC('second', (DATEADD('month', 2, DATEADD('month', -1, DATE_TRUNC('month', CURRENT_DATE()))))), 'YYYY-MM-DD HH24:MI:SS') AS boundary ) boundaries

BigQuery

SELECT boundary AS boundary FROM (SELECT FORMAT_TIMESTAMP('%F %T', TIMESTAMP_TRUNC((TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), WEEK(MONDAY)), INTERVAL (-4 * 7) DAY)), WEEK(MONDAY))) AS boundary ) boundaries