Azure Conceptual

The reasoning

Start from what the integration runtime does. Microsoft lists four capabilities: running a **Data Flow** on managed Azure compute, **data movement** (the Copy activity), **activity dispatch** to external compute such as Azure Databricks, Azure SQL Database or HDInsight, and SSIS package execution. Only the first one transforms data inside ADF. Everything else either moves bytes or hands work to a different engine.

That gives two honest answers. **ETL in ADF** means a mapping data flow: you design joins, aggregates and derived columns visually, and ADF runs them on a Spark cluster it manages, billed per vCore-hour while the flow runs. **ELT in ADF** means a Copy activity lands source data unchanged in ADLS Gen2 or a Synapse staging table, and then a Databricks Notebook, Stored Procedure or Script activity transforms it where it sits. Most Azure platforms you will be asked about are the second shape: ADF lands bronze, Databricks or SQL builds silver and gold.

The distinction matters for cost and debugging. A mapping data flow needs a cluster to spin up, so a small transformation can spend more time starting compute than working; pushing the same logic into the target warehouse or an existing Databricks job often costs less. Conversely, a team with no Spark skills gets a lot from data flows because the logic stays visual and version-controlled in the factory.

Say the positioning too, because it is current: Microsoft's documentation now describes **Data Factory in Microsoft Fabric** as the next generation of Azure Data Factory and offers an upgrade path. It does not describe Azure Data Factory as retired, so an answer that says "ADF is deprecated" is wrong in the other direction.

The reasoning

Microsoft's own framing is the cleanest: an **activity** defines the action, a **linked service** defines the target data store or compute service, and the **integration runtime** is the bridge between them. A linked service holds how to connect — server, database, authentication method, a Key Vault secret reference, and `connectVia` pointing at the runtime. A **dataset** holds what to read or write inside that connection — a table name, a file path, a format such as Parquet — and always references exactly one linked service.

Without parameters, forty SQL databases mean forty linked services and hundreds of datasets. With them, you define **one** SQL Server linked service with `serverName` and `databaseName` parameters used in the connection string, and **one** generic dataset with `schemaName` and `tableName` parameters that forwards its own values to the linked service. A pipeline then supplies `@item().server`, `@item().database` and `@item().table` from a control table, and the same two objects serve every source.

Two details come up in follow-ups. Secrets do not become parameters: the password stays in Azure Key Vault and the linked service references it by secret name, which keeps the secret out of Git and out of the ARM template. And parameterised linked services are not supported identically by every connector, so check the connector page before promising one definition for Oracle, SAP and SQL Server alike.

Global parameters are a different thing again — factory-wide constants such as an environment name — and they are overridden per environment during CI/CD, not per activity run.

The reasoning

Microsoft documents the copy rule explicitly: copying between two cloud stores uses the Azure IR; copying between a cloud store and a store in a private network runs **on the self-hosted IR** if either linked service points at one; copying between two private stores requires both to use the same self-hosted runtime. So the SHIR machine reads SQL Server and writes to ADLS Gen2 over outbound HTTPS itself — it only makes outbound connections, which is why it works behind a corporate firewall.

Two consequences follow. **Data Integration Units do not apply**: DIUs (4 to 256) are an Azure IR concept, so throughput is set by the SHIR machine's CPU, memory, network and concurrent-job limit, and you scale it by adding nodes in active-active mode. And the SHIR must be installed on Windows, with the Java runtime present if it has to write Parquet.

The other activities resolve differently. **Lookup and Get Metadata** run on the integration runtime associated with the data store they read, so a Lookup against the on-premises control table also runs on the SHIR. **External transformation activities** such as Databricks Notebook are dispatched from the runtime of the compute linked service — a lightweight operation — and the Spark work happens on the Databricks cluster. **Mapping data flows** only ever run on an Azure IR.

The default Azure IR is **auto-resolve**: for a copy it tries to use an IR in the sink's region and falls back to the factory's region. If data must not leave a geography, create a regional Azure IR and point the linked services at it explicitly with `connectVia`.

The reasoning

