Working with Large-Scale Datasets Questions

Brief comparison (Live / DirectQuery vs Extract / Import):

• Data freshness:

  • Live: real-time or near-real-time—queries reflect latest source data.
  • Extract: freshness depends on refresh schedule (hourly, nightly); not real-time.

• Query load on source:

  • Live: every user interaction issues queries to the source → high load with many concurrent users.
  • Extract: source hit limited to refresh windows; users query the BI engine instead → much lower operational load.

• Initial load time:

  • Live: dashboard renders once source responds; initial renders can be fast if source indexed, but can be slow for complex joins/aggregations.
  • Extract: initial extract can be heavy (full load), but subsequent loads are fast from the in-memory/columnar cache.

• Interactivity:

  • Live: can be interactive if the source supports fast ad-hoc queries and concurrency; otherwise sluggish.
  • Extract: highly interactive (in-memory engine, client-side query acceleration, caching) even under many concurrent users.

• Caching:

  • Live: depends on source and BI tool query caching; cache lifetimes are short if data freshness required.
  • Extract: built-in cache (Power BI VertiPaq, Tableau Hyper) and query result caching produce consistent fast UX.

Recommended strategy for an operational KPI dashboard updated hourly with many concurrent users:

1. Use extracts/imports as primary approach:

   • Schedule incremental refresh every hour (or use CDC) to minimize extract time and keep data within SLA.
   • Use partitioning/incremental loads to avoid full refreshes.

2. Design for performance:

   • Build aggregated materialized tables in the warehouse for dashboard-level KPIs (pre-aggregate by hour, region).
   • Push heavy filtering/joins to the warehouse (query folding) so extracts are compact.

3. Hybrid options for specific needs:

   • If a single metric truly needs near-real-time (seconds), keep that metric via a lightweight live connection to a dedicated, scaled source (read-replica) and combine into the dashboard using blended data or composite models (Power BI DirectQuery + Import).

4. Infrastructure & BI settings:

   • Use read replicas or a reporting mart to isolate reporting load.
   • Enable BI tool caching, tune cache TTL to just under refresh interval.
   • Limit complex visuals, use fewer quick filters, and ensure visuals use indexed fields.

5. Concurrency considerations:

   • Test with expected concurrent sessions; scale gateway/refresh capacity.
   • Monitor refresh durations and query times; raise refresh window or further aggregate if needed.

Why this approach: extracts minimize query load and maximize interactivity for many users; hourly incremental refresh meets the stated freshness requirement. Hybrid/live only where true real-time is required, and always isolate reporting from OLTP to protect source performance.

Situation: Dashboards are slow because queries join very large event/fact tables to a user profile table at runtime. Below are five practical strategies a BI analyst can propose, with how they work and trade-offs.

1. Denormalize / wide tables

• What: Store frequently-joined user attributes directly on the fact rows (copy profile fields into the event table during ETL).
• Benefit: Eliminates join cost; very fast reads for dashboards.
• Trade-off: More storage, ETL complexity, risk of stale profile data unless you update backfills or capture profile change events.

2. Columnar storage / partitioning and clustering

• What: Use columnar tables (e.g., Redshift, BigQuery, Snowflake) and partition by date; cluster/sort by user_id.
• Benefit: Scans only relevant partitions and reduces IO for selected columns; improves join performance via colocated sort keys.
• Trade-off: Needs careful key choice; re-clustering costs; slower single-row OLTP.

3. Indexing and join keys

• What: Ensure foreign keys/user_id columns are indexed on both sides; consider covering indexes for profile attributes used in predicates.
• Benefit: Faster lookups and join performance for point-joins.
• Trade-off: Index maintenance overhead on writes and increased storage; limited benefit for full table scans/analytical workloads.

4. Materialized views / pre-joined tables

• What: Create materialized views or precomputed events ⨝ profiles tables refreshed incrementally (nightly or streaming).
• Benefit: Dashboard queries read precomputed joins/aggregates—much lower latency.
• Trade-off: Data freshness vs. performance; more storage; refresh complexity on profile changes.

5. Aggregation and rollups

• What: Pre-aggregate metrics at dashboard granularity (daily, by user segment) and store aggregate tables.
• Benefit: Dramatically reduces rows scanned for KPIs and charts.
• Trade-off: Loss of row-level detail unless you keep raw store; complexity for ad-hoc queries requiring drill-down.

Additional tactics (complementary):

