ETL / ELT
Advanced 14 min read ETL / ELT

Batch Optimization

Attack bytes and config before you attack hardware

Slow batches usually waste bytes, not nodes: profile the query, prune scans, right-size partitions, and parallelize the right stage. The tuning loop is measure -> model/partition -> retest, in that order.

batchoptimizepartitionparallelismprofilebottleneck
Prerequisites: Pipeline Testing Warehouse Performance
Before we start

What you will learn

  • Profile a batch to find the real bottleneck
  • Right-size partitions and parallelism for the workload
  • Apply projection/pruning at each stage
Introduction

What is this?

Batch optimization finds the dominant cost — a full scan, a shuffle, a tiny row-group mess, a serialized section — and removes it. Practical levers: column projection, partition/cluster keys aligned with filters, parallelism matching free CPU, compression, and reading only what changed (incremental).

Motivation

Why do you need it?

  • Warehouse bills and batch durations track scanned bytes more than CPU.
  • The profile-first habit keeps you from 'fixing' the wrong stage.
  • Most 'need a bigger machine' requests are really 'need a better partition' requests.
Think about it

Mental model

Imagine

A trucking route with 4 minutes of driving and 41 minutes of waiting at a weigh station

Think of

Batch profile

Buying a faster truck (hardware) is pointless when the weigh station (the scan/shuffle) is the bottleneck. You fix the station first.

Mechanics

How it works

  • Profile: run a query profile/EXPLAIN and name the top cost node (scan bytes, shuffle, join, sort).
  • Prune: push filters to partition/cluster or Parquet stats so only needed slices are read.
  • Project: select only needed columns; every staging/mart keeps its schema tight.
  • Parallelize: right-size workers vs data skew; fewer, larger files split cleanly.
  • Right-size: batch cadence (per-partition incremental) beats 'rewrite the world nightly'.

The tuning loop.

Profile
Prune
Project
Parallel
Retest
Reference

Key commands

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

Profile a batch query's node cost and buffers.

SELECT count(*), avg(size) FROM parquet_metadata('...')

Diagnose tiny-file overhead before tuning writes.

duckdb -c "COPY (SELECT ... ) TO 'out.parquet' (FORMAT PARQUET, ROW_GROUP_SIZE 122880);"

Write right-sized, prunable output files.

Hands-on

Examples

The same batch, three fixes

The same batch, three fixes
-- Before: SELECT * over a whole year
SELECT * FROM fact_event WHERE day BETWEEN '2025-01-01' AND '2025-12-31';
 
-- Fix 1 (project): select the 6 columns the mart actually needs
SELECT user_id, day, event, amount FROM fact_event ...
 
-- Fix 2 (prune): assert the table is partitioned by day
WHERE day = '2026-03-14';
 
-- Fix 3 (parallel): split the read across partitions, one worker per partition.

Bytes scanned dominate; every fix here is about bytes, not brute force.

Real world

In production

  • A nightly fact rebuild cut from 90 to 20 minutes just by partitioning to day and projecting 8 columns.
  • A Spark batch reduced shuffle by clustering the fact on the join key before writing.
  • A monthly archive switches to partition-delta loads so only the new month is ever rewritten.
Guidelines

Best practices

  • Profile before and after every change; log both so wins are provable.
  • Align partition/cluster keys with the batch's most common filter (usually a date).
  • Keep files few and large; never let micro-partitions survive compaction.
  • Parallelize only after scan pruning; 'more workers' won't fix a wasteful plan.
Watch out

Common mistakes

  • Tuning the join when the profile shows the scan is the cost.
  • Adding workers to a stage that's I/O-, not CPU-, bound.
  • Rewriting the whole world nightly when 95% of data hasn't changed.
When things break

Debugging tips

  • A batch that's SLOWER with more workers often points at skew or tiny-file contention — not parallelism.
  • Compare bytes scanned vs returned: the gap IS your next optimization.
  • Watch per-stage time in the profile; one hot node names the fix.
Interviews

Interview checkpoint

Practice

Hands-on lab

Profile First: Prune, Project, Parallel
35 min · advanced
In progress
Objectives
  • Simulate a 'slow nightly batch' on a wide table
  • Apply projection and partition-aware reads
  • Measure before/after with DuckDB timing + metadata

Profile First: Prune, Project, Parallel

35 min · advanced

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

The first step of any batch optimization is…

2

SELECT * from a wide table wastes both time and money compared to projecting only needed columns.

Go deeper

Official documentation

BigQuery batch performance
docs
Spark tuning guide
docs