Business Intelligence Background Questions

A summary of business intelligence experience including the BI platforms and tools used, types of dashboards and reports built, data volumes and sources, analytical methods, stakeholder consumption patterns, and measurable business outcomes. Candidates should explain how BI efforts influenced decisions, examples of ETL or modeling work, and any leadership or ownership of BI initiatives.

EasyTechnical

25 practiced

List the common data sources you have integrated into BI (OLTP DBs, event stores, CSVs, APIs, third-party SaaS). For each provide: ingestion frequency, expected daily volume, transformation complexity (low/medium/high), and a short note on security or compliance considerations you handled.

Sample Answer

- OLTP Databases (Postgres/MySQL) - Ingestion frequency: near-real-time (CDC every few seconds/minutes) or nightly batch - Expected daily volume: 10–200M rows (varies by service; typical OLTP tables 10k–5M rows/day) - Transformation complexity: medium (schema normalization, slowly changing dimensions, deduplication) - Security/compliance: use TLS, network peering/VPC, least-privilege DB users, mask PII during extraction, enforce GDPR retention rules

- Event Streams (Kafka / Kinesis) - Ingestion frequency: real-time (streaming) - Expected daily volume: 100M–1B events - Transformation complexity: low–medium (parsing JSON/Avro, enrichment, windowed aggregations) - Security/compliance: TLS, ACLs, encrypted at rest, schema registry governance, PII tokenization before long-term storage

- Clickstream / Analytics Trackers (Snowplow, Segment) - Ingestion frequency: real-time or near-real-time - Expected daily volume: 10M–500M events - Transformation complexity: medium–high (sessionization, deduplication, attribution joins) - Security/compliance: anonymize IPs, consent flags, opt-out handling, GDPR/CCPA controls

- CSVs / Flat Files (SFTP, partner drops) - Ingestion frequency: hourly/daily/ad-hoc - Expected daily volume: 10MB–50GB (files vary) - Transformation complexity: low–medium (parsing, type coercion, validation) - Security/compliance: SFTP with key auth, checksum validation, quarantine invalid files, encrypt at rest

- REST APIs (internal/external) - Ingestion frequency: scheduled polling (minutely/hourly) or webhook-driven - Expected daily volume: 1k–1M records - Transformation complexity: low (mapping fields, rate-limit handling, retry logic) - Security/compliance: OAuth2, API keys rotation, handle vendor data-sharing agreements, rate-limit sensitive endpoints

- Third-party SaaS (Salesforce, Stripe, HubSpot) - Ingestion frequency: hourly/near-real-time (webhooks) or nightly ETL - Expected daily volume: 1k–10M records - Transformation complexity: medium (complex schemas, relational joins, dedupe across systems) - Security/compliance: use vendor-approved connectors, store tokens securely (secret manager), adhere to contractual data residency and audit logging

- Monitoring & Metrics (Prometheus, CloudWatch) - Ingestion frequency: high-frequency (seconds to minutes) - Expected daily volume: 10M+ metrics/time-series points - Transformation complexity: low (aggregation, downsampling) - Security/compliance: RBAC on dashboards, retention policies, do not store sensitive identifiers in metrics

Note: volumes and complexity depend on product scale; for each source I implement schema-on-write where possible, robust observability (data-quality checks, SLA alerts), and documented data contracts to satisfy compliance and downstream consumers.

MediumTechnical

28 practiced

Write an SQL pattern (standard SQL) to compute week-over-week percentage change for a metric stored in table metrics(metric_date DATE, metric_name STRING, metric_value FLOAT). Output columns: metric_name, metric_date, value, prev_week_value, pct_change. Explain how to handle missing previous week values.

Sample Answer

Approach: join each metric row to the row exactly 7 days earlier (same metric_name) so we compare same weekday; compute pct_change guarding against NULL or zero prior values.

sql

-- Self-join by metric_name and metric_date - 7 days (ANSI-ish date arithmetic)
SELECT
  m.metric_name,
  m.metric_date,
  m.metric_value        AS value,
  prev.metric_value     AS prev_week_value,
  CASE
    WHEN prev.metric_value IS NULL THEN NULL                       -- missing prior week
    WHEN prev.metric_value = 0 THEN NULL                           -- avoid divide-by-zero
    ELSE 100.0 * (m.metric_value - prev.metric_value) / prev.metric_value
  END AS pct_change
FROM metrics m
LEFT JOIN metrics prev
  ON prev.metric_name = m.metric_name
  AND prev.metric_date = m.metric_date - INTERVAL '7' DAY
ORDER BY m.metric_name, m.metric_date;

Notes / alternatives:- If your SQL dialect supports DATE_TRUNC('week', ...) and you want week-aggregates (e.g., weekly sums), aggregate by week_start then join on week_start - INTERVAL '7' WEEK.- Handling missing previous week: - Keep prev_week_value NULL to indicate no comparison available (typical). - For reporting, you can COALESCE(prev.metric_value, 0) but then pct_change will be misleading; better to show NULL or string "N/A". - When prev = 0, decide policy: show NULL, "inf", or a capped value; above uses NULL to avoid division-by-zero.- This is standard SQL; adjust INTERVAL syntax to your RDBMS if needed (DATE_SUB for MySQL, DATEADD for SQL Server).

