Skip to main content

Snowflake configurations

Transient tables​

Snowflake supports the creation of transient tables. Snowflake does not preserve a history for these tables, which can result in a measurable reduction of your Snowflake storage costs. Transient tables participate in time travel to a limited degree with a retention period of 1 day by default with no fail-safe period. Weigh these tradeoffs when deciding whether or not to configure your dbt models as transient. By default, all Snowflake tables created by dbt are transient.

Configuring transient tables in dbt_project.yml​

A whole folder (or package) can be configured to be transient (or not) by adding a line to the dbt_project.yml file. This config works just like all of the model configs defined in dbt_project.yml.

dbt_project.yml
name: my_project

...

models:
  +transient: false
  my_project:
    ...

Configuring transience for a specific model​

A specific model can be configured to be transient by setting the transient model config to true.

my_table.sql
{{ config(materialized='table', transient=true) }}

select * from ...

Query tags​

Query tags are a Snowflake parameter that can be quite useful later on when searching in the QUERY_HISTORY view.

dbt supports setting a default query tag for the duration of its Snowflake connections in your profile. You can set more precise values (and override the default) for subsets of models by setting a query_tag model config or by overriding the default set_query_tag macro:

dbt_project.yml
models:
  <resource-path>:
    +query_tag: dbt_special

models/<modelname>.sql
{{ config(
    query_tag = 'dbt_special'
) }}

select ...

In this example, you can set up a query tag to be applied to every query with the model's name.


  {% macro set_query_tag() -%}
  {% set new_query_tag = model.name %} 
  {% if new_query_tag %}
    {% set original_query_tag = get_current_query_tag() %}
    {{ log("Setting query_tag to '" ~ new_query_tag ~ "'. Will reset to '" ~ original_query_tag ~ "' after materialization.") }}
    {% do run_query("alter session set query_tag = '{}'".format(new_query_tag)) %}
    {{ return(original_query_tag)}}
  {% endif %}
  {{ return(none)}}
{% endmacro %}

Note: query tags are set at the session level. At the start of each model materialization, if the model has a custom query_tag configured, dbt will run alter session set query_tag to set the new value. At the end of the materialization, dbt will run another alter statement to reset the tag to its default value. As such, build failures midway through a materialization may result in subsequent queries running with an incorrect tag.

Merge behavior (incremental models)​

The incremental_strategy config controls how dbt builds incremental models. By default, dbt will use a merge statement on Snowflake to refresh incremental tables.

Snowflake's merge statement fails with a "nondeterministic merge" error if the unique_key specified in your model config is not actually unique. If you encounter this error, you can instruct dbt to use a two-step incremental approach by setting the incremental_strategy config for your model to delete+insert.

Configuring table clustering​

dbt supports table clustering on Snowflake. To control clustering for a table or incremental model, use the cluster_by config. When this configuration is applied, dbt will do two things:

  1. It will implicitly order the table results by the specified cluster_by fields
  2. It will add the specified clustering keys to the target table

By using the specified cluster_by fields to order the table, dbt minimizes the amount of work required by Snowflake's automatic clustering functionality. If an incremental model is configured to use table clustering, then dbt will also order the staged dataset before merging it into the destination table. As such, the dbt-managed table should always be in a mostly clustered state.

Using cluster_by​

The cluster_by config accepts either a string, or a list of strings to use as clustering keys. The following example will create a sessions table that is clustered by the session_start column.

models/events/sessions.sql
{{
  config(
    materialized='table',
    cluster_by=['session_start']
  )
}}

select
  session_id,
  min(event_time) as session_start,
  max(event_time) as session_end,
  count(*) as count_pageviews

from {{ source('snowplow', 'event') }}
group by 1

The code above will be compiled to SQL that looks (approximately) like this:

create or replace table my_database.my_schema.my_table as (

  select * from (
    select
      session_id,
      min(event_time) as session_start,
      max(event_time) as session_end,
      count(*) as count_pageviews

    from {{ source('snowplow', 'event') }}
    group by 1
  )

  -- this order by is added by dbt in order to create the
  -- table in an already-clustered manner.
  order by session_start

);

 alter table my_database.my_schema.my_table cluster by (session_start);

Automatic clustering​

Automatic clustering is enabled by default in Snowflake today, no action is needed to make use of it. Though there is an automatic_clustering config, it has no effect except for accounts with (deprecated) manual clustering enabled.

If manual clustering is still enabled for your account, you can use the automatic_clustering config to control whether or not automatic clustering is enabled for dbt models. When automatic_clustering is set to true, dbt will run an alter table <table name> resume recluster query after building the target table.

The automatic_clustering config can be specified in the dbt_project.yml file, or in a model config() block.

dbt_project.yml
models:
  +automatic_clustering: true

Configuring virtual warehouses​

The default warehouse that dbt uses can be configured in your Profile for Snowflake connections. To override the warehouse that is used for specific models (or groups of models), use the snowflake_warehouse model configuration. This configuration can be used to specify a larger warehouse for certain models in order to control Snowflake costs and project build times.

The example config below changes the warehouse for a group of models with a config argument in the yml.

dbt_project.yml
name: my_project
version: 1.0.0

...

models:
  +snowflake_warehouse: "EXTRA_SMALL"    # use the `EXTRA_SMALL` warehouse for all models in the project...
  my_project:
    clickstream:
      +snowflake_warehouse: "EXTRA_LARGE"    # ...except for the models in the `clickstream` folder, which will use the `EXTRA_LARGE` warehouse.

snapshots:
  +snowflake_warehouse: "EXTRA_LARGE"    # all Snapshot models are configured to use the `EXTRA_LARGE` warehouse.

Copying grants​

When the copy_grants config is set to true, dbt will add the copy grants DDL qualifier when rebuilding tables and views. The default value is false.

dbt_project.yml
models:
  +copy_grants: true

Secure views​

To create a Snowflake secure view, use the secure config for view models. Secure views can be used to limit access to sensitive data. Note: secure views may incur a performance penalty, so you should only use them if you need them.

The following example configures the models in the sensitive/ folder to be configured as secure views.

dbt_project.yml
name: my_project
version: 1.0.0

models:
  my_project:
    sensitive:
      +materialized: view
      +secure: true

Temporary tables​

Incremental table merges for Snowflake prefer to utilize a view rather than a temporary table. The reasoning is to avoid the database write step that a temporary table would initiate and save compile time.

However, some situations remain where a temporary table would achieve results faster or more safely. The tmp_relation_type configuration enables you to opt in to temporary tables for incremental builds. This is defined as part of the model configuration.

To guarantee accuracy, an incremental model using the delete+insert strategy with a unique_key defined requires a temporary table; trying to change this to a view will result in an error.

Defined in the project YAML:

dbt_project.yml
name: my_project

...

models:
  <resource-path>:
    +tmp_relation_type: table | view ## If not defined, view is the default.

In the configuration format for the model SQL file:

dbt_model.sql

{{ config(
    tmp_relation_type="table | view", ## If not defined, view is the default.
) }}

Dynamic tables​

The Snowflake adapter supports dynamic tables. This materialization is specific to Snowflake, which means that any model configuration that would normally come along for the ride from dbt-core (e.g. as with a view) may not be available for dynamic tables. This gap will decrease in future patches and versions. While this materialization is specific to Snowflake, it very much follows the implementation of materialized views. In particular, dynamic tables have access to the on_configuration_change setting. Dynamic tables are supported with the following configuration parameters:

Learn more about these parameters in Snowflake's docs:

Target lag​

Snowflake allows two configuration scenarios for scheduling automatic refreshes:

  • Time-based — Provide a value of the form <int> { seconds | minutes | hours | days }. For example, if the dynamic table needs to be updated every 30 minutes, use target_lag='30 minutes'.
  • Downstream — Applicable when the dynamic table is referenced by other dynamic tables. In this scenario, target_lag='downstream' allows for refreshes to be controlled at the target, instead of at each layer.

Learn more about target_lag in Snowflake's docs.

Limitations​

As with materialized views on most data platforms, there are limitations associated with dynamic tables. Some worth noting include:

  • Dynamic table SQL has a limited feature set.
  • Dynamic table SQL cannot be updated; the dynamic table must go through a --full-refresh (DROP/CREATE).
  • Dynamic tables cannot be downstream from: materialized views, external tables, streams.
  • Dynamic tables cannot reference a view that is downstream from another dynamic table.

Find more information about dynamic table limitations in Snowflake's docs.

For dbt limitations, these dbt features are not supported:

Source freshness known limitation​

Snowflake calculates source freshness using information from the LAST_ALTERED column, meaning it relies on a field updated whenever any object undergoes modification, not only data updates. No action must be taken, but analytics teams should note this caveat.

Per the Snowflake documentation:

The LAST_ALTERED column is updated when the following operations are performed on an object:

  • DDL operations.
  • DML operations (for tables only).
  • Background maintenance operations on metadata performed by Snowflake.
0
Edit this page