Data analysis | Анализ данных | DA

Про NULL, 0 и пустоту в SQL

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

🟠Для начала, в чем различие между пустотой, NULL и 0? Разберемся на примерах.

☑️NULL - это отсутствие информации как таковой, состояние неизвестности, и отражает состояние строки, а не конкретные данные.

Например, если при заключении договора с клиентом мы не спросили его номер телефона, то тогда в БД должен отразиться NULL.

☑️Пустота - это уже наличие информации о том, что значение известно и равно пустой строке.

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

☑️Ноль это обычное число, которое отражает количество, еще участвует в математических операциях.

Но есть еще специфические применения 0, когда он выступает не как число, а как маркер отсутствия / отрицания. Одно из таких применений - это поля с флагом, где условно 1 - это да, а 0 - это нет.

🟠Почему так важно разделять это всё? Что изменится, если, к примеру, мы везде будем записывать только пустоту или только NULL?

В этом случае нарушится логика и сегментация. Возвращаясь к примеру, мы не сможем сказать, почему у нас отсутствует номер телефона клиента: потому что мы его не спросили или потому что он сам отказался его оставлять. Очевидно, что такие вещи важно различать.

🟠Но ещё не всё так просто. Важно упомянуть тот факт, что очень часто в столбцах не может храниться одновременно и NULL, и пустота, и 0.

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

🟠Теперь о том, как разные операторы обрабатывают пустоты, NULL и 0:

▶️COUNT. Здесь важно различать COUNT(поле) и COUNT(*). COUNT(*) считает количество строк, независимо от того, есть в них NULL или нет. COUNT(поле) работает уже как агрегатная функция и выдает кол-во именно известных значений, т.е. NULL игнорируется.

▶️AVG. Считает среднее, игнорируя NULL и учитывая 0, что, впрочем, логично.

▶️MIN и MAX точно так же игнорируют NULL и учитывают 0. Но если использовать на числовом столбце ORDER BY, то NULL будет выводиться в самом верху, если сортировать по убыванию.

▶️При сравнении чего-то с NULL (обычно в WHERE) с помощью =, <, > всегда будет выдаваться NULL, поэтому фильтровать нужно, используя IS NULL. При этом пустоту и 0 операторы сравнения поддерживают.

▶️GROUP BY и PARTITION BY объединяет всё по разным группам: все NULL - в одну группу, пустоту - в другую, 0 - в третью.

▶️DISTINCT считает все NULL дубликатами, поэтому оставляет только одно значение NULL. Аналогично с пустотой и 0.

▶️UNION действует по такой же логике - убирает все дубли. Но UNION ALL объединяет всё как есть, ничего не убирая.

▶️JOIN не присоединяет NULL к NULL, потому что NULL = NULL это не TRUE. Но соединяет пустоты и нули.

▶️Ранжирующие функции (ROW_NUMBER(), RANK(), DENSE_RANK()) учитывают NULL и присваивают ранг.

Как хранить историю изменений в DWH

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

Говоря в контексте схем БД, прежде всего речь идет об обновлении таблиц измерений: изменившиеся данные куда-то нужно девать, и записать хронологию в этом случае сложнее, чем в таблице фактов. Как это всё осуществить архитектурно?

🟠На помощь приходит подход Slowly Changing Dimensions (SCD). Идея в том, чтобы трансформировать таблицу измерений в более информативную. Например, в таблице "Клиенты" помимо содержащихся там полей:

▶️id
▶️ФИО
▶️Сегмент и т.п.

добавить еще поля:

▶️Дата начала
▶️Дата окончания
▶️Флаг текущей версии
▶️id представить как 2 ключа: бизнес ключ (старый id, например номер договора) и суррогатный ключ

Отдельно прокомментирую разбивку на ключи. Это нужно для того, чтобы у строки был уникальный идентификатор. Потому что бизнес ключ остается одним и тем же - он определяет клиента Иванова И.И. как отдельного человека (например по уникальному номеру договора). Но суррогатный ключ, если можно так сказать - "технический" в рамках таблицы измерений, и постоянно меняется с каждой новой версией.

