Business Intelligence and Data Warehouse Architecture Questions

Design end to end business intelligence systems and the underlying data warehouse architecture. Topics include data ingestion patterns for batch and streaming sources, change data capture, transformation layers and the choice between extract transform load and extract load transform approaches, dimensional modeling and schema choices such as star and snowflake schemas, fact and dimension table design, slowly changing dimensions strategies, medallion and layered architectures, and the visualization and consumption layer. Also cover pipeline orchestration, monitoring, observability, data quality checks, and trade offs between centralized and federated approaches as well as real time versus batch processing.

MediumTechnical

69 practiced

Compare centralized BI teams versus federated analytics models for a global company with 200 analysts. Discuss trade-offs in speed to insight, domain knowledge, governance overhead, cost, tooling choices, and propose a hybrid approach that balances autonomy with consistency across metrics and datasets.

Sample Answer

High-level comparison (centralized vs federated) across the requested dimensions:

- Speed to insight: - Centralized: Slower for domain-specific asks because one team is a bottleneck; better for company-wide requests. - Federated: Faster locally—analysts embedded in teams can iterate quickly.

- Domain knowledge: - Centralized: May lack deep context per product/region; relies on stakeholder interviews. - Federated: Strong domain expertise, leading to more actionable analysis.

- Governance overhead: - Centralized: Easier to enforce standards, lineage, and security from one place. - Federated: Higher risk of inconsistent definitions; governance requires processes and tooling to scale.

- Cost: - Centralized: Lower headcount duplication; more efficient tooling spend but can create delays. - Federated: Higher staffing/tooling costs (redundant models, licences) but faster local impact.

- Tooling choices: - Centralized: Enterprise BI stack (Looker/PowerBI Premium, centralized ETL/warehouse, metadata/catalog). - Federated: Lightweight notebooks, self-service BI (Tableau/PowerBI), plus a shared warehouse and semantic layer.

Hybrid proposal (balance autonomy + consistency)1. Roles & structure: - Central Platform Team (10-15% of analysts): owns data warehouse, ETL standards, semantic/metric layer (dbt + metrics layer or LookML), governance, catalog, CI/CD, lineage, SLOs. - Embedded Analytics Pods (~85% of analysts): sit with product/region teams, build dashboards, run experiments, and extend central metrics for domain-specific KPIs.2. Guardrails & processes: - Canonical metrics maintained centrally (single source of truth). Any derived domain metrics must link back to canonical definitions or be explicitly versioned. - Data contract process and approval workflow for schema changes. - Review board (monthly) for approving new canonical metrics and resolving conflicts.3. Tooling & pipelines: - Central warehouse (Snowflake/BigQuery), transformation layer in dbt, semantic/metric layer (Looker/Metric Layer/dbt Metrics), governance via catalog (Alation/Amundsen) and access controls. - Provide templates, dashboard components, and a shared component library.4. Enablement: - Training, office hours, and certification paths for embedded analysts. - Shared CI pipelines and testing suites to avoid duplicate effort.5. KPIs to measure success: - Time from request → production dashboard - Metric consistency rate (% of dashboards using canonical metrics) - Duplicate model count - Stakeholder satisfaction

Trade-offs: this hybrid yields faster domain insights while keeping critical governance and metric consistency centralized. It requires investment in platform engineering, clear SLAs, and continuous enablement to avoid shadow-analytics sprawl.

HardSystem Design

96 practiced

Design a monitoring dashboard tailored for rapid detection and triage of data incidents in an analytics platform. Include widgets for freshness by dataset, row-count deltas, schema drift alerts, anomaly detection on aggregate metrics, pipeline job health, recent deploy annotations, and links to runbooks and responsible owners. Explain prioritization rules for alerting.

Sample Answer

Requirements:- Detect data incidents quickly (freshness, volume, schema, aggregate anomalies)- Enable fast triage with context (last good timestamp, pipeline job logs, recent deploys, runbooks, owners)- Minimal false positives; clear prioritization and escalation with SLAs

High-level design:- Ingest metrics from: scheduler (Airflow/Kubeflow), DB metadata (information_schema, table stats), ETL logs, observability (Prometheus), deploy annotations (CI/CD), and business metrics store.- Processing: metric collector → enrichment (dataset owner, runbook link, SLA) → anomaly engine (threshold + ML) → dedupe/aggregation → alerting + dashboard.

