Skip to main content

volatility

💡Did you know...
Available from dbt v1.11 or with the dbt "Latest" release track.
functions/<filename>.yml
functions:
  - name: <function name>
    config:
      volatility: deterministic | stable | non-deterministic

Definition​

You can optionally use the volatility config for SQL or Python UDFs to describe how predictable the function output is by using deterministic, stable, or non-deterministic. Warehouses use this information to decide if results can be cached, reordered, or inlined. Setting the appropriate volatility helps prevent incorrect results when a function isn’t safe to cache or reorder.

For example:

  • A function that returns a random number (random()) should be set as non-deterministic because its output changes every time it’s called.
  • A function that returns today’s date (current_date()) is stable; its value remains consistent within a single query execution but may change between queries. If it were configured as deterministic, a warehouse might incorrectly cache the value and reuse it on subsequent days.

By default, dbt does not specify a volatility value. If you don’t set volatility, dbt generates a CREATE statement without a volatility keyword, and the warehouse’s default behavior applies — except in Redshift.

In Redshift, dbt sets non-deterministic (VOLATILE) by default if no volatility is specified, because Redshift requires an explicit volatility and VOLATILE is the safest assumption.

 Warehouse-specific volatility keywords

Different warehouses show volatility controls with different keywords and default values:

Warehousedeterministicstablenon-deterministic
SnowflakeIMMUTABLENot supportedVOLATILE (default)
RedshiftIMMUTABLESTABLEVOLATILE (default)
DatabricksDETERMINISTICNot supportedAssumed non-deterministic unless declared
PostgresIMMUTABLESTABLEVOLATILE (default)

BigQuery does not support explicitly setting volatility. Instead, BigQuery infers volatility based on the functions and expressions used within the UDF.

Supported volatility types​

In dbt, you can use the following values for the volatility config:

ValueDescriptionExample
deterministicAlways returns the same output for the same input. Safe for aggressive optimizations and caching.substr() — Produces the same substring when given the same string and parameters.
stableReturns the same value within a single query execution, but may change across executions. Not supported by all warehouses. For more information, see Warehouse-specific volatility keywords.now() — Returns the current timestamp the moment a query starts; constant within a single query but different across runs.
non-deterministicMay return different results for the same inputs. Warehouses shouldn't cache or reorder assuming stable results.first() — May return different rows depending on query plan or ordering.
random() — Produces a random number that varies with each call, even with identical inputs.

Example​

In this example, we're using the deterministic volatility for the is_positive_int function:

functions/schema.yml
functions:
  - name: is_positive_int
    description: Check whether a string is a positive integer
    config:
      volatility: deterministic # Optional: stable | non-deterministic | deterministic
    arguments:
      - name: a_string
        data_type: string
    returns:
      data_type: boolean

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Edit this page