airflow.providers.snowflake.operators.snowflake

Module Contents

Classes

SnowflakeOperator

Executes SQL code in a Snowflake database

SnowflakeCheckOperator

Performs a check against Snowflake. The SnowflakeCheckOperator expects

SnowflakeValueCheckOperator

Performs a simple check using sql code against a specified value, within a

SnowflakeIntervalCheckOperator

Checks that the values of metrics given as SQL expressions are within

Functions

get_db_hook(self)

Create and return SnowflakeHook.
airflow.providers.snowflake.operators.snowflake.get_db_hook(self)[source]

Create and return SnowflakeHook.

Returns

a SnowflakeHook instance.

Return type

SnowflakeHook

class airflow.providers.snowflake.operators.snowflake.SnowflakeOperator(*, sql, snowflake_conn_id='snowflake_default', parameters=None, autocommit=True, do_xcom_push=True, warehouse=None, database=None, role=None, schema=None, authenticator=None, session_parameters=None, **kwargs)[source]

Bases: airflow.models.BaseOperator

Executes SQL code in a Snowflake database

See also

For more information on how to use this operator, take a look at the guide: SnowflakeOperator

Parameters

  • snowflake_conn_id (str) -- Reference to Snowflake connection id

  • sql (Any) -- the SQL code to be executed as a single string, or a list of str (sql statements), or a reference to a template file. Template references are recognized by str ending in '.sql'

  • autocommit (bool) -- if True, each command is automatically committed. (default value: True)

  • parameters (Optional[dict]) -- (optional) the parameters to render the SQL query with.

  • warehouse (Optional[str]) -- name of warehouse (will overwrite any warehouse defined in the connection's extra JSON)

  • database (Optional[str]) -- name of database (will overwrite database defined in connection)

  • schema (Optional[str]) -- name of schema (will overwrite schema defined in connection)

  • role (Optional[str]) -- name of role (will overwrite any role defined in connection's extra JSON)

  • authenticator (Optional[str]) -- authenticator for Snowflake. 'snowflake' (default) to use the internal Snowflake authenticator 'externalbrowser' to authenticate using your web browser and Okta, ADFS or any other SAML 2.0-compliant identify provider (IdP) that has been defined for your account 'https://<your_okta_account_name>.okta.com' to authenticate through native Okta.

  • session_parameters (Optional[dict]) -- You can set session-level parameters at the time you connect to Snowflake

template_fields :Sequence[str] = ['sql'][source]
template_ext :Sequence[str] = ['.sql'][source]
template_fields_renderers[source]
ui_color = #ededed[source]
get_db_hook(self)[source]
execute(self, context)[source]

Run query on snowflake

class airflow.providers.snowflake.operators.snowflake.SnowflakeCheckOperator(*, sql, snowflake_conn_id='snowflake_default', parameters=None, autocommit=True, do_xcom_push=True, warehouse=None, database=None, role=None, schema=None, authenticator=None, session_parameters=None, **kwargs)[source]

Bases: airflow.operators.sql.SQLCheckOperator

Performs a check against Snowflake. The SnowflakeCheckOperator 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 is failed and errors out.

Note that Python bool casting evals the following as False:

  • False

  • 0

  • Empty string ("")

  • Empty list ([])

  • Empty dictionary or set ({})

Given a query like SELECT COUNT(*) FROM foo, it will fail only if the count == 0. You can craft much more complex query that could, for instance, check that the table has the same number of rows as the source table upstream, or that the count of today's partition is greater than yesterday's partition, or that a set of metrics are less than 3 standard deviation for the 7 day average.

This operator can be used as a data quality check in your pipeline, and depending on where you put it in your DAG, you have the choice to stop the critical path, preventing from publishing dubious data, or on the side and receive email alerts without stopping the progress of the DAG.

Parameters

  • sql (Any) -- the SQL code to be executed as a single string, or a list of str (sql statements), or a reference to a template file. Template references are recognized by str ending in '.sql'

  • snowflake_conn_id (str) -- Reference to Snowflake connection id

  • autocommit (bool) -- if True, each command is automatically committed. (default value: True)

  • parameters (Optional[dict]) -- (optional) the parameters to render the SQL query with.

  • warehouse (Optional[str]) -- name of warehouse (will overwrite any warehouse defined in the connection's extra JSON)

  • database (Optional[str]) -- name of database (will overwrite database defined in connection)

  • schema (Optional[str]) -- name of schema (will overwrite schema defined in connection)

  • role (Optional[str]) -- name of role (will overwrite any role defined in connection's extra JSON)

  • authenticator (Optional[str]) -- authenticator for Snowflake. 'snowflake' (default) to use the internal Snowflake authenticator 'externalbrowser' to authenticate using your web browser and Okta, ADFS or any other SAML 2.0-compliant identify provider (IdP) that has been defined for your account 'https://<your_okta_account_name>.okta.com' to authenticate through native Okta.

  • session_parameters (Optional[dict]) -- You can set session-level parameters at the time you connect to Snowflake

template_fields :Sequence[str] = ['sql'][source]
template_ext :Sequence[str] = ['.sql'][source]
ui_color = #ededed[source]
get_db_hook(self)[source]

Get the database hook for the connection.

Returns

the database hook object.

Return type

DbApiHook

class airflow.providers.snowflake.operators.snowflake.SnowflakeValueCheckOperator(*, sql, pass_value, tolerance=None, snowflake_conn_id='snowflake_default', parameters=None, autocommit=True, do_xcom_push=True, warehouse=None, database=None, role=None, schema=None, authenticator=None, session_parameters=None, **kwargs)[source]

Bases: airflow.operators.sql.SQLValueCheckOperator

Performs a simple check using sql code against a specified value, within a certain level of tolerance.

Parameters

  • sql (str) -- the sql to be executed

  • pass_value (Any) -- the value to check against

  • tolerance (Any) -- (optional) the tolerance allowed to accept the query as passing

  • snowflake_conn_id (str) -- Reference to Snowflake connection id

  • autocommit (bool) -- if True, each command is automatically committed. (default value: True)

  • parameters (Optional[dict]) -- (optional) the parameters to render the SQL query with.

  • warehouse (Optional[str]) -- name of warehouse (will overwrite any warehouse defined in the connection's extra JSON)

  • database (Optional[str]) -- name of database (will overwrite database defined in connection)

  • schema (Optional[str]) -- name of schema (will overwrite schema defined in connection)

  • role (Optional[str]) -- name of role (will overwrite any role defined in connection's extra JSON)

  • authenticator (Optional[str]) -- authenticator for Snowflake. 'snowflake' (default) to use the internal Snowflake authenticator 'externalbrowser' to authenticate using your web browser and Okta, ADFS or any other SAML 2.0-compliant identify provider (IdP) that has been defined for your account 'https://<your_okta_account_name>.okta.com' to authenticate through native Okta.

  • session_parameters (Optional[dict]) -- You can set session-level parameters at the time you connect to Snowflake

get_db_hook(self)[source]

Get the database hook for the connection.

Returns

the database hook object.

Return type

DbApiHook

class airflow.providers.snowflake.operators.snowflake.SnowflakeIntervalCheckOperator(*, table, metrics_thresholds, date_filter_column='ds', days_back=- 7, snowflake_conn_id='snowflake_default', parameters=None, autocommit=True, do_xcom_push=True, warehouse=None, database=None, role=None, schema=None, authenticator=None, session_parameters=None, **kwargs)[source]

Bases: airflow.operators.sql.SQLIntervalCheckOperator

Checks that the values of metrics given as SQL expressions are within a certain tolerance of the ones from days_back before.

This method constructs a query like so

SELECT {metrics_threshold_dict_key} FROM {table}
WHERE {date_filter_column}=<date>
Parameters

  • table (str) -- the table name

  • days_back (SupportsAbs[int]) -- number of days between ds and the ds we want to check against. Defaults to 7 days

  • metrics_thresholds (dict) -- a dictionary of ratios indexed by metrics, for example 'COUNT(*)': 1.5 would require a 50 percent or less difference between the current day, and the prior days_back.

  • snowflake_conn_id (str) -- Reference to Snowflake connection id

  • autocommit (bool) -- if True, each command is automatically committed. (default value: True)

  • parameters (Optional[dict]) -- (optional) the parameters to render the SQL query with.

  • warehouse (Optional[str]) -- name of warehouse (will overwrite any warehouse defined in the connection's extra JSON)

  • database (Optional[str]) -- name of database (will overwrite database defined in connection)

  • schema (Optional[str]) -- name of schema (will overwrite schema defined in connection)

  • role (Optional[str]) -- name of role (will overwrite any role defined in connection's extra JSON)

  • authenticator (Optional[str]) -- authenticator for Snowflake. 'snowflake' (default) to use the internal Snowflake authenticator 'externalbrowser' to authenticate using your web browser and Okta, ADFS or any other SAML 2.0-compliant identify provider (IdP) that has been defined for your account 'https://<your_okta_account_name>.okta.com' to authenticate through native Okta.

  • session_parameters (Optional[dict]) -- You can set session-level parameters at the time you connect to Snowflake

get_db_hook(self)[source]

Get the database hook for the connection.

Returns

the database hook object.

Return type

DbApiHook

Was this entry helpful?