In BigQuery, a dbt events fact receives 6 TB per day, retains 24 months, and must refresh a dashboard every 15 minutes. How would you design the incremental model?

I would make the model partition-aware before optimizing any SQL expressions. - Partition by event_date and cluster by tenant_id and event_type so normal queries prune most of the 4 PB table. - Use event_id as the unique_key and a BigQuery merge with a 72-hour source lookback for late events. - Add an incremental predicate that limits target-side matching to the same three date partitions, rather than scanning all history. - Test event_id uniqueness per processed partition and compare daily row counts with the raw table before publishing.

A Snowflake orders table has 2 billion rows, 40 million new rows per day, and duplicate updates from Kafka for up to 14 days. What should the dbt incremental unique_key be, and how should the model handle duplicates?

I would define the key from the business grain, not from the Kafka delivery record. - Set unique_key to the composite of source_system and order_id because order_id is only unique inside one source. - Deduplicate each incremental input with row_number over that key, ordered by updated_at and Kafka offset descending. - Reject or quarantine null key components because Snowflake merge will not match nulls reliably as one business key. - Merge the winning row and test the composite key for uniqueness after every run covering the 14-day lookback.

A BigQuery dbt fact is 18 TB, each daily partition is about 600 GB, and the source republishes complete corrected partitions for the latest 2 days. Would you use merge or insert_overwrite?

I would use insert_overwrite because the source is authoritative at the whole-partition grain. - Build only the two corrected event_date partitions in a temporary relation and atomically replace those partitions. - Configure dbt's BigQuery insert_overwrite strategy with an explicit partition list when the run dates are known. - Keep merge for sparse row-level corrections, where rewriting 1.2 TB would cost more than matching a small change set. - Validate row counts and revenue totals for both dates before allowing the replacement to reach downstream models.

In Amazon Redshift, a dbt click model processes 4 billion rows per month; 99.7% arrive within 48 hours, while the remaining 0.3% can arrive up to 10 days late. How would you set the incremental lookback for a 1-hour SLA?

I would keep the hourly path short and handle the long tail on a separate bounded schedule. - Re-read 72 hours on each hourly run, which covers the measured 48-hour window with one day of margin. - Merge on click_id so rereading the same three days updates existing clicks instead of duplicating them. - Run a weekly 10-day backfill for the 0.3% tail, limited by event_date and split into daily batches. - Track lateness percentiles weekly and widen 72 hours only if the 99.7th percentile crosses that boundary.

A Databricks Delta pipeline writes 80 million payment events per hour, and an Airflow retry can replay any 2-hour window. How would you make the dbt incremental model idempotent?

I would make the replay produce the same target state rather than trying to prevent retries. - Use payment_event_id as the unique_key and Delta merge, with event_time restricted to the replayed two-hour window. - Deduplicate the batch by payment_event_id using source_sequence as the deterministic winner, not ingestion order. - Derive amounts, statuses, and timestamps only from source fields so rerun time never changes stored values. - Record batch_start, batch_end, input_count, and output_count in an audit table and accept a retry only when its counts reconcile.

A Snowflake dbt model processes 12 TB of telemetry per day, and one daily incremental run takes 100 minutes against a 90-minute SLA. How would you apply dbt microbatching?

I would split work by event_time so each batch has a fixed scan boundary and can be retried independently. - Configure dbt microbatch with event_time and a 1-hour batch_size, producing roughly 500 GB per batch. - Run up to 6 hourly batches in parallel after measuring warehouse concurrency instead of launching all 24 at once. - Set a 2-batch lookback to reprocess the previous 2 hours and merge on telemetry_id for late arrivals. - Persist batch status and retry only failed hours, then compare the 24 hourly counts with the landing total.

On Databricks, you have a 25 TB click fact with 90 billion rows and a 300 million-row customer dimension; analysts need daily campaign metrics in under 10 seconds. How would you model this in dbt?

I would keep the atomic fact for drill-down and serve dashboards from a smaller declared-grain aggregate. - Define fct_clicks at one row per click_id and dim_customer at one row per customer_id, with relationship tests on recent partitions. - Incrementally build agg_campaign_day at campaign_id, event_date, and customer_segment grain instead of joining 90 billion rows at query time. - Partition the fact and aggregate by event_date, and cluster the fact by campaign_id and customer_id for pruning. - Recompute the latest 3 days of the aggregate and reconcile clicks and spend against the atomic fact before promotion.

A BigQuery fact table is 400 TB, its daily partitions are 500 GB, and a tax-rule change requires recomputing exactly the previous 90 days. How would you run a bounded dbt backfill without a full refresh?

I would make the date range explicit and replace only the affected partitions in restartable chunks. - Pass backfill_start and backfill_end as dbt variables and reject any range longer than 90 days in the model logic. - Process 7 daily partitions per invocation with BigQuery insert_overwrite, so each chunk rewrites about 3.5 TB. - Store completed date ranges in an audit table and rerun only a failed chunk because every replacement is idempotent. - Reconcile row count, taxable_amount, and tax_amount per date, then run the normal incremental job from day 91 onward.

A PostgreSQL Debezium feed lands 5 million subscription changes per hour in Snowflake, including inserts, updates, and deletes, and the current-state table has 3 billion rows. How would you build the dbt incremental model?

I would model one current row per subscription_id and preserve delete state explicitly. - Deduplicate each batch by subscription_id, ordering by PostgreSQL LSN descending so the last database change wins. - Merge on subscription_id and write deleted_at plus is_deleted for delete records instead of relying on an untracked hard delete. - Limit the source to LSN values above the last successful watermark, with a 1-hour overlap for redelivery. - Test subscription_id uniqueness and compare insert, update, and delete operation counts with Debezium for every batch.

A BigQuery clickstream receives 14 TB per day, sessions close after 30 minutes of inactivity, and events can arrive 36 hours late. How would you incrementally build a dbt session fact without rescanning all history?

