Database Selection and Trade-offs Questions

Relational (RDBMS) — Best for transactional BI, consistent joins, ACID-backed reporting and SQL analytics across normalized schemas; example: PostgreSQL.

Key‑Value — Fits high-throughput, low-latency lookups and simple aggregated counters for realtime KPI serving (cache/session stores), but poor for ad-hoc multi‑dimensional queries; example: Redis.

Document — Good for semi-structured event or product data where flexible schemas and nested fields simplify ingestion and moderate analytical queries via JSON indexing; example: MongoDB.

Wide‑column — Suited to large-scale, sparse datasets and time-windowed aggregated metrics (denormalized event stores / pre-aggregations) with fast row-level reads; example: Apache Cassandra.

Columnar / OLAP stores — Designed for analytical BI: fast large-scale scans, aggregations and complex OLAP queries over many rows and few columns; example: Amazon Redshift (or ClickHouse).

Time‑series DBs — Optimized for high-ingest, append-only time series, windowed aggregations, retention policies and anomaly detection for metrics and telemetry; example: InfluxDB.

Search engines — Good for full-text, faceted search, fast ad-hoc filtered aggregations and exploratory dashboards on text-heavy data, but not for complex joins; example: Elasticsearch.

Requirements & constraints:

• Repair aggregates for last 90 days without disrupting live reports.
• Must be idempotent, auditable, and validated before switching consumers.

Plan (high level):

1. Isolation & safe workspace

• Create a timestamped shadow dataset/warehouse/schema (e.g., analytics_backfill_v20251122).
• Backfill writes only to shadow tables and versioned aggregate tables (no overwrite of prod tables).

2. Compute & run backfill (safe execution)

• Recompute aggregates from raw source (or corrected ETL) for the 90-day window in small chunks (daily or hourly).
• Use orchestration (Airflow/Prefect) with retries, concurrency limits, and monitoring.
• Use BEGIN/COMMIT transactions where supported; otherwise write to temp partition then atomically swap.

3. Idempotency & write semantics

• Design upserts keyed by deterministic keys (date + dimension keys). Use INSERT ... ON CONFLICT/ MERGE that is idempotent: same input => same row.
• Write to versioned partitions (partition_date + backfill_run_id). If job restarts, it replaces that run_id partition only.
• Maintain a backfill_runs table (run_id, params, status, started_at, finished_at, checksum, rows_written).

4. Computing diffs & reconciliation

• For each partition/day compute:
  • Row-level diffs: join prod_aggregate vs shadow_aggregate on keys to get delta_count, delta_sum, delta_metric.
  • Aggregated diffs: totals, means, pct change, and row-count mismatches.
  • Checksums: deterministic hash over key+values to detect any change quickly.
• Store diffs and expose to stakeholders via a validation dashboard. Flag any large unexpected deltas.

5. Validation checks (automated + manual)

• Automated DQ:
  • Row count parity within tolerance (or explicit zero tolerance for critical metrics).
  • Sum/mean reconcilable with source sample queries.
  • Referential integrity (dimension FK existence).
  • Idempotency check: re-run same run_id and ensure no additional change.
  • Statistical checks: z-score or pct-change thresholds to detect anomalies.
• Manual review:
  • Sample row-level verification against source for several dates and keys.
  • Business owner sign-off for critical KPIs.

6. Safe cutover (flip) strategy

• Staged roll-out:
  • Expose corrected aggregates as a shadow alias (e.g., metrics.current_v2) and run reports in parallel for a week to compare.
  • Run A/B reporting: a copy of dashboards pointing to corrected metrics; compare totals and business narrative.
  • If all checks pass and stakeholders approve, atomically swap production pointer (view or alias) to point to corrected version during low traffic window.
• Keep previous version as rollback (do not drop old data immediately). Record exact swap metadata in backfill_runs.

7. Monitoring & rollback

• After flip monitor KPIs and alert on anomalous behavior beyond thresholds.
• Rollback by switching alias back to previous version; log reason and communicate.

8. Post-mortem & prevention

• Document root cause, update ETL tests (unit tests, end-to-end checks), add data quality gates and early alerts to catch future regressions.

Why this works:

• Isolation prevents impact to ongoing reporting.
• Chunking + idempotent upserts allow safe retries.
• Diffs + checksums provide transparent, auditable reconciliation.
• Staged flip with business sign-off minimizes risk and preserves rollback path.

Requirements & constraints:

