如何将 dbt ETL 管道迁移到 Databricks

More teams are running their dbt transformations on Databricks Lakehouse: an open platform with no lock-in, unified pipelines, built-in Unity Catalog governance, and strong price/performance.

Your dbt project works: models compile, tests pass, and your transformation logic lives in version-controlled SQL and YAML, not hardwired into any one warehouse. dbt’s open adapter framework is designed for exactly this decoupling, so moving from one cloud data warehouse to another is mostly a matter of changing the adapter and connection config plus a few dialect tweaks - not rewriting your DAG or business logic.

Databricks delivers on that with the co-engineered dbt-databricks adapter, open lakehouse storage (Delta Lake and Apache Iceberg™), Unity Catalog, and Lakeflow Jobs that give you an open, unified platform where dbt runs with built-in governance and strong price/performance from day one, making it a great place to run your dbt workloads. That’s why more than 3,000 organizations already run dbt on Databricks.

If you’ve been evaluating a move, the good news is you don’t have to rebuild your project. This post walks through how to repoint a working dbt project to Databricks by swapping the adapter and profile, handling a handful of SQL differences, and keeping your transformation logic inside dbt.

Why repointing dbt is a strong migration starting point

A recurring challenge with data warehouse migration projects is trying to move everything at once, which can lead to delays and bottlenecks. A better approach is to start with the transformation layer, which is a fast way to unlock cost savings from a migration.

dbt projects are already modular and testable. That makes them ideal candidates for an incremental migration. When you repoint dbt to Databricks, you get:

• Immediate validation. You can compare outputs between old and new warehouses model by model
• Reduced risk. Your transformation logic doesn't change, so you're isolating the variable to compute and storage
• A working proof of concept. Stakeholders can see queries running on Databricks before you migrate ingestion or BI layers
• Faster time to value. Instead of migrating an entire tech stack at once, move the transformation layer first

Prerequisites

Scope: This guide covers repointing your dbt transformation layer only. It assumes your source data is already in Databricks — landed as Delta or Iceberg tables and registered in Unity Catalog — and that your catalogs and schemas exist. Migrating the data itself and setting up Unity Catalog are separate efforts; see the Databricks migration guides and Lakebridge for those.

Before you start, make sure you have:

• A Databricks workspace with a SQL warehouse provisioned
• A Unity Catalog setup with the target catalog and schema created, and your source (raw/bronze) tables already landed as Delta or Iceberg and registered in UC
• Your existing dbt project in version control (dbt Core 1.8+ or dbt Platform)
• Python 3.9+ installed locally (for dbt Core users)
• Access credentials: a Databricks personal access token or OAuth configuration
• Optionally, Lakebridge can automate much of the SQL conversion. It scans your source warehouse and the SQL in your dbt models, converts dialect-specific SQL to Databricks Lakehouse, and reconciles the results against the source. It repoints the SQL in a dbt project rather than migrating dbt itself, and it doesn't move data that uses separate patterns (Lakehouse Federation + CTAS, COPY INTO, or Auto Loader).

The model we will be migrating in this example is a standard analytics fact table built on top of two source models: orders and order_items. It aggregates order-level data to calculate total revenue and compiles a list of products sold for every individual transaction from the past year. 

This model uses several common SQL dialect patterns, such as regexp_substr, div0 and object_construct, which often differ between data warehouses, making it a great example. Once you see how to handle these patterns here, you can apply the same approach to every other model in your project.

Step 1: Install the adapter and add a Databricks target

This part covers the one-time migration work which involves installing the adapter, mapping namespaces, and handling dialect differences.

Install the dbt-databricks adapter

The dbt-databricks adapter is the bridge between your dbt project and the Databricks Lakehouse warehouse. It translates dbt's compiled SQL into Databricks-compatible queries.

(dbt Platform users: select "Databricks" as the connection type in a new environment as detailed in the dbt docs - the adapter is installed automatically.)

Add a Databricks target to `profiles.yml`. Keep your existing target intact; you'll need it during validation. Add a second target alongside it:

Verify the connection:

You should see:  Connection test: [OK connection ok].

If you still cannot connect, follow the connection troubleshooting steps in the Databricks + dbt integration guide and the dbt Databricks profile reference.

Pro Tip: http_path determines whether queries run on a SQL warehouse (recommended for dbt) or an all-purpose cluster. SQL warehouses offer better price/performance for SQL-heavy workloads.

Step 2: Point Table Sources at Unity Catalog and Add Tests

Databricks uses a three-level namespace: catalog.schema.table. Update the sources.yml entries that fct_orders reads from:

Update schema.yml to include tests

Pro Tip: If you skip database:, queries land in the workspace's default catalog. Set it explicitly.

Step 3: First compile

Now run dbt compile for the model:

Our fct_orders model produced 3 compilation errors as detailed below, all dialect-related. This is expected and while these three are representative of the dialect issues most projects hit, they aren't the whole story: larger migrations also run into incremental-model strategies, snapshots, and semi-structured functions with no direct equivalent. 

We intentionally use an example model that relies on dialect-specific patterns like REGEXP_SUBSTR with positional parameters, DIV0 for safe division, and OBJECT_CONSTRUCT for JSON building - the kind of functions that differ between warehouses. That way, the initial dbt run errors become a guide through the conversion process, showing you how to turn these into portable macros and Databricks-friendly SQL so you can apply the same fixes across the rest of your project. To demonstrate, we will walk you through each error, the root cause, and the fix. Before diving into the errors, a note on portability: where a function has a portable equivalent, dbt's cross-database macros (the dbt.* namespace) let you write it once so it compiles on any warehouse — worth adopting as you standardize. We'll walk through each error and its fix, then show how to automate the conversion across a large project.