Dashboard layout & widgets:1. Global Health Strip (top) - Overall incident count, mean time to detect (MTTD), unresolved incidents by priority, last deploy marker timeline.2. Freshness by Dataset (heatmap) - Rows: datasets, Columns: time windows; color: staleness severity; click to see last successful run, lineage, owner, runbook link.3. Row-count Deltas (sparkline + percentage change) - Show baseline vs current; highlight >X% delta or outside seasonal bounds; drill to sample rows.4. Schema Drift Alerts (table) - New/removed columns, type changes, nullable changes, example failing queries; severity based on downstream impact mapping.5. Aggregate Metric Anomalies (time-series panel) - Auto-detected anomalies (seasonal-aware models: Prophet/LOESS or EWMA), confidence score, affected downstream reports.6. Pipeline Job Health (status board) - Last run status, runtime, retries, error snippets, link to logs and job run; colored by SLA breach.7. Recent Deploy Annotations (timeline) - Auto-imported from CI; clickable to correlate deploy → incident.8. Owners & Runbooks (side panel) - For selected incident: owner contact (Slack, email), runbook link, playbook steps, RTO/RPO.

Alerting & Prioritization Rules:- Priority = impact × urgency. - Impact = number of downstream dashboards/reports/users affected (precomputed lineage). - Urgency = SLA breach (freshness age vs SLA) OR severity score from anomaly engine.- Rule examples: - P0 (Immediate): Freshness > SLA by >2× OR schema-breaking change affecting >1 critical dashboard → page on-call, create incident, 15-min acknowledgment. - P1 (High): Aggregate metric anomaly with confidence >95% affecting >5 dashboards → notify owner + channel, 1-hour ack. - P2 (Medium): Row-count delta 30–70% for non-critical datasets → email digest, 8-hour ack.- Noise reduction: require two signals within window (e.g., freshness + failed job) or adaptive suppressions for known deployments; exponential backoff for repeated alerts until new evidence.- Escalation: owner → team on-call → data platform SRE after ack timeout. All alerts include direct links: failing query, sample data snapshot, job logs, deploy id, runbook, and one-click “declare incident”.

Operational notes:- Store metric history for trend baselining; backfill support; role-based views (exec vs ops).- Telemetry to measure false-positive rate, MTTD, MTTR to refine thresholds and models.

HardTechnical

79 practiced

You discover a 2% discrepancy between revenue reported in the warehouse and revenue in the transactional system. Outline a systematic investigation plan: which aggregate counts and checks to run at each stage, staging-level reconciliation, sampling queries, timezone and currency validations, join cardinality checks, and how to find duplicates or missing events.

Sample Answer

Start with scope & hypothesis- Clarify time window, entity (orders/payments/invoices), metrics (net revenue, gross, refunds), and acceptable tolerance (2%).- Form hypotheses: missing events, double-counting, currency FX, timezone shifts, late-arriving data, aggregation bugs.

Stage 1 — high-level aggregates (fast sanity checks)- Compare totals by day/week: SUM(amount), COUNT(*) for transactional system vs warehouse.- Compare DISTINCT customer_count, distinct order_count.- Compare min/max timestamps, NULL rates, number of refunded/cancelled events.- Ratio per day to spot when discrepancy began.

Stage 2 — staging-level reconciliation- Reconcile raw staging tables ingest → transformed tables: row counts, SUM(amount), partition-by-date counts.- Use checksums: SUM(CAST(HASH(id, amount, ts) AS BIGINT)) or COUNT(*) + SUM(amount) to detect transformations differences.- Confirm ETL job run times, reruns, and error logs.

Stage 3 — join cardinality & transformation checks- For core joins (orders→line_items→payments), run: - COUNT(*) orders, COUNT(line_items) grouped by order_id (min/max/avg), check for >1:1 or 0:1 anomalies. - LEFT vs INNER join diffs: rows lost by join.- Verify aggregation keys used (currency, merchant_id).

Stage 4 — sampling queries and record-level diff- Find sample mismatches: SELECT * FROM tx WHERE id NOT IN (SELECT id FROM w) LIMIT 100 and vice versa.- Use anti-joins to get missing and extra events.- For amounts that differ, compare row-level hashes.

Stage 5 — timezone, date-boundary & currency validation- Ensure both systems use same timezone and cut-off (UTC vs local). Compare event_ts normalized to UTC.- For multi-currency: compare currency codes, FX rate application, whether conversion happened at payment vs reporting time.- Recompute revenue using stored FX rates and compare.

