BI engineer role-specific interview questions

As asked

Walk me through when you would choose a star schema over a snowflake schema in a dimensional model. What are the real performance and maintenance tradeoffs?

Sample answer outline

A strong answer covers query performance (fewer joins in star schema), storage redundancy vs. normalized lookup tables, BI tool compatibility (Tableau and Power BI both optimize better against a star), and when normalization matters for slowly changing dimensions. Bonus: mention aggregate tables and how each schema interacts with columnar warehouse storage.

Expect these follow-ups

• If a dimension has millions of rows, does that change your answer?
• How do you handle many-to-many relationships between a fact and dimension?

As asked

Your sales dimension has a 'region' column. A rep moves from EMEA to APAC. Walk me through which SCD type you would pick and why, and how you would implement it in a modern warehouse like Snowflake or BigQuery.

Sample answer outline

Candidate should articulate SCD type 1 (overwrite, no history), type 2 (new row with effective/expiry dates and a current flag, creates a natural key plus surrogate key), and type 3 (previous-value column). For the rep-moves scenario, type 2 preserves historical reporting integrity. Implementation detail: use MERGE statements in Snowflake/BigQuery, or dbt snapshots with strategy unique_key + check_cols.

Expect these follow-ups

• How does type 2 affect the size of your fact table joins over time?
• What is an SCD type 6 and when have you seen it used?

As asked

You are building a fact table for e-commerce orders. A product manager wants both order-level and order-line-level metrics in the same report. How do you define the grain of your fact table, and what problems arise if you get it wrong?

Sample answer outline

A strong answer defines grain as 'one row per order line' (the finest granularity) and explains that mixing grains in a single fact causes double-counting (e.g., order-level shipping cost summed across lines). It should mention factless fact tables, degenerate dimensions (order_id on a line-level fact), and why you might build an aggregate fact at order level separately.

Expect these follow-ups

• What is a factless fact table and when would you use one here?
• How would you handle a header-level discount that applies to the whole order?

As asked

A dashboard using a live connection to Snowflake is running slowly for 200 concurrent users. A colleague suggests switching to a Tableau extract. Walk me through the tradeoffs and what you would actually do.

Sample answer outline

Extracts pre-aggregate into a columnar hyper file so dashboards load much faster and do not hit the warehouse on every interaction. Downsides are data staleness (refresh schedule) and storage on Tableau Server. Candidate should also mention query optimization on the Snowflake side (clustering keys, materialized views, caching at the warehouse layer) as an alternative before switching to extract, and consider incremental extract refreshes for large tables.

Expect these follow-ups

• How would you schedule extract refreshes to minimize staleness during business hours?
• What happens to row-level security when you switch from live to extract?

As asked

A colleague writes a running total using SUM(revenue) OVER (PARTITION BY region ORDER BY date). The numbers are wrong on days where two rows share the same date. Explain what is happening and how you fix it.

Sample answer outline

When rows share the same ORDER BY key, the window frame default (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) includes all peers in the running total, effectively counting them together. The fix is to use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW instead of RANGE, or add a tiebreaker column to the ORDER BY. Candidate should also explain the difference between RANGE and ROWS frame modes.

Expect these follow-ups

• What does the ROWS BETWEEN frame mode use to define 'current row' boundary vs RANGE?
• How would you write a 7-day rolling average that excludes the current day?

As asked

You build a 'Monthly Active Users' metric and publish it to a Tableau dashboard. Three months later you discover the marketing team, the product team, and the finance team all have different numbers for MAU. How did this happen and what do you do about it?

Sample answer outline

Metric drift happens when each team defines 'active' differently (login vs. purchase vs. API call), applies different date filters, or uses different user-deduplication logic. The fix is a single certified metric layer: in Looker this is a LookML measure; in dbt it is a semantic model metric with a doc block; in Tableau it is a Tableau-published certified data source. Strong answers mention governance processes like metric review boards, documentation, and alerting when upstream logic changes.

Expect these follow-ups

• How would you use dbt Semantic Layer or Looker metrics to prevent this going forward?
• What do you do when two teams refuse to adopt the shared definition?

As asked

A business-critical Tableau dashboard takes 45 seconds to load. Your manager wants it under 5 seconds. Walk me through exactly how you would diagnose the problem before making any changes.

Sample answer outline

Start with Tableau's built-in Performance Recording (Help > Settings > Performance > Start Recording) to see which queries are slow. Check query text in Snowflake's QUERY_HISTORY to spot full scans or inefficient joins. Look at number of marks rendered, number of queries fired per worksheet, and whether filters are context filters vs. regular filters. Also check whether the dashboard uses FIXED LOD expressions that generate extra queries.

