Question 1

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?

Accepted Answer

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.

Question 2

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.

Accepted Answer

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.

Question 3

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?

Accepted Answer

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.

Question 4

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.

Accepted Answer

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.

Question 5

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.

Accepted Answer

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.

Question 6

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?

Accepted Answer

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.

Question 7

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.

Accepted Answer

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.

Question 8

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?

Accepted Answer

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.

Question 9

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?

Accepted Answer

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.

Question 10

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.

Accepted Answer

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.

Question 11

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?

Accepted Answer

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.

Question 12

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?

Accepted Answer

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.

Question 13

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?

Accepted Answer

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.

Question 14

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.

Accepted Answer

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.

Question 15

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.

Accepted Answer

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.

Question 16

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?

Accepted Answer

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.

Question 17

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?

Accepted Answer

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.

Question 18

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?

Accepted Answer

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.

Question 19

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.

Accepted Answer

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.

Question 20

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.

Accepted Answer

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).

Question 21

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?

Accepted Answer

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.

Question 22

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.

Accepted Answer

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.

Question 23

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?

Accepted Answer

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.

Question 24

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?

Accepted Answer

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.

Question 25

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?

Accepted Answer

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.

Question 26

Stakeholders expect the daily sales dashboard to show yesterday's data by 8am. How would you design monitoring to catch a pipeline delay before users notice, and what do you alert on?

Accepted Answer

Add a watermark column (max(updated_at) or max(event_date)) to the final table and monitor it with a scheduled check that runs at 7:45am. Alert if the watermark is more than X hours old. Use dbt source freshness to define freshness thresholds per source. Combine with a row-count anomaly check so a pipeline that succeeds but loads zero rows also fires an alert. Notify via Slack or PagerDuty with context on which model failed.

Question 27

In Power BI, how do you implement dynamic row-level security where each manager sees only their own team's data, using a mapping table rather than one static role per manager?

Accepted Answer

Create a Manager_Mapping table (manager_email, team_id). In the RLS role DAX filter on the Employees or fact table, use USERPRINCIPALNAME() to match the current viewer's email against the mapping table via a relationship. This is called dynamic RLS: one role handles all users. Test it by switching roles in Power BI Desktop to impersonate users. Publish to Power BI Service and assign users to the role in dataset settings.

Question 28

A query against a 100M-row Postgres table takes 3 minutes. You run EXPLAIN ANALYZE and see a sequential scan on a table with a WHERE clause on order_date. What do you look for in the plan and what do you do?

Accepted Answer

In the plan, look for Seq Scan vs Index Scan, and check the estimated vs actual row count (a large discrepancy indicates stale statistics). For a date filter with high cardinality, an index on order_date should allow an Index Scan. If it is still doing a Seq Scan, check if the query uses a function on the column (DATE(order_date) = ... disables index use; use order_date >= ... AND order_date < ... instead). Also check whether the planner thinks a Seq Scan is cheaper due to a low selectivity estimate, and run ANALYZE to refresh statistics.

Question 29

It is 10pm. The board presentation is at 9am tomorrow. You find a data error that affects the headline revenue figure in the CEO's deck. What do you do?

Accepted Answer

Contact the CEO and CFO immediately regardless of the hour (this is a material error). Provide: the wrong number, the correct number, the magnitude of the difference, and a one-line explanation of the cause. Give a realistic ETA to fix it. If the fix takes 20 minutes, do it now. If it takes 4 hours, discuss whether to update the deck with the correct number and a footnote or present the error in the meeting with live correction. Never let the wrong number go into a board room knowingly.

Question 30

A VP of Sales insists their dashboard uses a dual-axis chart comparing revenue (left axis, millions) and number of deals closed (right axis, small numbers) and wants both axes to start at zero. The resulting chart visually implies the correlation is stronger than it is. What do you do?

Accepted Answer

Do not build a chart that misleads, even if the stakeholder asks. Explain the specific issue: dual-axis charts are visually manipulable because scaling one axis changes the apparent correlation. Propose alternatives: a scatter plot showing the correlation directly, or two separate charts side by side. If the VP still insists, escalate to a data governance owner or manager and document your objection. Do not silently build something you know is misleading.

Questions

Star schema vs snowflake schema tradeoffsRole-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

SCD type 1 vs 2 vs 3: when each makes senseRole-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

Defining and defending a fact table grainRole-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

Tableau extract vs live connection: performance and freshnessRole-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

SQL window function internals and ordering pitfallsRole-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

Defining a metric and preventing metric driftRole-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

Debugging a slow Tableau or Power BI dashboardRole-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

NULL handling in SQL aggregations and joinsRole-specificeasyVery common

As asked

Sample answer outline

Expect these follow-ups

Pipeline is late and a time-sensitive report is due in 1 hourRole-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

A KPI suddenly spikes 300% and no one knows whyRole-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

Design row-level security for regional reportingRole-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

Looker derived tables: native SQL vs PDT vs NDTRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

Power BI DirectQuery vs Import mode decisionRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

Snowflake clustering keys and when they helpRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

Implementing row-level security across Tableau and the warehouseRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

dbt testing strategy for a production data modelRole-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

BigQuery partitioning vs clustering: choosing the right oneRole-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

Looker fan-out problem with one-to-many joinsRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

DAX CALCULATE function and filter context manipulationRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

Controlling warehouse query costs for BI workloadsRole-specificmediumCommon

As asked

Sample answer outline