MediumTechnical

27 practiced

A data scientist complains that a curated BI table lacks a granular feature needed for modeling. As the Data Engineer owning the BI table, how do you evaluate the request, estimate impact, and deliver with minimal disruption to existing reports?

Sample Answer

Situation: A data scientist asked to add user_session_id at event-level to our curated BI table because their model needs per-session features; current table is daily-aggregated by user.

Task: As the Data Engineer owning the table, I needed to evaluate whether to change the table, estimate downstream impact (reports, cost, latency), and deliver the feature with minimal disruption.

Action:- Clarify requirements with the scientist: why session-level, sample queries, expected cardinality (≈10M sessions/day), freshness SLA (hourly vs daily), and whether model can consume a separate dataset.- Inventory consumers: query table lineage, dashboards, scheduled jobs. Flag any that depend on current schema or aggregation.- Technical assessment: estimate storage increase (~10M new rows/day → +50GB/month), compute/backfill cost (Spark job ~2–3 cluster hours), and API/ETL changes. Evaluate alternatives: add optional session_id column preserving existing aggregation, publish a new session-level table/view, or provide a feature store materialized view.- Choose low-risk path: create a new session-level table (events_by_session) and a lightweight hourly materialized view joining to BI table for downstream users who need both. Keep original BI table unchanged.- Implementation plan: - Develop ETL that writes session-level table partitioned by date, include schema evolution policy and provenance tags. - Backfill last 90 days in controlled batch with monitoring and a sanity-check job comparing aggregate metrics to existing BI totals. - Add automated tests (row counts, null rates) and update data catalog + docs. - Communicate timelines, migration guidance, and deprecation plan for any future changes. - Rollout: deploy to staging → smoke tests → production during low-usage window; monitor dashboards and incident channel for 48 hours.

Result: Delivered session-level data within the agreed SLA without breaking existing reports. The scientist could build session features immediately; dashboards remained stable. The approach minimized blast radius, controlled costs, and left a clear path to promote session data into the primary pipeline later if adoption justified it.

This approach balances stakeholder needs, risk management, and operational cost—prioritizing backward compatibility and observability while enabling analytics work.

MediumTechnical

44 practiced

Describe how you implemented access control and data masking for BI dashboards containing PII. Include examples of role-based access rules, masked fields, and end-to-end enforcement from warehouse to dashboard.

Sample Answer

Situation: Our analytics team had BI dashboards (Looker/Tableau) that used warehouse tables containing PII (email, ssn, dob). I needed to enforce least-privilege and mask data end-to-end so analysts could work without exposing raw PII.

Approach & implementation:1) Warehouse-level controls- Column & row permissions: In Snowflake we created a raw schema (raw.events) owned by security team, then a curated schema with views. Used Snowflake RBAC: GRANT SELECT on curated views only to analyst roles; denied direct access to raw tables.- Dynamic masking & views: For sensitive columns we created parameterized views that return masked values based on role checks:

sql

CREATE OR REPLACE VIEW analytics.user_safe AS
SELECT id,
  CASE
    WHEN CURRENT_ROLE() IN ('PII_ADMIN','DATA_ENGINEER') THEN email
    ELSE CONCAT(SUBSTR(email,1,1),'***@',REGEXP_SUBSTR(email,'@(.+)$',1,1))
  END AS email_masked,
  CASE WHEN has_access_to_ssn(id) THEN ssn ELSE '***-**-****' END AS ssn_masked
FROM raw.users;

- Row-level security: Implemented RLS using session variables (user_org_id) and secure views to restrict rows to allowed orgs.

2) ETL & deployment- dbt models produce the curated views; tests assert no PII columns are present in analytics schema.- CI checks: dbt run + unit tests + role-based smoke tests that simulate analyst role querying views.

3) BI-layer enforcement- In Looker, we modeled fields mapping to masked view columns; Looker user_groups mapped to Snowflake roles. Tableau connected using service accounts with impersonation disabled; dataset published used the curated views only.- Additional masking in BI: for exploratory users we removed download/export privileges and applied UI-level masking for hover previews.

4) Monitoring & governance- Audit logging: Enabled Snowflake ACCESS_HISTORY and LOOKER query logs; alerts for any attempts to access raw tables.- Periodic review: Quarterly access reviews, automated certs in data catalog.

Result: Analysts retained full analytical capability without seeing raw PII. No unauthorized raw-PII access in six months; audit enabled quick detection of misconfigurations. Key lessons: enforce at the source (warehouse), codify masks in version-controlled models, and align BI permissions to warehouse roles.

EasyBehavioral

26 practiced

As a Data Engineer, how have you used BI output to influence product or business decisions? Provide a specific example where your BI work led to a measurable change in product strategy, revenue, or user behavior.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.