Gate Census syncs on dbt Cloud build success

PromptCreate

When a dbt Cloud job run finishes, I want to decide whether it is safe to push freshly transformed warehouse data into my go-to-market tools, and only then activate the matching Census syncs. Reverse ETL will cheerfully sync a stale or half-built table into my CRM and report success, so sales sees wrong lead scores and nobody notices. This workflow is the guard against that.

Trigger this on the dbt Cloud webhook event job.run.completed, not job.run.errored. dbt Labs documents that job.run.completed is triggered when artifacts and the job metadata are ingested and available, whereas job.run.errored is fired immediately upon encountering an error, at which point the artifacts and metadata of the job might not have been ingested and cannot be accessed. Since this workflow reads the run_results.json artifact, it must subscribe to job.run.completed and then branch on the run status itself. Subscribe to only one of the two events, because subscribing to both produces duplicate messages when a run fails.

Take the run id off the webhook payload and call dbt Cloud Retrieve Run to get the authoritative outcome. dbt Cloud run status codes are 1 Queued, 2 Starting, 3 Running, 10 Success, 20 Error, 30 Cancelled. Treat 10 as a successful build, and treat 20 or 30 as a failed build.

Then call dbt Cloud Retrieve Run Artifact for run_results.json on that same run. Every dbt Cloud response wraps its payload, so read the resource from data. Walk the results and build three lists: models that built successfully, models that failed with an error, and models that were skipped because an upstream dependency failed. Skipped counts as not built. Note failing tests as well, because a model that compiled but whose tests failed is not data I want pushed into a CRM.

If the run errored or was cancelled, or if any model feeding a revenue-critical table failed or was skipped, hold activation entirely and do not trigger a single Census sync. Instead post a Slack Bot Send a Message alert to my data ops channel that names which Census syncs were blocked, says which models failed and why, and states plainly that the CRM is intentionally holding the last known good data rather than receiving a partial refresh. Treat models feeding lead scoring, account health, pipeline, and any other CRM-facing revenue table as revenue critical.

If the run succeeded, work out which syncs are actually safe to run. Call Census List Datasets and Census List Syncs. Census datasets are SQL-defined and syncs reference source objects, so mapping a dbt model name to a sync is not a straight key lookup. Read each dataset's SQL and each sync's source_attributes and reason about which sync ultimately reads which rebuilt table, following the chain where a sync reads a dataset that reads a model. If a match is genuinely ambiguous, treat it as unsafe, leave it out, and mention it in the summary rather than guessing.

For each sync whose upstream model built successfully in this run, call Census Trigger Sync Run. A trigger does not equal success: Census returns 200 when the run is queued, not when it succeeds. Capture the returned sync_run_id and poll Census Fetch Sync Run until each run settles into a terminal state. Run-level failures come back as status failed with error_code, error_message and error_detail inside the sync run object, not as an HTTP error, so never infer success from the trigger response.

Census identifiers, including sync_id, sync_run_id and dataset_id, are integers rather than UUIDs, and every Census response is wrapped in an envelope, so read from data. Do not re-trigger a sync that is already mid-run: deduplicate trigger calls rather than hitting trigger again for the same sync. If Census returns 429, respect the Retry-After header and back off before retrying.

Once every triggered run has settled, post one single Slack Bot Send a Message summary, not one message per sync. List each sync by name, its destination, how many records were synced, and any rejected or invalid records along with the reason Census gave. Call out any sync that was deliberately skipped because its upstream model did not build, so the skip is visible rather than silent. If some syncs succeeded and others failed at run time, say both in the same message.

The hard rule for this workflow: never trigger a sync whose upstream model did not build successfully in this run. Holding stale but correct data in the CRM is always better than pushing a half-built table that everyone downstream will trust.

What does this prompt do?

Starts the moment your dbt Cloud job finishes and reads exactly which models built cleanly, which failed, and which were skipped.
If the build broke, nothing gets synced. We post a Slack alert naming the syncs we held back and confirming your CRM is still showing the last good data.
If the build succeeded, we match the rebuilt tables to the Census syncs that read them and start only those syncs.
We wait for each sync to genuinely finish, then post one Slack summary with each sync, where it sent data, how many records landed, and anything that got rejected.

What do I need to use this?

A dbt Cloud account with a production job that builds your models
A Census workspace (now branded Fivetran Activations) with your syncs already set up
A Slack workspace where the bot can post into your data or revenue ops channel
A short list of the tables you treat as revenue critical, such as lead scores, account health, or pipeline

How can I customize it?

Choose which models count as revenue critical, so a failure there holds every sync rather than just its own
Pick the Slack channel, and whether to post the all-clear summary every time or only the hold alerts
Decide whether a skipped model blocks its sync or is only flagged, with blocking as the safer default

FAQs

What happens if only some of my models fail?

We sync only the tables that built cleanly. Any sync that reads a failed or skipped table is held back and named in the Slack message. If one of your revenue critical tables failed, we hold everything rather than push a partial refresh.

Why not just let Census run on its own schedule?

A scheduled sync has no idea whether the table behind it is fresh. It will happily push yesterday's numbers, or a table that only half rebuilt, and still report success. This checks the build first and decides.

Will my CRM be left empty if the build fails?

No. Nothing is overwritten and nothing is deleted. Your CRM keeps the last good data it received, which is the safest place to be while the data team fixes the build.

Does this work with Salesforce, HubSpot, or Marketo?

Yes. It works with whatever destinations your Census syncs already point at, and the summary names each destination so you can see exactly where data landed.

How does it know which sync depends on which table?

It reads how each Census dataset and sync is defined and matches those to the models that just rebuilt. Anything genuinely ambiguous is left out and called out in the summary, so a guess never turns into a bad sync.

Related templates

Review desk for portal forms your team still fills in by hand

Stage a batch of filings overnight, then approve each completed form from a screenshot before anything is ever submitted.

App

Client-by-client cold email pipeline review for agencies

Pick a client and a date range to see sent, replies, meetings booked and the real deal value your cold email produced, campaign by campaign.

App

Audit what Intercom's Fin AI actually resolved before you pay

Review every conversation Fin closed as resolved, judge which ones actually stuck, and see what the gap is worth against your bill.

App

One triage console for every Jira service desk queue

Merge your IT, HR and Facilities queues into one list ranked by SLA time left, then reply, change status and escalate without ever opening Jira.

App

Run your Webflow site translations from one review desk

See every page's translation status at a glance, edit translations side by side with the English, and publish only what your team has actually approved.