Допустим, у Иванова И.И. обновился сегмент. Как это будет выглядеть для ключей:

БК = 1; СК = 1; ФИО = Иванов И.И; Сегм = РБ
-------------------------------
БК = 1; СК = 2; ФИО = Иванов И.И; Сегм = МСБ

🟠На фоне этого возникает вопрос: как джойнить эту таблицу с фактами? Ведь до SCD таблицы соединялись по БК, который всегда был уникальным, а теперь он стал дублироваться. Решение простое - нужно теперь джойнить по СК, а для этого его необходимо добавить еще и в таблицу фактов как дополнительный атрибут.

Сделать это можно постфактум. Например, в таблице фактов отражено, что 1-ого числа была совершена продажа Иванову И. И. В клиентской таблице смотрим, какой СК был актуален у Иванова И. И. на 1-ое число, и просто заносим его в факты, предварительно добавив там новое поле.

И так нужно сделать для каждого измерения, которое связывается с фактами. Если таких измерений, например, 5, то в фактах у нас будет 5 столбцов с СК для каждого из них.

MERGE в SQL

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

Допустим, у нас есть таблица customers (целевая) и new_customers (источник). И мы хотим перенести данные таким образом, что если клиент уже есть - обновить имя и дату. Если нет - добавить нового. Тогда процесс переноса будет выглядеть так:

MERGE INTO customers c
USING new_customers nc
   ON (c.id = nc.id) 
WHEN MATCHED THEN
   UPDATE SET 
      c.name = nc.name,
      c.last_updated = nc.last_updated
WHEN NOT MATCHED THEN
   INSERT (id, name, last_updated)
   VALUES (nc.id, nc.name, nc.last_updated)

Сначала по некой аналогии с джоинами соединяем выбранные таблицы по id. Затем идет разветвление на условия со специфической командой WHEN (NOT) MATCHED (не путать с конструкцией CASE WHEN THEN - это другое): есть совпадение - делаем UPDATE. Нет - делаем INSERT

🟠Можно ли решить эту задачу без использования MERGE? Можно, но это будет не так эффективно. Например:

▶️Последовательно выполнить 2 запроса: UPDATE + INSERT или DELETE + INSERT. Но, как правило, 2 запроса всё-таки хуже одного. Также между UPDATE и INSERT могут произойти изменения в таблицах, что нарушит корректность данных. В случае с DELETE это вообще грубый способ в рамках этой задачи, т.к. это всё так же 2 запроса, еще и теряются исторические данные и нет частичного обновления (например только конкретных полей).

▶️Можно написать сложный запрос с использованием СТЕ. Но это будет банально сложнее сделать, чем просто написать конструкцию с MERGE. С точки зрения производительности неочевидно, что будет быстрее работать, так что придется сравнивать оба варианта.

▶️Еще можно попытаться решить эту задачу при помощи процедуры и цикла, но основная проблема тут будет в том, что это всё будет крайне медленно работать, особенно на больших данных.

Talend

Talend — это довольно популярная платформ для интеграции и управления данными. В основном используется как ETL-инструмент.

Вообще, Talend - это целая экосистема продуктов, но самые популярные из них и наиболее нужные аналитикам - это Talend Open Studio (бесплатная версия, но уже считается старой) и Talend Data Integration Platform (платная версия, более расширенная по сравнению с Open Studio).

🟠ETL-процесс в Talend визуализируется в виде Job. Создаются они с помощью специальных операторов, с которыми можно просто взаимодействовать на уровне интерфейса Talend Studio (или TDI). После запуска Job трансформируется в код (в основном это Java). Выглядит этот процесс следующим образом:

1️⃣Сначала идет подключение к источнику данных. Вариантов источника может быть много: Talend поддерживает различные базы данных, файлы, CRM-системы, облачные хранилища и еще много всего.

