SQLITE

A COMPLETE, WORKING PROFILE

This is everything. Drop it in profiles.yml, point the path at a real file (SQLite creates it if it doesn't exist), and dvt run works:

# ~/.dbt/profiles.yml
my_project:
  target: local
  outputs:
    local:
      type: sqlite
      threads: 1
      database: analytics                  # a LABEL, not a path — see below
      schema: main
      schemas_and_paths:
        main: /Users/you/data/analytics.db # the actual file
      schema_directory: /Users/you/data/schemas

Both schemas_and_paths and schema_directory must exist as keys — the adapter requires them. If you only use one file, point schema_directoryat an empty directory and move on. And there's nothing to install: the driver is Python's own sqlite3 module, so dvt sync has no download to do for this engine — it just verifies the profile.

DATABASE IS A LABEL — THE FILE MAP IS THE REAL CONFIG

The one thing everyone trips on: in a SQLite profile, database does notpoint at your file. It's a display label dbt carries through logs and relation names. The actual files come from two other keys, resolved in this order:

1. schemas_and_paths — an explicit map from schema name to .db file. This is the way to do it: every file named, nothing guessed.

      schemas_and_paths:
        main: /Users/you/data/analytics.db
        raw:  /Users/you/data/raw_events.db   # attached as schema "raw"

2. schema_directory — a directory that gets scanned for *.db files. Each one attaches under its filename: staging.db becomes schema staging. Handy when files come and go; a filename that collides with a name already claimed in schemas_and_paths is refused with an error telling you to fix the profile, not silently shadowed.

      schema_directory: /Users/you/data/schemas
      # /Users/you/data/schemas/staging.db  ->  schema "staging"
      # /Users/you/data/schemas/archive.db  ->  schema "archive"

3. database as a last resort— for hand-written minimal profiles, DVT's data lane will fall back to treating database as a file path when neither map is usable. It works, but prefer the explicit map: the errors are better and the intent is visible.

When something's off, DVT tells you exactly what it looked for. A schema missing from the map: sqlite: schema 'raw' not in schemas_and_paths (has: [main]). No path at all: sqlite: missing database path — set schemas_and_paths, schema_directory, or database.

ENGINE BEHAVIOR WORTH KNOWING

Single writer, by design. SQLite allows one writer per file, full stop. DVT respects that end to end: data-movement jobs that touch the same .db file are serialized (one at a time per file, coordinated by file path), and threads: 1is the right setting. Paths are absolutized before use — write absolute paths in the profile and there's never a question of which directory a relative path resolved against. In-memory databases (:memory:) aren't useful here: DVT needs a file it can come back to.

md5() ships built in. Stock SQLite has no md5()function, which upstream dbt setups work around by loading a crypto extension. DVT's SQLite adapter registers md5()on every connection itself — snapshots and dbt's hash() macro work out of the box, no extension required.

Extensions load from the profile. If you do want SQLite extensions (spatialite, sqlean, your own), list their library paths under extensions and they load at connect time:

      extensions:
        - /usr/local/lib/mod_spatialite.dylib

Identifier case.Case is preserved on write and compared case-insensitively on read, the SQLite way; you'll rarely need quoting at all. Incremental models behave differently here than on any other engine in DVT — there is no strategy to choose at all — and the section below is that story in full.

Federation, fully. SQLite is a first-class citizen of the data lane: federated models read SQLite sources, f_table and f_incremental results land in SQLite targets, and seeds load through the same lane. Declare the connection on your sources like any other engine:

# models/sources.yml
sources:
  - name: local_data
    meta:
      connection: local     # the output name from profiles.yml
    tables:
      - name: users
      - name: transactions

Dev setup in one line. There is none. sqlite3 analytics.db creates a file if you want one ahead of time — or just run DVT and let the first materialization create it. This is the easiest engine to try StarRocks or Exasol workflows against before pointing at a real cluster.

INCREMENTAL MODELS — THERE IS NO STRATEGY, THE KEY DECIDES

Every other engine on this site answers “which incremental strategies do you support?” with a list. SQLite answers with a shrug, and it is better to say so than to invent one. The SQLite adapter ships its own incremental materialization that never reads incremental_strategyat all — no dispatch, no whitelist, no validation. Whatever you write there on a SQLite model is silently ignored. For completeness: because the adapter declares no strategy list of its own, it inherits dbt-core's base list of append — but that inherited value is inert here, because nothing ever consults it.

What actually happens is decided by unique_key, and by nothing else. With a unique_key, the incremental run is a delete then insert: rows whose key appears in the incoming batch are deleted from the target, then the batch is inserted. Without one, it is a plain insert — an append. If you need a label for it in a design doc, call it “delete+insert when unique_keyis set, append otherwise”, and do not call it a configurable strategy.

{{ config(
    materialized='incremental',
    unique_key='user_id'       -- present: delete then insert; absent: append
) }}

select user_id, email, updated_at
from {{ ref('stg_users') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Adding incremental_strategy='merge' to that config would change nothing whatsoever — not the SQL, not the result, not the log. Conditional support is the same short story: partition_by, incremental_predicates, microbatch and on_schema_change handling do not appear in the SQLite incremental materialization at all. --full-refreshdrops and recreates the relation rather than building a backup copy and swapping — a deliberate simplification the adapter documents inline, because the standard dbt path renames relations and SQLite's attached-file model makes that awkward.

The error for an unsupported strategy is that there is no error. Nothing in the SQLite adapter raises for an unrecognised incremental_strategy, because nothing reads it. This is the one engine documented here where a wrong strategy is never reported back to you — if an incremental model is not behaving the way its config reads, check the config against this page, not the logs.

When SQLite is the target of a federated f_incremental model, the strategy starts mattering again — because a different layer handles it. DVT computes the model and lands the rows through its data-movement layer, which understands append, merge and delete+insert. There, merge and delete+insert are the same operation, a key-based upsert on unique_key; insert_overwrite and microbatch are refused by name with DVT025: has no Sling equivalent — f_incremental supports append, merge and delete+insert. A merge or delete+insert with no unique_key warns and falls back to a full refresh rather than failing, and an append with no watermark_column does the same. SQLite is one of the engines admitted to the keyless-watermark set — postgres, redshift, mysql, duckdb, sqlite, sqlserver, oracle, clickhouse, trino — so an f_incremental carrying only a watermark_column and no key runs against a SQLite target. Engines whose connector has not been verified to honour an update-key-only load — starrocks, snowflake, databricks, bigquery, fabric and athena — are refused with DVT026 instead.

REFERENCE — EVERY PROFILE FIELD