I would recompute only users whose recent events can change a session boundary. - Read a 48-hour event_time lookback and collect the distinct user_ids touched in that window. - Pull those users' prior boundary event, rebuild their affected sessions, and use a deterministic session_id from user_id plus session_start. - Replace only the event_date partitions containing rebuilt sessions, with a hard maximum of 3 daily partitions per run. - Reconcile event counts between rebuilt sessions and source events, and send arrivals older than 48 hours to a bounded repair job.

A dbt snapshot on Snowflake tracks a 4 billion-row subscription table. It runs every hour, but the MERGE now scans terabytes and misses its 45-minute SLA. How would you redesign it without losing history?

I would keep the SCD history, but stop presenting all 4 billion source rows to each snapshot run. - Build a candidate relation from the source CDC stream or from rows whose ingestion timestamp falls after a persisted high-water mark, with a safety overlap for delayed records. - Keep the dbt snapshot on that candidate set, and cluster the snapshot table on the business key plus dbt_valid_from so Snowflake can prune the target side of the MERGE. - Process bounded microbatches, for example 15 minutes or 5 million keys, and advance the high-water mark only after the snapshot batch succeeds. - Run a daily reconciliation over changed source partitions and track candidate count, inserted versions, updated versions, runtime, and bytes scanned.

An 80 million-row customer table in PostgreSQL feeds a dbt snapshot. updated_at is indexed, but a legacy sync sometimes changes status without touching it, while harmless metadata changes every few minutes. Would you use timestamp or check strategy?

I would not trust timestamp strategy until updated_at represents every business change we must retain. - First verify the contract with the source team and profile one week of PostgreSQL WAL or audit data for business changes where updated_at stayed unchanged. - If updated_at can be fixed and made monotonic, use timestamp strategy because the indexed column gives a cheap incremental filter at this volume. - Until then, use check strategy with an explicit list such as status, plan_id, owner_id, and billing_country, excluding noisy metadata rather than using check_cols: all. - Add a source-side change feed or ingestion watermark so dbt hashes only candidate rows, then test that sampled WAL changes produce a new snapshot version.

A Salesforce account snapshot in BigQuery contains 600 million versions, but the connector sends no delete events. Compliance needs deleted accounts marked within 24 hours. How would you capture hard deletes?

I would detect deletes from a validated complete key set, because absence from an incremental batch proves nothing. - Land a daily complete set of active Salesforce account IDs in BigQuery, partitioned by extraction date, and reject it if coverage drops beyond the agreed threshold. - Run a dedicated daily dbt snapshot against that complete current-state relation with hard_deletes set to new_record, so missing keys close their active versions and receive an explicit deletion record. - Keep prior versions with dbt_valid_from and dbt_valid_to, and expose dbt_is_deleted so consumers can distinguish deletion from an attribute update. - Reconcile active-key counts, detected deletes, and extract age before publishing, with the 24-hour requirement measured from the successful source extract.

In Snowflake, a 900 million-row customer SCD2 dimension receives backdated CRM corrections, sometimes six months late. Analysts are seeing overlapping versions and two current rows for one customer. How would you repair the model and prevent recurrence?

I would rebuild the affected customer timelines from ordered change events instead of patching individual end dates. - For each impacted business key, deduplicate events with a deterministic tie-breaker such as source_updated_at, ingestion_sequence, and event_id. - Derive valid_from from the business-effective timestamp and valid_to with LEAD(valid_from), using a half-open interval where valid_from is inclusive and valid_to is exclusive. - Replace all versions for only the impacted keys in one Snowflake transaction so readers never see a partially repaired timeline. - Add dbt tests for one current row per key, no overlapping intervals, valid_to greater than valid_from, and uniqueness of the dimension version key.

Kafka delivers 25 million purchase facts per day to Databricks, but up to 3 percent arrive before the matching customer dimension event and some dimension changes arrive two days late. How would you keep facts queryable and historically correct?

I would load the facts immediately with a controlled unresolved key, then restate only the affected facts when the dimension arrives. - Persist the customer business key and event_time in the Delta fact staging table, and assign surrogate key 0 for unresolved customers rather than dropping or guessing the match. - Maintain a retry table keyed by customer business key and affected event-date partitions, then resolve against the SCD2 interval valid at the fact's event_time. - Use a Databricks MERGE to update only unresolved or newly affected fact rows, preserving the original event timestamp and ingestion metadata. - Monitor unresolved count and age by source, alert before the two-day expectation is breached, and quarantine facts that still cannot resolve after the policy window.

A marketplace processes 20 million orders per day in BigQuery. Each order has multiple items, one shipping charge, order-level coupons, and partial refunds. What grain would you choose for the main sales fact and how would you stop totals from drifting?

I would declare the main sales fact at one row per order_item_id and model amounts according to the grain where they are naturally recorded. - Keep item quantity, item gross amount, and item tax on the item fact, with order_id as a degenerate dimension for grouping. - Store shipping, coupons, and payment amounts in order-grain facts unless the business approves a deterministic allocation rule such as net-item-value share. - Model refunds at one row per refund_line_id because one item can be refunded several times, then aggregate before joining to item grain. - Add dbt tests for unique order_item_id and reconciliation checks from item totals plus order-level facts back to the payment processor's daily control totals.

A Looker explore joins 120 million orders to order_items and payments in BigQuery. Orders average 4 items and 1.5 payment records, and revenue appears about 6 times too high. How would you diagnose and fix the fanout?

I would prove the multiplicative join first, then make every joined measure match the query grain. - Compare row counts and SUM(order_revenue) after each join, and calculate child rows per order to show the 4 by 1.5 multiplication. - Aggregate order_items to one row per order and payments to one row per order in separate dbt models before joining either result to orders. - In LookML, declare the real relationships and primary keys, but do not rely on symmetric aggregates to hide an incorrectly modeled SQL grain. - Add grain tests on each aggregate model and a reconciliation query that compares explore revenue with the trusted order-grain total.

