Введение
Поскольку мы постоянно загружаем данные в хранилище данных, нам неизбежно придется иметь дело с изменениями в исторических данных.
Хранилища данных по определению содержат исторические данные. Что нам делать, когда приходит новый пакет данных и некоторые атрибуты в таблицах изменились? Это распространенная проблема в хранилищах данных, которая называется Медленно меняющиеся измерения (SCD).
Методы работы с SCD применяются к таблицам измерений, а не к таблицам фактов. Сами данные таблицы фактов не подлежат SCD, поскольку они представляют собой запись событий, которые уже произошли в определенный момент времени.
Методы SCD применяются к таблицам измерений, поскольку данные в таблицах измерений могут меняться со временем. Например, клиент может изменить свой адрес, или продукт может изменить свою цену. Эти изменения не отражаются в таблице фактов, но отражаются в таблице измерений.
Нет смысла изменять исторические записи о продажах после того, как цена продукта изменилась, потому что эти продажи уже произошли и являются частью исторической записи. Однако мы хотим отразить новую цену в будущих продажах, поэтому нам следует изменить таблицу измерений продукта.
Как обращаться с SCD
Есть несколько способов справиться с этими типами изменений. Три основных:
- Тип 1: перезапишите старые данные новыми данными. Это самый простой метод.
- Тип 2. Сохраняйте неограниченное количество данных, добавляя новую строку в таблицу измерений с обновленными данными. Это самый распространенный метод.
- Тип 3: сохранить ограниченные данные, добавив новый столбец в таблицу измерений с обновленными данными. Этот метод находится где-то между Типом 1 и Типом 2.
Тип 1: перезаписать старые данные новыми данными.
Как уже упоминалось, в каждом пакете новых данных мы заменяем старые данные новыми данными. Здесь нет сложной логики, поэтому это самый простой метод.
Цена, конечно, заключается в том, что мы теряем исторические данные. Это не всегда плохо, иногда нас не интересуют исторические данные, а интересует только их текущее состояние.
SCD типа 1 полезны в ситуациях, когда исторические данные не важны или могут быть легко получены из других источников. Они часто используются, когда атрибуты измерения не часто меняются и сохранение исторической информации не требуется для целей анализа или отчетности.
Например, пользователь в нашей таблице параметров клиентов повысил свое членство с Серебряного до Золотого. Мы не пользуемся историческими записями, и они не подвержены большим изменениям. Поэтому будем разбираться с этим методом типа 1.
Текущая таблица измерений клиентов выглядит следующим образом:
select * from dwh_customers;
Теперь «Джон Сноу» хочет изменить свое членство на Gold. Мы можем решить эту проблему следующим образом:
-- Check customer's membership level select membershiplevel from dwh_customers where customer_id = 15345; -- Overwrite and update old record update dwh_customers set membershiplevel = 'Gold' where customer_id = 15345; -- Check updated membership level select * from dwh_customers where customer_id = 15345;
Тип 2: сохранить неограниченное количество данных, добавив новую строку в таблицу измерений с обновленными данными.
В этом методе мы добавляем новую строку в таблицу измерений с обновленными данными. Мы также добавим в таблицу измерений два новых столбца с названием «Effective_date», в которых указано, когда строка была впервые вставлена. И «end_date», которые позволяют нам идентифицировать последнюю запись.
Этот метод приведет к тому, что таблица параметров со временем будет расти, но сохранит исторические данные. Это наиболее распространенный метод обращения с SCD, особенно в последние годы, когда стоимость хранения снизилась.
Давайте используем предыдущий пример, чтобы проиллюстрировать это. Как уже упоминалось, чтобы приспособить новый метод обработки изменений в параметре клиента, мы добавим два новых столбца: «дата_действия» и «дата_окончания».
В столбце date_date указывается, когда строка вступила в силу, а в столбце end_date указывается, когда строка больше не действует.
Чтобы определить последнюю строку, мы обновим столбец «end_date» до «null».
Чтобы отметить, что предыдущая строка больше не актуальна, мы обновим столбец «end_date», указав текущую дату.
Текущая таблица измерений клиентов будет выглядеть следующим образом:
Как и прежде, теперь «Джон Сноу» хочет обновить свое членство с Серебряного до Золотого. Давайте применим метод Типа 2, чтобы применить изменение и сохранить историческую запись.
Во-первых, я обновлю столбец end_date старой записи с null на сегодняшнюю дату.
Затем я вставлю новую строку для «Джон Сноу», в которой «вступительная_дата» будет равна сегодняшнему дню, а «конечная_дата ” будет нулевым (чтобы указать, что это последняя запись «Джон Сноу»)
-- Check customer's membership level select * from dwh_customers where customer_id = 15345; -- Update end_date = today for old record update dwh_customers set end_date = CURRENT_DATE where customer_id = 15345; -- Insert a new record of "John Snow" insert into dwh_customers(customer_id, first_name, last_name,date_of_birth,MembershipLevel,effective_date, end_date) select customer_id, first_name, last_name,date_of_birth,MembershipLevel,effective_date, end_date from dwh_customers where customer_id = 15345; -- Update new record. update dwh_customers set membershiplevel = 'Gold', effective_date = CURRENT_DATE, end_date = null where customer_id = 15345 and customer_key = (select max(customer_key) from dwh_customers); -- Check if John Snow now has two rows select * from dwh_customers where customer_id = 15345;
Тип 3: сохранить ограниченные данные, добавив новый столбец в таблицу измерений с обновленными данными.
В этом методе мы добавляем новый столбец в таблицу измерений с обновленными данными. Идея состоит в том, чтобы хранить фиксированное количество исторических записей в таблице измерений. Это фиксированное количество обычно равно 2, но может быть и больше, все зависит от того, сколько столбцов мы хотим добавить.
Обратите внимание, что тип 1 вообще не хранит исторические записи, а тип 2 хранит бесконечное количество исторических записей, поэтому тип 3 — это способ компромисса между этими двумя подходами.
Чтобы сделать это более конкретным, давайте используем тот же пример, что и раньше. На этот раз, поскольку наша таблица измерений должна быть подготовлена для обработки изменений в столбце «membershiplevel» с использованием метода типа 3, мы добавим еще один столбец с именем «membershiplevel_new».
Итак, теперь, когда «Джон Сноу» повысит свое членство с Серебряногодо Золотого, мы НЕвставим новую запись в таблицу измерений. Вместо этого мы обновим столбец «membershiplevel_new» на «Gold».
Текущая таблица измерений клиентов будет выглядеть следующим образом:
Теперь предположим, что Джон обновляет свое членство до «Gold»:
Как мы видим, мы смогли зафиксировать изменение, сохранить исторические данные и сделать все это без вставки новой записи и увеличения размера таблицы.
Другими словами, этот метод более эффективен, чем тип 2 с точки зрения места для хранения, однако цена заключается в том, что мы можем хранить только фиксированное количество изменений в каждой записи. . В данном конкретном случае эта фиксированная сумма составляет всего 2.
Допустим, теперь Джон хочет снова повысить уровень своего членства до «Платинового». В этом случае мы потеряли бы исторические записи о том, что Джон когда-либо имел статус «Серебряный», поскольку у нас есть только два столбца для хранения текущего и прошлого членства.
Чтобы применить изменение, нам нужно обновить оба столбца членства:
Как мы видим, мы смогли зафиксировать дополнительное изменение, но мы потеряли первоначальную запись о том, что Джон был участником Серебряного уровня. Это стоимость Типа 3.
Мы могли бы добавить еще один столбец с именем membershiplevel_new2, что позволит нам фиксировать три изменения в столбце membership, но в какой-то момент таблица может стать слишком сложно поддерживать, и нам было бы лучше просто использовать Тип 2. Все зависит от стоимости хранения, частоты изменений, архитектуры базы данных и т. д.…
Вот и все для этой статьи, надеюсь, вы хорошо прочитали и узнали что-то новое. Оставайтесь с нами, чтобы узнать больше о концепциях инженерии данных.