Models Sql Dbt

Hightouch Model SELECTs from a dbt mart that uses ROW_NUMBER() OVER (...) AS id as the primary key. Each rebuild reassigns ids; Lightning diff sees every row as new.

Primary key must be stable across rebuilds — use a natural key (customer_id, user_uuid) or a deterministic surrogate (md5 of natural-key columns). Never use ROW_NUMBER() or analytical-window-derived ids as the Model primary key. Per docs, an unstable PK breaks idempotency and floods destinations wi…

Operator picks 'all rows' diff strategy on a 50M-row Model because Lightning snapshot setup is unfamiliar. Every sync re-reads and re-pushes all 50M rows.

Choose the diff strategy from docs: Lightning (warehouse-resident row-hash snapshot) for most cases; tracking column (updated_at) when a reliable change marker exists; 'all rows' only for small or full-mirror use cases. Document the trade-off in the Model description and validate against destinatio…

Operator pastes raw SQL into a Hightouch Model that duplicates joins already defined in a dbt mart. Two definitions drift; sync sends data that doesn't match BI dashboards.

Reference the dbt model directly (Hightouch dbt integration per docs) so the single source-of-truth is the dbt mart. Drift between Hightouch SQL and BI SQL is a recurring data-quality bug; centralize transformations in dbt and let Hightouch consume the modeled output.