Data Modeling and Schema Design Questions

Hybrid architecture:

• Ingest: use a streaming layer (Kafka) with lightweight producers.
• Low-latency store: materialize near-real-time aggregates in a fast OLTP/NoSQL store (Redis, RocksDB-backed service, or ClickHouse with TTL) for second-level metrics and drilldowns.
• Analytical store: periodically batch/stream into a columnar warehouse (Snowflake, BigQuery, ClickHouse) for complex ad-hoc queries.
  Schema choices:
• For real-time layer: schema-normalized key-value with pre-aggregated counters and event windows; support fine-grained keys for drilldowns.
• For warehouse: denormalized wide tables or fact+dimension model optimized for columnar scans.
  Syncing: use CDC/streaming transforms (Kafka Streams or Flink) to update real-time store and write compressed events to warehouse. Maintain materialized views and rollups in both stores.
  Query routing: route interactive dashboard queries to real-time store for recent windows (seconds) and to warehouse for deep historical analysis with fallbacks.
  Trade-offs: real-time store optimized for latency and limited ad-hoc flexibility; warehouse for flexible analytics. Use consistent aggregation semantics and eventual convergence strategies.

Requirements: allow per-domain (e.g., payments) point-in-time recovery (PITR) with minimal restore time and minimal blast radius. Approach: combine schema partitioning, physical separation, WAL/transaction-log retention per DB or per tablespace, and logical change capture.
High-level pattern:

• Isolate domains into separate schemas/databases or separate tablespaces/physical volumes so backups/restores can target only payment storage.
• Use append-only history tables for critical domains (immutable rows) with effective_from timestamps and soft-delete flags so point-in-time state is reconstructable by time filters rather than in-place updates.
• Enable continuous WAL shipping and base backups per database; for finer granularity use logical replication (pglogical) streaming of payment tables to a standby replica that can be restored to arbitrary WAL position.
  DB features to use:
• Tablespaces / separate physical disks or separate DB instances per domain to reduce restore scope.
• PITR via base backup + WAL for whole DB; for per-domain fast restore, maintain a dedicated replica for that domain and use logical replication to replicate changes to a secondary DB you can rewind/truncate to a timestamp quickly.
• Use change-data-capture (CDC) into an append-only store (object storage or dedicated DB) with event timestamps. To restore payments to t, reapply events up to t into a new payments table/DB—fast because only payments events are processed.
  Schema patterns:
• Event sourcing / append-only tables: each change is a new row with event_time, event_type, and payload. Reconstruct state by replay or materialized views up to desired time.
• Time-range partitioning by event_date for history tables so restoring a segment is file-level (partition) restore—restore only partitions touching the time window.
• Soft-delete and effective_from/effective_to columns for current-state tables and history tables for quick snapshot queries.
  Other considerations:
• Maintain compact incremental backups for payment partitions; use snapshot isolation and consistent backup points.
• Provide automated tooling: a restore orchestrator that can restore partitions or replay CDC to target timestamp and verify constraints.
  Trade-offs: separate DBs increase operational complexity and cross-domain transactions become distributed (use two-phase commit or compensate). Append-only and CDC increase storage but greatly reduce restore time and risk.

Direct answer

Store customer addresses as a small, append-only history table (one row per address version, with an effective date range) rather than a single mutable row, so "current address" and "address at time T" are both simple, indexed queries against the same table, and deletion/retention can target specific historical rows without losing the ability to reconstruct any point in time still within the retention window.

Structured elaboration

CREATE TABLE customer_addresses (
  address_history_id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  line1 TEXT NOT NULL,
  city TEXT NOT NULL,
  postal_code TEXT NOT NULL,
  valid_from TIMESTAMP NOT NULL,
  valid_to TIMESTAMP     -- NULL means this is the current address
);
CREATE INDEX idx_addr_customer_range ON customer_addresses (customer_id, valid_from, valid_to);

• Current-address lookup: SELECT * FROM customer_addresses WHERE customer_id = ? AND valid_to IS NULL.
• Historical audit of changes: SELECT * FROM customer_addresses WHERE customer_id = ? ORDER BY valid_from, which shows every address the customer has ever had, in order.
• "Address at time T": SELECT * FROM customer_addresses WHERE customer_id = ? AND valid_from <= ? AND (valid_to IS NULL OR valid_to > ?), the same point-in-time range pattern used for any effective-dated attribute.
• Efficient deletion/retention: because each address version is its own row with its own valid_from/valid_to, a retention policy ("delete address history older than 7 years") can target and delete specific old rows directly (DELETE WHERE valid_to < now() - interval '7 years') without disturbing the current or more recent rows, which a single mutable-column design with no history at all could never support in the first place, since it never had the history to selectively retain or discard.

Worked example

