When do you use a covering index / clustering key versus rewriting the query?

If the query already filters a selective column and still scans everything, storage layout (partition, cluster, index) is the lever. If it selects * and joins at the wrong grain, rewrite first. Do not add a clustering key to hide a CROSS JOIN.

EXPLAIN says Hash Join. When do you want Nested Loop instead?

When one side is tiny and indexed (point lookups), nested loop + index can beat building a hash. Warehouse SQL usually hashes; OLTP Postgres often nested-loops. Say “probe side size,” not “hash is always faster.”

Broadcast join vs shuffle join. What is the failure mode of a hint?

Broadcast sends the small side to every executor. If it is not actually small, you OOM workers. AQE can convert automatically. A forced broadcast hint on a growing dimension is a time bomb. Measure the small side after filters.

Cloud / productivity · Interview pack

Microsoft data interview pack

21 written questions with answers, the loop shape people report for Microsoft, and 2 SQL coding labs to practise. Built from DataLane's original question bank and publicly discussed interview themes, not leaked exams. Last editorial update 2026-08-02.

What is in this pack

Questions by topic

SQL2
Coding2
Warehouse1
Cloud15
AI / ML1
SQL coding labs2

7 easy · 11 medium · 3 hard

Rounds (1)

Platform

Themes: Fabric vs Synapse, OneLake, Purview

Free with a DataLane account

The first 3 questions are below. Sign in (free, no plan needed) to read all 21 and download the pack as a PDF.

Free preview

1. SQL · hard · SQL
When do you use a covering index / clustering key versus rewriting the query?
If the query already filters a selective column and still scans everything, storage layout (partition, cluster, index) is the lever. If it selects * and joins at the wrong grain, rewrite first. Do not add a clustering key to hide a CROSS JOIN.
2. SQL · hard · SQL
EXPLAIN says Hash Join. When do you want Nested Loop instead?
When one side is tiny and indexed (point lookups), nested loop + index can beat building a hash. Warehouse SQL usually hashes; OLTP Postgres often nested-loops. Say “probe side size,” not “hash is always faster.”
3. Coding · medium · Spark
Broadcast join vs shuffle join. What is the failure mode of a hint?
Broadcast sends the small side to every executor. If it is not actually small, you OOM workers. AQE can convert automatically. A forced broadcast hint on a growing dimension is a time bomb. Measure the small side after filters.

18 more questions and 2 SQL labs are in the full pack.

The Microsoft loop

Platform
What you migrate last from Synapse. Shortcuts vs copies. Who owns the semantic model.

SQL (2)

hard · SQL
When do you use a covering index / clustering key versus rewriting the query?
If the query already filters a selective column and still scans everything, storage layout (partition, cluster, index) is the lever. If it selects * and joins at the wrong grain, rewrite first. Do not add a clustering key to hide a CROSS JOIN.
hard · SQL
EXPLAIN says Hash Join. When do you want Nested Loop instead?
When one side is tiny and indexed (point lookups), nested loop + index can beat building a hash. Warehouse SQL usually hashes; OLTP Postgres often nested-loops. Say “probe side size,” not “hash is always faster.”

Coding (2)

medium · Spark
Broadcast join vs shuffle join. What is the failure mode of a hint?
Broadcast sends the small side to every executor. If it is not actually small, you OOM workers. AQE can convert automatically. A forced broadcast hint on a growing dimension is a time bomb. Measure the small side after filters.
easy · Spark
UDF vs Spark SQL expr for a string normalize. Which do you merge?
expr / built-in functions. Python UDFs kill Catalyst and Photon. If you must UDF, pandas/arrow vectorized, and measure. “It was faster on my laptop” is not a plan.

Warehouse (1)

medium · PostgreSQL
A dashboard is slow. First tool?
EXPLAIN (ANALYZE, BUFFERS). Name seq scan vs index, then decide. Adding an index without a plan is cargo cult.

Cloud (15)