A retailer has 30 million fulfillment orders with milestones that change for 45 days, plus inventory for 8,000 stores and 60,000 SKUs. In Snowflake, when would you use an accumulating snapshot versus a periodic snapshot?

I would use two facts because the processes answer different questions and have different update patterns. - Use an accumulating snapshot with one row per fulfillment_order_id and columns for ordered_at, packed_at, shipped_at, delivered_at, and current status, updating that row as milestones arrive. - Derive cycle-time metrics from those milestone columns and retain the source event fact separately when analysts need every transition or correction. - Use a periodic snapshot at one row per store_id, sku_id, and snapshot_date for daily on-hand, reserved, and available inventory, even when nothing changed. - Cluster and retain each table for its access pattern, and test uniqueness at order grain for the accumulating fact and store-SKU-date grain for the periodic fact.

A dbt project on BigQuery must rebuild a 700 million-row SCD2 product dimension in another environment. The current model uses an auto-increment ID, so every fact foreign key changes after rebuild. How would you design surrogate keys?

I would replace sequence-dependent identifiers with deterministic hashes and separate the product identity from each historical version. - Create a durable product key by hashing a canonical representation of the source system and product business key with dbt_utils.generate_surrogate_key. - Create a version key from the durable product key plus valid_from and a stable source event ID, so two SCD2 versions never share a key. - Normalize types, time zones, whitespace, case rules, and null handling before hashing, and never concatenate raw fields without separators or null markers. - Test uniqueness and non-nullness, keep the natural key columns for debugging, and compare sampled keys across production and the rebuilt environment.

You must reproduce a 12-month, 18 TB revenue mart exactly in Spark and dbt after business logic changes. Raw events are in Apache Iceberg, late files can appear for 14 days, and Kafka events occasionally share the same event timestamp. What would make the rebuild deterministic?

I would pin both the input state and the transformation version before recomputing any partition. - Record an Apache Iceberg snapshot ID for every source table and read those snapshots explicitly, so late files arriving during the run cannot change the input set. - Run the dbt project from a fixed Git SHA with pinned package versions, warehouse settings, time zone, currency-rate version, and other external reference data. - Deduplicate with a total order such as event_time, Kafka partition, offset, and event_id, and avoid nondeterministic functions or unordered first-value logic. - Write each month idempotently to a staging table, validate row counts and monetary checksums against a run manifest, then atomically replace the target partitions.

Analytics Engineer interview questions

100 real questions with model answers and explanations for Senior candidates.

See a Analytics Engineer resume example →

Practice with flashcards

Spaced repetition · Hunter Pass

Questions

bigquerydesigndbt

I would make the model partition-aware before optimizing any SQL expressions.

Partition by event_date and cluster by tenant_id and event_type so normal queries prune most of the 4 PB table.
Use event_id as the unique_key and a BigQuery merge with a 72-hour source lookback for late events.
Add an incremental predicate that limits target-side matching to the same three date partitions, rather than scanning all history.
Test event_id uniqueness per processed partition and compare daily row counts with the raw table before publishing.

Why interviewers ask this: This design bounds both source and target scans while preserving corrections for recently arrived events.

snowflakekafkadbt

I would define the key from the business grain, not from the Kafka delivery record.

Set unique_key to the composite of source_system and order_id because order_id is only unique inside one source.
Deduplicate each incremental input with row_number over that key, ordered by updated_at and Kafka offset descending.
Reject or quarantine null key components because Snowflake merge will not match nulls reliably as one business key.
Merge the winning row and test the composite key for uniqueness after every run covering the 14-day lookback.

Why interviewers ask this: A stable business key plus deterministic deduplication makes repeated Kafka deliveries converge to one order row.

partitioningbigquerydbt

I would use insert_overwrite because the source is authoritative at the whole-partition grain.

Build only the two corrected event_date partitions in a temporary relation and atomically replace those partitions.
Configure dbt's BigQuery insert_overwrite strategy with an explicit partition list when the run dates are known.
Keep merge for sparse row-level corrections, where rewriting 1.2 TB would cost more than matching a small change set.
Validate row counts and revenue totals for both dates before allowing the replacement to reach downstream models.

Why interviewers ask this: Replacing two complete partitions avoids a large key match and exactly mirrors the source's correction contract.

redshiftconcurrencydbt

I would keep the hourly path short and handle the long tail on a separate bounded schedule.

Re-read 72 hours on each hourly run, which covers the measured 48-hour window with one day of margin.
Merge on click_id so rereading the same three days updates existing clicks instead of duplicating them.
Run a weekly 10-day backfill for the 0.3% tail, limited by event_date and split into daily batches.
Track lateness percentiles weekly and widen 72 hours only if the 99.7th percentile crosses that boundary.

Why interviewers ask this: Separating the common arrival window from the rare tail protects the hourly SLA without silently dropping late clicks.

dbtairflowci-cd

I would make the replay produce the same target state rather than trying to prevent retries.

Use payment_event_id as the unique_key and Delta merge, with event_time restricted to the replayed two-hour window.
Deduplicate the batch by payment_event_id using source_sequence as the deterministic winner, not ingestion order.
Derive amounts, statuses, and timestamps only from source fields so rerun time never changes stored values.
Record batch_start, batch_end, input_count, and output_count in an audit table and accept a retry only when its counts reconcile.

Why interviewers ask this: Stable keys, deterministic winners, and deterministic transformations make a replay converge to the original result.

snowflakeconcurrencydbt

I would split work by event_time so each batch has a fixed scan boundary and can be retried independently.

Configure dbt microbatch with event_time and a 1-hour batch_size, producing roughly 500 GB per batch.
Run up to 6 hourly batches in parallel after measuring warehouse concurrency instead of launching all 24 at once.
Set a 2-batch lookback to reprocess the previous 2 hours and merge on telemetry_id for late arrivals.
Persist batch status and retry only failed hours, then compare the 24 hourly counts with the landing total.

Why interviewers ask this: Hourly microbatches bound each query, enable controlled parallelism, and reduce the cost of retrying failures.

monitoringdbt

