Analytics engineer domain knowledge interview questions

As asked

Define MRR, ARR, churn rate, and net revenue retention as a SaaS analytics engineer would implement them in a data model. What are the common definitional traps that cause these metrics to be calculated incorrectly?

Sample answer outline

MRR is the total monthly recurring revenue from active subscriptions at a point in time. ARR is MRR times 12. Churn rate is the percentage of MRR at the start of a period that is lost by the end due to cancellations or downgrades. Net revenue retention (NRR) includes expansion revenue from upsells and cross-sells minus churn and contraction, measuring whether existing customers grow over time. Common traps: including one-time charges in MRR, using cash receipt date instead of subscription period date, counting a customer who cancels and restarts as both a new customer and a churned customer, and differences between customer churn and revenue churn. A strong answer defines the exact grain of the MRR fact table needed.

Expect these follow-ups

• What is the difference between gross revenue retention and net revenue retention?
• How do you handle a customer who pauses their subscription for one month?

As asked

Explain the bronze/silver/gold medallion data architecture. When is it the right pattern, and when is it overkill?

Sample answer outline

Bronze: raw ingested data, schema-on-read, no transformations. Silver: cleaned, conformed, deduplicated, joined with reference data. Gold: business-ready, often aggregated, modelled for specific consumer use cases. The point is to separate ingestion concerns from modelling concerns and to keep raw data immutable. It is overkill for small data volumes, or when there is only one downstream consumer, or when the source is already clean and stable. The hidden cost is storage duplication and three sets of tests to maintain.

Expect these follow-ups

• How does this compare to the data mesh approach?
• Where does dbt fit in this architecture?
• Why might you collapse silver and gold for some domains?

As asked

A cohort retention chart shows that the March cohort has much higher week-4 retention than the February cohort. A product manager concludes that the product improvement shipped in early March is working. What questions do you ask before accepting that conclusion?

Sample answer outline

Questions to ask: How large is each cohort? A small March cohort can show volatile retention rates. Was the March cohort acquired differently, such as through a different channel or promotion, which could explain the retention difference independently of the product change? Did both cohorts have the same week-4 observation window (the February cohort should have more weeks of data, but is there a data collection issue for March)? Was there another change around the same time as the product improvement? A strong answer demonstrates that correlation in cohort data does not imply causation and proposes an A/B test as the right validation method.

Expect these follow-ups

• How would you design an A/B test to properly evaluate the impact of this product change on retention?
• What is survivorship bias and could it affect this analysis?

As asked

What is the difference between cash basis and accrual basis revenue recognition, and how does that difference affect how you model revenue in a data warehouse?

Sample answer outline

Cash basis recognizes revenue when payment is received. Accrual basis recognizes revenue when it is earned (when the service is delivered), regardless of when cash arrives. For a SaaS company, a customer paying annually upfront generates cash in January but the revenue is recognized monthly over 12 months. This means the data model needs a revenue_recognition_date separate from payment_date, and the mart model must allocate annual payments into monthly recognized amounts. A strong answer discusses how this affects the grain of the revenue fact table (one row per month per subscription rather than one row per payment) and why Finance and Sales may disagree on what counts as revenue.

Expect these follow-ups

• How do you model annual prepaid subscriptions to produce accurate monthly recurring revenue figures?
• What is ASC 606 and why should an analytics engineer know about it?

As asked

Conversion rate from free trial to paid dropped from 22% to 18% this month. A product manager panics. What do you look at before concluding there is a problem?

Sample answer outline

Check the absolute numbers: did trial volume spike (diluting conversion rate with lower-intent users) or did paid conversions actually drop in absolute terms? Check whether the mix of acquisition channels changed (organic converts better than paid ads). Check for seasonal effects (trials started in December may convert less in January). Verify the data pipeline is complete and there is no lag in recording conversions. Compare month-over-month and year-over-year. A strong answer also mentions statistical significance: 22% to 18% on a small sample could be noise, and on a large sample it could be highly significant.

Expect these follow-ups

• How would you test whether the 4-point drop is statistically significant?
• What segmentation cuts would you apply first to narrow down the cause?

As asked

Explain the difference between first-touch, last-touch, and linear attribution models. When would you use each, and what does an analytics engineer need to consider when building attribution in the warehouse?

Sample answer outline

First-touch gives 100% credit to the first marketing touchpoint. Last-touch gives 100% to the final touchpoint before conversion. Linear distributes credit equally across all touchpoints. First-touch favors awareness channels (organic search, display). Last-touch favors closing channels (branded search, retargeting). Linear is more balanced but still arbitrary. In the warehouse, you need a clean touchpoint event stream with user identifiers stitched across devices and sessions. The biggest engineering challenge is identity resolution (cross-device, pre-login anonymous sessions) and deciding what counts as a touchpoint. A strong answer covers data-driven attribution (ML-based credit allocation) as the gold standard but notes it requires enough conversion volume to be reliable.

Expect these follow-ups

• What is position-based (U-shaped) attribution and when would you use it?
• How do you handle users who convert without any tracked marketing touchpoint?

As asked

Not all tables need to be equally fresh. How do you decide what freshness SLA to set for a customer-facing operational table versus an executive dashboard versus a historical trend report?

Sample answer outline

Freshness requirements scale with how quickly stale data causes a bad decision. Customer-facing operational data (like showing a customer their usage) may need to be fresh within minutes and is best served by a streaming pipeline or near-real-time incremental refresh. An executive dashboard used in weekly reviews can tolerate daily freshness. A historical trend report used for quarterly planning can tolerate weekly or even monthly freshness. Setting SLAs explicitly allows you to right-size the pipeline (batch daily is much cheaper than streaming) and communicate clearly to stakeholders what to expect. A strong answer also covers how to surface freshness metadata in the BI tool so users know when data was last updated.

Expect these follow-ups

• How do you display data freshness information to end users of a BI dashboard?
• What is the cost difference between a real-time pipeline and a daily batch pipeline for the same volume of data?

As asked

Your users are global but your database stores all timestamps in UTC. A stakeholder in New York wants to see daily active users by the business day in Eastern time. How do you handle this in your dbt model?

Sample answer outline

Convert UTC timestamps to the target timezone using the warehouse's timestamp conversion function (CONVERT_TIMEZONE in Snowflake, AT TIME ZONE in BigQuery) before applying DATE_TRUNC for daily bucketing. The key insight is that a UTC day boundary does not align with the Eastern day boundary, so events between midnight and 5 AM UTC belong to the prior Eastern day. A common mistake is truncating by date before converting timezone, which assigns events to the wrong day. A strong answer also discusses what to do for companies that operate globally (store in UTC everywhere and let each BI consumer convert to their local timezone) versus companies where all business happens in one timezone.

Expect these follow-ups

• How do daylight saving time transitions affect time-zone conversion logic?
• What happens to your DAU metric on the day the clocks change if you use CONVERT_TIMEZONE in Snowflake?

As asked

What is the difference between an entity-centric schema (one row per customer with many columns) and an event-centric schema (one row per event per customer), and when does each approach work better?

Sample answer outline

An entity-centric schema (wide customer table) is easy for simple snapshots and current-state queries but cannot represent history or changes over time without SCD complexity. An event-centric schema records each action or state change as a row, making it easy to reconstruct history and answer 'what happened when' questions, but queries require aggregation to get current state. The event schema is better for product analytics and behavioral analysis. The entity schema is better for CRM-style lookups. A strong answer discusses using both in conjunction: an event fact table plus a current-state dimension table derived from it, and how dbt snapshots bridge the two.

Expect these follow-ups

• How would you build a current-state customer table from an event stream in dbt?
• What is the Segment Personas or Amplitude Profile approach and how does it relate to this?