etl-pipeline

Use watermark-based extraction: query rows where updated_at exceeds the last successful watermark, write results to a staging area like S3 parquet, and update the watermark only after a successful load. This ensures idempotent, resumable delta loads.

Validate schema columns, null constraints on critical fields, numeric ranges, row counts within expected bounds, and duplicates on key columns. Run all checks before loading to the destination and fail the job if any check errors.

Incremental loading is mandatory for tables over 10M rows because full refresh is too slow. Use full refresh only for small dimension tables where delta detection adds unnecessary complexity.

Use SCD Type 2 for dimensions where history matters, such as customer addresses or product prices. Each change creates a new row with effective date ranges, preserving the full historical record for accurate reporting.

A staging area decouples extraction from loading, allowing quality checks and transformations to run before data reaches the destination. Loading directly from source to destination risks corrupting warehouse tables with unvalidated data.

Track data freshness with alerts when data exceeds 2 hours old, monitor daily row counts with alerts on deviations over 20 percent, and track pipeline duration with alerts when runtime exceeds twice the expected time. Failed jobs should retry 3 times before alerting.