Azure Scenarios & Design

The reasoning

**The control table is the design.** One row per table: `source_schema`, `source_table`, `load_type` (full or incremental), `watermark_column`, `last_watermark`, `target_path`, `enabled`, `batch_no`, and `last_run_status`. Adding table 501 is an `INSERT`, not a deployment. Keep it in an Azure SQL Database the factory reaches with its managed identity.

**Parent pipeline.** A Lookup reads the table list. A single Lookup returns at most **5,000 rows** and silently truncates beyond that, so 500 is fine; for larger estates, Lookup the batch numbers and let each child Lookup its own tables. A ForEach runs the child in parallel with `batchCount` of, say, 10 — the cap is 50 — chosen for what the source SQL Server and the self-hosted runtime can bear rather than for speed. You cannot nest ForEach loops, which is why the per-table work is an **Execute Pipeline** activity.

**Child pipeline, per table.** A Lookup gets the new high-water mark (`SELECT MAX(modified_at) FROM ...`). A Copy activity on the one parameterised SQL Server dataset runs `SELECT ... WHERE modified_at > '@{pipeline().parameters.last_wm}' AND modified_at <= '@{activity('GetNewWm').output.firstRow.new_wm}'` and writes Parquet to `bronze/<schema>/<table>/load_date=<date>/`. The copy runs on the **self-hosted IR** because the source is private; turn on the SQL Server partition option for the handful of large tables so one copy reads in parallel. Only on success, a Stored Procedure activity writes `new_wm` back and sets status; on failure, it logs the error and leaves the watermark untouched, so the next run picks the same range up again.

**Why bounded ranges.** Reading `> last_wm` without an upper bound lets rows committed during the copy slip past the watermark you record. Fixing the upper bound first makes every run a closed interval that can be rerun exactly. And the watermark column must be reliably set on update — if the application does not maintain `modified_at`, use Change Tracking or CDC for that table instead.

The reasoning

**Native CDC in mapping data flow.** For supported sources — SQL Server, Azure SQL Database and Managed Instance, SAP CDC, Cosmos DB, Snowflake — a data flow source can read changes using the database's own CDC, with no timestamp column required, and the sink can apply inserts, updates, upserts and **deletes** without an Alter Row transformation. ADF manages the **checkpoint** for you, keyed by pipeline and activity name. The catch Microsoft documents: rename the pipeline or activity and the checkpoint resets, so set an explicit **checkpoint key** on the Data Flow activity if you expect to refactor.

**The CDC factory resource.** The top-level Change Data Capture resource needs no pipeline at all: pick sources and targets, set a latency, and it runs continuously, billed for four cores of general-purpose data flow while it processes. It is the only continuously running option in ADF; pipelines are batch.

**Auto incremental extraction** in a data flow is the middle option for sources without CDC: you name an incremental column and ADF builds the delta query and tracks the checkpoint. It cannot see hard deletes, because a deleted row has no new timestamp.

**Tumbling window as the state.** If you only have a `modified_at` column and want no table of your own, a tumbling window trigger with a one-hour window passes `windowStart` and `windowEnd` into the copy query. The trigger remembers which windows succeeded, reruns a failed window on demand, and backfills from a past start time. For 200,000 changes a day, that is roughly 8,000 rows per hourly window — trivial per run.

Name the gap in the watermark-style options: none of them sees **deletes**. If deletes matter and CDC cannot be enabled, a periodic key comparison against the full key set is the only reliable fallback.

The reasoning

Microsoft documents the arithmetic: with a start time in the past, the trigger creates M = (now − start) / window size backfill runs — here 30 × 24 = **720** — and executes them in deterministic order **from oldest to newest**, honouring concurrency, and only then moves to future windows. The order cannot be changed. Microsoft's own advice for long periods is to do an initial historical load separately rather than backfill it window by window.

