Как мы удаляем частичные дубликаты: Сложная дедупликация для уточнения зерна ваших моделей

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

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

Вы здесь, потому что ваши дубликаты — это особые дубликаты. Эти особые дубликаты не являются базовыми, которые имеют одинаковые значения в каждом столбце и дублируют первичные ключи, которые можно легко исправить, небрежно добавив distinct (да, я назвал использование distinct небрежным!). Это частичные дубликаты, что означает, что первичный ключ вашей сущности не уникален намеренно (или, возможно, вы просто имеете дело с неидеальной синхронизацией данных). Вы можете фиксировать исторические, медленно изменяющиеся данные второго типа или инкрементально строить таблицу с помощью стратегии только добавления, потому что вы действительно хотите фиксировать некоторые изменения во времени для записываемой вами сущности. (Или, как уже упоминалось, ваш загрузчик может просто добавлять данные без разбора по расписанию, не заботясь о вашем времени и здравомыслии.) Что бы ни привело вас сюда, теперь у вас есть таблица, где не является первичным ключом вашей сущности, а вместо этого первичный ключ сущности + значения столбцов, которые вы отслеживаете. Запутались? Давайте посмотрим на пример.

Вот ваша необработанная таблица:

entity_id	unimportant_value	important_status	updated_date
1	cool	pending	2022-02-24
1	lame	submitted	2022-03-01
1	cool	submitted	2022-03-03
2	cool	pending	2022-02-27

У вас есть эта историческая запись, которая фиксирует все изменения, внесенные в сущности. Пока все в порядке, но вот в чем загвоздка: вас на самом деле не волнуют изменения, происходящие в столбце unimportant_value, и, следовательно, новое значение столбца updated_date и новая строка не всегда указывают на реальное изменение (реальное здесь определяется в терминах того, что важно для вас при моделировании данных для наших заинтересованных сторон). Как обсуждалось, зерно набора данных, которое вы хотите зафиксировать, — это комбинация столбцов, которые мы считаем важными и которые делают каждую строку уникальной. Таким образом, в этом случае зерно — это entity_id + important_status, а не просто entity_id. В результате строки 2 и 3 — это то, что мы будем называть частичными дубликатами. Если вы проигнорируете два столбца, значения которых вы не хотите отслеживать, вы найдете дубликаты, которые нужно устранить!

Определение ключевых столбцов

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

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

Комбинация entity_id + [изменяющиеся столбцы], которые вы хотите зафиксировать, становится зерном вашей модели. В нашем примере мы стремимся зафиксировать данные на уровне зерна entity_important_status, которое мы временно назовем entity_grain.

entity_grain	entity_id	unimportant_value	important_status	updated_date
1_pending	1	cool	pending	2022-02-24
1_submitted	1	lame	submitted	2022-03-01
1_submitted	1	cool	submitted	2022-03-03
2_pending	2	cool	pending	2022-02-27

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

entity_grain	entity_id	unimportant_value	important_status	updated_date
1_pending	1	cool	pending	2022-02-24
1_submitted	1	cool	submitted	2022-03-03
2_pending	2	cool	pending	2022-02-27

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

Обзор

Вот краткий обзор шагов, которые мы предпримем:

1. Создайте уникальный, хешированный grain_id для каждой строки вашей таблицы из каждого значимого значения столбца.
2. Отметьте реальные различия в данных на основе созданного вами ключа и отфильтруйте дубликаты.
3. Добавьте тесты, чтобы убедиться, что ваша дедупликация сработала и ваш набор данных остается чистым!

Шаг 1: Построение уникального ключа для каждой строки вашей таблицы

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

Идея на этом шаге заключается в том, чтобы обеспечить проверку дубликатов в данных, прикрепив уникальный ключ к хешированным значениям столбцов, которые составляют зерно сущности, которую вы хотите отслеживать. Важно отметить, что dbt_utils.generate_surrogate_key еще не создаст уникальный ключ! Вместо этого он создаст ключ, который будет таким же, как ключ другой строки, если значения столбцов, которые мы выбрали для нашего зерна сущности, одинаковы. Это намеренно и критически важно! Специфическая неуникальность — это то, как мы поймаем наши хитрые дубликаты.

В нашем примере вы можете увидеть, что функция surrogate_key создает одинаковый grain_id для двух строк, которые мы знаем, что являются дубликатами, строк 2 и 3, при этом строка 3 является самой последней строкой.

grain_id	entity_grain	entity_id	unimportant_value	important_status	updated_at_date
8e0bd4a0e4a6e3a4ad3f28f13a3d5e51	1_pending	1	cool	pending	2022-02-24
c8b91b84808caaf5870d707866b59c	1_submitted	1	boring	submitted	2022-03-01
c8b91b84808caaf5870d707866b59c	1_submitted	1	cool	submitted	2022-03-03
283ff22afb622dcc6a7da373ae1a0fb	2_pending	2	cool	pending	2022-02-27