Microsoft's comparison table is the answer. A tumbling window trigger supports **backfill** — windows in the past are scheduled — where a schedule trigger only runs from now on. It has a **retry policy** (default count 0, interval default and minimum 30 seconds) and retries automatically on throttling and server errors. It supports **concurrency** between 1 and 50 simultaneous windows via `maxConcurrency`. And it exposes `trigger().outputs.windowStartTime` and `windowEndTime`, where a schedule trigger only has `scheduledTime` and `startTime`.

The behaviour that matters most is that a tumbling window run **waits for the pipeline run and takes its status**: if the pipeline fails, that window is marked failed and can be rerun on its own. A schedule trigger is fire-and-forget — it is marked successful as soon as a pipeline run starts. That is why an hourly load is safer on a tumbling window: the query `WHERE modified_at >= @{pipeline().parameters.windowStart} AND modified_at < @{pipeline().parameters.windowEnd}` is deterministic, and rerunning 14:00–15:00 reloads exactly that hour.

The costs are real. A tumbling window trigger has a **one-to-one** relationship with a single pipeline, where schedule triggers are many-to-many. Its frequency can be minutes, hours or months with a minimum window of 5 minutes, and once published the `frequency` and `interval` cannot be edited — you delete and recreate it. It also supports dependencies on up to five other tumbling window triggers, including a self-dependency that stops window N starting until N-1 succeeded.

So the rule of thumb: schedule trigger for "run at 06:00 on weekdays", tumbling window for "process every hour of data exactly once, and let me rerun any one hour".

The reasoning

**Lookup** runs a query, stored procedure or file read and hands the result to later activities. With `firstRowOnly` (the default, true) you get `output.firstRow`; set it false and you get `output.value`, an array you pass to a ForEach. It returns **at most 5,000 rows** — anything beyond is silently dropped — fails if the output exceeds **4 MB**, and times out at 24 hours. **Get Metadata** returns properties of a dataset: `childItems` for a folder listing, `lastModified`, `size`, `exists`, `structure`. It is how you list the files that arrived before deciding what to copy.

**ForEach** takes an array in `items` and runs its inner activities per element, referenced as `@item()`. It is parallel by default (`isSequential` false) with `batchCount` defaulting to 20 and capped at **50**, and it accepts at most **100,000 items**. You cannot nest a ForEach inside another ForEach or an Until; the workaround is an Execute Pipeline activity inside the loop that calls a child pipeline with its own ForEach.

The silent limit is the dangerous one. A control table with 6,200 table entries read by one Lookup gives you 5,000 tables copied and a green pipeline. The fix is a two-level design: Lookup the distinct source systems or batches (a few dozen rows), ForEach over those, and have a child pipeline Lookup the tables for its batch. Also avoid **Set Variable inside a parallel ForEach** — variables are pipeline-scoped and not thread safe, so parallel iterations overwrite each other.

The reasoning

Downwards is plain. The child pipeline declares parameters such as `sourceTable` and `watermark`; the Execute Pipeline activity's `parameters` object maps expressions into them, for example `@item().table_name`. `waitOnCompletion` defaults to **true**, which is what you want when later steps depend on the child; set it false only for fire-and-forget fan-out, because the parent then cannot see the child's outcome.

Microsoft documents one trap: **Execute Pipeline passes an array parameter to the child as a string**, because the payload is serialised. A child that expects an array and calls `length()` on it gets the length of a string. Either declare the parameter as a string and wrap it in `json()` in the child, or pass a delimited list and `split()` it.

Upwards, you have two documented options. The newer one is the **Pipeline Return Value**: in the child, a Set Variable activity sets the system variable of that name (no declaration needed), and the parent reads it from the Execute Pipeline activity's output. Microsoft lists it as preview, so say so if you rely on it. The older, dependable pattern is to have the child write its result — rows copied, new watermark — to a control table or file, and have the parent Lookup it afterwards.

For a Databricks child the equivalent is different again: the notebook calls `dbutils.notebook.exit(value)` and ADF exposes it as `@activity('name').output.runOutput`, limited to 2 MB.

The reasoning

Get the history right first. **Data Lake Storage Gen1** was its own service, addressed as `adl://`, and Microsoft's documentation now states that it is retired and its resources are no longer accessible. Any answer that compares them as live alternatives is out of date; the useful point is that Gen2 replaced it by building the same file-system semantics into Blob Storage.

