ETL pipeline design: a practical Spark & Data Eng guide

Why ETL pipeline design matters

Most data engineers have inherited a pipeline that breaks silently, loses records on replay, or forces the entire warehouse to reprocess gigabytes unnecessarily. ETL pipeline design: a practical Spark & Data Eng guide isn't about moving data from point A to point B—it's about building systems reliable enough to run at 2 AM without waking anyone up. When you get the fundamentals right, you reduce debugging time, cut infrastructure costs, and give your analytics team trustworthy data they can actually act on.

Understanding the five-stage pipeline architecture

Every production ETL pipeline we've built follows a deliberate layering strategy. Each stage has a specific job, and mixing concerns across stages is where chaos begins.

Stage 1: Extract pulls data from the source system—a REST API, database, message queue, or file store. You don't transform anything here. Your job is to read the source exactly as it is, handle connection errors, and move data downstream. If a field contains garbage, it leaves as garbage.

Stage 2: Raw Landing writes data unchanged into cloud storage or a data lake, typically partitioned by ingestion date and hour. This is your audit trail and your insurance policy. When you discover a transformation bug six months later, you can replay the entire pipeline from raw data and fix it. If you skip this stage to save storage, you'll pay for it in recovery time.

Stage 3: Clean and Standardize removes duplicates, casts data types, handles nulls, and applies business rules that don't depend on other tables. A customer name gets trimmed and lowercased. A timestamp gets converted to UTC. Invalid records get tagged or filtered. This layer is where most of your validation logic lives.

Stage 4: Model for Analytics denormalizes data, creates dimensional tables, joins facts across sources, and builds the semantic layer. A raw events table becomes a fact table with foreign keys to dimension tables. This is where domain logic concentrates—what counts as "revenue," how you handle refunds, when an account is "active."

Stage 5: Publish to Consumers exposes curated datasets to BI tools, machine learning pipelines, and downstream applications. You control access, manage SLAs, and monitor query patterns. Consumers see a clean, documented, performant interface.

Keeping these stages separate means you can evolve modeling logic without reprocessing raw data, fix cleansing rules without re-extracting from the source, and debug issues by isolating which layer introduced a problem.

Designing for idempotent jobs

Idempotency means running the same job multiple times with the same input produces the same output. No accidental duplicates. No partially committed transactions that corrupt your data. In production, network failures, temporary outages, and retry logic guarantee your jobs will run more than once for the same data batch.

The practical approach is to make each Spark job recompute its entire output partition from scratch rather than updating records incrementally. If your job processes data for 2024-01-15, delete the entire 2024-01-15 partition and rewrite it completely. This is much simpler than tracking which rows were modified and issuing UPDATE statements.

from pyspark.sql import SparkSession
from datetime import datetime, timedelta

spark = SparkSession.builder.appName("idempotent_cleanse").getOrCreate()

# Read raw data for a specific day
run_date = "2024-01-15"
raw_df = spark.read.parquet(f"/data/raw/{run_date}")

# Apply transformations
cleaned_df = (raw_df
    .filter(raw_df.customer_id.isNotNull())
    .withColumn("event_date", raw_df.event_timestamp.cast("date"))
    .dropDuplicates(["customer_id", "event_timestamp"])
)

# Delete the partition for this day (idempotent)
spark.sql(f"ALTER TABLE cleaned_events DROP IF EXISTS PARTITION (event_date='{run_date}')")

# Write as a new partition—if the job reruns, it overwrites
cleaned_df.write.mode("overwrite").partitionBy("event_date").parquet("/data/cleaned/events")

Using DROP PARTITION before OVERWRITE ensures that if your job fails partway through, the next run starts fresh. You avoid the scenario where partial writes leave corrupt partition metadata.

Another pattern: use a staging table. Write to a temporary location, validate record counts, then atomic swap the staging table for the production table. If validation fails, production data stays clean.

Incremental loads and processing only what's new

Processing the entire dataset every day doesn't scale. A raw events table with billions of rows can't be fully processed daily. Incremental loads process only new or changed records since the last successful run.

