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.
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 > watermarkgate 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_progressprojects orlast_updatedwithin 180 days), pruned after every load. Live queries stay fast; history stays cheap. task_didas the dedupe/upsert key. Validated against a live production table (2,599,284 rows: 0 null, 0 duplicatetask_did) before committing to it, with anmd5(project|asset|task_name)fallback for defense.- Direct Postgres connection, not the Supavisor pooler. This is the specific fix for the
EMAXCONNSESSIONcascade that took down the predecessor. - Pilot-first rollout via config, not code. A
GC_ORG_DIDSallowlist 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_progressORlast_updatedwithin 180 days; cold storage: hive-partitioned Parquet, deduped on read - Dedupe key
task_didvalidated 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_updatedremoved 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_didis what made theON CONFLICTupsert safe, and themd5fallback covers the day that assumption ever breaks.
Links
GitHub repo. A TDD-built pipeline (168 tests passing); currently in a two-org pilot ahead of full rollout.