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

Я хотел бы сразу сузить рамки обсуждения и договориться о терминологии:

• Хранилище данных (Datawarehouse, DWH) подразумевается традиционное SQL DWH (Oracle Database, MS SQL Server и т.д.);
  
• При моделировании DWH обычно подразумеваются концепции single version of truth (единая  версия правды) и historical truth (историческая правда);
  
• Под ETL-процессом (Extraction-Transformation-Loading) подразумевается процесс загрузки данных из одной или нескольких source systems (исходных систем ) в DWH.
  
• DWH создано не вчера и в данный момент над ним независимо работает несколько команд разработчиков со своими проектами.
  

Термин ETL трактуется зачастую по-разному, благодаря «простой» расшифровке своей аббревиатуры. На самом деле задач ETL является лишь подмножеством задач Data Movement. У Кимбалла в его книге «The Data Warehouse ETL Toolkit» выделяются 3 операции, которые обязательно должен совершать ETL-процесс:

1. Загружать данные в максимальном удобном виде для аналитических приложений;
   
2. В процессе загрузки данных обогащать их дополнительной информацией;
   
3. Фиксировать и документировать lineage (происхождение) данных.
   

Первый пункт достаточно очевиден, поэтому его я пропущу. Второй пункт говорит о том, что данные должны не просто перегружаться из одного места в другое, но и обогащаться в процессе, например, новыми рассчитанными атрибутами, техническими атрибутами (id сессии загрузки, дата загрузки, исходная система и т.д.). Третий говорит о том, что для любой записи должна существовать возможность отследить откуда и когда эта запись появилась в DWH, когда и каким процессом она изменялась.

В целом, суть большинства ошибок ETL-разработчика можно объяснить игнорированием жизненного правила с этой картинки.



В дальнейшем будут использованы примеры для DWH на базе Oracle 11g. Итак приступим.

1. Использование системной даты (или аналогичной функции) в бизнес-логике

Одна из самых простых и частых ошибок, особенно у неопытных разработчиков. Допустим есть бизнес-правило: во время «ночного окна для загрузки» выгружать заказы, которые были закрыты за этот день (по полю close_date). Результатом иногда бывает примерно такой sql statement:

insert <….> into target_table
select <….> from orders
where close_date >= sysdate() — 1

Даже если забыть про то, что sysdate() может содержать не только дату, но и время, то у нас с этим скриптом возникают проблемы в тот момент, когда регулярная работа ETL процесса нарушается по вполне банальным причинам (исходная система апгрейдится на новую версию, пропала связь с исходной системой, из-за нового процесса ETL закончилось место во временном tablespace и т.д.). Т.е. в тот момент когда наш ETL процесс нужно по каким-то причинам перезапустить или же приостановить на время и потом снова запустить. Также может произойти нечто интересное, если по какой-то причине этот процесс запустят дважды за день.

Решение у этой ошибки обычно простое: параметризовать вызов данного процесса, и если нужно, то использовать sysdate() как дефолтное значение с возможностью переопределения. Хотя использование поля типа datetime для обработки дельты с точки зрения сопровождения ХД не очень оптимально, и вместо него лучше применить дельту по некоему дискретному полю (например целочисленный id сессии загрузки или нечто подобное)

2. Профилирование данных не было сделано перед началом разработки

Даже самая документированная и разработанная по всем правилам и методикам исходная система обычно содержит в себе некорректные или неконсистентные данные, несмотря на многочисленные уверения ее разработчиков или команды поддержки. И полагаться на уверения в правильности с той стороны баррикад, обычно чревато проблемами в конце разработки. Любой источник данных (таблица, файл, xml, json и т.д.) должен быть проверен на соответствие логической модели DWH. Существуют различные инструменты для профилирования данных, как встроенные в ETL инструменты, так и независимые от них. Перечислю наиболее востребованные проверки:

Проверка #1: Уникальность идентификаторов и натуральных ключей исходных данных

Различие между идентификатором и натуральным ключом состоит в том, что идентификатор — это обычно некое суррогатное значение, которое технически идентифицирует строку, а натуральный ключ — это значение или комбинация значений, которые имеют бизнес-смысл.

Таблица order_details:

