DataPlayArena
Interview Focus: PerformanceDifficulty: Hard

Incremental ETL Pipeline

Estimated duration: 20 mins

Lab Overview & Scenario

Build an incremental ETL pipeline using merge and upsert logic.

Business Scenario

You need to update a target table using delta logs containing new inserts and updates (upsert). Merge the incoming records into the target state without writing duplicates.

1. Local Raw Mock Dataset

The processing pipeline will ingest the following local mock file structures (e.g. simulated as `dataset.csv`):

idnamecitylast_updated
1AliceNew York2026-07-01
2BobBoston2026-07-01
---
idnamecitylast_updated
2BobChicago2026-07-02
3CharlieMiami2026-07-02
View Raw CSV Dataset
id,name,city,last_updated
1,Alice,New York,2026-07-01
2,Bob,Boston,2026-07-01
---
id,name,city,last_updated
2,Bob,Chicago,2026-07-02
3,Charlie,Miami,2026-07-02

2. Your Tasks

3. Pipeline Skeleton Script

Copy this starter template to write the data transformation logic:

python
# starter.py - Incremental ETL Pipeline
from pyspark.sql import SparkSession
from pyspark.sql.functions import col

def run_upsert():
    spark = SparkSession.builder.appName("IncrementalETL").getOrCreate()
    
    target_data = [(1, "Alice", "New York", "2026-07-01"), (2, "Bob", "Boston", "2026-07-01")]
    delta_data = [(2, "Bob", "Chicago", "2026-07-02"), (3, "Charlie", "Miami", "2026-07-02")]
    
    # TODO: Perform incremental upsert merge logic using join/union
    pass

if __name__ == "__main__":
    run_upsert()

4. Interactive Solution & Execution Output

Reveal Solution Pipeline Code
Implement the correct transformations using PySpark to achieve the aggregated values metrics output.

Pipeline Flow

Target & Delta Data
↓
Left Anti Join (preserved)
↓
Union with Delta Updates
↓
Sort by ID
↓
Display Output

Step-by-Step Implementation Guide

Step 1

Load the Target and Delta DataFrames.

Step 2

Identify target records that aren't in the incoming delta updates using a left anti join: target_df.join(delta_df, on="id", how="left_anti"). This filters out existing records that will be updated by the delta.

Step 3

Combine the preserved target records with the new delta updates using .union().

Step 4

Sort by ID and print the updated target table state.

Common Mistakes

  • Doing a simple outer join without deduplication, resulting in multiple duplicate row values for key matches.
  • Forgetting to sort, which leaves target tables out of order.

Interviewer's Expectations

Why this is asked:

Incremental ETL (upserting new/changed records) is the industry standard for keeping data warehouses up to date.

What they look for:

Clean implementation of merge logic using joins, unions, or Delta Lake APIs.

Common mistakes:

Using expensive full outer joins when anti-joins can isolate records more efficiently.

An excellent answer includes:

Explain anti-joins and discuss how ACID storage formats like Delta Lake handle MERGE INTO operations natively.

✔ What You Learned

  • ✔Isolate records using Left Anti Joins
  • ✔Union DataFrames sharing identical schemas
  • ✔Write incremental upsert ETL logic

Continue Learning

Previous LabHandle Data SkewRelated Theory LessonDelta LakeNext LabStreaming Order Processing