Подключение идет через вышеупомянутые операторы, например:

➖tFileInputDelimited - для CSV файлов
➖tFileInputExcel - для Excel файлов
➖tDBInput - для баз данных (MySQL, PostgreSQL, Oracle)
➖tHDFSInput - для Hadoop HDFS

2️⃣Затем опционально идет преобразование данных в нужный формат / вид. Осуществляется это с помощью операторов:

➖ tMap - универсальный оператор в части трансформации ETL-процесса. Отвечает за маппинг, вычисления, "джоины", форматирование...
➖tFilterRow - фильтрация данных
➖tAggregateRow - агрегатные функции
➖tSortRow - сортировка
➖tUniqRow - удаление дубликатов

3️⃣В конце данные загружаются в целевое место. Пример операторов, которые за это отвечают:

➖tDBOutput - запись в базу данных
➖tFileOutputDelimited - запись в CSV файл
➖tHDFSOutput - запись в HDFS
➖tLogRow - вывод в логи

🟠Также нельзя еще забывать, что Talend можно использовать для Data Quality и для Data Preparation. Для этого подойдёт всё тот же Talend Data Integration, но для Data Preparation можно еще использовать и Talend Data Preparation.

Для Data Quality процесс выглядит так:

1️⃣Сначала выбирается источник данных по аналогии с описанным ETL-процессом.

2️⃣Затем делается профилирование через отдельный модуль Data Profiling вне Job'а (сколько null, дубликатов, какие min, max, avg и т.д.)

3️⃣Потом настраиваются правила качества данных (оператор tRule). Позволяет задавать требования по null, формату, разные числовые ограничения и т.п.

4️⃣В конце через Talend можно вывести отчет по проверке данных на DQ.

Data Preparation во множестве ситуаций можно делать через обычный ранее упомянутый tMap. После подключения к источнику через tMap можно очищать данные (преобразовывать в нужный формат, удалять дубликаты...), заполнять пропуски и дополнять данные (например, добавлять маппинг)

Как провести корреляционный анализ

Допустим, мы имеем датасет с банковскими транзакциями с информацией по клиентам, суммам, балансу, времени и т.д.

На примере исследования гипотезы "Чем больше средний баланс клиента, тем больше его суммарные расходы" (упоминалось ранее) опишу, как можно проводить корреляционный анализ с помощью Python.

1️⃣Изначально посчитаем средний баланс и суммарные траты для каждого клиента c помощью обычной группировки (если нужно, то можно задать ограничение на временной период):

import pandas as pd
file_path = 'ПУТЬ.../bank_transactions.csv'
df = pd.read_csv(file_path)

stats = df.groupby('CustomerID').agg({
    'CustAccountBalance': 'mean',       
    'TransactionAmount': 'sum'   
}).rename(columns={
    'CustAccountBalance': 'avg_balance',
    'TransactionAmount': 'total_spent'
})

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

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

from scipy.stats import spearmanr
corr, pval = spearmanr(stats['avg_balance'], 
                       stats['total_spent'])
print(corr, pval)

Получим 2 числа: саму корреляцию и p_value. И если корреляция положительная и p_value < 0.05, значит, гипотеза поддерживается данными: есть статистически значимая связь между средним балансом и суммарными расходами. И, соответственно, чем выше коэффициент корреляции, тем связь будет сильнее.

3️⃣Для проверки гипотезы первых 2 пунктов может быть и достаточно. Но не лишним также будет еще построить тренд, чтобы посмотреть, как именно реагируют одни данные на изменения других.

Для этого разделим датасет на сегменты по ранее рассчитанным средним балансам, условно на 5 групп:

stats['balance_group'] = pd.qcut(stats['avg_balance'], 
q=5, labels=['Q1 (низкий)', 'Q2', 'Q3', 'Q4', 'Q5 (высокий)'])

group_stats = stats.groupby('balance_group').agg({
    'avg_balance': 'mean',
    'total_spent': 'mean'
}).round(2)

print(group_stats)

