We use Fivetran to pull data from Salesforce and Zendesk into BigQuery. Every time their API updates or a user adds a custom field, our downstream dbt models break. How are you automating 'Schema Drift' detection so that the pipeline doesn't crash every time a source changes?
3 answers
Schema drift is the silent killer of automated pipelines. The best way to handle this isn't to prevent the drift, but to build a "defensive" landing zone. We use a "Raw-to-Staging" pattern where our Fivetran syncs go into a dataset that allows any schema change. We then use dbt "source freshness" checks and generic tests like accepted_values to monitor for changes. If a new column appears, it doesn't break the load—it just stays in the raw table. We then use an automated Slack alert triggered by a Google Cloud Function that scans the Information Schema for new columns. This gives us a heads-up to update the models before the business users notice.
Are you using "Select *" in your staging models? That’s usually the primary reason schema drift causes a hard failure rather than a silent addition.
You could also look into "Schema Registry" tools if you are using Kafka. They enforce a contract between the producer and consumer so that breaking changes are caught before they hit the warehouse.
Schema registries are great for internal apps, but for SaaS like Salesforce, the "Defensive Staging" approach Cynthia mentioned is usually the only practical solution.
Ryan, you hit the nail on the head. To answer your point, we banned SELECT * in our dbt style guide. We now explicitly name every column. When Fivetran adds a new field, the staging model simply ignores it until we manually add it to the YAML file. This prevents "downstream pollution" where unexpected data types could break a complex join or a window function. It’s more manual work upfront, but it has reduced our emergency "pipeline fix" tickets by at least 70% over the last two quarters.