制定数据清洗计划

以下为“零售交易与会员数据”清洗计划，面向包含客户年龄、会员级别、订单明细、门店信息的多表数据，重点解决字段命名不统一、重复客户、缺失省市等问题。目标是建立一致的标准、可重复的流程与可度量的质量保障。

一、目标与范围
- 统一命名与数据类型，构建标准化数据字典。
- 去除或合并重复客户，形成唯一客户主档。
- 补全省市字段，提升地理信息完整性。
- 建立跨表一致性、完整性与合理性校验规则。
- 输出可追溯的清洗产物与质量度量。

二、数据清单与分层
- 源数据层（Raw/Bronze）：原始客户、订单、门店、会员等级映射等。
- 标准化层（Clean/Silver）：完成命名、类型、值域、基础填充与去重标记。
- 应用层（Curated/Gold）：统一主键、维度建模（客户维表、门店维表、订单事实表）、质量通过规则的可用数据集。

三、命名与元数据标准（Schema 对齐）
- 建立字段映射表（字段中文名/来源名 -> 标准英文字段名 -> 数据类型 -> 说明 -> 值域/约束）。
- 建议标准字段示例：
  - 客户维表：customer_id（STRING）、name、gender、age（INT）、phone_norm、email_norm、province、city、member_level、member_since（DATE）、status
  - 门店维表：store_id（STRING）、store_name、province、city、district、lat、lng、open_date、status
  - 订单事实表：order_id（STRING）、order_date（DATETIME/DATE+TIME）、customer_id、store_id、sku_id、qty（INT）、unit_price（DECIMAL(12,2)）、discount_rate（DECIMAL(5,4)）、net_amount（DECIMAL(12,2)）、payment_method
- 命名规范：小写+下划线；主键以_id结尾；金额类使用DECIMAL；时间统一ISO-8601。
- 元数据：记录来源系统、抽取时间、数据拥有者与口径说明。

四、类型与值标准化
- 编码与格式：统一UTF-8；日期解析为UTC或统一时区；全角转半角，去除前后空格与不可见字符。
- 分类值对齐：
  - 会员级别：建立标准映射（如 {VIP, GOLD, SILVER, REGULAR}），清理同义/错拼（如 “vip/Vip/VIP会员” -> “VIP”）。
  - 支付方式、性别、门店状态等采用枚举。
- 数值与金额：
  - qty >= 1 且为整数；unit_price >= 0；discount_rate 在[0,1]；net_amount = round(qty * unit_price * (1 - discount_rate), 2)。
- 地理值规范：
  - 省、市使用国家统计局/民政部标准名称；建立别名映射（如 “广西壮族自治区”->“广西壮族自治区”，“广西”->“广西壮族自治区”）。
- 联系方式标准化：
  - 手机：去除空格/破折号/国家码，校验合法号段后保存为11位；邮箱转小写并RFC校验。

五、客户去重策略（主档合并）
- 规则分层（先精确，后模糊），生成survivor记录与merge日志：
  1) 强标识精确匹配：同一 phone_norm 或同一证件号（如有）-> 同一客户。
  2) 准精确组合：name_norm + phone后4位 + email_norm 非空一致。
  3) 模糊匹配（仅在缺少强标识时启用并阈值保守）：
     - 姓名相似度（编辑距离/拼音相似）≥阈值，且出生年或age分箱一致（±1岁），且最近配送地址文本相似度≥阈值，或与同一member_id。
- 冲突解决（Survivorship）：
  - 字段选取优先级：最新更新时间 > 完整度评分 > 数据源可信级别。
  - member_level 选择时间轴上生效期最近/等级最高者，并保留历史（建议SCD Type 2）。
- 产出：
  - customers_clean：唯一customer_id（如新生成uuid），保留master记录。
  - customers_merge_log：old_customer_id -> new_customer_id，合并依据、置信度、时间戳。
- 审慎策略：模糊合并仅在≥高阈值（如0.92）且多证据一致时执行；否则打标pending人工复核。

六、缺失省市填充
- 级联填充策略（由强到弱）：
  1) 订单关联门店：orders.store_id -> stores.[province, city] 填充订单与客户最近订单的省市（到市级）。
  2) 配送/收货地址解析：从订单地址文本解析省市（维护行政区划词典，规则/模型解析）。
  3) 地理坐标反解：若含lat/lng，调用离线行政区划映射表反向地理编码。
  4) 账户历史传播：同一customer_id在T-180天内出现的省市众数/最近一次值。
- 质量保护：
  - 仅当目标字段为空时填充；保留source字段标识填充来源（store/address/geo/history）。
  - 解析置信度低于阈值时不填充，仅打标。
  - 统一省市标准名后再入库；无法确定则留空并记录缺失原因。

七、跨表一致性与业务校验
- 时间约束：order_date 不在未来；order_date ≥ member_since；门店open_date ≤ order_date。
- 引用完整性：orders.customer_id、orders.store_id 必须在维表存在；外键缺失的订单打标为无效并进入修复队列。
- 年龄校验：age在[0, 120]；若存在出生日期与年龄矛盾，优先出生日期，年龄重算。
- 金额一致性：net_amount 与明细一致；若存在税费/优惠券，分摊后重算并留差异阈值告警。
- 会员级别时效：按生效区间联结，确保订单落在相应会员级别区间内。

八、异常与离群值处理
- 规则+统计双重检测：
  - 硬规则：负价、负数量、极端折扣（>90%）直接拦截或打回。
  - 统计法：基于门店-品类维度的IQR/robust z-score，对单价/客单价/频次的离群打标，不直接删除。
- 处理策略：保留原值，新增异常标记与原因列，必要时进入人工复核。

九、隐私与安全
- 对phone/email等PII建立哈希列（如SHA-256+盐）用于去重与建模；明文最小化使用与脱敏展示。
- 全流程记录访问与变更日志，遵循最小权限原则。

十、实施顺序（流水线）
1) 萃取与落地：原始多源统一编码存储，记录批次ID。
2) 规范化预处理：重命名、去空白/全半角、类型转换、分类映射。
3) 联系方式标准化与验证。
4) 门店维表标准化与省市标准化。
5) 订单金额与时间校验、净额重算。
6) 客户去重与主档合并，生成merge日志。
7) 省市缺失填充（按策略级联），打标来源与置信度。
8) 维表-事实表引用一致性校验与修复。
9) 异常检测与标记。
10) 质量度量输出与审计快照；发布到应用层。

十一、质量度量与验收标准
- 完整性：关键字段非空率（customer_id、order_id、store_id、order_date、qty、unit_price ≥ 99.5%）。
- 唯一性：order_id唯一率=100%；customers_clean中customer_id唯一率=100%；去重后重复率<0.5%。
- 一致性：省/市标准名命中率≥99%；金额一致性误差<=0.01。
- 合法性：年龄合规率≥99.9%；折扣率在[0,1]比例≥99.9%。
- 可追溯性：所有修改均有来源标记与批次ID，随机抽检可复现率=100%。

十二、参考实现要点（示例伪代码/SQL）
- 重命名与类型：
  - 通过映射表驱动：select raw.colA as customer_id, cast(raw.age as int) as age, ... from raw_customers raw
- 客户去重（精确+优先级）：
  - 使用窗口函数保留主记录：
    with c as (
      select *,
             row_number() over(partition by phone_norm order by updated_at desc, completeness_score desc) as rn
      from customers_std
    )
    select * from c where rn=1;
  - 模糊：生成候选对（同城/同姓/同邮箱域名）后计算姓名编辑距离与地址相似度，阈值过滤，写入merge_log，经审核合并。
- 省市填充：
  - 通过门店：
    update orders o
    set province = s.province_std, city = s.city_std, geo_source = 'store'
    from stores_std s
    where o.store_id = s.store_id and (o.province is null or o.city is null);
  - 地址解析：基于行政区词典进行规则匹配，解析置信度<阈值则不填充，仅记录候选。

十三、输出与文档
- 数据字典：字段说明、类型、约束、值域与示例。
- 规则库：清洗与校验规则、阈值与变更记录。
- 批处理报告：每批数据的质量指标、缺陷明细、修复建议。
- 审计与回放：按批次ID可完整复现处理链。

备注与实践建议
- 对会员级别采用SCD Type 2保留历史，利于时间穿越分析。
- 地址标准化依赖高质量行政区划映射与别名词典，需持续维护。
- 模糊去重谨慎上线，建议先灰度并引入人工校验闭环。
- 对高量数据采用分布式框架（如Spark）与分区策略；金额统一使用Decimal避免浮点误差。

Data cleaning plan for credit risk modeling (user profile, transaction behavior, delinquency labels, time-series features; with missing values, outliers, and class imbalance)

1) Scope and objectives
- Build an analysis-ready, leakage-free Analytical Base Table (ABT) per customer and observation date.
- Ensure temporal consistency across static profile data, transactional logs, and time-series aggregates.
- Address missingness, outliers, and label/class issues without distorting risk patterns.

2) Data inventory, governance, and identifiers
- Inventory sources: user profile dimensions, transaction fact table, time-series features (precomputed or to be derived), delinquency/DPD labels.
- Standardize keys: define a stable customer_id; validate referential integrity across tables; resolve many-to-one conflicts; deduplicate customers and transactions (same id, timestamp, amount, merchant).
- Timestamps: normalize to a single timezone; ensure monotonicity; resolve daylight-saving anomalies; enforce ISO datetime format.
- Sensitive attributes: remove direct identifiers; tag protected attributes for later fairness review; do not use them in model features unless justified and compliant.

3) Temporal framing and leakage prevention
- Define per-customer index dates t0 (observation date).
- Define windows:
  - Feature window: [t0 - W_feat, t0)
  - Performance window: [t0, t0 + W_perf) to compute delinquency labels (e.g., 30+ DPD within 90 days).
  - Lookback minimum history: enforce a minimum effective history (e.g., ≥3 months) or add a short-history indicator.
- Exclude any data on or after t0 from features. Audit each feature for leakage (e.g., “ever defaulted” if registered after t0).
- Train/validation/test split by time blocks (e.g., rolling or expanding windows) to respect chronology.

4) Label auditing and consolidation
- Formalize delinquency label: specify DPD threshold, cure rules, charge-off handling, and observation alignment.
- Resolve conflicts: if multiple products per customer, define hierarchy (worst status wins or product-level models).
- Remove or relabel records with ambiguous or missing label windows; generate a label_quality flag.
- Ensure class prevalence is measured post-cleaning and per time slice to detect drift.

5) Data conformance and validation checks
- Schema checks: data types, ranges, allowed categories; explicit “Unknown/Other” categories rather than null strings.
- Logical constraints:
  - Age: 18–100; employment_years ≤ age − 14; income > 0 (or well-defined zero for unemployed).
  - Transaction rules: non-negative card payments, no future timestamps, currency normalization to a base currency; consistent sign conventions (debit negative, credit positive).
  - Time-series frequency: verify expected periodicity; mark structural zeros vs true missing.
- Duplicate detection:
  - Customer level: identical PII hash and demographics.
  - Transaction level: same customer_id, timestamp, amount, merchant_id, currency; keep one with highest data_quality score.

6) Missing data strategy
- Diagnose missingness:
  - Quantify per feature and per time slice; visualize missingness matrix.
  - Test MCAR vs MAR/MNAR proxies via missingness indicators correlated with label and key features.
- General rules:
  - Create missingness flags for features with ≥5% missingness or where missingness is informative (e.g., no income reported).
  - Distinguish Not Applicable (structural zero) from Missing; encode structural zeros explicitly.
- Static/categorical features:
  - Low-cardinality: impute “Unknown/Not provided.”
  - High-cardinality: group rare levels into “Other”; if target/WOE encoding used, compute on training folds only.
- Numeric profile features:
  - If near-normal: median imputation.
  - If skewed (income, balances): group-wise median by segment (e.g., employment_type, region) when available; otherwise global median.
  - Optionally model-based imputation (e.g., iterative regression) confined to training folds; monitor variance shrinkage.
- Transaction-derived aggregates:
  - If gaps arise from no activity, treat as zero only when zero implies no transactions; otherwise leave missing and impute with zero plus an inactivity flag.
  - Use last observation carry-forward (LOCF) for slowly varying attributes (e.g., KYC scores), with a max staleness threshold; else set to missing and flag.
- Time-series panels:
  - For regular grids, fill short internal gaps via linear interpolation capped by k periods; beyond that mark missing.
  - For irregular logs, aggregate to windows (daily/weekly/monthly) then impute per above.

7) Outlier and anomaly handling
- Univariate detection:
  - Use robust measures: median ± k*MAD or winsorize at [0.5, 99.5] percentiles per segment (e.g., product, region). Tune k via validation.
  - Apply monotonic transforms for heavy tails (log1p for positive amounts).
- Multivariate and sequence anomalies:
  - On transactions: isolation forest or robust covariance on feature snapshots (e.g., counts, amounts, velocity). Review high-score cases; cap/clip features rather than dropping entire customers when possible.
  - In time series: decompose seasonality/trend; flag spikes using STL residual thresholds; cap derived velocity/volatility features at robust percentiles.
- Rule-based constraints override: remove impossible values (negative age, negative DPD), correct sign inversions by domain rules.

8) Feature harmonization and encoding (cleaning-adjacent)
- Categorical standardization: unify label cases, trim whitespace, map synonyms; maintain a controlled vocabulary for occupation, industry, region.
- Rare category handling: collapse categories with frequency <1% unless known risk signal; record mapping.
- High-cardinality features: hashing or cross-validated target/WOE encoding with strict leakage control and smoothing; maintain out-of-vocabulary handling.
- Scaling:
  - Use robust scalers (median/IQR) for linear models; tree models typically do not require scaling.
  - For probability calibration or distance-based models, consider quantile transforms learned on training only.

9) Class imbalance treatment (cleaning-interface)
- Do not oversample before splitting. Within each training fold:
  - Prefer class weights or focal loss for baseline models.
  - If sampling is needed, use time-respecting under/over-sampling; avoid SMOTE on time-aggregated financial features unless validated (it may distort distribution). If used, apply within fold only and after feature construction.
- Keep evaluation metrics suited to imbalance (PR AUC, recall at fixed FPR, KS). Do not alter label prevalence in validation/test.

10) ABT construction and aggregation
- Per customer and t0, aggregate transactions over the feature window:
  - Monetary: sum, mean, std, max, percentiles; income-normalized ratios.
  - Behavior: counts, active days, inter-transaction times, merchant diversity, category shares.
  - Risk dynamics: rolling deltas, trend slopes, volatility, recency features (e.g., time since last delinquency-free month).
- Align precomputed time-series features to t0; drop any that leak performance-window info.
- Handle short histories by backoff aggregations (e.g., 1m if 3m missing) and include window_length used as a feature.

11) Data splitting and validation design
- Temporal splits:
  - Train: oldest block(s).
  - Validation: subsequent block.
  - Test: most recent block, untouched.
- For hyperparameter tuning: use blocked time-series cross-validation or rolling-origin evaluation.
- Stratify by label within time blocks when feasible to stabilize folds without breaking chronology.

12) Quality assurance and monitoring
- Implement automated checks (e.g., Great Expectations or custom assertions):
  - Completeness thresholds per feature.
  - Range and distribution drift checks between train/val/test (PSI, Jensen-Shannon).
  - Missingness and outlier rates monitored over time.
- Version and log:
  - Raw data snapshot IDs, cleaning code version, parameter files, capping thresholds, imputation statistics per split.
  - Seed control and deterministic pipelines for reproducibility.

13) Documentation and deliverables
- Data dictionary post-cleaning: feature definitions, units, allowed values, imputation method, capping thresholds, leakage audit outcome.
- Cleaning report:
  - Missingness summary before/after.
  - Outlier treatment summary and impact on distributions.
  - Label prevalence and consistency checks over time.
  - Known limitations (e.g., segments with sparse history).
- Reproducible pipeline scripts/notebooks and configuration files per environment (development and production).

Notes on implementation prioritization
- Start with schema validation and leakage audit before any imputation or outlier treatment.
- Treat missingness and outliers with simple, robust rules first; escalate to model-based imputation only when materially beneficial and properly cross-validated.
- Keep transformations minimal and explainable, aligned with credit risk governance and model risk management requirements.

Plan de nettoyage et d’harmonisation des données multi-sources (ads, web, app, commandes, remboursements)

Objectif
- Produire un modèle de données unifié, fiable et traçable, prêt pour l’attribution, l’analyse de parcours, et la mesure de performance commerciale.
- Résoudre les divergences de fuseaux horaires, d’unités monétaires et de nomenclature des canaux.

Périmètre des sources
- Expositions publicitaires: impressions, clics, coûts (multi-régies).
- Web: pages vues, événements, sessions, UTM.
- App: événements (foreground/background, in-app), attribution MMP le cas échéant.
- Commerce: commandes, lignes de commande, paiements, remboursements.

Modèle cible (schéma canonique minimal)
- Faits
  - fact_ad_events: ad_event_id, partner, campaign_id, adset_id, creative_id, event_type, cost_amount_original, cost_currency, event_ts_source, event_ts_utc, channel_canonical_id.
  - fact_web_events / fact_app_events: event_id, user/device ids, event_name, event_params, event_ts_source, event_ts_utc, session_id, channel_canonical_id.
  - fact_orders: order_id, user_id, order_ts_source, order_ts_utc, amount_original, currency, amount_base_ccy, tax, shipping, country, status.
  - fact_refunds: refund_id, order_id, refund_ts_source, refund_ts_utc, amount_original, currency, amount_base_ccy, refund_reason, refund_type (partiel/total).
- Dimensions
  - dim_time (UTC + local).
  - dim_user/device (résolution d’identité).
  - dim_channel (taxonomie harmonisée + mapping versionné).
  - dim_currency_rates (taux FX datés, source ECB/OER, granularité quotidienne ou intrajournalière).

Étapes de nettoyage et normalisation

1) Ingestion et profilage initial
- Définir un contrat de données par source: schéma, types, fuseau attendu, encodage, unité monétaire (centimes vs unités), clés uniques.
- Profiler: taux de nulls, cardinalités, valeurs aberrantes, distribution temporelle, champs de date non parsables, duplicats (via hash des colonnes clés).
- Normaliser les types: timestamps => type temporel, montants => décimal(18,6), identifiants => chaînes normalisées (case, trim).

2) Normalisation des horodatages et fuseaux horaires
- Déterminer le fuseau par enregistrement:
  - Si présent (IANA, ex. Europe/Paris) => utiliser tel quel.
  - Sinon dériver du site/app property, du pays de vente, ou du partner; documenter la règle.
- Convertir systématiquement en UTC en respectant les transitions DST; stocker:
  - event_ts_source (horodatage original + tz_source).
  - event_ts_utc (référence unique pour les jointures/attribution).
- Valider l’ordre des événements par (user/device, event_ts_utc); résoudre les inversions dues aux horodatages locaux erronés via règles conservatrices (pas de ré-écriture agressive sans preuve).
- Gérer les ambiguïtés:
  - Dates sans tz: rejeter en quarantaine ou annoter tz_imputed=true.
  - Timestamps en secondes vs millisecondes: détecter par plage temporelle et normaliser.
- Tests: 100% des événements avec event_ts_utc non nul; absence d’horodatages futurs/antédiluviens au-delà d’un seuil (ex. >7 jours futur, <10 ans passé).

3) Normalisation monétaire
- Identifier le code devise par enregistrement:
  - Si manquant, inférer depuis le marché, le flux de paiement ou la boutique; marquer currency_imputed=true.
- Harmoniser l’unité:
  - Détecter centimes vs unités; convertir en unités monétaires de base; conserver amount_original et scale_applied.
- Convertir en devise de référence (ex. EUR) via dim_currency_rates:
  - Taux datés “as-of”: joindre sur la date de transaction (UTC) et la devise; pour l’intraday, choisir le dernier taux connu avant l’événement.
  - Gérer week-ends/jours fériés: utiliser le dernier taux disponible.
  - Documenter arrondi (ex. bankers rounding, 4 décimales).
- Convention de signe:
  - Commandes: montants positifs; Remboursements: montants négatifs dans les faits, ou champ explicit refund_flag + signe négatif pour amount_base_ccy.
- Taxes et frais:
  - Séparer TVA, shipping, discounts; s’assurer que refund reflète les composantes remboursées.
- Tests: somme des montants par commande = somme lignes; cohérence devise/origine; % de conversions FX via taux valides = 100%.

4) Harmonisation des canaux et taxonomie
- Définir dim_channel (versionnée): channel (paid/owned/earned), source, medium, subchannel, partner, campaign_type.
- Construire une table de mapping:
  - Normaliser casse, trim, enlever caractères parasites.
  - Règles de parsing UTM (utm_source, utm_medium, utm_campaign, utm_content, utm_term).
  - Synonymes et erreurs fréquentes (ex. “FB”, “facebook”, “Face book” => “Facebook Paid Social”).
  - Fallback: “Unknown/Unmapped” avec reason_code.
- Appliquer le mapping à ads/web/app; stocker channel_canonical_id et mapping_version.
- Tests: couverture de mapping > 98%; liste blanche de mediums; alerte sur nouvelles valeurs non mappées.

5) Résolution d’identité (user/device)
- Clés déterministes: user_id (login), hashed_email (salted), device_id (IDFA/GAID), cookie_id, publisher_click_id.
- Graphe d’identités:
  - Relier identifiants via événements de liaison (login, deep link, same-session).
  - Scoring probabiliste optionnel (empreintes: IP, UA, langue) uniquement si conforme RGPD/consentement.
- Générer un person_id canonique stable; conserver l’historique des fusions/splits.
- Tests: unicité person_id; pas de composantes déconnectées spirales; taux de stitching attendu par plateforme.

