Data system design interview questionsAnalyst interview questions
← 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
Take it as a timed test
Free PDF
All 389 questions & answers, in one file

DAX, Excel, Power BI, Fabric, certification practice and analyst interviews — every answer with the reason behind it. Print it, study it offline.

Instant download, plus the free Tuesday briefing. Unsubscribe in one click.

Beginner 6 questions

1In ELT (as opposed to ETL), transformation happens…

  1. In the BI tool at render time
  2. After loading, inside the target (warehouse/lakehouse)
  3. Before loading, in a separate engine
  4. Never — data stays raw
  5. 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…

  1. Real-time message queuing
  2. High-volume transactional writes (OLTP)
  3. Analytical queries (OLAP) over modeled, structured data
  4. Raw file storage only
  5. 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…

  1. Only for tiny datasets
  2. You can process data periodically in chunks and latency isn't critical
  3. You must react per event
  4. You need sub-second latency
  5. 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…

  1. Remove duplicate rows
  2. Slow down scans
  3. Increase storage used
  4. Let queries prune to only the relevant partitions
  5. 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…

  1. It runs faster each time
  2. It deletes source data
  3. It can only ever run once
  4. Re-running it yields the same result without duplicates or side effects
  5. 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…

  1. Only database backups
  2. Only fully modeled tables
  3. Only application logs
  4. Only cached query results
  5. 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…

  1. Overwrite the whole table each run
  2. Ignore duplicates
  3. Delete-then-insert with no key
  4. Append everything blindly
  5. 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…

  1. It only works for CSV
  2. There is never any schema
  3. It only applies to OLTP
  4. Structure is applied when the data is queried, not when written
  5. 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…

  1. Guarantee correctness in every case
  2. Simplify every pipeline
  3. Process only new or changed data, saving time and cost
  4. Remove all history
  5. 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…

  1. Take screenshots of dashboards
  2. Cache query results
  3. Validate schemas
  4. Compress files
  5. 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…

  1. Write DAX measures
  2. Render dashboards
  3. Store the data itself
  4. Schedule tasks and manage dependencies between pipeline steps
  5. 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…

  1. Run it on one ever-bigger machine forever
  2. Add more report visuals
  3. Only reduce sampling
  4. Ignore the growth
  5. 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