Enterprise ETL Design: Metadata, Idempotency and Observability

Metadata-driven over hand-built

Hand-built pipelines scale linearly with effort: every new table is new code, new testing and new maintenance. A metadata-driven framework reverses that. A control table describes each source — connection, object, load type, watermark column, target path — and a small set of parameterised pipelines reads it and does the work.

The first source takes longer. The second and the hundredth take minutes, and they all behave the same way.

Idempotency is non-negotiable

An idempotent load produces the same result whether it runs once or five times. That property turns incident recovery from a careful manual procedure into a rerun button.

• Use MERGE or delete-insert by partition instead of blind inserts
• Advance watermarks only after a confirmed successful load
• Make every step safe to restart from the beginning

Observability built in

Every run should record what it read, what it wrote, how long it took and which watermark it reached. With that log table you can build SLA reporting, detect slow drift in runtimes and answer 'did yesterday's data load?' without opening the orchestration tool.