Case study · In production · public code

Async ETL Platform

Async multi-pipeline ETL into Postgres: about 14 scheduled pipelines across 25 GitHub Actions workflows

PythonasyncpgPostgresSupabaseGitHub ActionsApps Script
30-second version
Problem
A single sequential nightly script took about six hours, timed out, and silently dropped rows past the API's ~1,000-row cap.
Built
Async multi-pipeline ETL into Postgres: per-day API chunking, asyncpg binary COPY on a background event loop, atomic clear-and-reload CTEs.
Result
12.2M+ rows across 111 tables, about 14 pipelines nightly; the task transform fell from ~44 to ~3 minutes.
Role
Sole engineer

The Problem

Nightly ingestion of millions of rows from the vendor's project-management API (plus Gmail, Google Calendar, Google Forms and Google Sheets) into a Postgres warehouse, feeding analytics views consumed by internal dashboards, scheduled reports and internal apps. The naive approach, a single Python script pulling everything sequentially, took roughly six hours, regularly timed out, and silently dropped data when the upstream API returned more than ~1,000 rows for a single date range.

Today it's about 14 scheduled pipelines (counted by data source) running across 25 GitHub Actions workflows: the nightly task extract, orgs/projects, timers, user priorities, QA forms, daily reports, revenue emails, calendar events, timer corrections, targeted re-extracts and the invoicing feed, plus watchers, HR roster syncs, and dispatches into the date-validator and email-parser systems. Each must be parallel, idempotent, and observable: one extractor failing can't take down the others, and re-running produces the same result every time.

Architecture

rendering diagram…

