Business Intelligence Tools and Features Questions

Covers expert proficiency with major business intelligence tools such as Tableau, Power BI, and Looker, and the advanced capabilities these platforms provide. Topics include creating calculated fields and parameters, conditional formatting, complex filtering, dashboard interactivity and responsive layout design, and best practices for visualization and user experience. Includes performance optimization techniques such as extract versus live connection trade offs, query optimization, incremental refresh strategies, and general performance tuning. Also covers governance and security features including access controls and sharing models, considerations for tool selection and recommending the right tool for a specific use case, and high level migration strategies between BI platforms.

EasyTechnical

18 practiced

Explain row-level security (RLS) in BI tools and outline a simple implementation where sales reps can see only their region's data. Provide one approach for Power BI (dynamic RLS with USERPRINCIPALNAME) and one for Tableau (data source filter or user filter). Mention common pitfalls in maintenance and testing.

Sample Answer

Row-level security (RLS) restricts which rows each user can see in reports so users only access data they're authorized to view (e.g., sales reps see only their region). It's enforced in the BI layer (or DB) and prevents accidental exposure in shared dashboards.

Power BI — Dynamic RLS with USERPRINCIPALNAME:- Create a table (UserRegion) mapping user principal name (email) to Region.- In the model, add a relationship from UserRegion to Sales[Region].- Define a role with this DAX filter on UserRegion:

dax

[UserEmail] = USERPRINCIPALNAME()

- On the Sales table you can use RELATED(UserRegion[Region]) = Sales[Region] or filter via the relationship. Publish to Power BI Service and assign users to the role (or rely on Azure AD mapping).

Tableau — Data source filter / User Filter:- Add a Users table (username/email → Region) to the data source.- Join to Sales on Region, or use an extract filter.- Use a user filter: create a calculated field [CurrentUser] = USERNAME() then set a data source filter like [UserRegion] = [CurrentUserRegion] (or use VizQL “User Filters” / “Row Level Security” via the Users table).- Publish to Tableau Server and ensure authentication passes USERNAME() correctly.

Common pitfalls (maintenance & testing):- Email/username mismatch between auth provider and your mapping table — keep canonical identity fields.- Caching/Extracts: stale extracts can expose old data; schedule refreshes and validate after changes.- Complex joins: ensure relationship direction and cardinality don’t inadvertently broaden access.- Testing: test as different users (Power BI “View as role”, Tableau “Impersonate User”) and include negative tests. Automate regression checks when user-role mappings change.

MediumTechnical

18 practiced

A dashboard contains many quick filters which generate thousands of queries when users interact. Propose design and backend changes to reduce query sprawl and improve concurrent performance, including suggestions like parameterization, pre-aggregation, using materialized views, and client-side caching. Explain trade-offs.

Sample Answer

Situation: Our dashboards have many quick filters; each interaction issues distinct, ad-hoc SQL leading to thousands of queries and high DB concurrency/latency.

Proposal—overview:1. Parameterization & query templating- Use prepared statements or BI-tool parameterized queries so identical SQL shapes are reused; enables DB plan caching and reduces parsing/compilation overhead.Trade-off: must ensure safe parameter types and avoid constructing dynamic SQL that prevents plan reuse.

2. Pre-aggregation & rollups- Create periodic aggregated tables (hourly/daily) for common dimensions (region, product, date). Direct dashboards to these tables for high-cardinality metrics.Trade-off: near-real-time accuracy vs latency of refresh; storage cost increases.

3. Materialized views- Use materialized views for expensive joins/filters with scheduled refresh or incremental refresh (if DB supports it).Trade-off: complexity of refresh logic; potential staleness.

4. Query consolidation & debounce on client- Debounce filter changes (e.g., 300–500ms) and batch multiple filter changes into a single query; collapse redundant queries when users rapidly change UI.Trade-off: UX latency vs backend load.

5. Client-side caching & optimistic caching- Cache recent query results keyed by parameter hash in browser or edge (CDN). Serve cached results for identical parameter sets; invalidate on known data updates.Trade-off: memory use on client and possible staleness.

6. Server-side cache & resultstore- Add a result cache layer (Redis) to store query results or partial aggregations; TTLs based on freshness needs.Trade-off: cache invalidation complexity.

7. Adaptive sampling / approximate query engines- For exploratory filters, return approximate results (HyperLogLog, approximate COUNT). Provide exact option when needed.Trade-off: lower precision for big performance gains.

