Get set up: Snowflake, connect, structure
Alice's first hour: understand where the data lives, point dbt at it, and learn the project layout.
What is Snowflake, quickly
Snowflake is a cloud data warehouse sold as SaaS (software as a service): you use it in a browser, with nothing to install. It runs on AWS, Azure or GCP, but you never see that layer.
The one idea to know: storage and compute are separate. Data sits in storage, cheaply. A query runs on a virtual warehouse, which holds no data despite its name: it's the compute engine, billed per second while it runs. The bill is in credits, roughly $2 to $4 each: the smallest warehouse, X-Small, uses 1 credit per hour.
You can follow this whole part on a free 30-day trial. Its $400 of usage buys over 100 X-Small hours, far more than you'll need. Any cloud and region works: pick the nearest.
The naming goes database → schema → table. A table's full address is MY_DB.MY_SCHEMA.MY_TABLE. dbt creates tables for you inside a database and schema you choose.
dbt run wakes a warehouse, builds your tables, then it can suspend.DB.SCHEMA.TABLE.- userland, pre_prod, pro: this team's three environments: your sandbox, the test copy CI builds before a merge, production (
prois just their spelling). Each has a dbt target and a database of its own. Team names, not dbt words. - PR / merge request: asking to merge your Git branch into a shared branch (
main, orpre_prodon this team), after a teammate's review. Same thing on GitHub and GitLab. - CI (continuous integration): a robot that checks every PR. Here it runs
dbt buildintopre_prod. - SSO: logging in through your company's login page. MFA: a second proof, like a code on your phone.
- Key pair: a secret key file you keep, plus a public key saved on your Snowflake user. PAT: a long token used like a password. Both let programs log in without a prompt.
- DDL: SQL that creates or changes objects (
CREATE,ALTER,DROP). dbt writes it for you. - Model: one
.sqlfile holding oneSELECT. DAG: the graph of which model feeds which. - Jinja: dbt's template language.
{{ }}prints a value,{% %}runs logic,{# #}is a comment. Steps 2 and 5. ref()points at another model,source()at a raw table. Step 2.dbt runbuilds models,dbt testchecks them,dbt builddoes all of it (snapshots and seeds too), in DAG order.
Connect from your computer: profiles.yml
dbt normally reads the connection from ~/.dbt/profiles.yml (on Windows, C:\Users\<you>\.dbt\profiles.yml): on your machine, out of the repo. dbt init writes it for you (below), or you write it by hand.
The key idea in that file is the target: a named set of connection settings that answers where does this run land, and as who? Your models answer what gets built. That split is why one repo builds into Alice's sandbox on Monday and into production at 6am with no SQL change.
accountuser + authenticatorroledatabase + schemaDB.SCHEMA for the run.warehousethreadsA profile can hold several targets, and the target: line picks the default. dbt run uses that one, dbt run --target <name> overrides it for one command. Alice's laptop profile has exactly one target, userland, her personal sandbox.
# ~/. my_project: # the profile name target: userland outputs: userland: # Alice's own sandbox type: snowflake account: myorg-myaccount # yours: see the trial steps below user: alice authenticator: externalbrowser # SSO, no password in the file role: USERLAND_dbt/ profiles. yml ROLE database: USERLAND warehouse: DEV_WH schema: dbt_alice threads: 4 # and that's the whole file. no pro target here, on purpose: # production's profile lives in Airflow (part 3), not on any laptop.
pre_prod and Airflow runs pro, each with its own profile (below, and part 3). If Alice ever must touch production by hand, that's an audited emergency procedure agreed with the platform team, not a target in her profiles.yml.Two kinds of identity: user accounts and service accounts
A target says where a run lands. It also says who is running it, and there are only two kinds of who.
A user account is a person. Alice logs in through SSO with MFA, and every query she runs carries her name in Snowflake's query history. That's the userland target.
A service account is a machine. Airflow can't click a login page, so it logs in with a key pair, as a user created with TYPE = SERVICE (no password allowed). Its queries carry the platform's name. The platform team sets it up once, so there's nothing for you to do here.
The auth trap: what works locally breaks in production
Look again at the userland target. authenticator: externalbrowser opens a browser tab, SSO confirms it's Alice, dbt carries on. Ideal on a laptop, impossible on a scheduler: a server has no browser, so a headless dbt run (no screen, no human) hangs and then fails.
profiles.yml and commit it. That file, with a real secret in it, in Git, is the most common way analytics platforms get breached. Secrets come from the environment, never from the repo.So production gets its own profile, on the Airflow side. It logs in with a key pair and reads its settings from environment variables Airflow sets at run time. {{ env_var('NAME') }} is Jinja: dbt swaps in that variable's value. dbt hides the value of any variable prefixed DBT_ENV_SECRET_ in its logs:
# the production profile, written on the Airflow side # (never in ~/.dbt on a laptop, never committed with a secret in it) my_project: target: pro outputs: pro: # headless: key pair, no browser type: snowflake account: "{{ env_var( user: "{{ env_'DBT_ SNOWFLAKE_ ACCOUNT') }}" var( private_key: "{{ env_'DBT_ SNOWFLAKE_ USER') }}" var( # injected, hidden in logs role: PRO_ROLE database: PROD_DB warehouse: PROD_WH schema: analytics'DBT_ ENV_ SECRET_ SNOWFLAKE_ KEY') }}"
So it's one profile name in two files, one per machine: userland on Alice's laptop, pro on the Airflow side. Airflow points dbt at its file with --profiles-dir (by default dbt looks in the current folder, then ~/.dbt). pro is that file's default, so --target pro only makes it explicit. In part 3, Cosmos (which runs dbt inside Airflow) builds this profile from an Airflow connection, so even this file goes away.
Set it up on your computerInstall dbt, log in to a Snowflake trial with a key pair, and start a project. On a phone, skip it: the rest of the course reads fine without it.
Install dbt for Snowflake
You need Python 3.10, 3.11 or 3.12 (python3 --version). Install dbt in a virtual environment: a private folder of Python packages, so dbt can't clash with anything else on your machine.
# once, from your home folder python3 -m venv dbt-env # in every new terminal (Windows Git Bash: source dbt-env/Scripts/ source dbt-env/activate) bin/ # dbt Core (the free command-line dbt) plus the Snowflake adapter python -m pip install dbt-core dbt-snowflake # check: Core 1.10 or newer, and a snowflake plugin line dbt --versionactivate
Recent versions may print a note that a newer engine, dbt v2, exists. This guide teaches dbt Core v1 (1.10 and later), which is exactly what the command above installs, so you can ignore that note.
Following along on a trial account
At work, the platform team creates the role, database and warehouse, and SSO logs you in. A trial only gives you the ACCOUNTADMIN role and a COMPUTE_WH warehouse. So:
- account: in Snowsight (the web UI), click your name at the bottom left, then your account, then View account details. From the Account/Server URL, keep the part before
.snowflakecomputing.com. - user: your sign-up login name.
- Log in with a key pair, since there's no SSO. Make one in a terminal (on Windows, Git Bash has
openssl):
mkdir -p ~/.dbt # private key: stays in ~/.dbt, never in the repo openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -nocrypt -out ~/. # public key: printed, to paste into Snowflake below openssl rsa -in ~/dbt/ snowflake_ key. p8 . dbt/ snowflake_ key. p8 -pubout
- Create what the profile names. Run this once in a worksheet (Snowsight's SQL editor):
USE ROLE ACCOUNTADMIN; CREATE ROLE IF NOT EXISTS USERLAND_ROLE; CREATE WAREHOUSE IF NOT EXISTS DEV_WH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 INITIALLY_ CREATE DATABASE IF NOT EXISTS USERLAND; GRANT USAGE ON WAREHOUSE DEV_WH TO ROLE USERLAND_SUSPENDED = TRUE; ROLE; GRANT USAGE, CREATE SCHEMA ON DATABASE USERLAND TO ROLE USERLAND_ROLE; GRANT ROLE USERLAND_ROLE TO USER your_login; -- the key printed above: the lines between BEGIN and END, joined into one ALTER USER your_login SET RSA_PUBLIC_ KEY = 'MIIBIjANBgkqh. . ;. '
Last, in the profile, replace the authenticator line with private_key_path: and the key's full path (like /Users/you/.dbt/snowflake_, or C:/Users/you/.dbt/snowflake_). dbt creates the dbt_alice schema itself on the first run.
Start a project
This guide assumes a Git repository: reviews and CI stand on it. With the virtual environment active:
# scaffold a new project and put it under Git # (wrote profiles.yml by hand? add --skip-profile-setup) dbt init my_project cd my_project git init # test the connection, from inside the project folder dbt debug
dbt init asks for the adapter (snowflake), then the profile values (on a trial, pick keypair to log in), and writes ~/.dbt/profiles.yml. It asks before overwriting one. It also writes dbt_project.yml, the project's settings, whose profile: line must match the profile's name:
# dbt_project. name: 'my_project' profile: 'my_project' # the first line of profiles.ymlyml (top of the file)
profile: line and profiles.yml disagree. Login refused: check account, user and the key. Role or warehouse refused: a name in the profile doesn't match what the setup SQL created.What dbt init created: the project structure
You'll spend almost all your time in models/ and dbt_project.yml. Delete the demo folder models/example/ and the example: lines at the bottom of dbt_project.yml, then add the three layer folders:
mkdir models/staging models/intermediate models/marts No profiles.yml in there, on purpose: the connection stays in ~/.dbt. The .gitignore that dbt init adds also keeps target/ (dbt's compiled output), dbt_packages/ (downloaded packages) and logs/ out of Git.
The three sub-folders of models/ are not cosmetic: they're the layers every model has to belong to. Step 3 is entirely about them.

