Data Pipeline

Data Pipeline — Neon → BigQuery (dbt)

The data pipeline syncs Neon (OLTP / application database — the source of truth) into BigQuery (OLAP / analytics warehouse). Neon is where the app reads and writes; BigQuery is where analytics, reporting, and data-moat modeling run. Data flows app → warehouse (the reverse of the older Supabase-era pipeline).

Architecture

App (Cloud Run) ──writes──▶ Neon (Postgres · OLTP · source of truth)
                                  │
                    extract-load (incremental, nightly)
                                  ▼
                           BigQuery (raw_*)
                                  │
                              dbt (transform, in BigQuery)
                                  ▼
                           BigQuery (marts) ──▶ BI / analytics / ML

1. The app reads/writes Neon for low-latency OLTP.
2. A scheduled extract-load job copies changed rows from Neon into BigQuery raw_* tables — incrementally, WHERE updated_at > <watermark>.
3. dbt (BigQuery adapter) transforms raw_* → marts within BigQuery.

Directory Structure

dbt/
├── dbt_project.yml
├── profiles.yml.example      # BigQuery target
├── models/
│   ├── staging/
│   │   └── stg_patients.sql          # cleans raw_patients (from the Neon extract)
│   └── marts/
│       └── active_patients.sql       # incremental mart, materialized in BigQuery
└── README.md

jobs/
└── neon_to_bq.py             # extract-load: changed Neon rows → BigQuery raw_*

Extract-Load — neon_to_bq.py

Reads changed rows from Neon and loads them into BigQuery raw tables, watermarked to minimize egress:

# conceptual — pulled from Neon each run, then loaded into BigQuery raw_*
SELECT * FROM patients WHERE updated_at > :last_watermark;

• Runs as a scheduled Cloud Run Job (neon-to-bq) — a standalone job, not part of the backend/worker service. Cloud Scheduler (or the nightly dbt.yml workflow) triggers it.
• Loads into BigQuery raw_* via the google-cloud-bigquery client.
• Persists the watermark (max updated_at) so each run transfers only new/changed rows.

Cross-Cloud Egress

Neon runs in AWS us-west-2; BigQuery in GCP. The WHERE updated_at > watermark keeps the Neon → BigQuery transfer incremental — only new/changed records cross the boundary. Full-table cross-cloud scans are prohibited (egress cost + compliance risk).

Key Model — active_patients.sql (in BigQuery)

Incremental materialization minimizes reprocessing on each run:

{{ config(materialized='incremental', unique_key='patient_id') }}
 
SELECT
    patient_id,
    first_name,
    last_name,
    date_of_birth,
    updated_at
FROM {{ ref('stg_patients') }}
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Scheduling — GitHub Actions

The pipeline runs nightly via .github/workflows/dbt.yml: