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

125 practiced

Your BI environment experiences latency spikes from concurrent heavy ad-hoc queries by analysts, causing dashboard SLA breaches. Propose a multi-layered solution involving resource governance (warehouse sizing, workload isolation), query queuing/prioritization, result caching, and providing sandboxes. Include policy and technical implementation details.

Sample Answer

Situation: Analysts’ concurrent ad‑hoc queries are causing BI warehouse latency spikes and dashboard SLA breaches.

Approach: Multi-layered solution combining governance, workload isolation, queuing/prioritization, result caching, and sandboxes — with both policy and technical controls.

Solution (policy + technical):

1) Resource governance & sizing- Policy: Define service tiers (dashboard SLA, scheduled ETL, ad‑hoc) and assign SLOs.- Tech: Right‑size warehouses: dedicate a high‑concurrency, high‑priority cluster for dashboard serving (small latency-optimized nodes) and separate compute for heavy ad‑hoc (larger transient clusters). Use autoscaling and cost caps.

2) Workload isolation- Policy: Enforce that production dashboards run only on the dashboard cluster; heavy analysis must target analyst clusters.- Tech: Use role-based compute assignment (e.g., Snowflake resource monitors + warehouses, BigQuery reservations, Redshift Workload Management). Tag queries by user/application and route via connection profiles.

3) Query queuing & prioritization- Policy: Define priority rules (dashboards > scheduled jobs > ad‑hoc). SLA violations trigger auto-escalation.- Tech: Implement queueing with concurrency limits and weighted priorities (e.g., Snowflake multi-cluster warehouses, Redshift WLM queues, or a middleware query router). Set short timeouts for interactive queries and longer for long-running jobs; return progress to users.

4) Result caching & materialized views- Policy: Encourage using cached datasets for common metrics; maintain freshness SLAs.- Tech: Use result caching, query result reuse, and scheduled materialized views/aggregate tables updated incrementally. Provide a metrics API/semantic layer (Looker/AtScale) exposing precomputed metrics to dashboards.

5) Analyst sandboxes- Policy: Provide ephemeral sandboxes for experimentation with quota and cost monitoring; encourage promotion to shared derived tables for repeated queries.- Tech: Spin isolated, auto‑terminating clusters per analyst/team with enforced resource quotas and network/concurrency limits. Offer templates and shared datasets to reduce raw joins.

Monitoring, enforcement & education- Implement observability: per-query latency, queue depth, cost per query, and SLA dashboards. Use alerts and automated actions (pause low‑priority jobs, scale up dashboard cluster).- Run quarterly governance reviews and train analysts on optimized SQL patterns, using explain plans and limiting wildcard scans.

Expected outcome: Reduced dashboard latency, clear analyst experimentation space, predictable costs, and faster answers via caching—while maintaining agility for ad‑hoc analysis.

MediumTechnical

135 practiced

A dashboard performs expensive joins between a 100M-row fact table and 10-20 small dimension tables causing slow queries. Propose modeling changes (denormalized flattened tables, pre-join materialized views), clustering/index strategies, and estimate expected performance gains and trade-offs such as update complexity and storage footprint.

Sample Answer

Start by clarifying dashboard SLAs (allowed latency, freshness). Then propose two complementary approaches: denormalization (flatten) for read-heavy dashboards and/or pre-joined materialized tables for near-real-time needs.

Modeling changes- Denormalized flattened table: one nightly/near-real-time ETL job that joins fact + 10–20 dims into a wide fact_flat (only the attributes needed by dashboards). This eliminates runtime joins.- Materialized view / pre-joined table: create MV refreshed incrementally (if DB supports MV logs) or scheduled refreshes for fresher data.

Example (SQL):

sql

-- create a pre-joined table refreshed nightly
CREATE TABLE analytics.fact_flat AS
SELECT f.*, d1.name AS cust_name, d2.segment, d3.region, ...
FROM raw.fact f
LEFT JOIN dim_customer d1 ON f.customer_id = d1.id
...
;
-- or materialized view (DB-specific)
CREATE MATERIALIZED VIEW analytics.mv_fact AS
SELECT ...
WITH DATA;

Clustering / indexing strategies- Partition by date on the fact (range partition) to prune scans.- Cluster/sort by high-selectivity dashboard filters (customer_id, region) so reads hit fewer blocks.- Use covering indexes on key dashboard predicates and group-by columns to avoid lookups.- In columnar warehouses, use sort keys and compaction to speed scans.

Estimated performance gains- Removing 10–20 small joins typically reduces CPU and I/O; expect 2–10x faster dashboard queries depending on join complexity and network. Partition pruning + clustering can further cut scan size by 5–50x for narrow time/windowed queries.- Materialized views often give ~5–100x improvements vs full join at query time.

