Direct-response advertising, in-house media buying and analytics
Facebook Ads Pipeline with Spark Fallback: BigQuery, Slack
The warehouse layer is real and runs: the transformation SQL, the winner view with cumulative lifetime metrics, and the MERGE that inserts new winners into an unreported table all execute against a BigQuery dataset. The fallback extraction script is parameterised with placeholder credentials and account identifiers rather than live ones; the winner thresholds are documented as examples to be set per account, and the Slack dispatch job plus Airflow orchestration are specified in the design rather than included in the repository, so this is validated warehouse and alert-state work, not a system verified end to end in live operation.
- Our role
- Data engineer, ingestion redundancy, warehouse modelling and alert state design
- Timeline
- Built as a layered warehouse design with a second ingestion path and alert state machine
- Sector
- Direct-response advertising, in-house media buying and analytics
At a glance
- Ingestion paths
- Two, a connector primary and a Spark fallback
- Fallback trigger
- Connector failure, quota limit or connector downtime
- Warehouse layers
- Bronze raw, Silver/Gold modelled
- Winner evaluation
- Cumulative lifetime spend, revenue and purchases per ad
- Alert state
- Two tables, unreported and reported
- Alert guarantee
- One Slack message per winning ad
- Winner thresholds
- Set per account; documented examples include CTR >= 2% and ROAS >= 2
What was breaking
A managed connector is the right first choice for ad data: it handles authentication, paging and schema changes, and it costs a fraction of maintaining extraction code. It also has one failure mode that no amount of care on your side prevents: it stops. Quota limits, upstream API errors, a connector-side outage, an expired authorisation. The pipeline downstream does not fail loudly; it simply has no new rows, which is far harder to notice than an error.
That silence is the real problem. Every reporting surface keeps rendering, showing yesterday's shape of the world with full confidence. By the time someone questions a number, the gap is several days wide and the team has been making budget decisions against stale data without knowing it. The cost is not the missing rows, it is the decisions taken while believing the data was current.
The second problem is on the other side of the warehouse. Detecting which ads are working is not hard in SQL (thresholds on click-through rate, return on spend, cost per acquisition, minimum spend and minimum conversions), but acting on it is a state problem. A query that selects every ad currently meeting the criteria returns the same ads every time it runs. Wire that to Slack and the channel fills with repeats of yesterday's news, which trains everyone to ignore it. Wire it to a daily digest instead and the ad that crossed into profitability this morning waits until tomorrow.
So the requirement was not 'alert on winners'. It was: evaluate continuously, notify once per ad, and never lose the record of what has already been announced, including across re-runs, backfills and whatever manual re-execution happens while the thresholds are being tuned. That is a state machine, and the only sensible place to keep its state is the warehouse itself.
A connector failure is silent
Nothing errors. Dashboards keep rendering stale numbers at full confidence, and the gap is usually discovered by someone questioning a figure days later.
Decisions made against data believed to be current
The loss is not the rows that did not load, it is the budget and scaling decisions taken in the window before anybody knew they were working from an old picture.
Repeated alerts become no alerts
A detection query with no state announces the same ads on every run. A channel that repeats itself gets muted, and then the one alert that mattered is muted too.
Digest cadence delays the decision that matters
An ad crossing into profitability is worth knowing about now, because the action it implies, more budget, is time-sensitive in a way that a weekly summary cannot serve.
Metric definitions multiply
When CTR, CPA and ROAS are recomputed in each report, an alert and a dashboard can disagree about the same ad, and the alert loses its authority.
What we built
The ingestion design is deliberately asymmetric. The managed connector stays the primary path, because it is the cheaper and better-maintained way to load ad data on a normal day. The fallback is a direct extraction against the Facebook API (Python with the Business SDK, concurrent creative lookups, Parquet staging and a load into the same raw tables) that is only invoked when the primary fails. Keeping it as a second path rather than replacing the connector means the usual case stays cheap, and the bad case stays covered.
What makes that work is that both paths terminate in the same place with the same schema. The fallback normalises its output to the raw table shape, so no transformation, model, dashboard or alert downstream has any awareness of which path supplied a given day. A fallback that produced a slightly different shape would push the branch into every query that reads the data, which is the version of this design that rots.
The warehouse is layered in the conventional three stages, and the reason is the honest one: the raw layer exists so that a modelling mistake can be corrected without re-extracting from an API that rate-limits you and may no longer serve the same historical window. Raw tables hold ingestion output unmodified, per entity, campaigns, ad sets, ads and daily metrics. SQL transformations then deduplicate, normalise schemas, join the entity levels together and compute the derived metrics once, producing the modelled daily performance fact table that every downstream object reads.
The winner view is where the interesting modelling sits. Ads are reported at ad-name grain rather than ad-id grain, because the same creative is relaunched under new ad ids and the team thinks in creatives, not ids. Per-ad-name attributes (creative type, angle, hook, product, strategist, editor, brief number) are collapsed with aggregate selection rather than joined, and the creative permalink and thumbnail are deduplicated to strictly one row per ad name with a row-number window before being joined in, including from a separate thumbnail table the fallback extraction populates.
Lifetime metrics are computed as cumulative sums ordered by date rather than as a flat total. That is the detail that makes the alert useful: it means the view knows the first date on which an ad's running spend, revenue and purchases satisfied the criteria, rather than only that the ad satisfies them today. Detection becomes a question about a crossing point, not about a current state.
Alerting is a two-table state machine. A scheduled query evaluates the criteria and MERGEs newly qualifying ads into an unreported-winners table, skipping anything already present in the reported-winners table. The dispatch job reads the unreported table, sends a structured Slack message carrying the ad, its campaign and the metrics that qualified it, inserts the ad into the reported table and removes it from the unreported one. Because the state is in the warehouse and the detection query excludes already-reported ads, re-running detection, scheduled or by hand while tuning thresholds, cannot produce a second announcement.
Using a MERGE rather than an INSERT for the detection step is the same kind of decision as holding state in tables rather than in a script. The query is written so that running it twice in a row has the same effect as running it once, which means the schedule can be aggressive without consequence and a manual re-run during threshold tuning is harmless. Idempotence at the query level is what allows the cadence to be a free choice rather than a risk.
A second path, not a replacement
The managed connector remains primary because it is cheaper and better maintained on a normal day. The hand-written extraction earns its keep only on the days the connector does not run, so it is kept as a fallback rather than promoted.
Both ingestion paths must land identically
The fallback normalises to the same raw schema, so nothing downstream branches on provenance. The alternative pushes a conditional into every query that reads the data, and that is the version that decays.
Keep raw data unmodified
The raw layer exists so a modelling correction can be rebuilt from stored rows instead of re-extracted from a rate-limited API whose historical window may have moved.
Alert state belongs in the warehouse
Two tables, unreported and reported, survive a redeploy of the dispatch job, are inspectable when someone asks why an ad did or did not alert, and make the detection query's exclusion a plain join rather than application memory.
Make the detection query idempotent
A MERGE that skips already-reported ads means running detection twice equals running it once. That turns schedule frequency and manual re-runs during tuning into free choices rather than sources of duplicate noise.
Report at creative grain, not ad id
The same creative returns under new ad ids. Aggregating to ad name and computing cumulative lifetime metrics across those ids is what makes a crossing point meaningful to the people who brief the work.
Non-negotiables
- The fallback path had to write to the same raw tables as the connector, so that everything downstream is unaware of which path supplied a given day.
- Alerting had to be idempotent under re-runs. The detection query is scheduled and will be re-executed manually while thresholds are tuned, and neither case may re-announce an ad.
- The raw layer had to stay unmodified, so a modelling change can be rebuilt from stored data rather than re-extracted from the API.
- Winner thresholds had to be configurable per account rather than hard-coded, because a floor that makes sense for one account's price point is wrong for another's.
- Alert state had to live in the warehouse, not in a script's memory or a queue, so it survives a redeploy of whatever sends the message.
The pipeline
Drawn as text and shapes rather than shipped as an image, so the step labels stay readable and downloadable.
Redundant ingestion, layered warehouse, single-shot alerting
The connector is the primary path; the dashed route is the Spark fallback, invoked only when the connector fails, writing to the same raw tables. On the right, two tables hold the alert state so each ad reaches Slack once.
- Trigger
- Branch / filter
- Processing step
- Data store or CRM
- Delivered output
- Primary path
- Conditional or exception path
How the work runs
Each stage, what it does, and the specific failure it is built to prevent.
Keep the managed connector as the primary path
The connector handles authentication, paging and schema changes against the Facebook Ads API, and on an ordinary day it is both cheaper and better maintained than anything hand-written. The design question was never whether to replace it. It was what happens on the day it does not run, because that day arrives through quota limits, upstream API errors or connector-side downtime, and it arrives without an error anywhere in the pipeline.
Build a second extraction that lands in the same place
The fallback is a direct API extraction in Python (Business SDK authentication, concurrent creative lookups, Parquet staging and a load into BigQuery) that normalises its output to the raw table schema the connector writes. Identical landing shape is the whole requirement. If the fallback produced a slightly different schema, every transformation downstream would need to know which path supplied each day, and that conditional is what eventually breaks.
Nothing downstream branches on provenance, so a fallback day behaves exactly like a connector day for every model, report and alert.
Land everything in an unmodified raw layer
Raw tables hold ingestion output per entity (campaigns, ad sets, ads and daily metrics) exactly as it arrived. The reason is recovery: ad APIs rate-limit, and their historical windows move, so re-extracting six months because a metric was modelled wrongly is not reliably possible. With raw data retained, a modelling correction is a rebuild rather than a re-extraction.
Separates an ingestion problem from a modelling problem, which otherwise look identical from the dashboard.
Model once, in SQL, into one fact table
The transformation layer deduplicates, normalises schemas across the two ingestion paths, joins campaign, ad set and ad levels, and computes the derived metrics (click-through rate, cost per click, cost per acquisition, return on spend, conversion rate) a single time. Reports and alerts then both read that one modelled table, which is what keeps a Slack message and a dashboard from disagreeing about the same ad.
One definition per metric means an alert cannot cite a number the reporting layer contradicts.
Aggregate to creative grain and compute lifetime metrics cumulatively
The winner view reports at ad-name grain rather than ad id, because the same creative is relaunched under new ids and the team reasons about creatives. Per-creative attributes are collapsed with aggregate selection, and the permalink and thumbnail are reduced to strictly one row per ad name with a row-number window before being joined. Lifetime spend, revenue and purchases are then accumulated as running sums ordered by date, so the view knows the date an ad first qualified rather than only that it qualifies now.
Deduplicating creative links to one row per ad name stops a fan-out join from multiplying the lifetime totals the thresholds are measured against.
Express the winner criteria as configurable thresholds
Detection is a set of floors and ceilings on the modelled metrics: minimum click-through rate, minimum return on spend, maximum cost per acquisition, minimum spend and minimum conversion count. The documented examples are a 2% click-through rate and a return of 2, but the numbers are meant to be set per account; a floor that is right for one price point is wrong for another, and the minimum spend and conversion thresholds exist specifically to stop a tiny, lucky ad from qualifying.
Minimum spend and minimum conversions keep statistically meaningless winners out of the alert channel.
Hold alert state in two tables and MERGE into the first
A scheduled query MERGEs newly qualifying ads into an unreported-winners table while excluding anything already present in the reported-winners table. Because the state lives in the warehouse rather than in the dispatch job, running the detection query twice has the same effect as running it once, which matters most during threshold tuning, when it will be re-run by hand repeatedly.
Idempotence at the query level means schedule frequency and manual re-runs are free choices rather than sources of duplicate alerts.
Dispatch to Slack, then move the ad between the two tables
The dispatch job reads the unreported table, sends a structured message carrying the ad, its campaign and the metrics that qualified it, inserts the ad into the reported table and deletes it from the unreported one. Alerting with the qualifying metrics attached is deliberate: a message that only names an ad sends the reader back to a dashboard, and an alert that requires a second lookup is an alert that gets deferred.
The move is what closes the loop, an ad in the reported table is permanently excluded from detection, so there is no second announcement.
What changed
| Aspect | Before | After |
|---|---|---|
| Connector failure | Ingestion stopped and the gap surfaced when someone questioned a stale dashboard. | A second independent extraction path loads the same raw tables, so a connector outage is degraded rather than absent. |
| Finding a winning ad | Someone reviewed reports and spotted it, on whatever cadence they happened to review. | A scheduled query evaluates every ad against the stated criteria and the first ad to cross is pushed to Slack. |
| Repeat notifications | Re-running any detection logic meant re-announcing the same ads. | Reported ads are held in their own table and excluded from detection, so each ad alerts once. |
| Metric definitions | CTR, CPA and ROAS were recomputed in each report or spreadsheet. | Computed once in the modelled layer, so reports and alerts read the same numbers. |
| Raw data | Whatever the connector last wrote, reshaped in place. | Kept unmodified in a raw layer, so a modelling change can be rebuilt without re-extracting from the API. |
What it runs on
Every tool listed with the job it actually does in this build.
Integration
Facebook Ads API
The source both ingestion paths read, campaign, ad set and ad entities plus daily performance metrics.
SyncWith
Primary managed ingestion into the raw BigQuery tables. Handles authentication, paging and schema changes on a normal day.
Platform
Apache Spark / Python extraction
Fallback ingestion invoked when the connector fails. Extracts directly from the API and normalises to the same raw schema.
Data
BigQuery raw layer
Unmodified ingestion output per entity, retained so a modelling correction can be rebuilt without re-extracting from the API.
BigQuery transformation SQL
Deduplication, schema normalisation, entity joins and the single computation of CTR, CPC, CPA, ROAS and conversion rate.
Winner view
Creative-grain aggregation with cumulative lifetime spend, revenue and purchases, so the first qualifying date is knowable.
Orchestration
Scheduled BigQuery queries
Run detection on a cadence and MERGE new winners into the unreported table, excluding anything already reported.
Cloud Function dispatch job
Reads unreported winners, sends the Slack message, then moves the ad into the reported table.
Delivery
Slack
Delivery surface for the alert, carrying the ad, campaign and the metrics that qualified it so no second lookup is needed.
What stopped being manual
Snippets are illustrative and generic. No client data appears in any of them.
Cumulative lifetime metrics per creative
Replaces reading a performance report and mentally adding up an ad's history across relaunches. Running sums ordered by date mean the warehouse knows the date an ad first qualified, not only that it qualifies today.
sql
-- Illustrative. Ad-name grain, because the same creative
-- returns under new ad ids.
SELECT
Ad_Name,
Date,
SUM(spend) OVER (
PARTITION BY Ad_Name ORDER BY Date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS lifetime_spend,
SUM(revenue) OVER (
PARTITION BY Ad_Name ORDER BY Date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS lifetime_revenue,
SUM(purchases) OVER (
PARTITION BY Ad_Name ORDER BY Date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS lifetime_conversions
FROM `project.dataset.ads_daily`;Idempotent winner detection
Replaces a person reviewing reports to spot ads that have started working, and removes the duplicate-alert failure that a plain SELECT-and-send introduces. Running this twice has the same effect as running it once.
sql
-- Illustrative. MERGE, not INSERT: re-running is harmless.
MERGE `project.dataset.ads_winner_unreported` AS target
USING (
SELECT Ad_Name, campaign_name, lifetime_spend,
lifetime_roas, lifetime_cpa, ctr
FROM `project.dataset.winner_view`
WHERE ctr >= @min_ctr
AND lifetime_roas >= @min_roas
AND lifetime_cpa <= @max_cpa
AND lifetime_spend >= @min_spend
AND Ad_Name NOT IN (
SELECT Ad_Name FROM `project.dataset.ads_winner_reported`
)
) AS src
ON target.Ad_Name = src.Ad_Name
WHEN NOT MATCHED THEN INSERT ROW;Dispatch, then move the ad between state tables
Replaces the judgement about whether an ad has already been mentioned in the channel. State lives in the warehouse, so it survives a redeploy of the job and is inspectable when someone asks why an ad did or did not alert.
python
# Illustrative. The state transition is the alert guarantee.
for ad in read_unreported():
post_to_slack(
ad=ad["Ad_Name"],
campaign=ad["campaign_name"],
metrics={"roas": ad["lifetime_roas"],
"cpa": ad["lifetime_cpa"],
"spend": ad["lifetime_spend"]},
)
insert_reported(ad) # excluded from all later detection
delete_unreported(ad) # queue drains as it is servedWhat it was worth
The two design decisions in this build both convert a vigilance problem into a structural one. A connector that fails silently is normally handled by someone noticing; here it is handled by a second path that writes to the same tables, so the failure degrades the pipeline's cost profile rather than its output. And an alert that would otherwise repeat itself is handled by state in the warehouse rather than by whoever is reading the channel deciding what is new.
Holding the alert state in two tables rather than in the dispatch job is the part worth transplanting. It survives a redeploy, it is inspectable when someone asks why a particular ad did or did not alert, and it makes the detection query's exclusion an ordinary join. The alternative, remembering what has been sent inside the application, fails the first time the job is restarted mid-run, and fails invisibly.
Cumulative lifetime metrics are what make the alert worth reading. A query over flat totals can only tell you that an ad currently meets the criteria, which is the same statement every day. Running sums ordered by date identify the crossing point, so the message says something that was not true yesterday, which is the only kind of alert that keeps a channel's attention.
An honest statement of what this is. The warehouse layer is real and runs: the transformation SQL, the winner view and the MERGE into the unreported table all execute against a BigQuery dataset. The ingestion fallback ships parameterised rather than pointed at live credentials, the thresholds are documented as examples to be set per account, and the Slack dispatch and Airflow orchestration are specified in the design rather than present in the repository. No hours-saved or revenue figure is claimed, because none was measured.
Independent ingestion routes
A managed connector as primary and a direct API extraction as fallback, both landing in the same raw schema.
Warehouse layering
Unmodified raw ingestion, then SQL modelling, then one daily performance fact table that reporting and alerting share.
Alert guarantee
Enforced by two state tables plus a MERGE that excludes anything already reported, so re-running detection cannot re-announce.
How lifetime metrics are computed
Running sums ordered by date identify the first qualifying date, rather than restating that an ad still qualifies.
Reporting grain
The same creative relaunches under new ids, so aggregation is at creative grain to match how the team reasons.
Winner thresholds
The documentation gives examples (a 2% click-through rate, a return of 2) alongside minimum spend and conversion floors that keep lucky small ads out.
Handed over
- A two-path ingestion design: a managed connector as primary, and a direct API extraction as fallback, both normalising to the same raw BigQuery schema.
- A fallback extraction script with concurrent creative lookups, Parquet staging and a BigQuery load, parameterised for account identifiers and credentials.
- A raw bronze layer holding ingestion output unmodified, per entity, campaigns, ad sets, ads and daily metrics.
- SQL transformations performing deduplication, schema normalisation and entity-level joins, and computing CTR, CPC, CPA, ROAS and conversion rate once.
- A modelled daily performance fact table as the single object that reporting and alerting both read.
- A winner view at creative grain, with per-creative attributes collapsed by aggregate selection and creative links deduplicated to one row per ad name.
- Cumulative lifetime spend, revenue and purchase metrics, so the first date an ad qualified is knowable rather than only its present state.
- Configurable winner criteria over click-through rate, return on spend, cost per acquisition, minimum spend and minimum conversions.
- A MERGE-based scheduled detection query that inserts only ads not already present in the reported table.
- A two-table alert state machine, unreported and reported, with a dispatch job that sends the Slack message and moves the ad between them.
Questions about this build
What teams usually ask when they recognise their own process here.
Because on a normal day the connector is the better option, it maintains authentication, paging and schema handling that you would otherwise own, and it costs less than running and watching your own extraction. The hand-written path earns its keep only on the days the connector does not run. Promoting the fallback to primary would mean carrying the maintenance burden every day to solve a problem that occurs occasionally.
By the absence of the rows it should have produced for the window, which is the honest check for a silent failure: a freshness condition on the raw tables rather than an error signal from the connector, because a connector that stops rarely tells you. The design point is that the check has to exist somewhere explicit. A pipeline whose only failure detector is a person noticing a flat line in a chart has no failure detector.
A flag would work, but two tables make the queue and the history different objects, which is worth more than it looks. The unreported table is a short work queue that drains as it is served, so inspecting it answers 'what is waiting to be sent' directly. The reported table is an append-only record of what has been announced, which is what the detection query excludes against. Separating the queue from the log keeps both small questions easy to answer.
So that running the query twice is the same as running it once. That matters most while thresholds are being tuned, which is exactly when someone re-executes the detection query by hand repeatedly. With a plain INSERT, every re-run would add duplicate rows to the queue and the channel would receive repeats. With a MERGE that matches on the ad, the schedule frequency and any manual re-run become free choices.
Because the same creative is relaunched under new ad ids, and a team that briefs creatives wants to know whether the creative works, not whether one of its ad ids does. Aggregating to ad name and accumulating lifetime metrics across those ids is what makes a crossing point meaningful. It does require deduplicating the creative links to exactly one row per ad name before joining, or the fan-out would multiply the totals the thresholds are measured against.
Three things, stated plainly. The fallback extraction is parameterised with placeholder credentials and account identifiers rather than pointed at live ones. The winner thresholds in the documentation are examples and have to be set per account before the alerts are meaningful. And the Slack dispatch job and the Airflow orchestration are specified in the design rather than present in the repository, the warehouse layer and the alert state machine are the parts that exist and run.
Capabilities this used
Have a process that works like this one did?
Book a strategy call. We will walk the workflow with you and say plainly which parts are worth automating and which are not.