4) Technical and professional questions (where offers are won)
This is the part most candidates under-prepare. US interviewers won’t just ask “do you know SQL?” They’ll ask how you prevent silent metric drift, how you model subscriptions, and how you’d fix a warehouse bill that doubled.
Q: How do you decide the grain of a fact table, and how do you prevent double-counting?
Why they ask it: Grain mistakes are the #1 cause of broken metrics.
Answer framework: “Grain–Keys–Tests”: state grain, define primary key, add tests and usage guidance.
Example answer: “I start by writing the grain in plain English—like ‘one row per order line item’—and I make the primary key explicit. Then I model dimensions to join at that grain, and I avoid many-to-many joins unless I’m intentionally bridging. I add uniqueness tests on the key and relationship tests to key dimensions. If stakeholders need multiple grains, I create separate facts or aggregates rather than mixing grains in one table.”
Common mistake: Saying “I just group by what I need,” which signals ad-hoc thinking.
Q: In dbt, when would you use incremental models vs. full refresh, and what pitfalls do you watch for?
Why they ask it: They want someone who can scale transformations safely—classic dbt Developer territory.
Answer framework: “Volume–Change pattern–Backfill plan”: decide based on data size, update behavior, and recovery.
Example answer: “I use incremental when the table is large and new data arrives append-only or with a clear ‘updated_at’ strategy. I’m careful with late-arriving data and hard deletes—those require merge strategies or periodic backfills. I also treat schema changes as a first-class risk: I’ll use dbt’s on_schema_change settings intentionally and add tests around the incremental filter so we don’t silently miss records. And I always document the backfill procedure so incidents don’t turn into guesswork.”
Common mistake: Using incremental everywhere and then being surprised when historical corrections never show up.
Q: Show me a SQL pattern you use to deduplicate event data with late arrivals.
Why they ask it: Event data is messy; they want practical SQL, not theory.
Answer framework: “Window + deterministic tie-break”: partition by natural key, order by event time + ingestion time.
Example answer: “If I have duplicate events by event_id, I’ll use a window function: row_number() over (partition by event_id order by event_timestamp desc, ingested_at desc) and keep row_number = 1. If event_id isn’t reliable, I’ll build a composite key from user_id + event_name + event_timestamp bucket and then apply the same pattern. I’ll also keep a ‘dedupe_reason’ field or a separate audit model if the business cares about counts.”
Common mistake: Deduping without a deterministic rule, which makes results change run to run.
Q: How do you design a metrics layer or semantic layer so Finance and Product stop arguing?
Why they ask it: They’re testing whether you can operationalize definitions, not just document them.
Answer framework: “Metric contract”: owner, definition, grain, filters, and validation.
Example answer: “I treat metrics like APIs. Each metric has an owner (often Finance for revenue), a definition with edge cases, the grain it’s valid at, and default filters like excluding internal users. Then I implement it in a semantic layer or dbt metrics/semantic tooling depending on the stack, and I validate it against a trusted reference—like the GL for revenue. The key is governance that’s lightweight: changes require a PR, a reviewer, and a changelog so downstream teams aren’t surprised.”
Common mistake: Thinking documentation alone will stop metric drift.
Q: What tests do you consider non-negotiable in dbt for production models?
Why they ask it: They want to see your risk instincts.
Answer framework: “Risk-based testing pyramid”: start with keys, then relationships, then business rules.
Example answer: “At minimum: not_null and unique on primary keys, relationships to core dimensions, and freshness on critical sources. Then I add accepted_values where enums matter, and custom tests for business invariants—like ‘paid_orders <= created_orders’ or ‘revenue is non-negative.’ I also like anomaly detection at the metric level—if daily revenue deviates beyond a threshold, alert. The exact set depends on blast radius: executive dashboards get more guardrails.”
Common mistake: Only testing for nulls and calling it “data quality.”
Q: How do you optimize a slow warehouse query powering a dashboard?
Why they ask it: In the US, cost and performance are constant topics—especially on Snowflake/BigQuery/Redshift.
Answer framework: “Observe–Reduce–Restructure”: inspect query plan, reduce scanned data, then remodel.
Example answer: “First I look at the query profile: what’s scanning the most, what joins are exploding, and whether filters are applied early. Then I reduce scan volume—partition pruning, clustering keys, pre-filtered staging, or materialized aggregates. If the dashboard needs the same join repeatedly, I’ll build a dedicated mart at the right grain and cache it. I also check BI behavior—some tools generate inefficient SQL, so semantic modeling can help.”
Common mistake: Jumping straight to ‘add an index’—often irrelevant in cloud warehouses.
Q: How do you handle PII in analytics models in the United States?
Why they ask it: They’re testing governance maturity and awareness of US privacy expectations.
Answer framework: “Minimize–Control–Audit”: reduce PII, enforce access, log usage.
Example answer: “I try to keep PII out of broad analytics marts by default—use surrogate keys and only expose what’s necessary. For access, I rely on role-based controls in the warehouse and BI tool, and I separate restricted schemas. I also document which fields are sensitive and add automated checks to prevent accidental exposure. Depending on the company and states involved, I align with privacy obligations like CCPA/CPRA and internal security policies.”
Common mistake: Treating PII as only a legal issue, not a modeling and access-design issue.
Q: Tell me about a time you migrated a legacy SQL pipeline into dbt. What did you change besides the tool?
Why they ask it: They want to see if you modernize practices—lineage, tests, documentation.
Answer framework: “Lift–Refactor–Harden”: move it, improve it, then add guardrails.
Example answer: “I started by lifting the existing transformations into dbt models with the same outputs so we could validate parity. Then I refactored into layers—staging, intermediate, marts—so logic wasn’t duplicated. Finally, I hardened it with tests, exposures for dashboards, and a CI job that ran on PRs. The biggest change wasn’t dbt itself; it was making changes reviewable and making breakages visible before they hit stakeholders.”
Common mistake: Describing a migration as ‘we rewrote it’ without explaining validation and rollout.
Q: What would you do if your dbt run starts failing every morning after a source schema change?
Why they ask it: They’re testing incident response and resilience.
Answer framework: “Triage–Stabilize–Prevent”: fix today, then stop repeats.
Example answer: “I’d triage which models fail and whether it’s a breaking change or a data issue. To stabilize, I might pin column selection in staging, add a temporary compatibility layer, and communicate impact to downstream users. Then I’d prevent repeats by setting expectations with the upstream team: schema change notifications, contracts, and automated checks—like source tests or schema snapshots. If the org supports it, I’d add CI that detects schema drift before production.”
Common mistake: Treating it as ‘dbt is flaky’ instead of an upstream contract problem.
Q: How do you model subscriptions (upgrades, downgrades, churn) so metrics are consistent?
Why they ask it: Subscription logic is a classic “insider” trap—easy to get subtly wrong.
Answer framework: “State machine + effective dating”: represent changes over time explicitly.
Example answer: “I like an effective-dated subscription table: one row per customer per plan period with start/end timestamps and a status. Upgrades/downgrades become transitions, and churn is an end state with a clear definition (end of paid period vs. cancellation event). Then MRR/ARR is derived from the active state at a point in time, not from raw events. I validate against billing invoices and define edge cases like pauses and refunds upfront.”
Common mistake: Mixing event-level data and period-level revenue in one model and hoping it works out.