Expect these follow-ups

• What is the difference between a context filter and a regular filter in Tableau, and why does it matter for performance?
• How would you reduce the number of SQL queries a single Tableau worksheet fires?

As asked

A revenue dashboard shows total revenue as NULL for certain regions. You trace it back to a SQL query with SUM(unit_price * quantity). Why might this return NULL and how do you fix it?

Sample answer outline

If either unit_price or quantity is NULL for any row, the multiplication is NULL for that row. SUM ignores NULLs, so if ALL rows in a partition are NULL the SUM is NULL. Fix: COALESCE(unit_price, 0) * COALESCE(quantity, 0), and investigate upstream why prices or quantities are missing. Also check for outer join nulls if the table was joined and the region has no matching rows.

Expect these follow-ups

• How does NULL behave in GROUP BY? Is it grouped with other NULLs or each in its own group?
• How would you distinguish 'no sales' from 'missing data' in the dashboard?

As asked

Your hourly ETL pipeline is 90 minutes late due to a source system issue. A weekly leadership report is due in 60 minutes and it requires yesterday's complete data. What do you do?

Sample answer outline

Immediately notify the leadership report consumers of the delay with an estimated resolution time. Investigate whether you can use prior-day data with a staleness note, or whether you can run the transformation on whatever partial data is available and note the completeness percentage. Do not silently send a report based on incomplete data without labeling it. If the source is partly available, check whether 95% completeness is good enough for the decisions being made from this report.

Expect these follow-ups

• How would you communicate the partial data scenario in the report itself?
• What safeguard would you add to detect this class of failure automatically next time?

As asked

Monday morning a daily active users metric is 300% higher than Friday. There have been no product releases over the weekend. Walk me through how you would investigate this before escalating.

Sample answer outline

First check whether it is a data problem (pipeline duplicate, ETL logic change, source system timestamp issue) before assuming it is a real business event. Check row counts in the raw table, look for duplicate event_ids, check whether a batch process ran multiple times. If the data looks clean, segment the spike by dimension (platform, country, device) to isolate where it comes from. Check infrastructure logs for an event that loaded historical data incorrectly. Only after ruling out data issues escalate to the business.

Expect these follow-ups

• How would you communicate your findings to stakeholders before you have a definitive answer?
• What monitoring would have caught this anomaly before you arrived Monday morning?

As asked

Regional sales managers should only see their own region's deals, while executives can see everything. How would you implement and test this in BI?

Sample answer outline

Model entitlements explicitly: user or group to permitted region, with executive groups mapped to all regions. Enforce row-level security in the BI semantic layer or warehouse policies, preferably close to the data if multiple tools consume it. Test with impersonation, fixture users, and negative cases so a dashboard change cannot bypass the rule. Handle edge cases like managers covering multiple regions, historical territory changes, and shared accounts. Strong answers consider privacy and auditability, not just a filter on a dashboard.

Expect these follow-ups

• Where would you enforce RLS if both Looker and ad-hoc SQL users need access?
• How do you test that a user cannot bypass the filter through drill-downs?
• What happens when a salesperson moves region mid-quarter?

As asked

In LookML, what is the difference between a native derived table, a persistent derived table, and a SQL derived table? When would you pick each, and what are the gotchas with PDTs in production?

Sample answer outline

SQL derived tables are raw SQL sub-selects materialized at query time. PDTs are materialized into a scratch schema on a trigger or cron, improving query speed at the cost of build time and schema management. Native derived tables use LookML view syntax and can be non-persistent or persistent. Gotchas: PDT rebuild failures can silently serve stale data; cascading PDTs can lock the scratch schema; they need a dedicated connection with schema write access.

Expect these follow-ups

• How do you handle a cascading PDT where one depends on another and the first rebuild fails?
• How would you write a LookML test to validate a PDT's output?

As asked

A finance team wants a Power BI report on a 500-million-row transaction table in Azure Synapse that must always show data as of 15 minutes ago. What mode do you use and what do you configure to make it work?

Sample answer outline

DirectQuery avoids the 1GB import limit and gives near-real-time data at the cost of query performance. For 500M rows the candidate should mention aggregation tables (dual mode) in Power BI to pre-aggregate the bulk of queries while DirectQuery handles drill-through detail. They should also call out turning on query reduction options, limiting visuals per page, and using dedicated SQL pools in Synapse with proper distribution keys.

Expect these follow-ups

• What is composite mode and how does it combine Import and DirectQuery?
• How do you prevent a slow DirectQuery from timing out and showing a blank visual?

