A Decision Framework for ETL Migration to Databricks

A Decision Framework for ETL Migration to Databricks | Databricks Blog Skip to main content

Summary

Three paths, not one: Lakehouse, Spark Declarative Pipelines (SDP), and PySpark or Spark SQL notebooks address different migration scenarios. Most organizations end up using a combination.

Phase for outcomes: A four-stage approach (assess, quick wins, modernize, optimize) lets you retire legacy systems incrementally instead of betting on a big-bang cutover.

Let the tooling do the heavy lifting: Lakebridge, partner transpilers, and AI-assisted code conversion automate much of the mechanical translation so your team can focus on validation and optimization.

Your team has hundreds of stored procedures, a couple of schedulers, permissions scattered across roles and schemas, and a cloud data warehouse renewal deadline coming up. Nobody agrees on what to move first. Some want to rewrite everything in PySpark. Others want to move SQL as-is and call it done. Lost in the conversation: the metadata, lineage, and permissions that move with the code, plus the opportunity to consolidate them on the way. Neither extreme works. The teams that succeed at data warehouse migration look at each workload individually and pick the right tool for the job. This post suggests a decision framework for selection: when to use Lakehouse (Databricks SQL), Spark Declarative Pipelines, or PySpark, and how to phase the work so you ship results instead of stalling on a plan. Three paths, one migration On Databricks, you can migrate ETL pipelines in three primary ways, often used together. Lakehouse (Databricks SQL) This is the most direct path for SQL-heavy teams. It covers a spectrum from simple to complex. It runs on SQL warehouses, which are Photon-accelerated by default and fully compatible with ANSI and Spark SQL (%sql). Choose Serverless for variable or unpredictable workloads (fast startup, scales to zero, pay per second). Choose Classic for steady workloads or when you need specific networking or cost controls. A straightforward SQL task:

When the logic requires control-of-flow (conditionals), loops, variables, error handling, or parameter-driven execution, stored procedures give you that procedural layer. They are governed via Unity Catalog and can be called from Workflows with parameters.

The rule of thumb: if your legacy code is a single SQL statement, migrate it as a SQL task. If it has procedural logic (variables, loops, parameters, error handling), wrap it in a stored procedure, governed by Unity Catalog and callable from Workflows. Do not wrap simple SQL in a procedure just because the original system required it. Spark Declarative Pipelines ( SDP ) Which is part of Lakeflow, take a different approach. You declare what your pipeline should produce and the engine handles execution order, retries, and scaling. You get built-in data quality constraints, automatic dependency resolution, and unified batch-plus-streaming in the same definition.

Under the hood, Enzyme decides when to incrementally update versus fully recompute derived tables. Autoscaling adjusts capacity to data volume changes without manual tuning. Companies like Block lean on this declarative model to simplify pipeline orchestration as usage grows. PySpark and Spark SQL notebooks Which gives you full control. They run on job clusters and handle the workloads that don't fit a SQL Warehouse or a declarative pipeline. Reach for PySpark when the workload needs complex business logic, ML feature engineering, API integrations, or custom validation. The example below scores transactions with a model registered in Unity Catalog:

Reach for Spark SQL in a notebook when the language is still SQL but the workload may exceed SQL Warehouse fit: very large tables, heavy shuffles, long-running batch ETL where you want explicit control over partitioning, broadcast joins, or caching. Enable Photon on the job cluster for compute-bound SQL or DataFrame work: large joins, aggregations, window functions, scans over big columnar tables. Photon is a native, vectorized engine that accelerates these patterns without code changes, including Pandas UDFs (Arrow-based). Skip Photon when row-wise Python UDFs dominate, datasets are small, or the job is pure I/O. Notebooks also fit well in hybrid pipelines: ingestion in SDP, enrichment in a notebook task. Decision matrix The table below is a starting point for team conversations, not a hard rule. Criteria Lakehouse(tasks and stored procs) Spark Declarative Pipelines PySpark + Spark SQL notebooks Team profile SQL-heavy, DBAs, DW engineers Data engineers and SQL teams building managed pipelines Python/Spark developers, ML engineers Type of logic SQL ETL: simple tasks for single statements, stored procs for procedural logic Declarative pipelines, CDC, SCD Complex logic, custom UDFs, ML prep SQL migration speed High for SQL ANSI-like workloads Medium: pipeline redesign, but SQL reuse Variable: may require significant refactoring Pipeline orchestration Workflows with SQL tasks or CALL procedure Embedded in pipelines Workflows with notebook tasks Batch vs. streaming Primarily batch Unified batch and streaming Batch and streaming via Structured Streaming Data quality Manual SQL checks Declarative constraints Custom validation in code

Quick decision grid Find your team in the column and your workload complexity in the row. The cell may suggest where to start. Workload complexity SQL-first team Hybrid team Code-first team Low (batch loads, aggregations, MERGE) SQL tasks in Workflows SQL tasks or SDP PySpark or SDP Medium (multi-step pipelines, CDC, data quality) Stored procedures or SDP SDP SDP or PySpark High (ML prep, Custom UDFs, APIs, dense business logic) SDP + PySpark assist PySpark + SDP for ingestion PySpark

Four phases instead of big-bang Rather than deciding "which approach for everything," decide "what to do next" in each phase. Phase 1 — Assess. Collect metrics from your legacy data warehouse: CPU time, runtime, frequency, source and target tables. Classify workloads by complexity. Use migration tools, when possible, to build an inventory scored by value versus difficulty. Where you find this data depends on the source. On Teradata , query DBC.QryLog . On SQL Server , use sys.dm_exec_query_stats . On Oracle, AWR reports . On Snowflake, QUERY_HISTORY . The specifics may vary. If you have an integration tool in place, you can leverage its metadata to identify relationships between tables, or rely on...