Databricks + dbt: How the Modern Transformation Stack Works

dbt turns SQL SELECT statements into tested, versioned, documented transformation pipelines. Databricks provides the compute engine and Delta Lake storage. Together, they give analytics engineers a workflow where transformations are code-reviewed like software, tested on every run, and automatically documented.

This combination has become a common architecture for teams that want to apply software engineering practices to data without leaving SQL. You write a SELECT, dbt compiles it into DDL, and Databricks executes it at scale. The result is a Delta table registered in Unity Catalog, ready for downstream BI tools, ML models, or reverse ETL.

Why Databricks + dbt Became a Common Stack

Before dbt, many data teams transformed data using custom Python scripts orchestrated by Airflow. The scripts were hard to test, rarely documented, and difficult for new team members to read. When something broke at 3 AM, debugging meant tracing through hundreds of lines of PySpark or pandas code.

dbt changed the workflow by making SQL the unit of transformation. Each model is a SELECT statement in its own file. Dependencies between models are declared with ref() calls, and dbt resolves them into a directed acyclic graph (DAG) automatically. Tests are defined in YAML alongside the models. Documentation is generated from the same YAML and the SQL comments.

Databricks changed the compute layer by providing SQL warehouses: compute endpoints optimized for SQL workloads, offered as serverless, pro or classic, where serverless warehouses typically start in 2 to 6 seconds and scale quickly. Instead of spinning up Spark clusters and managing JVM configs, analytics engineers point dbt at a SQL warehouse through the dbt-databricks adapter, which Databricks publishes and which supports the Unity Catalog three-level namespace, and let Databricks handle the rest. The results land as Delta Lake tables with ACID transactions, time travel, and schema enforcement built in.

The combination also simplifies orchestration. dbt resolves the order of models itself, so the scheduler only has to trigger a run: a job in the dbt platform, a dbt task in Databricks Lakeflow Jobs, or an external orchestrator such as Airflow. Much of the transformation logic that used to live in custom Python operators can move into SQL models, with tests and documentation attached.

Architecture: How the Pieces Fit Together

The dbt + Databricks stack has four stages. Raw data arrives from source systems through ingestion tools like Fivetran or Airbyte (Fivetran and dbt Labs completed their merger on 1 June 2026) and lands as staging tables in Delta Lake. dbt reads those staging tables, applies transformations organized into staging, intermediate, and mart layers, and writes the results back as new Delta tables or views. A SQL warehouse executes every statement. Unity Catalog governs access and metadata throughout.

The model layering pattern keeps transformations organized. Staging models (stg_) rename columns, cast types, and clean raw data - one model per source table. Intermediate models (int_) join staging models and apply business logic. Mart models (fct_, dim_) produce the final, analysis-ready tables organized by business domain.

dbt manages the dependency order automatically. When you run dbt build, it compiles every model's SELECT into DDL, resolves the DAG, and executes statements in the correct sequence. If a staging model changes, dbt knows which intermediate and mart models depend on it and rebuilds them too.

Materializations on Databricks

dbt has five built-in materializations (view, table, incremental, ephemeral and materialized view), snapshots are a separate resource type, and the dbt-databricks adapter adds streaming tables. Four choices cover most models, and the right one depends on table size, query patterns, and freshness requirements.

View is the lightest option. dbt creates a SQL view - no data is stored, no rebuild is needed, and the result is always fresh. Views work well for staging models and simple transformations. The trade-off is that every query re-executes the underlying SQL.

Table materializes the full result as a Delta table on each run. Query performance is fast because the data is pre-computed, but rebuild time scales with data volume. Tables suit mart-layer models whose full rebuild still fits the schedule.

Incremental processes only new or changed rows. You wrap a WHERE filter on a timestamp or ID column in the is_incremental() macro, and on Databricks dbt merges the results into the existing Delta table by default, updating rows that match the unique_key. For large fact tables this is the main lever: an incremental run reads only the latest slice of an events table instead of rebuilding all of it.

Snapshot captures Type 2 slowly changing dimensions. dbt compares the current state of a source table against its previous snapshot and creates historical records with dbt_valid_from and dbt_valid_to timestamps. Use snapshots when you need to track how dimension attributes change over time - for example, a customer's pricing tier or an employee's department.

A practical rule: switch to incremental when a full rebuild no longer fits your schedule or your compute budget. Until then, the added complexity of incremental logic (merge keys, timestamp filters, late-arriving data handling) usually isn't worth it.

Testing and Data Quality

dbt's testing framework runs assertions against your data after every build. Tests are defined in YAML schema files alongside the models they validate.

Generic data tests cover the most common checks, and dbt ships four of them: unique (no duplicates on a key column), not_null (required fields are populated), accepted_values (status fields contain only known values), and relationships (foreign keys reference existing records in the parent table). You declare them in a few lines of YAML, and dbt generates the SQL to verify them.

Singular data tests handle business-specific rules. You write a SQL query that returns rows violating the rule. If the query returns any rows, the test fails. For example, a test catching orders with negative revenue:

SELECT order_id, revenue FROM fct_orders WHERE revenue < 0

A check like this is how teams catch migration errors, such as refund records double-counted during a move from an old ERP, on the first production run instead of in a dashboard. dbt calls these data tests to distinguish them from unit tests, which check a model's logic against fixed input rows.

Tests run as part of dbt build. A failing test (at the default error severity) makes dbt skip the models downstream of it. This is the key difference from traditional ETL, where tests - if they exist - are usually a separate, disconnected step that nobody checks until something visibly breaks.

Performance Optimization

dbt handles the transformation logic, but performance depends on how the underlying Delta tables are structured and how the Databricks SQL warehouse is configured.