As asked

A Tableau dashboard queries a 2-billion-row event table in Snowflake, always filtering on event_date and event_type. Query performance is inconsistent. Walk me through Snowflake's micro-partition pruning and whether a clustering key would help here.

Sample answer outline

Snowflake stores data in micro-partitions containing 50MB to 500MB of uncompressed data each. If data was loaded in event_date order, natural clustering may be sufficient. A clustering key on (event_date, event_type) forces re-ordering and incurs ongoing maintenance credits, but can dramatically reduce the number of partitions scanned when filters match those columns. The candidate should mention SYSTEM$CLUSTERING_INFORMATION to assess overlap and average depth before paying for reclustering, and note that the benefit is only worth the cost when partition overlap is high and queries filter on those columns frequently.

Expect these follow-ups

• How does clustering differ from a traditional database index?
• When would you NOT use a clustering key despite repeated filter on a column?

As asked

You have a sales dashboard in Tableau Server where each regional manager should only see their region's data. Describe how you would implement row-level security, and the tradeoffs between doing it in Tableau vs. in the warehouse vs. in dbt.

Sample answer outline

Tableau user filters (data source or calculated field using USERNAME()) are easy to set up but brittle and bypass when extracts are downloaded. Warehouse-level RLS (Snowflake policies, BigQuery row access policies) is more secure and works for any tool connecting to the warehouse. dbt can generate user-entitlement tables that feed the warehouse policy. Candidate should note that extract-based dashboards can leak data if not combined with Tableau permissions and encrypted extracts.

Expect these follow-ups

• How does your RLS approach change when you switch from live to extract in Tableau?
• How would you test that a user cannot see rows they should not see?

As asked

You are about to push a new dbt model that feeds a revenue dashboard used by the CFO. What tests do you write before going to production, and how do you structure them?

Sample answer outline

At minimum: not_null and unique on primary keys, accepted_values for status columns, referential integrity (relationships test) between fact and dimension surrogate keys. Add custom generic tests for non-negative revenue, no future dates, and row-count assertions. Use dbt's elementary or re_data package for anomaly detection on key metrics. Separate test severity: warn on row count drops, error on null primary keys.

Expect these follow-ups

• How do you prevent a failing dbt test from silently continuing past it in CI?
• What is a dbt source freshness test and where would you add it here?

As asked

You have a BigQuery event table partitioned by event_date. A dashboard always filters on both event_date and user_country. What does partitioning buy you vs. clustering on user_country, and can you use both at once?

Sample answer outline

Partitioning prunes entire partitions from the query (affecting bytes billed directly). Clustering sorts data within partitions by the clustered columns, so BigQuery can skip blocks within a partition. You can use both: partition by event_date and cluster by user_country. Partitioning gives hard billing guarantees, clustering gives additional scan reduction but is best-effort (no hard cost guarantee). Up to 4 clustering columns are supported.

Expect these follow-ups

• What happens to clustering efficiency when the table is very small?
• How do you check whether BigQuery actually used partition pruning on a query?

As asked

A Looker explore joins orders to order_items (one-to-many). A count of orders returns the correct number but a SUM of order revenue is inflated. What is happening and how do you fix it in LookML?

Sample answer outline

The fan-out happens because joining one order to N items duplicates the order-level revenue column N times before aggregation. Fix options: use type: sum_distinct with sql_distinct_key set to the order primary key; or split into separate explores and use merged results in Looker; or push the aggregation into a derived table first. The candidate should know that Looker's symmetric aggregates (enabled by default on supported databases) handle this automatically using HyperLogLog-style distinct sums.

Expect these follow-ups

• What is a symmetric aggregate and which databases support it in Looker?
• How would you test that the SUM is correct after your fix?

As asked

In Power BI, a measure for prior-year revenue using CALCULATE(SUM(Revenue), SAMEPERIODLASTYEAR(Date[Date])) returns blank when users filter to a single month. Explain why and how to fix it.

Sample answer outline

SAMEPERIODLASTYEAR returns dates from the prior year that correspond to the current filter context. If the date table does not include prior-year dates or is not marked as a date table, it returns blank. Fix: ensure the date table covers the full range including prior years, is marked as a date table in Power BI, and has no gaps. Also check that CALCULATE is removing the existing date filter before applying the time-intelligence filter.

Expect these follow-ups

• What is the difference between CALCULATE with a filter and CALCULATETABLE?
• How does ALL() differ from REMOVEFILTERS() in a CALCULATE context?

As asked

Snowflake costs doubled after you added 10 new Tableau dashboards. Walk me through the approach you would take to identify the expensive queries and reduce costs without breaking the dashboards.

