SQL and modelling
Explain the difference between a fact and a dimension table. They are checking whether you have modelled anything. Answer with a concrete example, and mention grain — the level of detail one row represents. Candidates who never say the word “grain” tend to be repeating a definition.
How would you handle a slowly changing dimension? Name the types, then say which you would use and why. Type 2 preserves history at the cost of table size; Type 1 overwrites. The follow-up is usually about how you would query “as at” a past date.
A query that ran in 30 seconds now takes 20 minutes. Walk me through it. Structure the answer: has data volume changed, has the plan changed, has an index been dropped, is there skew. They want a diagnostic process, not a guess.
Spark and Databricks
What is a shuffle and why should you care? The single most reliable separator between people who have used Spark and people who have read about it. Explain data movement across partitions, and why wide transformations are expensive.
How do you handle data skew? Salting, broadcast joins for small tables, and repartitioning. Best answered with an actual incident if you have one.
Explain Delta Lake’s transaction log. How ACID works on object storage, and what time travel actually reads.
When would you not use Spark? An honest “when the data fits in memory on one machine” answers this well. Interviewers like candidates who do not reach for the biggest tool by reflex.
Azure Data Factory
How do you make a pipeline idempotent? Watermarking, upserts rather than appends, and designing so a rerun cannot double-count. This question separates production experience from tutorials.
Integration runtime — which type and when? Azure IR for cloud sources, self-hosted for on-premises or private networks.
How do you handle secrets? Key Vault with managed identity. Any answer involving a connection string in a parameter is a fail.
Production judgement
Your pipeline failed at 2am. What happens next? They want to hear about alerting, a runbook, whether downstream consumers are notified, and how you would backfill safely.
How do you know your data is correct? Row counts, null checks, referential integrity, and freshness checks that run as part of the pipeline rather than as an afterthought.