Snowflake rules on views with non-deterministic functions

Resolve failures when creating Snowflake data quality rules—including custom SQL rules—on views whose definitions reference non-deterministic functions such as CURRENT_DATE, CURRENT_TIMESTAMP, FIRST_VALUE, LAST_VALUE, RANDOM, UUID_STRING, or other window functions.

Rule fails to schedule with non-deterministic function error

Error

Data metric function body can't refer to the non-deterministic function <FUNCTION_NAME>

Examples seen in the UI:

• FIRST_VALUE is not supported in custom SQL rules
• CURRENT_DATE is not supported
• 510101: Data metric function body cannot refer to the non-deterministic function ...

Cause

This is a Snowflake constraint on data metric functions (DMFs). Snowflake requires the DMF body expression to be deterministic and explicitly disallows non-deterministic functions inside it. This isn't an Atlan limitation, and not a problem with the SQL you wrote in the rule.

When Atlan creates the DMF for your rule, Snowflake validates the function body before accepting it. During that validation Snowflake transitively expands the definition of every view referenced in the body. If any of those view definitions contain a non-deterministic function, Snowflake refuses to create the DMF—even when your rule SQL itself is fully deterministic.

The error message can be misleading: it names the offending function (for example, FIRST_VALUE) without telling you that the function is coming from the underlying view definition, not from your rule SQL. Look for the non-deterministic function inside the definition of the view you scheduled the rule on, or any view it joins to.

Solution

You have three options, in order of recommendation:

Dynamic Table (recommended)

A Snowflake Dynamic Table is materialized data refreshed by Snowflake on a schedule you choose. Because a Dynamic Table is stored data and not a view, Snowflake doesn't inline any upstream view definitions when validating the DMF, so the non-deterministic function restriction no longer applies.

1. Ask your data team to create a Dynamic Table that selects from the views your rule targets:

   CREATE DYNAMIC TABLE MY_DB.MY_SCHEMA.BOM_F_DT
     TARGET_LAG = '1 hour'
     WAREHOUSE = MY_WAREHOUSE
   AS
   SELECT * FROM MY_DB.MY_SCHEMA.BOM_F_V;

2. Recrawl the schema in Atlan so the Dynamic Table appears as an asset.

3. Re-create your data quality rule for the Dynamic Table instead of the view.

Your existing views stay exactly as they are. Pick a TARGET_LAG that matches how fresh your DQ checks need to be (the minimum is 1 minute, but for most DQ workloads 1 hour or more is enough).

Snapshot table on schedule

If Dynamic Tables aren't available in your edition, a Snowflake task that performs CREATE OR REPLACE TABLE ... AS SELECT * FROM <view> on a cadence gives you the same effect manually.

1. Create a snapshot table:

   CREATE OR REPLACE TABLE MY_DB.MY_SCHEMA.BOM_F_SNAPSHOT AS
   SELECT * FROM MY_DB.MY_SCHEMA.BOM_F_V;

2. Schedule a task to refresh it on your preferred cadence.

3. Re-create the rule for the snapshot table.

Point rule at base tables

If neither materialization option is acceptable, write the rule directly targeting the base tables and replicate the filter logic from the view in your rule SQL. This avoids the view entirely and the DMF validates cleanly.

This is the lowest-cost option but couples your rule to the view's internal logic, so the rule has to be updated if the view definition changes.

A few approaches look promising but won't resolve the error:

• Modifying just your rule SQL doesn't help because the restriction is on the view definitions, not your rule. As long as the rule targets the view, Snowflake still inlines it and rejects the DMF.
• Wrapping the view in another view doesn't help either, since Snowflake walks through nested views recursively during validation.
• Snowflake materialized views don't permit window functions like FIRST_VALUE in their definition, so they can't wrap the same logic.

If none of the preceding workarounds are viable and you need the view itself to be DMF-eligible, raise this with your Snowflake account team as a request to relax the DMF non-determinism restriction. Atlan can share a minimal reproduction on request—contact Atlan support and reference this page.

---