Stage 6 — duplicates & idempotency- Detect duplicates: GROUP BY id HAVING COUNT(*)>1, or partition by id ORDER BY ts and look for repeated events with same amount/status.- Check idempotent ingest: dedupe keys, look for replays / replayed Kafka offsets or job restarts.

Stage 7 — root cause triangulation & fix- Correlate anomalies with deploy/ETL runs, schema changes, partner API changes.- Create reproducible query and small rollback/fix: re-run transformation on a small window.- Add automated checks (row counts, checksum diffs, daily ratio alerts) and a reconciliation dashboard.

Key SQL patterns to run- Aggregate deltas: SELECT date, SUM(amount_w), SUM(amount_tx), SUM(amount_w)-SUM(amount_tx) ...- Anti-joins for missing: SELECT tx.* FROM tx LEFT JOIN wh ON tx.id=wh.id WHERE wh.id IS NULL- Cardinality: SELECT order_id, COUNT(*) FROM line_items GROUP BY order_id ORDER BY COUNT DESC LIMIT 10- Duplicates: SELECT id, COUNT(*) FROM events GROUP BY id HAVING COUNT(*)>1

Communicate findings with stakeholders, quantify business impact, propose immediate patch and long-term monitoring.

MediumSystem Design

94 practiced

You must support a near-real-time dashboard with a 5-second end-to-end latency SLA for user activity counts. Compare streaming vs micro-batch architectures, design ingestion and materialization components (for example Kafka + stream processor + materialized store), and discuss trade-offs around consistency, operational complexity, fault tolerance, and cost.

Sample Answer

Requirements & constraints:- E2E SLA: 5s for user activity counts shown on dashboards.- High write throughput (user events), duplicates/late events possible.- BI user needs near-real-time correctness, not perfect transactional semantics.- Tools: Kafka available; BI consumes a materialized store (low-latency reads).

Comparison: streaming vs micro-batch- Streaming (event-at-a-time, e.g., Flink/Beam/ksqlDB): - Pros: sub-second processing, fine-grained windowing, can meet 5s SLA reliably, low tail-latency. - Cons: higher operational complexity, needs careful state management, more costly if always-on.- Micro-batch (e.g., Spark Structured Streaming with say 1–5s triggers): - Pros: simpler mental model, easier scaling for batch-style workloads, cheaper if you can tolerate batch windows. - Cons: batching introduces latency spikes and higher end-to-end jitter; guaranteeing <5s under load is harder.

Recommended architecture- Ingestion: Kafka topics for user events (partitioned by user/region).- Stream processing: Stateful stream processor (Flink or ksqlDB/Beam) performing: - Real-time aggregations (sliding/tumbling windows, sessionization) with event-time semantics and watermarking to handle late events. - Exactly-once semantics or idempotent sinks to avoid double-counting.- Materialized store: low-latency read store for dashboards: - Option A: Redis or DynamoDB for hot, per-key counters (sub-ms reads). - Option B: ClickHouse/BigQuery materialized views for analytic queries, updated by streaming writes for less-frequent, heavier queries.- Serving: BI tool queries the materialized store or a small API layer for access control and caching.

Key design points and trade-offs- Consistency: - Streaming + exactly-once (checkpointing, Kafka transactions) gives strong correctness for counts; eventual consistency for window boundaries until watermarks advance. - Micro-batch yields batch-consistent snapshots; within-batch events are atomic but you’ll see updates only per batch interval.- Fault tolerance: - Stream processors: checkpointing + Kafka retention provides replay; must size state backends and checkpoint frequency. - Micro-batch: simpler recovery (replay whole micro-batch), but recovery time may be larger.- Operational complexity: - Streaming: more ops effort (state storage, scaling, backpressure handling). - Micro-batch: easier if org already runs Spark jobs; less fine-grained tuning.- Cost: - Streaming often costs more (always-on compute, state storage); but for strict SLAs, it’s justified. - Micro-batch can be cheaper if you tolerate larger windows or use serverless batch runtimes.