• 20 TB of daily delta (very large)
• Dashboards must reflect data within 15 minutes (near-real-time)
• BI role: reliable, queryable summarized tables for Tableau/Looker

Brief comparison of strategies:

• Full-refresh: simplest but infeasible at 20 TB/day and 15-min SLA (too slow, costly, heavy IO).
• Watermark-based incremental extracts: good when source exposes reliable timestamp columns; simpler ETL (pull rows where updated_at > watermark). Works at scale but still requires scanning partitions/indices and careful handling of late-arriving or back-dated changes.
• CDC (Change Data Capture): captures row-level inserts/updates/deletes from source logs (transactional log or message stream). Small footprint, low latency, ideal for near-real-time and high-volume deltas.

Proposed architecture (justification: CDC-first hybrid):

1. Source -> CDC stream
   • Use native CDC connector (Debezium/GoldenGate/Cloud Dataflow) to capture changes into a streaming platform (Kafka / Kinesis / PubSub). CDC handles 20 TB/day as compact change events rather than full records.
2. Streaming ingestion & transformation
   • Stream processor (Flink/ksql/dbt-core streaming or Spark Structured Streaming) to:
     • Normalize schema, apply deduplication, handle out-of-order events (event-time watermarks), and produce upsert events.
     • Produce two outputs: raw change lake (append) and materialized upsert tables.
3. Low-latency OLAP sink
   • Upsert-capable analytical store (Delta Lake / Snowflake / BigQuery with streaming insert / ClickHouse) holding denormalized, aggregated tables (facts and pre-aggregates) refreshed continuously.
   • Maintain micro-batch windows (1–5 minutes) to keep within 15-min SLA and reduce small-write overhead.
4. Semantic layer & BI
   • Expose curated semantic tables/views to Tableau/Looker; use materialized views or query acceleration to ensure sub-second dashboard interactions.
5. Orchestration & reliability
   • Use Airflow or similar for jobs, monitor lag, schema drift, late-arriving data, and alerting. Backfill path via batch reprocessing if CDC gaps detected.

Key reasons:

• CDC minimizes data moved (change events << full rows), meeting 15-min freshness at scale.
• Streaming processors manage ordering/late arrivals using event-time watermarks and stateful dedupe.
• Upsert-capable OLAP store gives BI-friendly denormalized tables with fast query performance.
• Micro-batching balances cost and latency; full-refresh reserved for periodic reconciliation/backfills.

Risks & mitigations:

• Schema changes: use schema registry and evolve consumers.
• Exactly-once/upsert correctness: use transactional sinks or idempotent keys.
• Operational complexity: start with critical dashboards on CDC pipeline, keep batch watermark jobs for non-critical datasets.

This hybrid CDC + micro-batch/materialized-views architecture gives near-real-time freshness, cost-efficient data movement for 20 TB/day deltas, and stable, fast BI consumption.

Approach: load incremental changes into a small staging table, then apply SCD Type 2 using targeted, indexed operations and batching to avoid full-table scans/long locks. Key techniques: partitioning by customer_id or date, an index on (customer_id, is_current), small transactions (micro-batches), and using MERGE only against the staging set.

Example (ANSI-ish SQL — adapt syntax for your RDBMS):

-- staging table contains incoming rows: customer_id, name, email, change_ts
CREATE TEMP TABLE stg_customers AS SELECT * FROM incoming_changes; -- small, indexed on customer_id

-- 1) MERGE: expire current rows when attributes changed, insert new history rows
MERGE INTO customers tgt
USING (
SELECT s.customer_id, s.name, s.email, s.change_ts
FROM stg_customers s
) src
ON (tgt.customer_id = src.customer_id AND tgt.is_current = 1)
WHEN MATCHED AND (tgt.name <> src.name OR tgt.email <> src.email) THEN
UPDATE SET valid_to = src.change_ts, is_current = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (customer_id, name, email, valid_from, valid_to, is_current)
VALUES (src.customer_id, src.name, src.email, src.change_ts, '9999-12-31', 1);

-- 2) Insert new current rows for changes (ensure duplicates avoided)
INSERT INTO customers (customer_id, name, email, valid_from, valid_to, is_current)
SELECT s.customer_id, s.name, s.email, s.change_ts, '9999-12-31', 1
FROM stg_customers s
LEFT JOIN customers c ON c.customer_id = s.customer_id AND c.is_current = 1
WHERE c.customer_id IS NULL
OR (c.name <> s.name OR c.email <> s.email);

