Data Modeling and Schema Design Questions

Direct answer

For a sub-second dashboard, denormalized pre-aggregated tables win on latency because they avoid computing joins and aggregations at query time, at the cost of freshness (the aggregate lags behind the source) and extra write amplification (every source-row change must also update the aggregate).

Structured elaboration

The four axes to reason through:

• Freshness: a normalized OLTP query always reflects the current state exactly; a pre-aggregated table reflects whatever state existed the last time it was refreshed. If the dashboard needs true real-time counts, the aggregate must be updated synchronously or via low-latency streaming, not batch.
• Write amplification: every insert/update to the source table that affects an aggregate now triggers a second write (an increment, a trigger-fired update, or a streaming re-aggregation), roughly doubling the write cost for that logical event.
• Storage cost: pre-aggregating trades a small amount of extra storage (the aggregate table itself, usually orders of magnitude smaller than the source) for large latency wins, so storage is rarely the limiting factor here.
• Maintenance complexity: normalized OLTP tables have one shape to maintain; adding an aggregate means maintaining a second artifact that must be kept consistent with the first, including handling late-arriving or corrected source rows.

Worked example

Concretely: a normalized query computing "total orders and revenue in the last hour" by scanning and joining orders and order_items directly might need to scan a few hundred thousand recent rows and join them, taking hundreds of milliseconds to seconds depending on indexing, which fails a sub-second SLA under load. A pre-aggregated hourly_summary(hour_bucket, order_count, revenue) table, incrementally updated as each order is placed, turns the same dashboard query into a single-row lookup by hour_bucket, typically sub-millisecond. The trade a team accepts here is that the current (in-progress) hour's bucket is only as fresh as the last increment, and a late-arriving refund or correction to an order placed 10 minutes ago must also correctly decrement the right bucket, not just insert a new row.

Trade-offs and pitfalls

• The single biggest pitfall is treating the aggregate as authoritative once it's built: if the increment logic ever double-counts or misses an event (a common bug with retries or out-of-order delivery), the aggregate silently drifts from the true source-of-truth total and nobody notices until the numbers look implausible.
• A second common pitfall is under-provisioning for late corrections: refunds, cancellations, and backdated adjustments must decrement or re-bucket the aggregate correctly, and a naive "increment on insert, never decrement" design will overstate revenue forever.
• The decision isn't binary: many teams keep the normalized tables as ground truth and periodically reconcile the aggregate against a full recompute (nightly, say) specifically to catch any drift the incremental path introduced, accepting that reconciliation cost as the price of a correct sub-second read path.

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.

Grain = the atomic level of detail stored in a fact table.
Three example grains for online retail:

1. Order-line grain: one row per order line (order_id, product_id, quantity, line_total). Use when analyzing product-level sales and fulfillment.
2. Order-level grain: one row per order (order_id, customer_id, order_total). Use for customer lifetime and order-level KPIs.
3. Daily-store-product grain: one row per product per day per store (date, store_id, product_id, units_sold, revenue). Useful for fast time-series reporting.
   Practical consequence of wrong granularity: too-coarse (order-level) loses ability to analyze product-level metrics; too-fine (per-click) inflates storage, slows ETL and queries and complicates aggregates. Choose grain to match reporting needs and retention constraints.

Direct answer

Before removing a column proposed for deletion, build an impact map from metadata, saved-query parsing, and dependency graphs to identify every dashboard, report, and downstream job that reads it, then drive a staged deprecation (not an immediate drop) so every identified consumer has a chance to migrate before the column actually disappears.

Structured elaboration

• Metadata-driven discovery: check the data catalog (if one exists) for documented lineage from this column to downstream tables and reports; even an incomplete catalog is a useful starting list, not a complete one.
• SQL parsing of saved queries: parse the SQL text of every saved dashboard query, scheduled report, and known ETL job for references to the specific table and column name, which catches consumers the catalog missed or never had recorded, especially ad hoc queries saved outside any formal cataloging process.
• Dependency graphs: for pipeline-level consumers (a downstream ETL job that reads this table and feeds yet another table), walk the full transitive dependency chain, not just direct consumers, since a job two hops downstream can still break even if it never directly queries the column itself.
• Stakeholder ownership: once the consumer list is built, map each consumer to an owning team or individual, since the goal of this whole exercise is to reach a human who can confirm "yes, we can migrate off this" or "no, we still need it," not to unilaterally decide from the data alone.

Worked example

A staged deprecation timeline once the impact map is built: (1) announce the planned removal to every identified owner with a concrete deadline; (2) mark the column deprecated in the catalog/schema documentation and, if supported, log a warning whenever a query actually reads it, to catch anyone the static analysis missed; (3) as the deadline approaches, follow up specifically with any consumer whose usage telemetry still shows active reads; (4) only after the deadline passes AND telemetry confirms zero reads for a sustained period, actually drop the column, ideally behind the same expand-contract discipline (rename to a clearly-deprecated name first, drop it fully later) used for any other breaking schema change.

Trade-offs and pitfalls

• Static SQL parsing of saved queries will miss dynamically-constructed queries (a report tool that builds SQL from user-configured filters at runtime) and ad hoc queries run outside any saved-query system entirely; runtime usage telemetry (logging actual column reads as they happen) is the necessary complement to static analysis, not a replacement for it, precisely because static analysis alone cannot see everything.
• The dependency graph needs to go beyond DIRECT consumers: a report built on top of a materialized view that itself reads the column is still broken by dropping the column, even though the report's own query never mentions it, which is why transitive, not just first-hop, dependency tracing matters.
• Rushing the communication step to save time is the most common way this process fails in practice; a technically complete impact map that isn't actually acted on by the owning teams (no deadline, no follow-up, no verification the migration actually happened) just delays the same production incident rather than preventing it.

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.