Case study · Prototype · public code

GC Asset Lake

Lakehouse-lite ETL: incremental API sync with hybrid hot (Postgres) / cold (Parquet + DuckDB) storage

PythonDuckDBParquetSupabasepsycopg2
30-second version
Problem
A nightly full re-pull of ~11.8M rows (~13 GB JSON) hit the 60-minute runner limit and crashed the pooler.
Built
Watermark-gated incremental extraction with a hot Postgres window and cold hive-partitioned Parquet deduped on read by DuckDB.
Result
168 passing unit tests; prototype in a two-org (~62 project) pilot, not yet replacing the nightly pipeline.
Role
Sole engineer

The Problem

An earlier pipeline re-exported the entire GC asset-task dataset every night (roughly 11.8M rows, ~13 GB of JSON) to discover that almost nothing had changed. Two things broke as a result: GitHub Actions hit the 60-minute runner ceiling mid-transform, and the Supabase connection pooler death-cascaded (EMAXCONNSESSION) under the connection load. The work was real, but the shape of it was wrong. It was a full re-pull to capture a few dozen changed projects.

gc-asset-lake re-architects it as an incremental, two-tier load. Only projects whose last_updated changed get pulled. Recent data lives hot in Postgres for live queries; full history lives cold as Parquet on object storage, queried with DuckDB, so you stop paying hot-database prices for cold history.

Architecture

Extraction is gated on a watermark; transformed rows fan out to a cold tier (full history) and a hot tier (bounded recent window), orchestrated per run with the mode (incremental / sweep / backfill) deciding what's selected and written.

rendering diagram…

orchestrate.py builds the project-selection SQL, excluding internal/test/archived orgs and, in incremental mode, adding the last_updated > watermark gate, then extracts each project over a 6-worker thread pool (with one-shot 401 re-auth). transform.py normalizes PascalCase → snake_case and computes a dedupe_key (task_did, with an md5(...) fallback). Rows then go two ways: coldtier.py writes hive-partitioned Parquet (org_did/load_date) that DuckDB dedupes on read via row_number(), with monthly compaction; hottier.py upserts into Postgres (ON CONFLICT (task_did)) and prunes anything outside the recent window. Watermarks are tracked in pipeline.gc_lake_runs, and the DB connection is direct, deliberately bypassing the pooler that killed the old pipeline.

Key Decisions

  • Incremental by watermark, with a monthly full sweep as a safety net. The last_updated > watermark gate means a run touches the ~30–60 changed projects instead of all of them; a periodic sweep backstops any missed change.
  • Hot/cold tiering. Cold = full-history Parquet (compressed from ~13 GB JSON), deduped on read; hot = a bounded Postgres window (in_progress projects or last_updated within 180 days), pruned after every load. Live queries stay fast; history stays cheap.
  • task_did as the dedupe/upsert key. Validated against a live production table (2,599,284 rows: 0 null, 0 duplicate task_did) before committing to it, with an md5(project|asset|task_name) fallback for defense.
  • Direct Postgres connection, not the Supavisor pooler. This is the specific fix for the EMAXCONNSESSION cascade that took down the predecessor.
  • Pilot-first rollout via config, not code. A GC_ORG_DIDS allowlist scopes Stage A to two orgs (~62 projects); clearing it opens Stage B to all non-internal orgs, with no code change to widen the blast radius.

Code Samples

The incremental gate lives in the project-selection SQL. The same query serves sweep/backfill (no gate) and incremental (watermark clause):

def select_projects_sql(mode: str, has_org_filter: bool) -> str:
    lines = [
        "SELECT org_did, project_did, last_updated",
        "FROM data_staging.stg_projects",
        "WHERE org_name <> 'INTERNAL_ORG' AND org_name NOT LIKE 'Testing%'",
        "  AND project_name NOT LIKE 'x_Archive:%' AND status <> 'pending'",
    ]
    if has_org_filter:
        lines.append("  AND org_did = ANY(%(org_dids)s)")
    if mode not in ("sweep", "backfill"):
        lines.append("  AND last_updated > %(watermark)s")  # incremental gate
    lines.append("ORDER BY org_name, project_name")
    return "\n".join(lines)

Dedupe happens on read in the cold tier. DuckDB keeps the latest row per key across every Parquet file:

def latest_view_sql(base: str) -> str:
    glob_posix = (pathlib.Path(base) / "asset_tasks" / "**" / "*.parquet").as_posix()
    return (
        "SELECT * EXCLUDE (rn) FROM (\n"
        "  SELECT *, row_number() OVER (\n"
        "    PARTITION BY dedupe_key ORDER BY loaded_at DESC\n"
        f"  ) AS rn FROM read_parquet('{glob_posix}', hive_partitioning = true)\n"
        ") WHERE rn = 1"
    )

Metrics

These are verifiable facts about the system, not measured outcomes:

  • ~1,540 lines of Python across 8 modules; 168 passing unit tests (TDD)
  • 6 dependencies (requests, duckdb, pyarrow, psycopg2-binary, python-dotenv, pytest)
  • 3 run modes (incremental / sweep / backfill); 6-worker thread pool for extraction
  • Hot window: in_progress OR last_updated within 180 days; cold storage: hive-partitioned Parquet, deduped on read
  • Dedupe key task_did validated against 2,599,284 live production rows (0 null, 0 duplicate), the basis for the key, not GC rows processed
  • Designed to replace a nightly 11.8M-row / ~13 GB JSON full re-pull

What I Learned

  • The fix was the shape of the work, not the size of the machine. No amount of tuning saves a full nightly re-pull; gating on last_updated removed the 60-minute timeout and the pooler cascade at once.
  • Tier by access pattern, not by instinct to keep everything hot. Most queries hit recent data; pushing history to deduped-on-read Parquet kept Postgres small and cheap without losing a single row.
  • Pick the upsert key by proving it, not assuming it. Counting nulls and duplicates across 2.6M real rows before betting on task_did is what made the ON CONFLICT upsert safe, and the md5 fallback covers the day that assumption ever breaks.

GitHub repo. A TDD-built pipeline (168 tests passing); currently in a two-org pilot ahead of full rollout.