dbt tests deep на собеседовании Data Engineer

Generic tests

Built-in 4 tests:

• unique
• not_null
• accepted_values
• relationships (FK)

columns:
  - name: id
    tests: [unique, not_null]
  - name: status
    tests:
      - accepted_values:
          values: ['active', 'inactive']
  - name: customer_id
    tests:
      - relationships:
          to: ref('customers')
          field: id

Singular tests

Custom SQL tests. File в tests/ directory.

-- tests/no_negative_amounts.sql
SELECT *
FROM {{ ref('orders') }}
WHERE amount < 0;

Test fails если query returns rows.

Custom generic tests

Reusable across models.

-- macros/test_positive_value.sql
{% test positive_value(model, column_name) %}
SELECT *
FROM {{ model }}
WHERE {{ column_name }} <= 0
{% endtest %}

Use:

columns:
  - name: amount
    tests:
      - positive_value

Severity и thresholds

columns:
  - name: email
    tests:
      - unique:
          severity: warn  # или error
          warn_if: ">100"
          error_if: ">1000"

error — fail run. warn — log, continue.

Useful когда small drift acceptable.

store_failures

tests:
  - unique:
      store_failures: true

Failed rows saved в audit table. Investigate later.

SELECT * FROM analytics.dbt_test__audit.unique_orders_id;

Critical для debugging тестов на больших datasets.

Связанные темы

• dbt на собесе DE
• dbt incremental models для DE
• Great Expectations для DE
• DQ dimensions для DE
• Подготовка к собесу Data Engineer

FAQ

Это официальная информация?

Нет. Статья основана на документации dbt 1.7+.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.