order_details_id	document_position	order_id
35346346	10	1224114
35346365	20	1224114
….	….	….
35345464	10	1224438

В данном примере order_details_id — это идентификатор, а комбинация document_position+order_id — это натуральный ключ.

Пример: я участвовал в проекте по загрузке данных в DWH из распределенной системы (instance-based), в которой велся учет объектов сетевой инфраструктуры. Разработчики этой системы на голубом глазу уверяли, что id этого объекта является уникальным и даже показывали в исходной системе уникальный индекс на таблице в подтверждение своих слов. Подвох выявился не сразу: оказывается уникальность этих id существовала только в рамках одного инстанса системы, и когда попробовали загрузить все данные со всех инстансов, то получилась проблема с уникальностью. В результате пришлось менять модель данных и расширять натуральный ключ сущности «сетевой объект» дополнительным полем «инстанс», чтобы обеспечить уникальность.

Проверка #2: Типы данных

Если поле называется Order_nr, то в нем необязательно содержатся только числовые значения — там вполне могут быть буквенно-цифровые последовательности. Также всегда стоит проверять длину полей. Эта проблема обычно характерна для файловых источников данных — таблицы БД обычно хорошо типизированы.

Проверка #3: Ссылочная целостность (проверка FK)

То, что разработчик показывает ER-диаграммы своей исходной системы, показывает у себя на DEV-окружении существующие FK между таблицами, и вообще мамой клянется, что у него все под контролем, не является поводом не проверить существование «повисших» записей. Т.к. он может быть не в курсе, что на продуктивном окружении DBA уже отключил эту проверку для улучшения производительности (конечно, согласовав это с менеджером разработчика, т.е. никто не виноват). Также проблемы со ссылочной целостностью очень часто встречается для файловых источников данных. Также не стоит забывать о применении сценария late-arriving-data (например, если данные приходят согласовано сегодня, далеко не факт, что так будет и через полгода).

Проверка #4: NULL значения

Основная проблема NULL значений состоит в том, что NULL<>NULL, поэтому любые запросы с джойнами по полю, которое может содержать NULL, будут возвращать непредсказуемые результаты. Поэтому все важные поля стоит обернуть конструкцией nvl(). Существует отдельный холивар по поводу грузить NULL в неключевые поля или заменять на некие значения по умолчанию. Мне ближе идея о всеобщей замене NULLов для более стандартизированного подхода к использованию DWH, но я не берусь настаивать, что так нужно делать всегда.

Проверка #5: Даты

Проверки полей с датами обычно являются самыми усложненными, т.к. помимо стандартных проверок приходится учитывать то, что не все даты, которые являются допустимыми с точки зрения БД, являются таковыми с точки зрения DWH: дата «21-07-1007» вряд ли является допустимой для даты заключения договора на оказание услуг сотовой связи. При моделировании DWH обычно существуют т.н. даты «начала времен» и «конца времен» (возможны другие названия), и любая дата, не попадающая в этот диапазон времени должна заменяться на некое значение по умолчанию.

Отдельного упоминания заслуживают случаи использования типов данных вроде varchar(8) для хранения дат (в формате например ‘20151201’), т.к. количество проверок здесь должно быть еще больше.

3. Удаление дубликатов через GROUP BY или DISTINCT

Несмотря на то, что в DWH обычно грузятся все данные, которые приходят из источника, существуют сценарии, когда на вход приходят заведомо дублирующиеся данные. Но уникальность натурального ключа требует только одну запись из дубликатов. Существует два неправильных способа удаления дубликатов:

Неправильный способ #1: GROUP BY

Допустим, мы грузим адреса клиентов и знаем, что теоретически для одного клиента может прийти несколько записей с адресной информацией (обычно они являются полными дубликатами из-за проблем, например, с синхронизацией). Поддавшись желанию решить задачу «в лоб», разработчик может написать такой запрос:

insert into customer_address
select customer_id, max(street_name), max(house_nr)
from source_table
group by customer_id

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

customer_id	street_name	house_nr
1321	Moskovskaya str	127
1321	Pushkinskaya str	34

Запрос может вернуть такой результат (в зависимости от локали):

customer_id	street_name	house_nr
1321	Pushkinskaya str	127

