Beware of DBT Incremental Updates Against Snowflake External Tables

{{  config(    materialized='incremental'  )}}select  *,  my_slow_function(my_column)from raw_app_data.events{% if is_incremental() %}  -- this filter will only be applied on an incremental run  where event_time > (select max(event_time) from {{ this }}){% endif %}

Not all predicate expressions can be used to prune. For example, Snowflake does not prune micro-partitions based on a predicate with a subquery, even if the subquery results in a constant.

{{  config(  materialized='incremental'  )}}{% set query %}SELECT max(event_time) FROM {{ this }};{% endset %}{% set max_event_time = run_query(query).columns[0][0] %}select  *,  my_slow_function(my_column)from raw_app_data.events{% if is_incremental() %}  -- this filter will only be applied on an incremental run  where event_time > '{{ max_event_time }}'{% endif %}
# macros/get_max_event_time.sql{% macro get_max_event_time() %}{% if execute and is_incremental() and env_var('ENABLE_MAX_EVENT_TIME_MACRO', '1') == '1' %}{% set query %}SELECT max(event_time) FROM {{ this }};{% endset %}{% set max_event_time = run_query(query).columns[0][0] %}{% do return(max_event_time) %}{% endif %}{% endmacro %}
{{config(materialized='incremental')}}select*,my_slow_function(my_column)from raw_app_data.events{% if is_incremental() %}-- this filter will only be applied on an incremental runwhere event_time > '{{ get_max_event_time() }}'{% endif %}

Profiling

SELECT  count(*)FROM  the_external_table as tlogWHERE  collector_hour >= (    SELECT      max(collector_hour)    FROM      other_table);
$output = SELECT  max(collector_hour)FROM other_table;SELECT  count(*)FROM  the_external_table as tlogWHERE  collector_hour >= $output;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store