**Controlling it.** `maxConcurrency` (1 to 50) caps parallel windows; with 10, windows 00:00 to 09:00 of the first day run, then the next ten. Set it to what the source can take, not to 50. A `retryPolicy` retries failed windows with the same parameters (default count 0, interval at least 30 seconds). A `delay` shifts each window's start past its due time for late-arriving source data without changing its boundaries. And if each window depends on the previous one — a running total, for instance — add a **self-dependency** with offset `-01:00:00` so window N waits for N-1.

**The daily dependency.** Create a second tumbling window trigger with a one-day window on the aggregate pipeline and add a dependency on the hourly trigger with `size` `1.00:00:00` and offset zero. Each daily window then waits until all hourly windows covering that day have succeeded. If one hour fails, you rerun that hour; the daily window proceeds once it succeeds. A dependent window waits up to **seven days** before its run fails, and it cannot be cancelled while it is waiting on a dependency. A trigger can depend on at most **five** other triggers.

One constraint to say out loud: once published, a tumbling window trigger's `frequency` and `interval` cannot be edited. Changing hourly to 15-minute windows means deleting the trigger and creating a new one, which forgets which windows ran.

The reasoning

**Size it.** 2 TB is 16 terabits; at 200 megabits per second that is 80,000 seconds, roughly **22 hours**, before protocol overhead and before anyone else uses the link. Columnar Parquet with Snappy is usually far smaller than the source, so writing Parquet on the runtime machine and sending compressed data is the lever you control. If the arithmetic fails, the answer is a bigger link or an offline transfer device, and you say so rather than promising the weekend.

**The runtime.** A **self-hosted IR** is required because Oracle has no public endpoint. It runs only on Windows, makes only outbound HTTPS connections (so the firewall team opens nothing inbound), and needs the **Java runtime** installed to write Parquet. Put it close to Oracle on the network, give it enough CPU and memory, and register two or more nodes for high availability. Remember Data Integration Units do not apply here; throughput is the machine, the link and the concurrent-jobs setting.

**Parallelism.** Use the Oracle connector's **partition option** (physical partitions or dynamic range on a numeric key) so one Copy activity reads several ranges at once, and copy tables in parallel with ForEach. Microsoft notes that with a self-hosted runtime writing to ADLS Gen2 the effective parallel copies are about 4 or 5 per node, so scale out nodes rather than raising `parallelCopies` indefinitely. Watch the source: parallel range scans on a legacy Oracle during business hours are how migrations get stopped.

**Then the steady state.** Record a watermark per table at the moment of the initial extract, so Monday night's run copies only changes. Store the Oracle password in Key Vault and reference it from the linked service. Land raw Parquet in bronze by table and load date, and let Databricks build silver from there — the on-prem-to-Azure-to-Databricks shape the question is after.

The reasoning

Microsoft defines drift as columns arriving that are **not in the source projection**. With **Allow schema drift** checked on the source transformation, every incoming field is read at run time and passed through the flow; without it, extra columns are ignored or the flow fails when a projected column disappears. Drifted columns arrive as **strings** by default; **Infer drifted column types** makes ADF guess their types.

On the sink, check **Allow schema drift** too and switch on **Auto-mapping** so every incoming column is written. If auto-mapping is off, only columns matched by a **rule-based mapping** are written. In between, reference drifted columns in expressions with `byName('col')` or `byPosition(n)`, or use **column patterns** in Derived Column and Aggregate transformations, for example applying `trim()` to every string column whatever its name.

Choose the trade deliberately. Microsoft describes drift-enabled flows as **late-binding**: you lose design-time column lists and early type checking. For a generic bronze ingestion framework that is right — land whatever arrives. For a curated silver or gold load, keep a fixed schema and turn on **Validate schema**, so a surprise column fails loudly instead of flowing into a report.

Handle the target as well. A Delta sink with schema merging accepts new columns; a Synapse or Azure SQL table does not grow on its own, so the framework needs a step that detects new columns (compare the source's metadata with the target's) and alters the table or routes them to a staging area for review.

The reasoning

**Sequence.** Five Execute Pipeline activities, each with `waitOnCompletion` true, linked by **Upon Success**. If step 3 fails, steps 4 and 5 are skipped and the failure is visible at the step that caused it.

