The first report I sent a client and the same report a week later did not match, and I had changed nothing. That is not a bug you fix once. It is the normal behavior of every source a marketing pipeline reads, and the architecture either accounts for it or quietly produces numbers that nobody in the building trusts.
The thesis of this page in one sentence: ad platforms and CRMs are not well behaved data sources. They restate history, they arrive late, they mutate rows in place, and their own interface disagrees with their own API. An ingestion design that assumes otherwise is not simpler, it is just wrong in ways you find out about in front of a client.
Quick answer
- Extract incrementally with a per-source lookback, never "everything since last time". Every run re-reads a deliberate overlap.
- Load with an upsert on a stable natural key, so a re-run is boring. Re-running should be the first thing you try, not the last thing you dare.
- Keep the landing layer exactly as it arrived, and build the model in a separate transactional step. Then any number in a report can be traced back to the rows that produced it.
- Treat sources with a retrieval horizon as urgent. Some history is not slow to fetch, it is unavailable, and no budget fixes that later.
- Isolate clients or brands with a schema per tenant, because a forgotten filter on a shared table is a cross-client leak.
How does each source misbehave, specifically?
Generic advice about "handling late data" is useless because the sources fail differently. Naming the failure mode per source is what sets the parameter.
| Source | How it misbehaves | What that forces |
|---|---|---|
| Web analytics export | Data matures for a couple of days after the fact | Re-read a rolling window, never trust the most recent day |
| Ad platform cost and conversions | Restates past days, especially conversions that land on the click date | Lookback long enough to cover restatement, and never cache a "final" number too early |
| Call tracking | A call record is edited after the call: tagged, scored, corrected by a human | The longest lookback of the three, because the edit is on human time |
| Booking or CRM outcomes | Status changes long after creation, sometimes twice | Track the update timestamp, not the create timestamp, or you will miss every change |
| Click level identifiers | Retrievable only inside a hard horizon, and often one day per request | Turn this extractor on first. History outside the horizon is gone |
That last row is the one that costs real money. Everything else you can fix later at the price of some compute. Click level history you cannot buy back at any price, which makes it the first thing to switch on and the reason I would rather ship an ugly extractor this week than a pretty dashboard next month.
What does the flow actually look like?
Three properties of that picture are doing the work.
The watermark advances only after a successful run. A job that dies halfway does not get to claim it read the day. This sounds obvious and is the single most common bug I find in inherited pipelines, because the naive implementation stamps the watermark when the job starts.
The landing layer is never edited. It holds what the source said, including the parts that later turn out to be wrong. Every figure downstream can be walked back to a row. When a client asks why a number moved, the answer is a query, not an apology.
The run journal is part of the product. Rows read, rows changed, duration, per source, per run. Without it, "the report looks low" is an argument. With it, you can see that one extractor returned zero rows for three days while everything else ran fine.
Why is a re-run allowed to be boring?
Because it will happen whether you allow it or not. Loading with an upsert keyed on a stable natural identifier means running the same day twice changes nothing, so the correct first response to almost any pipeline problem becomes "re-run it and look again".
Here is the difference, with illustrative numbers on a synthetic tenant:
| Day | Rows in source | Insert-only load | Upsert on natural key |
|---|---|---|---|
| Mon, first run | 1,000 | 1,000 | 1,000 |
| Mon, retry after a timeout | 1,000 | 2,000 | 1,000 |
| Tue, source restates Monday | 1,010 | 3,010 | 1,010 |
| Reported Monday total | 1,010 | wrong and unrecoverable without a cleanup | correct |
The insert-only column is not a strawman. It is what you get from the obvious implementation, and the damage is discovered a month later when a total is inexplicably high.
How do several clients or brands share one pipeline?
One database, one schema per tenant, shared compute. The alternative, a single set of tables with a tenant column on every row, is more convenient right up until someone writes a query without the filter, and then it is a cross-client data leak rather than a bug.
- One file per tenantcredentials, source toggles, lookback overrides
- Vocabulary mappingtheir status names to the canonical ones
- schema: tenant Alanding, model, serve views
- schema: tenant Bsame shape, no shared table
- metaregistry, watermarks, run journal
The rule underneath it: everything client specific lives in one config file, and nothing client specific lives in code. Credentials, source toggles, lookback overrides, the mapping from their vocabulary to the canonical one. Onboarding then becomes copy a config, create a schema, run the backfill, rather than a fork of the pipeline that drifts for a year.
The honest cost of schema per tenant is migrations. A column added to the model has to be applied N times, and that turns into a script early. I take that trade because the failure it prevents is not a bug report, it is a phone call about another client's data.
What breaks anyway, and how do you find out?
Assume the pipeline will fail silently, because the most expensive failures are the quiet ones. Nothing errors. A source returns zero rows and everything downstream dutifully reports zero, which looks exactly like a slow week.
So the checks are on the shape of the data, not just on exit codes:
- Rows loaded per source per day, compared against that source's own recent range. Zero is an alert, not a data point.
- Freshness per source. The question is not "did the job run" but "how old is the newest row it has".
- Rows against distinct keys per day. Anything above one means a duplicate is forming, usually a week before anyone notices in a report.
- Totals against the source's own interface, on a schedule, with a tolerance agreed in writing before the first report ships. Some gap is structural. An undefined gap is an argument waiting to happen.
Alerts carry numbers, never records, and they say what to do: which source, how stale, what a healthy value looks like. An alert that only says something is wrong trains people to ignore alerts.
This ingestion layer is the unglamorous half of marketing analytics that ties spend to revenue, and it is what the offline conversion loop stands on. If the report and the ad account disagree in your account right now, the reconciliation walkthrough is the place to start, because the gap usually has a name.
Status, so nothing here is oversold: the loop that reports outcomes back to an ad platform runs in production for a real client. The generalized multi source, multi tenant version described on this page is my own reference implementation, exercised against synthetic data with unit tests and a full smoke run, not a platform with clinics on it. The failure modes in the table above are the ones I have actually hit, which is why they are the ones the design is shaped around.
Tags
Frequently asked questions
Why do my marketing numbers change days after the fact?
Because most marketing sources are not append-only. Analytics data matures for up to about three days, ad platforms restate spend and conversions after the click date, and call tracking rewrites a call record when it is tagged or reviewed later. A pipeline that reads only new rows since the last run will never see any of those edits.
What is a lookback window in a data pipeline?
It is the overlap you deliberately re-read on every run. Instead of asking a source for everything since the last watermark, you ask for everything since the watermark minus a few days, then upsert. Late edits inside that window get corrected automatically, and the cost is re-reading a small amount of data you already have.
How long should the lookback be for each source?
Per source, not global, and set by how that source misbehaves rather than by a round number. Analytics needs roughly the platform's own maturation period. Ad spend needs long enough to cover restatements. A call or CRM source needs long enough to cover a human editing a record after the fact, which is usually the longest window of the three.
Why does a re-run have to be idempotent?
Because at some point a job will run twice: a retry, a manual re-run after a fix, an overlapping schedule. If loading is an insert, the second run doubles your data and the report inflates. If loading is an upsert on a stable natural key, the second run changes nothing, and re-running becomes a safe first response to any problem.
What is the risk of starting attribution work late?
Some sources cannot be backfilled. Click level data in particular has a hard retrieval horizon, so history before that window is not slow to get, it is gone. If a source has a horizon, its extractor is the first thing to turn on, before the dashboard and before anything is pretty.
Should each client or brand get its own database?
Its own schema, in one database, is usually the right trade. A shared table with a tenant column relies on every query remembering a filter, and one forgotten filter is a cross-client data leak. A schema boundary is enforced by the database rather than by discipline, and it costs you having to run migrations more than once.
Want this loop closed on your account?
I connect your EHR, booking system, and calls to Google Ads so it counts patients who actually showed up. HIPAA-conscious, fixed price, verified in the account.