OPTIMIZE and Z-ORDER are the classic layout tools. OPTIMIZE compacts small files into larger ones, reducing the number of file reads per query. Z-ORDER co-locates related data within files based on column values, so if dashboards always filter by region and date, queries on those columns skip more files. On Unity Catalog managed tables, predictive optimization can run OPTIMIZE for you.

Liquid clustering replaces Z-ordering and partitioning, and Databricks recommends it for all new tables. It clusters incrementally, and with CLUSTER BY AUTO Databricks picks the keys from query patterns. In dbt-databricks you set it with liquid_clustered_by or auto_liquid_cluster; with liquid_clustered_by the adapter runs OPTIMIZE after each run unless you skip it. An existing unpartitioned table can switch with ALTER TABLE; a partitioned one can be converted on Databricks Runtime 18.1 and above.

SQL warehouse sizing matters more than most teams realize. A warehouse that's too small causes queries to queue and spill to disk. A warehouse that's too large wastes DBU spend. Start small, monitor query times and queue depth in the SQL warehouse history, and scale from there. Photon, the vectorized C++ engine that accelerates SQL execution while staying compatible with Spark APIs, is the default engine for SQL warehouses, so the SQL that dbt generates already runs on it.

Incremental model strategies also affect performance. The dbt-databricks adapter supports append, insert_overwrite, merge (the default on Delta), replace_where, delete+insert and microbatch. merge handles upserts on a unique_key; append is the simplest but suits only immutable event data; replace_where overwrites just the rows that match your incremental_predicates; and microbatch processes time-based batches. Whatever the strategy, filter the source side to the slice that can contain new records so Databricks reads less data.

CI/CD and Deployment

dbt projects use the same Git workflow as application code: feature branches, pull requests, code review, and automated testing before merge.

The CI step runs dbt build --select state:modified+, which builds only the models that changed and their downstream dependents. This keeps CI fast: a large project might rebuild only a handful of models during a typical PR, as long as CI can compare against the manifest of a production run. Tests run as part of the build. If any test fails, the PR is blocked.

Environment strategy separates concerns cleanly. Each developer gets their own schema (e.g., dev_jane) so experiments don't affect other team members. A staging environment runs the full build against production-scale data as a final QA step. The production environment serves the tables that BI tools and other consumers read from.

For execution, teams choose between the dbt platform (formerly dbt Cloud: managed scheduling, the browser-based dbt Studio IDE, job history) and dbt Core with an orchestrator such as Airflow, Dagster or a dbt task in Lakeflow Jobs. The dbt platform is simpler to operate. dbt Core gives more control over the execution environment and works better for teams already running Airflow for non-dbt workloads. dbt Labs' newer Rust-based engine, introduced in 2025 as the dbt Fusion engine and now dbt v2, lists Databricks as generally available.

Documentation, Lineage, and Dawiso Integration

dbt generates documentation and lineage graphs from model references. Running dbt docs generate produces a searchable website showing every model, its columns, descriptions, tests, and the DAG of dependencies. The persist_docs config pushes model descriptions into Databricks table comments, making them visible in Unity Catalog's UI. In the dbt platform, dbt Catalog (formerly dbt Explorer) serves the same metadata.

But dbt documentation has a scope limitation: it only knows about what's inside the dbt project. It doesn't see the upstream ingestion pipelines, the downstream BI reports, the Excel files someone uploads manually, or the other data platforms in the organization. For a team running dbt alongside Snowflake, a legacy Oracle warehouse, and a set of Power BI dashboards, dbt docs cover only the part of the estate that dbt builds.

This is where Dawiso fits. Its dbt connector reads dbt platform metadata (projects, jobs, models, sources, tests and exposures) into the data catalog, next to the metadata from Dawiso's other connectors, such as the Databricks connector. In the business glossary, a term such as "revenue" can be related to the catalog objects that implement it, such as a fct_orders.revenue column. Data lineage combines the dbt relations (model to model, model to source, exposure to model) with lineage from other connectors, such as column lineage that the Databricks connector reads from system.access.column_lineage. The dbt connector itself provides object-level relations, not column-to-column lineage.

Through the Model Context Protocol (MCP), AI agents can use the Dawiso MCP server to search the catalog, read object details, trace lineage and preview data samples, and, for users whose role allows it, write back through its write tools. An AI assistant helping an analyst write a dbt model in an MCP-capable IDE can look up whether a column exists, how it is described, and which downstream objects depend on it, without the analyst leaving the editor.

Conclusion

The dbt + Databricks stack works because it meets analytics engineers where they are: in SQL. dbt provides the software engineering practices - version control, testing, documentation, CI/CD - and Databricks provides the compute and storage. The result is transformation pipelines that are faster to build, easier to debug, and possible to hand off to new team members without tribal knowledge. The remaining gap - cross-platform visibility - closes when a catalog like Dawiso connects dbt's project-scoped documentation to the broader data estate.

Sources

• dbt Developer Hub - Materializations
• dbt Developer Hub - Configure incremental models
• dbt Developer Hub - Databricks configurations
• dbt Developer Hub - Add snapshots to your DAG
• dbt Developer Hub - Add data tests to your DAG
• dbt Developer Hub - Supported data platforms
• dbt Labs - Updated names for dbt platform and features
• GitHub - databricks/dbt-databricks
• Fivetran - Fivetran and dbt Labs complete merger
• Databricks - dbt task for jobs
• Microsoft Learn - SQL warehouse types
• Databricks - What is Photon?
• Databricks - Use liquid clustering for tables
• Dawiso Help - dbt connector
• Dawiso Help - Databricks connector
• Dawiso Help - Business glossary
• Dawiso Help - Dawiso Model Context Protocol (MCP)