Business Intelligence and Analytics Performance Questions

Performance considerations for business intelligence and analytics tools and pipelines. Topics include extract versus live connections, incremental refresh strategies, aggregated tables and precomputation, dashboard profiling, minimizing visual complexity, and caching strategies for reporting layers. Candidates should understand when to denormalize data for reporting, how to monitor query times inside BI tools, and trade offs between real time versus pre aggregated reporting.

HardTechnical

65 practiced

Compare pre-aggregated tables, materialized views, and OLAP cubes for multi-dimensional analytics at petabyte scale. Discuss query flexibility, freshness, storage costs, maintenance complexity, and ability to serve ad-hoc drill-downs. Recommend scenarios where each approach is preferable.

Sample Answer

High-level summary: Pre-aggregated tables, materialized views (MVs), and OLAP cubes address the same problem—speeding multi-dimensional analytics—but trade off flexibility, freshness, cost, and maintainability differently. Below is a compact comparison and recommendations tailored to a BI analyst working at petabyte scale.

Comparison (by dimension)- Query flexibility - Pre-aggregated tables: Low–medium. Fast for predefined rollups; poor for unanticipated groupings. - Materialized views: Medium. Can support a variety of SQL patterns if designed broadly (e.g., aggregation on different dimensions), but ad-hoc queries outside defined aggregates still slow. - OLAP cubes: Medium–high for slice/dice/dimensions they model; limited for arbitrary joins or new dimensions.- Freshness - Pre-aggregates: Often periodic batch; seconds-to-hours latency depending on ETL cadence. - MVs: Can be incremental or near-real-time if DB supports fast refresh; depends on engine. - OLAP cubes: Varies—real-time OLAP is costly; most cubes refresh on schedule or incremental streams.- Storage costs - Pre-aggregates: High if many granular rollups are stored (combinatorial blowup). - MVs: Moderate—only store necessary aggregates, DB compression helps. - OLAP cubes: High upfront modeling and storage, but compression and sparse storage mitigate costs.- Maintenance complexity - Pre-aggregates: High operational overhead to manage many tables and ETL pipelines. - MVs: Lower—DB handles refresh logic but needs tuning and dependency tracking. - OLAP cubes: High design complexity (dimensional modeling, hierarchies) and tooling maintenance.- Ad-hoc drill-downs - Pre-aggregates: Poor unless lower-level aggregates retained. - MVs: Better if base tables accessible; otherwise limited. - OLAP cubes: Best UX for drill-downs within modeled hierarchies; seamless in BI tools.

Recommendations (scenarios)- Use pre-aggregated tables when: dashboards demand sub-second responses for a small set of repeatable KPIs and you can accept scheduled freshness. Good for heavy read patterns and cost-effective when limited rollups suffice.- Use materialized views when: you want SQL-native performance improvements with simpler maintenance; suitable if your DB supports incremental refresh and you need moderate flexibility across queries.- Use OLAP cubes when: business requires rich ad-hoc slice/dice, hierarchies, and drill-paths for analysts and execs; invest in cubes if user interactivity and UX outweigh storage/maintenance costs.

Practical tip: at petabyte scale, hybrid strategies win—keep narrow pre-aggregates for top KPIs, MVs for common analytical patterns, and a cube/semantic layer for analyst-facing exploration; always expose raw/base tables for deep ad-hoc needs and implement cost-based access (materialized fast path vs. on-demand slow path).

MediumTechnical

86 practiced

An hourly ETL job refreshes aggregated tables at :20 each hour. Users report stale cache for up to 10 minutes after refresh. Propose a reliable architecture and process to invalidate BI caches atomically after the aggregated tables are replaced while minimizing user disruption and ensuring no partial refreshes are exposed.

Sample Answer

Solution summary: use a double-buffer (staging) + atomic swap + deterministic versioning so BI always reads a single committed version; then trigger cache invalidation immediately after swap and optionally warm caches. This prevents partial reads and minimizes downtime.

Proposed process (step-by-step):1. ETL writes into a new staging table with version id (e.g., agg_v20251122_13) or suffix: aggregated_staging.2. Run validation checks (row counts, checksums, sample queries). Fail ETL if checks fail.3. When validated, perform an atomic rename/swap: - If DB supports atomic rename within same schema (Postgres: ALTER TABLE old RENAME TO old_backup; ALTER TABLE staging RENAME TO aggregated; inside a short maintenance window), or use CREATE OR REPLACE VIEW aggregated AS SELECT * FROM agg_vX for atomic pointer swap. - Alternative: maintain a metadata table current_version and update it in a single transaction; dashboards query via join to pick current_version.4. Immediately after successful swap commit, call BI cache invalidation endpoint (Tool API/webhook) or publish a message to a cache-invalidation topic (Kafka/SNS). The BI layer listens and invalidates relevant dashboard caches.5. Optionally warm caches: run representative dashboard queries (headless browser / API queries) after invalidation to repopulate caches to avoid slow first user.6. Add monitoring & fallback: if invalidation fails, retry and alert; keep old backup table for quick rollback.

