Analytics engineer role-specific interview questions

As asked

You inherit a dbt project with 200 models and no consistent structure. How would you reorganise it?

Sample answer outline

Standard layering: staging (one-to-one with source tables, light renaming and casting), intermediate (joins and reshaping, no business logic), marts (business-facing, one row per business concept). Group marts by domain (finance, marketing, product) not by source. Name models predictably: stg_, int_, fct_, dim_. Tag models for selection in CI and prod. Document at the model and column level so the warehouse is browsable. Enforce structure with project linters (dbt-project-evaluator). Migrate the existing 200 models in waves, not all at once.

Expect these follow-ups

• How do you handle a one-off analytics question that does not fit any mart?
• What is your testing strategy: every model or just marts?
• When is a model worth materialising as a table vs a view?

As asked

What is your testing strategy for a dbt project? Walk me through what you test, where, and how you handle failures in production.

Sample answer outline

Tests sit at three levels. Source tests: freshness, schema, the absolute basics ('this table has rows today'). Staging tests: not_null and unique on primary keys, accepted values for enums. Mart tests: row-level invariants, relationships between tables, expected ranges. Severity: warn vs error. Errors block downstream and page on-call; warns flag for triage. CI runs tests on a sample for fast PR feedback; production runs them on every refresh. Handle failures: a quarantine schema where bad data goes for investigation rather than poisoning the warehouse silently.

Reference implementation (yaml)

version: 2
sources:
  - name: stripe
    tables:
      - name: charges
        loaded_at_field: created_at
        freshness:
          warn_after: { count: 2, period: hour }
          error_after: { count: 6, period: hour }

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - not_null
          - unique
      - name: status
        tests:
          - accepted_values:
              values: ["pending", "paid", "refunded", "cancelled"]
              severity: error
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customer')
              field: customer_id
              severity: warn

Expect these follow-ups

• What is the right test to write for a freshness regression that is hard to spot?
• How do you avoid alert fatigue from data tests?
• When does dbt's testing fall short and what do you reach for?

As asked

A 30-person Series A SaaS company asks you to pick their data stack from scratch. Walk me through your choices and the tradeoffs.

Sample answer outline

Warehouse: Snowflake or BigQuery; both work, pick based on engineering team familiarity. Ingestion: Fivetran or Airbyte for SaaS sources; build custom only where there is no connector. Transformation: dbt Core (with dbt Cloud if you want managed scheduling and IDE). BI: Mode, Hex, or Looker depending on user persona. Reverse ETL: Hightouch or Census if marketing or ops needs warehouse data in their tools. Observability: Monte Carlo or open-source elementary. Total monthly cost in the low thousands; complexity is the team's, not the stack's. Avoid premature distributed compute (Spark, Trino) at this stage.

Expect these follow-ups

• When would you build instead of buy for ingestion?
• How does the choice change at Series C with 200 engineers?
• Where do open-source tools beat the SaaS versions and where do they lose?

As asked

Your warehouse has 500 dbt models across 8 teams. An analyst breaks a model and 30 dashboards go red. How would you build lineage to prevent this?

Sample answer outline

dbt already builds a DAG of models; expose it. Add column-level lineage with a tool like dbt-osmosis or a vendor (Castor, Atlan, Select Star) that parses the SQL. Tie BI tools into lineage so a model change shows the downstream dashboards. CI: warn when a PR touches a model with many downstream consumers; require reviewers from affected teams. Process: a model contract (the columns and types it commits to) and a deprecation flow for breaking changes. Lineage tooling alone does not prevent breakage; the contracts and review process do.

Expect these follow-ups

• How do you handle a critical model that has 100 downstream consumers?
• What is a model contract in dbt and what does it cost you?
• When is lineage information overwhelming rather than helpful?