Важно разделять сегменты по признакам из гипотезы (баланс), а не по, например, демографическим критериям.

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

4️⃣Теперь считаем сам тренд. Для этого задаём группу Q1 как базис (самый низкий средний баланс) и считаем отношение суммы транзакций одной группы к другой (Q1/Q1, Q2/Q1, Q3/Q1 и т.д.):

base_spent = group_stats['total_spent'].iloc[0]
trend = []

for i in range(len(group_stats)):
    ratio = group_stats['total_spent'].iloc[i] / base_spent
    trend.append(ratio)

print(trend)

Результат получится в виде списка из 5 чисел, который всегда будет начинаться с 1. Он может многое показать:

▶️Например, если мы имеем плавный рост [1.0, 1.3, 1.7, 2.2, 3.0] (или снижение), то можно сделать вывод, что рост баланса стабильно влияет на изменение трат во всех диапазонах

▶️Резкий скачок (или снижение) [1.0, 1.8, 2.0, 2.1, 2.2] может показать, переход между какими группами наиболее влиятелен

▶️Если есть что-то наподобие такого [1.0, 1.2, 1.1, 1.8, 1.9] (небольшой рост, снижение, резкий скачок), то значит, что внутри датасета скрыты какие-то другие факторы, которые влияют на траты сильнее, чем рост баланса

▶️Если изменения незначительны [1.0, 1.1, 1.0, 1.2, 1.3], то, очевидно, никакой связи тут нет

Qlik Sense

Qlik Sense — это BI-инструмент для визуализации данных и аналитики. Как и множество других инструментов, Qlik Sense позволяет создавать интерактивные дашборды с диаграммами, таблицами и графиками, а также строить гистограммы, линейные графики, карты и т.д.

Qlik Sense часто сравнивают, например, с Tableau, и не просто так: с его помощью точно так же можно подключаться к большому количеству самых разных источников данных (реляционные БД, Excel, CSV, веб-страницы, инструменты Apache, Teradata, Salesforce и т.п.). Так же поддерживается и питон с SQL, а еще есть возможность шерить друг с другом дашборды и отчеты.

🟠Но у Qlik Sense есть уникальная черта - это ассоциативная модель. Это движок, который автоматически связывает данные. Условно, если мы подключились к БД и вывели несколько таблиц, то Qlik Sense будет анализировать поля в таблицах и искать совпадающие значения. Найдя одни и те же поля в этих таблицах (например, client_id), Qlik Sense свяжет их в граф связей (грубо говоря, это как автоматически прописанные джоины, которые собирают, насколько это возможно, все таблицы из БД воедино).

В рамках этого примера, если мы просто зададим какой-либо client_id, то сможем увидеть всю информацию по нему, которая прямо или косвенна раскидана по разным таблицам в БД. И вдобавок к этому, благодаря функционалу, мы еще сможем не только понять, как связаны данные, но и то, как они НЕ связаны (например, если клиент не покупает определенный товар).

Очевидно, что в этом всем есть 2 проблемы:

▶️Нужно учитывать то, что для выявления связей нужны исключительно качественные данные, а именно такие, где все нужные столбцы названы идентично.

▶️И еще нужно учитывать то, что Qlik Sense может построить и лишние, не совсем логичные, связи. Для этого желательно грузить не всю БД целиком, а только нужные таблицы.

Но если по какой-либо причине автоматическое построение связей работает некорректно, то можно настроить связи вручную. Это можно делать как и через интерфейс, так и через "Qlik-SQL" перед загрузкой данных. Во втором случае можно поступать по-разному: либо прописать джоины вручную:

Sales:
LOAD * FROM [ПУТЬ К ИСТОЧНИКУ 1];

Customers:
LOAD * FROM [ПУТЬ К ИСТОЧНИКУ 2];

// Соединяем по CustomerID
Join (Sales)
LOAD CustomerID, CustomerName
FROM [ПУТЬ К ИСТОЧНИКУ 2];

либо же создать мэппинг:

// Создаём справочник
CustomerMap:
MAPPING LOAD
    CustomerID,
    CustomerName
FROM [ПУТЬ К ИСТОЧНИКУ 1];

// Применяем к основной таблице
Sales:
LOAD
    OrderID,
    CustomerID,
    ApplyMap('CustomerMap', CustomerID, 'Unknown') as CustomerName
FROM [ПУТЬ К ИСТОЧНИКУ 2];

(в этом примере для отображения CustomerName напротив CustomerID)

Source-to-Target (S2T) Mapping для миграции данных

S2T-маппинг (Source-to-Target Mapping) — это сопоставление полей исходных данных (source) с целевыми полями (target) с описанием правил преобразования. Это важный этап ELT-процессов, который используется в контексте миграции данных (переносе данных, например, из одной СУБД в другую) и построении витрин данных.

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

Для целей такого маппинга создаются таблицы, которые могут быть как и просто обычной документацией в Word/Excel/PDF, так и быть, например, SQL-таблицами, с которыми можно взаимодействовать на уровне запросов.

Создание таких таблиц - процесс достаточно творческий, они могут иметь разные атрибуты и содержание. В одном из достаточно типовых вариантов S2T таблица помимо атрибутов source и target может включать в себя:

▶️Правило преобразования (может быть SQL-функцией, Python-кодом или бизнес-логикой)

▶️Тип данных в атрибутах source и target

▶️Логику обработки NULL-значений

▶️Связи между таблицами

▶️Источник данных

▶️Частота обновления

▶️Владелец данных

Логика заполнения этих таблиц следующая. Например, мы захотели перенести данные с oracle на postgre. Очевидная проблема - это разный SQL-синтаксис и разные типы данных в этих СУБД. При этом нужно еще учитывать существующие связи между таблицами.

1️⃣Создавая в этой ситуации таблицу маппинга, мы пропишем в ней атрибут Source (Oracle), который будет содержать данные о столбцах в источнике в формате "schema.table.column". Аналогично поступим и с атрибутом Target (Postgre), который будет содержать информацию о конечном назначении столбцов из источника.

2️⃣Далее пойдет атрибут преобразования, условно Transformation Rule. В него можно прописать любые самые разные используемые sql-запросы (и не только sql-запросы) на диалекте источника (в случае, если перенос данных происходит напрямую между СУБД), в данном случае на оракле, по типу UPPER(column), to_char(column), ROUND(column, n), CONCAT(a, b) и любую другую, в том числе намного более сложную, используемую логику.

3️⃣В атрибуте Data Type, как правило, одновременно прописываются типы данных как и source, так и target столбцов:
Oracle: NUMBER(10)
PG: INTEGER

4️⃣Что касаемо обработки null-значений, здесь всё те же самые разные sql-запросы на диалекте источника, как и в Transformation Rule, например, NVL(column, 'N/A').

5️⃣Связи между таблицами обычно отражаются путем указания первичных (PK) и внешних (FK) ключей напротив столбцов с айдишниками. Например, есть у нас в оракле связанные между собой таблицы "category" и "sales", которые мы хотим перенести на постгре, предварительно создав там эти же таблицы.

Пишем сначала про перенос category:

source ▶️ category_id; target ▶️ category_id; тип связи ▶️ PK. Это означает, что category_id является первичным ключом в оракле, и что в постгре этот столбец тоже должен быть первичным ключом.

Теперь перенос sales:

source ▶️ sales_id; target ▶️ sales_id; тип связи ▶️ PK.

source ▶️ category_id; target ▶️ category_id; тип связи ▶️ FK -> category_id. Это будет означать, что sales_id из sales можно связать по category_id c category (category_id есть как и в sales, так и в category).

6️⃣Ну и наконец в атрибуте "Источник данных" принято указывать базу данных, например, Oracle PROD_DB. Атрибуты "Частота обновления" и "Владелец данных" заполняются произвольно.

Триггеры в SQL

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

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

