Only run Census syncs when your dbt models build cleanly

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.