Partitioning And Parallel Loads

Why this matters

Partitioning and parallel loads are how ETL Developers move big data fast without breaking systems. You will:

• Load terabytes into warehouses within SLAs by splitting data into manageable chunks.
• Speed up queries via partition pruning and clustering.
• Avoid hotspots, skew, and the small-files problem that slow pipelines.
• Design idempotent parallel jobs that can resume safely after failure.

Concept explained simply

Partitioning splits large datasets into labeled slices (by date, ID hash, region). Parallel loads process those slices at the same time using multiple workers.

• Range partition: slices by time or numeric ranges (e.g., daily partitions).
• Hash partition: spreads rows evenly by hashing a key (e.g., user_id).
• Composite partition: combine range + hash to get both pruning and balance.

Parallelism is the number of tasks working at once. Good parallelism matches your compute, I/O, and destination limits.

Mental model

Think of a highway:

• Partitions are the lanes and exits: you label where traffic goes (date=2026-01-10/hour=13).
• Parallelism is how many cars can drive at once (number of workers).
• Balance lanes to avoid jams: avoid skewed partitions like a single super-busy exit.
• Right-size cars: target file size (e.g., 128–512 MB) to avoid too many tiny files.

Worked examples

Example 1 — Log ingest to data lake/warehouse

Scenario: 900 GB raw logs/day, compressed output ~300 GB/day. Target file size: 256 MB. SLA: hourly loads within 20 minutes.

1. Partitioning: event_date=YYYY-MM-DD/hour=HH.
2. Files per day: 300 GB / 256 MB ≈ 1172 files. Per hour ≈ 49 files.
3. Parallelism: If your platform can run 32 writers per hour, you will complete in two waves (49/32 ≈ 1.53).

# Pseudo plan
partition_template: event_date={yyyy-mm-dd}/hour={HH}
target_file_size_mb: 256
expected_files_per_hour: ~49
max_concurrent_writers: 32
waves_per_hour: 2

Example 2 — Warehouse table with range + hash

Fact table: orders. Queries filter by order_date; some heavy aggregations by customer_id. Choose:

• Primary partition: monthly by order_date (2026-01, 2026-02, ...).
• Sub-partition/cluster: hash(customer_id) into 16 buckets.

-- Generic SQL-like DDL
CREATE TABLE fact_orders (
  order_id BIGINT PRIMARY KEY,
  order_date DATE,
  customer_id BIGINT,
  amount NUMERIC,
  ...
)
PARTITION BY RANGE (order_date) -- monthly
SUBPARTITION BY HASH (customer_id) BUCKETS 16;

Benefits: pruning by month for time-filtered queries, plus even distribution by customer_id for parallel scans and balanced loads.

Example 3 — Parallel backfill by key ranges

You need to backfill 200M rows by order_id without overloading the source. Use 8 workers with key ranges:

# Worker assignments (uniform distribution example)
W1: 1..25M
W2: 25M..50M
W3: 50M..75M
W4: 75M..100M
W5: 100M..125M
W6: 125M..150M
W7: 150M..175M
W8: 175M..200M

-- Idempotent load (pseudo SQL)
MERGE INTO warehouse.orders t
USING (SELECT * FROM staging.orders_batch) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...)
VALUES (...);

Example 4 — Avoid small files in a Spark-like job

# Before write
repartition(target_files)  # set by total_size / target_file_size

# Write path template
output/event_date=2026-01-10/hour=13/part-*.parquet

Pick target_files so each file ≈ 128–512 MB. Fewer, bigger files speed reads and metadata ops.

How to choose partition keys (quick checklist)

• High selectivity: pick fields your queries filter on (often date/time).
• Even distribution: add a hash/bucket if some partitions grow too large.
• Bounded cardinality: avoid partitioning by highly unique fields (e.g., user_id alone).
• Operational fit: make partitions align to ingest windows (hourly/daily).
• Retention and lifecycle: partitions you can drop easily when expiring data.

Degree of parallelism: simple sizing

• Parallelism ≈ min(compute_slots, destination_concurrency_limit, source_rate_limit, files_needed).
• Files needed ≈ total_output_size / target_file_size.
• Start small, observe bottlenecks (CPU, I/O, network), and scale until utilization flattens or errors/queuing appear.

Exercises (mirror below)

1. Exercise 1: Plan a daily partitioning and parallel load for compressed output of ~300 GB/day with 256 MB target files and hourly partitions. What is the partition scheme, approximate total files per day, and writers per hour if you cap concurrency at 32?
2. Exercise 2: Split a skewed order_id range (1..200M) into 10 parallel ranges when 60% of rows are in 150M..200M. Produce worker ranges that balance rows.

• [ ] I computed files = total_output_size / target_file_size.
• [ ] My partitions align with ingest windows.
• [ ] I addressed skew with narrower ranges where data is denser.
• [ ] My plan is idempotent (safe to retry).

Common mistakes and self-check

• Mistake: Partitioning by high-cardinality keys (e.g., user_id) directly, causing millions of tiny partitions. Fix: Use time + hash bucket.
• Mistake: Too many tiny files. Fix: Aim for 128–512 MB per file; coalesce/repartition before writing.
• Mistake: Ignoring skew. Fix: Hashing, dynamic chunking, or uneven ranges based on histograms.
• Mistake: Non-idempotent parallel writes. Fix: Use MERGE/UPSERT or write-to-temp-then-commit.
• Mistake: Overloading sources/destinations. Fix: Respect rate limits; gradually ramp up concurrency.

Practical projects

• Build an hourly ingest for clickstream data with date/hour partitions and a configurable target file size.
• Design a fact table with monthly partitions and hash clustering; benchmark query times with and without pruning.
• Create a backfill tool that generates dynamic key ranges based on histogram stats and runs N parallel workers safely.

Mini challenge

You have 2 TB raw/day, 4:1 compression, 512 MB target files, hourly partitions, and max 48 writers/hour. What waves per hour should you plan for?

Who this is for

• ETL/ELT Developers moving medium-to-huge datasets.
• Data Engineers optimizing ingestion and query performance.
• Analytics Engineers designing warehouse tables with pruning in mind.

Prerequisites

• Comfort with SQL (filters, joins, window functions helpful).
• Basic understanding of filesystems/object stores and columnar formats.
• Familiarity with one compute engine (e.g., a distributed processing framework).

Learning path

1. Data modeling and partitioning fundamentals.
2. File formats and file sizing strategy.
3. Parallelism, resource limits, and backpressure.
4. Idempotent writes (MERGE/UPSERT, atomic swaps).
5. Monitoring and tuning for skew and small files.

Next steps

• Do the exercises below and validate your reasoning.
• Implement one practical project in a sandbox environment.
• Take the Quick Test to confirm your understanding.

Quick Test is available to everyone; sign in to save your progress.