Example (view + metadata approach):- ETL creates table agg_vX, validates.- In a single DB transaction: - UPDATE metadata SET current_version = 'agg_vX' WHERE name='aggregated';- Dashboards query: SELECT * FROM public.aggregated_view WHERE version = (SELECT current_version FROM metadata);- After commit, ETL posts to BI invalidate API.

Key points and trade-offs:- Atomic pointer swap (view or metadata) avoids long locks and guarantees readers see either old or new data, never partial.- Post-swap invalidation ensures caches map to the new data. Use retries and idempotent invalidation.- Warming caches reduces user-perceived latency.- If your BI tool lacks API invalidation, set short cache TTLs (1–5 minutes) plus the above for stronger guarantees.- Keep rollback/backups for <10-minute safe window.

Monitoring:- Track ETL validation metrics, swap time, cache invalidation success, and first-query latency after refresh. Alert on anomalies.

This architecture ensures atomic visibility of aggregates, reliable cache invalidation, minimal disruption, and a clear rollback path.

MediumTechnical

70 practiced

Write SQL (choose BigQuery, Snowflake, or Spark SQL) to create a daily aggregated table 'agg_daily_sales(region, category, day, total_amount, total_orders)' from a raw 'orders' table and show how to incrementally populate today's partition using insert-overwrite or partition insert. Include DDL and DML and explain assumptions about partitioning.

Sample Answer

Approach: create a partitioned, clustered aggregate table (partitioned by day DATE) and upsert daily aggregates for a single day (today) using BigQuery MERGE. This is efficient for BI workloads and avoids full-table rewrites.

DDL — create the target table (if not exists):

sql

CREATE TABLE IF NOT EXISTS analytics.agg_daily_sales (
  region STRING,
  category STRING,
  day DATE,
  total_amount NUMERIC,
  total_orders INT64
)
PARTITION BY day
CLUSTER BY region, category;

DML — compute today's aggregates from raw orders and MERGE to replace/insert today's partition:

sql

MERGE analytics.agg_daily_sales T
USING (
  SELECT
    region,
    category,
    DATE(order_ts) AS day,
    SUM(amount) AS total_amount,
    COUNT(1) AS total_orders
  FROM raw.orders
  WHERE DATE(order_ts) = CURRENT_DATE()  -- only today's data
  GROUP BY region, category, day
) S
ON T.region = S.region AND T.category = S.category AND T.day = S.day
WHEN MATCHED THEN
  UPDATE SET total_amount = S.total_amount, total_orders = S.total_orders
WHEN NOT MATCHED THEN
  INSERT (region, category, day, total_amount, total_orders)
  VALUES (S.region, S.category, S.day, S.total_amount, S.total_orders);

Notes & assumptions:- orders.order_ts is a TIMESTAMP/DATE; we use DATE(order_ts) to derive partition key.- Partitioning: day DATE partition reduces scan cost when querying by day and makes deletions/upserts efficient.- Clustering by region, category improves query performance for common GROUP BY/filters.- For late-arriving data, run the same MERGE for the affected past date(s) (adjust WHERE to specific date) to refresh that partition.- Alternative: use partition decorator (analytics.agg_daily_sales$20251122) for overwrite, or use INSERT...SELECT when only inserting new regions (no updates).- Validate idempotency: the MERGE is idempotent for identical source results.

MediumTechnical

84 practiced

In Looker, expensive Explores are causing long query times. Describe how you would use Persistent Derived Tables (PDTs), aggregate tables, and Looker caching to improve performance. Include trade-offs, refresh strategies, and how to detect/handle stale data exposed to end users.

Sample Answer

Start by profiling the expensive Explores: identify the heavy joins, high-cardinality dimensions, and slow SQL (Looker’s SQL Runner / Query History). Then apply a three-pronged strategy: PDTs, aggregate tables, and caching.

PDTs- Use PDTs to precompute expensive joins/transformations into materialized tables in your warehouse (derived_table: {persist_for / sql_trigger_value} or datagroup). This moves CPU from runtime Looker SQL to scheduled warehouse jobs.- Refresh strategies: for near-real-time, use sql_trigger_value or datagroup triggers tied to upstream table changes; for daily summaries use persist_for with a TTL (e.g., 1h/24h).- Trade-offs: faster queries vs. storage cost and staleness window. Complex rebuilds can spike warehouse cost.

