Problem
dbt-clickhouse incremental inserts map the model SELECT to table columns positionally, and union_by_tag unions members with SELECT *. Physical column order must equal model order. Three families diverged:
- ai — label columns patched onto pre-existing tables via tail-appending ALTERs while the models declare them mid-SELECT. Syncs fail:
CANNOT_PARSE_TEXT on staging inserts, NO_COMMON_TYPE on the class union.
- collab chat —
direct_and_group_messages added mid-SELECT with append_new_columns, no rebuild. Same drift latent on pre-existing member tables; the class table (default on_schema_change='ignore') never receives the column at all.
- crm — salesforce members project
custom_fields, hubspot members do not. Class union fails with NUMBER_OF_COLUMNS_DOESNT_MATCH whenever both connectors are configured.
Fix
- ai labels leave silver.
tool_label / surface_label are constants derived from tool / surface; the gold view now derives them via macros/ai_labels.sql (same pattern as git_source_label). Deletes the label backfill migration, both staging repair functions, and the mutations_sync=2 mutations they re-ran on every deploy.
- Data columns get position pins. Idempotent
ADD/MODIFY COLUMN ... AFTER heals in the migrate hook + 20260716000000_class_contract_heal.sql: conversation_count (ai), direct_and_group_messages (collab chat), custom_fields (crm). Guarded for absent tables and placeholders (the minimal gold-view placeholders lack the AFTER anchors).
- crm parity. hubspot models emit a structural
'{}' custom_fields at the salesforce position, in every positional branch.
- Bounded gold builds.
query_settings (memory, threads, disk spill) on both table-materialized observation models — deploy-time rebuilds degrade to disk spill instead of failing the release.
Every damaged instance converges on its next deploy + sync. No manual intervention, no knowledge of per-instance damage state needed: DROP preserves relative order, ADD/MODIFY AFTER are metadata-only no-ops once converged.
Problem
dbt-clickhouse incremental inserts map the model SELECT to table columns positionally, and
union_by_tagunions members withSELECT *. Physical column order must equal model order. Three families diverged:CANNOT_PARSE_TEXTon staging inserts,NO_COMMON_TYPEon the class union.direct_and_group_messagesadded mid-SELECT withappend_new_columns, no rebuild. Same drift latent on pre-existing member tables; the class table (defaulton_schema_change='ignore') never receives the column at all.custom_fields, hubspot members do not. Class union fails withNUMBER_OF_COLUMNS_DOESNT_MATCHwhenever both connectors are configured.Fix
tool_label/surface_labelare constants derived fromtool/surface; the gold view now derives them viamacros/ai_labels.sql(same pattern asgit_source_label). Deletes the label backfill migration, both staging repair functions, and themutations_sync=2mutations they re-ran on every deploy.ADD/MODIFY COLUMN ... AFTERheals in the migrate hook +20260716000000_class_contract_heal.sql:conversation_count(ai),direct_and_group_messages(collab chat),custom_fields(crm). Guarded for absent tables and placeholders (the minimal gold-view placeholders lack the AFTER anchors).'{}'custom_fieldsat the salesforce position, in every positional branch.query_settings(memory, threads, disk spill) on both table-materialized observation models — deploy-time rebuilds degrade to disk spill instead of failing the release.Every damaged instance converges on its next deploy + sync. No manual intervention, no knowledge of per-instance damage state needed: DROP preserves relative order, ADD/MODIFY AFTER are metadata-only no-ops once converged.