# Data Engineering Best Practices

## Purpose
This skill provides engineering guidelines for building reliable, maintainable data pipelines
and implementing data solutions. Apply it when implementing, reviewing, or testing data
engineering work.

## Pipeline Design Principles

### Idempotency
Every pipeline run must produce the same result regardless of how many times it is executed.
- Use `MERGE` or `INSERT OVERWRITE` instead of plain `INSERT` where duplicates are possible.
- Partition writes by date or batch ID to enable targeted re-runs.

### Incremental over full load
Prefer incremental loads (delta detection) over full reloads.
Full reloads are acceptable only when: source cannot provide deltas, or table is small.
- Track high-watermark (timestamp or sequence ID) per source table.

### Fail fast, fail explicitly
A pipeline that silently produces wrong data is worse than one that fails loudly.
- Validate input before processing: row count, schema, null checks on key columns.
- Abort on validation failure — do not continue with bad data.

### Atomicity
A pipeline run is either fully complete or fully rolled back — never partially applied.
- Write to a staging location first, then promote to production in a single atomic step.

## Data Quality

### Mandatory checks on every load
1. **Row count**: Compare source count to target count after load.
2. **Null key check**: No null values in business key or surrogate key columns.
3. **Referential integrity**: All FK values in Fact tables exist in the referenced Dimension.
4. **Duplicate key check**: No duplicate business keys in Key tables.

### Quality failure handling
- Log the failure with: table name, check name, expected value, actual value.
- Do not proceed to downstream tables if an upstream check fails.
- Quarantine bad records rather than dropping them.

## Schema Evolution

### Additive changes (safe)
Adding a nullable column, adding a new partition — apply without coordination.

### Breaking changes (require coordination)
Renaming a column, changing a data type, dropping a column.
Protocol: Add new alongside old → migrate consumers → remove old.

## Error Handling & Observability

### Retry strategy
- Transient errors: retry up to 3 times with exponential backoff.
- Data errors: do not retry — log and fail.

### What to log (minimum)
For every pipeline run: start/end time, source count, target count, rejected count,
each quality check result, any error with table/operation/message.

## Common Anti-Patterns
- **Silent truncation**: Inserting into a VARCHAR(50) column with a 100-char value without catching the error.
- **Mutable staging**: Writing to a staging table also read by other processes — causes race conditions.
- **Catch-all exception handlers**: Catching all exceptions and marking the run as successful.
- **Hardcoded dates**: Hardcoding a reference date instead of using a parameter — makes backfill impossible.
- **No reconciliation after load**: Assuming the load succeeded because no exception was thrown.