• Use query pushdown and limit columns selected; avoid SELECT *.
• Cache BI tool extracts or use in-memory cubes for heavy dashboards.
• Use adaptive query planning (broadcast smaller table) or join hints to optimize execution plans.

Recommendation: Start with materialized pre-joins/aggregates for critical dashboards for immediate wins, add partitioning/clustering to improve underlying scan costs, and denormalize selectively where freshness requirements allow. Monitor query plans and cost metrics to iterate.

SCD quick definitions

• Type 1: Overwrite the attribute in the dimension — no history retained. Simple, keeps only current state.
• Type 2: Add a new row for each change with surrogate key + effective_from/effective_to (or current_flag). Preserves full history; facts join to the version valid at event time.
• Type 3: Add extra columns to hold “previous” value(s) (e.g., previous_address). Limited history (one or two prior states).

Recommendation for frequent address changes with no desire to create new historical rows for minor corrections

• Use a hybrid: treat routine/minor corrections as SCD Type 1 (overwrite current dimension) and treat major business-significant address changes as SCD Type 2. Keep a separate immutable address_history audit that records every correction for traceability without impacting reporting unless explicitly used.

How to implement so historical reporting isn’t broken

1. Dimension design
   • Customer_dim: surrogate_key, natural_key, current_address fields, current_flag, last_updated.
   • Customer_address_history: surrogate_key (or natural_key), address, valid_from, valid_to, change_type (major|minor|correction), source, created_at.
2. Ingestion rules
   • If change is “major” (e.g., customer moved residence) create new SCD2 row in Customer_dim with new surrogate_key and set previous valid_to; update current_flag.
   • If change is “minor/correction” (typo, standardization), update current_address in Customer_dim (SCD1) and INSERT a row into Customer_address_history with change_type=correction.
3. Preserve fact joins
   • Store event_date on facts. For historical reporting that must reflect the address at event time, join facts to Customer_address_history using event_date BETWEEN valid_from AND valid_to. For reports that should use current address, join to Customer_dim.
4. Backfill/corrections to past rows without breaking reports
   • Never mutate historical valid_from/valid_to in the audit table. If you discover past data errors, add a correction row with change_type=correction and a flag correction_applies_to_past=true and link to affected fact IDs (or provide a mapping table fact_id → correct_address_version). Reporting can be configured:
     • Default: use immutable history for event-time accurate reporting.
     • For corrected reporting: optionally apply correction mapping or re-run an ETL snapshot to regenerate fact-address joins (documented, auditable).
5. Example SQL pattern (lookup by event date)
   sql

   SELECT f.*, h.address
   FROM facts f
   JOIN customer_address_history h
     ON f.customer_id = h.customer_id
    AND f.event_date BETWEEN h.valid_from AND h.valid_to;
   

Why this approach

• Avoids SCD2 row explosion for small corrections while preserving an auditable trail.
• Keeps historical reporting stable (reports that rely on event-time history use the history table) and enables controlled corrections or rebakes when business requires corrected historical views.

Approach (high-level)

• Goal: for each new user, compute time delta between their first session/registration and their first purchase; aggregate into distribution (histogram, percentiles) at scale (200M events).
• Use an ETL job that finds first-event timestamps per user once (idempotent), then joins to first-purchase timestamps. Persist results in a partitioned, indexed table and build pre-aggregates for dashboarding.

Implementation (SQL pattern)

-- 1. derive first_seen and first_purchase per user (stream/batch)
CREATE TABLE user_first_events PARTITION BY DATE(first_seen);
INSERT INTO user_first_events
SELECT
  user_id,
  MIN(CASE WHEN event_type = 'session_start' OR event_type='signup' THEN event_time END) AS first_seen,
  MIN(CASE WHEN event_type = 'purchase' THEN event_time END) AS first_purchase
FROM events
WHERE event_time >= @start_date AND event_time < @end_date
GROUP BY user_id;

-- 2. compute time-to-first-purchase (in hours/days) and store
CREATE TABLE ttfp PARTITION BY DATE(first_seen);
INSERT INTO ttfp
SELECT
  user_id,
  first_seen,
  first_purchase,
  TIMESTAMP_DIFF(first_purchase, first_seen, HOUR) AS hours_to_purchase
FROM user_first_events
WHERE first_purchase IS NOT NULL;

Partitioning & indexing

• Partition by first_seen date to limit scans for cohort queries.
• Cluster/index by user_id and event_time for fast point lookups and joins.
• Use hash bucketing if single-day partitions still large.

Windowing & window functions