**Fan-out.** Twenty-five Execute Pipeline boxes on the canvas are unreviewable. Put the child pipeline names in an array parameter or a control table, and run a **ForEach** whose inner Execute Pipeline takes the child name dynamically — or, since Execute Pipeline needs a fixed reference, call one generic child that branches with a Switch activity on the name. Parallelism comes from `batchCount` (default 20, maximum 50); choose it for what the shared self-hosted runtime and source systems can bear. ForEach cannot be nested, so if a child itself loops, keep that loop inside the child.

**Join.** The Databricks Notebook (or a single Databricks job started from ADF) depends on **Upon Success** of both the fifth sequential step and the ForEach. A ForEach succeeds only if every iteration succeeds, so one failed child blocks the aggregation, which is what you want before publishing numbers.

**Failure handling and reruns.** Connect **Upon Failure** and **Upon Skip** from the final activity to one shared logging-and-alert step, the generic error-handling pattern Microsoft documents, so a failure anywhere is reported once. Each child should be idempotent so that, after a fix, the master can be rerun from the failed activity in the Monitor view without duplicating what already succeeded. And set the master pipeline's concurrency to 1 so an overlapping trigger cannot start a second copy while the first is still running.

The reasoning

Start with why it duplicates. The Copy activity into Azure SQL commits in batches; when it fails after 7 million rows, those rows stay. Rerunning the same append writes all 12 million again, leaving 19 million. Nothing in ADF undoes the partial write for you, so the rerun has to be designed to converge.

**Slice replace.** If the target is naturally partitioned by business date, set the Copy activity sink's **pre-copy script** to `DELETE FROM dbo.orders WHERE order_date = '@{pipeline().parameters.load_date}'` and reload the day. Run twice, same result. It needs the day to be fully reloadable from the source, and on very large slices a partition switch is kinder to the log than a big `DELETE`.

**Stage and merge.** Copy the day into `stg.orders` (truncated by the pre-copy script), then a Stored Procedure activity runs a `MERGE` on `order_id`. Rows already written are updated in place, missing rows are inserted, and reruns are harmless. This is also the right shape when late corrections for older days arrive in today's extract.

**Make the pipeline carry the day.** Drive the load from a tumbling window trigger or a `load_date` parameter, never from "now", so reprocessing 14 March on 17 March loads 14 March. Only advance any watermark after the write step succeeds. For file targets in ADLS, write to a date folder that the job deletes first, or to a temporary path that is swapped in on success, so readers never see half a day.

The reasoning

ADF itself keeps pipeline run data for only **45 days**, and its Monitor view is per factory. So create a **diagnostic setting** that sends `PipelineRuns`, `ActivityRuns` and `TriggerRuns` to **Log Analytics** in resource-specific mode, which writes the `ADFPipelineRun`, `ADFActivityRun` and `ADFTriggerRun` tables. Several factories can send to one workspace, which is how you get one view across dev, test and prod or across business units.

**Fast signal:** a metric alert on **Failed pipeline runs** greater than 0 — Microsoft lists it as a recommended ADF alert. Metrics are near real time and need no query.

**Useful signal:** a log alert on `ADFPipelineRun` returning failed runs in the last 15 minutes with `PipelineName`, `RunId` and `FailureType`, so the notification says what failed. Separate `UserError` from system failures; they need different people.

**The missing signal:** a scheduled query that lists the pipelines expected every day — from the control table or a static list — and returns any with **no Succeeded run in the last 26 hours**. A disabled trigger, a paused integration runtime or a trigger stopped during a deployment produce no failures at all, only absence. Deliver all alerts through an **action group** (email, Teams webhook, or an ITSM connector) and put the run URL in the message.

The reasoning

Microsoft documents how pipeline status is determined: evaluate every **leaf** activity (if a leaf was skipped, evaluate its parent), and the pipeline succeeds only if all of them succeed. With the copy followed only by an **Upon Failure** logging step, a failed copy makes the logging step run and succeed; it is the leaf, so the run is green. Microsoft names this the **Try-Catch** pattern and shows it produces Success in both cases.