The key is a high-water mark: a timestamp or sequence number that tracks the last record you processed. On the next run, you query the source with WHERE last_modified > {last_high_water_mark}, extract only those records, and update your high-water mark.

# Read the last processed timestamp
try:
    last_run = spark.read.parquet("/metadata/highwatermark").collect()[0]["max_timestamp"]
except:
    last_run = "1970-01-01"  # First run, start from beginning

# Extract only new records
new_records = spark.read \
    .jdbc("jdbc:postgres://db:5432/source", "events",
          predicates=[f"updated_at > '{last_run}'"],
          properties={"user": "reader", "password": db_pass}) \
    .cache()

if new_records.count() == 0:
    print("No new records, exiting")
    exit(0)

# Transform and write
processed = new_records.withColumn("processed_at", lit(datetime.now()))
processed.write.mode("append").partitionBy("created_date").parquet("/data/cleaned/events")

# Update high-water mark
new_hwm = new_records.agg(max("updated_at")).collect()[0][0]
spark.createDataFrame([(new_hwm,)], ["max_timestamp"]) \
    .write.mode("overwrite").parquet("/metadata/highwatermark")

This approach reduces compute and storage by 90% or more on mature datasets. You only touch changed records. The tradeoff is complexity—you must handle out-of-order arrivals, delayed updates, and retries carefully. If a source system replays old records with corrected values, your incremental logic must detect and reprocess them.

Data contracts: agreeing on schema and guarantees

A data contract is a written agreement between the team that produces data and the team that consumes it. It specifies the schema, partition strategy, update frequency, freshness SLA, and what happens when the schema changes.

Without contracts, teams operate in isolation. A producer renames a field or adds a new column, and the consumer's pipeline breaks without warning. Contracts prevent this by making changes explicit and coordinated.

Document your contract as a versioned file in Git. Include a schema in Avro or Protobuf format, not just English descriptions. Enforce schema validation in your Spark jobs using libraries like spark-expectations or custom validation logic.

from pyspark.sql.types import StructType, StructField, StringType, LongType, TimestampType

expected_schema = StructType([
    StructField("event_id", StringType(), False),  # Not nullable
    StructField("customer_id", LongType(), False),
    StructField("event_timestamp", TimestampType(), False),
    StructField("event_type", StringType(), False),
    StructField("metadata", StringType(), True),  # Nullable
])

# Read and validate
raw_df = spark.read.parquet("/data/raw/events")

if raw_df.schema != expected_schema:
    raise ValueError(f"Schema mismatch. Expected {expected_schema}, got {raw_df.schema}")

# Safe to proceed
print("Schema validation passed")

Common mistakes

• Skipping the raw landing zone. Engineers often try to save storage by transforming directly on ingest. When you need to replay or debug six months later, you're stuck reprocessing the source system or losing history entirely.
• Non-idempotent transformations. Using UPDATEs or incremental inserts without checking for duplicates means a network retry corrupts your data silently. Always recompute partitions from scratch or use atomic table swaps.
• No high-water mark tracking. Reprocessing all historical data daily wastes compute and delays new data. High-water marks are essential for any production pipeline at scale.
• Ignoring data contracts. Without explicit agreements, producers and consumers drift out of sync. Schema changes break pipelines. Start contracts early, even if informal.
• Mixing business logic across layers. Don't apply both cleansing and modeling in a single job. Separate concerns make debugging, testing, and reuse easier.

Key takeaways

• Layer your pipeline into five stages: extract, raw landing, cleanse, model, and publish. Each stage has a specific purpose and guards against corruption downstream.
• Design jobs to be idempotent by rewriting entire partitions rather than updating individual rows. If a job reruns, it produces the same output without corruption.
• Use high-water marks to process only new or changed data. Track the last processed timestamp and query the source incrementally to reduce compute and storage costs.
• Establish data contracts between producers and consumers. Document schema, partition strategy, update frequency, and SLAs. Validate schemas in code to catch breaks early.
• Keep raw data immutable and queryable. It's your insurance policy for bugs, audits, and replays. The storage cost is trivial compared to the debugging cost if you lose it.