**ADLS Gen2 is a storage account with the hierarchical namespace (HNS) enabled.** Without HNS, a "folder" is only a prefix in blob names, so renaming or deleting a directory means touching every blob under it — millions of calls for a large table. With HNS, the directory is a real entry and the rename is a single metadata operation. Microsoft calls out exactly why this matters: Spark and Hive write job output to a temporary location and rename it on commit, and without HNS that rename can take longer than the job.

HNS also brings **POSIX-style ACLs** on directories and files, which is how you grant one team `r-x` on `/finance` and nothing else. You address it through the `dfs.core.windows.net` endpoint with the `abfss://` driver. Microsoft is explicit that once HNS is enabled you **cannot revert** the account to a flat namespace, and that some workloads — backups, image stores, anything whose organisation lives in a separate database — gain nothing from it.

So the decision is simple: analytics lake, medallion layers, Spark or Synapse reading it — Gen2 with HNS. Archive of objects addressed by ID from an application — plain Blob, with access tiers and lifecycle rules either way.

The reasoning

Microsoft documents the order precisely. For an Entra ID principal, Azure first checks for a **role assignment** (and any ABAC conditions on it). If a role such as **Storage Blob Data Reader, Contributor or Owner** grants the operation, access is granted and **ACLs are never consulted**. Only if no role grants it are the **ACLs** evaluated. The consequence, in Microsoft's words: you cannot use an ACL to restrict access that a role assignment has already granted.

That dictates the design. Give broad data roles only to identities that genuinely need the whole container. For folder-level access, give the principal **no data role** (at most the management-plane Reader role so it can see the account), and grant ACLs instead: `--x` (execute) on every parent directory down the path, and `rwx` or `r-x` on the target folder. Set **default ACLs** on the folder too, or new files written tomorrow will not inherit the permission.

Always put **Entra security groups** in ACL entries, not individual users or identities. Microsoft recommends it because ACL entries are copied onto every child: removing one user later means rewriting the ACL across the whole tree, where removing them from a group is instant. There is also a hard limit of **32 entries per ACL** (effectively 28 usable), separately for access and default ACLs. For the scenario asked: add the factory's managed identity to a `lake-writers-sales` group with `rwx` on `/bronze/sales`, and analysts to a `lake-readers-gold` group with `r-x` on `/gold`.

Note what bypasses all of this: **Shared Key** and account or service **SAS** authorisation carry no identity, so RBAC and ACLs have no effect on them. Disabling shared-key access on the account is part of making the model above actually hold.

The reasoning

A **service principal** is the identity of an app registration in Entra ID. To use it you hold a client ID plus a client secret or certificate, which you must store somewhere, rotate before it expires, and keep out of Git. A **managed identity** is also a service principal, but Azure manages the credential: the resource asks the platform for a token and never handles a secret.

Data Factory supports two kinds. The **system-assigned** identity is created with the factory (automatically when you create it in the portal or PowerShell), shares its lifecycle, and is deleted with it. A **user-assigned** identity is a standalone resource you can attach to several factories and keep across rebuilds; in ADF you reference it through a credential object. One documented catch: **trusted-service bypass** on a storage account or Key Vault firewall only works with the system-assigned identity.

In a pipeline the managed identity does three jobs. It authenticates the **Key Vault linked service**, so connection secrets that cannot be removed live in Key Vault rather than in the factory. It authenticates directly to stores that support Entra auth — ADLS Gen2, Azure SQL, Synapse, the Databricks and Web activities — so for those there is no secret at all. And it is what you grant RBAC roles and ACLs to, which makes access auditable per factory.

Microsoft adds a warning worth repeating: anyone with Data Factory Contributor can build a pipeline that uses the factory's identities and inherits every permission they hold, so grant those identities the narrowest roles on the narrowest scope.

The reasoning

**Active Directory Domain Services** dates from Windows 2000. It organises users, groups and computers into domains and organisational units, manages Windows machines with Group Policy, and authenticates with Kerberos and NTLM over the corporate network. On-premises services run as AD service accounts or group managed service accounts.