If you add both **Upon Success** and **Upon Failure** paths (**Do-If-Else**), a failed copy skips the success branch, its parent failed, and the run fails. That surfaces the failure but is easy to break by editing branches. The explicit fix is a **Fail activity** at the end of the failure branch: log, then `Fail` with `message` set to `@activity('Copy orders').error.message` and an `errorCode` of your choosing. The run is red, alerts fire, and the message says why.

**Retries belong to the activity.** Each activity's policy has `retry` (default 0) and `retryIntervalInSeconds` (default 30). Set two or three retries on the Copy for throttling and network blips, so a transient error never reaches the failure branch. Tumbling window triggers add a trigger-level retry on top. Do not retry a non-idempotent write — retrying an append after a partial commit is how duplicates appear.

Remember that **Upon Completion** cannot coexist with Upon Success or Upon Failure on the same activity, and that a **best-effort** step such as optional logging belongs on Upon Completion so its own failure does not fail the load.

The reasoning

**Find the error.** The failed Databricks Notebook activity's output includes the run page URL; open it and read the actual Spark exception rather than ADF's wrapper message. Classify it: transient (cluster could not start, spot eviction, throttling on ADLS) or deterministic (bad data, schema change, code). This takes five minutes and decides everything else.

**Decide on the rerun.** A transient failure with an idempotent notebook — MERGE or partition overwrite, not append — can be rerun at once with **Rerun from failed activity** in the Monitor view, which keeps the successful copy. A deterministic failure needs a fix or a quarantine: if 300 bad rows broke the job, route them aside, load the rest, and flag the gap. Do not rerun a non-idempotent notebook to "see if it works".

**Communicate.** Tell the reporting owner at 04:45, not 06:05, what is late, the likely ETA and whether numbers will be partial. Month-end is a business event; a clear message is worth more than a heroic silent fix.

**Make the next one faster.** Pass `@pipeline().RunId` and the business date as `baseParameters` to every notebook; have each notebook write the ADF run ID, its own Databricks run ID, row counts in and out, and status to an audit Delta table, and return a small JSON summary through `dbutils.notebook.exit()` for ADF to log. Send ADF diagnostics to Log Analytics, alert on failures and on missing runs, and keep a runbook per critical pipeline naming the owner and the safe rerun action.

The reasoning

Microsoft breaks a copy into stages, and the bottleneck is the longest one. **Queue** is time waiting for the integration runtime to have capacity. **Transfer** splits into **time to first byte** (the source query before any rows come back), **listing source**, **reading from source** and **writing to sink**. Compare today's run with a normal run of the same activity; the stage that grew is the answer.

**Long Queue** on a self-hosted runtime: other jobs are using its concurrent slots. Check CPU and memory on the runtime nodes; if they are low, raise the concurrent jobs limit on the node (scale up); if they are high, add a node (scale out). Also check whether a new pipeline was scheduled into the same window.

**Long time to first byte:** the source is slow to start returning rows — a missing index on the watermark column, a table that grew, blocking on the source. Fix the query or its index. **Long reading:** enable the SQL Server connector's **partition option** so the copy reads ranges in parallel; check latency and bandwidth from the runtime machine. **Long writing:** throttling on the sink, or too many parallel copies; tune `parallelCopies` gradually, because Microsoft warns too many can hurt.

Check the performance tuning tips shown at the top of the copy monitoring view. And rule out the non-performance cause: a watermark that did not advance makes a delta copy a full copy, which looks exactly like a 9x slowdown with 9x the rows read.

The reasoning

Every file costs a listing entry, an open and a footer read, so 900,000 files of 2 MB make the planner and the executors spend minutes on overhead. **OPTIMIZE** rewrites them into larger files; Databricks autotunes the target to **256 MB** for tables under 2.56 TB. Run it once over the backlog, then on a schedule or let predictive optimization run it.

