Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 36 ยท Projects and Practice

Practice Exercises and Interview Questions

Hands-on data engineering practice: SQL and pandas exercises (dedup, sessionization, SCD2), design questions, trade-off discussions and interview answers to rehearse.

You learn data engineering by doing, and you prove it in interviews by explaining your choices. This lesson has three parts: coding exercises with runnable starting points, design questions to talk through, and short model answers to common interview questions. Try each exercise before reading the hint.

Part 1: Coding exercises

Exercise 1: latest record per customer

Given several versions of each customer, keep only the latest. Edit the code until the assertion passes.

Python Exercise 1: dedup keeping the latest version

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Exercise 2: sessionize clickstream events

A new session starts when a user is idle for more than 30 minutes. Assign session numbers per user.

Python Exercise 2: sessionization

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The pattern (flag a boundary, then cumsum) solves many "group consecutive rows" problems.

Exercise 3: find missing days (gaps)

Detect dates with no data in a daily table, a typical freshness and completeness check.

Python Exercise 3: missing partitions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Exercise 4: SQL, top product per category

Python Exercise 4: top-1 per group in SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Challenge: change it to the top two products, then handle ties with RANK() or DENSE_RANK().

Exercise 5: apply an SCD Type 2 change

Given the dimension below and a changed source row, close the old version and insert a new one. (Compare with your own solution from the SCD lesson.)

Python Exercise 5: Type 2 update with SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Part 2: Design questions to talk through

Practice answering these aloud in four steps: clarify (volume, latency, consumers), sketch (a diagram), detail (storage, processing, failure handling), trade-offs.

  1. Design a pipeline that ingests 200 million clickstream events per day and serves a dashboard with at most 5 minutes of delay.
  2. Your finance team says last month's revenue changed between two dashboard views. How do you investigate and prevent it?
  3. A source system will be replaced next year. How do you design the pipeline so the replacement is cheap?
  4. Move a nightly 6-hour batch job under 1 hour. What do you measure first?
  5. Build a customer 360 table from CRM, billing and support data with different customer ids.
  6. A pipeline must delete a user's data within 30 days of a request. Where is the data, and how do you find and delete it?
flowchart LR
  A["Clarify<br/>volume, latency, users"] --> B["Sketch<br/>sources to serving"]
  B --> C["Detail<br/>formats, partitions, failures"]
  C --> D["Trade-offs<br/>cost, freshness, complexity"]

Part 3: Common interview questions with model answers

What is idempotency and why does it matter? A job is idempotent if repeating it with the same input yields the same final state. Retries, restarts and backfills are routine, so idempotency (partition overwrite, upserts, deterministic keys, logical-date parameters) prevents duplicates and wrong totals.

Batch versus streaming: how do you choose? Start from the latency the business actually needs. If hourly or daily is fine, batch is simpler, cheaper and easier to debug. Use streaming when decisions or user features depend on seconds-level freshness (fraud, alerts, personalization), and accept the added complexity of state, ordering, late data and exactly-once semantics.

Why Parquet instead of CSV? Columnar layout, typed schema, compression and statistics: queries read only needed columns and skip row groups, files are much smaller, and types do not drift between loads. CSV stays useful for exchange and debugging.

What causes the small-files problem and how do you fix it? Frequent small writes (streaming, over-partitioning) create many tiny files; per-file overhead dominates reads. Fix by batching writes, coarser partitions, scheduled compaction to 128-512 MB files and table-format maintenance.

Explain slowly changing dimensions. Type 1 overwrites, Type 2 adds a versioned row with validity dates (full history), Type 3 stores a previous value. Choose per attribute based on whether history matters for reporting.

How do you handle late-arriving data? Separate event time from processing time, use lookback windows or watermarks, make loads idempotent so recent partitions can be recomputed, and reconcile periodically.

How do you ensure data quality? Contracts with producers, checks at ingestion, after transforms and before publishing (null, unique, accepted values, relationships, freshness, volume), quarantine bad rows with reasons, severity levels, alerts with runbooks and post-incident tests.

What is a lakehouse? Open table formats (Delta, Iceberg, Hudi) on object storage add ACID transactions, schema evolution, time travel and upserts to Parquet files, so one copy of data supports both BI and ML workloads.

How would you reduce a high warehouse bill? Find the top spenders by query log, cut scanned bytes (columns, partition filters, pre-aggregates), auto-suspend and right-size compute, tier or delete old storage, process incrementally, and add tags, budgets and alerts.

Python Self-check: classify these failures

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A study plan

  1. Re-do the batch ETL and mini lakehouse projects without looking, then change the data to break them.
  2. Practice SQL windows, joins and CTEs daily (dedup, top-N, gaps and islands, running totals, sessionization).
  3. Learn one orchestrator, one warehouse and one processing engine well, and know the concepts behind each.
  4. Be ready to explain every choice in a past project: why this format, partitioning and failure handling.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In the sessionization exercise, what is the key trick?

    Each boundary flag increments the running count, giving a session number.
  2. 2. A top-1-per-group query uses which technique?

    The window function ranks rows inside each group.
  3. 3. When should you choose streaming over batch?

    Streaming adds complexity, so choose it only when latency requirements demand it.
  4. 4. What is the first thing to do when a 6-hour job must become faster?

    Optimization without measurement often fixes the wrong thing.

Summary

  • Practice the recurring patterns: dedup, sessionization, gaps, top-N per group and SCD updates.
  • For design questions, clarify requirements, sketch, detail failure handling and name trade-offs.
  • Explain core ideas simply: idempotency, columnar formats, small files, quality, lakehouse and cost.

Next: Back to the Data Engineering course