The whole fleet was migrated off Windows Task Scheduler (2026-05-28) onto GitHub Actions. Most workflows are fired by Google Apps Script time-driven triggers posting repository_dispatch (GitHub's own cron can lag by hours); a smaller set of watchers and syncs use GitHub cron, and some run as event chains off another workflow's completion. A single asyncpg pool (min=4 / max=20) runs on a dedicated background event-loop thread so synchronous extractor code can submit queries via run_coroutine_threadsafe(). A ThreadPoolExecutor runs extractors in parallel, streaming rows over asyncpg's binary COPY protocol into six schemas: data_raw (JSONB) → data_staging (normalized) → analytics (views + materialized views), plus reference, pipeline, and agent. After extraction, a backfill pass resolves derived foreign keys, then materialized views refresh one at a time.

The pipelines

Each pipeline has its own source and extraction method; they share the warehouse, the asyncpg/COPY machinery, and the trigger layer, which is why this is one platform, not a folder of scripts.

PipelineSourceWhat it does
tasks (nightly core)Vendor APIThe largest dataset; per-day chunked to dodge the ~1k truncation cap
orgs / projectsVendor APIOrg + project master data
timerVendor APILabor timer activities
user prioritiesVendor APITask-prioritization signals
QA formsGoogle FormsQuality-form submissions
daily reportsVendor APIDaily operational report rows
revenue emailsGmailFinance figures parsed from inbound email
calendar eventsGoogle CalendarLeave and schedule events, parsed into structured rows (deterministic first, AI fallback)
timer correctionsForm → Sheets → DBReconciles and de-duplicates timer entries
targeted tasksVendor APIReport-driven re-extract of a subset
invoicingVendor APIInvoicing-form data; email dispatcher
HR rosterGoogle SheetsEmployee and schedule-change sync, with a gap watcher

Shadow incremental walker with a drift audit

The nightly task extract is a full reload. To get fresher data without trusting a new code path blindly, an incremental walker runs hourly in shadow, writing to separate shadow tables alongside the nightly reload.

The walk goes project → asset list → task lists, and skips as much as it can: a project is skipped when its content watermark has not moved, and task lists are fetched only for assets whose stored last-updated value changed. Writes are guarded upserts, deletions are handled by a keep-list reconcile, and a weekly full-walk sweep catches what an incremental walk cannot see.

The audit compares shadow against current, per project. It does not demand equality. It demands that every difference is explained by one of four allowed classes (for example, ad-hoc tasks the bulk export drops, or bounded lifecycle lag that is tolerated only while the weekly sweep is fresh). One result row per project is written on every run. One run a day is a strict gate that fails the workflow on unexplained drift; daytime runs only record. The walker is still a pilot: the nightly reload remains authoritative. Its successor is the event-driven sync on Google Cloud.

Pipeline health watch and auto-rerun

Two separate mechanisms:

  • Health watch (daily, alert-only). Four checks: failed database cron jobs since the last seen run id; refresh chains whose last success is too old; zero-row checks on key tables and views; and a rebuild function whose max execution time is approaching its statement timeout. A healthy run is silent; any finding sends one plain-text email. After alarming on the execution-time check it resets the statistic, so the alarm re-arms instead of re-sending the same high-water mark every day. A crash of the watcher itself exits non-zero so it shows up red.
  • Auto-rerun (one pipeline). A workflow_run workflow watches the calendar-events pipeline. If a run failed and it has been attempted fewer than three times, it waits and re-runs only the failed jobs, for at most two automatic reruns. It is deliberately scoped to the one pipeline whose failures are usually transient.

HR sheet-to-roster sync

HR maintains the roster in Google Sheets. Three workflows keep the warehouse's employee reference in step with it:

  • Employee sync reads the active-employee sheet, maps headers, diffs against the reference table and writes new hires, updates and resignations as new history rows with an effective date. It is dry-run by default and needs --apply. It runs from a daily cron and from a sheet menu; the effective-date input is allow-listed to YYYY-MM-DD before it reaches a shell.
  • Schedule-change sync parses every tab of the schedule-change sheet. A wipe guard stops a partial read from replacing a full snapshot, and the refresh is upsert-then-prune by run id, so the table is never empty mid-run.
  • Roster gap watcher finds people who are submitting reports but have no roster row, infers what it can from warehouse data, appends a row to the HR sheet and runs the employee sync. Cases it cannot fix are flagged. It posts one chat summary per run and stays silent when there is nothing to report.

AI calendar extraction

Calendar entries are free text typed by people, so the summaries vary. Parsing is layered:

  1. A cache lookup on the whitespace-normalized summary.
  2. A deterministic parser that returns a shape with a confidence score.
  3. Only if that confidence is below the gate (0.8) does it call Claude Haiku (claude-haiku-4-5) with a single prompt that lists the known leave codes and teams and asks for one JSON object. This is a prompted-JSON call, not tool use or a structured-output feature.

The reply is stripped of code fences, the first JSON object is extracted and parsed, and fields are read defensively with a fixed confidence of 0.75, below the deterministic gate on purpose. Any exception or unparseable reply returns a needs_review shape with confidence 0.0; the function never raises, so an API outage cannot fail the pipeline. Results are cached with the model id and prompt version, so a repeated string costs nothing. Known limits: there is no per-run call cap beyond max_tokens=512, the allowed values are not enum-checked after parsing, and a needs_review result from a transient API failure is cached too until it is cleared.

Key Decisions

  • Per-day API chunking. The vendor API silently truncates a wide date range at ~1,000 rows: no error, just fewer rows. The timer extractor splits each range into one API call per calendar day, which sidesteps the cap entirely. Cost: more API calls. Benefit: zero silent data loss.
  • asyncpg on a background thread, not in-process. A large synchronous extractor codebase couldn't be rewritten fully async at once. Running one pool on its own event loop and bridging via run_coroutine_threadsafe() lets sync callers stay sync while the DB layer is fully async and thread-safe.
  • Atomic clear+reload via a data-modifying CTE. The full-refresh tables used to clear in a separate auto-committed DELETE, then INSERT. When the big INSERT later hit statement_timeout, the table was already empty, and committed. Folding the clear into the insert as WITH cleared AS (DELETE ... RETURNING 1) INSERT ... makes clear-and-reload one statement: a timeout rolls back both, so a table is never left empty.
  • Six-schema split. data_raw holds unmodified API responses; data_staging holds transformed rows; analytics holds views and MVs; reference, pipeline, and agent hold lookups, run metadata, and assistant metadata. Every layer is rebuildable from the one beneath it.
  • Shadow first, then cut over. New extraction paths run beside the old one and must pass an audit that explains every difference before they are trusted.
  • The model is the fallback, never the parser. Deterministic code handles the common case; the LLM only sees what the parser is unsure about, and its failure mode is a review flag.

Code Samples

Async DB pool on a background event-loop thread, with retry on transient startup failures, letting synchronous callers reach asyncpg:

class PipelineDB:
    def start(self):
        self._thread = threading.Thread(target=self._run_loop, daemon=True, name="db-loop")
        self._thread.start()
        if not self._ready.wait(timeout=30):
            raise RuntimeError("Database pool failed to initialize within 30s")

    async def _create_pool(self):
        for attempt in range(1, 4):                 # 3 attempts, 5s/10s backoff
            try:
                self._pool = await asyncpg.create_pool(
                    DSN, min_size=4, max_size=20, command_timeout=300,
                    init=_init_connection, ssl=ssl_ctx,
                )
                return
            except Exception:
                if attempt == 3:
                    raise
                await asyncio.sleep(5 * (2 ** (attempt - 1)))

    def _run(self, coro):                            # sync bridge
        return asyncio.run_coroutine_threadsafe(coro, self._loop).result()

Atomic clear+reload, where a statement_timeout can no longer empty the table:

# A separate DELETE then INSERT can leave the table EMPTY if the INSERT
# times out after the DELETE commits. The CTE makes it one statement.
sql = (
    f"WITH cleared AS (DELETE FROM {SCHEMA_STAGING}.{table} RETURNING 1) "
    f"INSERT INTO {SCHEMA_STAGING}.{table} (...) "
    f"SELECT ... FROM {SCHEMA_RAW}.{raw_table} WHERE run_id = $1"
)
db.execute(sql, run_id, statement_timeout=900)

The AI fallback degrades to a review flag on any failure (lightly simplified):

def extract_with_ai(summary: str, client=None) -> dict:
    """Call Haiku for one summary. Degrades to needs_review on any error."""
    try:
        client = client or anthropic.Anthropic(api_key=os.getenv("CLAUDE_API_KEY"))
        resp = client.messages.create(
            model=MODEL, max_tokens=512,
            messages=[{"role": "user", "content": _build_prompt(summary)}],
        )
        parsed = _parse_ai_json(resp.content[0].text)
        return _shape_from_ai(parsed) if parsed else _needs_review_shape()
    except Exception:
        return _needs_review_shape()

Metrics

  • Warehouse scale: 12.2M+ rows across 111 tables in six schemas.
  • Core dataset: the nightly task table is transformed server-side in ~2-3 minutes, down from ~44 minutes when the same logic ran in Python.
  • Orchestration: 25 workflow files in .github/workflows; about 14 scheduled pipelines by data source. Most are fired by Apps Script time triggers via repository_dispatch, 8 have a GitHub cron schedule, and the rest are event chains or manual.
  • Schema evolution: 278 numbered SQL migration files (highest number 267).

What I Learned

  • Silent truncation bugs hide in the small. The API never returned an error; it just returned fewer rows than asked. Comparing per-day counts against expectation was the only way to catch it. Validate row counts as a first-class signal, not an afterthought.
  • Atomicity is a correctness property, not a nicety. A two-statement clear-then-reload looks fine until the reload times out and you're staring at an empty table in production. Folding the delete into the insert with a data-modifying CTE made the failure mode impossible.
  • Sync-async bridging is a real pattern, not a hack. Pure-async rewrites of large sync codebases stall. A pool on a background event loop unlocks async DB access inside sync callers with one helper class.
  • An audit that requires equality never passes. Two extraction paths over a live system always differ a little. Naming the allowed classes of difference, and failing on anything else, is what made the shadow run useful.

Public reference repository. The production system runs in a private employer repository; the numbers on this page describe that system.