Ad API Pipelines That Restate Correctly, Not Just Load

A pipeline that loads yesterday once and never looks back is wrong by the second week. Meta keeps restating conversions for 28 days after the click, Google Ads figures move as offline and imported conversions land, and Yahoo! JAPAN Ads settles later still. The design decision that matters is the re-extraction window. Here it is a rolling 35 days re-pulled daily and merged into date-partitioned tables, so history corrects itself instead of accumulating a quiet, growing understatement in every number older than a month. The pipeline: Managed connectors where they exist and behave — Fivetran or Airbyte for Google Ads, Meta, TikTok and LinkedIn. Custom extraction in Python on Cloud Run for Yahoo! JAPAN Ads and LINE Ads, where managed coverage is thin or absent and the reporting APIs need their own pagination, rate limiting and asynchronous report polling. Orchestration in Dagster or Cloud Composer with declared dependencies between assets rather than a nightly cron chain that hopes each step finished. Modelling in dbt. Landing in BigQuery or Snowflake, whichever your organisation already runs; I do not introduce a second warehouse to satisfy a preference of mine. Loads that survive being run twice: Every load is idempotent — a merge on a natural key into a partitioned table, never a blind append. A watermark table records what was extracted and when, so a rerun after a failure produces the same rows instead of duplicates that surface three weeks later as inflated spend. Each run writes row counts and spend totals per platform per day, and an alert fires when a day thought final moves by more than a set threshold. That single check has caught more real problems than any dashboard I have built: a currency change, a duplicated account link, a platform restating outside its documented window. Reconciliation is part of the build: Monthly platform invoices load alongside API spend and are compared automatically. When the API says one figure and the invoice says another, the pipeline reports it rather than your controller discovering it during a quarterly review. This is the check most pipelines skip and the first thing finance asks for once they learn it is possible. What you own afterwards: Everything runs in your cloud project, under your service accounts, in your repository, with credentials in your secret manager. No component is hosted by me. Runtime cost is estimated in writing before the build; for accounts at this size it usually settles between two and six hundred dollars a month across warehouse and orchestration, with any connector subscription held directly by you. What I decline: Hand-rolling extraction for a platform a managed connector already covers properly — it is billable work that becomes your maintenance burden the first time an API version is deprecated. Streaming or near-real-time delivery for reporting that is read weekly. Pipelines feeding a dashboard nobody has committed to using, because the pipeline is not the deliverable and the decision at the end of it is. And I do not build customer-level exports into ad platforms for health and wellness clients without written sign-off from your counsel on the transfer. Fit and limits: Right when you run four or more paid platforms, at least one of them Japanese, and somebody is currently exporting CSVs by hand on Monday mornings. Wrong when you run a single platform and a spreadsheet that works — that is a scheduled export, not a pipeline, and I will say so on the call rather than quoting for it.