Финансовые данные - Хранение данных о комиссиях маркетплейсов и платежных систем

Финансовый блок в DWH для электронной коммерции охватывает две ключевые модели: комиссии, взимаемые маркетплейсами, и платежные сборы, начисляемые платежными системами. Эти данные необходимы для формирования финансовой отчетности, сопоставления выручки и ревизии расчетов с партнерами. В условиях многоконтурной экосистемы данные поступают из разных источников, в разных единицах измерения и с различными правилами начисления. Эффективное хранение и обработка таких данных требуют продуманной архитектуры, единообразной модели данных, устойчивой инфраструктуры интеграции и строгого контроля качества и безопасности.

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

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

Архитектура и целевые схемы хранения

Стратегия хранения финансовых данных в DWH строится на трёх уровнях обработки: staging-броня (Bronze), очищенный слой (Silver) и готовые аналитические витрины (Gold). Такая схема обеспечивает прозрачность происхождения данных, облегчает трассировку ошибок и упрощает соблюдение требований аудита. В контексте комиссий маркетплейсов и PSP важно не только хранить итоговую сумму, но и сохранять исходные источники, курсы валют, даты начисления, а также любые корректировки и возвраты.

Bronze-слой служит входной точкой для всех источников: API ответов маркетплейсов, выгрузки PSP, подписки на события и CSV-отчеты. Здесь сохраняются "как есть" данные, включая поле времени, идентификаторы источника, валидность заполнения и возможные пропуски.
Silver-слой концентрирует бизнес-правила и нормализацию: единицы валют, единый формат дат, консолидацию по фактору времени, сопоставление идентификаторов мерчанта и платежной системы, привязку к календарю.
Gold-слой предоставляет готовые, часто используемые витрины: общие суммы комиссий по marketplace и по PSP за заданный период, агрегаты по мерчантам, по странам/региону, детализированные показатели по валютам, каналы продажи и типы транзакций. Здесь применяются итоговые вычисления, бизнес-метрики и подготовка к отчетности.

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

В качестве аналитической основы целесообразна звездная модель: fact_commission и набор размерных таблиц (merchant, marketplace, payment_system, currency, date). Это обеспечивает простые и эффективные запросы и поддерживает высокий уровень агрегаций.

Для целей аудита и изменения источников можно хранить исторические ленты изменений в отдельном Data Vault-объекте или в расширениях фактной таблицы с версионностью. Такой подход облегчает реконструкцию событий спустя месяцы и годы.

-- Пример DDL: базовая звездная модель для комиссий
CREATE TABLE dim_date (
  date_id DATE PRIMARY KEY,
  year INT,
  month INT,
  day INT,
  quarter INT,
  is_holiday BOOLEAN
);

CREATE TABLE dim_marketplace (
  marketplace_id INT PRIMARY KEY,
  name VARCHAR(100),
  country VARCHAR(2),
  region VARCHAR(50)
);

CREATE TABLE dim_payment_system (
  payment_system_id INT PRIMARY KEY,
  name VARCHAR(100),
  provider VARCHAR(100),
  settlement_currency VARCHAR(3)
);

CREATE TABLE dim_merchant (
  merchant_id INT PRIMARY KEY,
  external_id VARCHAR(50),
  name VARCHAR(200),
  channel VARCHAR(50)
);

CREATE TABLE dim_currency (
  currency_id INT PRIMARY KEY,
  code VARCHAR(3),
  symbol VARCHAR(5),
  is_base BOOLEAN
);

CREATE TABLE fact_commission (
  commission_id BIGINT PRIMARY KEY,
  date_id DATE REFERENCES dim_date(date_id),
  marketplace_id INT REFERENCES dim_marketplace(marketplace_id),
  payment_system_id INT REFERENCES dim_payment_system(payment_system_id),
  merchant_id INT REFERENCES dim_merchant(merchant_id),
  currency_id INT REFERENCES dim_currency(currency_id),
  order_amount DECIMAL(18,2),
  commission_rate DECIMAL(5,4),
  commission_amount DECIMAL(18,2),
  refunds_amount DECIMAL(18,2),
  net_commission_amount DECIMAL(18,2),
  source_system VARCHAR(50),
  created_at TIMESTAMP,
  processing_id VARCHAR(100)
);

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

Источники данных и интеграции

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

Маркетплейсы обычно предоставляют данные по комиссиям как часть расчетов за продажи. Это может быть API, CSV-отчеты или прямые выгрузки из панели управления. Для единообразия важно определить единый контракт полей: order_id, marketplace_id, date, total_amount, commission_rate, commission_amount, currency, settlement_date, status.
Платежные системы предоставляют сборы за обработку платежей, возвраты и выдачу скидок. Важно синхронизировать время начисления с дата-границей и учесть курсы валют, если платежи идут в другой валюте.
Внешние и внутренние источники различаются по частоте обновления. Для критичных к времени показателей применяют потоковую передачу событий (Kafka, Kinesis) или регулярные инкрементные выгрузки. Для исторического анализа достаточно пакетной загрузки с полной перерасчётной реконструкцией.
Контракты и форматы данных должны быть согласованы между поставщиками данных и командой DWH: набор обязательных полей, кодовые константы статусов, единицы измерения и правила обработки ошибок.

Инструментарий интеграции может включать:

Apache Kafka и Kafka Connect для потоковых данных и CDC.
Airbyte или собственные коннекторы для извлечения данных из маркетплейсов и PSP.
dbt для трансформаций и моделирования данных.
Airflow или Prefect для оркестрации ETL/ELT-процессов.
Хранилища: облачные DWH (Snowflake, BigQuery) или колоночные базы (ClickHouse) в зависимости от требований к задержке обновления и стоимости.

С точки зрения архитектуры важно обеспечить:

Consistency (согласованность): единый механизм сопоставления идентификаторов источников и стандартов календаря.
Idempotence (идемпотентность): повторные загрузки не приводят к дубликатам.
Observability (наблюдаемость): трассируемость данных от источника до аналитической витрины, с понятными метриками качества и задержки.
Resilience (устойчивость): обработка ошибок и повторные попытки без потери данных.
```
-- Пример схемы интеграционных слоёв и ключевых атрибутов источников
CREATE TABLE staging_marketplace_commissions (
  raw_id VARCHAR(100) PRIMARY KEY,
  source_system VARCHAR(50),
  source_timestamp TIMESTAMP,
  order_id VARCHAR(100),
  marketplace_id INT,
  date_str VARCHAR(10),
  amount DECIMAL(18,2),
  commission_rate DECIMAL(5,4),
  currency_code VARCHAR(3),
  country VARCHAR(2),
  status VARCHAR(20),
  extra JSONB
);
CREATE INDEX idx_staging_marketplace ON staging_marketplace_commissions (source_system, date_str);
```
Интеграция подразумевает не только загрузку данных, но и их нормализацию: привязку к календарю, привязку к справочникам по маркетплейсам и платежным системам, перевод валют, привязку к мерчантам и каналам продаж. Эффективная интеграция требует единых правил обработки ошибок, мониторинга задержек и автоматических тестов на валидность полей (например, корректность кодов валют, дат и статусов). В зависимости от объёмов данных можно выбрать гибридный подход с использованием потоковых и пакетных путей, чтобы обеспечить и своевременность, и полноту репортажа.

Модели данных и хранение

Основной аналитический слой строится на понятной и расширяемой схеме: dimension-таблицы для контекстов и fact-таблица для измеряемых величин. Для финансовых данных, связанных с комиссиями и платежами, такая реализация позволяет быстро формировать как операционные, так и управленческие отчёты, а также поддерживать детальные ревизии по каждому источнику.

Dim_date - единый календарь: дата сделки, год, месяц, квартал, признак праздничности и рабочего дня. С помощью даты формируются агрегаты по времени, например, ежемесячные и годовые показатели.
Dim_marketplace - справочник маркетплейсов: идентификатор, название, регион, страна.
Dim_payment_system - справочник платежных систем: идентификатор, название, провайдер, валютообеспечение.
Dim_merchant - справочник мерчантов: внутренний и внешний идентификаторы, наименование, канал продаж.
Dim_currency - справочник валют: код, символ, базовая валюта.

Fact_commission - факт начисления комиссии: ссылки на все измерения, суммы, ставки комиссии, валюта, денежные показатели и вспомогательные поля по источнику.

-- Пример DDL для фактов и размерностей (расширение к предыдущему примеру)
CREATE TABLE dim_merchant (
  merchant_id INT PRIMARY KEY,
  external_id VARCHAR(50),
  name VARCHAR(200),
  channel VARCHAR(50)
);

CREATE TABLE dim_date (
  date_id DATE PRIMARY KEY,
  year INT,
  month INT,
  day INT,
  quarter INT
);

CREATE TABLE fact_commission (
  commission_id BIGINT PRIMARY KEY,
  date_id DATE REFERENCES dim_date(date_id),
  marketplace_id INT REFERENCES dim_marketplace(marketplace_id),
  payment_system_id INT REFERENCES dim_payment_system(payment_system_id),
  merchant_id INT REFERENCES dim_merchant(merchant_id),
  currency_id INT REFERENCES dim_currency(currency_id),
  order_amount DECIMAL(18,2),
  commission_rate DECIMAL(5,4),
  commission_amount DECIMAL(18,2),
  refunds_amount DECIMAL(18,2),
  net_commission_amount DECIMAL(18,2),
  source_system VARCHAR(50),
  created_at TIMESTAMP
);

В описании модели важно обеспечить возможность детализации по источнику и каналу. Часто целесообразна поддержка версий источников и атрибутов: например, marketplace_version, PSP_version или currency_rate_version. Это позволяет реконструировать логику расчётов и корректировки без изменения уже сохранённых фактов.

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

создать Dim_currency как центральный узел конвертации; хранить код валюты и базовую валюту (например, TRY / USD) для единообразной агрегации;
поддерживать FX-слой, который обновляется на ежедневной основе или по расписанию, и хранить курс конвертации на дату операции;
хранить в фактной таблице исходную валюту и итоговую конвертированную сумму, чтобы обеспечить прозрачность и аудит.

Кроме того, полезно хранить дополнительные поля: source_system (какой источник принёс запись), processing_id (уникальный идентификатор обработки), status (например, "ok", "failed", "adjusted"). Эти данные упрощают диагностику и аудит.

Обработка данных: валюты, коррекции и reconciliation

Ключевые задачи при обработке данных о комиссиях и платежах включают:

привязку транзакций к календарю и контекстам (merchant, marketplace, currency);
учет валютных курсов и корректировок на момент выполнения расчета;
обработку возвратов, возвратов комиссий, refunds и chargebacks, которые изменяют итоговую сумму комиссии;
reconciliation между данными маркетплейса и платежной системы и внутренними расчетами.

Алгоритм консолидации может выглядеть так:

Загружаем сырые данные по комиссиям из источников, нормализуем поля и приводим даты к единому формату.
Приводим валюты к базовой валюте через FX-курсы на дату сделки; сохраняем исходную и конвертированную суммы.
Применяем правила возвратов и корректировок: суммируем возвраты и добавляем в соответствующие поля фактов.
Рассчитываем чистую комиссию: net_commission_amount = commission_amount - refunds_amount.
Агрегируем данные по нужным разрезам для витрин Gold: по дате, marketplace, merchant, currency, и т.д.

Поддерживаем версионность и аудит изменений источников: фиксируем processing_id и source_system для трассируемости.

-- Пример SQL-вычисления конвертации и чистой комиссии
## WITH fx AS (
  SELECT currency_code, date_id, rate_to_base
## FROM fx_rate
  WHERE date_id = CURRENT_DATE -- для примера; на практике по нужной дате операции
),
trans AS (
  SELECT c.commission_id, c.date_id, c.currency_id, cur.code AS currency_code,
         c.commission_amount, c.refunds_amount,
         (c.commission_amount - c.refunds_amount) AS net_commission
## FROM fact_commission c
  JOIN dim_currency cur ON c.currency_id = cur.currency_id
  LEFT JOIN fx_rate f ON f.currency_code = cur.code AND f.date_id = c.date_id
)
## SELECT commission_id, date_id, currency_code,
       commission_amount, refunds_amount, net_commission,
       CASE WHEN fx.rate_to_base IS NOT NULL THEN net_commission * fx.rate_to_base ELSE NULL END AS net_commission_base
## FROM trans
LEFT JOIN fx_rate fx ON fx.currency_code = currency_code AND fx.date_id = date_id;

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

Ключевые аспекты контроля качества на этапе обработки:

проверка целостности дат и ссылок на dimension-таблицы (date_id, marketplace_id, merchant_id и т.п.);
верификация сумм: commission_amount должна равняться order_amount * commission_rate (с учетом политики округления);
проверка отклонений между данными из маркетплейса и PSP, чтобы выявлять незавершенные или дублированные записи;
аудит входных полей: код валюты, статус транзакции, channel и источник данных.

Управление качеством данных и аудит

Управление качеством данных - это не разовое событие, а непрерывный процесс. Финансовые данные требуют высокого уровня точности и прозрачности, поэтому внедряются следующие практики:

Логирование стадий обработки: от загрузки до финальных витрин. Зачем? Чтобы быстро локализовать причину несоответствия и восстановить данные.
Контроль целостности: периодически выполняются проверки соответствия между исходным набором строк и результирующим количеством записей в витрине.
Валидаторы на уровне ETL/ELT: правила в dbt or аналогичной системе валидируются при каждом прогоне, включая проверки на нулевые значения, корректность связей и валидность кода валют.
Метаданные и каталогизация: хранение описаний источников, версий схем, даты обновления и зависимостей между таблицами, чтобы обеспечить трассируемость.
Мониторинг задержек и ошибок: алерты на задержку между источниками и витриной, а также на неожиданные статусы данных (например, записи без currency_kod в течение периода).
Документация бизнес-правил: фиксация того, как учитываются возвраты, как применяются FX-курсы, какие ставки комиссий используются, и как формируются итоговые цифры.

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

Безопасность, соответствие и эксплуатация

Финансовые данные несут повышенные требования к безопасности и соответствию. При проектировании DWH для комиссий и платежей следует учитывать:

Роли и доступ: принцип наименьших прав, ограничение доступа к данным по функциональным ролям (финансовая аналитика, ревизия, операциям, управлению данными).
Архитектура секьюрности: шифрование данных в покое и в транзитe, использование безопасных протоколов API и хранилищ, журналирование доступа и изменений.
Защита PII и конфиденциальности: минимизация хранения персональных данных, маскирование по необходимости, настройка политики хранения данных.
Мониторинг и аудит: создание журналов аудита доступа к критическим данным, хранение копий изменений, возможность восстановления состояния системы до конкретной точки времени.
Соответствие требованиям: соблюдение регуляторных требований (например, аудит, хранение финансовых документов, архивирование) и внутренних политики компании.

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

Примеры реализации и технологические ссылки

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

PostgreSQL или другой RDBMS на стадии Bronze/Silver для стромя; в качестве аналитического движка - ClickHouse или облачный Snowflake/BigQuery для Gold-витрин.
Apache Kafka как основа потоковых данных и CDC-интеграций.
dbt для трансформаций и документирования моделей данных.
Apache Airflow как оркестратор рабочих процессов.

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

Key takeaways

Финансовые данные по комиссиям маркетплейсов и платежным системам требуют четкой архитектуры, которая поддерживает трассируемость, аудит и консолидацию из разных источников.
Гибридная архитектура хранения (Bronze-Silver-Gold) в сочетании с звездной моделью данных обеспечивает простоту использования и высокую производительность аналитических запросов.
Важная часть - единая датасекция и валюта-конвертация: хранение FX-курсов и конвертация сумм на дату операции для корректной агрегации в базовой валюте.
Интеграции должны следовать контрактам, быть идемпотентными и обеспечивать качественный мониторинг и обработку ошибок.
Контроль качества, аудит и безопасность данных являются постоянными требованиями к эксплуатации DWH в финансовой области.
Наличие четких бизнес-правил и документированной метадаты снижает риски ошибок и упрощает адаптацию к изменениям контрактов и источников данных.
Реализация должна быть ориентирована на управляемость: мониторинг, повторное выполнение, версия источников и автоматическое уведомление о проблемах.

FAQ

Какие схемы данных предпочтительны для хранения комиссий и почему?

В большинстве случаев эффективна звёздная схема с фактами по комиссиям и размерностями: marketplace, payment_system, merchant, currency и date. Она обеспечивает простые и быстрые агрегации по основным бизнес-разрезам. При необходимости можно внедрить гибрид Data Vault 2.0 для исторической трассируемости изменений источников и упрощения миграций.

Как обрабатывать множество валют и курсов?

Вводится Dim_currency и отдельный слой FX-курсов (fx_rate) с привязкой курсов к дате операции. После загрузки данных осуществляется конвертация в базовую валюту, чтобы обеспечить единообразие в аналитике. Необходимо сохранять и исходную валюту, и конвертированную сумму для аудита.

Как учесть возвраты и корректировки в формулах комиссий?

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

Какие источники данных требуют наибольшего контроля качества?

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

Какие техники обеспечивают прозрачность и аудит данных?

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

Какие инструменты лучше использовать для интеграции данных?

Рекомендуется связка Kafka (для потоковых данных) + dbt (моделирование и тестирование) + Airflow/Prefect (оркестрация). В качестве хранилища можно выбирать Snowflake, BigQuery или ClickHouse в зависимости от стоимости и требований к задержке и количеству запросов.

Как обеспечить безопасность и соответствие требованиям?

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

Как тестировать модели и трансформации?

Следует внедрить тесты качества данных на уровне dbt/ETL-ленты: проверки целостности связей, корректности валютных конверсий, диапазоны значений и иерархии агрегатов. Регулярное тестирование с использованием контрольных наборов данных и регрессионного тестирования помогает поддерживать качество при эволюции источников.

Как мигрировать существующую систему в новую архитектуру?

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

Как обеспечить скорость и масштабируемость аналитических запросов?

Выбор колоночного хранилища (ClickHouse или облачный Snowflake) в сочетании с эффективной партицизацией по date и другим ключам, а также использованием агрегированных материалов и Materialized Views. Важно балансировать обновление и чтение, чтобы не возникало узких мест в пиковые периоды продаж.

← Предыдущая статья

Финансовые данные - Формирование таблиц для расчета валовой прибыли по товарам категориям и каналам продаж

Следующая статья →

Финансовые данные - Подготовка данных для анализа unit экономики включая стоимость привлечения клиента и его ценность