Помните, важно искать дубликаты строк только для значений, которые указывают на истинное различие между строками данных; например, в данных второго типа updated_at_date не означает, что другие столбцы, которые мы решили, что нас интересуют, изменились с момента предыдущей загрузки, поэтому этот столбец не обязательно указывает на истинное различие между строками (хотя обычно указывает, что что-то изменилось, но это изменение может быть вне нашего круга интересов в данном случае). Но изменение в important_status, для наших целей, указывало бы на изменение в данных, которое вы, вероятно, захотите отслеживать. Если вы не применяете эту технику к данным второго типа, а вместо этого хотите удалить все, кроме самых последних данных, у вас может быть всего несколько столбцов, которые указывают на истинное различие между строками (идентификатор на правильном уровне зерна и/или идентификатор на более крупном уровне зерна + временная метка).

Чтобы построить наш ключ grain_id, мы используем чистое золото из пакета dbt_utils. Если вы не уверены, что это за пакет, прекратите чтение прямо сейчас и убедитесь, что он установлен в вашем проекте dbt. Он принесет радость в вашу жизнь и облегчение вашим страданиям!

dbt_utils.get_filtered_columns_in_relation — это звезда шоу здесь, которая позволяет вам захватить все столбцы из relation (ссылка/источник), кроме тех, которые вы указываете, и поместить их в список. Если у вас всего пара столбцов, может быть проще просто перечислить их для переменной cols, вместо использования этой функции.

{%- macro build_key_from_columns(dbt_relation, exclude=[]) -%}

{% set cols = dbt_utils.get_filtered_columns_in_relation(dbt_relation, exclude)  %}

{{ return(dbt_utils.surrogate_key(cols)) }}

{%- endmacro -%} 

Для каждой строки данных этот макрос захватывает каждое значение из всех столбцов, кроме тех, которые мы указываем в списке исключений. Затем он создает хеш-ключ с использованием dbt_utils.surrogate_key, который будет отражать уникальность значений столбцов (т.е. если комбинация значений не уникальна, surrogate_key будет таким же, что и то, что мы хотим зафиксировать). Столбцы в списке исключений — это значения, которые мы хотим игнорировать при поиске изменений в таблице данных (например, unimportant_value, столбец, колебания которого мы не хотим указывать как реальное различие между строками). Вызовите макрос выше, чтобы создать столбец в вашем базовом или промежуточном слое, и назовите его grain_id, чтобы мы могли отфильтровать изменения, где count(grain_id) > 1:

{{ build_key_from_columns(source('name', 'table_name')) }} as grain_id,

Шаг 2: Отметьте действительно уникальные строки и отфильтруйте

Шаг 2 проведет вас через процесс фильтрации дубликатов на основе вашего нового grain_id из Шага 1.

Чтобы избавиться от дубликатов, найдите предыдущий grain_id (помните, это хеш-ключ всех значений в строке), сравните его с самым последним grain_id, упорядоченным по надежной временной метке. Если они не равны, то отметьте это как реальное различие в данных, что означает, что вы его сохраните! Обратите внимание, что мы используем coalesce для нашей оконной функции со строкой ‘first_record’, чтобы первая запись сущности, которая естественно не имеет previous_grain_id, не имела null в этом столбце и не сбивала все наши последующие сравнения.

mark_real_diffs as (

  select
      *,
      coalesce(
          lag(grain_id) over (partition by entity_id order by updated_at_date),
          'first_record'
      ) as previous_grain_id,
      case
          when grain_id != previous_grain_id then true 
          else false
      end as is_real_diff

  from base_product

),

Отфильтруйте все, что не отмечено как реальное различие, и теперь вы свободны от дубликатов!

filter_real_diffs as (

    select *
  
    from mark_real_diffs
  
    where is_real_diff = true

)

select * from filter_real_diffs

Шаг 3: Протестируйте ваши данные

Что происходит на этом шаге? Вы проверяете свои данные, потому что вы тщательны!

Хорошо, что dbt уже построил это для вас. Добавьте уникальный тест в ваш YAML блок модели для вашего grain_id в этой модели без дубликатов и проведите тест dbt!

models:
  - name: my_model
    columns:
      - name: grain_id
        tests:
          - unique
          - not_null

Заключение

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

Вы звезда! Я так рад, что вы дошли до конца, и теперь ваши данные безупречно чисты.

Подведем итоги, мы предприняли следующие шаги, чтобы идентифицировать и устранить частичные дубликаты:

1. Определили, какие столбцы нас интересуют, включая:
   1. Столбец(ы), которые формируют уникальную сущность, которая нас интересует
   2. Столбец(ы), которые составляют атрибуты этой сущности, изменения которых мы хотим отслеживать
   3. Столбец(ы), которые составляют наше наиболее надежное измерение времени обновления
2. Создали surrogate_key из всех вышеперечисленных столбцов (кроме временной метки, которую мы используем для упорядочивания), который фиксирует комбинацию значений из этих столбцов в один столбец
3. Использовали этот ключ, чтобы отметить и удалить дубликаты в частичном наборе столбцов, которые мы отслеживаем

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

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