SNOWFLAKE

A COMPLETE PROFILE

Here is a complete, working output using password auth — the most common setup. Copy it, change the values, and dvt debug --all will tell you the moment it connects:

# ~/.dbt/profiles.yml
my_project:
  target: snowflake
  outputs:
    snowflake:
      type: snowflake
      account: xy12345.us-east-1        # include the region — see below
      user: DVT_USER
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      database: ANALYTICS
      schema: PUBLIC
      warehouse: COMPUTE_WH             # required — not optional in DVT
      role: TRANSFORMER                 # optional
      threads: 4

account is the part of your Snowflake URL before .snowflakecomputing.com. For locator-style accounts the region rides along (xy12345.us-east-1); org-style identifiers (myorg-myaccount) are already complete as they are.

KEY-PAIR AUTHENTICATION

For service users and CI, Snowflake key-pair auth swaps the password for a PEM private key. Point private_key_path at the key file:

    snowflake:
      type: snowflake
      account: xy12345.us-east-1
      user: DVT_SERVICE
      private_key_path: ~/.ssh/snowflake_rsa_key.p8
      private_key_passphrase: "{{ env_var('SF_KEY_PASSPHRASE') }}"  # only if the key is encrypted
      database: ANALYTICS
      schema: PUBLIC
      warehouse: COMPUTE_WH
      role: TRANSFORMER

The path expands ~and is checked before any connection is attempted — a missing key file is a named error, not a hanging login. On machines where a key file can't live on disk, put the PEM itself inline as private_key instead (an environment variable works well here too).

SSO THROUGH YOUR BROWSER

If your Snowflake account signs in through an identity provider, set authenticator: externalbrowser and drop the password entirely. The first connection opens your browser, you sign in as usual, and DVT carries on:

    snowflake:
      type: snowflake
      account: xy12345.us-east-1
      user: you@company.com
      authenticator: externalbrowser
      database: ANALYTICS
      schema: PUBLIC
      warehouse: COMPUTE_WH

This is the human-at-a-laptop option — a browser has to open, so it doesn't suit CI. For unattended machines, use key-pair auth above or an OAuth token below.

OAUTH ACCESS TOKEN

With authenticator: oauth, DVT presents an OAuth access token you obtained from your identity provider. The token is required — leave it out and DVT refuses by name (authenticator 'oauth' requires token):

    snowflake:
      type: snowflake
      account: xy12345.us-east-1
      user: DVT_USER
      authenticator: oauth
      token: "{{ env_var('SNOWFLAKE_OAUTH_TOKEN') }}"
      database: ANALYTICS
      schema: PUBLIC
      warehouse: COMPUTE_WH

Set more than one credential and DVT picks deterministically: key pair first, then authenticator, then password.

THE WAREHOUSE IS REQUIRED

dbt can limp along without a warehouse if your user has a default one server-side. DVT can't — federation runs real extraction queries, and those need compute attached up front. A profile without warehouse is refused immediately:

snowflake: warehouse is required for Sling extraction

Name the warehouse in the profile and the error never appears. An X-Small is plenty to start; federation extraction is mostly reads.

FULLY QUALIFIED, ON PURPOSE

DVT deliberately does notput your database and schema into the connection itself — Snowflake session setup rejects that combination in the data lane even when both exist ("Object does not exist"). Instead, every table reference in the SQL DVT sends is fully qualified as DATABASE.SCHEMA.TABLE, and loads write to a three-part target object.

The practical consequence: the role you connect with needs USAGE on every database and schema your sources name — not just on the one in the profile.

ONE CASING CONVENTION — EVERYTHING UPFOLDS

Snowflake resolves unquoted identifiers by upfolding them, and a quoted mixed-case name is a different object from its unquoted twin. That split once bit real projects: a profile schema like BRONZE_trial landed tables quoted-and-case-preserved through the bulk loader, while post-hooks, staging and the native lane spoke unquoted — upfolding straight past tables that had just landed, with 002003 as the only witness.

So DVT commits to Snowflake's own convention: every object DVT names on Snowflake upfolds— landed tables, ensured schemas, staging areas, the DVT stage. Write your profile schema in whatever case reads best; it lands as its uppercase self, and every lane — hooks' {{ this }}, the native phase, retract, the catalog — finds it by the same name. One convention per engine, decided once.

Upgrading from an older DVT with mixed-case relations already landed: run dvt retract once (it speaks every spelling an engine can hold, quoted twins included) and re-run — the project re-lands wholly in the one convention.

READING SNOWFLAKE IN FEDERATION

