1. The Problem
Writing execution logs to unindexed flat JSON files or local log files made it impossible to query run history, track high-watermark timestamps, or isolate schema evolution bugs when jobs failed.
2. What I Initially Thought
3. What I Learned
Pipeline execution metadata requires the exact same ACID transactional guarantees as application data. State updates (watermarks, row counts) must execute atomically alongside target data batch writes.
4. What I Built
Relational metadata catalog in PostgreSQL tracking pipelines, pipeline_runs, dataset_snapshots, and atomic watermark state (sql/tables/pipeline_state.sql).
CREATE TABLE IF NOT EXISTS pipeline_state (
pipeline_name VARCHAR(100) NOT NULL,
table_name VARCHAR(100) NOT NULL,
last_processed_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
records_processed BIGINT DEFAULT 0,
status VARCHAR(20) NOT NULL DEFAULT 'IDLE',
PRIMARY KEY (pipeline_name, table_name)
);5. The Experiment
Storing execution state in unindexed JSON files (state.json). Querying historical run status required parsing every log file on disk.
Migrated metadata management to PostgreSQL using atomic ON CONFLICT DO UPDATE state tracking with indexed primary and foreign keys.
State lookup latency dropped to milliseconds; concurrent state updates safely locked via PostgreSQL transactions.
6. What Went Wrong
Initial watermark script updated the watermark timestamp in memory without wrapping the database call in an explicit transaction commit. A mid-batch job failure skipped 15,000 records on retry.
7. Engineering Decision & Trade-offs
Chose PostgreSQL for operational metadata over Redis or NoSQL key-value stores because foreign keys enforce entity integrity between pipeline runs, schema versions, and dataset snapshots.
8. What I Would Do Differently in Production
In an enterprise production environment, integrate standard open metadata frameworks like Apache Airflow MetaDB or OpenLineage to automatically emit execution metadata.
Questions I Can Now Answer Confidently in an Interview:
- How do you track high-watermarks for incremental data extraction without concurrency race conditions?
- Why are relational databases preferable to flat files or key-value stores for metadata catalogs?
- How do atomic database transactions protect pipeline recovery during mid-batch job failures?