Why move
Mapping data flows run on Spark clusters that ADF manages for you, but the logic is locked in a visual graph, start-up adds minutes to each run, and debugging and testing are limited. In Databricks the same logic is code: versioned, testable, reusable, and often cheaper at scale.
1. Inventory
For each data flow: sources and sinks, transformations used, parameters, runtime and cluster size (core count, TTL), schedule and downstream dependencies. The data flow script (Script view in the editor) is a readable description of the graph — use it as the spec.
2. Translate transformations
| Data flow transformation | Spark SQL / PySpark |
|---|---|
| Source / Sink | spark.read / table names; MERGE or saveAsTable |
| Derived column | SELECT ..., expr AS col / withColumn |
| Filter | WHERE |
| Join / Lookup | JOIN (lookup = left join + first match) |
| Aggregate | GROUP BY |
| Conditional split | Several WHERE outputs, or a CASE + filter per target |
| Exists | LEFT SEMI JOIN / LEFT ANTI JOIN |
| Surrogate key | IDENTITY column on the Delta table or monotonically_increasing_id() (not consecutive) |
| Alter row + upsert sink | MERGE INTO |
| Window | Window functions |
| Flatten | explode / LATERAL VIEW |
| Pivot / Unpivot | PIVOT / UNPIVOT |
Example — data flow "dedupe customers and upsert":
MERGE INTO main.silver.customers t
USING (
SELECT * FROM main.bronze.customers_raw
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1
) s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;
3. Or declarative pipelines
For bronze → silver → gold chains with quality rules, Lakeflow Declarative Pipelines express the flow and data quality checks directly:
CREATE OR REFRESH STREAMING TABLE customers_bronze AS
SELECT * FROM STREAM read_files('/Volumes/main/ingest/raw/customers/', format => 'json');
CREATE OR REFRESH STREAMING TABLE customers_silver (
CONSTRAINT valid_id EXPECT (customer_id IS NOT NULL) ON VIOLATION DROP ROW
);
APPLY CHANGES INTO customers_silver
FROM STREAM(customers_bronze) KEYS (customer_id) SEQUENCE BY updated_at STORED AS SCD TYPE 1;
(Syntax evolves — newer releases use AUTO CDC naming; check your runtime's docs.)
4. Keep ADF for orchestration (optional)
Replace the Data Flow activity with a Databricks Job activity that triggers the new job, authenticated with the factory's managed identity. Pipelines, triggers and monitoring in ADF stay the same.
5. Compare and switch
Run both for a few cycles writing to separate tables; compare counts and key aggregates, plus runtime and cost (ADF data flow vCore-hours vs. Databricks DBUs). Switch the sink consumers, then delete the data flow and its integration runtime settings.
Checklist
- Data flows inventoried with scripts exported
- Logic translated and unit-tested
- Quality rules as expectations/tests
- ADF calls Databricks jobs via managed identity
- Parallel run reconciled; data flows removed