**Prevent it.** **Optimized writes** shuffle data before writing so each partition gets fewer, larger files; **auto compaction** compacts small files synchronously after a successful write, targeting 128 MB when set to true and autotuned when set to `auto`. Both are always on for MERGE, UPDATE and DELETE. On **Unity Catalog managed tables**, Databricks tunes file size itself and predictive optimization runs OPTIMIZE and VACUUM in the background. Also question the 15-minute trigger: if consumers need data hourly, an `AvailableNow` run each hour writes a quarter of the files.

**VACUUM** is housekeeping, not compaction. It removes data files no longer referenced by the log and older than the retention threshold — **7 days** by default — so after OPTIMIZE the old small files still sit in storage until VACUUM removes them. After VACUUM, time travel to versions older than the retention stops working.

Layout matters as much as size. If queries filter on `customer_id`, cluster on it: liquid clustering with `CLUSTER BY` is Databricks' recommendation for new tables and makes OPTIMIZE incremental. And avoid over-partitioning — a table partitioned by date and hour at this volume is how you get 2 MB files in the first place.

The reasoning

The dimension carries `customer_sk`, `customer_id`, the tracked attributes, `valid_from`, `valid_to` and `is_current`. For each changed customer you must update the current row (set `valid_to`, `is_current = false`) **and** insert a new current row. A MERGE applies at most one action per source row per target match, so feeding it the changes once can only do one of the two.

The standard fix is to **union the changes with themselves**: once with `merge_key = customer_id`, which matches the current row and closes it, and once with `merge_key = NULL` for customers whose attributes changed, which never matches and falls through to `WHEN NOT MATCHED THEN INSERT`. Brand-new customers come through the first branch, find no match, and are inserted. Compare a hash of the tracked columns so a customer re-sent unchanged produces no new version.

At 40,000 changes against 5 million rows, the cost is the join. Filter the target side to `is_current = true`, cluster or Z-order the dimension on `customer_id` so data skipping prunes files, and deduplicate the staged changes to one row per customer first — two changes for the same key in one batch make MERGE fail with multiple source rows matching the same target row.

Keep the load idempotent: land the day's extract in bronze, build the staged set from it, and run the MERGE; rerunning the same day finds every change already current and does nothing.

The reasoning

Auto Loader provides the `cloudFiles` Structured Streaming source. Discovered files are recorded in a RocksDB store in the **checkpoint**, which is what gives **exactly-once** ingestion into Delta and lets a failed run resume without reprocessing. Directory listing is the default detection mode; Databricks recommends **file notification mode** with file events for most workloads, which avoids listing a directory that grows by 50,000 files a day.

Setting `cloudFiles.schemaLocation` enables inference and evolution. For JSON, inferred columns are **strings** unless you set `cloudFiles.inferColumnTypes`; `cloudFiles.schemaHints` pins types you know. With the default `schemaEvolutionMode` **addNewColumns**, a new column makes the stream fail with `UnknownFieldException` after the schema has been updated; restart it — Databricks recommends a job configured to retry — and it continues with the column. `rescue` never evolves and never fails, putting new fields in `_rescued_data`; `failOnNewColumns` stops until someone acts.

The **rescued data column** is what makes "nothing gets lost" true: type mismatches, case mismatches and unknown fields go to `_rescued_data` as JSON with the source file path. In bronze keep everything; enforce types in silver, where rows with rescued content can be quarantined. Run it with `trigger(availableNow=True)` on a schedule if minutes-level latency is not required.

The reasoning

**Diagnose.** In the SQL and stage views, check the join strategy (sort-merge or broadcast), shuffle read and write per task, and spill. If the median task takes 20 seconds and three take 40 minutes, it is **skew** on a hot key. If every task is slow and spilling, the shuffle is too big for the cores and memory you have.

**Reduce the data first.** Select only needed columns and push date filters to the fact before joining; Delta data skipping only helps if the filter reaches the scan. A 30 million-row dimension trimmed to four columns may fall under a broadcastable size; AQE on Databricks switches sort-merge to broadcast at runtime below `spark.databricks.adaptive.autoBroadcastJoinThreshold`, 30 MB by default, and an explicit `broadcast()` hint is still worthwhile when you know the side is small.

