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…

Model returns customer_revenue as DECIMAL(18,4). Destination Salesforce field is a Currency(16,2). Hightouch truncates the decimal silently.

Match warehouse column types to destination field types at Model definition time; cast in SQL to the precision the destination accepts (ROUND, CAST). Surface destination type-mismatch warnings in the run history. Document the precision contract in the Model description.