Course contents

The Analytics Engineer’s Field Guidedbt 1: build the modelsStep 1: Setup & structure

Setup & structure

Part 2 · dbt · step 1

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.

Compute: paid per second
Storage: paid per TB
Warehouses. Wake up to run a query, suspend when idle (idle means no query has run for a while, so it pauses itself and stops billing), resize for speed.
Databases → schemas → tables. Where the data lives, cheaply, always on.
A dbt run wakes a warehouse, builds your tables, then it can suspend.
The tables dbt builds land here, addressed as DB.SCHEMA.TABLE.
Words you'll meet
Later lessons use these freely. Come back here when one trips you up.
  • userland, pre_prod, pro: this team's three environments: your sandbox, the test copy CI builds before a merge, production (pro is 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, or pre_prod on 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 build into pre_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 .sql file holding one SELECT. 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 run builds models, dbt test checks them, dbt build does 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.

Key in the target
What it decides
account
Which Snowflake account to reach.
user + authenticator
Who logs in, and how they prove it.
role
What that login is allowed to read and write. The real safety boundary.
database + schema
Where the tables dbt builds land, the default DB.SCHEMA for the run.
warehouse
Which compute engine runs the SQL and spends the credits.
threads
How many models dbt builds at once, when they don't depend on each other. 4 is a sensible start.

A 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.

# ~/.dbt/profiles.yml
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_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.
Where is the pro target, then?
Not on any laptop. CI builds 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.

Alicea human, iterating
user accountSSO · externalbrowser · MFA
USERLANDher schema, her name
Airflowa machine, on schedule
service accountkey pair or PAT · no browser
PROD_DBthe platform's name
humans authenticate as themselves, machines as themselves · every query in history has an honest author

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.

Never do this
The fix is not to paste a password or a private key into 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('DBT_SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('DBT_SNOWFLAKE_USER') }}"
      private_key: "{{ env_var('DBT_ENV_SECRET_SNOWFLAKE_KEY') }}"   # injected, hidden in logs
      role: PRO_ROLE
      database: PROD_DB
      warehouse: PROD_WH
      schema: analytics

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/activate)
source dbt-env/bin/activate

# 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 --version

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 ~/.dbt/snowflake_key.p8
# public key: printed, to paste into Snowflake below
openssl rsa -in ~/.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_SUSPENDED = TRUE;
CREATE DATABASE IF NOT EXISTS USERLAND;
GRANT USAGE ON WAREHOUSE DEV_WH TO ROLE USERLAND_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_key.p8, or C:/Users/you/.dbt/snowflake_key.p8). 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.yml (top of the file)
name: 'my_project'
profile: 'my_project'    # the first line of profiles.yml
Reading dbt debug
"All checks passed!" means you're connected. Otherwise, it's usually one of three things. Profile not found: the 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
my_project/ ├─ models/your .sql models, grouped in folders │  ├─ staging/one model per source table, light cleaning │  ├─ intermediate/joins and business logic, never read by BI │  └─ marts/business-ready tables the BI tools read ├─ macros/reusable Jinja + SQL (step 5) ├─ tests/your own custom tests (step 7) ├─ seeds/small CSVs loaded as tables ├─ snapshots/row history for changing tables (step 3) ├─ analyses/one-off queries dbt compiles but never builds (step 6) ├─ packages.ymlthe packages you install (step 6) └─ dbt_project.ymlproject settings, folder defaults (step 4)

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.

Lesson quiz

  1. In Snowflake, which part do you pay for per second, and only while it runs?

    1. The role that runs the query
    2. The schema holding the tables
    3. The warehouse (compute)
    4. The database (storage)
  2. Alice's userland target uses authenticator: externalbrowser. Why can't the production run reuse that setting?

    1. SSO sessions expire after an hour, so long dbt production runs get cut off midway
    2. Production needs a password written into the committed profiles.yml
    3. externalbrowser only works with the USERLAND database, not with PROD_DB
    4. A scheduler has no browser to complete SSO, so a headless run hangs and fails
  3. Alice's userland target logs in with authenticator: externalbrowser. To save time, a teammate copies her profiles.yml onto the Airflow server and points the 06:00 run at it. What happens?

    1. It runs, since dbt falls back to the key pair in ~/.dbt
    2. It runs, signed in as Alice through her SSO session
    3. The headless run waits for a browser, then fails
    4. It prompts for Alice's password in the task log