I would keep the atomic fact for drill-down and serve dashboards from a smaller declared-grain aggregate.

Define fct_clicks at one row per click_id and dim_customer at one row per customer_id, with relationship tests on recent partitions.
Incrementally build agg_campaign_day at campaign_id, event_date, and customer_segment grain instead of joining 90 billion rows at query time.
Partition the fact and aggregate by event_date, and cluster the fact by campaign_id and customer_id for pruning.
Recompute the latest 3 days of the aggregate and reconcile clicks and spend against the atomic fact before promotion.

Why interviewers ask this: A tested atomic layer plus a narrow daily aggregate preserves detail while meeting the dashboard latency target.

partitioningmodelingbigquery

I would make the date range explicit and replace only the affected partitions in restartable chunks.

Pass backfill_start and backfill_end as dbt variables and reject any range longer than 90 days in the model logic.
Process 7 daily partitions per invocation with BigQuery insert_overwrite, so each chunk rewrites about 3.5 TB.
Store completed date ranges in an audit table and rerun only a failed chunk because every replacement is idempotent.
Reconcile row count, taxable_amount, and tax_amount per date, then run the normal incremental job from day 91 onward.

Why interviewers ask this: Explicit bounds and partition replacement cap scan cost while allowing safe restart from the last completed chunk.

postgressnowflakedbt

I would model one current row per subscription_id and preserve delete state explicitly.

Deduplicate each batch by subscription_id, ordering by PostgreSQL LSN descending so the last database change wins.
Merge on subscription_id and write deleted_at plus is_deleted for delete records instead of relying on an untracked hard delete.
Limit the source to LSN values above the last successful watermark, with a 1-hour overlap for redelivery.
Test subscription_id uniqueness and compare insert, update, and delete operation counts with Debezium for every batch.

Why interviewers ask this: Ordering by the database log position gives deterministic current state across duplicate and out-of-order deliveries.

bigquerysessionsdbt

I would recompute only users whose recent events can change a session boundary.

Read a 48-hour event_time lookback and collect the distinct user_ids touched in that window.
Pull those users' prior boundary event, rebuild their affected sessions, and use a deterministic session_id from user_id plus session_start.
Replace only the event_date partitions containing rebuilt sessions, with a hard maximum of 3 daily partitions per run.
Reconcile event counts between rebuilt sessions and source events, and send arrivals older than 48 hours to a bounded repair job.

Why interviewers ask this: User-scoped recomputation captures cross-window session boundaries while keeping the normal scan bounded to recent partitions.

snowflakesnapshotdbt

I would keep the SCD history, but stop presenting all 4 billion source rows to each snapshot run.

Build a candidate relation from the source CDC stream or from rows whose ingestion timestamp falls after a persisted high-water mark, with a safety overlap for delayed records.
Keep the dbt snapshot on that candidate set, and cluster the snapshot table on the business key plus dbt_valid_from so Snowflake can prune the target side of the MERGE.
Process bounded microbatches, for example 15 minutes or 5 million keys, and advance the high-water mark only after the snapshot batch succeeds.
Run a daily reconciliation over changed source partitions and track candidate count, inserted versions, updated versions, runtime, and bytes scanned.

Why interviewers ask this: This preserves version history while making hourly work proportional to changed keys rather than total table size.

indexespostgressnapshot

I would not trust timestamp strategy until updated_at represents every business change we must retain.

First verify the contract with the source team and profile one week of PostgreSQL WAL or audit data for business changes where updated_at stayed unchanged.
If updated_at can be fixed and made monotonic, use timestamp strategy because the indexed column gives a cheap incremental filter at this volume.
Until then, use check strategy with an explicit list such as status, plan_id, owner_id, and billing_country, excluding noisy metadata rather than using check_cols: all.
Add a source-side change feed or ingestion watermark so dbt hashes only candidate rows, then test that sampled WAL changes produce a new snapshot version.

Why interviewers ask this: The choice depends on change-capture correctness first and scan cost second.

soft-deletebigquerysnapshot

I would detect deletes from a validated complete key set, because absence from an incremental batch proves nothing.

Land a daily complete set of active Salesforce account IDs in BigQuery, partitioned by extraction date, and reject it if coverage drops beyond the agreed threshold.
Run a dedicated daily dbt snapshot against that complete current-state relation with hard_deletes set to new_record, so missing keys close their active versions and receive an explicit deletion record.
Keep prior versions with dbt_valid_from and dbt_valid_to, and expose dbt_is_deleted so consumers can distinguish deletion from an attribute update.
Reconcile active-key counts, detected deletes, and extract age before publishing, with the 24-hour requirement measured from the successful source extract.

Why interviewers ask this: A validated full-key reconciliation distinguishes real deletion from an incomplete connector load.

snowflake

I would rebuild the affected customer timelines from ordered change events instead of patching individual end dates.

For each impacted business key, deduplicate events with a deterministic tie-breaker such as source_updated_at, ingestion_sequence, and event_id.
Derive valid_from from the business-effective timestamp and valid_to with LEAD(valid_from), using a half-open interval where valid_from is inclusive and valid_to is exclusive.
Replace all versions for only the impacted keys in one Snowflake transaction so readers never see a partially repaired timeline.
Add dbt tests for one current row per key, no overlapping intervals, valid_to greater than valid_from, and uniqueness of the dimension version key.

Why interviewers ask this: Recomputing the whole key timeline makes late corrections and interval boundaries deterministic.

querieskafka

I would load the facts immediately with a controlled unresolved key, then restate only the affected facts when the dimension arrives.

Persist the customer business key and event_time in the Delta fact staging table, and assign surrogate key 0 for unresolved customers rather than dropping or guessing the match.
Maintain a retry table keyed by customer business key and affected event-date partitions, then resolve against the SCD2 interval valid at the fact's event_time.
Use a Databricks MERGE to update only unresolved or newly affected fact rows, preserving the original event timestamp and ingestion metadata.
Monitor unresolved count and age by source, alert before the two-day expectation is breached, and quarantine facts that still cannot resolve after the policy window.

