Концепции обработки данных № 1  — «Медленно меняющиеся измерения»

Введение

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

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

Методы работы с SCD применяются к таблицам измерений, а не к таблицам фактов. Сами данные таблицы фактов не подлежат SCD, поскольку они представляют собой запись событий, которые уже произошли в определенный момент времени.

Методы SCD применяются к таблицам измерений, поскольку данные в таблицах измерений могут меняться со временем. Например, клиент может изменить свой адрес, или продукт может изменить свою цену. Эти изменения не отражаются в таблице фактов, но отражаются в таблице измерений.

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

Как обращаться с SCD

Есть несколько способов справиться с этими типами изменений. Три основных:

1. Тип 1: перезапишите старые данные новыми данными. Это самый простой метод.
2. Тип 2. Сохраняйте неограниченное количество данных, добавляя новую строку в таблицу измерений с обновленными данными. Это самый распространенный метод.
3. Тип 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. Все зависит от стоимости хранения, частоты изменений, архитектуры базы данных и т. д.…

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