**Microsoft Entra ID** is identity as a service. It has users and groups too, but no domain join in the AD sense and no Group Policy; apps authenticate with **OAuth 2.0, OpenID Connect and SAML**, and access is governed with Conditional Access, MFA and Entra roles. Microsoft's comparison describes it as the cloud control plane for app access. Its equivalent of a service account for workloads is the **managed identity**, whose lifecycle Entra ties to the resource.

Hybrid estates connect them: **Microsoft Entra Connect** synchronises on-premises identities into Entra ID so one person has one sign-in. That does not make an on-premises SQL Server accept Entra tokens, which is the practical point for a data engineer: a factory's managed identity can authenticate to Azure SQL, ADLS or Key Vault, but reaching an on-premises SQL Server through a self-hosted runtime still uses SQL or Windows authentication, with the credential kept in Key Vault.

The reasoning

**Unity Catalog** records table and column lineage for queries and notebooks run on Unity Catalog-enabled compute, and exposes it in Catalog Explorer and the `system.access.table_lineage` and `system.access.column_lineage` system tables. It sees what Databricks runs, and nothing that happens before data lands in a table it governs.

**Microsoft Purview** (the product sold as Azure Purview before the rename) is the estate-level catalogue. Its Data Map scans sources such as ADLS Gen2, Azure SQL and Synapse. **Data Factory pushes lineage** to it for Copy, Data Flow and Execute SSIS package activities once you connect the factory and grant its managed identity the **Data Curator** role on the root collection. Purview also has an **Azure Databricks Unity Catalog** connector that extracts catalogues, schemas, tables and views and fetches lineage from those system tables.

The gap is the boundary. Microsoft documents that when an external service such as an ADF pipeline triggers a Databricks notebook, the lineage in Purview **does not reflect that dependency**: you see ADF's copy into bronze and Databricks' notebook from bronze to silver as separate graphs. Column lineage also does not appear for notebooks run from SQL warehouses. So a complete answer names both tools and says where you add the missing edge — usually by pointing both at the same scanned ADLS path so the asset is shared.

The reasoning

**Dedicated SQL pool** (formerly SQL DW) separates storage from compute. You size compute in DWUs, from DW100c on one compute node to DW30000c on 60, and every table is spread across **60 distributions** regardless of size. You can **pause** it, which releases compute so DWU charges drop to zero while data stays intact, and scale it, which cancels running queries and rolls back open transactions first. It supports tables, indexes, materialised views, DML and workload management: a warehouse in the full sense.

**Serverless SQL pool** is an endpoint every Synapse workspace gets automatically. It reads Parquet, Delta and delimited files in ADLS (plus Cosmos DB analytical store and Dataverse) through `OPENROWSET` and external tables, and Microsoft describes its billing as pay-per-use: you are charged only for the data your queries process, with nothing reserved. It has **no local storage**, so it does not support tables, DML, triggers or materialised views; the one way it writes is **CETAS** (`CREATE EXTERNAL TABLE AS SELECT`) back to the lake. Queries have a fixed timeout you cannot change.

Choose by workload shape. Dedicated fits a curated star schema that hundreds of dashboards hit all day, with predictable concurrency and data you want indexed and distributed. Serverless fits ad-hoc exploration of bronze files, a thin "logical warehouse" of views over gold Delta tables for Power BI, and one-off transformations where paying per query beats paying per hour. Because serverless bills per data scanned, Parquet or Delta with partition pruning and column projection is what keeps it cheap; querying raw CSV scans everything.

Say where Microsoft points new work, briefly: the Synapse pages now carry a note recommending Fabric Data Warehouse for new warehousing projects and an upgrade path for dedicated pools. They do not say dedicated pools are retired.

The reasoning

A hash-distributed table assigns each row to a distribution with a deterministic hash of the distribution column, so equal values always land together. Microsoft recommends it for tables larger than **2 GB** on disk and tables with frequent DML. When two large facts join on the same hash column of the same type, with an equality join, **no data moves**. A **round-robin** table spreads rows evenly with no regard to value; it is the default and loads fastest, but every join against it needs a shuffle. A **replicated** table keeps a full copy on every compute node, which removes the broadcast move for joins — Microsoft's guidance is for tables under **2 GB compressed**, and it warns that every DML change triggers a rebuild of the copies on the next query.