Practical recommendation for BI role- For a hard 5s SLA, choose streaming for hot metrics (Kafka → Flink/ksqlDB → Redis/DynamoDB) to guarantee low-latency counts.- Use micro-batch/warehouse for heavy analytic aggregates (Kafka → streaming to append table → periodic batch load into ClickHouse/BigQuery).- Mitigations: implement watermarking and late-event policies (e.g., accept updates for a grace period), idempotent writes, dashboards that show “last updated” timestamps and provide eventual-correct indicators.- Monitor: latency SLOs end-to-end, consumer lag, state sizes, checkpoint durations, and error/retry rates.

This hybrid approach balances 5s latency for operational dashboards, cost control for large analytical workloads, and manageable operational overhead for a BI team.

HardSystem Design

89 practiced

Propose a GDPR-compliant deletion strategy for a warehouse that stores SCD2 history and aggregated facts. Address removing or pseudonymizing personal data across history, backups, snapshots, logs, and downstream analytics while maintaining auditability and minimizing disruption to aggregated metrics.

Sample Answer

Requirements & constraints:- Must remove or irreversibly pseudonymize personal identifiers on request (right-to-be-forgotten) across SCD2 history, backups, snapshots, logs, and downstream analytics.- Maintain audit trail of deletion actions (who/when/why) without retaining deleted PII.- Minimize disruption to aggregated KPIs (avoid recomputing months of historical metrics if possible).

High-level strategy:1. Classify data: tag columns/tables as PII, pseudonymizable, or non-PII. Maintain a metadata catalog.2. Soft workflow: implement a Deletion Request Service that records request ID, scope, requester, legal basis and target keys (customer_id).3. Two-tier remediation: - Fast path (pseudonymize): replace direct identifiers with irreversible token (hash+secret salt stored in HSM/KMS) or use format-preserving tokenization. Keep surrogate keys for joins but remove plaintext PII. - Hard delete: for tables where retention requires full erasure (logs, some SCD2 rows), remove rows or wipe PII fields irreversibly.4. SCD2 handling: for historical rows, replace PII fields with pseudonym and set a deletion marker column (deleted_at, deletion_request_id). Preserve SCD2 timestamps/keys for analytics but no cleartext PII.5. Aggregates & facts: - Prefer deterministic pseudonymization so counts by pseudonym stay consistent; for metrics that must exclude deleted users, maintain a lightweight privacy-safe lookup table mapping active status. - For aggregates that must be recalculated (e.g., lifetime value), compute delta adjustments: calculate contribution of target user(s) from raw event facts and subtract from precomputed aggregates, store adjustment records to preserve auditability.6. Backups/snapshots/logs: - Implement retention and legal hold policy. For backups older than retention and not under legal hold, allow regular purge. - For backups that must be retained but contain PII: maintain encrypted backups with key rotation and, upon deletion request, rotate/zeroize keys for chunks containing deleted PII (crypto-erase) or maintain an index mapping objects to affected keys so you can re-encrypt without plaintext. - For immutable snapshots where crypto-erase isn't possible, record that data is unreachable and log action; if regulation requires, schedule reingestion without the subject and rebuild snapshot.7. Downstream systems: - Propagate deletion events via reliable event bus (Kafka with compacted topic). Consumers subscribe to deletion topic and apply their own pseudonymization/deletion; enforce SLA for downstream compliance. - For BI semantic layers (Looker/PowerBI), filter views to hide deleted PII; maintain materialized views as above with adjustment deltas.8. Auditability: - Immutable audit log (append-only, write-once) recording request lifecycle and actions taken (tables/rows affected, hash tokens used). - Store only metadata and hashes in audit log; avoid storing deleted plaintext.9. Operational & governance: - Test deletion requests in staging with replay to validate metric impact. - Create runbooks, monitoring for backlog of deletion requests, and regular compliance reports.Trade-offs:- Pseudonymization preserves analytics but may not satisfy full erasure in some jurisdictions—coordinate legal.- Crypto-erase is efficient but depends on granular encryption and key management.Example small flow:- User requests deletion → Deletion Service writes request → Issue deletion event to Kafka → ETL workers update SCD2 rows: set deleted_at, replace email/name with token(email) using HSM salt → facts processor computes delta adjustments and writes adjustment table → downstream BI materialized views apply adjustments on read; audit log entry created.

This approach balances regulatory compliance, auditability, and minimal disruption to aggregated metrics.

Unlock Full Question Bank

Get access to hundreds of Business Intelligence and Data Warehouse Architecture interview questions and detailed answers.

Join thousands of developers preparing for their dream job.