Prepare for an analytics engineering interview by explaining how you turn raw records into tested, documented business models. Strong answers connect SQL choices to grain, reliability and downstream use. The questions below are original practice prompts, not claims about questions asked by specific employers.

1. What is the grain of your model?

State what one row represents before writing SQL. An orders model might contain one row per order; an order-lines model contains one row per item within an order. Summing an order-level total after joining it to three line items multiplies that total by three.

A strong answer names the key, states whether it can be null, and explains how uniqueness is checked. “One row per order_id, with unique and non-null tests” is more precise than “a sales table.”

2. How would you organise a dbt project?

One workable approach is staging models for consistent names and types, intermediate models for reusable transformations, and marts for defined business outputs. This is a design convention, not a requirement to create three layers for every query. Explain when another layer makes a transformation easier to test or reuse.

3. Which tests would you add first?

For a model called fct_orders, begin by testing the primary key:

version: 2
models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique

Place the YAML in your project’s models directory. Once the model exists in a configured dbt project, run dbt test --select fct_orders. This is a configuration fragment, not a complete runnable project. A passing key test does not prove that revenue is defined correctly.

4. How do you test a business rule?

Suppose refund amounts must never exceed the original order amount in a model whose grain is one row per order. A singular test can return violations. Save this as a SQL file in the tests directory of the project:

select order_id, refund_cents, original_amount_cents
from {{ ref('fct_orders') }}
where refund_cents > original_amount_cents

With the usual zero-failure threshold, returned rows indicate a failed assertion. Confirm whether your business allows exceptions before adopting the rule. Test missing amounts separately: comparisons with NULL do not return true.

5. What makes an incremental load difficult?

Late-arriving data, updates to older records, deletes and repeated deliveries complicate incremental processing. Explain the unique key, watermark, overlap window and merge behaviour. A filter on the maximum event date can miss an old event that arrives today. An overlap window also needs deduplication; repeatedly appending the same rows is not idempotent.

6. Revenue doubled after a deployment. What next?

Compare the last trusted output with the new one. Check key uniqueness, source freshness, join cardinality, status filters and refund handling. Reconcile by day or channel to narrow the affected records. Pause dependent reporting if necessary, communicate the scope, and restore a known-good version using your team’s deployment process.

7. How would you describe your own project?

Use four parts: the business question, the model grain, the checks, and the limitations. For a portfolio project, describe observed dataset results rather than inventing commercial impact. A good follow-up exercise is to introduce duplicate keys and show exactly which test catches them.

Continue with data engineering resources and SQL CTEs.

Sources and further reading

• dbt: data tests
• dbt: incremental models

Found an error? Send a correction.