BI engineer technical fundamentals interview questions

As asked

You run a LEFT JOIN between a fact table and a dimension table but your row count in the result is higher than the fact table row count. What are the possible causes and how do you diagnose it?

Sample answer outline

The dimension table has duplicate rows on the join key (more than one row per surrogate or natural key). A LEFT JOIN on a key with duplicates fans out the fact rows. Diagnose by running SELECT join_key, COUNT(*) FROM dim_table GROUP BY join_key HAVING COUNT(*) > 1. Fix: deduplicate the dimension before joining (use a DISTINCT or ROW_NUMBER to keep one row per key). This is a common error when joining on natural keys in SCD type 2 dimensions where multiple historical rows share the same natural key.

Expect these follow-ups

• How does this problem manifest differently in an INNER JOIN vs a LEFT JOIN?
• What SQL pattern would you use to ensure the dimension has exactly one row per key before joining?

As asked

A dashboard filters on a VARCHAR column user_uuid with 50 million distinct values. A colleague suggests adding a B-tree index. Will that help? What would actually help in a columnar warehouse like Snowflake?

Sample answer outline

B-tree indexes are a row-store concept. Snowflake and similar columnar warehouses do not use traditional indexes. For high-cardinality string columns in Snowflake, the relevant optimization is a clustering key if queries frequently filter on user_uuid. For point lookups on specific UUIDs, Snowflake's metadata-based pruning helps somewhat but columnar stores are inherently less efficient for single-row lookups. Better architecture: if this is a user-specific filter, cache results client-side or pre-aggregate per user in the BI layer.

Expect these follow-ups

• When would you use a hash join vs a sort-merge join in a columnar warehouse query?
• What Snowflake feature provides the nearest equivalent to a traditional index for equality lookups?