Why interviewers ask this: The fact remains available on arrival and later receives the dimension version that was valid when the event occurred.

bigqueryconcurrencyiac

I would declare the main sales fact at one row per order_item_id and model amounts according to the grain where they are naturally recorded.

Keep item quantity, item gross amount, and item tax on the item fact, with order_id as a degenerate dimension for grouping.
Store shipping, coupons, and payment amounts in order-grain facts unless the business approves a deterministic allocation rule such as net-item-value share.
Model refunds at one row per refund_line_id because one item can be refunded several times, then aggregate before joining to item grain.
Add dbt tests for unique order_item_id and reconciliation checks from item totals plus order-level facts back to the payment processor's daily control totals.

Why interviewers ask this: An explicit grain prevents order-level amounts from being repeated across item rows.

joinsbigquerybi

I would prove the multiplicative join first, then make every joined measure match the query grain.

Compare row counts and SUM(order_revenue) after each join, and calculate child rows per order to show the 4 by 1.5 multiplication.
Aggregate order_items to one row per order and payments to one row per order in separate dbt models before joining either result to orders.
In LookML, declare the real relationships and primary keys, but do not rely on symmetric aggregates to hide an incorrectly modeled SQL grain.
Add grain tests on each aggregate model and a reconciliation query that compares explore revenue with the trusted order-grain total.

Why interviewers ask this: Pre-aggregating independent one-to-many children removes the cross-product that duplicates measures.

milestonessnapshotsnowflake

I would use two facts because the processes answer different questions and have different update patterns.

Use an accumulating snapshot with one row per fulfillment_order_id and columns for ordered_at, packed_at, shipped_at, delivered_at, and current status, updating that row as milestones arrive.
Derive cycle-time metrics from those milestone columns and retain the source event fact separately when analysts need every transition or correction.
Use a periodic snapshot at one row per store_id, sku_id, and snapshot_date for daily on-hand, reserved, and available inventory, even when nothing changed.
Cluster and retain each table for its access pattern, and test uniqueness at order grain for the accumulating fact and store-SKU-date grain for the periodic fact.

Why interviewers ask this: Accumulating snapshots follow a finite process, while periodic snapshots preserve state at regular observation times.

foreign-keysbigquerydesign

I would replace sequence-dependent identifiers with deterministic hashes and separate the product identity from each historical version.

Create a durable product key by hashing a canonical representation of the source system and product business key with dbt_utils.generate_surrogate_key.
Create a version key from the durable product key plus valid_from and a stable source event ID, so two SCD2 versions never share a key.
Normalize types, time zones, whitespace, case rules, and null handling before hashing, and never concatenate raw fields without separators or null markers.
Test uniqueness and non-nullness, keep the natural key columns for debugging, and compare sampled keys across production and the rebuilt environment.

Why interviewers ask this: Deterministic identity and version keys keep fact joins stable across environments and full rebuilds.

kafkadbt

I would pin both the input state and the transformation version before recomputing any partition.

Record an Apache Iceberg snapshot ID for every source table and read those snapshots explicitly, so late files arriving during the run cannot change the input set.
Run the dbt project from a fixed Git SHA with pinned package versions, warehouse settings, time zone, currency-rate version, and other external reference data.
Deduplicate with a total order such as event_time, Kafka partition, offset, and event_id, and avoid nondeterministic functions or unordered first-value logic.
Write each month idempotently to a staging table, validate row counts and monetary checksums against a run manifest, then atomically replace the target partitions.

Why interviewers ask this: Pinned inputs, code, ordering, and atomic publication make the same rebuild produce the same historical result.

Locked questions

