BI engineer system design interview questions

As asked

Design a metrics platform that serves 500 internal users with self-serve dashboards. Users range from executives who need daily KPI summaries to data scientists who write custom SQL. The data lives in Snowflake. Walk me through the architecture from ingestion through presentation.

Sample answer outline

Layers: raw data in Snowflake (source schemas), transformation via dbt (staging, intermediate, marts), a semantic layer (dbt Semantic Layer or Looker) for metric governance, Tableau/Looker/Mode for presentation by persona. Data ingestion via Fivetran or Airbyte. Orchestration via Airflow or dbt Cloud. For executives: pre-built certified dashboards with extract caches. For data scientists: direct Snowflake access with query cost governance. Address user authentication, row-level security, and usage monitoring.

Expect these follow-ups

• How do you prevent data scientists from running runaway queries that impact dashboard users?
• How would you version-control LookML or Tableau workbooks as the platform grows?

As asked

Your dbt pipeline transforms raw data from 15 source systems into 80 marts models running every hour. When a source is late, downstream dashboards show stale data. Design a pipeline that handles partial failures gracefully and communicates data freshness to dashboard consumers.

Sample answer outline

Use Airflow DAGs or dbt Cloud jobs with source-specific tasks so a late source fails only its branch without blocking unaffected sources. Watermark tables: after each source load, update a metadata table with the max loaded timestamp. dbt source freshness checks against this table before running downstream models. Dashboard freshness indicators: a small Tableau or Looker widget reads the metadata table and shows a green/yellow/red staleness badge. Failed source branches retry on a backoff schedule and trigger a Slack alert after N failures.

Expect these follow-ups

• How do you handle a source that is late but then provides 6 hours of backfill data all at once?
• How would you prioritize which models to run first if the warehouse has limited concurrency?

As asked

The business wants to be alerted automatically when daily revenue drops more than 15% vs the same day last week or shows an unusual spike. Design the monitoring system from detection to notification.

Sample answer outline

Compute a scheduled SQL query that compares current day's revenue to the 7-day-ago equivalent using a DATE_TRUNC window. A threshold-based alert (static 15% rule) is easy but generates false positives during seasonal patterns. Statistical approach: compute a trailing 28-day mean and standard deviation, alert when the current value is more than 2 standard deviations from the mean. Tools: Monte Carlo, dbt's elementary package, or a custom Airflow sensor. Notification via Slack webhook or PagerDuty with a link to the relevant dashboard and the raw numbers.

Expect these follow-ups

• How do you prevent a holiday period from permanently raising or lowering the baseline?
• What is the difference between an anomaly detection approach and a simple threshold alert, and when does each fail?

As asked

Your team has 80 dbt models and 5 engineers committing to the same repo. Model changes can break downstream dashboards. Design a CI/CD pipeline that catches regressions before they reach production.

Sample answer outline

On each PR: run dbt build --select state:modified+ (only changed models and their descendants) against a dev schema using dbt Cloud's Slim CI or a GitHub Actions job. Run dbt test on the affected models. Check dbt docs diff to surface schema changes to reviewers. Use dbt's defer flag to reference production artifacts for unchanged upstream models, avoiding a full rebuild. Gate merges on test pass. On merge to main: run full dbt build in production. Notify failed runs to Slack with model name and error.

Expect these follow-ups

• How does the --defer flag work and what artifacts does it require?
• What do you do when a CI run takes 40 minutes because too many models are in the modified set?

As asked

The CEO wants a single-page weekly dashboard showing revenue vs target, customer churn rate, support ticket backlog, and pipeline coverage. It must load in under 3 seconds and always be accurate by Monday 7am. Design this end-to-end.

Sample answer outline

Build a pre-aggregated 'executive summary' table in the warehouse, refreshed by a dbt job that finishes by Sunday 11pm, scheduled via Airflow or dbt Cloud. Tableau or Power BI connects to this table (not the raw fact tables). Use Tableau extract or Power BI Import mode so dashboard load is sub-second. Set up alerts if the dbt job fails so the on-call analyst can investigate before Monday morning. The executive summary table should have a single row per week with one column per KPI, making the dashboard query trivial.