Performance & locking strategies:

• Work with a small staging set (only changed rows). Do NOT MERGE against full 100M table.
• Ensure an index on customers(customer_id, is_current) so MERGE/updates hit few pages.
• Use partitioning (by customer_id hash or date) so updates target single partition.
• Run in micro-batches (e.g., 10k–100k staging rows) inside separate transactions to limit lock duration and log growth.
• Use snapshot/READ COMMITTED SNAPSHOT isolation if available to reduce blocking.
• Prefer set-based MERGE for matched expire/update and a separate INSERT for new current rows to avoid long-held locks from large MERGE transactions.
• Monitor and tune: statistics, fillfactor, and avoid page splits; use async replication or change-data-capture for upstream ingestion.

Edge cases: concurrent updates for same customer — serialize by customer_id (e.g., application lock) or include change_ts / source ordering to deterministically order history.

Requirements & constraints:

• Support individual "right to be forgotten" (RtBF) requests across raw events (data lake), aggregated metrics (warehouse), and cold archives while preserving analytic integrity, auditability, and scale.

High-level strategy:

1. Identity layer & mapping

• Central PID (personal-id) service maps business identifiers (email, user_id) → internal GUID. Store GUIDs and PII in an encrypted identity store with strict ACLs and KMS keys. BI uses GUIDs for joins, not PII.

2. Pseudonymization & ingestion

• At ingestion, strip direct PII and replace with GUID; log original-to-GUID mapping only in encrypted identity store. For reversible needs, use tokenization with KMS-wrapped keys so deletion can remove mapping.

3. Deletion workflow (request → enforcement)

• Request validated + recorded in Consent/Request store with request id, scope, timestamp.
• Enforcement steps (orchestrated by a deletion service):
  a. Mark PID as "delete_pending" and remove mapping from identity store (or zeroize keys) — this prevents future joins.
  b. Send deletion marker to stream processing layer to apply to downstream datasets (raw, enriched, warehouse, archives).
  c. Soft-delete in data lake: tag affected raw events with deletion_request_id and scrub direct PII fields. Physical purge scheduled per SLA and archival rules.
  d. Warehouse: emit retraction events that subtract user contributions from aggregates (see aggregates section), and mark historical rows referencing GUID as pseudonymized/removed.
  e. Cold archives: redact PII and mark entries; schedule permanent deletion according to retention policy.

4. Aggregates handling

• Two approaches depending on metric needs:
  a. Recompute-on-demand: for high-accuracy KPIs, maintain lineage and materialized views; when deletion occurs, re-run incremental recomputations for affected partitions (use retraction events). Use event-sourcing: each raw event has an event_id and user_guid so you can subtract user’s contributions deterministically.
  b. Corrective deltas: for heavy precomputed aggregates, produce delta records that subtract the user's prior contributions (retraction messages) and apply them to materialized tables. Keep a retraction ledger to ensure idempotency.
• For non-user-identifiable aggregated metrics (e.g., pure counts where user removal changes totals), either recompute or flag metrics as approximate and show audit provenance on dashboards. Consider differential privacy for public/rolling reports to reduce churn from deletions.

5. Auditing & verification

• Immutable audit log of requests and all enforcement actions (service, timestamps, actor, affected datasets). Logs stored with WORM (write-once) and accessible to compliance.
• Automated verification job: after deletion pipeline completes, run a read-only scan (sampling + targeted queries) ensuring no PII and that aggregates reflect retractions; produce signed compliance report.

6. Operational controls & BI impacts

• Data catalog & lineage: tag datasets with personal-data sensitivity, link to PID mapping and retention rules so analysts know which datasets require special handling.
• Dashboard behavior: prevent display of PII; show provenance badges and a "data freshness/compliance" indicator when metrics were recently recomputed for deletions.
• SLA/Trade-offs: real-time full recompute expensive — define classes of metrics (real-time, daily recompute, eventual consistency with deltas).

Edge cases & notes

• If identity mapping is deleted irreversibly, you cannot restore historic linkage — use irreversible pseudonymization if allowed.
• Cross-system joins (third-party tools) require contracts to ensure deletion propagation.
• For legal holds/exemptions, implement override flags with audited approval.

This design ensures deletions are propagated, PII removed/pseudonymized, aggregates adjusted deterministically or via deltas, and all steps are auditable for GDPR compliance while keeping BI analytic utility.