What is the difference between dbt data tests and unit tests? Which would have caught each of these: a revenue model that counts refunds as sales, and a source that started sending duplicate order ids?

A data test checks the data a model produced, on every run, in production: is order_id unique, is status one of the expected values, does every customer_id exist in customers. A unit test checks a model's logic on a few rows you write by hand, before it is built, in development and CI. The duplicate order ids are a data problem, caught by a unique data test on the source or staging model. Refunds counted as sales is a logic error: the real data can look plausible, so a unit test with one sale and one refund, expecting revenue of the sale only, catches it.

1. Side by side

2. The data test for duplicates

models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['placed', 'shipped', 'completed', 'returned']
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('stg_customers')
                field: customer_id

In dbt build a failing unique test stops the models downstream of stg_orders from building on duplicates. store_failures keeps the failing rows in a table to inspect.

3. The unit test for refunds

unit_tests:
  - name: refunds_do_not_count_as_revenue
    model: fct_revenue
    given:
      - input: ref('stg_payments')
        rows:
          - {order_id: 1, amount: 100, kind: sale}
          - {order_id: 1, amount: -100, kind: refund}
          - {order_id: 2, amount: 50, kind: sale}
    expect:
      rows:
        - {gross_sales: 150, refunds: 100}

It runs before fct_revenue is built and fails if the logic adds refunds to sales.

4. Where each goes

• Data tests on every primary key (unique, not_null) and every foreign key (relationships) of sources and models.
• Unit tests where the SQL is tricky: regex, date math, window functions, long case when statements, and any logic that has had a bug before. Not on functions the warehouse already tests, such as min().
• Severity: make a test warn when a failure should be looked at but not stop the build.

What interviewers want: data tests check produced data on every run and unit tests check logic on fixed inputs in CI, the 4 generic tests, a test failure skipping downstream models in dbt build, unit tests for complex logic, and matching each failure to the test that catches it.