The problem I’m having
Like the titles, I’m using the macro to create the variables in dbt_project.yml, the macro using the run_query to query value from the Snowflake table and then return the _cdc_start_timestamps
dbt_project.yml
Vars:
_cdc_start_timestamp: |
{{ cdc_start_timestamp() | trim }}
cdc_timestamp.sql
{% macro cdc_start_timestamp() %}
{% if execute %}
{% if '_cdc_start_timestamp' in invocation_args_dict['vars'] %}
{{ invocation_args_dict['vars']['_cdc_start_timestamp'] }}
{% elif var('external_executor') == 'manual_cli' %}
1000-01-01 00:00:00
{% elif var('load_type') == 'FULL' %}
1000-01-01 00:00:00
{% elif var('load_type') == 'INCREMENTAL' %}
{% set last_row_query %}
SELECT CDC_END_TIMESTAMP, STATUS
FROM "{{ var('database') }}"."{{ var('schema') }}".load_history_table
WHERE LAYER_WINDOW_ID = '{{ var('layer_window_id') }}'
AND STATUS = 'SUCCESS'
ORDER BY LOAD_DATE DESC
LIMIT 1;
{% endset %}
{% set last_row_query = run_query(last_row_query) %}
{% if last_row_query|length > 0 %}
{% set last_row_end_value = last_row_query.columns[0].values()[0] %}
{% set last_row_end_value_str = last_row_end_value|string %}
{% set last_row_end_value_clean = last_row_end_value_str.strip() %}
{{ last_row_end_value_clean }}
{% else %}
1000-01-01 00:00:00 {# Fallback if there's no last row #}
{% endif %}
{% else %}
1000-01-01 00:00:00 {# Default value in case none of the conditions match #}
{% endif %}
{% endif %}
{% endmacro %}
dv_sat_optomate.sql
{{ config
(
materialized = 'vault_insert_by_period',
timestamp_field = 'load_date',
period = 'day',
tags=['optomate_2000_060'],
start_date= convert_to_date(var('_cdc_start_timestamp') | trim),
stop_date= convert_to_date(var('_cdc_end_timestamp') | trim)
)
}}
The _cdc_start_timestamp variables return None in the reading phase and return the right value in the run phase.
I tried to investigate and learned that we can not run the query in the reading phase, and dbt set the configuration for the model in this phase.
Is there any way to parse the value from Snowflake and then use that value in the model config?
I don’t want to use the approach that parses the value before the dbt and then uses that value as command-line variables