When a customer updates their address, the write is two steps in one transaction: UPDATE customer_addresses SET valid_to = now() WHERE customer_id = ? AND valid_to IS NULL (close out the previous current row), then INSERT the new row with valid_from = now() and valid_to = NULL. This keeps exactly one "current" row per customer at all times (enforceable with a partial unique index, CREATE UNIQUE INDEX ON customer_addresses (customer_id) WHERE valid_to IS NULL), while preserving every prior address indefinitely (or until the retention policy removes it).

Trade-offs and pitfalls

• The storage and query cost of this design is proportional to how often a customer's address actually changes, which for most customers is rare; the cost is well justified given the alternative (a mutable single row) cannot answer "address at time T" or support any auditable history at all, both of which are commonly real requirements (fraud investigation, compliance, shipping-dispute resolution).
• The two-step "close out the old row, insert the new one" write must happen in a single transaction; if a crash or bug leaves a customer with two "current" rows (valid_to IS NULL on both) or zero, downstream queries assuming exactly one current row will misbehave, which is exactly why the partial unique index enforcing "at most one current row per customer" is worth adding as a schema-level safety net, not just relying on application-level discipline.
• Retention and deletion interact with the historical-audit requirement in tension: deleting old rows to satisfy a retention policy permanently forecloses any future "address at time T" query into the deleted range, so the retention window itself needs to be a deliberate compliance/business decision, not an arbitrary storage-saving default.

Definitions:

• 1NF: atomic values, single-valued cells.
• 2NF: 1NF + no partial dependency on a composite PK (each non-key attribute depends on whole PK).
• 3NF: 2NF + no transitive dependencies (non-key attributes depend only on PK).
  Example violating 2NF:
  Table: order_product(order_id, product_id, product_name, product_price, quantity)
  Sample row: (1001, 200, "Widget", 9.99, 2)
  Here product_name/price depend only on product_id (part of composite PK), causing partial dependency.
  Transform to 2NF:
• order_items(order_id, product_id, quantity) -- PK (order_id, product_id)
• products(product_id, product_name, product_price) -- PK product_id
  Now product attributes live in products and order_items stores only quantity. This removes partial dependencies and redundancy.

Direct answer

Backward and forward compatibility for an evolving API or message schema both rest on the same principle: only add optional things, never repurpose or remove what already exists, so an old reader can ignore fields it doesn't recognize and a new reader can supply sensible defaults for fields an old writer never sent.

Structured elaboration

• In a relational database: add new columns as nullable (or with a default), never repurpose an existing column's meaning, and treat removing a column as a multi-step deprecation (stop writing to it, confirm nothing reads it, then drop it), the same expand-contract discipline used for any schema migration.
• In a message schema (Avro/Protobuf): both formats have explicit compatibility rules for exactly this scenario. Protobuf: every field has a unique numbered tag; adding a new field with a new tag number is safe for both old and new readers (old readers simply ignore the unrecognized field, new readers see a default if an old writer never sent it); renaming a field is safe (only the tag number matters on the wire, not the name); but reusing a tag number for a different field, or changing a field's type incompatibly, breaks compatibility. Avro: relies on both the writer's and reader's schema being available at deserialization time, with defined rules for how a reader schema resolves fields present in the writer schema but not the reader's (and vice versa), which is why Avro deployments typically pair with a schema registry to track compatibility across versions centrally.

Worked example

A concrete safe evolution: an Order message adds a new optional discount_code field with a fresh tag number. Old consumers, compiled against the schema before the change, simply never see discount_code and continue working unaffected (forward compatibility: old code tolerates new data). New consumers reading a message produced by an old producer that never set discount_code see it as unset/default (backward compatibility: new code tolerates old data). Contrast with an UNSAFE change: renaming a field's tag number, or changing discount_code from a string to a nested message, both of which would misinterpret bytes on the wire for whichever side didn't get the corresponding update at the same time.

Trade-offs and pitfalls

• The single most common way this compatibility discipline breaks in practice is repurposing a field for a new meaning without changing its identifier (its tag number, or its column name); this passes casually because the schema still "type-checks," but it silently misinterprets historical data or in-flight messages produced under the old meaning.
• A schema registry (for Avro/Protobuf in a streaming system) or an automated compatibility-check step in CI (for a relational migration) is what turns "we're supposed to follow these rules" into "a violation is caught before it ships," which matters because a compatibility violation is often invisible in a single service's own tests and only surfaces when an old and new version run side by side in production.
• Backward and forward compatibility are genuinely two different guarantees and it's easy to satisfy one while breaking the other; a new required (non-optional) field satisfies "old readers tolerate new data" (nothing about old readers changed) but breaks backward compatibility, since new readers now expect the field on every message, and an old writer's message, produced with no knowledge of the new requirement, would be missing it and rejected as invalid by the new reader.