Такой записи в исходных данных не было, и у пользователей DWH может возникнуть резонный вопрос, что вообще это такое? На самом деле здесь нарушено 3-е требование к ETL процессу: в DWH была загружена запись, которая не может быть отслежена до исходной системы, проще говоря, которой там нет. И это однозначная ошибка ETL разработчика.

Неправильный способ #2: DISTINCT

Второй вариант «решения в лоб» в описанном выше сценарии — это использовать для удаления дублирующихся записей DISTINCT

insert into customer_address
select distinct customer_id, street_name, house_nr
from source_table

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

Один из правильных способов

Как же стоит решать проблему наличия двух записей с одинаковым натуральным ключом, но разными атрибутами? Очевидно, что если в модель данных данных изменений не внести, то из всех записей должна быть выбрана одна единственная правильная. Выбирать ее нужно согласно заранее определенному критерию: если информация является довольно критичной, то можно реализовывать различные сценарии Data Quality, если же нет, то в качестве корректной записи брать последнюю загруженную.

insert into customer_address
select customer_id, street_name, house_nr from (
select customer_id, street_name, house_nr,
row_number() over (partition by customer_id order by change_datetime desc) row_num
from source_table)
where row_num = 1

В общем следует не забывать, что любая запись в DWH должна иметь возможность быть отслеженной до источника(ов) данных в зависимости от бизнес-правила и не создавать «необъяснимые» записи.

4. Использование «статичных» скриптов из исходных систем

Очень часто бизнес-логика для сущностей DWH приходит от разработчиков или аналитиков исходных систем в виде SQL скриптов. И это большое подспорье для ETL-разработчика, но, как говорится, «бойтесь данайцев, дары приносящих»: как правило эти скрипты фиксируют некое условно «статичное» состояние исходное системы в некоторый момент времени, а ETL-разработчик обычно занимается отслеживанием динамики в данных и загрузкой только изменений («дельта»). Что же должно настораживать в этих «статичных» SQL скриптах? Вот некоторые из:

• агрегатные функции (SUM, AVG, COUNT и т.д.)
  
• операторы IN и EXISTS
  
• оконные функции ( OVER(PARTITION BY …))
  

Пример такого скрипта:

insert order_id into orders_from_calls
select order_id from orders
where order_id IN (select order_id from calls where order_id <> -1)
and changed_date > $last_loaded_date

Вроде бы все логично: грузить в нашу таблицу order_from_calls все заказы, на которые есть ссылка в таблице звонков, и для которых дата последнего изменения больше даты последней загрузки. А теперь представим, что обновление таблицы calls в DWH не произошло (например, она грузится из другой исходной системы и связь с ней по какой-то причине нарушена), и этот запрос не загрузил некоторые id заказов. После этого таблица calls была дозагружена правильно, и там эти пропущенные id заказов появились, но мы их уже не загрузим в таблицу order_from_calls, т.к. в таблице orders ничего не поменялось и новые запуски этого запроса ничего не дадут. Поэтому в данном случае отслеживать дельту нужно не только по таблице orders, но и по таблице calls.

5. Разработка на небольшом объеме данных для разработки

Как правило, ETL-разработчику для разработки на DEV-окружении выгружается небольшая часть данных из продуктивной системы, на которой и предлагается вести разработку  и отладку работы ETL-процессов. К сожалению, разработанные на таком малом объеме данных решения обычно приводят к различным проблемам на продуктивной системе, таким как недостаточная производительность, нехватка места для промежуточных таблиц (например, разработчик решил красиво разнести шаги бизнес-логики по набору промежуточных таблиц, последовательно перегружая из одной в другую — а вот в продуктивной системе данных оказалось слишком много, и tablespace для временных таблиц скоропостижно закончился).

К сожалению, эту ошибку ETL-разработчик не всегда может решить самостоятельно, из-за различных регламентов и инструкций, отсутствия бюджета на полноценное DEV-окружение с тем же объемом данных как на продуктиве и т.д. Таким образом, это стоит рассматривать как проектный риск.

Одним из выходов является дробление этапов проекта на более мелкие и делать релизы более часто, чтобы идентифицировать такие проблемы не в конце проекта, а хотя бы посередине.

6. Неправильное использование технических и бизнес дат