8. Monitoring and telemetry- Track query cardinality, cache hit rates, and slow queries; iterate on which aggregates/views to build.

Conclusion: Combine parameterization, pre-aggregations/materialized views, client+server caching, and UI debouncing to reduce query sprawl. Balance freshness, storage, and complexity depending on stakeholder SLAs and acceptable staleness.

HardTechnical

25 practiced

Explain how to implement dataset lineage and metadata management for BI reports across multiple tools. Include techniques for capturing lineage (parsing SQL, connector metadata, logging), integrating with a data catalog (e.g., Alation), and how to present lineage to business users to increase trust and enable impact analysis.

Sample Answer

Requirements & goals:- Capture end-to-end lineage for BI reports across tools (Tableau, Power BI, Looker), source systems, ETL/ELT jobs.- Make lineage visible and searchable in a catalog (e.g., Alation) to enable trust and impact analysis for business users.

Implementation approach:1. Ingestion / capture techniques- Parse authored SQL & LookML: extract table/column references, joins, CTEs, and aliases using a SQL parser (e.g., sqlparse, Apache Calcite). Store normalized upstream objects.- Connector metadata: pull dataset/table/schema metadata from databases, cloud warehouses and BI tool APIs (Tableau Metadata API, Power BI REST, Looker’s LookML/explore metadata) to link report objects to physical tables and columns.- Execution/logging hooks: capture job run metadata and query logs (Snowflake, BigQuery INFORMATION_SCHEMA, audit logs) to record runtime dependencies and most-recent-used timestamps.- ETL lineage: ingest metadata from orchestration tools (Airflow, dbt manifests) to map transformations and model versions.

2. Integration with data catalog (Alation example)- Use catalog ingestion APIs to push standardized lineage records (source -> transformation -> dataset -> report). Map to Alation metadata model (tables, columns, dashboards, queries).- Enrich catalog entries with provenance attributes: last-run, owner, SLA, data quality checks, business glossary terms, and confidence score.- Auto-link: match column/table names using deterministic and fuzzy matching; validate with sample queries and sampling to reduce false links.

3. Presenting lineage to business users- Business-friendly view: show a simplified graph from KPI/dashboard card → semantic dataset → source systems, with hoverable cards showing owner, freshness, definitions, and recent incidents.- Impact analysis UI: enable “what breaks if X changes?” by selecting a table/column and highlighting downstream dashboards, scheduled reports, and SLAs; include clickable run-history and score of impact severity (usage frequency, exec viewers).- Trust signals: surface data quality badges (QA results, anomaly detectors), lineage depth (how many hops), and human-verified notes. Allow users to request verification or flag issues directly from the lineage pane.- Notifications: integrate with Slack/email for owners when high-impact sources change.

Key trade-offs & best practices:- Combine static parsing and dynamic query-log evidence to reduce false positives/negatives.- Prioritize high-value assets (top dashboards) for manual validation early.- Store lineage as a graph (Neo4j or graph DB) for fast traversal; expose through catalog API and an embeddable widget in BI tools.- Governance: enforce owners and SLAs; keep automated onboarding but require periodic human review.

This approach provides traceability, drives trust via visible provenance and quality signals, and enables fast impact analysis for business stakeholders.

MediumTechnical

18 practiced

Write a SQL query that flags outlier transactions per user where an outlier is defined as amount > mean + 3 * stddev over that user's last 365 days. Table: transactions(id, user_id, amount, occurred_at). Include handling for users with fewer than 5 historical transactions (do not flag them) and show expected columns: user_id, id, amount, occurred_at, is_outlier.

Sample Answer

Approach: for each transaction, compute aggregate statistics (count, mean, stddev) over that user's prior 365-day window (excluding the current row) using a correlated subquery (works in most engines like Postgres). Only flag if historical_count >= 5 and amount > mean + 3 * stddev. Use stddev_samp (or STDDEV) depending on your SQL dialect.

sql

SELECT
  t.user_id,
  t.id,
  t.amount,
  t.occurred_at,
  CASE
    WHEN stats.hist_count >= 5
         AND stats.hist_stddev IS NOT NULL
         AND t.amount > stats.hist_mean + 3 * stats.hist_stddev
    THEN TRUE
    ELSE FALSE
  END AS is_outlier
