ETL Process Design Document

An ETL process that nobody documented is an ETL process nobody can safely change. When the analyst who built it leaves, the nightly job becomes a black box the business depends on.

This document specifies your ETL process completely enough that any competent data engineer can implement, operate, or repair it. Extraction is defined per source: connection method, query or API call, incremental watermark strategy, and expected volume.

Transformation rules are written at field level — every cleaning step, type cast, lookup, join, derived column, and business rule, with the handling for nulls, duplicates, and values that fail validation stated explicitly. The target schema is specified with load strategy: full refresh, incremental upsert, or slowly changing dimension, whichever fits the data.

Scheduling covers cadence, dependency order, and what must succeed before the next stage runs. Data quality checks are defined as concrete assertions — row counts, referential integrity, range and uniqueness tests — with the failure action for each.