We execute one SQL with cursor and extract variable to set some variables in Snowflake. How to do it efficiently in dbt? Can I set values of variables from the results of a model?
def func() -> None:
cursor = connection.cursor()
query = query_builder.prepare("""
SELECT
1 AS FIRST_VALUE,
2 AS SECOND_VALUE
FROM
my_table""")
cursor.execute(query)
if cursor.rowcount > 0:
rows = cursor.fetchone()
if rows[0] is not None:
variable.set("var1", rows[0])
else:
variable.set("var1", 0)
if rows[1] is not None:
variable.set("var2", rows[1])
else:
variable.set("var2", 0)
else:
variable.set("var1", 0)
variable.set("var2", 0)
I have done it with this and it is working.
{% set query %}
select * from {{ ref('set_variables') }}
{% endset %}
{% set results = run_query(query) %}
{% if execute %}
{% set last_processed_week = results.columns[0][0] %}
{% set current_process_week = results.columns[0][0] %}
{{ print("Variable li_last_processed_week => " ~ last_processed_week)}}
{{ print("Variable li_current_process_week => " ~ current_process_week)}}
{% endif %}