Azure Synapse Analytics & Azure Data Factory

ADF triggers, integration runtimes, dedicated SQL pool distribution and indexing, serverless SQL, CI/CD with adf_publish, and migration choices.

Same series as Parts 1-8. Structure: theory primer, then Q&A in tiers (core, subtleties, design/scenario), then a gotchas table. If ADF is part of your day-to-day, lean on real numbers and incidents from your own pipelines wherever a question invites it — that will out-perform any generic answer here.

Caveat, same as the Fabric doc: Synapse dedicated SQL pools are in a genuine legacy/migration phase (Microsoft is steering new workloads toward Fabric Warehouse), and some ADF feature availability shifts. Items marked [verify] are worth a quick docs check before the interview, especially given how fast this space has moved since your last hands-on touch on any particular sub-feature.

THEORY PRIMER

Two different things under one brand: ADF and Synapse

Azure Data Factory is a pure orchestration and data-movement service: pipelines, activities, triggers, integration runtimes. It doesn't store data itself.

Azure Synapse Analytics is a workspace that bundles several engines under one roof: dedicated SQL pools (provisioned MPP data warehouse), serverless SQL pools (on-demand, pay-per-query, T-SQL over files in a lake), Spark pools, and — because Synapse absorbed ADF's pipeline engine — Synapse Pipelines, which are functionally the same product as ADF pipelines with a different portal and a few Synapse-specific integrations (notebook activities, native SQL pool activities).

The practical consequence for an interview: "ADF vs Synapse Pipelines" is mostly a deployment/portal choice, not a different orchestration technology — the underlying pipeline JSON/activity model is the same lineage. The real architectural decision is whether you need Synapse's other engines (dedicated SQL, Spark, serverless SQL) tightly integrated in one workspace, or whether you're happy with ADF as pure orchestration talking to separately-provisioned compute (Databricks, a standalone SQL DB, etc.) — the common ADF + Databricks pattern.

ADF object model

Data Factory
 ├─ Linked Services       (connection definitions: auth + endpoint)
 ├─ Datasets              (schema/location pointer bound to a linked service)
 ├─ Pipelines
 │   └─ Activities         (copy, dataflow, notebook, stored proc, lookup, web, ...)
 │       └─ Activity dependencies (Succeeded / Failed / Completed / Skipped)
 ├─ Triggers               (schedule, tumbling window, event-based, manual)
 ├─ Integration Runtimes   (Azure IR, Self-Hosted IR, Azure-SSIS IR)
 └─ Data Flows             (Mapping Data Flow — Spark under the hood)

Integration Runtime (IR) — the compute abstraction underneath everything

Every activity runs somewhere, and the IR is what decides where:

• Azure IR: fully managed, serverless, regional. Default for cloud-to-cloud copy and Data Flow execution.
• Self-Hosted IR (SHIR): your own VM/on-prem machine running the IR agent — required whenever a source or sink lives behind a firewall/on-prem network with no direct cloud path (an on-prem SQL Server, a private VNet resource without a managed private endpoint).
• Azure-SSIS IR: a provisioned VM cluster specifically to lift-and-shift and run legacy SSIS packages inside ADF.

Dedicated SQL pool architecture (the MPP internals that get probed hardest)

Control node  →  distributes query across 60 Distributions
                       │
        ┌──────────────┼──────────────┐
   Compute node1   Compute node2  ... Compute nodeN
        │               │                │
   distributions    distributions    distributions
   (data + local     ...              ...
    compute)

Every table's rows are hashed (or round-robined, or replicated) into exactly 60 distributions, always — regardless of DWU (compute) level. Scaling compute (changing DWU) doesn't repartition data; it changes how many distributions each compute node owns (e.g. at DW100c, 1 node might own all 60; at DW6000c, 60 nodes each own 1). This is why data distribution/skew is a bigger deal than compute size for query performance — you can throw more DWU at a badly-distributed table and it still bottlenecks on whichever distribution holds the most rows.