Можно задать триггеры, которые будут срабатывать при изменении данных (INSERT, UPDATE, DELETE), а также при изменении структуры БД и при возникновении системных событий.

Общий вид запроса на создание триггеров:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
[WHEN (condition)]
DECLARE
-- объявление переменных
BEGIN
-- тело триггера
EXCEPTION
-- обработка исключений
END;

Посмотреть созданные триггеры можно в таблице user_triggers (подробнее про метаданные).

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

Пример создания:

CREATE OR REPLACE TRIGGER C##username.trg_check
BEFORE INSERT OR UPDATE ON C##username.table
FOR EACH ROW
BEGIN
IF :new.amount < 100 THEN
RAISE_APPLICATION_ERROR(-20001, 'Маленькое значение');
END IF;
END;

Этот триггер создается в контексте отдельного пользователя (username), не затрагивая пространство SYS. Он действует перед вставкой или обновлением значений в таблице и предназначен для проверки поля amount (amount < 100) в таблице table.

Иными словами, если мы захотим вставить в поле amount значение меньше 100, то сработает триггер и не даст нам этого сделать.

🟠Еще один пример триггера. На этот раз он запрещает пользователю удаление таблиц:

CREATE OR REPLACE TRIGGER C##username.trg_prevent_table_drop
BEFORE DROP ON SCHEMA
BEGIN
IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
RAISE_APPLICATION_ERROR(-20000, 'Удаление таблиц запрещено!');
END IF;
END;

Теперь при любом запросе по типу

DROP TABLE table_name

будет срабатывать триггер и специально вызывать ошибку.

Отключить/включить триггеры можно так:

ALTER TRIGGER trg_name DISABLE --или ENABLE, чтоб включить

Проверить, включен ли триггер или нет, можно все в той же таблице user_triggers.

Informatica PowerCenter

Informatica PowerCenter — это ETL-инструмент, предназначенный для извлечения, преобразования и загрузки данных из разных источников в целевые хранилища, витрины и аналитические системы.

Поддерживает очень много источников данных: реляционные СУБД, файлы, облачные сервисы, продукты Apache и т.д.

Также поддерживает параллельную обработку, способен оптимизировать загрузку и работать с большими данными.

В контексте метаданных можно создавать S2T Mapping, отслеживать происхождение данных с помощью Data Lineage (базово подойдет Metadata Manager, расширенно - Informatica Enterprise Data Catalog) и обеспечивать Data Quality (нужна интеграция с Informatica Data Quality).

🟠Организация ETL в PowerCenter имеет базово следующую логику:

[Source] → [Transformation (опционально)] → [Target]

Выглядит это так. Необходимо создать репозиторий в Repository Manager, подключить к нему источник данных и потом добавить нужные таблицы в категории Source и Target. В Informatica передадутся именно метаданные, а не сами данные. В Informatica PowerCenter Designer создается маппинг, куда потом просто добавляются метаданные из Source и Target. Сопоставление полей происходит автоматическим образом по принципу равенства их названий, но можно отключить Auto Link и создавать связи вручную или через Transformation. Существует много разных видов Transformation: Expression, Aggregator, Data Masking, Filter, Rank, Router и т.д. Остановимся на Expression и Aggregator:

▶️Expression работает по принципу построчной обработки: может делать CONCAT, IF, арифметические операции, SUBSTR, ROUND, DECODE и т.д. Для работы маппинга по такому принципу на вход Expression надо задать таблицу Source, на выход - таблицу Target, и связать выходные поля с полями целевой таблицы.

▶️Aggregator работает по принципу группировки наподобие GROUP BY. Как и в Expression, на вход идет Source-таблица. После этого можно делать преобразования по типу SUM, AVG, MAX, MIN, COUNT и т.п. После этого надо связать полученные значения с Target.

Для дальнейшего переноса данных между БД по созданному маппингу в Informatica PowerCenter Workflow Manager нужно будет создать Workflow, в него добавить маппинг, создать связь между Workflow и Mapping и затем просто запустить Workflow. После завершения процесса данные из source-таблицы перенесутся в target-таблицу согласно маппингу.