**Skew.** AQE is on by default and splits skewed partitions in sort-merge joins when a partition exceeds both 5 times the median and 256 MB. It cannot help broadcast joins, or the right side of a left outer join. For a known hot key such as a default `customer_id = 0`, handle it separately or salt it.

**Layout and compute.** Cluster or Z-order both tables on the join key so matching files are co-located and skippable; check the shuffle partition count (`auto` lets Databricks size it); use a memory-optimised worker type if it spills; evaluate Photon for the job. Change one thing at a time and compare the stage metrics.

The reasoning

**Remove secrets first, store the rest.** For each linked service, ask whether the target accepts Entra ID. ADLS Gen2, Azure SQL and Synapse do: grant the factory's **system-assigned managed identity** the right role or database user and switch authentication to managed identity — there is then no secret at all. For on-premises SQL logins or third-party APIs, the secret moves to Key Vault.

**Key Vault in ADF.** Grant the factory identity **Get and List** secret permissions by access policy, or the **Key Vault Secrets User** role, create an Azure Key Vault linked service, and point each secret field at `AzureKeyVaultSecret` by name. Leave `secretVersion` empty so the latest version is used and rotation needs no redeploy.

**Releases.** Microsoft's CI/CD guidance is the key detail: use **one Key Vault per environment with identical secret names**, so the only parameter that changes between dev, test and prod is the vault URL, and production secrets need not be visible to developers. ADF does not store secrets in Git, which is another reason Key Vault references make ARM deployments simpler. If the vault firewall uses trusted-service bypass, it works only with the system-assigned identity.

**Databricks.** Create an **Azure Key Vault-backed secret scope** and read values with `dbutils.secrets.get()`, which redacts them in output. Microsoft documents that such scopes support only the vault access policy permission model, not Azure RBAC. Better still, access ADLS through Unity Catalog storage credentials so notebooks need no storage secret at all. Finally, **rotate** every key that was ever inline — it is in Git history and exported templates.

The reasoning

**ADF flow, as Microsoft documents it.** The dev factory is configured with Azure Repos or GitHub. Feature branch, debug, pull request into the collaboration branch (`main` by default). Publishing — manually, or automated with the `@microsoft/azure-data-factory-utilities` npm package in a build pipeline — generates `ARMTemplateForFactory.json` and a parameters file, by default into the `adf_publish` branch. Test and prod factories are **not** connected to Git and change only through the release.

**Release.** An Azure DevOps release deploys the ARM template to each environment with its own parameter file: linked service endpoints, Key Vault URL, integration runtime references. Microsoft's best practices: run the **pre- and post-deployment PowerShell script** to stop triggers before deployment and restart them after (the Ver2 script touches only modified triggers), keep integration runtimes with the **same name and type** in every environment, and keep one Key Vault per environment with the same secret names.

**What you cannot do.** ADF publishes everything in the collaboration branch — no cherry-picking or selective publishing. Microsoft suggests feature flags with global parameters and If Condition for work not ready for prod, and a hotfix branch for urgent fixes.

**Databricks.** Keep notebooks and Python in Git and describe jobs and pipelines as a **Declarative Automation Bundle** (formerly Databricks Asset Bundles): a `databricks.yml` with dev, test and prod targets, deployed by `databricks bundle validate` and `databricks bundle deploy -t prod` from the same Azure DevOps pipeline, authenticated as a service principal. The ADF release and the bundle deploy go together so a pipeline never calls a notebook version that does not exist yet.

The reasoning

**Arithmetic from documented limits.** 10,000,000 / 60 ≈ **166,700 events per second**. One Standard **throughput unit** allows ingress of up to 1 MB/s or 1,000 events/s, whichever comes first, and egress of 2 MB/s or 4,096 events/s; exceed it and ingress is throttled with **ServerBusy**. Ten TUs is 10,000 events/s — which is why producers throttle at a tenth of the target. Standard allows at most **40 TUs** per namespace and **32 partitions** per event hub, and Microsoft's rough figure is about 1 MB/s ingress per partition on Standard. On events alone that is at least 167 TUs, four times the tier's ceiling.

