Что такое первичный ключ и зачем мы их тестируем?

Мы все это делали: расширяли данные во время объединения, создавая дублирующиеся записи (иногда дублирующиеся в нескольких экземплярах).

Тот случай, когда исторические данные о доходах удвоились в понедельник? Классический пример расширения.

Можно ли было этого избежать? Да, очень просто: определив уникальность для с помощью первичного ключа и обеспечив её с помощью теста dbt.

Итак, давайте углубимся в тему: что такое первичные ключи, какие облачные аналитические хранилища их поддерживают и как вы можете тестировать их в вашем хранилище для обеспечения уникальности.

Что такое первичный ключ?

— это столбец в вашей базе данных, который существует для уникальной идентификации одной строки.

Первичные ключи критически важны для моделирования данных. Без первичного ключа вы постоянно будете сталкиваться с проблемами идентификации дублирующихся строк и определения ожидаемой зернистости ваших таблиц.

Нет более железного закона в мире аналитики, чем у вас должен быть первичный ключ в каждой таблице.

Зачем мы тестируем первичные ключи?

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

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

Это, конечно, то, что делает ваши первичные ключи такими мощными. Видите ли, большинство случаев, когда первичные ключи нарушаются, происходит из-за:

• Есть строки, где первичный ключ равен null
• Есть строки, где первичный ключ не уникален (дублирующиеся значения)

Как вы увидите ниже в разделе «поддержка PK в хранилищах», некоторые хранилища позволяют вам определять первичные ключи, но не будут обеспечивать ни отсутствие null, ни уникальность значений. Поэтому мы тестируем.

В те времена, когда тестирование данных не было обычным делом, вы часто узнавали, что у вас есть проблемы с первичными ключами, когда вы (или, что хуже, ваш начальник) замечали, что отчет неверен. Это приводило к множеству ненужных переживаний и потере доверия к данным.

Как тестировать первичные ключи с помощью dbt

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

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

• Not_null
• Unique

Эти тесты указываются в конфигурационных файлах .yml ваших моделей, так что вы можете определить набор тестов для моделей из одного файла. Вместе тесты not_null + unique будут выглядеть так:

models:

  - name: orders

    columns:

      - name: order_id

        tests:

          - unique

          - not_null

Это действительно так просто, как добавление этих двух тестов к первичным ключам всех ваших таблиц, и у вас будет встроенная защита от плохих данных в ваших первичных ключах.

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

Поддерживает ли ваше хранилище первичные ключи?

Поддерживает ли ваше хранилище вообще первичные ключи? Если да, то как вы можете узнать, установлены ли первичные ключи для таблицы и какие это ключи?

Давайте рассмотрим поддержку первичных ключей и доступ к ним на основных облачных платформах .

Краткий обзор поддержки первичных ключей в хранилищах

BigQuery и Databricks не поддерживают первичные ключи, Redshift и Snowflake поддерживают первичные ключи, но не полностью их обеспечивают, а Postgres полностью поддерживает и обеспечивает первичные ключи.

Это означает, что в основных аналитических хранилищах тестирование данных (с использованием инструмента, такого как dbt) для обеспечения ваших первичных ключей крайне важно для обеспечения качества аналитических данных.

Redshift, Snowflake и Postgres позволяют вам запрашивать списки столбцов первичных ключей из таблиц информационной схемы вашей базы данных. Читайте дальше для получения подробной информации и ссылок на соответствующую документацию.

Первичные ключи в BigQuery

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

Первичные ключи в Databricks

Databricks Delta SQL не поддерживает первичные ключи в классическом смысле SQL, и вместо этого предлагает то, что они называют ограничениями для полей (not null является одним из них).

Аналогично BigQuery, суррогатные ключи могут быть использованы для обхода этого ограничения.

Первичные ключи в Redshift

Amazon Redshift позволяет устанавливать первичные ключи как ограничение таблицы (что помогает с оптимизацией запросов), однако эти ограничения фактически не обеспечиваются самим хранилищем.

Вы можете затем запрашивать эти ограничения из таблиц information_schema.table_constraints и information_schema.key_column_usage.

В конечном итоге, это ваша ответственность проверять уникальность и отсутствие null в ваших данных, чтобы гарантировать, что ваши ограничения таблицы действительно обеспечены (см. раздел тестирования в конце этого поста).

Первичные ключи в Snowflake

Snowflake поддерживает команду SHOW PRIMARY KEYS, которая позволяет вам запрашивать первичные ключи для ваших таблиц.

Обратите внимание, что первичные ключи в Snowflake являются чисто декларативными — ни уникальность, ни ограничения на отсутствие null не обеспечиваются. Однако Snowflake поддерживает отдельное ограничение not null, которое может быть применено к столбцу и обеспечивается. Тем не менее, вам все равно нужно будет убедиться, что значения вашего столбца первичного ключа действительно уникальны (см. раздел тестирования внизу).

Первичные ключи в Postgres

Postgres полностью поддерживает первичные ключи, что означает, что он обеспечивает уникальность и отсутствие null в таблицах с первичными ключами.

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

Вы можете запрашивать столбцы первичных ключей из административных таблиц pg_index и pg_attribute.

Начали ли вы тестировать первичные ключи?

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