BI engineer at Cloudflare

As asked

Sales, finance, and product all have different revenue numbers in dashboards. How would you build a semantic layer that people trust?

Sample answer outline

Start by reconciling definitions with stakeholders: booked revenue, recognized revenue, net revenue, refunds, tax, and currency conversion are different metrics. Encode canonical definitions in a semantic layer or governed BI model rather than in dashboard-specific SQL. Add ownership, documentation, tests, certification status, and a change process so metric changes are deliberate. Keep enough flexibility for exploratory analysis without letting every team redefine company metrics. Strong BI engineers know this is partly technical and partly governance, because trust is lost through unclear ownership as much as bad SQL.

Expect these follow-ups

• How do you handle a board dashboard that uses an old definition?
• When should a metric be certified?
• How do you stop the semantic layer becoming a bottleneck?

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?