← Interview & exam prep
Analyst interview questions
Data system design interview questions
12 questions from beginner to advanced, each with the correct answer and a one-line explanation of why. Study them here, or take them as a timed test.
12 questionsBeginner 6 · Intermediate 6Answers + explanations
Beginner 6 questions
1In ELT (as opposed to ETL), transformation happens…
- In the BI tool at render time
- After loading, inside the target (warehouse/lakehouse)
- Before loading, in a separate engine
- Never — data stays raw
- Only in the source system
Show answerHide answer
B · After loading, inside the target (warehouse/lakehouse) ELT loads raw data first, then transforms it using the target's compute.
2A data warehouse is primarily optimized for…
- Real-time message queuing
- High-volume transactional writes (OLTP)
- Analytical queries (OLAP) over modeled, structured data
- Raw file storage only
- Front-end caching
Show answerHide answer
C · Analytical queries (OLAP) over modeled, structured data Warehouses serve fast analytical reads over curated, structured data.
3Batch processing (vs streaming) is the better fit when…
- Only for tiny datasets
- You can process data periodically in chunks and latency isn't critical
- You must react per event
- You need sub-second latency
- You never need history
Show answerHide answer
B · You can process data periodically in chunks and latency isn't critical Batch suits periodic, high-throughput jobs where some delay is acceptable.
4Partitioning a large table by date mainly helps to…
- Remove duplicate rows
- Slow down scans
- Increase storage used
- Let queries prune to only the relevant partitions
- Enforce primary keys
Show answerHide answer
D · Let queries prune to only the relevant partitions Partition pruning skips irrelevant data, cutting scan time and cost.
5Idempotency in a pipeline means…
- It runs faster each time
- It deletes source data
- It can only ever run once
- Re-running it yields the same result without duplicates or side effects
- It never fails
Show answerHide answer
D · Re-running it yields the same result without duplicates or side effects An idempotent job can safely retry — reruns don't double-count or corrupt data.
6A data lake typically stores…
- Only database backups
- Only fully modeled tables
- Only application logs
- Only cached query results
- Raw and semi-structured files at scale (schema-on-read)
Show answerHide answer
E · Raw and semi-structured files at scale (schema-on-read) Lakes hold raw/varied data cheaply; structure is applied on read.
Intermediate 6 questions
7To handle late-arriving or duplicate events reliably, a common pattern is…
- Overwrite the whole table each run
- Ignore duplicates
- Delete-then-insert with no key
- Append everything blindly
- Idempotent upserts (MERGE) keyed on a unique/business key
Show answerHide answer
E · Idempotent upserts (MERGE) keyed on a unique/business key Keyed upserts make loads idempotent, so retries and dupes don't corrupt the table.
8'Schema-on-read' means…
- It only works for CSV
- There is never any schema
- It only applies to OLTP
- Structure is applied when the data is queried, not when written
- Structure is enforced when data is written
Show answerHide answer
D · Structure is applied when the data is queried, not when written Lakes defer schema to read time, giving flexibility for varied/raw data.
9Incremental loading (vs full reload) is preferred to…
- Guarantee correctness in every case
- Simplify every pipeline
- Process only new or changed data, saving time and cost
- Remove all history
- Avoid partitioning
Show answerHide answer
C · Process only new or changed data, saving time and cost Loading just the delta scales far better than reprocessing everything each run.
10Change Data Capture (CDC) is used to…
- Take screenshots of dashboards
- Cache query results
- Validate schemas
- Compress files
- Capture row-level source changes (insert/update/delete) to propagate downstream
Show answerHide answer
E · Capture row-level source changes (insert/update/delete) to propagate downstream CDC streams source changes so downstream stays in sync without full reloads.
11Orchestration tools (e.g., Airflow, Data Factory) are used to…
- Write DAX measures
- Render dashboards
- Store the data itself
- Schedule tasks and manage dependencies between pipeline steps
- Replace the warehouse
Show answerHide answer
D · Schedule tasks and manage dependencies between pipeline steps Orchestrators coordinate order, dependencies, retries and scheduling of jobs.
12To scale a pipeline as data grows, a sound approach is to…
- Run it on one ever-bigger machine forever
- Add more report visuals
- Only reduce sampling
- Ignore the growth
- Partition and parallelize work, pushing compute to the data
Show answerHide answer
E · Partition and parallelize work, pushing compute to the data Distribute the work (partition/parallelize) and compute where the data lives.
What changed lately? Latest Microsoft Fabric news, releases & questions →
More analyst interview questions