dbt, Airflow, and production | Snowflake course

Picture it this way

dbt is the cook who follows written recipes. Snowflake is the kitchen. Airflow is the head cook who decides what time dinner starts and what to do if a dish burns. CI is cooking the new recipes in a spare kitchen that was cloned from the real one, so a bad recipe does not land on tonight's plates. Observability is the receipt: which warehouse ran, which query failed, and which role read the table.

dbt Core against Snowflake

Install dbt-snowflake in the same environment as dbt Core. The profile type is snowflake. Put the account identifier, user, role, database, warehouse, and schema in the profile, and pull secrets with env_var. dbt's Snowflake setup page says password authentication for this adapter is not supported starting 31 August 2026, with a remaining exception it documents for LEGACY_SERVICE users. Use key-pair or SSO. The adapter field may be private_key_path or an inline private_key depending on the version you installed. Read the page for the version you have, and do not commit the key.

Give transformations their own warehouse, with auto-suspend, and do not point that profile at the warehouse analysts use for ad hoc queries. Threads in the profile are dbt's parallelism. They queue on the warehouse if the warehouse is small. Raising threads without watching the warehouse queue does not make a small warehouse larger.

The dbt installation lesson and the deployment lesson cover adapters and targets. This lesson is the Snowflake side of that work.

learn_shop:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
      role: TRANSFORMER
      database: LEARN_DB
      warehouse: TRANSFORM_WH
      schema: dev_analytics
      threads: 4

CI with a zero-copy clone

For a pull request, clone the production database, point a dbt target at the clone, and run dbt build there. The clone is cheap until models write new partitions. Drop the clone when the check finishes so Time Travel on the clone does not linger. If a child table's retention is shorter than the timestamp you clone, add IGNORE TABLES WITH INSUFFICIENT DATA RETENTION or clone the current state instead of an old timestamp.

Slim CI, using dbt state and state:modified+, builds what changed and what is downstream of it. It still needs a database to run in. The clone is that database. It does not replace tests.

Airflow, then the receipt

Airflow should run dbt build after loads have landed. A Bash task or a container task is enough. Cosmos can map each dbt model to an Airflow task. Treat Cosmos operator names as something you check in the provider you installed. This course does not pin a Cosmos version. The Airflow production lesson is the place for schedulers, executors, and metadata databases.

If you would rather keep the transform inside Snowflake, a dynamic table with SCHEDULER = DISABLE lets the DAG call the refresh. Do not also set TARGET_LAG in that mode.

For a query that just failed, open Snowsight Query History and the query profile. For yesterday's spend, query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY and warehouse metering, and remember Account Usage lags. INFORMATION_SCHEMA.QUERY_HISTORY() is the nearer window. Add a resource monitor on TRANSFORM_WH. On Enterprise accounts, ACCESS_HISTORY is the column-level audit. dbt tests and source freshness are the application-level checks. None of these replace the others.

SELECT query_text, execution_status, warehouse_name, total_elapsed_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE warehouse_name = 'TRANSFORM_WH'
ORDER BY start_time DESC
LIMIT 20;

A change on its way to production

Test on a free copy, run on a schedule, and keep the receipt.

Pull requestA dbt model change
CI cloneZero-copy copy of production
Airflow + dbt buildScheduled production run
Query historyWhat ran, how long, what it cost

Step 1 of 3Each pull request runs dbt against a zero-copy clone of production, so tests use real-shaped data without any risk to it.

CREATE DATABASE ci_pr_42 CLONE analytics;

Pull requestA dbt model change
CI cloneZero-copy copy of production
Airflow + dbt buildScheduled production run
Query historyWhat ran, how long, what it cost

Step 2 of 3After merging, an Airflow DAG starts dbt build on a schedule. dbt runs models and their tests in dependency order.

dbt build --target prod

Pull requestA dbt model change
CI cloneZero-copy copy of production
Airflow + dbt buildScheduled production run
Query historyWhat ran, how long, what it cost

Step 3 of 3Query history shows each statement, its warehouse, its run time and the data it scanned: the receipt for a failed or expensive run.

SELECT query_text, total_elapsed_time
FROM snowflake.account_usage.query_history
WHERE query_tag = 'dbt';

Lab: a transform warehouse and a clone you can drop

Create a warehouse that sleeps, clone LEARN_DB, and read recent queries. Dropping the clone removes the extra name. Storage from the clone's later changes leaves through Time Travel and, for permanent objects, Fail-safe. Skip the clone if you are out of trial balance.

CREATE WAREHOUSE IF NOT EXISTS TRANSFORM_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'dbt and Airflow only';

CREATE OR REPLACE DATABASE LEARN_CI CLONE LEARN_DB;
SHOW SCHEMAS IN DATABASE LEARN_CI;
DROP DATABASE LEARN_CI;

SELECT query_text, execution_status, warehouse_name
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
ORDER BY start_time DESC
LIMIT 10;

Concept summary

dbt-snowflake profiles name a role, a database, and a warehouse that can suspend.
A zero-copy clone is the CI database. Drop it when the check is done.
Airflow orders the run. Dynamic tables with a disabled scheduler are the in-platform alternative.
Query profile for one statement. Information schema for recent history. Account Usage for the later audit.

Knowledge check

Why give dbt its own warehouse?

What makes a clone a practical CI database?

Which history should you open for a query that failed a minute ago?

Official sources

Checked against Snowflake documentation in October 2026. Product behavior still depends on your edition, cloud, and region.