Trade-offs- Storage: flattened tables and MVs increase storage (roughly original fact size × (1 + avg_dim_width/fact_width)); expect 10–100% extra depending on selected dimensions.- Freshness & update complexity: incremental refresh logic required; near-real-time increases ETL complexity and potential latency. Full refreshes are simpler but may not meet SLA.- Maintainability: schema changes in dims require ETL/MV updates. Risk of denormalization inconsistencies — add periodic reconciliation tests.- Cost: more storage and ETL compute; balance against user productivity gains.

Recommendation (Data Analyst POV)- Start with a flattened table containing only dashboard-used columns and nightly refresh. Add partitioning by date and clustering on main filters. Measure before/after (query latency, compute cost). If stakeholders need fresher data, implement incremental materialized views or change data capture pipelines.

MediumTechnical

72 practiced

Using Tableau/Power BI performance tools you discover a worksheet executes a subquery that aggregates the entire fact table for each filter. Explain specific techniques to optimize the workbook and the generated SQL, including use of extracts, context filters, parameterized filters, or pre-aggregations. Provide step-by-step actions.

Sample Answer

Start by confirming the problem: use the performance/profiling tool to capture the worksheet’s generated SQL and identify the subquery that repeatedly aggregates the entire fact table per filter.

Step-by-step optimization plan:

1. Short-term: Reduce repeated full-table scans- Convert heavy worksheets to extracts (Tableau) / import mode (Power BI) for static/historical data: create extracts with necessary fields and aggregations to avoid live repeated scans.- In Tableau, create an extract that pre-aggregates at the workbook grain (Create Extract → Aggregate data for visible dimensions). In Power BI, use Power Query to fold and load summarized tables.

2. Use context filters to limit data before expensive aggregations- Promote high-selectivity filters (date range, region) to context so subsequent filters operate on a reduced dataset (Tableau: right-click filter → Add to Context). This changes SQL to apply WHERE before aggregation.

3. Replace multi-valued quick filters with parameterized or single-value filters- Use parameters (or slicers set to single-select) to push the filter value into the main query rather than causing separate per-value subqueries. In Tableau use parameters plus calculated fields; in Power BI use measure/filter context or query parameters to enable folding.

4. Pre-aggregate / materialize- Create summary tables in the source DB (or a scheduled ETL): daily aggregates by dimension, stored procedure or materialized view. Point workbook to these tables for dashboards that don’t need row-level detail.

5. Reduce fields and join complexity- Remove unused fields, avoid complex calculated fields in the viz that force row-level computation. Move calculations to the source or extract.

6. Validate and iterate- After each change, re-run the profiler to confirm generated SQL now aggregates smaller dataset or uses pre-aggregated tables. Measure dashboard load time and DB CPU.

Trade-offs and notes:- Extracts and pre-aggregations reduce freshness—schedule refreshes appropriately.- Context filters can change row counts and affect other sheets; document behavior.- Parameterization improves SQL but may limit end-user flexibility; consider UX.

This sequence yields fewer full-table aggregations, simpler SQL, and predictable, faster dashboards.

MediumTechnical

77 practiced

Design SLOs and instrument metrics to monitor BI platform health: query latency percentiles (p50/p90/p99), cache hit ratios, refresh success/failure rates, query concurrency, and data freshness. Explain threshold choices, alerting strategies, and what dashboards you would provide for on-call engineers.

Sample Answer

Situation/Goal: Ensure BI platform reliability so analysts and stakeholders receive timely, accurate reports. I’d define measurable SLOs, instrument metrics to track them, set pragmatic thresholds and alerting tiers, and build on-call dashboards with runbooks.

SLOs & instrumentation (what to collect):- Query latency (histogram + aggregated percentiles): p50 < 200ms, p90 < 1s, p99 < 5s for interactive dashboards. Use request latencies labeled by workspace/dashboard, user-type, dataset.- Cache hit ratio (gauge/counter): SLO ≥ 85% for dashboard tile cache; track per-dataset and global.- Refresh success rate (counter of runs): SLO ≥ 99.5% over 7d; record success/failure, duration, owner, error type.- Query concurrency (gauge): Soft target < 200 concurrent queries; monitor queue length and wait time.- Data freshness (timestamp gauge per dataset): SLO: 95% of critical datasets fresher than SLA (e.g., < 15 min latency).

Threshold rationale:- p50/p90/p99 reflect UX — most must be fast (p50/p90); p99 catches outliers. Percentile targets chosen to balance user experience and backend cost.- Cache hit threshold balances cache effectiveness vs staleness risk.- Refresh and freshness SLAs aligned to business needs (reporting cadence).- Concurrency limits protect shared DB; thresholds based on historical capacity.