• Use MIN(...) FILTER or conditional aggregates to compute first timestamps in a single scan.
• Alternatively, use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) and pick row_number=1 for first event types if event streams are pre-filtered.

Late-arriving events & correctness

• Implement streaming ingestion with a watermark (e.g., 24–72 hours) to allow most late events; run daily backfill/compaction job to reconcile late purchases.
• Use idempotent upserts (MERGE) into user_first_events: if a new earlier first_purchase appears, update and recompute downstream aggregates.
• Keep an audit table of changed user_ids and re-aggregate only those (incremental recompute) rather than full rebuild.

Performance optimizations

• Pre-filter events to only relevant event_types before heavy grouping.
• Use approximate_quantiles for interactive percentile queries on dashboards (e.g., APPROX_QUANTILE).
• Materialized view or pre-aggregated histogram per cohort-day (buckets like 0-1h,1-6h,6-24h,1-7d,7+d) for fast dashboard rendering.
• Use and maintain statistics; choose appropriate cluster keys for your DB (BigQuery partitioning/clustering, Snowflake clustering, Redshift sort keys).

Presentation for business consumption

• Provide:
  • Cohort table: new users by first_seen date with median, p25, p75, % converting within 24h/7d/30d.
  • Interactive histogram and CDF (time-to-event distribution) with cohort filters (acquisition channel, country, campaign).
  • KPI cards: conversion rate, median TTFP, accelerating/declining trend.
  • Drilldowns: list of users in each bucket, ability to compare cohorts side-by-side.
• Materialize daily artifacts used by BI tool (Looker/Power BI): precomputed aggregates and percentiles to avoid heavy live SQL.
• Add freshness metadata and indicate when data is final vs provisional (due to watermark/late arrivals).

Trade-offs and notes

• Watermark window balances freshness vs accuracy; communicate provisional windows to stakeholders.
• For ultra-low-latency needs use streaming + stateful processing (Beam/Flink) with checkpoints; for most BI daily batch with incremental upserts is simpler and cheaper.
• Use approximate methods for interactive queries; compute exact numbers asynchronously for executive reports.

Situation: We need weekly retention cohorts from 100B event rows — compute who performed an event in week N (cohort) and whether they returned in subsequent weeks. The challenge is scale: storage layout, incremental compute, and acceptable accuracy vs cost/latency.

Approach — storage & layout

• Store raw events in a columnar data warehouse (BigQuery/Snowflake/Redshift Spectrum/Delta Lake). Use a time-partitioned table by event_date (daily) to prune scans.
• Cluster/cluster-by user_id (or user_hash) and event_name to co-locate a user’s events together — greatly reduces IO for per-user scans when computing cohorts.
• Keep a compact user dimension table (user_id, signup_week) and lightly denormalized event summary tables (user_id, event_week, first_seen_week).

Pre-aggregation & incremental computation

• Compute first_seen_week per user (cohort) once and materialize (daily incremental job). Use INSERT-ON-CONFLICT or MERGE to update only new users.
• Build incremental weekly aggregates: for each week, produce a table of (cohort_week, active_week, user_count) by joining new weekly event_batch with first_seen table. Run as streaming/ETL job (Airflow / dbt / Dataflow), processing only new partitions to keep latency low.
• Materialized views or scheduled aggregation tables feed dashboards — refresh nightly or hourly depending on SLA.

Approximation techniques

• HyperLogLog (HLL) per (cohort_week, active_week) to estimate unique users with tiny memory. Store HLL sketches (e.g., BigQuery HLL functions or Redis bitmaps) and merge sketches incrementally.
• Bloom filters for membership tests (e.g., filter out users already counted in earlier batches) to reduce duplicate work; be cautious about false positives reducing counts.
• Use count-distinct approximation when exact dedupe across 100B rows is too expensive.

Trade-offs

• Accuracy: HLL gives ~1-2% error with low storage; Bloom filters introduce false positives (under-count). Exact counts require heavy joins/deduplication — expensive.
• Latency: Pre-aggregations + incremental updates allow near-real-time cohorts. Full re-computation for exact results increases latency to hours/days.
• Cost: Columnar + partitioning minimizes scan cost. HLL reduces compute/storage cost drastically. Full exact joins over 100B rows increase compute and cloud costs.

Concrete pattern: maintain user_first_week table (materialized), then for each incoming partition compute per-week HLL of active users keyed by cohort_week and active_week; merge into cohort_aggregates. Dashboards read cohort_aggregates for fast queries; provide confidence intervals for HLL estimates to stakeholders and offer exact-refresh on-demand for critical slices.