6) Déduplication et idempotence
- Événements ads/web/app:
  - Clé de déduplication: coalesce(event_id_source, hash(source, event_ts_source, user/device, event_name, params_normalisés)).
  - Fenêtre temporelle: ex. 10 min pour considérer des doublons quasi-identiques.
- Commandes:
  - order_id comme clé; versionner les updates (SCD2) avec status_ts.
- Remboursements:
  - refund_id si disponible; sinon clé naturelle (order_id, amount, refund_ts_utc, reason) avec tolérance.
  - Empêcher double comptage des remboursements partiels.
- Tests: taux de doublons résiduels ~0; conservation des premiers événements (watermark).

7) Cohérence commandes–remboursements
- Contraintes référentielles: chaque remboursement doit référencer un order_id existant.
- Bornes:
  - Remboursement total <= montant payé net; cumul partiels <= net.
- Alignement temporel:
  - refund_ts_utc >= order_ts_utc; flag si anomalie.
- Tests de bilan: revenu net = ventes – remboursements par période/devise/base_ccy.

8) Valeurs manquantes, aberrations, filtrage de trafic invalide
- Valeurs manquantes: imputation contrôlée avec indicateurs; pas d’imputation pour montants.
- Outliers: règles robustes (IQR, MAD) par métrique; examiner avant exclusion.
- Bot/IVT:
  - Exclusions basées sur user-agent, IP datacenter, taux d’événements extrêmes, listes MMP/IAS/Moat si disponibles.
- Tests: rapport d’exclusion, pourcentages dans des bornes attendues.

9) Sessionisation et alignement web/app
- Règles web: session de 30 min d’inactivité, reset sur changement de campagne.
- Règles app: foreground/background, timeout configurable (ex. 5 min).
- Sessions cross-device: seulement pour utilisateurs authentifiés.
- Tests: distribution de durées/sessions cohérente; taux de rebond plausible.

10) Contrôles qualité automatisés et observabilité
- Contraintes:
  - Unicité des clés (event_id, order_id).
  - Non-nullité des champs critiques (event_ts_utc, currency, amount).
  - Domaines autorisés (devise ISO 4217, IANA TZ).
  - Intégrité référentielle (FK vers dimensions).
- Anomalies:
  - Surveiller volumes, coûts, CPC/CPA, taux de remboursements; détection par modèles saisonniers.
- Fraîcheur:
  - SLA par source; alertes si retard > seuil.
- Outils suggérés: tests de données (Great Expectations/dbt tests), métriques de pipeline, catalogage (OpenMetadata/Amundsen).

11) Gouvernance, sécurité et conformité
- Documentation du dictionnaire de données et de la taxonomie des canaux.
- Gestion des schémas évolutifs via contrats (Avro/JSON Schema) et versioning.
- Protection des PII: hashing salé, minimisation, contrôle d’accès, logs d’audit.
- Conformité consentement (TCF, ATT) et filtrage conditionnel des identifiants.

12) Critères d’acceptation (exemples quantifiables)
- 100% des horodatages convertis en UTC avec tz_source renseigné.
- 100% des montants normalisés; >99% convertis en devise de base avec taux valides.
- >98% des événements mappés à un canal canonique; 0% de nouvelles valeurs non mappées sans alerte.
- <0,1% de doublons post-nettoyage.
- Équation de bilan ventes–remboursements respectée à +/- 0,1% par jour.

13) Stratégie d’implémentation
- Chargements incrémentaux idempotents (watermarks, CDC si possible).
- Staging par source -> bronze (brut, horodatage) -> silver (normalisé: tz/devise/canaux) -> gold (modèle analytique).
- Recalculs rétroactifs contrôlés lors de changements de mapping canaux ou de taux FX historiques (versionnement).
- Tests CI/CD de schéma et de données; déploiement par lots.

Pièges fréquents à éviter
- Mélange secondes/millisecondes causant des dates 1970/5138.
- Application d’un taux FX du jour de traitement plutôt que du jour de transaction.
- Confusion centimes vs unités monétaires.
- Double comptage des clics/attributions multi-régies non dédupliquées.
- Sessions scindées par un changement d’UTC mal géré.

Livrables
- Schémas cibles (DDL) et contrats de données par source.
- Tables de mapping: dim_channel et currency_rates (avec sources et périodes de validité).
- Suite de tests de qualité et tableaux de bord d’observabilité.
- Guide d’exploitation et de reprise (backfill, reprocessing, versioning).

Ce plan vise une normalisation stricte des temps, des devises et des canaux tout en garantissant l’intégrité transactionnelle et la traçabilité, prérequis pour des analyses multi-sources robustes.