Alerting strategy:- Three tiers: 1. Page (P0/P1) — immediate paging for hard SLO breaches: sustained p99 > 10s for >5m, refresh success rate < 95% in 1h for critical pipelines, mass data-staleness (>10% of critical datasets stale > SLA). Use burn-rate windows. 2. High-priority ticket (P2) — degrade but not outage: p90 > 2s for >15m, cache hit < 70% for 30m, concurrency > soft limit with growing queue. 3. Info/ops (P3) — anomalies and trends: rising error rates, one-off slow queries.- Alerts include context: recent query examples, affected dashboards, owner/team, runbook link, suggested mitigations (e.g., clear cache, restart job, throttle ad-hoc queries).

Dashboards for on-call:- Executive overview: SLO tile counts (green/yellow/red), trend lines, current incidents.- Latency summary: real-time p50/p90/p99 heatmap by workspace and dashboard; top slow queries with SQL snippets and explain plans.- Cache health: global and per-dataset hit ratio, evictions, TTLs.- Ingestion/refreshes: recent runs, success/failures, durations, owners, failure reasons, retry counts.- Freshness matrix: datasets vs SLA, last-updated times, downstream dashboards impacted.- Concurrency & DB load: concurrent queries, queue length, host CPU/IO, slowest queries causing locks.- Alert stream and runbook panel with one-click actions (disable heavy reports, rerun refresh, notify stake holders).

Additional practices:- Tag metrics with dataset/dashboard/owner to route alerts.- Use histograms for accurate percentiles; store raw trace for root-cause.- Regularly review thresholds with stakeholders and adjust based on traffic patterns.- Post-incident blameless reviews to refine SLOs and runbooks.

HardTechnical

72 practiced

Propose a framework to ensure metric correctness and detect silently broken metrics (for example a join bug or missing partition in ETL). Include unit/integration tests for SQL metrics, data lineage tracking, anomaly detection on metric time series, and alerting/roll-back procedures.

Sample Answer

Framework overview: treat metrics as software — define contracts, test, monitor, and have safe rollback.

1) Metric contract & spec- For each metric record: SQL definition, primary grain, input tables, expected null rate, value ranges, FK constraints, refresh cadence, SLAs.- Store as YAML/JSON alongside SQL.

2) Unit tests for SQL metrics (local/CI)- Small, deterministic fixtures: create temp tables with known rows; assert metric equals expected.Example:

sql

-- test_total_revenue.sql
CREATE TEMP TABLE orders(id,int,amount numeric);
INSERT INTO orders VALUES (1,100),(2,200);
SELECT SUM(amount) as total_revenue FROM orders;
-- assert total_revenue = 300

- Use tSQLt / Great Expectations SQL / dbt tests to run these and fail CI on mismatch.

3) Integration tests- Run metrics end-to-end on sampled production-ish dataset (daily partition) to validate joins, partitions, and performance. Include lineage-based regression tests: remove a partition or change a join to simulate bugs and ensure alerts fire.

4) Lineage tracking- Auto-extract lineage from SQL (dbt, OpenLineage, Marquez) and store graph. Use lineage to: - Identify upstream sources for impacted metrics - Trigger targeted tests when upstream schema changes

5) Anomaly detection on metric time series- Implement hybrid rules: - Statistical: rolling z-score / seasonal decomposed residuals (STL) with configurable window - Model-based: Prophet or simple ARIMA for expected and confidence intervals - Thresholds from historical pct change (e.g., >30% day-over-day) and business rules- Combine to produce an anomaly score; flag sustained anomalies (3 periods) or single high-severity events.

6) Alerting & runbook- Alerts to Slack/email with context: metric name, current value, expected, last successful run, upstream changes, lineage snapshot, and query plan.- Triage playbook: quick checks (recent deployments, late partitions, upstream job failures), run unit/integration tests, inspect raw data sample.- Roll-back: if metric SQL was changed via CI, provide automated rollback via: - Revert PR in repo (dbt snapshot), redeploy previous model, re-run downstream materializations; or - If data issue, mark metric as “stale” in dashboard with reason and ETA.

7) CI/CD integration- On PR: run lint, unit tests, lineage diff, run lightweight integration tests. Block merge on failures.- On deploy: run full integration tests and smoke metric checks; only promote if green.

8) Governance & observability- Metric catalog (with owners), SLA dashboard, audit trail of changes.- Regular audits: monthly lineage drift, test coverage, and anomaly false-positive review.

Why this works: combines deterministic tests to catch logic bugs, lineage to localize failures, statistical monitoring to detect silent data issues, and operational playbooks for fast remediation and safe rollbacks — suitable for a data analyst owning metric reliability.

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.