Skip to main content

Why doesn't my TABLE() function work in Analytics SQL Data Explorer?

Updated

TABLE() is intentionally blocked in the Analytics SQL Data Explorer by Unity's query validator, so TABLE(GENERATOR(...)) date sequences no longer run. Use a recursive common table expression instead — the example below generates the same rolling date range and passes validation.

TABLE() is blocked in the Analytics SQL Data Explorer. It is a valid Snowflake function, but Unity's query validator rejects it before the query reaches Snowflake, so queries built on TABLE(GENERATOR(...)) — commonly used to generate date sequences for rolling averages — stop running. Rebuild the date sequence with a recursive common table expression instead.

What you will see

  • A query that uses TABLE() fails to run and returns the error TABLE function is not allowed.
  • This most often shows up in queries using TABLE(GENERATOR(...)) to build date sequences, such as a 7-day rolling average. The restriction is on the TABLE() function itself, not just the generator pattern, so other table-valued functions wrapped in TABLE() are affected too.

Why is it blocked?

TABLE() was added to a blocked list in Unity's SQL validation parser, which sits between the Data Explorer and the underlying Snowflake warehouse. The change was made deliberately, to keep queries inside supported and customer-scoped boundaries.

The full list of blocked keywords is not published. As a general rule, the Data Explorer supports read-only reporting queries: as the SQL Data Explorer documentation notes, non-SELECT statements and $SYSTEM commands are limited in functionality.

How do I fix my query?

Replace the TABLE() generator with a recursive common table expression. This example produces the last seven days:

WITH RECURSIVE RECENT_DATES AS (
    SELECT current_date AS event_date, 0 AS n
    UNION ALL
    SELECT dateadd(day, -1, event_date), n + 1
    FROM RECENT_DATES
    WHERE n < 7
)
SELECT *
FROM RECENT_DATES;
 

To adapt it:

  1. Change the WHERE n < 7 condition to the number of days your original query covered.
  2. Join RECENT_DATES to your event data exactly as you joined the previous generated table.

WITH, RECURSIVE and UNION ALL are not blocked, so this approach passes validation.


 

Related to

Related articles