Choosing the hash column is a trade between skew and movement. Pick a column that has **many unique values**, **few or no NULLs** (all NULLs hash to one distribution), is used in `JOIN`, `GROUP BY`, `DISTINCT` or `OVER`, and is **not** used mainly in `WHERE` filters and **not a date**. The date case is Microsoft's own example: filtering on today sends all the work to one distribution, so the query is as slow as that single distribution. For a fact table, a customer or order key usually wins; since compatibility level 50, up to eight columns can form the key to smooth skew further.

Check it after loading. `DBCC PDW_SHOWSPACEUSED('dbo.FactSales')` returns rows per distribution; Microsoft says up to about 10% variation has no noticeable effect. In query plans, look for **ShuffleMove** and **BroadcastMove** steps in `sys.dm_pdw_request_steps` — those are the data movement you are trying to design away.

You cannot alter a distribution column. The fix is `CREATE TABLE AS SELECT` into a new table with the right key, recreate statistics, and swap with `RENAME OBJECT`.

The reasoning

Microsoft's loading guidance says the fastest and most scalable ways into a dedicated pool are **PolyBase external tables and the COPY statement**, and that "for the most flexibility when loading, we recommend using the COPY statement". With PolyBase you create an `EXTERNAL DATA SOURCE`, an `EXTERNAL FILE FORMAT` and an `EXTERNAL TABLE`, then CTAS from it. COPY does the same parallel read with one statement and a target table.

The pattern for 1 TB a day is ELT into staging. Land files in ADLS Gen2 as **Parquet**, or as many similarly sized compressed CSVs rather than one large gzip, because a single gzip file cannot be split across readers. `COPY INTO stg.Sales FROM 'https://account.dfs.core.windows.net/raw/sales/2026/09/14/' WITH (FILE_TYPE = 'PARQUET', CREDENTIAL = (IDENTITY = 'Managed Identity'))` loads into a **round-robin heap staging table**, the fastest load target, and a CTAS or `INSERT ... SELECT` moves it into the hash-distributed columnstore fact.

Two things decide throughput more than the statement: the pool's DWU level (loads use more readers and writers as you scale) and the resource class of the loading user. Scaling up before the nightly load and back down afterwards is a documented pattern.

In Data Factory, a Copy activity with a Synapse sink offers PolyBase or COPY statement modes; when the source is not already in a supported format or store, ADF uses **staged copy** through Blob or ADLS first. Name that, because the follow-up is usually why the pipeline wrote temporary files you did not ask for.

The reasoning

Microsoft describes Fabric as an analytics platform delivered as **software as a service**, with workloads for Data Factory, Data Engineering (Spark), Data Science, Data Warehouse, Databases, Real-Time Intelligence and Power BI operating over **shared compute and storage**. The storage is **OneLake**: one logical lake per tenant, built on ADLS Gen2, where the warehouse stores data natively in the open **Delta** format so Spark, SQL and Power BI read the same files without copies.

What changes for a Synapse team is the operating model. There are no storage accounts, resource groups or DWU sliders to manage per workload; you buy capacity and workloads draw on it. The Fabric warehouse separates compute and storage and handles physical optimisation itself — Microsoft's migration guide notes that indexes and similar source-system optimisations are taken care of automatically — so the distribution design you tuned in a dedicated pool is not something you carry over.

Migration is documented rather than automatic. Microsoft publishes a **Migration Assistant for Fabric Data Warehouse** and lists type differences, for example `datetime` to `datetime2`, `nvarchar` to `varchar`, `money` to `decimal(19,4)`, and no `datetimeoffset`, so time-zone offsets must move to a separate column. Some T-SQL must be rewritten. On the integration side, Microsoft calls Data Factory in Fabric the next generation of ADF and documents an upgrade path from ADF.

The accurate summary for an interview: Fabric is where Microsoft directs new analytics work; Synapse and ADF remain documented products with upgrade paths into Fabric. Claiming Synapse is deprecated is a claim Microsoft's documentation does not make.

The reasoning

