Wrong GROUP BY Grain Causing Customer Revenue Inflation
You will practice
Build a customer-level revenue result with exactly one row per customer. Include only completed orders, return customer_id, customer_name, and completed_revenue, and make sure duplicate status rows cannot inflate the dashboard.
The query groups by customer and order status, but the dashboard expects one row per customer. When downstream users sum the status-level rows again, cancelled and completed order rows are mixed into the customer metric.
Return every active customer. For customers who clicked campaign SPRING_26, show their latest click timestamp. For customers with no click, keep the customer row and return NULL for last_click_at.
The query uses a LEFT JOIN, but a filter on the campaign table is placed in the WHERE clause. That removes NULL right-side rows and silently turns the result into an inner join for this campaign.
Duplicate Revenue from Joining Orders to Multiple Payments and Refunds
You will practice
Return one row per order with paid_amount, refunded_amount, and net_revenue. Aggregate each child table to order_id before joining so payment and refund rows cannot multiply each other.
The mart joins orders directly to payments and refunds at row level. Because both child tables can have multiple rows per order, the join multiplies records before aggregation.
Yesterday's Sales Missing After Late Source Arrival
You will practice
Diagnose why yesterday's sales are late, fix the PySpark transformation so sales are assigned to the correct business_date, and propose an Airflow scheduling change that waits for the required source partition before loading the warehouse. For executable validation, create a Spark DataFrame named daily_sales or fixed_daily_sales with business_date, order_count, and gross_sales. Do not write to the warehouse from the practice runner.
The pipeline expected yesterday's sales export to be available before the 2 AM Airflow run. The source file arrived late, so the first run failed. A later retry picked up data, but the PySpark transformation filters by ingestion date instead of sales business date, so late-arriving rows for yesterday still do not land in the correct dashboard partition.
Fix the PySpark code so the pipeline is correct, scalable, and safe to rerun.
The job reaches 99 percent quickly, 199 out of 200 tasks finish, but one task runs for hours with high GC and 100 percent CPU before ending with OutOfMemoryError.
Type
Broken PySpark Fix
Time
24 min
Progress
Not started
PysparkBroken PysparkSpark Performance and DebuggingSpark job stuck at 99% because of data skew
Fix the PySpark code so the pipeline is correct, scalable, and safe to rerun.
The job technically succeeds, but downstream reads get slower over time and cloud object store listings become painfully expensive. Each partition contains hundreds or thousands of tiny files.
Type
Broken PySpark Fix
Time
24 min
Progress
Not started
PysparkBroken PysparkSpark Performance and DebuggingSpark write path creates too many tiny files
Fix the PySpark code so the pipeline is correct, scalable, and safe to rerun.
Someone forced a broadcast hint. It worked in test and smaller markets, but production now fails intermittently with broadcast timeout or executor memory pressure.
Type
Broken PySpark Fix
Time
24 min
Progress
Not started
PysparkBroken PysparkSpark Performance and DebuggingWrong broadcast choice causes timeout or memory issues
Fix the PySpark code so the pipeline is correct, scalable, and safe to rerun.
After onboarding a new market, some columns shifted position and downstream consumers started seeing nulls or incorrect values even though the job itself succeeded.
Type
Broken PySpark Fix
Time
24 min
Progress
Not started
PysparkBroken PysparkSpark Performance and DebuggingSchema mismatch during union creates silent data corruption risk
Fix the PySpark code so the pipeline is correct, scalable, and safe to rerun.
After a deployment and infrastructure interruption, the stream restarts but either reprocesses events, produces duplicates, or struggles with state-store recovery.
Type
Broken PySpark Fix
Time
24 min
Progress
Not started
PysparkBroken PysparkSpark Performance and DebuggingStructured Streaming restarts cause duplicates or state issues
Fix the PySpark code so the pipeline is correct, scalable, and safe to rerun.
Production performance becomes inconsistent. On some days the job gets faster, but on others one stage becomes very heavy, task counts collapse, and the downstream write becomes slower than before.
Type
Broken PySpark Fix
Time
24 min
Progress
Not started
PysparkBroken PysparkSpark Performance and DebuggingAdaptive Query Execution changes join or partition strategy in unexpected ways
Fix the PySpark code so the pipeline is correct, scalable, and safe to rerun.
The job does not fail immediately, but stages with sort and window execution become extremely slow, spill heavily to disk, and sometimes time out under peak loads.
Type
Broken PySpark Fix
Time
24 min
Progress
Not started
PysparkBroken PysparkSpark Performance and DebuggingLarge window operations create spill, skew, or sort pressure
Fix the PySpark code so the pipeline is correct, scalable, and safe to rerun.
Runtime suddenly deteriorates after a source issue causes a large share of merchant_id values to arrive as null or a default placeholder, and one stage becomes badly imbalanced.
Type
Broken PySpark Fix
Time
24 min
Progress
Not started
PysparkBroken PysparkSpark Performance and DebuggingNull or default join keys collapse data into a pathological partition
Fix the PySpark code so the pipeline is correct, scalable, and safe to rerun.
After the change, row counts increase by orders of magnitude, a formerly healthy job now spills heavily, and downstream tables become far larger than expected.
Type
Broken PySpark Fix
Time
24 min
Progress
Not started
PysparkBroken PysparkSpark Performance and DebuggingFlattening nested arrays multiplies row counts and destabilizes the plan
Identify the most likely failure mechanism, propose a reversible production-safe fix, and explain validation, trade-offs, monitoring, and recovery.
The incident centers on bounded-memory ingestion and streaming parsing. The current implementation or operating process does not make that contract explicit, so the team needs a diagnosis supported by evidence rather than a tool or configuration guess.
Type
Mixed Lab
Time
23 min
Progress
Not started
Python IngestionBounded Memory Ingestion And Streaming ParsingProduction ScenarioInterview Practice
Identify the most likely failure mechanism, propose a reversible production-safe fix, and explain validation, trade-offs, monitoring, and recovery.
The incident centers on CDC retention and recovery. The current implementation or operating process does not make that contract explicit, so the team needs a diagnosis supported by evidence rather than a tool or configuration guess.
Type
Log / Error Analysis
Time
28 min
Progress
Not started
CDCCDC Retention And RecoveryProduction ScenarioInterview Practice
Identify the most likely failure mechanism, propose a reversible production-safe fix, and explain validation, trade-offs, monitoring, and recovery.
The incident centers on data locality and egress cost. The current implementation or operating process does not make that contract explicit, so the team needs a diagnosis supported by evidence rather than a tool or configuration guess.
Type
Log / Error Analysis
Time
18 min
Progress
Not started
Cloud PlatformData Locality And Egress CostProduction ScenarioInterview Practice
Identify the most likely failure mechanism, propose a reversible production-safe fix, and explain validation, trade-offs, monitoring, and recovery.
The incident centers on additive, semi-additive, and non-additive facts. The current implementation or operating process does not make that contract explicit, so the team needs a diagnosis supported by evidence rather than a tool or configuration guess.
Type
Mixed Lab
Time
23 min
Progress
Not started
Data ModelingAdditive, Semi Additive, And Non Additive FactsProduction ScenarioInterview Practice
Identify the most likely failure mechanism, propose a reversible production-safe fix, and explain validation, trade-offs, monitoring, and recovery.
The incident centers on test pyramid for data systems. The current implementation or operating process does not make that contract explicit, so the team needs a diagnosis supported by evidence rather than a tool or configuration guess.
Type
Log / Error Analysis
Time
18 min
Progress
Not started
Data ReliabilityTest Pyramid For Data SystemsProduction ScenarioInterview Practice
Identify the most likely failure mechanism, propose a reversible production-safe fix, and explain validation, trade-offs, monitoring, and recovery.
The incident centers on policy inheritance and bypass prevention. The current implementation or operating process does not make that contract explicit, so the team needs a diagnosis supported by evidence rather than a tool or configuration guess.
Type
Log / Error Analysis
Time
28 min
Progress
Not started
Security & PrivacyPolicy Inheritance And Bypass PreventionProduction ScenarioInterview Practice
Identify the most likely failure mechanism, propose a reversible production-safe fix, and explain validation, trade-offs, monitoring, and recovery.
The incident centers on parallel-run migration validation. The current implementation or operating process does not make that contract explicit, so the team needs a diagnosis supported by evidence rather than a tool or configuration guess.
Type
Interview Explanation
Time
28 min
Progress
Not started
Architecture & LeadershipParallel Run Migration ValidationProduction ScenarioInterview Practice