Adstack Sync
An advertiser running campaigns on Google, Meta and TikTok has their most important numbers sitting inside three systems they don't control — in three currencies, three timezones, and three incompatible definitions of a conversion. Getting those numbers into one place they own is a whole product category: Supermetrics, Fivetran, Funnel.io.
This is a small, working one. Three mock platform APIs reproduce the real ones' response shapes and failure modes, a pipeline pulls from them on a schedule, and a warehouse holds the result. The interesting part was never moving the rows — it's staying correct while the platforms revise history after the fact, rename fields without warning, throttle you mid-fetch, and report failures as HTTP 200.
Below you can break it on purpose. Run a clean sync first, then advance the simulated clock and watch a day that had already “closed” change its numbers — then tick the faults and check whether the warehouse still holds.
Run a sync
Break something first, then check whether the warehouse stays right. You get your own isolated warehouse.
Inject a fault
Incremental from here: Google 3 days, Meta 7, TikTok 2 — each sized to that platform's attribution window, not guessed.
Staying correct when the source keeps changing its mind
Moving rows from an API into a database is the easy part. Everything hard about this category comes from four properties of ad platform reporting data, and the pipeline is organised around surviving them.
Restatement. Google and Meta credit a conversion back to the date of the click, so someone who clicks on Monday and buys on Thursday makes Monday's number go up on Thursday. A day is not final until its attribution window closes. Measured from this warehouse's own log: Google has ~72% of a day's conversions on day zero and ~97% by day three; Meta starts at ~55% and is still climbing on day six.
Schema drift, rate limits, and soft errors. Fields get renamed upstream, platforms throttle mid-fetch, and at least one returns HTTP 200 for an authentication failure. Each of these has a naive behaviour that produces a wrong number with no error anywhere — which is the actual danger, because nothing alerts.
Semantic mismatch. Three currencies, three reporting timezones, three definitions of a conversion. Google's and Meta's 2026-09-01 overlap by nine hours, not twenty-four.
The fault toggles above are those failures, injected on demand. The claim the demo is making is that the numbers survive all five — and 75 automated assertions check it on every change, running the same SQL against node:sqlite that production runs against D1.
Key design decisions
- Every write is an UPSERT on a natural key (platform|account|campaign|adset|date), never an INSERT. Ad platform reporting APIs have no row IDs — you can only ask for a date range and get whatever the platform currently believes — so the grain has to be the identity. That one decision buys idempotent re-runs, duplicate absorption, and in-place restatement at once.
- Lookback windows are derived from each platform's attribution window, not chosen for convenience: Google 3 days, Meta 7, TikTok 2. Too short creates a permanent downward bias that never self-corrects; too long burns API quota, which is the resource that actually runs out. Copying one connector's setting onto another is how a pipeline ends up quietly wrong on exactly one source.
- Fields are never read by a single hard-coded name. Each is declared with a primary path plus known aliases, and a fallback firing is logged as an early warning. Without this, a platform renaming a field turns spend into $0 while every job stays green — the failure mode a human notices a week late and monitoring never does.
- Transport success is not application success. TikTok returns HTTP 200 with an error code in the body, so a client that trusts response.ok reads a dead token as a successful empty sync. A silent zero is worse than a loud failure, because nothing pages anyone.
- A failing connector yields a 'partial' run, not a failed one. The other two sources still land and the mart stays usable — the UI just has to be honest about which third is stale. Failing the whole run throws away good data to punish an unrelated source.
- The date column is deliberately not called date_utc, and the dates are not shifted. A daily aggregate can't be re-bucketed into UTC days without hourly data you were never given; shifting the label just moves a whole day's numbers onto the wrong day. The honest treatment is to pass the label through and store the timezone beside it.
- The destination sits behind a {query, batch, exec} interface. BigQuery was the obvious résumé choice and was rejected on evidence: its sandbox blocks DML entirely, so MERGE — the whole point of this project — cannot run there. D1 backs the live demo; a BigQuery adapter is a new file, not a rewrite.
- Chart colours were validated against this site's cream surface rather than eyeballed. Palettes anchored on the site's green failed colourblind separation (green vs rust reads as ΔE 2.7 under protanopia), so the brand gives way on the few square inches where matching it would cost a reader the ability to tell two series apart.
Tech stack
- Cloudflare Workers — Mock platform APIs + the pipeline itself, wired by a service binding
- Cloudflare D1 — SQLite warehouse — upserts, restatement log, quarantine
- SQL — Canonical schema, natural keys, ON CONFLICT merge, mart views
- node:sqlite — Test harness running the same SQL that runs in production
- Next.js + Cloudflare Pages — This site and this page