Every Fabric tenant gets **one OneLake**, organised as tenant, then workspaces, then items such as lakehouses and warehouses. Microsoft says it is built on ADLS Gen2 and that all Fabric workloads store and read through it, which is what enables zero-copy reuse between engines.

A **shortcut** behaves like a symbolic link: an object in OneLake whose target path is somewhere else — another workspace, **ADLS Gen2**, Blob Storage, Amazon S3, S3-compatible storage, Google Cloud Storage, Dataverse, OneDrive or SharePoint, Iceberg tables, or on-premises storage through the data gateway. In a lakehouse's **Tables** folder you can create shortcuts only at the top level, and a target in Delta format is recognised as a table automatically, with schema changes synchronised. In the **Files** folder, any level and any format is allowed.

Security and deletion are where people are surprised. ADLS and S3 shortcuts authorise through a **cloud connection** bound when the shortcut is created, whereas internal OneLake shortcuts use the calling user's identity. Deleting the shortcut leaves the target untouched, but deleting a **file inside** the shortcut deletes it in ADLS if the connection has write permission. Caching, which cuts egress for cross-cloud reads, applies to GCS, S3, S3-compatible and gateway shortcuts, for 1 to 28 days, and not to files over 1 GB.

Limits worth knowing: up to 100,000 shortcuts per item, up to 10 on a single OneLake path, and chains of shortcuts at most 5 deep.

The reasoning

Microsoft splits a workspace into two planes. The **control plane** holds the backend services Databricks manages, including the web UI, notebooks metadata, job scheduling and cluster management, and it lives in the **Azure Databricks account**, not in your cloud account. The **compute plane** is where data is processed, and it comes in two forms.

With **classic compute**, clusters are virtual machines created in each workspace's virtual network **in your Azure subscription**. That is why VNet injection, network security groups and your own firewall rules apply to them, and why they read your ADLS Gen2 over your network. With **serverless compute** — serverless SQL warehouses, serverless jobs and notebooks — the machines run in a **serverless compute plane in the Databricks account**, in the same region as the workspace, isolated per workspace by Databricks' network boundary. Connectivity to your storage is then configured with network connectivity configurations and private endpoints rather than your VNet.

A **classic workspace** also has a **workspace storage account in your subscription** holding workspace system data (job results, notebook revisions, cluster logs), the legacy DBFS root and, for workspaces enabled for Unity Catalog automatically, the default workspace catalog. Microsoft's warning is blunt: do not delete or modify it, or the workspace cannot be recovered. Serverless workspaces use Databricks-managed default storage instead.

Above workspaces sits the **account**, which owns identities and **Unity Catalog metastores**. One metastore can be attached to several workspaces in the same region, so they share one governed view of catalogues, schemas and tables.

The reasoning

ADF's case is **reach**. A typical pipeline copies from an on-premises SQL Server through a self-hosted runtime, lands Parquet in ADLS, runs a notebook, then calls a stored procedure in Azure SQL and emails on failure. Databricks has no equivalent of the SHIR or the Copy activity's connector catalogue, and a tumbling window trigger with per-window reruns is an ADF feature. The Notebook activity passes `baseParameters` in and gets a value back through `dbutils.notebook.exit()` as `output.runOutput`, capped at 2 MB.

Databricks' case is **the Spark DAG**. A **Lakeflow Job** runs many tasks with dependencies, and Microsoft's cost guidance notes that a multi-task job can **reuse one job compute** for all tasks so the startup cost is paid once; ten ADF Notebook activities each configured with a new job cluster pay it ten times. Jobs also offer file-arrival triggers, and when one task fails you can repair the run from that task instead of restarting the pipeline.

There is a governance angle too. Microsoft documents that when an external service such as ADF triggers a Databricks notebook, Purview does not show that dependency, so the lineage graph breaks at the boundary. Keeping the transformation chain inside one Databricks job keeps its lineage whole in Unity Catalog.

