Bring dbt to IBM Db2: Modern Data Transformation with ibm-dbt-db2
A technical journey building a production-ready dbt adapter for the enterprise database that runs the world's most critical workloads.
Tags: #Db2 #dbt #DataEngineering #ELT #AnalyticsEngineering #OpenSource
dbt (data build tool) has become the standard way to transform data inside the warehouse: you write SQL, and you get version control, testing and documentation for free. Most cloud warehouses have had first-class dbt adapters for years.
IBM Db2 — the database that quietly runs OLTP/OLAP, mainframe, and analytics workloads across banking, insurance, healthcare, and retail — has largely been left out of that story.
But what if you could run dbt directly against Db2? Version-controlled SQL models, automated tests, generated docs, and snapshots, without leaving your existing enterprise database?
That is exactly what the ibm-dbt-db2 adapter enables.
What is dbt?
dbt is an open-source command-line tool that lets data teams transform data in the warehouse using plain SQL and software-engineering practices. Instead of the old ETL model (transform data before loading it, using a proprietary tool), dbt uses ELT: load your raw data into the warehouse first, then transform it in place with SQL that your team owns.
In practice, dbt gives you:
• Version control – every transformation is a .sql file tracked in Git
• Testing – built-in data-quality checks like unique and not_null
• Documentation – auto-generated, searchable docs and lineage
• Modularity – reusable SQL models and macros
The ibm-dbt-db2 adapter brings all of this to Db2, so your transformation logic lives where your data already does.
The ELT model — dbt does the “T” inside Db2, where the data already lives.
What You Can Build
With ibm-dbt-db2, you can build the full range of dbt workflows on top of Db2:
• Tables and views: materialize transformed datasets as persistent tables or virtual views
• Incremental models: process only new or changed rows in large tables instead of rebuilding from scratch
• Snapshots (SCD Type 2): track how records change over time, with history columns managed for you
• Seeds: load small CSV reference datasets straight into Db2
• Tests: enforce data-quality rules on your models and catch issues before they ship
• Documentation: generate browsable docs and lineage for your whole project
It works across the Db2 family — Db2 LUW, Db2 for z/OS, and Db2 for i (iSeries) — so the same tooling covers on-premises, mainframe, and Power Systems estates.
Prerequisites
Before you start, make sure your environment matches the supported versions. These are the combinations the adapter is built and tested against:
|
Component
|
Requirement
|
|
Python
|
3.10 – 3.12
|
|
dbt-core
|
~= 1.11.0
|
|
ibm_db driver
|
== 3.2.8
|
|
IBM Db2 database
|
LUW, z/OS, or iSeries
|
Note: Python 3.13+ is not yet tested, and Python 3.9 is not supported due to the requirements of dbt-core 1.11+.
Installation
The adapter is published on PyPI. Install it with a single command:
This pulls in dbt-core and the ibm_db driver, which handles all Db2 connectivity. If you prefer an isolated setup (recommended), create and activate a virtual environment first:
|
python -m venv dbt-db2-env
source dbt-db2-env/bin/activate # On Windows: dbt-db2-env\Scripts\activate
pip install ibm-dbt-db2
|
Configuration
dbt connects to Db2 through a profile. Create or edit ~/.dbt/profiles.yml and add a Db2 target:
|
my_project:
outputs:
dev:
type: db2
host: <your-db2-host>
port: <50000>
database: <your_database>
schema: <your_schema>
username: <your_username>
password: <your_password>
threads: 4
target: dev
|
For production, enable SSL/TLS and connection retries directly in the profile:
|
prod:
type: db2
host: secure-db2.example.com
port: <50001>
database: <your_database>
schema: <your_schema>
security: SSL
ssl_server_certificate: /path/to/ca.crt
retries: 3
|
Verify the connection at any time with:
Building Your First Models Step by Step
Part 1: A simple table model
Create models/customer_summary.sql. The config block tells dbt to materialize the result as a table in Db2:
|
{{ config(materialized='table') }}
SELECT
customer_id,
customer_name,
COUNT(*) AS order_count,
SUM(order_amount) AS total_spent
FROM {{ source('raw', 'orders') }}
GROUP BY customer_id, customer_name
|
Run it:
dbt creates (or replaces) the customer_summary table in your target schema.
Part 2: An incremental model
For large, frequently updated tables, rebuilding everything each run is wasteful. Incremental models process only new rows:
|
{{
config(
materialized='incremental',
unique_key='id',
incremental_strategy='merge'
)
}}
SELECT * FROM {{ source('raw', 'events') }}
{% if is_incremental() %}
WHERE event_time > (SELECT MAX(event_time) FROM {{ this }})
{% endif %}
|
On the first run, dbt builds the full table. On every run after that, it only loads rows newer than what already exists based on the condition.
Part 3: A snapshot (SCD Type 2)
Snapshots capture how a record changes over time — useful for slowly changing dimensions:
|
{% snapshot customers_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='customer_id',
strategy='timestamp',
updated_at='updated_at'
)
}}
SELECT * FROM {{ source('raw', 'customers') }}
{% endsnapshot %}
|
dbt automatically maintains dbt_valid_from, dbt_valid_to, and dbt_scd_id columns so you always have a full history.
Run snapshots with:
Incremental Strategies Explained
The adapter supports two incremental strategies for Db2. Pick one based on how your data changes.
1. Merge (recommended for most cases)
Uses Db2's native MERGE statement to upsert in a single atomic operation: matched rows are updated, new rows are inserted.
Example scenario: A daily orders feed where existing orders occasionally change status and new orders arrive. merge updates the changed orders and inserts the new ones in one pass — ideal for slowly changing data.
2. Delete + Insert
A two-step approach: delete the matching rows, then insert the new data. This is better when your matching logic is complex or you want a clean full replacement of a slice of data.
Example scenario: Reloading a full day's partition of clickstream data where it is simpler to clear the day and reinsert it than to reconcile row by row.
|
Behaviour
|
Merge
|
Delete + Insert
|
|
Operation
|
Single atomic upsert
|
Two-step (delete, then load)
|
|
Updates existing rows
|
Yes
|
Via delete + reinsert
|
|
Best for
|
Slowly changing data
|
Complex conditions / full replacement
|
Wrapping Up
With ibm-dbt-db2 in place, you can now:
• Run dbt models, tests, snapshots, and docs directly against Db2 LUW, z/OS, or i
• Choose between merge and delete-and-insert incremental strategies based on how your data changes
• Bring version control, testing, and documentation to your Db2 transformations — without leaving your enterprise database
It is the same modern, SQL-first workflow that cloud-warehouse teams already rely on, now available where your Db2 data already lives.
Resources
• ibm-dbt-db2 on PyPI — https://pypi.org/project/ibm-dbt-db2/
• ibm-dbt-db2 on GitHub — https://github.com/IBM/db2-dbt
• Report an issue or request a feature — https://github.com/IBM/db2-dbt/issues