Aggregate tables- Create aggregate_table definitions (or dedicated summary tables) for common rollups (daily, weekly, by region). Let Looker’s aggregate_table feature route relevant queries to pre-aggregates.- Use granularities that match dashboard filters to maximize hit rate.- Trade-offs: fewer rows scanned but less flexibility — ad-hoc queries outside the aggregation will bypass and be slow.

Looker caching- Leverage persistent cache and query cache: set model-level persistent_cache_ttl and explore-level:datagroup or sql_trigger_value to invalidate appropriately.- Use query caching for identical parameterized queries and result_cache_for to keep results briefly in Looker.- Trade-offs: caches improve latency but can expose stale data if invalidation is misconfigured.

Detecting and handling stale data- Surface data currency: add a dashboard tile showing last_pdt_build_time or last_refresh metadata (querying INFORMATION_SCHEMA or Looker System Activity). Display “last updated” timestamps on dashboards.- Implement freshness SLAs per dashboard (e.g., “data within 15 minutes”) and show warnings if exceeded.- Invalidate caches/datagroups programmatically after ETL runs (webhooks or API to trigger datagroup or PDT rebuild).- For critical reports, offer a “Refresh now” button that triggers a rebuild (via Looker API) and blocks until completion.

Monitoring & operational notes- Monitor PDT build durations and warehouse credits; stagger rebuild schedules to avoid concurrency spikes.- Keep a balance: use aggregates for high-frequency business metrics, PDTs for heavy joins/transforms, and caching for repetitive identical queries.- Document assumptions (staleness windows) for stakeholders so they understand trade-offs between performance and freshness.

HardTechnical

78 practiced

How would you compute daily unique active users per campaign (a high-cardinality metric) for 100M daily events so that dashboard queries return in seconds and storage is reasonable? Discuss approximate algorithms (HyperLogLog), sketch-based pre-aggregation, exact counters, and trade-offs in accuracy, storage, and query latency.

Sample Answer

Approach summary: for 100M daily events and many campaigns, use sketch-based pre-aggregation (HyperLogLog) at ingestion + periodic rollups to keep queries in seconds and storage modest. Keep exact counters only where business needs perfect counts.

Design details:- Ingestion: stream events into a processing layer (Spark/Flink or kinesis + lambdas). For each event emit (campaign_id, day, user_id).- Pre-aggregate per shard/partition into HyperLogLog (HLL) sketches keyed by (campaign_id, day). HLL merge is associative so you can incrementally update sketches as events arrive.- Store sketches in a compact store (Redis, RocksDB, or columnar DB as binary blobs). One HLL per (campaign, day) typically ~1–2 KB depending on precision (e.g., p=14 -> ~1.5KB) => 1.5KB * 100k campaigns = ~150MB/day.- Query path: dashboard reads pre-merged HLL for requested campaigns/dates and returns estimated uniques in milliseconds. For time-series, fetch a few blobs and convert to counts client-side.

Accuracy vs storage trade-offs:- HLL gives ~1-2% relative error with p=14. Increase p for better accuracy at linear storage cost.- Sketches are probabilistic: small bias for very low-cardinality groups; use sparse mode or switch to exact counters for small campaigns.

Exact counters and hybrid approach:- Maintain exact distinct counters for top-N campaigns or those with SLAs using materialized views (e.g., daily distinct user sets in BigQuery with APPROX_COUNT_DISTINCT vs COUNT(DISTINCT) when exact needed).- Hybrid: use HLL for most, exact for VIP campaigns. For small-cardinality segments, exact sets (bitmaps like Roaring) are efficient.

Operational considerations:- TTL/retention for old days; rollup weekly to further compress.- Periodic compaction jobs to merge shard sketches into a single per-(campaign,day) blob.- Displaying error: surface confidence intervals, and optionally show exact numbers for sampled small sets or allow drill-down to exact recompute (on-demand, slower).- Testing: validate HLL estimates against batch exact counts on historical data to tune precision and detect biases.

Trade-offs summary:- Latency: HLL pre-aggregation → seconds or sub-second queries. Exact recompute → minutes.- Storage: HLL ~KB per key vs exact sets potentially much larger.- Accuracy: HLL ~1-2% error (configurable); exact counters = 0% but costly.This hybrid sketch-first architecture balances dashboard responsiveness, storage efficiency, and acceptable accuracy for BI needs.

Unlock Full Question Bank

Get access to hundreds of Business Intelligence and Analytics Performance interview questions and detailed answers.

Join thousands of developers preparing for their dream job.