Trade-offs: Append Only vs Idempotent Upserts

The Fundamental Trade-off: Every data pipeline makes a choice between write simplicity and read correctness. You can make writes fast and dumb, or you can make them smart and safe. Both have a cost. Append Only (Fast Writes, Complex Reads): With pure append, every write is an INSERT. No key lookups, no conflict checks, no coordination. At 100,000 events per second, you can sustain sub 10ms p99 latency to a distributed log like Kafka. This is why raw ingestion is almost always append only. The cost appears downstream. Consumers must deduplicate and resolve conflicts. Queries need GROUP BY with DISTINCT or complex window functions to pick the latest version. Storage grows linearly with retries: 5% duplicate rate means 5% wasted storage. For petabyte scale systems, that's expensive. This pattern works well for immutable event logs where readers are sophisticated data engineers. It fails for user facing analytics where business analysts run ad hoc SQL. They shouldn't need to know about deduplication logic. Idempotent Upsert (Safe Writes, Simple Reads): With upserts, each write checks for an existing key and either inserts or updates. At 100,000 operations per second, this requires careful indexing and sharding. Write latency increases to 20 to 50ms p99 because of the lookup cost. Hot keys can create bottlenecks: if all events hash to the same partition, throughput drops significantly. The benefit is clean, simple downstream consumption. Queries just SELECT without worrying about duplicates. Storage is efficient because there's one row per business entity. This is essential for customer facing tables, financial reporting, and ML feature stores where correctness is non negotiable. When to Choose What: Raw logs and event streams: append only. Maximizes ingestion throughput, accepts duplicates. Examples: click streams, application logs, IoT sensor data at millions of events per second. Derived aggregates and dimensions: idempotent upserts. Enables safe recomputation and backfills. Examples: user profiles, daily revenue by merchant, ML features, any table used by BI or operational dashboards. Financial transactions: idempotent with extra care. Use database transactions or distributed sagas to ensure upserts are atomic with related state changes. Cannot tolerate even temporary duplicates.