Data engineering practice
Each challenge is a brief someone could plausibly hand you, followed by what is actually going on underneath it. Decide what you would do before opening anything else — and read the trap on every one, because the trap is the fix that looks right and makes the next incident worse.
One pipeline, done properly. Where transformation runs, what one row means, why the file format matters.
Partitioning, orchestration, incremental loads, a warehouse model and the first quality test that fails.
Change capture, a partitioned log, a shuffle you can read, skew you can find, and a backfill that does not corrupt today.
Platform design, real-time analytics, governance across teams, and incidents where every job was green.
Beginner
A new table stg_order_changes is fed by change capture. An analyst counts rows for last Tuesday and gets a number noticeably larger than the count of orders in the source for the same day. Nothing failed, and the totals in the source are not in dispute.
The warehouse daily revenue figure and the finance system's daily figure differ by a small amount every day. Neither is drifting, the totals over a month are nearly identical, and no check has ever fired.
A partner drops a CSV each morning. Today's file loaded without error and every downstream job succeeded, but the country column contains what look like postcodes and the amount column is empty.
A payment method launched last week. It appears in the source system and in the raw layer, and it is entirely absent from fct_payments. Every task is green and the total is only slightly lower than expected.
The application team renamed a column in a migration. It was a correct, reviewed change. Eleven dashboards broke that afternoon, and two more produce numbers that are wrong without producing an error.
Intermediate
Reconciliation against the source shows the warehouse is short by a small number of orders every single day. It is never large enough to move a volume check. Re-running the extract for a past day changes nothing.
A table was repartitioned by customer_id two months ago to speed up per-customer lookups. Those lookups did get faster. Every other query against the table has become progressively slower, and the volume of data has barely changed.
A nightly job rebuilds a fact table from all history. It has been getting slower for a year and now regularly runs past the morning deadline. The team has already increased the warehouse size once, which bought a few months.
A consumer reports that a dashboard tile shows an old figure. Freshness monitoring is green, the serving dataset was published on time, row counts are normal, and querying the model directly returns the current number.
A breakdown by subscription status has looked stable for two months, while the total has grown. One status has been flat since a mobile release, and no check has fired. Types are unchanged and no schema diff exists.
Revenue by country differs depending on which model an analyst uses. The CRM, the orders table and the payments provider each hold a country for a customer, and for a meaningful set of customers the three do not agree.
Advanced
An auditor asks the team to reproduce a revenue-by-customer-tier figure from a report filed a year ago. The pipeline is deterministic, the raw data is retained, the code is at the same version — and the recomputed figure does not match the filed one.
A streaming job joins orders to payments on order id and emits a matched record. Checkpoint size grows steadily, restarts take longer each week, and a meaningful share of matched pairs never appear in the output even though both sides are present in the log.
A job that joins a large fact to a small dimension ran comfortably for a year. Over the past two months its runtime has climbed sharply. Nothing in the code changed, and the fact table has grown only modestly.
A new downstream model needs a year of event history to compute its first output. The broker retains a fraction of that. A team member suggests raising the retention setting and re-running.
A scheduled report occasionally produces a figure that matches no version of the data. It happens perhaps once a month, always in the same half-hour, and the run it read from was green.
A customer exercises a deletion right. The source row is deleted and the request is marked complete. Weeks later their support text is returned by the in-product assistant, and their name appears in a monthly aggregate that is regenerated on demand.
Expert
The company decides that its headline retention metric should exclude a customer segment it has always included. The decision is correct and final. Two years of published figures, several external filings and every trend chart in the company were computed under the old definition.
Eight teams share one warehouse. Spend has grown steadily for a year. Every team believes their usage is modest and each is right about their own share. Leadership wants the total reduced and has asked for a plan by the end of the month.
A dashboard joins a warehouse fact table to a table in an operational database through a federated query engine. It was fast when it was built. It now times out, and it is also showing up on the source database's slow query list.
One data team of four supports twelve product teams and is a permanent bottleneck. Leadership approves a move to domain ownership: each product team will own its own data products. The reorganisation is announced with a date.
The product now operates in regions with data residency requirements: certain personal data may not leave its region. The platform is a single warehouse in one region. Finance needs global revenue; product analytics needs global behaviour; the legal position is not negotiable.