Data pipeline discipline

Analytics pipelines default to append-only, idempotent, and ClickHouse-shaped. Row-level OLTP mutation habits don't transfer — see [[z-go-clickhouse]] for the engine side.

Batch ingestion

• Key batches and events with stable identifiers. Use atomic conflict handling or the engine's documented insert-deduplication contract. A separate existence check can race another writer. PostgreSQL: use a matching unique constraint with ON CONFLICT.
• ReplacingMergeTree deduplicates during background merges, not immediately. Use the intended FINAL/argMax query semantics for correct logical results. Verify concurrent replay and partial failures; do not promise unchanged physical row counts.
• Backfill one partition or date range at a time, not "truncate and reload everything." A backfill that touches the whole table blocks readers and hides the actual gap you're fixing.

Append-only modeling

• Model events by event_time, not ingestion_time. Late-arriving data — an offline mobile client, a retried webhook — lands hours after the event happened; partition and query on when it occurred, not when it showed up.
• A late row reprocesses the partition it belongs to, not "today's" partition. Getting this wrong is the most common cause of a metric that's quietly wrong for the last N days until a reconciliation catches it.
• Engine and table-design specifics (MergeTree family, FINAL/argMax reads): [[z-go-clickhouse]].

Incremental vs full-refresh

Full-refresh is the correct default until incremental earns its complexity: