Why Moving Data From A to B Was the Easy Part

1. The Problem

Running heavy multi-join analytical aggregations directly against the production PostgreSQL primary database caused severe CPU contention and API latency spikes during peak user traffic.

2. What I Initially Thought

3. What I Learned

Operational DBs store data in row-oriented Heap Pages (optimal for single-row updates). Analytical queries aggregate specific columns across millions of rows (requiring columnar storage and decoupled processing layers).

4. What I Built

Decoupled Medallion architecture for DataPulse: App DB → Python Batch Extractor → ADLS Gen2 Bronze Raw → PySpark Silver/Gold Delta Tables → Snowflake Analytical Serving.

def extract_incremental_batch(connection, last_watermark, batch_size=10000):
    query = """
        SELECT id, tenant_id, title, status, created_at, updated_at
        FROM tasks WHERE updated_at > %s ORDER BY updated_at ASC
    """
    with connection.cursor(name="batch_extract_cursor") as cursor:
        cursor.itersize = batch_size
        cursor.execute(query, (last_watermark,))
        while rows := cursor.fetchmany(batch_size):
            yield rows

5. The Experiment

6. What Went Wrong

7. Engineering Decision & Trade-offs

Chose asynchronous batch extraction to ADLS Gen2 and Spark instead of read-replicas because read-replicas still enforce row-oriented storage and do not scale for complex multi-year analytics.

8. What I Would Do Differently in Production

In a high-throughput production environment, implement Change Data Capture (CDC) using Debezium and Kafka to stream PostgreSQL write-ahead logs (WAL) without querying DB tables.

Expected / Verified Evidence