How we automated the migration of 500+ orchestration activities across 37 data factories — with zero manual JSON editing
Target audience: Data engineers migrating Azure cloud data platforms; technical architects evaluating ADF-to-Databricks strategies
Estimated read time: 12 minutes
The Problem
Your organization runs dozens of Azure Data Factory (ADF) factories containing hundreds of pipelines. Many of those pipelines read from or write to Azure Synapse Analytics (formerly SQL Data Warehouse) — and the decision has been made to migrate that workload to Databricks Unity Catalog.
A manual approach would require teams to open each pipeline in the ADF portal, replace activities, test, repeat. At 500+ impacted activities across 37 factories, that’s months of tedious, error-prone work.
We took a different approach: treat ADF pipeline JSON as a parseable AST, and build a compiler-like engine that rewrites it programmatically.
This post walks through the architecture, the key technical decisions, and the patterns that made it work.
Architecture Overview: A Two-Phase Pipeline Compiler
The framework operates in two distinct phases, orchestrated by a single parameterized notebook:
Phase 1: Normalize (“Load Clean”)
ADF’s REST API export format is not the same as the ADF Git/authoring format that the portal and CI/CD tooling expect. Before migration, we normalize every pipeline JSON through four transformations:
- Strip ARM envelope fields — id, type, etag are Azure Resource Manager metadata, not pipeline logic
- Convert snake_case keys to camelCase — the REST API returns linked_service_name; ADF authoring expects linkedServiceName
- Nest activity-specific fields under typeProperties — the API export flattens them; authoring format nests them
- Wrap top-level fields under properties — activities, variables, annotations belong inside a properties block
The critical subtlety: Not all keys should be camelCased. ADF pipeline variables, parameters, and stored procedure parameters are user-defined identifiers referenced literally in expressions like @{variables(‘Config_Schema’)}. Converting Config_Schema to configSchema would silently break every expression in the pipeline.
The solution: maintain a set of “user-defined containers” whose immediate child keys are exempt from case conversion:
USER_DEFINED_CONTAINERS = {
"variables",
"parameters",
"globalParameters",
"storedProcedureParameters",
}
def convert_keys_camel(obj, parent_key=None):
if isinstance(obj, dict):
skip = parent_key in USER_DEFINED_CONTAINERS
return {
(k if skip else snake_to_camel(k)): convert_keys_camel(v, parent_key=k)
for k, v in obj.items()
}
elif isinstance(obj, list):
return [convert_keys_camel(item, parent_key=parent_key) for item in obj]
return obj
Phase 2: Migrate
Once normalized, the engine scans every activity in every pipeline, determines whether it references a Synapse resource, classifies the migration pattern, and generates a replacement activity.
┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │ ADF REST API │ │ Normalized │ │ Migrated │ │ Export (raw) │──▶│ Pipeline JSON │──▶│ Pipeline JSON │ │ snake_case │ │ camelCase │ │ Databricks │ └─────────────────┘ └─────────────────┘ └─────────────────┘ Phase 1: Load Clean Phase 2: Activity Migration
Zero-Config Migration: Auto-Discovering the Blast Radius
The first hard question in any migration is: what actually needs to change?
Manually cataloging Synapse-dependent resources across 37 factories, 2,200+ JSON files, and dozens of linked services is a recipe for missed dependencies. Instead, the framework auto-discovers its own migration scope by scanning the ADF export artifacts.
The Discovery Chain
linked_services/*.json datasets/*.json pipelines/*.json
│ │ │
▼ ▼ ▼
Filter by type: Match dataset → LS: Match activity → dataset:
AzureSqlDW or "Which datasets point "Which activities reference
AzureSynapseAnalytics to a Synapse LS?" a Synapse dataset?"
│ │ │
▼ ▼ ▼
SYNAPSE_LS[ ] SYNAPSE_DATASETS[ ] Migration targets
LS_TO_SECRET{ } DS_TO_LS{ }
Step 1: Identify Synapse linked services. Scan linked_services/ for JSON files where properties.type is AzureSqlDW or AzureSynapseAnalytics. While scanning, extract the Key Vault secret name from the connection string definition — this is needed later when generating JDBC parameters for the replacement notebook.
Step 2: Identify Synapse datasets. Scan datasets/ and keep any dataset whose linkedServiceName.referenceName points to a LS from Step 1.
Step 3: Classify pipeline activities. For each activity in each pipeline, check five reference patterns:
| Pattern | Where to look | What it means |
| activity.inputs[].referenceName ∈ Synapse DS | Copy source side | Data is READ from Synapse |
| activity.outputs[].referenceName ∈ Synapse DS | Copy sink side | Data is WRITTEN to Synapse |
| typeProperties.dataset.referenceName ∈ Synapse DS | Lookup activity | Query runs against Synapse |
| typeProperties.linkedServiceName ∈ Synapse LS | Stored procedure | SP executes on Synapse |
| activity.linkedServiceName ∈ Synapse LS | Root-level LS ref | Script/SP on Synapse |
This three-step chain means the framework needs zero manual configuration for a new factory — just point it at the ADF export folder and run.
Handling Nested Control Flow: Recursive Activity Tree Walking
ADF pipelines are not flat lists of activities. They contain container activities that nest child activities arbitrarily deep:
- ForEach — typeProperties.activities[]
- IfCondition — typeProperties.ifTrueActivities[] and typeProperties.ifFalseActivities[]
- Until — typeProperties.activities[]
- Switch — typeProperties.cases[].activities[]
A Synapse-dependent Lookup might be three levels deep inside a ForEach → IfCondition → activities array. A flat scan would miss it entirely.
The solution is a recursive descent function that walks every branch of the activity tree:
def migrate_activities(activities, migration_log):
migrated = []
for activity in activities:
is_target, pattern, ref = is_synapse_activity(activity)
if is_target:
new_act = apply_migration_pattern(activity, pattern, ref)
migration_log.append(build_log_entry(activity, pattern, ref))
migrated.append(new_act)
else:
new_act = deepcopy(activity)
tp = new_act.get('typeProperties', {})
if 'activities' in tp: # ForEach / Until
tp['activities'] = migrate_activities(tp['activities'], log)
if 'ifTrueActivities' in tp: # IfCondition
tp['ifTrueActivities'] = migrate_activities(tp['ifTrueActivities'], log)
if 'ifFalseActivities' in tp:
tp['ifFalseActivities'] = migrate_activities(tp['ifFalseActivities'], log)
if 'cases' in tp: # Switch
for case in tp['cases']:
if 'activities' in case:
case['activities'] = migrate_activities(case['activities'], log)
migrated.append(new_act)
return migrated
Key design decision: Non-Synapse activities are deepcopy’ed and passed through unchanged. The engine only touches what it needs to. This means the output JSON is a perfect copy of the input, except for the specific activities that were replaced — a property we validate later.
The Pattern Catalog: Three Migration Strategies for Three Activity Types
Not every Synapse-dependent activity can be replaced the same way. The framework defines a pattern catalog — a mapping from source activity type to target Databricks activity, based on what the activity actually does.
Pattern A: Copy Activity → DatabricksNotebook
Source: ADF Copy activity reading from Synapse via SqlDWSource or AzureSqlSource
Target: A DatabricksNotebook activity that calls a parameterized Generic Ingestion Framework notebook
The replacement notebook uses JDBC to connect to the source database (credentials fetched from Key Vault at runtime), executes the original SQL query, and writes the result using the original sink format.
The sink-type-aware design is the critical detail here. The original Copy activities don’t all write to the same destination format — some write Parquet to ADLS, some write CSV, some write to SQL tables. Simply replacing everything with Delta writes would break downstream consumers.
The framework inspects the original activity’s sink.type and maps it:
SINK_TYPE_MAP = {
"ParquetSink": "parquet",
"DelimitedTextSink": "csv",
"AvroSink": "avro",
"AzureSqlSink": "delta",
"SqlDWSink": "delta",
}
For file-based sinks, it also extracts the output dataset’s container, path, and filename parameters (which may be static strings or ADF dynamic expressions) and passes them through to the notebook.
Pattern B: Lookup Activity → WebActivity (SQL Statement Execution API)
Lookup activities query config tables, watermarks, or CDC flags. They’re lightweight — spinning up a notebook cluster for a single SELECT MAX(watermark) is overkill.
Instead, the framework replaces these with a WebActivity that calls the Databricks SQL Statement Execution API directly:
{
"type": "WebActivity",
"typeProperties": {
"url": "https://<workspace>/api/2.0/sql/statements",
"method": "POST",
"body": {
"warehouse_id": "<sql_warehouse_id>",
"statement": "<original_query>",
"catalog": "<target_catalog>",
"wait_timeout": "30s"
},
"authentication": {
"type": "MSI",
"resource": "2ff814a6-3304-4ab8-85cb-cd0e6f879c1d"
}
}
}
This is cheaper, faster, and doesn’t require a running cluster — the SQL warehouse handles it.
But not all Lookups are read-only. Some contain DML (INSERT, EXEC stored_proc). The framework includes a query classifier that inspects the SQL text — including dynamically generated ADF expressions — to route:
- Read-only SELECT / WITH → WebActivity (Pattern B)
- DML / stored procedure calls → DatabricksNotebook (Pattern C)
Pattern C: Stored Procedure → DatabricksNotebook
Synapse stored procedures have no direct equivalent in Databricks. These are replaced with a DatabricksNotebook activity, and the stored procedure logic itself must be rewritten as SparkSQL or PySpark in a dedicated notebook.
The framework generates the activity scaffolding; the actual logic translation is a separate workstream.
Preserving ADF Expressions Across Migration
ADF pipelines are full of dynamic expressions: @pipeline().parameters.SourceSchema, @activity(‘Lookup1’).output.firstRow.watermark, @concat(…). These are evaluated at runtime by the ADF engine.
The migration framework must handle three scenarios:
- Static values — pass through as {“value”: “dbo”, “type”: “String”}
- ADF expressions that still work — pipeline parameters and variable references don’t change when the target activity changes. These are preserved verbatim:
{"value": "@pipeline().parameters.SourceSchema", "type": "Expression"}
- Expressions that break — The biggest one: Lookup result access.
In native ADF:
@activity('MyLookup').output.firstRow.watermark_value
When the Lookup is replaced by a WebActivity calling the SQL Statement Execution API, the output shape changes. ADF reads notebook output via runOutput, and we wrap the result in a JSON envelope:
@json(activity('MyLookup').output.runOutput).firstRow.watermark_value
For WebActivity replacements, the output is the raw API response, requiring a different access pattern:
@activity('MyLookup').output.result.data_array[0][0]
The framework detects which downstream activities reference the migrated Lookup and annotates the migration report with the required expression changes. This is one area where full automation gives way to guided manual review — the combinatorial space of downstream expression patterns is too large to safely auto-rewrite.
Trust But Verify: A Five-Point Validation Suite
Automated migration is only valuable if you can trust the output. The framework runs five validation checks after every migration:
- Passthrough integrity. For pipelines with zero Synapse dependencies, the output JSON must be byte-identical to the input. Any difference means the normalization or deepcopy logic introduced a bug.
- File count verification. The all/ output folder must contain exactly as many files as the source. The impacted/ folder must match the number of pipelines that had at least one activity migrated.
- Linked service reference check. Every migrated activity (identifiable by the [MIGRATED from Synapse] description tag) must reference the Databricks linked service. If any still point to the old Synapse LS, the replacement logic has a gap.
- Synapse dataset removal. Scan all migrated pipelines for any remaining references to Synapse datasets. A DatabricksNotebook activity should never reference a Synapse dataset in its inputs/outputs.
- False positive detection. Verify that only Synapse-linked activities were migrated. If an activity referencing an ADLS linked service or a REST API was converted, the detection logic is too aggressive.
These five checks give us a green/red signal per factory, and they run in seconds. In practice, checks 3-5 caught real bugs during development — edge cases where the LS reference was at the activity root instead of inside typeProperties, or where a dataset name appeared in both Synapse and non-Synapse contexts.
One Notebook, 37 Factories: Parameterization at Scale
The framework is a single notebook with widget parameters:
| Parameter | Purpose |
| factory_name | Which ADF factory to migrate |
| target_catalog | Destination Unity Catalog catalog |
| databricks_ls | Databricks linked service name in ADF |
| dry_run | Preview mode — no files written |
| notebook_base | Production notebook path for generated activities |
| workspace_url | Databricks workspace URL (for WebActivity endpoints) |
| sql_warehouse_id | SQL warehouse for Lookup-to-WebActivity pattern |
Changing factory_name is all it takes to migrate a different factory. The auto-discovery chain rebuilds the Synapse LS list, dataset list, and secret mappings from scratch for each factory.
The two-phase architecture is composed via %run:
%run "./Load_Clean_Pipelines" ← Phase 1 (normalize) # ... then Phase 2 (migrate) runs in the same notebook
This means a single “Run All” executes the complete pipeline: normalize → discover → classify → migrate → generate templates → validate → report.
Output Artifacts
For each factory, the framework generates:
- migrated_pipelines/all/ — Complete set of pipeline JSONs, deployable to ADF via CI/CD. Non-impacted pipelines pass through unchanged.
- migrated_pipelines/impacted/ — Only the pipelines that had activity-level changes (suffixed _UC for disambiguation during parallel deployment).
- notebooks/ — Template notebooks (generic ingestion framework, delta lookup query) parameterized to work with any source table.
- migration_report.json — Machine-readable report: every activity migrated, its original type, the pattern applied, source dataset, linked service, and sink type.
Lessons Learned
1. ADF’s REST API export and Git format are NOT the same
This was the single biggest time sink. We initially assumed we could migrate directly from the API export. The snake_case keys, flat activity structure, and missing properties wrapper meant every downstream tool — ADF portal import, CI/CD pipelines, validation scripts — rejected the output. Building the normalization phase as a separate, testable step was the right call.
2. User-defined identifiers hide in plain sight
The camelCase conversion bug was subtle. Everything looked correct until we tested a pipeline that used a variable called Config_Schema. The expression @{variables(‘Config_Schema’)} silently failed because the variable had been renamed to configSchema in the JSON. The fix — exempting user-defined container keys from conversion — required understanding ADF’s evaluation model, not just its JSON schema.
3. Sink-type preservation matters more than you think
Our first version replaced everything with Delta table writes. It worked — until we discovered downstream SSIS packages, Power BI dataflows, and third-party tools that consumed Parquet and CSV files at specific ADLS paths. Preserving the original sink format and output path was non-negotiable for a drop-in replacement.
4. Auto-discovery beats manual configuration every time
For the first factory, we hand-built a JSON config listing every Synapse LS and dataset. For factory #2, we realized the linked service JSON files already contain everything we need — the type, the connection string, the Key Vault reference. Scanning them programmatically eliminated a class of errors (missed datasets, typos in LS names) and made the framework truly zero-config.
5. Validation is not optional when you’re rewriting pipelines
We caught real bugs in production-bound output through automated validation: a linked service reference at the activity root instead of inside typeProperties, a dataset that appeared in both Synapse and non-Synapse contexts, and a passthrough pipeline that was accidentally modified by the normalization step. Five automated checks run in seconds and saved hours of debugging in ADF.
6. AI pair programming accelerated the iteration cycle
The framework was developed iteratively with AI coding assistance. The pattern was: describe the desired behavior → generate code → run against real data → inspect edge cases → refine. This was especially effective for the recursive tree walker (where getting the JSON path names right for each container type was fiddly) and the query classifier (where the combinatorial space of ADF expression patterns benefited from rapid prototyping). The human’s role was domain judgment: deciding which patterns to support, validating business rules, and making architectural choices the AI couldn’t infer from code alone.
Conclusion
Migrating ADF pipelines from Synapse to Databricks is not a lift-and-shift — it’s a targeted rewrite of specific activities within a larger orchestration graph. By treating pipeline JSON as a parseable tree, auto-discovering the migration scope from artifact metadata, and applying a pattern catalog with recursive traversal, we turned a months-long manual effort into a parameterized, repeatable, validated process.
The framework processed 37 factories, identified 245 impacted pipelines, and migrated 526 activities with zero manual JSON editing. Every output was validated against five automated checks before deployment.
Key Takeaways:
- Treat pipeline JSON as an AST, not a config file
- Auto-discover scope from the artifacts themselves — don’t rely on manual inventories
- Define a pattern catalog that maps source activity types to specific target implementations
- Preserve sink formats and ADF expressions — migration is not the time to change data contracts
- Validate aggressively: passthrough integrity, reference checks, false positive detection
- Parameterize everything — the 37th factory should be as easy as the first
Have questions about migrating ADF pipelines to Databricks? Reach out — we’ve seen the edge cases so you don’t have to.
