Pointing dbt at Snowflake (with Key-Pair Auth)

The dbt-snowflake adapter is one profile away — but in 2026 that profile has to authenticate with a key pair, not a password.

The dbt-snowflake adapter is genuinely boring to install and genuinely easy to misconfigure in exactly one way that most tutorials still get wrong. They hand you a profiles.yml with password: in it. That profile connects today and stops connecting some morning this autumn, because the account it uses is exactly the kind Snowflake is retiring password sign-in for.

So we’re doing this in the order that survives contact with 2026: key pair first, grants second, profile third. By the end of the post you’ll have a svc_dbt service user that logs in with an RSA key, a transformer role scoped to exactly what dbt touches, and a profiles.yml with real dev and prod outputs — plus a debugging section for the one error everyone hits first.

Why a password won’t do

Snowflake is deprecating single-factor password authentication for service accounts — the non-human users that tools like dbt log in as. The legacy password path is scheduled to be retired by October 2026, and Snowflake has been steadily turning it off for new accounts already. A dbt project that authenticates with a username and password is building on a floor that’s being removed.

The supported answer for a service account is key-pair authentication. You generate an RSA key pair, register the public key on a Snowflake user, and give dbt the private key. Nothing about the login is a shared secret that travels over the wire — dbt signs a short-lived JSON Web Token with the private key, Snowflake verifies the signature with the public one, and the private key never leaves your machine. Password auth still exists for now, but treat it the way you treat a deprecated API: only if you have no choice, and never in anything new.

Generate the key pair

Two commands with OpenSSL. Make a private key wrapped in a passphrase, then derive the public key from it:

openssl genrsa 2048 | \
  openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Three details in that first line earn their keep. genrsa 2048 is the key length Snowflake expects — 2048-bit RSA, not a smaller key and not an EC curve. pkcs8 -topk8 wraps it in PKCS#8, the container format the login flow reads; a bare PKCS#1 BEGIN RSA PRIVATE KEY block is not what Snowflake’s clients want, and feeding one in is a common first stumble. -v2 des3 encrypts the key so it’s useless on disk without the passphrase you’ll be prompted for.

You can skip the encryption and produce an unencrypted key with openssl genrsa 2048 | openssl pkcs8 -topk8 -nocrypt -out rsa_key.p8. Don’t, for anything a human might read — an unencrypted .p8 is a plaintext credential to your warehouse. The one place unencrypted keys are defensible is inside a secrets manager that already encrypts at rest and hands the raw bytes to the process at runtime; there the passphrase would just be a second secret to store next to the first. Everywhere else, keep the passphrase.

rsa_key.p8 is the private key dbt will use. rsa_key.pub is the public half you’ll paste into Snowflake. The private key never goes into git, never into a Slack thread, never into a DAG file.

Register the key on the user

In Snowsight, create a dedicated user for dbt — a service identity, not your personal login — and attach the public key. The key body is everything between the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines, with the line breaks stripped out:

CREATE USER svc_dbt
  DEFAULT_ROLE = transformer
  DEFAULT_WAREHOUSE = transforming
  TYPE = SERVICE;

ALTER USER svc_dbt SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...IDAQAB';

GRANT ROLE transformer TO USER svc_dbt;

TYPE = SERVICE tells Snowflake this is a non-human account, which excludes it from MFA-for-humans policies and makes the intent explicit in audits. The user has no password at all — the only way in is the key.

Confirm the key registered with DESC USER svc_dbt. The row that matters is RSA_PUBLIC_KEY_FP: the SHA-256 fingerprint of the key Snowflake now trusts, printed as SHA256: followed by a base64 hash. Write that value down — it’s the single most useful thing you have when authentication mysteriously fails later, because you can compute the same fingerprint from your local private key and compare.

One more thing to get right before you ever open profiles.yml: the account identifier. Snowflake’s current format is orgname-accountname — your organization name and account name joined by a hyphen, both findable under Admin → Accounts in Snowsight. The older locator-style identifier (xy12345.us-east-1) still resolves for some connections but is exactly the kind of mismatch that produces a baffling auth error, because the account identifier is baked into the JWT the client signs. Use orgname-accountname and you sidestep a whole category of failure.

What dbt actually needs granted

A dedicated transformer role matters more than it looks. It’s the boundary that decides what dbt is allowed to touch — the raw sample data to read from, and the working schemas to write into — and it keeps dbt’s blast radius separate from a human analyst’s or an ingest job’s. Grant it precisely what it needs and nothing else.