The pragmatic answer is both: ADF for ingestion and cross-system control flow, and a single Databricks job for the transformation stage, which ADF starts as one step (for example through the Jobs API from a Web activity authenticated with the factory's managed identity) instead of calling ten notebooks one by one.

The reasoning

Delta records, for every data file, statistics such as minimum, maximum and null count per column. **Data skipping** compares a query's filter with those ranges and skips files that cannot contain matches. It works only for columns with statistics — for external tables the first 32 columns by default, adjustable with `delta.dataSkippingNumIndexedCols` or `dataSkippingStatsColumns`; Unity Catalog managed tables with predictive optimisation choose columns by query pattern instead.

**OPTIMIZE** rewrites many small files into fewer large ones; Databricks autotunes the target, 256 MB for tables under 2.56 TB and rising towards 1 GB above that. **ZORDER BY (col)** does the same rewrite but orders rows along a Z-order curve so that each file covers a narrow range of `col`, which makes the min and max statistics selective. Without it, every file holds every customer and skipping skips nothing.

Choosing columns: pick columns used often in filters and with **high cardinality**; Databricks documents that effectiveness drops with each additional column and that Z-ordering a column without statistics wastes compute. Z-order is also **not idempotent** — rerunning it on new data rewrites again. Date is usually better handled by partitioning or clustering than by Z-order.

The current recommendation changes the answer's ending. Databricks' documentation says **liquid clustering replaces partitioning and ZORDER**, recommends it for all new tables, and states clustering is not compatible with partitioning or Z-order. You declare `CLUSTER BY (customer_id, event_date)` — up to four keys — or `CLUSTER BY AUTO` on Unity Catalog managed tables, run OPTIMIZE incrementally, and can change the keys later without rewriting existing data.

The reasoning

Without `TIMESTAMP BY`, a job uses **arrival time** — for Event Hubs input, `EventEnqueuedUtcTime` — which only ever increases, so ordering settings are ignored. With `TIMESTAMP BY event_time` the job uses **application time**, and needs rules for events that do not arrive in order.

Two configurable policies apply. The **late arrival tolerance** compares event time with arrival time: if an event arrived more than the window after it happened, it is dropped or its time is adjusted into the window. The default is **5 seconds** and the maximum is **20 days**; Microsoft suggests starting around 5 minutes for IoT devices with drifting clocks. The **out-of-order tolerance** sets how far behind the watermark an event may be and still be reordered; the default is **zero**, and raising it delays every output by that amount because the watermark trails the maximum event time by the tolerance. A third, fixed rule drops events whose time is more than **5 minutes ahead** of arrival.

Adjusted events keep their original payload field but get a new `System.Timestamp()`, which is how you spot them. Windowed aggregates — tumbling, hopping, sliding, session and snapshot windows — emit only at the **end** of the window, and Stream Analytics does not emit partial or revised aggregates, so a dropped late event is simply missing from that window. `TIMESTAMP BY ... OVER device_id` gives each device its own watermark.

"Failed records" in the output sense are a separate policy, and it covers only data-conversion errors — output events that do not fit the sink's schema. **Retry**, the default, retries with no timeout and blocks every later event behind it; **Drop** discards the event, which cannot be recovered. Transient errors such as network failures are always retried, whatever the policy. The durable answer for replay is the input, not the job — Event Hubs retains events (up to 7 days on Standard) and Capture can archive them to ADLS for reprocessing.

The reasoning

Microsoft defines the IR as the bridge between an activity and a linked service, providing four capabilities: **data flow**, **data movement**, **activity dispatch** and **SSIS package execution**. The linked service names which IR it connects through with `connectVia`.

The **Azure IR** is fully managed and serverless. It copies between cloud stores, runs mapping data flows (the only IR that can), and dispatches activities such as Databricks, Stored Procedure and Web. It scales copy throughput through Data Integration Units, 4 to 256, and by default auto-resolves its region. With a **managed virtual network** it reaches stores over private endpoints.

The **self-hosted IR** is software you install on Windows machines inside your network or a VNet. It is required when a store has no public line of sight — on-premises SQL Server, a database behind a firewall — or needs a bring-your-own driver such as SAP HANA or MySQL. It makes only outbound HTTPS connections, scales out across several nodes in active-active mode, and cannot run data flows.

The **Azure-SSIS IR** is a managed cluster of VMs that runs existing SSIS packages with the catalogue in Azure SQL Database or Managed Instance. You stop it when idle to control cost. Synapse pipelines support only the Azure and self-hosted types.

The reasoning

The **schedule trigger** runs pipelines on a wall-clock recurrence, including calendar rules such as "Monday 17:00 and Thursday 21:00", with a time zone that adjusts for daylight saving on daily and longer recurrences. It has a **many-to-many** relationship with pipelines and is fire-and-forget.

The **tumbling window trigger** fires for a series of fixed-sized, non-overlapping, contiguous windows from a start time, which can be in the past for backfill. It has a one-to-one relationship with a pipeline, retries, a concurrency limit of 1 to 50, window start and end variables, and dependencies on other tumbling window triggers.

The **event-based trigger** comes in two flavours. A **storage event trigger** fires when a blob is created or deleted in a storage account, delivered through Azure Event Grid, with path filters on prefix and suffix. A **custom event trigger** fires on events published to an Event Grid custom topic. Microsoft warns that stopping and restarting an event trigger resumes its old pattern and can fire unwanted runs, so delete and recreate it instead.

Separately, any pipeline can be run **manually** or through the REST API, PowerShell and SDKs, which is how external schedulers start ADF.

The reasoning

A Synapse **workspace** is tied to a primary ADLS Gen2 account and offers several engines under one studio. **Dedicated SQL pools** (formerly Azure SQL Data Warehouse) are MPP warehouses sized in DWUs, storing tables across 60 distributions and able to pause. The **serverless SQL pool** endpoint exists in every workspace and queries Parquet, Delta and CSV in the lake, charged by data processed. **Apache Spark pools** run notebooks and jobs.

**Synapse pipelines** use the same engine as Data Factory, with one documented difference: they support only the Azure and self-hosted integration runtimes, not Azure-SSIS. Security is Entra ID based, with workspace RBAC roles on top of storage permissions.

Position it honestly. Microsoft's Synapse pages now recommend Fabric Data Warehouse for new warehousing work and document an upgrade path for dedicated pools, but they do not describe Synapse as retired. An answer that says so, and says which pool you used and why, lands better than a list of every feature.

The reasoning

Before it, each workspace had its own **Hive metastore** and access was enforced by table ACLs per workspace, or not at all when people read storage paths directly. Two workspaces meant two permission models and no record of which job built which table.

Unity Catalog moves governance to the **account**. A **metastore** is created per region and attached to workspaces there; data is addressed as `catalog.schema.table`. Tables and volumes are **managed** (Unity Catalog owns the files and their lifecycle) or **external** (it governs files at a path you own). Access to your ADLS Gen2 goes through **storage credentials** — typically an Azure Databricks access connector's managed identity — and **external locations**, instead of keys in notebooks. Microsoft states workspaces created after 9 November 2023 are enabled for it automatically.

The benefits interviewers want named: **one permission model** with `GRANT` on catalogs, schemas and tables across workspaces; **row filters and column masks**, or tag-based ABAC policies, for fine-grained access; **automatic lineage** to column level; **audit logs** in system tables; and sharing across organisations through Delta Sharing.

The reasoning

A Delta table is a directory of **Parquet** files and a `_delta_log` folder of numbered JSON commits, periodically compacted into Parquet checkpoints. A reader builds the current table by replaying the log to find which files are live. A writer commits by atomically adding the next log entry; if two writers conflict, one retries or fails. That is what makes writes **atomic and isolated** — a failed job leaves no half-written files visible.

From the log come the features: **time travel** (`VERSION AS OF`, `TIMESTAMP AS OF`), **MERGE, UPDATE and DELETE** (by rewriting files, or by recording deletion vectors instead of rewriting), **schema enforcement** that rejects mismatched writes, and opt-in **schema evolution**. Per-file statistics in the log drive **data skipping**.

Plain Parquet has none of it: two jobs writing the same folder can corrupt each other, a failed job leaves partial files that readers pick up, and a correction means rewriting a whole partition by hand.

Two retention facts come up next. `VACUUM` removes unreferenced files older than **7 days** by default, after which time travel to older versions stops working; log files are kept for **30 days** by default. With deletion vectors, a GDPR delete is only physical after `REORG TABLE ... APPLY (PURGE)` followed by `VACUUM`.