**Tier.** **Premium** buys processing units (up to 16 per namespace), with up to 100 partitions per event hub and 200 per PU at namespace level; Microsoft's indicative figure is ~5–10 MB/s ingress per PU, depending on the workload. **Dedicated** uses capacity units, 1,024 partitions per event hub and scales beyond 10 CUs by request. Which fits depends on payload size, so the honest answer is a load test against the target — Microsoft says as much. On Premium and Dedicated, partitions can be increased later but not decreased; on Standard they are fixed at creation.

**Partitions and consumers.** Partition count caps parallel consumers per consumer group — extra consumers sit idle — so pick it for peak processing parallelism. Key by device id to keep per-device order. Enable **Capture** to write Avro to ADLS for replay and history; it reads from internal storage and does not consume egress capacity.

**Processing and serving.** Stream Analytics windowed aggregates for simple real-time metrics, or Databricks Structured Streaming via the Kafka endpoint (Standard and above) into Delta for richer logic; serve dashboards from pre-aggregated tables, not from raw events. Add auto-inflate on Standard only as a stopgap: it raises TUs automatically but never past the tier ceiling.

The reasoning

**Measure.** Tag workspaces, clusters and pools by project and environment; tags flow to `system.billing.usage` and Azure Cost Management. ADF copy cost is DIUs × duration × unit price; Databricks is DBUs plus VMs; a dedicated pool bills DWUs every hour it is online, used or not.

**Databricks.** Microsoft's guidance: non-interactive jobs on **job compute** cost significantly less than on all-purpose compute, and a multi-task job can reuse one job cluster so startup is paid once. Set **auto-termination** on interactive clusters, enable **autoscaling**, use spot instances for workers while keeping the driver on demand, enforce it all with **compute policies**, and evaluate Photon per job — faster runs are only savings if they are also cheaper.

**Synapse.** A paused dedicated pool costs nothing for compute while storage persists, so pause it outside reporting hours or scale down after the morning load. If the reports are light, a serverless SQL pool over gold Delta files is billed per data processed and may cost less than keeping DW1000c online — provided files are Parquet or Delta and queries prune.

**ADF and design.** Leave DIUs on auto; 40 GB a day rarely needs more. Copy incrementally instead of full extracts, avoid mapping data flows for logic Databricks already runs, stop any idle Azure-SSIS IR, and if a stream only needs daily freshness, run it as `AvailableNow` on a schedule rather than 24/7. Put lifecycle rules on ADLS to cool old bronze data.

The reasoning

**Know where it is.** Microsoft Purview scans sources, including Unity Catalog, and classifies sensitive columns; Unity Catalog adds data classification and tags. The first deliverable is a list of every table holding the email — bronze, silver, gold, exports.

**Restrict access.** A **column mask** is a SQL function bound with `ALTER TABLE ... ALTER COLUMN email SET MASK`, returning the clear value to a privileged group and a masked value to everyone else; **row filters** do the same for rows. Databricks recommends **ABAC policies** on governed tags for consistency across many tables. Note documented limits: no masks on views, and time travel and clones are not supported on masked tables.

**Erase.** A `DELETE` on a Delta table with deletion vectors only marks rows as deleted. Microsoft documents the physical path: `REORG TABLE ... APPLY (PURGE)` rewrites the affected files, then `VACUUM` removes the old ones once they are past the retention threshold (7 days by default). Until then, time travel can still read the customer. Repeat for every copy, including raw bronze files, or pseudonymise identifiers at ingestion so bronze never holds them in clear.

**Prove it.** Log each erasure request, the tables touched and the VACUUM that completed it, and keep lineage current so a new downstream copy does not escape the process.

The reasoning

Databricks describes it as a data design pattern — recommended, not required — where data quality improves layer by layer, sometimes called a multi-hop architecture.

