BI engineer domain knowledge interview questions

As asked

Explain the difference between an OLTP and an OLAP system. Why should BI workloads generally not run directly against OLTP databases?

Sample answer outline

OLTP is optimized for high-frequency, low-latency reads and writes on individual rows (row-store). OLAP is optimized for bulk analytical reads across many rows, typically using columnar storage and compression. Running BI queries on OLTP databases adds read load that competes with transactional writes, can lock rows, does not benefit from columnar compression, and lacks the pre-aggregated dimensional model. Columnar warehouses (Snowflake, BigQuery, Redshift) compress and prune columns not in the query, making analytical queries orders of magnitude faster.

Expect these follow-ups

• What is a columnar storage format and why does it help BI queries?
• When might you query an OLTP database directly for BI (give a legitimate use case)?

As asked

What is the difference between an additive, semi-additive, and non-additive fact? Give a real example of each in a retail context.

Sample answer outline

Additive: revenue can be summed across all dimensions (time, product, region). Semi-additive: inventory_balance can be summed across products but not across time (you cannot add Monday's balance to Tuesday's balance to get a meaningful number; use AVG or point-in-time). Non-additive: profit margin percentage cannot be summed at all; you must re-derive it as SUM(profit)/SUM(revenue). Strong answers include at least one example of each and the correct aggregation treatment in a BI tool.

Expect these follow-ups

• How do you handle a semi-additive measure in a Tableau time series chart?
• What is the correct way to aggregate a ratio metric like conversion rate across segments?

As asked

Why does dbt use ref('model_name') instead of just writing the schema.table name directly? What does ref() enable that a hardcoded table name does not?

Sample answer outline

ref() resolves to the correct schema at compile time based on the environment (dev vs production), enabling environment isolation without code changes. It also builds a dependency graph so dbt knows execution order and can run affected models only on change (--select state:modified+). It enables documentation of lineage (shown in the dbt docs DAG) and lineage-based testing. Hardcoded table names break environment isolation and make the DAG invisible to dbt.

Expect these follow-ups

• What is the difference between ref() and source() in dbt?
• How does the --defer flag use the dependency graph that ref() creates?

As asked

What is the difference between ETL and ELT, and why have most modern data teams shifted toward ELT with tools like Fivetran plus dbt?

Sample answer outline

ETL transforms data before loading (often in a middleware tool like Informatica), which limits flexibility and requires rebuilding logic when business rules change. ELT loads raw data first into the warehouse, then transforms using SQL (dbt). ELT is preferred because: warehouses are cheap and scalable so storing raw data is not expensive, SQL is easier to version-control and test than visual ETL flows, transformations can be rerun without re-extracting, and raw data is available for ad-hoc analysis. The tradeoff is that raw sensitive data lands in the warehouse, requiring access controls.

Expect these follow-ups

• What is a situation where ETL still makes sense over ELT?
• How do you handle PII in an ELT architecture where raw data lands in the warehouse?

As asked

In dbt, what is Jinja and how does it execute relative to the SQL query that gets sent to the warehouse? Give an example of where Jinja solves a problem that pure SQL cannot.

Sample answer outline

Jinja is a Python-based templating language that dbt processes at compile time, before any SQL reaches the warehouse. It renders macros, ref() and source() calls, config blocks, and if/for logic into plain SQL. Example: a Jinja for loop generating UNION ALL across 12 monthly partition tables without repeating code. Pure SQL has no variables or loops at the meta-SQL level; Jinja fills that gap. Candidate should know the distinction between compile-time Jinja and run-time SQL.

Expect these follow-ups

• How would you use a Jinja if block to write a model that behaves differently in dev vs production?
• What is the difference between a dbt macro and a dbt model?

As asked

A product manager asks you what a p-value of 0.03 means for an A/B test result in a dashboard you built. How do you explain it without using statistical jargon?

Sample answer outline

A p-value of 0.03 means: if there were no real difference between the control and treatment, there is a 3% chance of seeing a difference this large or larger just by random chance. It does NOT mean the variant is 97% likely to win in production. The candidate should mention practical significance vs statistical significance, the importance of effect size alongside p-value, and that a low p-value from a very large sample can still represent a trivially small business impact.

Expect these follow-ups

• What would you tell a PM who wants to stop the test early because the p-value just hit 0.05?
• What is the difference between a one-tailed and two-tailed test in the A/B testing context?

As asked

A colleague places 5 different measures on a Tableau view and the chart becomes a mess of overlapping circles. Explain how Tableau's marks card works and how you would redesign this to be readable.

Sample answer outline

The marks card controls how data is encoded: shape, color, size, label, detail, tooltip. Placing multiple measures on the view creates a measure names/values structure that can explode mark counts. For 5 measures, consider: a small multiples layout (separate charts per measure), a bar chart with a single measure and color for category, or a line chart over time with one measure per color. The candidate should explain the difference between encoding dimensions as color/shape (categorical) vs continuous measures as color gradients or size.

Expect these follow-ups

• When would you use size encoding vs color encoding for a measure?
• What is a dual-axis chart in Tableau and what are the visualization risks?

As asked

Your Tableau dashboards are slow because they share a single Snowflake XS virtual warehouse with engineering's dbt jobs. Explain how Snowflake virtual warehouses work and what you would change.

Sample answer outline

A Snowflake virtual warehouse is an independent compute cluster. Multiple warehouses share the same storage layer but have completely isolated compute, so dbt jobs on one warehouse cannot starve dashboard queries on another. The fix is to dedicate a separate BI warehouse for Tableau so dbt workloads do not compete for the same slots. Warehouse size controls the number of servers in the cluster and therefore the memory and CPU available per query, which matters most for large, complex queries. For high concurrency with many smaller queries, enabling multi-cluster warehouses (available on Enterprise tier) adds additional clusters automatically during peak demand rather than queueing queries. Auto-suspend when idle reduces cost. The candidate should also mention Snowflake's result cache, which serves identical queries instantly without charging compute credits.

Expect these follow-ups

• What is Snowflake's concurrency scaling feature and when does it kick in?
• How do you choose between sizing up (larger T-shirt) vs enabling multi-cluster for concurrency?

As asked

In Looker, what is the difference between a view and an explore? If you define a view with 20 dimensions, does every user automatically see all 20 in every explore?

Sample answer outline

A view defines the dimensions and measures available from a table or derived table. An explore is the entry point for users: it specifies which views are joined, in what order, and with what join type. A view can be included in multiple explores. You can hide specific fields from an explore using the fields parameter on the join or explore definition, so the same view can expose 20 fields in a developer explore and only 5 in an executive explore. Users only see fields exposed by the explore they are using.

Expect these follow-ups

• How do you create a hidden view that provides a join but no fields to the end user?
• What is the difference between a view-level field: hidden: yes and an explore-level fields: [field1, field2] restriction?

As asked

What is Data Vault 2.0 and in what scenarios is it a better choice than Kimball dimensional modeling for a data warehouse?

Sample answer outline

Data Vault separates raw historical data (hubs, links, satellites) from business rules, making it more agile when source systems change and when auditability is paramount. Kimball prioritizes query performance for known business questions. Data Vault is better when the source landscape is large and volatile, regulatory requirements demand full audit history, or multiple source systems define the same business concept differently and reconciliation is deferred. The tradeoff is higher query complexity (more joins) and slower initial development compared to a star schema.

Expect these follow-ups

• Can you use Data Vault and Kimball in the same warehouse?
• How does dbt fit into a Data Vault architecture?