Orchestrate with Data Factory, transform with Databricks: a production medallion pipeline on Azure

ADF and Databricks are complementary:

• ADF is the connector and orchestrator. It has 90+ connectors and a self-hosted integration runtime for on-premises sources, and it does scheduling and event triggers.
• Databricks is the transformation engine. Spark, Delta, SQL, ML.

#Architecture

On-prem SQL / Oracle --(SHIR)--+
SaaS APIs, SFTP, files --------+--> ADF Copy activity --> ADLS landing (Parquet)
                                                              |
                         ADF pipeline --> Databricks job: bronze -> silver -> gold (Delta, Unity Catalog)
                                                              |
                                  Power BI / Databricks SQL / Fabric (gold)

Design rule: ADF moves data in, Databricks transforms it. Avoid complex transformations in ADF data flows and Databricks for the same data. Pick one engine for logic.

#Step 1: ingestion with ADF

• Metadata-driven copy: a control table lists sources, tables, watermark columns and target paths. A Lookup + ForEach + Copy pattern loads them all.
• Incremental loads: use a watermark column (modified_date > @{last_watermark}), or CDC where the source supports it. Update the watermark only after a successful copy.
• Land as Parquet in landing/source/table/load_date=.../ and keep it immutable.
• On-premises sources go through a self-hosted integration runtime with at least two nodes (see the SHIR HA post).

#Step 2: connecting ADF to Databricks securely

Use ADF's managed identity. Don't use a personal access token.

1. Add the ADF managed identity to the Databricks workspace as a service principal. Give it Contributor on the workspace resource, or add it in the account console with the needed entitlements.
2. Give it permission to run the job (CAN MANAGE RUN), or rights to a cluster policy if ADF creates job clusters.
3. Grant it Unity Catalog privileges on the catalogs and schemas it writes to. Alternatively, run the job as a dedicated pipeline service principal.
4. In the Azure Databricks linked service, choose Managed service identity authentication and the workspace URL.

#Which ADF activity?

When ADF defines compute, use a new job cluster from a cluster policy or an instance pool. Don't use an all-purpose cluster: it's more expensive, and jobs compete with interactive users.

#Step 3: transformation in Databricks

# notebook: silver_orders (parameters arrive as widgets)
dbutils.widgets.text("load_date", "")
dbutils.widgets.text("source_path", "")
load_date   = dbutils.widgets.get("load_date")
source_path = dbutils.widgets.get("source_path")

df = spark.read.parquet(f"{source_path}/load_date={load_date}")
df.createOrReplaceTempView("incoming")

spark.sql("""
MERGE INTO main.silver.orders t
USING (SELECT * FROM incoming QUALIFY row_number() OVER (PARTITION BY order_id ORDER BY modified_date DESC) = 1) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.modified_date > t.modified_date THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *
""")

rows = spark.table("main.silver.orders").where(f"load_date = '{load_date}'").count()
dbutils.notebook.exit(str(rows))          # returned to ADF as runOutput

In ADF, pass parameters in Base parameters (for example load_date: @formatDateTime(pipeline().TriggerTime, 'yyyy-MM-dd')). Read the result with @activity('silver_orders').output.runOutput.

For new builds, consider landing files and using Auto Loader or a Lakeflow declarative pipeline for bronze and silver. You get schema evolution, exactly-once processing and data quality expectations without writing that logic yourself (see the Auto Loader post).

#Medallion layers

#Step 4: reliability

• Idempotency: every step can be rerun for the same load_date without duplicates. Use MERGE or replace-by-partition, never blind appends in silver and gold.
• Retries: use ADF activity retries (2, with a 60–300 s interval) for transient errors. Databricks job retries cover cluster start failures.
• Failure paths: use an "On failure" branch to log to a control table and alert through Logic Apps, Teams or email.
• Data quality gates: row counts and null checks in Databricks (expectations in Lakeflow). Fail the run instead of publishing bad gold data.
• Timeouts on every activity, so a hung job doesn't block the next schedule.

#Step 5: monitoring

• ADF: diagnostic settings to Log Analytics; alert on failed pipeline runs.
• Databricks: job run history, the system.lakeflow tables, and job failure notifications.
• Link the two by passing the ADF run ID (@pipeline().RunId) as a job parameter and storing it with the data. One ID then traces a load end to end.

ADFPipelineRun
| where Status == "Failed" and TimeGenerated > ago(1d)
| project TimeGenerated, PipelineName, RunId, FailureType, Parameters

#Step 6: CI/CD for both tools

Keep environment-specific values (workspace URL, cluster policy ID, storage paths, job IDs) in parameters, never hard-coded in pipelines or notebooks.

#When to drop ADF

If all sources are cloud storage, databases reachable from Databricks (Lakehouse Federation, JDBC), or Lakeflow Connect connectors, Lakeflow Jobs can orchestrate everything on its own. That gives one tool, one monitoring surface and lower cost. Keep ADF when you need the self-hosted IR, ADF's specific SaaS connectors, or enterprise scheduling that already lives there.

#Checklist

• ADF managed identity for Databricks; no PATs.
• Job clusters from policies or pools; no all-purpose clusters for scheduled work.
• Metadata-driven, incremental ingestion with watermarks.
• Idempotent MERGE / partition-replace in silver and gold.
• Retries, timeouts, failure alerts, data quality gates.
• End-to-end run ID, from ADF to Databricks to the data.
• CI/CD: ARM for ADF, Asset Bundles for Databricks.