Expect these follow-ups

• How do you handle a KPI definition that changes mid-year (e.g., churn rate formula is revised)?
• What do you put in the dashboard to show stakeholders when the data was last updated?

As asked

A logistics company needs a dashboard showing delivery status updated every 60 seconds for 1000 dispatchers. The source is Kafka events from delivery trucks. Design the pipeline from Kafka to the dashboard.

Sample answer outline

Kafka events consumed by a stream processor (Flink, Spark Structured Streaming, or Snowflake's Snowpipe Streaming). Land into a Snowflake or Redshift table with micro-batch inserts. Power BI or Tableau live connection with auto-refresh (Tableau has a 60-second minimum auto-refresh). Alternatively: materialize aggregates into a Redis or ClickHouse layer for sub-second latency. For 1000 concurrent dispatchers, a pre-aggregated operational data store is more scalable than direct warehouse queries. Address WebSocket-based push vs polling.

Expect these follow-ups

• How do you handle a 30-second Kafka consumer lag and ensure the dashboard does not show stale data with a green indicator?
• At what point would you move from Snowflake to a purpose-built OLAP database like ClickHouse?

As asked

Your analytics team has 200 dbt models, 80 Tableau workbooks, and 40 Looker explores. Analysts spend hours finding the right dataset. Design a data catalog and discovery system that reduces time-to-insight.

Sample answer outline

Use a catalog tool (DataHub, Atlan, Alation, or Collibra) that ingests metadata from dbt artifacts (manifest.json), Tableau Server REST API, and Looker API. Populate column-level lineage from dbt's node graph. Add search by business term with a glossary layer. Critical features: certified vs experimental dataset flags, last-updated freshness, and row-count stats. Process: run catalog ingestion as a post-step in the dbt CI job so the catalog always reflects the current state of models.

Expect these follow-ups

• How do you keep the catalog accurate when a model is renamed or deprecated?
• How would you integrate the catalog with Slack so analysts can search for datasets without leaving their tool?

As asked

You are building a SaaS product where each customer tenant should see only their own data in an embedded Tableau or Looker dashboard. The system must support 500 tenants, and onboarding a new tenant must not require a human to create new database objects. Design the architecture.

Sample answer outline

Two approaches: single shared database with tenant_id column + dynamic RLS (simpler, harder to isolate), or separate schemas/databases per tenant (stronger isolation, higher cost). For embedding, use Tableau Embedded (JWT tokens with user attributes for RLS) or Looker Embed with user attributes passed at session creation. Tenant onboarding automation: provision a new schema, run a seeding script, add the tenant to the RLS mapping table. Address token expiry, session management, and what happens when the RLS mapping is misconfigured (security risk).

Expect these follow-ups

• How do you prevent one tenant's query from consuming all warehouse resources and slowing others?
• What is the risk of using user attributes in Looker Embed for security vs database-level RLS?

As asked

A CFO needs a data warehouse that powers quarterly financial reports that must be reproducible: if an auditor asks what the Q3 revenue figure was as of October 1st, the system must be able to regenerate it exactly. How do you design this?

Sample answer outline

Point-in-time reproducibility requires either: immutable append-only raw tables (never update or delete source records) combined with SCD type 2 dimensions and a 'as-of-date' filter query pattern; or Snowflake's Time Travel feature (up to 90 days) for ad-hoc historical queries. For long-term audit, take a snapshot of the final reporting tables to a versioned storage location (S3 with object lock) at each close period. All transformations must be deterministic (no NOW(), CURRENT_TIMESTAMP in business logic). Version-control all SQL with git tags per reporting period.

Expect these follow-ups

• How does Snowflake Time Travel interact with MERGE statements when source rows are updated?
• What is the difference between a slowly-changing dimension snapshot and Snowflake Time Travel for this use case?