Serverless SQL pool

No provisioning, no cluster — a shared, multi-tenant, pay-per-TB-scanned query service that runs T-SQL directly over files (Parquet, CSV, JSON, Delta-read-only) in a data lake, or over the same underlying storage a Spark pool wrote to. It's read-only against external data (no DML into the lake through it in the write sense you'd want for a warehouse), and its cost model (per-TB-scanned) makes file layout and pruning the single biggest cost lever, mirroring the Delta file-size/partition-pruning discussion from Parts 2/4/5.

Q&A TIER 1: CORE

1. ADF Pipelines, Activities, Datasets, Linked Services

Q: Explain the relationship between a Linked Service, a Dataset, and an Activity.

A: A Linked Service is the connection — auth details and endpoint (a storage account, a SQL server, an API). A Dataset is a named pointer to a specific piece of data reachable through that Linked Service — a table, a file path, a folder pattern — plus its schema, but it holds no rows itself. An Activity (Copy, Lookup, Stored Procedure, Data Flow, etc.) is the actual unit of work inside a pipeline, and it references Datasets (or, in newer inline-dataset patterns, references a Linked Service directly without an intermediate Dataset object) as its source/sink. This three-layer separation is what lets you reuse one Linked Service across many Datasets, and one Dataset pattern across many pipelines, rather than hardcoding connection strings into every activity.

Q: What are the activity dependency conditions, and how do you use them to build a failure-handling branch?

A: Succeeded, Failed, Completed (ran regardless of outcome), Skipped. A pipeline's on-failure branch is built by chaining a notification/cleanup activity off the main activity's Failed dependency — same conceptual pattern as Databricks Jobs' run_if: AT_LEAST_ONE_FAILED from Part 1. Completed is useful for a step that must always run (e.g. releasing a lock, logging an attempt) whether or not the preceding step succeeded.

Q: Parameters vs Variables in a pipeline — what's the actual difference?

A: Parameters are set at pipeline invocation and are immutable for the run — they flow in from a trigger, a parent pipeline's Execute Pipeline activity, or a manual trigger call, and every activity in the pipeline can reference them via @pipeline().parameters.x. Variables are mutable pipeline-scoped state, set via a Set Variable or Append Variable activity, used for things like accumulating a list across a ForEach loop or tracking a running flag mid-pipeline. The practical rule: if a value is decided before the pipeline starts and never changes, it's a parameter; if the pipeline itself needs to compute or update it as it runs, it's a variable.

2. Triggers

Q: Schedule trigger vs tumbling window trigger — when do you use which?

A: A schedule trigger just fires at wall-clock times (cron-like) — it doesn't know anything about "windows" of data, dependencies between runs, or catching up on missed runs; if the ADF service is down at the scheduled time, that firing is simply missed. A tumbling window trigger represents a fixed-size, non-overlapping time window (hourly, daily) with built-in dependency chaining (a window won't fire until a dependent window — in this pipeline or another) has completed, built-in retry per window, and critically, built-in backfill: you can create a tumbling window trigger with a start date in the past and it will automatically generate and run every historical window between then and now. This is the trigger type I'd use for anything where "did every day actually run, and can I cleanly re-run day N" matters — which in your Bronze/Silver/Gold pipelines is basically always.

Q: What's WindowStart/WindowEnd, and why does the tumbling window trigger's backfill behavior matter for idempotency (tie-back to Part 2)?

A: Every firing of a tumbling window trigger gets @trigger().outputs.windowStartTime / windowEndTime system variables representing that specific window's boundaries, which get passed as pipeline parameters into the incremental-load logic (WHERE updated_at >= WindowStart AND updated_at < WindowEnd). Because backfill just re-fires historical windows with their correct boundaries through the exact same pipeline logic, an idempotent pipeline (MERGE/overwrite-by-partition, same principle as Part 2's Delta MERGE) makes backfill trivial — re-running window N always converges to the same result regardless of how many times it's rerun, which is the entire point of designing both the trigger and the pipeline logic this way together.

