Skip to main content

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

Updated

Description

If you use Unity Analytics SQL Data Explorer to build custom reports and dashboards, you might have encountered an unexpected error when running previously working queries. Specifically, queries utilizing the TABLE() function—commonly used alongside GENERATOR(...) to create date sequences for 7-day rolling averages and other time-series graphs—are now being restricted and blocked from running. This article explains why this valid Snowflake SQL function is now blocked by Unity's internal query validator to help enforce supported query boundaries, and provides a supported workaround using a Recursive Common Table Expression (CTE) to help you rebuild your date-generation logic and get your graphs working again.
 

Symptoms

  • Queries executed in the Unity Analytics SQL Data Explorer that use the TABLE() function fail to run and return an error indicating the function is restricted.
  • This commonly breaks queries that rely on TABLE(GENERATOR(...)) to create date sequences, such as drawing 7-day rolling average graphs.

Cause

While TABLE() is a valid Snowflake SQL function, it was intentionally restricted in the SQL Data Explorer as part of a security-related query validation change.

To help enforce supported query boundaries, several SQL keywords—including TABLE()—were added to a blocked list in Unity's validation parser, which sits between the Data Explorer and the underlying Snowflake data warehouse. Because of this change, constructs like TABLE(GENERATOR(...)) are blocked by the parser and no longer supported.
 

Workaround

You can achieve similar date-generation logic by using a Recursive CTE (Common Table Expression) instead of the blocked TABLE() function.

To generate a rolling set of dates (for example, the last 7 days), replace your TABLE() generator with the following SQL approach:
 
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;

You can adjust the WHERE n < 7 condition to match whatever rolling date range your original query required before joining it with your event data.

Related to

Related articles