После многих лет работы над наукой о данных и инженерией качество данных — это призрак, который появляется почти в каждом проекте, уничтожая достижения бизнеса.

SQL де-факто является языком данных. Одним из способов улучшения качества данных является улучшение базы кода SQL с помощью модульного тестирования и тестирования данных. Эта статья в основном вдохновлена ​​постом г-жи Гао .

В этой статье фундаментальная логика модульного теста на SQL с dbt будет проиллюстрирована простым набором данных.

Основная идея

Основная идея проведения модульного тестирования SQL точно такая же, как и модульного тестирования кода Python:

1. макет управляемого ввода D
2. есть тестируемый модуль/функция/алгоритм, назовите его A
3. вычислить ожидаемый результат, используя D в качестве входных данных, получить O_should
4. сравните ожидаемый результат (O_should) с фактическим результатом A (O_is)

Иногда это не обязательно должно быть точное совпадение, т.е. совпадение только до 2 цифр после запятой.

Пример

Давайте построим наивный пример, чтобы проиллюстрировать идею выше. Нам нужна следующая структура папок проекта dbt

-- dbt_project.yml

-- data/
------ iris.csv
------ selected_iris_expected.csv

-- models/
------ iris/
---------- selected_iris.sql
---------- schema.yml

# example: dbt_project.yml

# take things under data/ as seeds
data-paths: ["data"]

# configure seed, all going into unittesting schema
seeds:
    schema: unittesting

-- selected_iris.sql
{{ config(
        materialized='table',
        schema='unittesting',
        tags=['iris']
    )
}}

-- count the number of special iris id (above average in all aspects)
-- not a very meaningful logic, just for exemplare purpose
SELECT
distinct count(distinct id)
FROM "public"."iris";
where sepallengthcm > 5.9 and sepalwidthcm > 3.1 and petallengthcm > 3.8 and petalwidthcm > 1.2
-- selected_iris_expected.csv
count
150

# schema.yml 

version: 2

# table model selected_iris should be equal to iris
models:
  - name: selected_iris

    tests:
      - dbt_utils.equality:
          compare_model: ref('selected_iris_expected')
          tags: ['unit_testing']

Шаг 1: загрузите тестовые данные в базу данных

# here we use
# iris.csv will be loaded into unittesting.iris table
# selected_iris_expected.csv will be loaded into unittesting.selected_iris_expected table
dbt seed
# build selected_iris model into unittesting.selected_iris
dbt run

Шаг 2: сравните

# here we use
# all tests within folder model/iris/ with be executed
# of course, we can restrict to only unittesting using tags
dbt test --model iris

Технические особенности

Модульное тестирование SQL выглядит простым, верно? но на самом деле все может быть сложнее:

• вам, вероятно, нужна более сложная структура папок (с использованием вложенных подпапок) для разделения и организации тестов в соответствии с проектами.
• вам может потребоваться включить/выключить модульное тестирование на основе текущей среды dev/prod
• у вас может быть большая таблица ввода, которую трудно издеваться
• у вас может быть сложная модель, по которой сложно заранее рассчитать ожидаемый результат (модель не тестируема)
• ожидаемый результат может не на 100% совпадать с фактическим выходом (даже они практически одинаковы) из-за точности с плавающей точкой и т. д.
• или у вас может просто не быть бюджета времени в этом проекте, что не редкость, люди не часто проводят модульное тестирование sql в 2022 году, операторы SQL считаются правильными после того, как они были написаны

Совет: модулируйте операторы SQL

Несмотря на все проблемы, перечисленные выше, есть одна вещь, которая помогает при модульном тестировании в случае с SQL, а также с любыми другими более общими языками программирования, такими как Python.

Это модуляризация. Хорошая модульная модель/функция/алгоритм гарантирует тестируемость и удобочитаемость даже для sql.

Есть много способов реализовать это с помощью sql и dbt:

• дбт макрос (https://docs.getdbt.com/docs/build/jinja-macros)
• с заявлением (https://learnsql.com/blog/what-is-with-clause-sql/)

Заключение

SQL — это родной язык данных, в этой статье мы продемонстрировали способ модульного тестирования SQL с помощью dbt.

Точно так же мы могли бы также выполнить тест интеграции данных или другие трюки, такие как делегирование создания sql более мощному языку, такому как python, с помощью rasgoQ L.