21
You need to publish paid_orders from a 10 million row orders table. Each row is one order, order_id is unique, account_id is required, timestamps are UTC, and analysts need daily values by account. What exact semantic contract would you define?
22
An order_items model has 35 million rows and one row per item. A tag bridge has 80 million rows because an item can have up to 5 tags. Product asks for item revenue by tag while requiring the tag totals to equal overall revenue exactly. How would you model this in MetricFlow or another semantic layer without fanout?
semantic-layermonitoring
23
Define a 7-day session-to-purchase conversion metric for 12 customer accounts. Events arrive in UTC, each account has an IANA timezone, a session can have several purchases, and the dashboard groups cohorts by the session's local calendar day. What exact time and grain rules do you use?
cohortsmonitoringsessions
24
Finance uses a 4-4-5 fiscal calendar: weeks start Monday, fiscal year starts on the Monday nearest February 1, and some years contain week 53. The semantic layer must support fiscal week, quarter, and year for daily revenue from 2022 through 2032. How would you implement it?
semantic-layer
25
A daily snapshot contains balances for 2 million accounts in EUR, USD, and GBP. Finance asks for month-end total balance in USD. Summing all daily balances over a month is wrong, some accounts have no row on the final day, and FX rates are published once per day. How do you define the metric?
snapshotmonitoring
26
The existing active_accounts metric means at least one login in the trailing 30 days. Product now wants at least one report export in the trailing 28 days. The metric feeds 14 dashboards, 3 API clients, and two quarterly targets. How would you version the semantic definition?
monitoringapi
27
For April, Looker shows USD 8.42 million net revenue while Tableau shows USD 8.57 million, a 1.8% difference. Both are supposed to use the same semantic metric, but Tableau extracts nightly and Looker queries live. How would you establish and enforce parity?
monitoringbiqueries
28
Build weekly account retention for a B2B product. An account enters a cohort on its first successful data sync, is retained in a week if it runs at least one successful sync, events may arrive 72 hours late, and the chart shows weeks 0 through 12. What exact cohort and denominator rules do you define?
retentioncohorts
29
A semantic metric exposes ARR for 40,000 customer accounts across 4 regions. Sales reps may see only assigned accounts, regional managers may see their region, Finance may see all regions, and customer names must not appear in exported BI data for sales roles. How would you enforce access without duplicating the metric?
monitoring
30
You need month-end MRR by customer segment. mrr_snapshots has one row per subscription and month end, while customer_segment_history is SCD Type 2 with valid_from and valid_to. About 6% of customers change segment each year. How do you model the entity join so historical MRR is not rewritten?
joins
31
A BigQuery dashboard query scans 4.2 TB and costs about $21 per refresh, although users only view the last 7 days of a 2-year events table. How would you reduce bytes scanned and prove the change worked?
queriesbigquery
32
A 6 TB BigQuery table is already partitioned by day, but a tenant dashboard still scans 180 GB from one daily partition to return 2,000 rows. Filters are usually tenant_id plus event_name. What would you change?
partitioningbigquery
33
In Snowflake, a query filters 90 days of a 5-year, 14 TB orders table but scans 82% of its micro-partitions. The table receives out-of-order updates. How would you diagnose and improve pruning?
queriespartitioningsnowflake
34
A Snowflake dbt model takes 38 minutes on an X-Small warehouse and 7 minutes on a Large warehouse. The Large run uses 0.93 credits versus 0.63 credits on X-Small. Which size would you choose and what would you inspect first?
warehousesnowflakedbt
35
A Snowflake BI warehouse serves 900 short queries per day in 15 predictable usage windows. With AUTO_SUSPEND at 60 seconds, it resumes 140 times daily and each resume has a 60-second minimum charge. How would you tune it?
querieswarehousesnowflake
36
At 9 a.m., 40 dashboard queries queue behind a 20-minute Snowflake transformation on one Medium warehouse. Doubling to Large shortens the transformation but dashboard p95 remains 70 seconds. What would you change?
querieswarehousesnowflake
37
A Redshift fact table has 2 billion rows, and one tenant owns 42% of them. It is distributed by tenant_id, nodes show 4.8x row skew, and joins spill to disk. How would you redesign the table?
joinsmodelingredshift
38
A dbt incremental merge reads 40 GB of changed source data but scans a 12 TB destination table and runs for 52 minutes. Late updates arrive for up to 5 days. How would you reduce the work without losing corrections?
dbt
39
A reusable revenue model is referenced by 18 dashboards each day. As a view, each use scans about 900 GB; materializing it as a table requires a 120 GB hourly refresh. How would you choose the materialization?
40
Warehouse spend rose from $48,000 to $71,000 per month, but finance cannot tell which dbt domain or dashboard caused it. How would you build cost attribution that can explain at least 90% of spend?
warehousedbt
41
A dbt project has 2,400 models and 12,000 dependency edges, while the nightly build takes 75 minutes. How would you return the upstream and downstream blast radius of a model change in under two seconds without querying the warehouse?
querieswarehousedependencies
42
You have 500 dbt models, 18 BI exposures, and a finance dashboard that must be ready by 07:30 after a 70-minute transformation window. How would you use exposures to prioritize and verify that SLA?
dbt
43
A warehouse has 900 dbt models and 14,000 columns, and a proposed customer_id rename needs an impact report within 15 minutes. Since the dbt manifest mainly provides model-level dependencies, how would you build reliable column lineage?
schemawarehousedependencies
44
Your platform runs 1,200 dbt models inside Airflow plus 6,000 daily Spark and ingestion jobs, and the end-to-end data window is 90 minutes. How would you use OpenLineage to connect those systems without creating duplicate or ambiguous runs?
dbtairflowsystem-design
45
A dbt repository has 1,100 models, a three-billion-row event fact, 180 dimensions, and a 22-minute CI budget. How would you divide coverage between generic and singular tests without scanning the entire fact table on every pull request?
coveragegenericscode-review
46
A 260-line dbt model handles effective-dated currency rates, null currencies, and late-arriving payments, but each warehouse validation takes eight minutes. How would you create a unit-test suite that finishes in under 60 seconds?
hypothesis-testingvalidationfundamentals
47
You are replacing a full-refresh revenue model over 2.4 billion rows with an incremental version that must finish in 45 minutes. What reconciliation tests would you run when a row-by-row comparison is too expensive?
reacttesting
48
Production builds 1,800 dbt models in 70 minutes, but pull-request CI has a 12-minute limit. How would you use state selection and deferral while preventing CI from hiding dependency problems?
dependenciesbuilddbt
49
A contracted 60-column model feeds 36 dashboards by 08:00, and you need to rename customer_segment while adding a non-null segment_source across two releases. How would you evolve the schema without requiring every consumer to change at once?
schemafundamentals
50
Five dbt projects release independently each week, expose 40 shared models, and contain 300 cross-project references. How would you enforce compatibility when consumers cannot all upgrade in the same release?
dbt
51
At 10:05, twenty minutes after a dbt merge, finance reports that today's net revenue is 18.4% higher than the payment processor for 2.1 million daily transactions, and the 11:00 executive review depends on the dashboard. What do you do?
transactionsconcurrencydbt
52
A growth dashboard jumps from 420,000 to 687,000 weekly active users after a model change that joins 900 million events to a 14 million-row account history table, and campaign budget allocation starts in 90 minutes. How do you handle it?
soft-skillsjoins
53
At 08:30, operations says a dashboard still shows yesterday's 3.8 million orders even though the warehouse has 4.1 million, 600 managers open it each morning, and the freshness SLA is 07:00. What is your response?
warehouse
54
A release at 14:00 changes a shared dbt dimension, and by 14:25 twelve downstream models fail while the hourly revenue pipeline is due at 15:00 and supports a $9 million daily business. Do you roll back or fix forward?
rollbackdbtci-cd
55
At 16:00, a payment source sends a correction that moves €1.7 million of revenue from June 30 to July 1 across 82,000 records, after June board numbers were already exported at 09:00. How do you process it?
concurrency
56
At 02:10, an upstream deploy renames customer_id to customer_uuid in a 35 million-row daily source, breaking 27 dbt models; customer support reporting opens at 07:00 and misses its four-hour SLA if recovery takes more than 50 minutes. What do you do?
deploymentdbt
57
A six-month backfill of 4.6 billion events completes after 11 hours, but conversion rate falls from 4.8% to 3.1% and paid conversions are down 210,000; marketing presents the result in two hours. How do you respond?
conversionbackfill
58
After an incremental model deployment, daily subscription revenue is exactly 7.2% high for three days, 19 million rows have been processed, and invoices totaling $640,000 will be generated in 70 minutes. Investigation shows retries may have inserted duplicate subscription events. What do you do?
deploymentconcurrency
59
At 12:15, the CEO dashboard reports $12.6 million month-to-date revenue while finance reports $11.9 million, with a board call at 13:00 and 48,000 orders in the disputed $700,000. Both teams claim their query is correct. How do you lead the incident?
incidentsqueries
60
A 09:20 warehouse job overwrites 31 days of a customer status table with only the latest day, shrinking it from 780 million to 26 million rows; 34 dashboards and a churn export due at 10:30 are affected. How do you recover?
churnwarehouse
61
A nightly dbt run grew from 41 to 89 minutes after one merge and now misses a 60-minute SLA. How would you diagnose it and decide whether to roll back?
rollbackdbt
62
At 05:45, a 1,500-model dbt DAG is projected to finish in 82 minutes, but a payroll dashboard must be fresh by 06:30 and has 96 upstream models. Would you run only part of the DAG?
dbt
63
A Snowflake dbt build now takes 64 minutes: 17 minutes are queued, and one model spills 1.6 TB to remote storage while 9 a.m. dashboards share the same warehouse. What would you do?
warehousesnowflakedata-structures
64
An operator accidentally started a full refresh of 70 TB of dbt models; after 18 minutes, 23 of 140 relations have been replaced and projected cost is $4,800. How would you respond?
dbt
65
A dbt CI job using state and defer selects 8 models, but a manifest diff against production shows 312 affected descendants, and one missed model references a removed column. How would you investigate and contain this?
schemadbt
66
A critical dbt model fails 4 of 12 runs after 27 to 35 minutes, then usually passes on retry with no code change. How would you handle the flakiness without normalizing retries?
normalizationresiliencedbt
67
An orchestrator timed out after 29 minutes even though the dbt query committed, then its retry appended 14 million rows and inflated revenue by 2.3%. What would you do?
resilienceorchestrationqueries
68
A refactor touches 960 nodes in a 1,800-model dbt DAG, a representative build takes 52 minutes, and pull-request CI has a hard 15-minute budget. How would you make a release decision?
refactoringdbt
69
After a large DAG refactor, a 1,600-model production build grows from 52 to 76 minutes and breaches a 60-minute SLA even though total warehouse credits are unchanged. How would you recover and judge the refactor?
refactoringbuildwarehouse
70
You must backfill 120 days at 5 TB per day within 72 hours; benchmarking shows 15 TB per hour on an exclusive warehouse, but production consumes 65% of shared capacity. What plan would you approve?
capacitybackfillwarehouse
71
Finance reports 24.6 million dollars of quarterly revenue while Product reports 25.0 million, a 1.8% discrepancy, and the CFO needs one number in 90 minutes. What do you do?
72
Paid orders rise 7.4% overnight, but payment-provider settlements are flat; a new promotion join may have double counted orders with two coupons. How would you investigate and decide whether to roll back?
joinsrollback
73
Finance says ARR is 82.3 million dollars, Sales says 86.1 million, and both numbers are used in board materials due tomorrow. How would you resolve the conflict?
74
Growth reports 41% day-30 retention while Product reports 36% for the same January cohort of 120,000 users. How would you determine which cohort definition should govern planning?
retentioncohorts
75
Three product teams refuse a common active-user metric: their 28-day counts are 9.2 million, 8.5 million, and 7.9 million. How would you move the organization forward?
monitoring
76
A churn definition change lowers reported monthly churn from 4.7% to 4.1% and alters the previous 18 months. How would you decide whether to restate history?
churn
77
The live BI dashboard shows 3.42 million weekly orders, its 06:00 CSV extract shows 3.31 million, and an executive has already forwarded the CSV. How would you investigate and respond?
78
At 15:30, the CEO asks whether to stop a launch by 16:00 because conversion is 12.4% in one dashboard and 11.1% in another across 48,000 sessions. What do you recommend?
sessions
79
Finance excludes refunds issued after month-end while Product backdates them to the original order, creating a 2.6% gap in net revenue for March. How would you decide what each report should show?
80
A new semantic definition raises reported gross margin from 31.8% to 33.0% two days before earnings guidance, and 14 dashboards still use the old formula. How would you manage the decision and cutover?
css
81
Your monthly warehouse bill rose from $180,000 to $310,000 while data volume grew only 8%. How do you investigate, contain the increase, and prove the savings?
warehouse
82
A backfill launched by one analyst has spent $28,000 in three hours and is projected to run for 14 more hours. What do you do now, and how do you decide whether to resume it?
backfill
83
Snowflake consumption jumped from 9,000 to 16,000 credits per week although row volume and dashboard traffic are flat. Walk through your investigation and response.
zero-to-onesnowflake
84
A BigQuery dashboard scans 1.15 TB per refresh, refreshes 96 times daily, and now costs about $690 per day. How do you contain the spend and validate a durable fix?
bigqueryvalidation
85
Dashboard concurrency rose from 120 to 900 users at 9 a.m.; p95 latency increased from 6 to 42 seconds and autoscaling raised compute cost 70%. How do you restore service without locking in the higher bill?
lockinglatencyconcurrency
86
A product team disputes a $74,000 chargeback from a $110,000 shared warehouse bill, claiming its tagged queries account for only $29,000. How do you resolve the dispute and correct the model?
querieswarehouse
87
An observability vendor invoice increased from $240,000 to $390,000 at renewal because monitored assets grew from 18,000 to 31,000. How do you decide what to pay and prove value?
observabilitymonitoringprocurement
88
Seven petabytes of analytical data cost $161,000 per month to retain, but legal requires seven years for some records and analysts use only 12% of tables after 90 days. What retention decision do you make?
retention
89
Leadership requires a 25% reduction from a $400,000 quarterly warehouse bill, but finance freshness must stay within 30 minutes, dashboard p95 below 5 seconds, and availability at 99.5%. How do you choose the cuts?
warehouse
90
Your team claims $420,000 in annual warehouse savings after an optimization, but query traffic fell 18% and the provider cut prices 7% during the same period. How do you prove the engineering contribution?
querieswarehouseoptimization
91
You must migrate 180 production marts to a new namespace with less than 10 minutes of write downtime. How would you plan the cutover and rollback?
rollbackkubernetes
92
Your company is moving 80 TB, 420 models, and 600 dashboards to a new cloud warehouse in 12 weeks. How would you sequence the migration without freezing analytics?
migrationswarehouse
93
You need to move 650 dashboards and 1,800 users from one BI platform to another before a license expires in 90 days. What would you do?
94
You must migrate 900 scheduled analytics jobs to a new orchestrator while preserving SLAs, retries, and backfill state. How would you execute it?
orchestrationbackfill
95
A 2,400-node DAG takes 86 minutes against a 90-minute SLA, but you have only 3 engineers and 6 weeks to refactor it. How would you reduce risk and runtime?
refactoring
96
A data incident overstated weekly revenue by 8% for 36 hours and sales leaders no longer trust the dashboards. How would you restore business trust?
incidents
97
Two hours after a board meeting, you discover that the reported gross margin was 31.4%, not 34.1%. How would you correct the report and the underlying process?
cssconcurrency
98
Your dbt project has 1,300 models, and usage data suggests hundreds are obsolete. How would you deprecate them without breaking unknown consumers?
dbt
99
A source team will remove a customer key in 10 days, breaking a contract used by 14 teams and 75 downstream models. How would you handle the cross-organization break?
100
Ninety minutes into a warehouse migration cutover, 22% of certified metrics fail parity and dashboard latency has tripled. What do you do?
migrationswarehouselatency