medium · Azure
ADF, Synapse, and Fabric — who owns the pipeline?
ADF moves and orchestrates (IR, linked services). Synapse is the older SQL/Spark analytics plane. Fabric is the SaaS lakehouse (OneLake, warehouse, pipelines). Do not run the same load in all three. Pick the plane the company already pays for, then the service.
easy · Azure
Self-hosted IR versus Azure IR. When is on-prem the only answer?
Azure IR talks to public PaaS. A self-hosted IR sits inside the VNet / on-prem so you can reach SQL Server that has no public endpoint. “We opened 1433 to the internet so we could use Azure IR” is the wrong answer.
easy · Azure
Incremental load in ADF without a CDC product. What is the watermark pattern?
Store last_success_ts (or an increasing id) in a control table. Source query: WHERE updated_at > watermark. On success, update the watermark in the same transaction story as the load (or you skip / duplicate). This is still the most common DP-203 answer.
easy · Azure
OneLake shortcuts vs copying data into Fabric. When do you shortcut?
Shortcut when the source of truth stays in ADLS / S3 / another workspace and you only need to query it. Copy when you need a Fabric-native table, different lifecycle, or a transform that must own the files. Shortcuts are not a backup.
medium · Azure
Synapse serverless vs dedicated SQL pool. Sixty seconds.
Serverless: per-TB scanned, great for exploration on a lake. Dedicated: provisioned DWU, predictable BI, you pay for the pool whether you query or not. Mixing both for the same gold table without a reason is how the bill surprises finance.
medium · Azure
How do you keep secrets out of an ADF pipeline JSON?
Azure Key Vault linked service + secret references. Never commit connection strings in the repo. Managed identity to Key Vault and to Storage. This is a hygiene question; failing it ends the loop.
medium · Azure
Purview vs “the pipeline wiki.”
A catalog with owners and classifications beats a Confluence page that rot. If you cannot name the producer, you cannot page.
easy · Azure
Mapping data flow on 2 TB vs a Databricks job.
ADF mapping can work; at serious Spark scale many teams still submit a Databricks/Synapse Spark job and let ADF only orchestrate.
medium · Azure
Private endpoints vs opening 1433 to Azure IR.
Self-hosted IR or private endpoints. Opening SQL to the internet is the wrong answer in 2026.
easy · Azure
Fabric capacity is idle on weekends. Lever?
Pause / scale down / don’t run dedicated compute you do not use. Same FinOps story as Synapse DWU.
medium · Azure
ADF Copy of 2 TB with a self-hosted IR on one VM. What saturates first?
The VM NIC or disk, not “Azure.” Scale out SHIR nodes, or land files in ADLS and transform with Spark. Mapping Data Flows on 2 TB is how the bill and the runtime both surprise you.
medium · Azure
Fabric Direct Lake vs Import. When is Direct Lake the wrong choice?
When the lake table is not Delta/Iceberg-shaped for the semantic model, or you need a frozen extract for a board meeting. Direct Lake still needs a clean gold grain. Import is not “legacy” if the model is 400 MB.
hard · Azure
Synapse dedicated pool DISTSTYLE EVEN on a 1 TB fact joined to a 20-row date dim. What happens?
The date dim should be replicated; the fact should distribute on a join key used in large joins. EVEN + broadcast of a huge table is the classic “why is this 2 hours” story.
easy · Azure
Event Hubs Capture vs a Spark Structured Streaming job. How do you choose?
Capture for “land Avro/Parquet to ADLS, batch later.” Streaming job when you need watermarks, joins, or a 2-minute mart. Capture is not a stream processor.
medium · Azure
Purview scan never sees your gold tables. First three causes?
Wrong collection/scope, the identity cannot list the catalog, or tables live in a workspace Purview is not connected to. A wiki page is not a scan. Fix the connection before buying another catalog.

AI / ML (1)

medium · AI / RAG / MLOps
Why do you store the embedding model version next to every vector?
Embeddings from model A and model B are not comparable. A silent model upgrade without a backfill mixes spaces and tanks retrieval. Version the model, the chunker, and the source document hash. This is data versioning, not ML trivia.

SQL coding labs

Problem statements only. Run them against tests on DataLane; solutions live in the pad.

medium · about 6 min · tables: employees
Who reports to Ben
The employees table is an org tree: manager_id points back at employee_id in the same table. Return Ben’s direct reports — one level down, not the whole subtree.
https://www.datalane-data.blog/coding/problems/reports-to-ben/
hard · about 14 min · tables: employees
Salaries above the department average
Return employees earning more than their own department’s average, and show that average beside them. The trap: compute the average with a window and filter it in WHERE, and the average is recomputed over only the surviving rows.
https://www.datalane-data.blog/coding/problems/salary-above-dept-average/