Parameterization By Date Partition

Why this matters

As an ETL Developer, most production data pipelines run daily or hourly and land data into partitioned storage (for example, dt=YYYY-MM-DD folders in object storage or date-partitioned tables in a warehouse). Parameterizing the run with a specific date lets you:

• Load exactly one partition per run for predictability and idempotency.
• Backfill historical days without code changes.
• Reprocess late-arriving partitions quickly.
• Keep schedules simple while your tasks know which date to work on.

Concept explained simply

Parameterization by date partition means your pipeline receives a date value (like 2025-04-01) and uses it everywhere: in input paths, SQL filters, and output partitioning.

Mental model: imagine a calendar knob. Each pipeline run sets the knob to a specific date. Every task reads the knob and processes only that slice of data.

• Execution (logical) date: the date assigned by your scheduler to the run.
• Data (event) date: the date inside the records. Sometimes they match; sometimes they differ. Be explicit about which one you use.
• Time zone: use UTC for partition keys to avoid daylight savings issues.

Core patterns you will use

1. Single-day loads: Process only dt=YYYY-MM-DD.
2. Hourly loads: Process dt=YYYY-MM-DD and hour=HH.
3. Rolling window: Process a range like [start_date, end_date) for aggregations.
4. Backfill loops: Orchestrator iterates dates and triggers the same job per date.
5. Late data: Detect a late partition and re-run just that date, or maintain a small rolling reprocess window.

Worked examples

Example 1: Orchestrator passes a daily date to tasks

Pass a partition date to a shell task and SQL filter.

# Environment variable set by the orchestrator (example value)
PARTITION_DATE=2025-04-01

# Use it in object storage paths
RAW_PATH="s3://raw-bucket/events/dt=${PARTITION_DATE}/"

# Use it in SQL to load only that partition
-- Pseudocode SQL (warehouse-agnostic)
INSERT INTO dw.fact_events (cols...)
SELECT cols...
FROM staging.events
WHERE dt = '${PARTITION_DATE}';

Example 2: Spark job reads a date-partitioned path

# Python (Spark) pseudo-code
import os
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()

partition_date = os.getenv("PARTITION_DATE")  # e.g., 2025-04-01
input_path = f"s3://raw-bucket/events/dt={partition_date}"
df = spark.read.parquet(input_path)
# transform df ...

# Write to warehouse landing partition
output_path = f"s3://wh-bucket/fact_events/dt={partition_date}"
df.write.mode("overwrite").parquet(output_path)

Example 3: Rolling 7-day window with scheduler-provided date

# Assume the orchestrator provides END_DATE (inclusive upper bound - 1 day)
END_DATE=2025-04-01
START_DATE=2025-03-26  # 6 days before END_DATE for a 7-day window

-- Pseudocode SQL for incremental aggregate
DELETE FROM mart.active_users WHERE dt >= '${START_DATE}' AND dt < '${END_DATE}';
INSERT INTO mart.active_users
SELECT dt, COUNT(DISTINCT user_id) AS active_users
FROM dw.fact_events
WHERE dt >= '${START_DATE}' AND dt < '${END_DATE}'
GROUP BY dt;

Exercises

Do these two exercises. Then open the solutions to compare.

Checklist before you move on

• You can pass a date to shell, Spark, and SQL tasks.
• You default DATE to UTC yesterday when none is provided.
• Your jobs are idempotent per partition (overwrite or MERGE).
• You can backfill a range of dates via a loop.
• You use ISO format (YYYY-MM-DD) everywhere.

Common mistakes and self-check

• Using current_date in SQL instead of a passed-in parameter. Self-check: Can you re-run the same date tomorrow and get the same result?
• Mixing local time and UTC. Self-check: Are all partition keys UTC? Are conversions done only at display boundaries?
• Not overwriting or merging the target partition. Self-check: Re-running a date produces duplicates.
• Backfilling with the wrong boundaries. Self-check: Do you use [start, end) and verify counts per day?
• Confusing execution date with event date. Self-check: Is the chosen date explicitly documented and used consistently?

Practical projects

1. Daily partition loader: Build a job that reads dt partitions from object storage and writes to a warehouse table with partition-level overwrite.
2. 7-day backfill command: A single script that takes START_DATE and END_DATE and iterates over all dates.
3. Late-arrival handler: A small utility that, given a list of dates, re-runs only those partitions and reports success/failure per date.

Learning path

1. Pass a single DATE parameter end-to-end (shell → Spark/SQL).
2. Add defaulting and validation (ISO format, UTC).
3. Implement idempotent writes (overwrite or MERGE per partition).
4. Introduce backfill loops and windowed loads.
5. Handle late data and partition health checks.

Who this is for

• ETL Developers building scheduled pipelines.
• Data Engineers maintaining partitioned data lakes/warehouses.
• Analysts/Analytics Engineers who need reliable incremental loads.

Prerequisites

• Basic shell scripting and environment variables.
• Comfort with SQL WHERE clauses and simple DML.
• Familiarity with partitioned storage concepts.

Mini challenge

You discover that dt=2025-04-01 was loaded with incomplete data. Draft a short runbook note that:

• Explains how to re-run only that date.
• States which parameter to set and to what value.
• Confirms the write mode (overwrite/MERGE) to keep the run idempotent.
• Lists how to verify success (row counts or simple checksums).

Next steps

• Refactor one of your pipelines to take an explicit DATE and document the timezone and format.
• Add a simple backfill script that loops over dates safely.
• When ready, take the quick test below. Note: The test is available to everyone; only logged-in users get saved progress.

Quick Test

Take the quick test to check your understanding. The test is available to everyone; only logged-in users get saved progress.