Questions

1In BigQuery, a dbt events fact receives 6 TB per day, retains 24 months, and must refresh a dashboard every 15 minutes. How would you design the incremental model?

2A Snowflake orders table has 2 billion rows, 40 million new rows per day, and duplicate updates from Kafka for up to 14 days. What should the dbt incremental unique_key be, and how should the model handle duplicates?

3A BigQuery dbt fact is 18 TB, each daily partition is about 600 GB, and the source republishes complete corrected partitions for the latest 2 days. Would you use merge or insert_overwrite?

4In Amazon Redshift, a dbt click model processes 4 billion rows per month; 99.7% arrive within 48 hours, while the remaining 0.3% can arrive up to 10 days late. How would you set the incremental lookback for a 1-hour SLA?

5A Databricks Delta pipeline writes 80 million payment events per hour, and an Airflow retry can replay any 2-hour window. How would you make the dbt incremental model idempotent?

6A Snowflake dbt model processes 12 TB of telemetry per day, and one daily incremental run takes 100 minutes against a 90-minute SLA. How would you apply dbt microbatching?

7On Databricks, you have a 25 TB click fact with 90 billion rows and a 300 million-row customer dimension; analysts need daily campaign metrics in under 10 seconds. How would you model this in dbt?

8A BigQuery fact table is 400 TB, its daily partitions are 500 GB, and a tax-rule change requires recomputing exactly the previous 90 days. How would you run a bounded dbt backfill without a full refresh?

