dbt (via dbt-odbc)

Use dbt as a transformation layer against Delta Forge through the generic dbt-odbc adapter.

Syntax

type: odbc; driver: 'DeltaForge ODBC Driver'

Description

## Setup ``` pip install dbt-core dbt-odbc ``` `dbt-odbc` is a generic ODBC adapter; it drives any ODBC-compliant target. The driver's responsibility is to make Delta Forge's SQL dialect close enough to standard for dbt's macros to compile cleanly. A Delta Forge-specific adapter (`dbt-deltaforge`) is on the roadmap. Until it ships, the generic adapter works. ## profiles.yml The minimum profile sets the driver name, connection target, credentials, and a thread count. Delta Forge tolerates the generic `host`, `database`, `schema`, `user`, `password` keys via the alias mapping (see `ODBC_CONN_ALIASES`). For secrets, prefer the env-var form (`{{ env_var('DELTAFORGE_PWD') }}`) and avoid checking passwords into the dbt project. Even better: omit `password:` and store the credential in the OS keychain so the driver looks it up. ## Materializations | dbt materialization | What the driver runs | Notes | |---|---|---| | `view` | `CREATE OR REPLACE VIEW ...` | Standard. | | `table` | `CREATE OR REPLACE TABLE ... AS SELECT ...` | Full rebuild every run. | | `incremental` | `INSERT INTO ... SELECT ...` with a `WHERE` filter | Requires `unique_key` for `delete+insert` strategy. Other strategies depend on the engine SQL features available; verify against current Delta Forge SQL coverage. | | `ephemeral` | inlined as a CTE | No engine round-trip. | ## Transactions dbt sets `SQL_AUTOCOMMIT=OFF` for incremental models. The driver buffers writes client-side and dispatches them as a single multi-statement script on `SQLEndTran(SQL_COMMIT)`. Partial-failure semantics: if any statement in the batch fails, the whole transaction rolls back; dbt sees one error and the model is marked failed. ## Threading The `threads: 4` setting in profiles.yml controls dbt's worker count, not anything in the driver. Each worker opens its own connection. Delta Forge handles concurrent connections from the same user fine; size the thread count by your cluster's compute headroom rather than by the driver.

Examples

# profiles.yml
deltaforge:
  outputs:
    prod:
      type: odbc
      driver: 'DeltaForge ODBC Driver'
      host: df.example.com
      database: prod
      schema: analytics
      user: alice@example.com
      password: "{{ env_var('DELTAFORGE_PWD') }}"
      threads: 4
  target: prod

Pitfalls

• `type: odbc` requires the `dbt-odbc` package; do not confuse with `dbt-spark[ODBC]` which is a Spark-specific path.
• Some dbt macros assume SQL Server's three-part naming (`database.schema.object`); Delta Forge uses zone / schema / object too, so the macros mostly work, but watch for edge cases in `dbt_utils.star` or custom macros that quote identifiers.
• The dbt thread pool opens a connection per worker. On cold-start deployments, the connect cost is paid N times. Either warm the cluster before the run or set `threads: 1` for runs that do not benefit from parallelism.

See Also

• ODBC_CONN_ALIASES
• ODBC_CONN_AUTHMODE
• ODBC_CRED_STORE