rzv Data Engineering

Эксперимент — серия постов будет выходить средними кусочками несколько дней подряд

#зачем_нужно

Проблемы и решения в очистке данных 1/?

При загрузке данных из исходных систем мы почти всегда сталкиваемся с "грязными" данными - опечатки, разные форматы, технические ошибки. Если не обработать такие случаи, таблицы перестанут джойниться или будут выдавать мусор на выходе (в BI, отчётах и пр.).

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

🔶 Название поля не соответствует naming convention в DWH

column as new_column

Лучше хотя бы на raw слое оставить исходные названия колонок для lineage и traceability. И старайся не множить сущности, где возможно, приводи к единому стилю (naming convention) и называй одинаковые параметры одинаково, а разные — по-разному.

🔶 Формат даты

try_cast(date_column as date) /* для безопасного приведения */ 

to_date(date_string, 'YYYY-MM-DD') /* если известен формат */ 

case when date_column ~ '^\d{4}-\d{2}-\d{2}$' then cast(date_column as date) end /* с валидацией */ 

🔶 JSON, который нужно распарсить и разложить по колонкам

case when is_valid_json(json_column) then /* проверка валидности */
     json_value(json_column, '$.field_name'),
     json_query(json_column, '$.contacts[*].phone'), /* массив */
     json_value(json_column, '$.address.city'), /* вложенный объект */
     (select string_agg(value, ',') 
      from json_table(json_column, '$.tags[*]' columns (value varchar path '$'))
     ) as tags /* массив в строку */
   end 

Не забывай обрабатывать случаи с пустыми JSON'ами и массивами.

🔶 Вручную заполняемые поля "перечисляемого типа", которые нужно привести к одному виду

Использовать нечёткое сопоставление, например splink на python, или вручную заполненный маппинг значений, предварительно приведённых к trim(upper(replace(column, ' ', ''))) или другому подобному формату.

Написание запросов а-ля lower(col) like '%sub%string%' плохо масштабируется и зачастую приводит к неожиданным результатам (когда под шаблон начинают попадать "не те" категории).

www.tgoop.com/rzv_de/257

2.1K viewsedited  Nov 19 at 14:46

Эксперимент — серия постов будет выходить средними кусочками несколько дней подряд

#зачем_нужно

Проблемы и решения в очистке данных 1/?

При загрузке данных из исходных систем мы почти всегда сталкиваемся с "грязными" данными - опечатки, разные форматы, технические ошибки. Если не обработать такие случаи, таблицы перестанут джойниться или будут выдавать мусор на выходе (в BI, отчётах и пр.).

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

🔶 Название поля не соответствует naming convention в DWH

column as new_column

try_cast(date_column as date) /* для безопасного приведения */ 

to_date(date_string, 'YYYY-MM-DD') /* если известен формат */ 

case when date_column ~ '^\d{4}-\d{2}-\d{2}$' then cast(date_column as date) end /* с валидацией */ 

case when is_valid_json(json_column) then /* проверка валидности */
     json_value(json_column, '$.field_name'),
     json_query(json_column, '$.contacts[*].phone'), /* массив */
     json_value(json_column, '$.address.city'), /* вложенный объект */
     (select string_agg(value, ',') 
      from json_table(json_column, '$.tags[*]' columns (value varchar path '$'))
     ) as tags /* массив в строку */
   end 

BY rzv Data Engineering