FROM transactions t
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) AS hist_count,
    AVG(amount) AS hist_mean,
    STDDEV_SAMP(amount) AS hist_stddev
  FROM transactions t2
  WHERE t2.user_id = t.user_id
    AND t2.occurred_at >= t.occurred_at - INTERVAL '365 days'
    AND t2.occurred_at < t.occurred_at   -- exclude current tx to use historical window
) stats ON TRUE;

Key points:- Correlated lateral subquery computes per-row historical aggregates efficiently when indexed on (user_id, occurred_at).- Excludes current transaction from stats so outlier threshold is based on prior behavior.- Requires >=5 historical transactions to avoid unstable statistics.- Use STDDEV_SAMP (sample stddev) — adjust to STDDEV_POP if population stddev is desired.- For large tables consider pre-aggregating per user-day buckets or using window functions with RANGE/ROWS if supported and performant; ensure proper indexing to keep query performant.

HardSystem Design

18 practiced

Architect an enterprise BI platform that must support 10,000 concurrent users, sub-second response for a few executive dashboards, and near-real-time streaming dashboards for operations. Describe components (data warehouse, semantic layer, caching, query layer, BI tools), scaling strategies, freshness SLAs, and cost-performance trade-offs.

Sample Answer

Requirements & constraints:- 10,000 concurrent users- Sub-second responses for a small set of executive dashboards (aggregates, <500ms)- Near-real-time streaming dashboards for operations (data freshness 1–5s)- Cost-performance trade-offs must be explicit

High-level architecture:- Ingest: Kafka for events (operational streams), CDC (Debezium) for DB changes, batch ETL via Airflow- Stream processing: Flink/Beam to compute rolling aggregates, dedupe, enrich; writes to streaming stores and materialized views- Data warehouse: Cloud MPP (Snowflake / BigQuery / Redshift Serverless) for historical analytics, BI exploration- Serving layer / OLAP store: ClickHouse / Pinot for sub-second, high-concurrency reads for both exec and operational dashboards (columnar, vectorized)- Semantic layer: LookML/DBT + a semantic layer (e.g., Metrics Layer or Looker model) to centralize metrics, lineage, access control- Cache / Query acceleration: Dedicated pre-aggregations/materialized views in OLAP store; Redis or Varnish for very hot single-value queries; BI-tool in-memory caching for dashboards- BI tools: Looker/Power BI/Tableau connected to semantic layer; real-time dashboards via WebSocket endpoints reading from Pinot/ClickHouse- Monitoring & Observability: Prometheus/Grafana, query latency tracing, SLO alerts

Scaling strategies:- Separate workloads: isolate interactive exec dashboards (small set of high-priority pre-aggregates) from ad-hoc analytical queries via different clusters/virtual warehouses- Autoscale OLAP nodes based on concurrency/CPU; use read-replicas for BI query farm- Precompute: materialized aggregates for common slices (time, region, product) to guarantee sub-second- Throttle/queue ad-hoc queries, route heavy exploratory queries to warehouse with larger resources and higher latency- Use ephemeral compute for batch/ETL; keep streaming compute long-running for low-latency transforms

Freshness SLAs:- Executive dashboards: eventual consistency with strong SLAs for pre-aggregations: data latency <= 1–2 minutes; sub-second response from cached aggregates- Operational streaming dashboards: end-to-end latency <= 1–5 seconds (Kafka → Flink → OLAP store → dashboard)- Historical/analytical reports: SLA minutes to hours depending on job

Cost vs performance trade-offs:- Pre-aggregation and OLAP store capacity increases cost but reduces query latency—invest for executive dashboards.- Serving everything from MPP warehouse is cheaper for storage but will cost more compute and have higher latency under concurrent load.- Real-time streaming (Flink + Pinot) has steady operational cost; cheaper to limit real-time to operational dashboards and keep executives on precomputed aggregates.- Use tiered storage: keep hot data in OLAP/ClickHouse, colder in warehouse to balance storage cost.

Operational notes:- Define a central metrics catalog with ownership, freshness, and SLA tags.- Run load tests simulating 10k concurrent users (mix of read patterns) and tune pre-aggregations and autoscaling thresholds.- Implement row/column-level security in semantic layer to ensure governance.

This design balances sub-second SLAs for a few critical dashboards via materialized views and fast OLAP engines, while supporting large-scale concurrent exploration through an MPP warehouse and careful workload isolation to control costs.

Unlock Full Question Bank

Get access to hundreds of Business Intelligence Tools and Features interview questions and detailed answers.

Join thousands of developers preparing for their dream job.