Sample answer outline

Start with ACCOUNT_USAGE.QUERY_HISTORY filtered to Tableau-sourced queries (client application = Tableau). Identify queries by credits used and bytes scanned. Common culprits: full table scans from missing filters, Tableau auto-generating UNION ALL queries for blending, and unnecessary live connections that should be extracts. Remedies: materialize expensive base queries as views or materialized views, switch to extracts, right-size the Snowflake warehouse (auto-suspend, smaller T-shirt size for BI).

Expect these follow-ups

• How would you attribute Snowflake costs back to individual dashboards or teams?
• What is a Snowflake resource monitor and how would you use it here?

As asked

You have a 500GB session events table that grows by 2GB per day. You are modeling it in dbt. Why would you use an incremental model, and what pitfalls exist with the default merge strategy on Snowflake?

Sample answer outline

Incremental models rebuild only new or updated rows rather than the full table, cutting runtime and cost. The default merge strategy on Snowflake uses a MERGE statement on unique_key, which is safe but slow at scale because it scans both the existing table and the new batch. A better approach for append-only data is strategy: append; for late-arriving data, use delete+insert to replace a date partition. Always include a lookback buffer (e.g., last 3 days) to catch late events.

Expect these follow-ups

• What happens if you run a full-refresh on an incremental model and the underlying data has changed in the past?
• How do you handle schema changes (new columns) in an incremental model?

As asked

You need a metric showing each customer's first purchase date alongside their current order details in a Tableau view. Walk me through whether you use FIXED, INCLUDE, or EXCLUDE, and what SQL Tableau generates under the hood.

Sample answer outline

FIXED computes the expression independent of view dimensions, so {FIXED [customer_id] : MIN([order_date])} always gives the customer's first order regardless of any filters except context filters. INCLUDE adds dimensions to the current view level (more granular), EXCLUDE removes them (less granular). Tableau generates a nested query with the LOD as a sub-select or a temp table. Candidate should note that FIXED respects context filters but ignores dimension filters unless a context filter is used.

Expect these follow-ups

• Why would a FIXED LOD expression perform differently on a live connection vs. an extract?
• How do you prevent FIXED LODs from bypassing a date filter a user applies?

As asked

Your company has a sales fact table and a support tickets fact table, both with a customer_id. How do you implement a conformed customer dimension so a single Tableau or Looker dashboard can report on both without double-counting customers?

Sample answer outline

A conformed dimension shares the same definition, surrogate key, and grain across all fact tables that reference it. In practice this means a single dim_customers table loaded by one ETL process, with both fact tables using the same surrogate customer_key. In Tableau, use data blending or a cross-database join anchored on customer_key. In Looker, define the customer dimension in one view and reference it in multiple explores. The answer should explain why shared grain prevents double-counting vs. two separate customer definitions.

Expect these follow-ups

• What happens if the sales fact uses a different customer identifier than the support fact?
• How do you handle a customer that exists in one fact but not the other?

As asked

A Looker explore is hammering your BigQuery warehouse because 50 users are running the same explore with different filters every few minutes. How does Looker's caching work, and what would you configure to reduce warehouse queries without serving stale data to users?

Sample answer outline

Looker caches query results by the exact SQL fingerprint. A datagroup lets you tie cache invalidation to a cron expression or a max_cache_age plus a trigger SQL query that detects when the underlying data has changed. For this scenario, define a datagroup triggered by a SQL check on the warehouse's INFORMATION_SCHEMA.PARTITIONS (for BigQuery) so the cache is invalidated only when new data lands, not on a fixed schedule. Also consider limiting the explore's time dimension to reduce unique filter combinations.

Expect these follow-ups

• What happens to a cached result if a user changes only the date filter by one day?
• How do you apply a different caching policy to different explores in LookML?

As asked

A data engineer argues you should just use the source system's customer_id as the primary key for your customer dimension. Why do dimensional modeling best practices recommend surrogate keys instead, and when does using a natural key actually make sense?

Sample answer outline

Surrogate keys are system-generated integers or hashes that are stable across source system changes, support SCD type 2 (multiple rows per natural key with different time ranges), and avoid cross-source key collisions. Natural keys are fine for dimensions that do not change (e.g., date dimension using YYYYMMDD as the key) or for single-source systems with guaranteed stability. The candidate should mention that joining on integer surrogate keys is faster than string natural keys in columnar warehouses.

Expect these follow-ups

• How do you generate surrogate keys in dbt?
• What is a junk dimension and does it use natural or surrogate keys?