Database Selection and Trade-offs Questions

Requirements & constraints:

• Store ~100 TB compressed analytical data (cold + warm)
• Support ~10k dashboard queries/day (peak concurrency maybe hundreds)
• Many large joins across wide tables, acceptable latency: interactive (2–10s) for dashboards, longer for ad-hoc
• Cost-sensitive for steady-state; spikes tolerable if transient
• Role: data analyst—needs SQL, BI tools (Tableau/PowerBI), ad-hoc exploration

High-level options compared

1. Partitioned Postgres (OLTP/Postgres analytics)

• Concurrency: limited. Postgres can handle moderate analytic load but 100s of concurrent heavy joins will contend CPU/memory; queries block each other.
• Latency: good for small queries, degrades severely on large joins.
• Cost: potentially low infra cost for small scale, but to meet 100 TB + concurrency requires very large instances (expensive) or sharding complexity.
• ETL: straightforward ELT into tables; vacuum/maintenance overhead heavy.
• Trade-offs: strong ACID, familiar SQL, but not designed for MPP scale or many large analytic joins.

2. Columnar MPP (Redshift / Snowflake dedicated clusters)

• Concurrency: Redshift concurrency scaling requires WLM queues; can add concurrency scaling or RA3 nodes. Good for repeated dashboard queries when properly tuned.
• Latency: excellent for large joins if tables sorted/encoded, distribution keys chosen and data modeled (star schema). Materialized views and result caching reduce latency.
• Cost: predictable cluster costs; can be more expensive for always-on large clusters but controllable with pause/resume or auto-scaling (depending on vendor).
• ETL: ELT in stages, use COPY/UNLOAD; benefit from pre-aggregation and sort keys to accelerate joins.
• Trade-offs: best raw performance for large joins, requires careful distribution and vacuuming.

3. Serverless columnar (BigQuery)

• Concurrency: very high; handles thousands of concurrent queries via fully managed execution.
• Latency: interactive for many queries (with BI Engine or materialized views); large joins cost more time but still parallelized.
• Cost: pay-per-query (bytes scanned) — cost-efficient if you prune/partition/cluster and use materialized tables; can become expensive for repeated full-table scans unless you cache/results persist.
• ETL: supports ELT; encouraged to maintain partitioned & clustered tables, use materialized views and BI Engine for low-latency dashboards.
• Trade-offs: minimal ops, excellent concurrency, unpredictable cost spikes without controls; SQL compatibility good.

Recommendation (Data Analyst perspective)

• Primary: use serverless warehouse (BigQuery) for dashboards and ad-hoc concurrency needs: it offloads ops, scales elastically, supports BI Engine caching for sub-second dashboards, and handles 10k queries/day easily.
• Secondary: use MPP (Redshift / Snowflake) for cost-sensitive, predictable heavy batch workloads where you can optimize distribution keys, sort keys, and maintain reserved clusters for expensive joins or complex transformations that benefit from tuned hardware.
• Avoid relying solely on large Postgres for analytics at this scale.

Suggested architecture & ETL patterns

• Raw ingestion: land data in object storage (GCS/S3) partitioned by date.
• ELT: transform in warehouse using scheduled SQL jobs / dbt. Create wide fact and dimension tables, apply partitioning (date) and clustering (join keys).
• Performance: create denormalized/monthly/weekly aggregates and materialized views for common dashboard patterns; use BI Engine / result caching for hot dashboards.
• Cost control: table partitioning, clustering, column pruning, scheduled query caching, cost alerts, query quotas, and storing hot datasets in cached/reserved clusters if using Redshift.
• Monitoring & governance: query logs, access controls, cost allocation tags, and automated optimization (query profiling and automated materialized view refresh).

Final trade-off summary

• BigQuery: best for concurrency, low ops, analyst productivity; watch query costs and use clustering/materialized views.
• Redshift (MPP): best for predictable, tunable heavy join performance and cost predictability with reserved infrastructure.
• Postgres: only for small-scale analytics or serving low-concurrency reporting; not recommended as primary for 100 TB + heavy joins.

Requirements & constraints:

• Functional: reliable, repeatable analytics for historical reports and dashboards; support ad‑hoc investigator queries.
• Non‑functional: low and predictable dashboard latency (<5s), consistent results, minimal impact to OLTP, maintainable pipeline, reasonable cost.

High-level recommendation (primary): Move to an ETL-based analytics database (data warehouse or OLAP store) as the canonical analytics layer, with selective query pushdown and a federated query engine as transitional tooling. This gives predictable latency and strong consistency for historical analytics while minimizing OLTP load.

Why: BI dashboards typically query wide, aggregated historical data — best served by a tuned analytics DB (columnar/OLAP) with precomputed aggregates. Federation against OLTP + data lake causes cross-system joins, network/scan variability and timeouts. A warehouse provides consistent performance and easier maintenance.

Pros/cons (latency / maintenance / consistency)

• Leave federation (do nothing)
  • Latency: high, variable; frequent timeouts
  • Maintenance: low short-term but higher firefighting burden
  • Consistency: inconsistent due to non-atomic cross-source joins and schema drift
• ETL → analytics DB (recommended)
  • Latency: low and predictable for dashboards (pre-aggregations, columnar)
  • Maintenance: medium — requires ETL jobs and modeling, but centralized
  • Consistency: high — controlled refresh cadence, single source for analytics
• Pushdown filters (optimize queries)
  • Latency: medium improvement; helpful for some queries
  • Maintenance: low-medium (query tuning required)
  • Consistency: neutral — still cross-source joins risk mismatches
• Query federation engine (e.g., Presto/Trino, Athena federation)
  • Latency: medium; can improve by locality but still subject to slow source
  • Maintenance: medium-high (infra + optimizer tuning)
  • Consistency: medium — offers better planning but doesn't eliminate source inconsistency

Migration plan (phased)

1. Discovery & modeling (2–4 weeks)
   • Inventory BI queries, SLAs, data freshness needs.
   • Identify hot tables/joins and required dimensions/metrics.
2. Choose tech & design schema (2 weeks)
   • Columnar DW (Snowflake, Redshift, BigQuery) or OLAP cubes depending on cost and concurrency.
   • Design dimensional model and aggregate tables.
3. Build ETL pipelines incrementally (4–8 weeks)
   • Start with highest-impact dashboards: implement CDC or scheduled batch loads, transformations, and pre-aggregations.
   • Validate row-level reconciliation and business metric parity.
4. Parallel run & test (2–4 weeks)
   • Point selected BI reports to DW; run side-by-side with federated results for accuracy and stakeholder sign-off.
   • Implement monitoring, data quality checks, and SLA alerts.
5. Optimize & expand (ongoing)
   • Add more datasets, build incremental loads, materialized views.
   • Introduce automated lineage and documentation.
6. Decommission / hybrid strategy
   • Retire federation for covered reports.
   • Keep federation + pushdown for rare ad-hoc queries against OLTP with strict guardrails (query limits, read replicas).

Operational practices

• Use CI for ETL, unit tests, and row-level reconciliation.
• Define freshness SLAs (hourly/daily) per dataset.
• Implement RBAC and masks to avoid OLTP-sensitive reads.
• For urgent real-time needs, consider narrow pushdown to OLTP read replicas rather than full federation.

Conclusion
For a BI-focused data analyst team, an ETL-based analytics DB delivers the best trade-off: predictable low latency, strong consistency, and sustainable maintenance. Use selective pushdown and a federation engine only as transitional or for rare ad-hoc needs, not as the long-term backbone for dashboards.