Для получения документации по S2T-Mapping можно использовать Metadata Manager. В нее могут включиться: Source, Target, связи между таблицами, источник данных, преобразования в Transformation (но внешние Python или SQL-скрипты не будут автоматически документированы, их нужно описывать вручную), частота обновления (через Workflow Manager), владелец данных (через Enterprise Data Catalog).

🟠Data Lineage осуществляется, например, через ранее упомянутый Metadata Manager. Сервис входит в состав PowerCenter и представляет собой веб-приложение для просмотра, анализа и управления метаданными, но может показать Data Lineage только в пределах PowerCenter, то есть по описанной ранее логике ETL: Source → Transformation → Target, а также связи, заданные вручную. При создании маппинга в PowerCenter Designer, метаданные сохраняются в репозитории. Metadata Manager анализирует эти метаданные и визуализирует их.

Для Data Lineage за пределами PowerCenter используется отдельный продукт - Enterprise Data Catalog. Чтоб через него отследить происхождение данных в том числе и для PowerCenter - это уже отдельная история, но в двух словах для этого надо создать Connection для PowerCenter с указанием хоста, порта, логина и пароля. Затем добавить Scan Profile и выбрать объекты (маппинги, сессии, workflows). После этого запускается сканирование. В итоге получим полный путь от источника до цели с учетом преобразований и бизнес-атрибутов.

🟠Похожая ситуация и с Data Quality, для этого тоже нужен отдельный продукт - Informatica Data Quality, который подключается к PowerCenter, что позволит в рамках ETL-процессов обеспечить качество данных. Но простые проверки можно осуществлять и с помощью встроенных Transformation.

Аномалии в данных

Аномалии (или выбросы) — это наблюдения, которые значительно отличаются от остальных данных и могут указывать на ошибки, мошенничество, редкие события или другие интересные явления.

Обычно различают 3 типа аномалий:

▶️Точечные аномалии — отдельные точки данных, сильно отличающиеся от остальных (например те, которые явно выделяются из общей массы).

▶️Контекстные аномалии — точки, которые выглядят нормально в общем контексте, но аномальны в конкретном подмножестве (например, температура +30°C зимой).

▶️Коллективные аномалии — группа точек, которая сама по себе нормальна, но её появление аномально (например, что-то однотипное, что часто повторяется).

🟠Чтобы выявить аномалии, можно использовать:

▶️Статистические методы (например, считать Z-score или разницу между перцентилями)

▶️Методы машинного обучения (алгоритмы наподобие k-means, isolation forest или метода ближайших соседей)

▶️Временные ряды (считать скользящее среднее, стандартное отклонение, использовать специальные библиотеки в python по типу Prophet и т.п.).

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

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

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

import numpy as np
from scipy import stats

#Предварительно надо передать в переменную data 
#очищенные данные только о размере транзакций, 
#условно 1 колонка из excel/БД

result = stats.anderson(data, dist='norm')

if result.statistic > result.critical_values[2]:
    print("Распределение не нормальное")
else:
    print("Распределение нормальное")

Стоит отметить, что result.critical_values[2] - это проверка на уровень значимости 5% (наиболее часто используемый). Если по какой-то причине нужен другой уровень значимости, то можно использовать другие индексы: [0]=15%, [1]=10%, [3]=2.5%, [4]=1%.

Далее, в зависимости от полученных результатов, применим Z-score (если распределение нормальное) :

z_scores = stats.zscore(data)
anomalies = data[np.abs(z_scores) > 3]

print(anomalies)

Или же будем считать межквартильный размах (если распределение ненормальное):

Q1 = np.percentile(data, 25)
Q3 = np.percentile(data, 75)
IQR = Q3 - Q1

k = 1.5
low = Q1 - k * IQR
up = Q3 + k * IQR

out = data[(data < low) | (data > up)]

print(out)

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