В DWH существует 2 типа дат: бизнес-даты и технические даты. Разница у них в происхождении: бизнес-дата — это та дата, которая пришла из источника данных или была создана по бизнес-правилам; техническая дата — это дата, которая была сгенерирована ETL процессом либо самим DWH. И очень часто их используют неправильно:

#1 Бизнес-даты используются как технические даты

Если сущность историзируется как SCD2 (Slowly Changing Dimension type 2) и в источнике данных есть поля "_from" и "_to", которые ETL разработчику предлагается использовать в качестве диапазонов валидности данных, то у него должны быть просто железобетонные гарантии того, все диапазоны валидности для каждого натурального ключа будут: 1) непересекающимися, 2) между диапазонами не будет разрывов, 3) объединение этих диапазонов дат будет совпадать с диапазоном даты «от начала времен» до «конца времен» установленных для вашего DWH (это могут быть например пары дат «01.01.1000» и «31.12.9999», или «11.11.1111» и «09.09.9999»). Как правило, разработчики исходных систем мало заморачиваются, и если правило «непересекающихся диапазонов дат» обычно соблюдается, то со 2-м и 3-м пунктом обычно возникают проблемы. В любом случае, общей рекомендацией является не использовать бизнес-даты для SCD2, а генерировать свои технические даты.

#2 Технические даты используются как бизнес-даты

Очень часто источники данных не поставляют поля для отслеживания каких-либо контрольных дат: например, документ имеет только статус закрытия, но не метку времени, когда это событие произошло, и в качестве решения предлагается использовать технические даты "_from" и "_to", которые были сгенерированы ETL процессом. Однако это решение работает до первого сбоя ETL процесса (например, остановки ETL процессов на пару дней): сбой произошел в понедельник, восстановление наступило в среду, и т.к. исходная система вполне себе работала все это время, все созданные документы будут загружены как созданные в среду. В общем случае, сценарий «историческая правда» не реализуем, если источник данных не поставляет всех нужных пользователям дат и может быть лишь сэмулирован (с помощью технических дат), но в таком случае этот сценарий должен быть проговорен и описан в документации, чтобы через год пользователи не удивлялись нулевому количеству закрытых документов в понедельник и вторник, а также тройному количеству их в среду.

7. «Механическая» реализация

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

Иногда так случается, что целевая сущность в DWH была неправильно смоделирована исходя из-за расхождений в терминологии для разработчика исходной системы и архитектора. Разработчик исходной системы мыслит категориями своей исходной системы, архитектору DWH же необходимо продумывать различные интеграционные схемы, как связать в едином DWH множество объектов из разнородных исходных систем.

Опишу на примере сущности «клиент» как одной из типичных для такого рода проблем: в источнике данных есть таблица «customer», имеющая уникальный натуральный ключ, ссылочная целостность в порядке. На основе этой таблицы в DWH была создана сущность «customer». Исходя из названия, логично предположить, что одна запись в этой таблице должна соответствовать одному клиенту, но фактически выяснилось, что на самом деле один и тот же реальный клиент мог иметь несколько записей с одними и теми же атрибутами, но разными натуральными ключами. И это привело бы к неприятной коллизии для пользователей DWH, которые использовали эту сущность, например, для подсчета общего количества клиентов компании. В результате было принято решение разделить эту сущность на две: «customer_record» и «customer», связанные через FK отношением M:1.

А если бы ETL разработчик «механически» реализовал все по спецификации, то он бы конечно был бы не виноват, но у него была возможность заметить это, т.к. в любом случае он по сравнению с архитектором работает условно говоря «на земле», в отличие от «витающего в облаках» архитектора.

В целом можно упомянуть некоторые симптомы «механической» реализации:

• «Наследовать» имена таблиц из исходной системы
  
• Копировать бизнес-логику из существующих потоков данных в новые
  
• Использовать джобы «пилотного» проекта в основном
  

Что же стоит делать, чтобы минимизировать риски «механической» реализации:

• Внимательно анализировать бизнес-правила с любыми джойнами, которые потенциально могут «отрезать» часть данных (left outer join обычно предпочтительнее inner join)
  
• C другой стороны проверять «сомнительные» джойны, которые могут «размножить» данные из-за некорректного или неполного условия
  

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

Заключение

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