An hourly ETL that can safely re-run itself

Three sources pulled in parallel into one warehouse, written with idempotent upserts so re-running the same window never duplicates rows. A forecast model then flags days that look wrong.

Pipeline

trigger

Cloud Scheduler

Hourly tick (or manual /api/marketing/etl/run).

→

pull

3 sources in parallel

Meta Ads, GA4 Data API, Firestore - different APIs, different tables.

→

load

Idempotent upsert

NDJSON load-job into a temp table, then MERGE on the key.

→

detect

Anomaly pass

BigQuery ML ARIMA_PLUS over fresh data writes alerts.

Why load-job + MERGE, not streaming insert

Streaming-inserted rows sit in a buffer that cannot be MERGE-d for ~90 minutes. An hourly ETL that re-runs the same window would either duplicate rows or fail. Loading into a temp table via a load-job makes rows visible immediately, so the MERGE is correct on every run.

Source	Grain	Note
Meta Ads	date x ad	Daily insights with `time_increment=1`, enriched with campaign status
GA4	date x UTM x platform	Web sessions + a fixed allowlist of funnel events
Firestore	date x event x UTM	Source of truth for mobile conversions (GA4 under-fires Flutter events)

Anomaly detection

Build series

180-day daily series per metric (sessions, submissions, spend).

→

ARIMA_PLUS model

CREATE OR REPLACE MODEL - idempotent, holiday-aware.

→

Detect

ML.DETECT_ANOMALIES at 0.98 over the last 14 days.

→

Write alerts

Direction, % deviation, severity into the alerts table.

Tolerant by design: a missing model is auto-created on the next run, so a fresh environment self-heals without a manual bootstrap step.

Relay - anonymized engineering case study. Code in ../src/marketing.