dbt needs four things: compute, read access to its sources, the right to build objects in its target, and — so the tables it produces are actually useful to anyone — a way to let downstream readers see its output.

-- 1. Compute: dbt hands SQL to this warehouse.
GRANT USAGE ON WAREHOUSE transforming TO ROLE transformer;

-- 2. Read the source. TPCH ships in a shared database;
--    shared databases are granted via IMPORTED PRIVILEGES.
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake_sample_data TO ROLE transformer;

-- 3. Write the output. dbt builds into ANALYTICS_DEV in development.
GRANT USAGE ON DATABASE analytics_dev TO ROLE transformer;
GRANT CREATE SCHEMA ON DATABASE analytics_dev TO ROLE transformer;

That CREATE SCHEMA grant is load-bearing and easy to miss. dbt doesn’t just write tables — it creates schemas, one per developer or environment, on the fly (dbt_dev, dbt_alice, a CI run’s throwaway schema). When the transformer role creates a schema it owns it, and ownership carries CREATE TABLE and CREATE VIEW automatically, so you don’t grant those separately in this model — dbt gets them by owning the schema it just made. What you do still need is the fourth thing:

-- 4. Future grants: anything dbt builds later becomes readable
--    by consumers without a manual grant per table.
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE analytics_dev TO ROLE reader;
GRANT SELECT ON FUTURE TABLES IN DATABASE analytics_dev TO ROLE reader;
GRANT SELECT ON FUTURE VIEWS IN DATABASE analytics_dev TO ROLE reader;

Without those FUTURE grants, every new mart dbt materializes is invisible to your BI tool until someone runs a GRANT SELECT by hand — which someone always forgets, so the dashboard is empty and nobody knows why. ON FUTURE says “any table or view that comes to exist in this database, grant reader SELECT on it automatically.” dbt builds it, reader can immediately query it, no human in the loop.

This is deliberately the minimum dbt needs — the compute, the source, the target, and the reader hand-off. It is not the whole stack’s security model. The full picture — a separate ingest role that loads raw data, the transform role you see here, a reader role for consumers, how future grants interact with schema ownership across all three, and how dev and prod databases divide — gets its own treatment in Environments and RBAC Across the Stack. For now: dbt can read TPCH, build into ANALYTICS_DEV, and hand its results to readers. That’s enough to connect.

The profile, with dev and prod

Now the part that’s identical to every other dbt project except for how it authenticates. In ~/.dbt/profiles.yml:

tpch_snowflake:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: svc_dbt
      role: transformer
      authenticator: snowflake_jwt
      private_key_path: "/secure/rsa_key.p8"
      private_key_passphrase: "{{ env_var('SNOWFLAKE_KEY_PASSPHRASE') }}"
      warehouse: transforming
      database: analytics_dev
      schema: dbt_dev
      threads: 8
      query_tag: dbt-dev
      client_session_keep_alive: false

    prod:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: svc_dbt
      role: transformer
      authenticator: snowflake_jwt
      private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
      private_key_passphrase: "{{ env_var('SNOWFLAKE_KEY_PASSPHRASE') }}"
      warehouse: transforming
      database: analytics_prod
      schema: analytics
      threads: 12
      query_tag: dbt-prod
      reuse_connections: true
      connect_retries: 2
      connect_timeout: 30
      session_parameters:
        STATEMENT_TIMEOUT_IN_SECONDS: 3600

The authenticator: snowflake_jwt line is what flips the adapter from password mode to key-pair mode — it tells dbt to sign a JWT with your private key instead of sending a secret. Miss it and dbt won’t attempt key-pair auth at all, even with a private_key_path sitting right there; it’s the switch, and the two private_key_* fields are what the switch reads.

target: dev at the top picks which output runs when you don’t say otherwise. Everyday dbt build runs against dev — your ANALYTICS_DEV database, your dbt_dev schema, safe to clobber. When Airflow or CI runs the pipeline for real it passes --target prod, and dbt swings over to ANALYTICS_PROD with production’s schema and thread count. Same models, same profile, one flag decides the blast radius. Keeping both outputs in one profile — rather than two profiles or two files — means there is exactly one description of “how dbt reaches Snowflake” and the only thing that varies is which environment.

The two outputs differ in exactly the ways that matter: database (analytics_dev vs analytics_prod), schema (a shared dev schema vs prod’s canonical analytics), threads (more parallelism in prod, where the warehouse is sized for it), and query_tag so you can tell dev noise from prod work in Snowflake’s query history at a glance. Everything else — the user, the role, the key — is shared, because a single service identity reaching two databases is simpler to reason about than two identities.

Three ways to hand dbt the key

The profile above uses private_key_path — a path to the .p8 file on disk, which is the right default for a laptop or a container that mounts a secret file. But the adapter accepts the key two other ways, and which you pick depends on where the key lives:

  • private_key_path — a filesystem path. Best when a secrets manager or Kubernetes mounts the key as a file, or on a developer machine.
  • private_key — the key material inline, as the PEM text (or a base64-encoded DER). Best when the key arrives as an environment variable rather than a file, e.g. private_key: "{{ env_var('SNOWFLAKE_PRIVATE_KEY') }}". This is the form CI systems reach for, since they inject secrets as env vars, not files. (The Airflow provider in the next post calls its inline equivalent private_key_content — same idea, different field name, so don’t be thrown when you see it there.)
  • A secrets backend — you never put the key in the file at all; env_var() pulls it from Vault, AWS Secrets Manager, or the CI secret store at runtime. Whichever field you use, wrap the value in env_var() and the profile stays committable while the key stays out of git.

In every form, if the key is encrypted — and it should be — private_key_passphrase supplies the passphrase, itself sourced from the environment. Notice what’s not in the file: no account identifier hardcoded, no passphrase, no key bytes. The profile is safe to commit; the secrets it references are not in it.

Tuning the connection

The knobs at the bottom of each output are worth understanding rather than copying:

threads is how many models dbt builds concurrently. It is not a Snowflake setting — it’s dbt opening that many sessions at once. The right number is bounded by your DAG’s width (there’s no point in 12 threads if only 4 models can run in parallel) and by the warehouse’s concurrency. A single Small warehouse comfortably absorbs 8 concurrent queries; push threads well past what the warehouse can run at once and queries queue rather than run, so you pay for the same wall-clock time with more moving parts. Start at 8 in dev, size prod to the warehouse, and watch Snowflake’s query history for queuing.

query_tag stamps every statement dbt issues with a label that shows up in QUERY_HISTORY. When the monthly bill spikes, WHERE query_tag = 'dbt-prod' is how you prove — or disprove — that dbt was the cause.

session_parameters sets Snowflake session settings for dbt’s connections. STATEMENT_TIMEOUT_IN_SECONDS: 3600 caps any single statement at an hour, so a runaway model with a bad join gets killed instead of burning credits until you notice. You can set any session parameter here.

reuse_connections (default true on current dbt-snowflake) lets dbt reuse an open connection across models on the same thread instead of tearing down and re-authenticating each time — which, with key-pair auth, means re-signing a JWT per model. On a wide DAG that’s real overhead saved. connect_retries and connect_timeout make the initial handshake resilient to a warehouse that’s resuming or a transient network blip: retry twice, wait 30 seconds before giving up.

client_session_keep_alive deserves a caution. Set true, it keeps dbt’s session token from expiring during a very long run — but it also keeps the session (and its keep-alive traffic) alive after dbt is done unless the process exits cleanly, which for a scheduled job can quietly hold sessions open. Leave it false unless you have a specific run that genuinely exceeds the four-hour session lifetime, which a dbt run almost never does.

Prove it connects:

dbt debug           # uses the default target: dev
dbt debug --target prod

dbt debug runs the whole connection check — it opens a session as svc_dbt, signs the JWT, confirms the warehouse, database, and schema resolve, and tells you exactly which line is wrong if one is. Run it against both targets before you trust either.

Rotating the key with zero downtime

Keys get rotated — on a schedule, or the day someone leaves, or the hour a laptop goes missing. The naive rotation is a self-inflicted outage: overwrite RSA_PUBLIC_KEY with the new public key, and every dbt run signing with the old private key fails instantly, until you’ve deployed the new private key everywhere. In a stack where dbt runs from Airflow on a schedule, “everywhere” and “instantly” don’t line up, and you get a red pipeline.

Snowflake gives each user two public-key slots for exactly this reason: RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2. Both are valid for authentication at the same time, which lets you roll forward without a gap:

-- 1. Register the NEW public key in the second slot.
--    The old key still works; nothing breaks yet.
ALTER USER svc_dbt SET RSA_PUBLIC_KEY_2 = 'MIIBIjANBg...NEWKEY...IDAQAB';

Now deploy the new private key to dbt (and Airflow) at your leisure and verify with dbt debug. Both keys authenticate, so a run using the old key and a run using the new key both succeed during the overlap. Once every consumer is on the new key:

-- 2. Promote the new key into the primary slot and drop the old one.
ALTER USER svc_dbt SET RSA_PUBLIC_KEY = 'MIIBIjANBg...NEWKEY...IDAQAB';
ALTER USER svc_dbt UNSET RSA_PUBLIC_KEY_2;

DESC USER svc_dbt shows both fingerprints — RSA_PUBLIC_KEY_FP and RSA_PUBLIC_KEY_2_FP — throughout, so you can confirm exactly which keys are live at each step. No window where authentication is impossible, which is the whole point.

When dbt debug says the token is invalid

The error you will meet, probably on your first attempt, reads roughly:

Database Error
  250001 (08001): Failed to connect to DB:
  <account>.snowflakecomputing.com:443.
  JWT token is invalid.

JWT token is invalid is Snowflake refusing the signed token, and it has three usual causes. Work them in this order.

1. The account identifier is wrong. The account is part of what the JWT is built from, so a mismatched identifier produces an invalid token even when the key is perfect. Confirm SNOWFLAKE_ACCOUNT is the orgname-accountname form from Snowsight, with a hyphen — not the legacy xy12345.us-east-1 locator, not the full ...snowflakecomputing.com URL. This is the single most common cause, and the error message is unhelpfully silent about it.

2. The clocks disagree. The JWT carries an issued-at and an expiry a minute or two apart. If the machine running dbt has drifted more than about a minute from real time, Snowflake sees a token that’s expired or not-yet-valid and rejects it. Containers with a paused or unsynced clock are the usual culprit. Fix: make sure NTP is running (timedatectl on Linux) and the clock is accurate.

3. The fingerprints don’t match. The private key dbt is signing with doesn’t correspond to the public key on the user — because you rotated one but not the other, pointed private_key_path at the wrong file, or the passphrase is decrypting the key wrong. Prove it directly. Compute the fingerprint of your local key:

openssl rsa -in rsa_key.p8 -pubout -outform DER | \
  openssl dgst -sha256 -binary | \
  openssl enc -base64

Prefix the result with SHA256: and compare it to RSA_PUBLIC_KEY_FP from DESC USER svc_dbt. If they don’t match character for character, the key on the user isn’t the key dbt is using — re-register the right public key, or point dbt at the right .p8.

Two failures that look like auth errors but aren’t: a wrong private_key_passphrase surfaces as a decryption error before any JWT is even built, and a missing warehouse grant surfaces after a successful login as a permissions error on the first query. dbt debug distinguishes these — it reports the connection open separately from the schema/warehouse checks — so read which line it flags rather than assuming every red line is the key.

A first model

With dbt debug green, point a small model at the sample data to confirm the round trip end to end. In models/staging/stg_orders.sql:

select
    o_orderkey    as order_id,
    o_custkey     as customer_id,
    o_orderstatus as status,
    o_totalprice  as total_price,
    o_orderdate   as order_date
from snowflake_sample_data.tpch_sf1.orders

Then build it:

dbt build --select stg_orders

dbt compiles that into a create ... as select in your dbt_dev schema, Snowflake runs it on the transforming warehouse, and any tests you’ve attached run right after. Look in QUERY_HISTORY and you’ll see the statement tagged dbt-dev, run by SVC_DBT, on the TRANSFORMING warehouse — the whole chain you just wired, visible in one row. You now have a real table, built by dbt, materialized in Snowflake, authenticated with a key.

The mental model

Here’s the reassuring part: nothing you learned about dbt changed. ref() still stitches models into a DAG. Materializations still decide table versus view versus incremental. Tests, sources, docs — all identical. The things that moved are the profile (which warehouse dbt hands its SQL to) and the login (a signed token instead of a password). Swap type: bigquery for type: snowflake and the same project runs against a different engine.

That’s the whole trick to the modern data stack. dbt is warehouse-agnostic by design, so “putting dbt on Snowflake” is a configuration task, not a rewrite. The interesting work stays in the models. The profile just tells them where to run, and the key just proves who’s asking.

Final thoughts

Key-pair auth isn’t a security-team nicety you’ll get to later — it’s the default that keeps your pipeline connecting past October. Set it up on day one and it’s ten minutes of OpenSSL, one ALTER USER, and one authenticator: snowflake_jwt line. Retrofit it after a password user gets disabled mid-run, and it’s an incident. The good news is that once the key is registered and the transformer role is scoped, the profile is the same tidy config it’s always been, both environments live in one file, and everything you know about dbt still applies. Next we give Airflow the same key.

Next: Connecting Airflow to Snowflake

Comments