Q: Event-based trigger — what does it actually watch, and what's the gotcha?

A: Fires on blob-created or blob-deleted events in a storage account (via Event Grid under the hood). Gotcha: it needs Event Grid resource provider registered on the subscription, and the trigger fires per blob path pattern — a very broad pattern on a high-churn container can generate a lot of trigger firings and pipeline runs, so the path filter needs to be scoped tightly (specific folder patterns, not a whole container) or you get an explosion of small, redundant pipeline runs and unnecessary capacity/cost.

3. Integration Runtime

Q: When exactly do you need a Self-Hosted IR, and what are its scaling/HA characteristics?

A: Any time source or sink data isn't reachable from Azure's public network directly — an on-prem SQL Server, a database inside a private corporate network without a site-to-site VPN/ExpressRoute plus private endpoint setup, or a SaaS system only reachable from a specific whitelisted on-prem IP. A single logical SHIR can be a node cluster (up to 4 nodes) for high availability and to scale out throughput — nodes share the workload and provide failover; losing one node doesn't kill in-flight jobs on the others. It's registered by installing the IR agent (MSI) on a Windows machine/VM and connecting it to the ADF/Synapse instance via an authentication key.

Q: SHIR is a single point of failure/bottleneck in a design — how would you mitigate that?

A: Multi-node SHIR cluster for HA and horizontal throughput scaling, sized (CPU/memory/network) for concurrent job count rather than assuming default VM sizing is enough — a common real-world failure is an undersized SHIR VM choking under concurrent Copy activities. Where the source can be reached via a managed private endpoint instead (many first-party Azure PaaS sources support this once proper VNet/private-link setup exists), that removes the SHIR dependency entirely, which I'd always prefer over maintaining a persistent VM if the option exists.

4. Mapping Data Flows vs Wrangling Data Flows vs Notebooks

Q: What is a Mapping Data Flow, technically, under the hood?

A: A visual, no-code transformation designer that compiles down to Spark code, executed on a Databricks-managed cluster that ADF provisions and controls behind the scenes — you never see or manage that cluster directly, but you pay for its spin-up (Time To Live / TTL settings reduce repeated cold-start cost across multiple activities in one pipeline run) and its execution. So performance tuning here is Spark tuning wearing a GUI: partitioning strategy on transformations, broadcast join hints for size-asymmetric joins (same concept as Part 4), and avoiding unnecessary wide transformations (sorts, aggregates) in the flow graph.

Q: Mapping Data Flow vs a Databricks notebook for the same transformation — how do you decide?

A: Mapping Data Flow: good for teams without strong Spark/Python skills, visually reviewable transformation logic, tight native integration with ADF pipeline parameters/triggers with no separate compute to manage. Databricks notebook (the usual default in an ADF + Databricks stack): full control over Spark tuning, reusable Python/PySpark logic, access to the whole Databricks ecosystem (Delta Live Tables/Lakeflow-style declarative pipelines, MLflow, Unity Catalog), and materially cheaper/faster at scale once you're optimizing seriously, because you control cluster sizing and reuse warm clusters across jobs rather than paying Data Flow's own provisioning overhead per activity. I'd reach for Mapping Data Flow only where the team explicitly wants a low-code option and volume/performance requirements are modest — for anything approaching the pipelines discussed in Parts 1-2, a notebook running on a properly-sized job cluster is the more scalable and controllable choice, which is why ADF + Databricks stacks are usually built that way rather than around Data Flows.

5. Dedicated SQL Pool: Distribution

Q: Explain the three distribution strategies and when to use each.

A:

• Hash distributed: rows distributed across the 60 distributions by a hash of one column. Use for large fact tables — pick a high-cardinality column that's frequently used as a join key (e.g. customer_id) so joins on that column are co-located (no data movement needed because matching rows already live on the same distribution).
• Round-robin: rows spread evenly, no logic — fastest, simplest loads, but any join on this table requires a shuffle (data movement) since there's no guarantee related rows share a distribution. Good default for a staging table that gets loaded then immediately reshuffled into a properly hash-distributed target, not for a query-serving table.
• Replicated: the full table copied to every compute node. Ideal for small dimension tables (comparable to a broadcast join target in Part 4) — every node has a local copy, so a fact-to-dimension join needs zero data movement for that side, exactly the broadcast-join reasoning applied at the storage layer instead of query-time.

Q: What's distribution skew and how do you detect and fix it?

A: Skew happens when the hash column has a very uneven value distribution (a few dominant values), so some of the 60 distributions end up holding far more rows than others — and because MPP query time is bounded by the slowest distribution to finish its local work, a skewed table makes every query against it slow regardless of DWU. Detect via DBCC PDW_SHOWSPACEUSED (or the equivalent system views) comparing row counts per distribution — a healthy table should have roughly even counts across all 60. Fix: choose a different, higher-cardinality, more evenly distributed hash column (this may mean the "obvious" business key isn't the right physical distribution key), or, if no single column distributes well, consider a composite/computed distribution column, or accept round-robin for that table and pay the shuffle cost at query time instead of permanent skew.

6. Dedicated SQL Pool: Indexing

Q: Clustered Columnstore Index (CCI) vs Clustered Index vs Heap — when does each apply?

A: CCI is the default and right choice for the overwhelming majority of large fact tables — columnar storage gives strong compression and is optimized for the scan-heavy, aggregate-heavy analytical query pattern a warehouse serves. Clustered (rowstore) index: better for a table that's frequently filtered down to a small number of specific rows via a highly selective lookup (point-lookup patterns), which columnstore isn't optimized for. Heap: fastest for loading, best for small staging tables that get loaded, immediately transformed, and dropped/truncated — not for anything serving repeated analytical queries. A common real mistake: leaving a CCI on a very small dimension table, where columnstore's compression/batch-execution advantages need enough rows (roughly over a million) to pay off — small tables are often better as a heap or clustered index, or just replicated (from question 5) since size is exactly what allows full replication to be cheap.

Q: What is a "deleted rows" / closed rowgroup problem with CCI, and how do you fix it?

A: Updates and deletes against a columnstore index don't rewrite compressed rowgroups in place — a delete just marks the row logically removed in a delete bitmap, and heavy update/delete activity against a CCI table over time leaves it full of rowgroups with a high percentage of "deleted" (but not reclaimed) rows, degrading scan performance because the engine still has to read and filter past them. Fix: ALTER INDEX ... REORGANIZE (or REBUILD for a fuller rewrite) periodically as maintenance, similar in spirit to Delta's OPTIMIZE/VACUUM cycle from Part 2 — this is the dedicated-pool analogue of that maintenance discipline, and it's the kind of thing a team new to dedicated pools often misses until performance has already degraded.

7. Dedicated SQL Pool: Workload Management

Q: How do you prevent one heavy query from starving the rest of the warehouse?

A: Resource classes (static: smallrc through xlargerc, or dynamic, scaled relative to current DWU) control how much memory a query gets and how many queries can run concurrently at that class — a user/query assigned a larger resource class gets more memory per query but the warehouse allows fewer such large queries running concurrently, since total memory is fixed at a given DWU level. Workload Management (Workload Groups + Workload Classifiers) is the more modern, granular control: define workload groups with reserved minimum resource percentages and max concurrency, and classifiers that route queries to a group based on login, label, or resource class — e.g. guaranteeing ETL loads always get a reserved slice of resources so they can't be starved out by a flood of ad hoc BI queries, and vice versa protecting BI responsiveness from a runaway batch job. This is the dedicated-pool version of the multi-tenant "don't let one job blast-radius the shared capacity" problem you saw in Fabric (Part 8) and in Databricks cluster/job concurrency design (Part 1).

8. Serverless SQL Pool

Q: What can and can't you do with a serverless SQL pool?

A: Can: run T-SQL SELECT queries directly against files (CSV, Parquet, JSON, and Delta tables in read-only fashion) in a lake using OPENROWSET or external tables, create views and (through CREATE EXTERNAL TABLE AS SELECT — CETAS) materialize query results back out as new files/Parquet in the lake. Can't: run arbitrary DML/DDL as an operational database, and it's not meant for interactive dashboard-level concurrency at low latency the way a dedicated pool or Fabric Warehouse is — it's a pay-per-query, scan-based exploration and light-serving layer, and cold-start/compile overhead per query is non-trivial for very short queries run at high frequency.

Q: How do you control serverless SQL pool cost, given it's billed per TB scanned?

A: File layout is the single biggest lever — Parquet over CSV (columnar pruning means a query touching 3 of 20 columns only scans those columns' data), reasonably sized files (not thousands of tiny files — same small-file cost story as every other engine in this series), and partitioning the underlying folder structure by the columns most commonly filtered on so a query's WHERE clause lets the engine skip whole partitions/files rather than scanning everything and filtering after the fact — this is the exact same partition-pruning discipline from Parts 2/4/5, just billed directly and visibly per query here rather than hidden inside a cluster-hour cost.

Q&A TIER 2: SUBTLETIES

9. PolyBase vs COPY INTO vs CTAS

Q: You need to load a large file from ADLS into a dedicated SQL pool table. What are your options and how do you choose?

A: COPY INTO is the modern, simplest, most flexible statement — T-SQL syntax, handles various file formats and error handling options directly, and is generally the recommended default today. PolyBase (external tables + INSERT ... SELECT from the external table, or the older bulk-load pattern) is the original mechanism, more setup (external data source, file format, external table definitions) but still relevant for certain formats/scenarios and for querying external data without loading it at all. CTAS (CREATE TABLE AS SELECT) is the fastest way to do a large transform-and-load within the pool — it's a fully parallel, minimally-logged operation and is the standard pattern for big in-warehouse transformations (rebuild a table with a different distribution strategy, or materialize a large aggregation) rather than row-by-row INSERT/UPDATE, which don't parallelize nearly as well in an MPP engine.

Q: Why would you rebuild a table via CTAS instead of just running ALTER TABLE ... UPDATE/an in-place data fix?

A: Because CTAS is a bulk, parallel, minimally-logged operation across all 60 distributions at once, while row-level UPDATE/DELETE against a large dedicated-pool table is comparatively slow and heavier on transaction log/tempdb — the standard pattern for "fix the distribution key" or "apply a large-scale transformation" in a dedicated pool is: CTAS into a new table with the corrected structure/distribution, verify, then rename tables to swap it in (RENAME OBJECT), rather than mutating the existing table row by row.

10. ADF CI/CD

Q: How does ADF's Git integration and deployment actually work, mechanically?

A: Two branches matter: your normal feature/collaboration branch (where you author pipelines in the ADF Studio UI, which commits JSON resource definitions to Git on save), and a separate, ADF-managed adf_publish branch, which only gets updated when you click Publish in the Studio — that publish step is what runs ADF's build validation and generates the ARM templates (ARMTemplateForFactory.json + a parameters file) representing the ADF resources as deployable infrastructure. CI/CD pipelines (Azure DevOps/GitHub Actions) then deploy from adf_publish's generated ARM templates into Test/Prod factories, parameterizing environment-specific values (linked service connection strings, key vault references) via the ARM template parameters file. The adf_publish branch + ARM template pattern is the standard; if you have run it, say so plainly in an interview — it shows you've operated it, not just read about it.

Q: What's the risk/gotcha with the manual "Publish" step, and how do teams work around it for a more automated pipeline?

A: Publish is a manual UI action by default, which means adf_publish's state depends on someone remembering to click it — easy to forget, and it also means ARM template generation historically happened through the ADF Studio's own build process rather than a pure CLI/CI step. The workaround: Microsoft's ADF utilities npm package (@microsoft/azure-data-factory-utilities) lets you run the same validation and ARM template export from the command line in a CI pipeline, directly from your main collaboration branch — bypassing the manual Publish/adf_publish branch UI dependency entirely for a fully automated release pipeline. This is the closer analogue to Databricks Asset Bundles' validate/deploy CLI flow from Part 1, and I'd bring this up specifically if asked how to make ADF deployment less manual — a lot of teams stop at the classic adf_publish pattern without knowing the CLI path exists.

Q: What commonly breaks when promoting an ADF pipeline from Dev to Prod?

A: Linked service connection strings/Key Vault references pointing at Dev resources instead of being parameterized per environment (the ARM template parameter file is exactly where these need to be templated, not hardcoded); a Self-Hosted IR referenced by name that doesn't exist (or isn't correctly network-reachable) in the target environment; global parameters or Key Vault-backed secrets not provisioned in the target Key Vault before deployment; and — a subtle one — dataset/linked service names need to match exactly what downstream pipelines expect, so a rename during Dev iteration that wasn't fully propagated breaks references silently until the pipeline actually runs.

11. Error Handling and Retries in ADF

Q: How do you configure retry behavior on an ADF activity, and what's the limitation?

A: Every activity has retry (count) and retryIntervalInSeconds policy settings directly in its definition — same principle as Databricks task retries from Part 1: use for transient failures (throttling, transient network errors), not logic bugs that will fail identically every retry. Limitation: activity-level retry is a flat count/interval, with no built-in exponential backoff or conditional "retry only on this error type" logic out of the box — for more nuanced retry logic (backoff, error-code-specific handling) you typically wrap the activity in an Until loop with your own wait/condition logic, or handle it in a downstream Databricks notebook/stored procedure where you have full programmatic control.

Q: How do you build a global error-handling pattern across many pipelines rather than repeating try/catch logic in each one?

A: A common pattern: every pipeline calls a shared "parent" orchestration pipeline wrapping the actual work pipeline in an Execute Pipeline activity, with the parent's Failed dependency path routing to a shared logging/alerting pipeline (writes to a central log table, sends a Teams/email/webhook notification) — this avoids duplicating the same failure-handling activities inside every individual pipeline, and centralizes what "a failure" means and how it's surfaced across the whole factory, similar in spirit to the dedicated on-failure branch pattern from Part 1's Databricks Workflows discussion.

12. Incremental Load Patterns Specific to ADF

Q: What incremental-load mechanisms does ADF give you natively, beyond hand-rolled watermark logic?

A: A few, layered by sophistication:

• Watermark-based incremental copy (the classic pattern): a Lookup activity reads the last high-watermark value from a control table, a Copy activity's source query filters WHERE updated_at > @watermark, then a Stored Procedure/Script activity advances the watermark after success — same pattern as Part 5's incremental-load SQL, expressed as pipeline activities.
• Native CDC support for certain sources (Azure SQL DB, SQL Server, others) — ADF can read the source's native change-tracking/CDC mechanism directly in a Copy activity rather than you building the watermark logic by hand.
• Copy Data tool's "Delta" wizard patterns — a UI-assisted setup for the common incremental cases, generating the pipeline/watermark scaffolding for you.

I'd still design the target-side idempotency (MERGE/upsert, not blind append) the same way regardless of which of these feeds it — the incremental extraction mechanism and the idempotent load mechanism are separate concerns, and getting the extraction "smart" doesn't remove the need for the load side to handle a reprocessed/duplicate batch safely (Part 2's core point, still true here).

13. Synapse Link

Q: What is Synapse Link and what problem does it solve?

A: Near-real-time replication of operational data (Azure SQL DB, Cosmos DB, Dataverse/Dynamics) into Synapse (into a dedicated pool or straight into the lake for serverless/Spark querying) without building an ETL pipeline and without impacting the operational source's transactional performance — for Cosmos DB specifically it works off the analytical store (a separate, column-oriented copy Cosmos DB maintains internally) so analytical queries never compete with the source's transactional request-unit budget. This is conceptually the direct ancestor of Fabric's Mirroring feature (Part 8) — same idea, earlier product generation, and worth naming that lineage if asked to compare the two platforms.

14. Security: Managed Identity, Private Endpoints, Purview

Q: How do you avoid embedding credentials in ADF linked services?

A: Managed Identity (system-assigned, one per Data Factory/Synapse workspace) authenticates to supported sinks/sources (ADLS Gen2, Key Vault, Azure SQL, Synapse dedicated pools) without a stored secret — grant the Data Factory's managed identity the appropriate RBAC role (Storage Blob Data Contributor, for example) on the target resource instead of storing a connection string or key. Where a source doesn't support managed identity auth, store the actual credential in Key Vault and reference it from the linked service via a Key Vault-backed secret reference, never a hardcoded value in the linked service JSON — same discipline as the secrets guidance in Part 3.

Q: How do you connect ADF to a source behind a private network without opening it to the public internet?

A: Managed Virtual Network with Managed Private Endpoints — ADF/Synapse provisions its own isolated VNet, and you create private endpoints inside it to specific PaaS resources (a storage account, an Azure SQL server), so traffic from ADF's Azure-IR-in-managed-VNet to that resource never traverses the public internet, without you having to build/maintain a full customer-owned VNet and SHIR. For non-Azure/on-prem sources, this doesn't apply — that's still the Self-Hosted IR's job (question 3).

Q: How does Purview fit in for lineage/governance across ADF/Synapse pipelines?

A: Purview scans data sources and can automatically capture pipeline-level lineage from ADF/Synapse Copy and Data Flow activities — showing which pipeline moved data from which source to which sink — plus data catalog/classification across the estate. This is the closer analogue of Unity Catalog's lineage feature from Part 3, but scoped to a data-catalog/governance product that spans beyond just one compute engine — worth mentioning that ADF/Synapse's native lineage visibility is comparatively shallow without Purview layered on top, unlike Databricks where Unity Catalog lineage is built into the same platform.

Q&A TIER 3: DESIGN AND SCENARIO

15. "Design a production ADF + Databricks medallion pipeline" (ties directly to Part 1)

Q: How does ADF's role change when Databricks (not Synapse Spark/dedicated pool) is the actual transformation engine — a very common setup?

A: ADF becomes purely the orchestrator and ingestion layer: tumbling window/schedule triggers own the "when," Copy activities land raw source data into Bronze (ADLS Gen2), and a Databricks notebook/Databricks Job activity does the actual Bronze→Silver→Gold transformation, invoked with baseParameters carrying the window/business-date values (exactly the ADF-to-Databricks parameter handoff described in Part 1, question 3). ADF's DQ-gate role is typically a Lookup/Script activity checking a control/reconciliation table the Databricks job wrote (Part 6's reconciliation pattern) before allowing the pipeline to proceed to the publish stage, rather than ADF doing the DQ logic itself — the heavy lifting (transformation and DQ evaluation) lives in Databricks/Spark, and ADF's job is sequencing, parameter passing, and failure branching around it.

Q: Why keep ADF in that role at all instead of using Databricks Workflows for everything, including the "when" (as Part 1 discussed as an alternative)?

A: Two honest reasons, not just inertia: first, when the platform has non-Databricks activities to coordinate too (a Copy from an on-prem SQL Server via SHIR, a downstream stored-procedure call into a different Azure SQL system, a wait-for-external-file trigger) — ADF gives one orchestration pane across all of that, whereas Databricks Workflows is scoped to the Databricks estate. Second, organizational: a platform team already invested in ADF pipeline patterns, existing Azure DevOps release pipelines for adf_publish, and a team with more ADF/Azure familiarity than deep Databricks-Workflows familiarity gets a smoother adoption path keeping ADF as the outer scheduler. If the estate were Databricks-only with no other systems to coordinate, I'd genuinely consider Databricks Workflows as the single scheduler (per Part 1) — this isn't a case where one is objectively correct, it's a call that depends on what else the platform has to touch.

16. Migrating a Dedicated SQL Pool Workload

Q: A company wants to move off a dedicated SQL pool. What are the options and how do you decide?

A: Depends on the actual reason for moving: (1) Fabric Warehouse — closest conceptual match (T-SQL, similar workload shape), fully managed, no more DWU/distribution management, but requires re-validating the T-SQL surface (Part 8's warehouse limitations) and rethinking distribution strategy since Fabric Warehouse doesn't expose the same explicit hash/round-robin/replicated controls. (2) Databricks SQL Warehouse over Delta, if the org is already investing in a lakehouse elsewhere (a common situation) — trades the MPP-specific tuning (distributions, CCI maintenance) for Delta/Z-Order/partition tuning (Parts 2/4), and unifies the warehouse and the lake into one storage layer instead of two. (3) Stay on dedicated pool if the workload genuinely needs its specific strengths (very mature, predictable, provisioned-capacity T-SQL workload with existing tuned distribution strategies that already work well) — migration for its own sake isn't free, and "we should modernize" isn't itself a sufficient reason without a concrete pain point (cost, feature gap, operational burden) driving it. I'd start any such conversation by asking what specifically is motivating the move — cost, feature limitations, consolidation with an existing lakehouse — because that answer picks the target, not a general preference for newer platforms.

17. Synapse vs Fabric vs "just ADF + Databricks" — likely comparison question

Q: If your platform runs ADF + Databricks rather than full Synapse, how would you frame the comparison if asked "why didn't you use Synapse/Fabric for this platform"?

A: Honest framing: ADF + Databricks + Terraform gives maximum control over the Spark layer (tuning, Unity Catalog governance, DABs-based CI/CD) with ADF doing what it's genuinely best at — coordinating a variety of activities and triggers — while not committing to a single vendor-managed compute+warehouse+BI bundle. Synapse's dedicated pool would add MPP-specific tuning overhead (distributions, CCI maintenance) for a workload that Databricks/Delta already serves well, and Fabric's shared-capacity model (Part 8) is a different operational trade-off (simpler ops, less granular tuning control, capacity-sharing risk across workloads) that makes more sense for a Power-BI-centric, less Spark-tuning-heavy organization than for a platform built around deep Spark/Delta engineering. None of this is "Synapse/Fabric is worse" — it's that the tool should match the team's skills and the workload's actual needs, and I'd say so plainly rather than positioning it as a one-sided technology preference.

GOTCHAS TABLE (fast review)

VOLATILE DETAILS: RE-CHECK BEFORE THE INTERVIEW

1. Current Microsoft guidance/roadmap framing for dedicated SQL pools vs Fabric Warehouse (messaging shifts as Fabric matures).
2. Serverless SQL pool's current Delta Lake read support maturity (write support, if any, and feature parity with Spark's Delta reader).
3. Exact current resource-class table (percent memory grant per class) and whether Workload Isolation/Workload Management defaults have changed.
4. ADF utilities npm package's current name/version and whether Microsoft has since shipped a more first-class CLI-native publish flow.
5. Current Synapse Link source list and whether Fabric Mirroring has effectively superseded it for new builds.
6. Managed VNet/Managed Private Endpoint current limitations and regional availability for ADF specifically (vs Synapse workspace).

Framing tip: if ADF is in your core stack, this is likely the topic where you have the deepest production mileage. Don't undersell it: when a question invites a "design it" or "how would you" answer, say what you actually built (for example an adf_publish + ARM template CI/CD flow) and what you'd change about it. That reads as far stronger than a clean textbook answer.