To read Snowflake inside federated models, point a source at the output via meta.connection:

# models/sources.yml
sources:
  - name: sf_raw
    meta:
      connection: snowflake     # the profiles.yml output above
    database: RAW
    schema: PUBLIC
    tables:
      - name: ORDERS
      - name: CUSTOMERS

One case note: Snowflake folds unquoted identifiers to UPPERCASE, so spell tables the way Snowflake stores them — usually uppercase, as here. Because references arrive fully qualified, RAW.PUBLIC.ORDERS is exactly what runs.

INCREMENTAL MODELS — FIVE STRATEGIES, AND THE ONES THAT BITE

Snowflake accepts the widest strategy set of any engine DVT ships. Five names are legal in incremental_strategy: merge, append, delete+insert, insert_overwrite and microbatch. Leave the config out entirely and you get merge— that is the adapter's declared default, and unlike Postgres or SQL Server it does not swing on whether a unique_key is present.

The key matters anyway, and this is the first thing that bites: a merge with no unique_key is not a merge. The adapter emits a plain insert into instead — append semantics under a merge name, no warning, no error. If you meant an upsert, name the key.

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id'
) }}

select order_id, status, updated_at
from {{ source('sf_raw', 'ORDERS') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

The second thing that bites is insert_overwrite. On BigQuery or Spark that name means "replace the partitions this run touched". On Snowflake it targets no partitions at all: specifying OVERWRITE clears the entire target table before the new rows are inserted, in one atomic operation — a truncate-and-reload wearing an incremental's clothes. It reads one Snowflake-specific config, overwrite_columns, which is empty by default and therefore writes select *.

delete+insert and microbatch carry a condition of their own. When a unique_key is set, the temporary relation those two stage through must be a table or a transient table — a view will not do, because a view re-evaluated after the delete cannot give consistent results. Ask for anything else and the model stops before it writes:

In order to maintain consistent results when `unique_key` is not none, the
`delete+insert` strategy only supports `table` or `transient` for
`tmp_relation_type` but view was specified.

Left alone, the adapter chooses for you, and the choice follows the strategy: merge, append, insert_overwrite and the default all stage through a view; delete+insert and microbatch stage through a table whenever a key is set, and through a view when there is none. transientis DVT's addition to that list — a transient table costs no fail-safe storage, which is the right shape for a relation that exists for one statement. Python models go the other way: they always stage through a real table, and any other tmp_relation_type is refused by name.

Iceberg changes the mechanics under every strategy. A catalog-linked database allows only Iceberg tables, so temporary and transient relations are unavailable there — DVT stages through a permanent relation instead, and the DML runs without the usual begin; … commit;wrapper. Changing a live model's table format is refused outright, whichever strategy it uses:

Unable to update the incremental model `orders` from `default` to `iceberg`
due to Snowflake limitation. Please execute with --full-refresh to drop the
table and recreate in the new catalog.

Because Snowflake accepts every strategy dbt knows about, the usual not valid for this adapter refusal never fires here. A misspelling gets you the other message instead — the one that reports a missing macro rather than a rejected name:

dbt could not find an incremental strategy macro with the name "mrege" in snowflake

When Snowflake is the target of a federated f_incremental model, the vocabulary shrinks. DVT computes the model in DuckDB and lands the result through its data-movement layer, so the strategy is mapped onto a load mode rather than compiled into Snowflake SQL, and only three names survive that mapping: append, merge and delete+insert. insert_overwrite and microbatch — both perfectly legal on a native Snowflake incremental — are refused before the run starts:

DVT025  … has no Sling equivalent — f_incremental supports append, merge and delete+insert.

Two more federated facts worth knowing before you configure against them. merge and delete+insert are the same thing on this lane — both become a merge on the unique_key, and there is no delete-then-insert pass. And a missing key is not an error: merge or delete+insert without a unique_key falls back to a full refresh with a warning, as does append without a watermark_column.

One thing DVT does not do here: rewrite your strategy. Its substitution table holds exactly one engine — ClickHouse, whose adapter refuses merge by name — so dvt flip-target-to carries whatever incremental_strategy you wrote across to Snowflake unchanged. An engine DVT has not measured is left alone on purpose.

DRIVERS — ONE COMMAND

dvt sync reads your profiles.yml and installs what each output needs — for Snowflake, the official dbt adapter and the snowflake-connector-python driver. Then dvt debug --all proves both lanes: the dbt connection and the federation lane, per output.

REFERENCE — EVERY FIELD

Everything a Snowflake output can carry. One credential is always required: a password, a private key, or an authenticator.