9A PostgreSQL Debezium feed lands 5 million subscription changes per hour in Snowflake, including inserts, updates, and deletes, and the current-state table has 3 billion rows. How would you build the dbt incremental model?

10A BigQuery clickstream receives 14 TB per day, sessions close after 30 minutes of inactivity, and events can arrive 36 hours late. How would you incrementally build a dbt session fact without rescanning all history?

11A dbt snapshot on Snowflake tracks a 4 billion-row subscription table. It runs every hour, but the MERGE now scans terabytes and misses its 45-minute SLA. How would you redesign it without losing history?

12An 80 million-row customer table in PostgreSQL feeds a dbt snapshot. updated_at is indexed, but a legacy sync sometimes changes status without touching it, while harmless metadata changes every few minutes. Would you use timestamp or check strategy?

13A Salesforce account snapshot in BigQuery contains 600 million versions, but the connector sends no delete events. Compliance needs deleted accounts marked within 24 hours. How would you capture hard deletes?

14In Snowflake, a 900 million-row customer SCD2 dimension receives backdated CRM corrections, sometimes six months late. Analysts are seeing overlapping versions and two current rows for one customer. How would you repair the model and prevent recurrence?

15Kafka delivers 25 million purchase facts per day to Databricks, but up to 3 percent arrive before the matching customer dimension event and some dimension changes arrive two days late. How would you keep facts queryable and historically correct?

16A marketplace processes 20 million orders per day in BigQuery. Each order has multiple items, one shipping charge, order-level coupons, and partial refunds. What grain would you choose for the main sales fact and how would you stop totals from drifting?

17A Looker explore joins 120 million orders to order_items and payments in BigQuery. Orders average 4 items and 1.5 payment records, and revenue appears about 6 times too high. How would you diagnose and fix the fanout?

18A retailer has 30 million fulfillment orders with milestones that change for 45 days, plus inventory for 8,000 stores and 60,000 SKUs. In Snowflake, when would you use an accumulating snapshot versus a periodic snapshot?

19A dbt project on BigQuery must rebuild a 700 million-row SCD2 product dimension in another environment. The current model uses an auto-increment ID, so every fact foreign key changes after rebuild. How would you design surrogate keys?

20You must reproduce a 12-month, 18 TB revenue mart exactly in Spark and dbt after business logic changes. Raw events are in Apache Iceberg, late files can appear for 14 days, and Kafka events occasionally share the same event timestamp. What would make the rebuild deterministic?

Locked questions