**Bronze** keeps the source's raw state, appended incrementally, with minimal validation; Databricks suggests storing most fields as strings, VARIANT or binary to survive schema changes and adding metadata such as the source file name. It is for engineers and audit, not analysts, and it is what lets you reprocess.

**Silver** is where schema enforcement, null handling, deduplication, late and out-of-order data, type casting and joins happen, keeping at least one validated, non-aggregated representation of each record. Databricks advises against writing silver directly from ingestion, because schema changes and corrupt records then break the silver load.

**Gold** is modelled for the business — dimensional models, aggregates, materialised views — and optimised for query performance, sometimes as separate gold layers per domain. On Azure the usual mapping is ADF or Auto Loader into bronze, Databricks for silver and gold, and Power BI or SQL on gold.

The reasoning

**ADLS Gen2** is a storage account with the hierarchical namespace: it holds Parquet and Delta files, secured with RBAC and ACLs. It computes nothing; both other services read and write it.

**Azure Databricks** is where most Azure teams run Spark: notebooks and jobs, Delta Lake, Auto Loader, Structured Streaming, Photon, and Unity Catalog for governance and lineage. It is the usual engine for bronze to silver to gold.

**Azure Synapse Analytics** brings a **dedicated SQL pool** (an MPP warehouse across 60 distributions), a **serverless SQL pool** that queries lake files by the data processed, Spark pools and ADF-style pipelines into one workspace. Teams use it for a T-SQL warehouse serving BI, or serverless views over gold Delta.

A common design uses all three: ADLS as the lake, Databricks to build it, and Synapse serverless or a dedicated pool — or, for new projects, Fabric, which Microsoft now recommends — to serve SQL consumers.

The reasoning

Microsoft documents five approaches. **Watermark**: a column with last-updated time or an increasing key, loading changes between the old and new watermark. **Change Tracking** in SQL Server and Azure SQL, which identifies inserts, updates and deletes. **LastModifiedDate** for files — simple, but ADF still scans every file to filter. **Time-partitioned folder or file names** such as `/yyyy/mm/dd/`, which Microsoft calls the most performant for new files. And **native CDC** in mapping data flows or the CDC factory resource, where ADF manages the checkpoint.

For the watermark pattern, the order matters: read the new maximum **before** copying, copy the closed range `> old AND <= new`, and update the watermark **only on success**. A failure then leaves the old watermark in place, so the next run repeats the same range — which is safe only if the write merges or replaces rather than appends.

Say its blind spot: a timestamp watermark never sees **hard deletes**. If deletes matter, use Change Tracking or CDC.

The reasoning

**Unity Catalog.** Create an access connector (a managed identity), grant it Storage Blob Data Contributor on the account, register it as a **storage credential**, and define an **external location** for `abfss://container@account.dfs.core.windows.net/`. Users and jobs then read paths or tables under it according to Unity Catalog grants, with access audited.

**Legacy but still seen.** A Microsoft Entra ID **service principal** configured with the `fs.azure.account.oauth2.*` Spark properties and its secret from a secret scope; **SAS tokens**; and **account keys**, which Databricks advises against because they grant full control of the account. Databricks' page on these patterns is marked retired.

**Deprecated.** Databricks no longer recommends **mounting** storage to DBFS or using **credential passthrough**. If your project still mounts, say so and say it is on the migration list — interviewers ask this to find out whether you know it changed.

The reasoning

**All-purpose compute** is for interactive development: notebooks attach to it, several people can share it, and it runs until terminated. Set auto-termination, or it bills idle time.

**Job compute** is created by the job scheduler for a run and removed afterwards. Microsoft's cost guidance says non-interactive workloads cost significantly less on job compute than on all-purpose compute, and each run gets a fresh, isolated cluster. A multi-task job can share one job cluster across tasks so the startup is paid once.

Other options: **serverless** jobs compute and **SQL warehouses** for SQL workloads, and **pools** of idle instances to cut start-up time. When ADF calls a notebook, choosing a new job cluster in the linked service gets job-compute pricing; pointing it at an existing interactive cluster does not.