SQLExecuteQueryOperator for Snowflake¶
Use the SQLExecuteQueryOperator to execute
SQL commands in a Snowflake database.
Using the Operator¶
Use the conn_id argument to connect to your Snowflake instance where
the connection metadata is structured as follows:
Parameter |
Input |
|---|---|
Login: string |
Snowflake user name |
Password: string |
Password for Snowflake user |
Schema: string |
Set schema to execute SQL operations on by default |
Extra: dictionary |
|
An example usage of the SQLExecuteQueryOperator to connect to Snowflake is as follows:
snowflake_op_sql_str = SQLExecuteQueryOperator(
task_id="snowflake_op_sql_str", sql=CREATE_TABLE_SQL_STRING
)
snowflake_op_with_params = SQLExecuteQueryOperator(
task_id="snowflake_op_with_params",
sql=SQL_INSERT_STATEMENT,
parameters={"id": 56},
)
snowflake_op_sql_list = SQLExecuteQueryOperator(task_id="snowflake_op_sql_list", sql=SQL_LIST)
snowflake_op_sql_multiple_stmts = SQLExecuteQueryOperator(
task_id="snowflake_op_sql_multiple_stmts",
sql=SQL_MULTIPLE_STMTS,
split_statements=True,
)
snowflake_op_template_file = SQLExecuteQueryOperator(
task_id="snowflake_op_template_file",
sql="example_snowflake_snowflake_op_template_file.sql",
)
# Create and populate a small dataset for the data quality operator examples.
# We insert one row for `ds` and one for `ds - 1`, each with value = 4.
create_check_table = SQLExecuteQueryOperator(
task_id="create_check_table",
sql=CREATE_CHECK_TABLE_SQL_STRING,
)
populate_check_table = SQLExecuteQueryOperator(
task_id="populate_check_table",
sql=SQL_CHECK_TABLE_INSERT,
)
Note
Parameters that can be passed onto the operator will be given priority over the parameters already given
in the Airflow connection metadata (such as schema, role, database and so forth).
SnowflakeCheckOperator¶
To perform checks against Snowflake you can use
SnowflakeCheckOperator
This operator expects a SQL query that will return a single row. Each value on
that first row is evaluated using Python bool casting. If any of the values
return False the check fails and errors out.
snowflake_check = SnowflakeCheckOperator(
task_id="snowflake_check",
sql=f"SELECT COUNT(*) FROM {SNOWFLAKE_CHECK_TABLE} WHERE ds = TO_DATE('{{{{ ds }}}}')",
)
SnowflakeValueCheckOperator¶
To perform a simple value check using SQL code you can use
SnowflakeValueCheckOperator
This operator expects a SQL query that will return a single row. That value is
evaluated against pass_value, which can be either a string or numeric value.
If numeric, you can also specify tolerance.
snowflake_value_check = SnowflakeValueCheckOperator(
task_id="snowflake_value_check",
sql=f"SELECT SUM(value) FROM {SNOWFLAKE_CHECK_TABLE} WHERE ds = TO_DATE('{{{{ ds }}}}')",
pass_value=4,
)
SnowflakeIntervalCheckOperator¶
To check that the values of metrics given as SQL expressions are within a certain
tolerance of the ones from days_back before you can use
SnowflakeIntervalCheckOperator
snowflake_interval_check = SnowflakeIntervalCheckOperator(
task_id="snowflake_interval_check",
table=SNOWFLAKE_CHECK_TABLE,
metrics_thresholds={"COUNT(*)": 1.5},
days_back=1,
)
SnowflakeSqlApiOperator¶
Use the SnowflakeSqlApiHook to execute
SQL commands in a Snowflake database.
You can also run this operator in deferrable mode by setting deferrable param to True.
This will ensure that the task is deferred from the Airflow worker slot and polling for the task status happens on the trigger.
Using the Operator¶
Use the snowflake_conn_id argument to connect to your Snowflake instance where
the connection metadata is structured as follows:
Parameter |
Input |
|---|---|
Login: string |
Snowflake user name. If using OAuth connection this is the |
Password: string |
Password for Snowflake user. If using OAuth this is the |
Schema: string |
Set schema to execute SQL operations on by default |
Extra: dictionary |
|
An example usage of the SnowflakeSqlApiHook is as follows:
snowflake_sql_api_op_sql_multiple_stmt = SnowflakeSqlApiOperator(
task_id="snowflake_op_sql_multiple_stmt",
sql=SQL_MULTIPLE_STMTS,
statement_count=len(SQL_LIST),
)
Note
Parameters that can be passed onto the operator will be given priority over the parameters already given
in the Airflow connection metadata (such as schema, role, database and so forth).
Durable execution¶
SnowflakeSqlApiOperator submits one or more SQL statements and then polls their statement
handles to completion on the worker. By default the operator runs in a durable mode that makes
this crash-safe: the statement handles are persisted to task state store before polling begins, so if the worker crashes
or is preempted and the task is retried, the operator reconnects to the statements that are
already executing in Snowflake instead of resubmitting the SQL.
On retry the operator checks the prior statements’ state:
if any handle is still running, the operator reconnects and continues polling – handles that already finished are not re-run, only the ones still in progress are waited on
if every handle already succeeded, the operator returns immediately without resubmitting
if any handle failed, or a handle has expired past Snowflake’s retention window, the operator submits the SQL fresh
Because Snowflake’s SQL API has no way to retry or repair a single failed statement within a multi-statement request, a genuine failure always resubmits the whole request batch, matching the operator’s all-or-nothing submission semantics.
Durable execution requires Airflow 3.3 or newer, since it relies on the task state store. On earlier Airflow versions the flag is a no-op and the operator always submits fresh SQL on retry, exactly as before. If the task state store is unavailable at runtime, the operator logs that crash recovery is disabled and behaves the same way.
Like the persisted state itself, the stored statement handles aren’t deleted automatically, that
only happens when someone runs airflow state-store clean. If a task’s retry_delay is
longer than [state_store] default_retention_days (30 days by default) and cleanup runs in
between, the handles won’t be there for the next retry, and the operator will submit the SQL
fresh instead of reconnecting. Avoid running cleanup on a schedule shorter than your longest
retry_delay.
Clearing a task instance from the UI or the REST API (so airflowctl too) deletes the stored
statement handles by default from Airflow 3.4. Every other clear, such as airflow tasks clear or
clearing a whole Dag run, does so from Airflow 3.5. To keep it, tick Keep task state and resume
in the Clear dialog, or send "keep_task_state": true in the request. The
Keep task state on clear UI setting ticks that box by default. From Airflow 3.5,
airflow tasks clear --keep-task-state keeps it too. The next attempt then reads the stored
statement handles back. In synchronous mode, what the next attempt does depends on the state of the
statements at that point:
Statement state |
Default clear |
Clear that keeps task state |
|---|---|---|
Still running (the task had already failed, or its worker died) |
Submits the SQL again. The first statements keep running. |
Reconnects to the running statements. |
Succeeded |
Submits the SQL again. |
Returns right away without running the SQL again. |
Failed or aborted |
Submits the SQL again. |
Submits the SQL again. |
With deferrable=True, the operator does not store the statement handles, so the next attempt
submits the SQL again whether or not task state was kept.
With [state_store] clear_on_success on, the stored statement handles are deleted when the task
succeeds, so clearing a succeeded task submits again even when task state is kept.
Keep task state when statements are still running and nothing about the code or inputs changed, so
you don’t pay for two. This assumes the statements survive the clear; keeping task state does not
prevent cancellation via on_kill. See Resumable Tasks
for the ways of clearing.
By default (cancel_on_kill=True), clearing a running task requests cancellation via
on_kill, including when deferred (Airflow 3.3+). With cancel_on_kill=False, the statements
are left running; a default clear submits the SQL again on the next synchronous attempt.
To opt out and always submit fresh SQL on retry, set durable=False:
api_operator = SnowflakeSqlApiOperator(
task_id="snowflake_sql_api",
snowflake_conn_id="snowflake_default",
sql="select * from table",
statement_count=1,
durable=False,
)
Durable execution applies to the synchronous path. When deferrable=True is set, the Triggerer
already tracks the statement handles across the wait, so deferrable mode takes precedence and
durable has no effect.
Durable execution requires Airflow 3.3 or newer, since it relies on the task state store. Below
3.3, durable has no effect either way: setting it explicitly only emits a warning, and the
operator always submits fresh SQL on retry, exactly as before this feature existed.
SnowflakeNotebookOperator¶
Use the SnowflakeNotebookOperator
to execute a Snowflake Notebook
via the Snowflake SQL API.
This operator builds an EXECUTE NOTEBOOK statement and delegates execution to
SnowflakeSqlApiOperator.
Using the Operator¶
execute_notebook = SnowflakeNotebookOperator(
task_id="execute_notebook",
notebook=SNOWFLAKE_NOTEBOOK,
snowflake_conn_id=SNOWFLAKE_CONN_ID,
)
You can pass parameters to the notebook:
execute_notebook_with_params = SnowflakeNotebookOperator(
task_id="execute_notebook_with_params",
notebook=SNOWFLAKE_NOTEBOOK,
snowflake_conn_id=SNOWFLAKE_CONN_ID,
notebook_parameters=["param1", "target_db=PROD"],
)
You can also run the operator in deferrable mode:
execute_notebook_deferrable = SnowflakeNotebookOperator(
task_id="execute_notebook_deferrable",
notebook=SNOWFLAKE_NOTEBOOK,
snowflake_conn_id=SNOWFLAKE_CONN_ID,
deferrable=True,
)