Error 1: REGEXP_SUBSTR dialect incompatibility

Databricks follows Apache SQL dialect and only supports 2 parameters for REGEXP_SUBSTR

Fix: use Databricks’ native regexp_extract() function

You could also let the Genie Code make this conversion for you, fixing dialect gaps like this is exactly what it's good at. We'll do all three by hand so you can see what's changing.

Error 2: DIV0 (safe division)

Root cause: some warehouses use DIV0 to return 0 instead of throwing an error when dividing by zero.

Fix: add dbt_utils to your packages and use its built-in safe_divide function

Error 3: object_construct (JSON builder)

Cannot resolve routine `object_construct`

Fix: Use the Databricks named_struct function

Re-run compile:

Compiles successfully. Total dialect changes for this model: dbt_utils package installed (for safe division), regexp_substr call converted to regexp_extract, div0 call replaced with dbt_utils.safe_divide(), object_construct call converted to named_struct.

Fixing three functions by hand is easy. A real dbt project has hundreds or thousands of models, and these dialect gaps are exactly what AI tooling closes automatically. The Genie Code converts dialect-specific SQL and handles these fixes right in the editor, so you spend your time reviewing the conversions instead of writing each one.

Step 4: First run

With compile green, execute the model and any tests that exist:

Both model build and tests pass. Pro Tip:

• Runtime. Note it down - you'll compare against the old warehouse in the next step.
• Test failures on numeric columns. If equality or accepted_values tests fail, it's almost always floating-point precision, not a logic bug.

Step 5: Validate row-by-row against the legacy warehouse

A green dbt run proves the SQL executes. Now, we need to reconcile the outputs across legacy warehouse and Databricks. Use dbt-audit-helper to compare row-by-row.

Install:

Compare fct_orders across both warehouses. In analyses/compare_fct_orders.sql:

Run it against Databricks (assuming you've replicated the legacy output into Databricks for the comparison, or run a cross-warehouse comparison):

Expected result:

Note: These examples cover the most common patterns, but are not exhaustive. For any additional mismatches (e.g. string trimming, collation, or custom UDF behavior), set summarize=false to materialize sample rows, inspect a few primary keys where in_a and in_b differ, fix the model or macro, and rerun until you get a 100% match.

In our run: fct_orders matched exactly.

Step 6: Deploy in Databricks

Instead of maintaining a separate orchestration layer for dbt, you can run dbt alongside upstream ingestion and downstream actions in a single pipeline with Lakeflow Jobs. dbt is a first-class task type within Jobs and you don’t need an external orchestrator or a custom Docker image.

Create the Job:

1. Workspace → Jobs & Pipelines → Create Job
2. Task type: dbt
3. Git source: your dbt repo
4. Commands:

1. SQL warehouse: your prod warehouse
2. Warehouse Catalog: The catalog the table will be written to: dev
3. Warehouse Schema: The schema the table will be written to: analytics
4. Schedule: your preferred cadence

What Jobs give you out of the box:

• Fully managed—no additional infrastructure to purchase, secure, or maintain
• Ability to create a single pipeline to run dbt tasks alongside upstream ingestion pipelines along with downstream tasks like Power BI refreshes

Step 7: Cut over and decommission

Once you have validated your dbt project and deployed on Databricks, the next step is to move production traffic to Databricks in a controlled way, keep a short rollback path, and avoid paying for two warehouses longer than necessary.

Keep this checklist:

1. Flip the production target in profiles.yml so prod points at Databricks - all new production runs now write to Databricks.
2. Update CI/CD credentials so PR checks run against a Databricks staging catalog, not the legacy warehouse.
3. Monitor costs via system.billing.usage to confirm the Databricks spend profile.
4. Decommission the legacy target once the rollback window closes and you are confident Databricks is stable in production..

Scaling to the rest of your project

Most of the work you just did is one-time: the adapter install, the profiles.yml target, and the source namespace change. Once those are in place, repointing the next model costs only the incremental dialect fixes.

Some patterns need more than a dialect swap, and you'll meet them as you scale:

• Incremental models — incremental strategies differ across platforms; the strategy your source uses may not map 1:1 to a Databricks incremental strategy, so you'll re-select the strategy and re-validate the incremental logic.
• Snapshots — you'll re-point the snapshot logic and, separately, bring the existing historical snapshot data across so you don't lose history.
• Semi-structured and niche functions — some source functions have no direct Databricks equivalent and need a rewrite or a macro, not a one-liner.

For the heavy lifting here, lean on the full migration guides.

From there, migrate in small batches and work in DAG order - sources first, then staging, intermediate, and marts so that each batch validates cleanly against models you’ve already moved. Run both targets in parallel and audit-helper to compare every model until they all match. When the last model is green, cut over the whole project. This guide is a simplified walkthrough of repointing, with the full scope of a migration covered in our public migration guides. 

Conclusion

Repointing dbt to Databricks is a practical, low-risk way to start a warehouse migration. Your models and tests stay the same - you are changing where they run, and you gain open source formats and standards such as Delta Lake and Apache Iceberg™, with Unity Catalog providing governance and lineage on top of a platform that can also serve your downstream AI work. Start with one model, compare the outputs, then expand until you are comfortable making Databricks the primary home for your dbt project.

Ready to try it? 

1. Set up a Databricks free trial
2. Install the dbt-databricks adapter
3. Run your first dbt compile against a Databricks Lakehouse warehouse. Afterwards, deploy a Databricks Job to execute a dbt model in production by following the Databricks documentation.

For more on dbt with Databricks, explore the dbt-databricks adapter on GitHub.