数据验证规则编写

年龄字段数据验证规则（Age Validation Rules）

1. 定义与适用范围
- 字段名称：age
- 语义：以参考日期为准的整岁年龄（单位：年），不存储小数。
- 参考日期：优先使用记录的有效日期/快照日期（例如 as_of_date、record_date）；如缺失则使用系统当前日期。
- 若存在出生日期（date_of_birth, DOB），年龄应为派生值；建议不长期存储冗余年龄而在展示/计算时派生。

2. 基础校验（字段级）
- 类型约束：整数（Int），不允许浮点或字符串；输入规范化后仍需为纯数值。
- 非空约束：按业务规定。若必填，则不允许 NULL；不得使用哑值（如 -1、999）。
- 值域约束：0 ≤ age ≤ 120
  - age < 0：错误。
  - age > 120：错误。
  - 110 < age ≤ 120：标记为高龄异常（告警），需人工确认来源可信度。
- 格式清洗：剔除前后空格；转换全角到半角；拒绝附加单位或文本（如“岁”“years”）。

3. 跨字段一致性（与 DOB 校验）
- 规则：若存在 DOB，则 age 必须等于参考日期相对于 DOB 的完整年差，允许容差 0（推荐）或 ±1（仅在参考日期来源存在不确定性的场景）。
- 年龄计算（通用逻辑）：
  - age_calc = ref.year - dob.year - I(ref.month-day < dob.month-day)
  - 其中 I(cond) 为指示函数：条件成立取 1，否则取 0。
- 校验判定：
  - 严格模式：age == age_calc（推荐生产场景）。
  - 容差模式：abs(age - age_calc) ≤ 1（用于来源系统参考日期存在时区/入库延迟等不确定性）。
- 示例（SQL，不同方言）：
  - PostgreSQL：
    - age_calc = date_part('year', age(ref_date::date, dob::date))
    - 校验：age = age_calc
  - MySQL：
    - age_calc = TIMESTAMPDIFF(YEAR, dob, ref_date)
    - 校验：age = age_calc
  - SQL Server（矫正 DATEDIFF 偏差）：
    - age_raw = DATEDIFF(YEAR, dob, ref_date)
    - age_calc = CASE WHEN DATEADD(YEAR, age_raw, dob) > ref_date THEN age_raw - 1 ELSE age_raw END
    - 校验：age = age_calc

4. 时间一致性（纵向记录）
- 若同一主体存在多条时间序列记录，年龄随时间变化应满足：
  - 在相邻记录间（按参考日期排序），Δage ∈ {0, 1}。
  - 若相邻记录相隔超过一年：Δage ≤ 年差（同 DOB 计算逻辑）。
  - 年龄不得下降；出现下降为错误，提示数据时间戳或主体合并问题。
  
5. 业务规则联动（可选）
- 未成年人标识一致性：若 age < 18，则 minor_flag 应为 true；若 age ≥ 18，则为 false（如存在该字段）。
- 年龄与类别约束：针对特定业务（如某产品仅面向 18–65 岁），应增加条件 18 ≤ age ≤ 65，否则标记为业务不适配（告警或拒绝）。

6. 异常与处理策略
- 错误级（阻断）：类型错误、非数值、负值、>120、与 DOB 严格不一致、年龄下降。
- 告警级（可通过但需关注）：110–120、与 DOB 存在 ±1 差异（在容差模式下）。
- 纠正建议：
  - 存在 DOB 时，以 DOB+参考日期派生年龄并覆盖不一致值。
  - 无 DOB 时，保持 age 并记录数据溯源；对异常值进行人工复核或回溯来源系统。

7. 监控指标（数据质量度量）
- invalid_age_rate = 无效年龄记录数 / 总记录数（类型、值域错误）。
- age_dob_mismatch_rate = 年龄与 DOB 不一致记录数 / 含 DOB 的记录数。
- age_temporal_inconsistency_rate = 年龄在时间序列中出现下降或异常跳变的主体占比。
- high_age_warning_rate = 110–120 区间占比。

8. 示例校验脚本（批量检测伪代码）
- 基础值域与类型：
  - where not is_integer(age) or age < 0 or age > 120 → error
- 与 DOB 一致性（MySQL 示例）：
  - select id where TIMESTAMPDIFF(YEAR, dob, ref_date) <> age → error
- 时间一致性（按主体和时间排序）：
  - 对每个主体计算相邻记录 Δage 与 Δ年差；若 Δage < 0 或 Δage > Δ年差 → error

说明
- 以上规则适用于通用人群数据。特定场景（如超高龄研究数据）可将上限调整至 130，并同步调整监控阈值与处置策略。
- 若业务允许年龄缺失（NULL），需在消费层明确缺失处理策略，严禁使用哑值代替缺失。

Sale Price Data Validation Rules

Scope
- Applies to sale_price for product pricing records keyed by product_id, channel_id, currency_code, and effective date range.
- Assumes presence of: list_price (optional), cost (optional), price_floor/price_ceiling (from a reference table), min_margin_pct (configurable), map_price (Minimum Advertised Price; optional), price_effective_start, price_effective_end, is_active, price_includes_tax, tax_region_code, derivation_method (e.g., MANUAL, FX_DERIVED).

Severity Levels
- Critical: Failing records must be blocked.
- Major: Failing records require correction or explicit override.
- Warning: Records can pass but require monitoring.

Rule Set

Critical
1) Type, nullability, and domain
- sale_price must be numeric and not null when is_active = true and current_timestamp within [price_effective_start, price_effective_end].
- sale_price >= 0.

2) Currency and precision
- currency_code must be a valid ISO 4217 code in the currency reference table.
- Decimal precision must match the currency minor unit:
  - round(sale_price, minor_unit) = sale_price.
  - Example: JPY minor_unit = 0; USD = 2; KWD/BHD/JOD/TND often 3 (rely on reference table, not hardcoding).

3) Configured range limits
- Join to a product-price policy table to enforce:
  - sale_price >= price_floor.
  - sale_price <= price_ceiling.
- If no policy found, enforce a platform hard limit (e.g., sale_price <= 10,000,000) to prevent extreme values.

4) Effective window integrity
- price_effective_start < price_effective_end.
- No overlapping active price windows for the same product_id, channel_id, and currency_code:
  - For any pair of records: [start1, end1) must not overlap [start2, end2).

5) Referential completeness
- product_id, channel_id, currency_code must exist in their respective master data tables and be active.

Major
6) Relationship to list price (if present)
- If pricing_type = 'SALE' and list_price is present: sale_price <= list_price + tolerance_abs, where tolerance_abs defaults to 0.01 in applicable currency units.
- If pricing_type = 'REGULAR': sale_price should equal list_price within tolerance_abs; otherwise flag.

7) Margin guardrail (if cost present)
- If cost is present and margin policy applies:
  - sale_price >= cost × (1 + min_margin_pct) unless override_approved = true.
  - If override_approved = true, record override reason and approver_id must be not null.

8) MAP compliance (if map_price present)
- sale_price >= map_price unless map_override = true and policy permits.

9) Tax inclusion consistency
- If price_includes_tax = true:
  - sale_price ≈ net_price × (1 + tax_rate) within tolerance_abs, based on tax_region_code and tax effective date.
- If price_includes_tax = false:
  - sale_price should equal net_price within tolerance_abs.
- If net_price not modeled, this rule can be applied where net_price is derived upstream.

10) FX-derived consistency (if derivation_method = 'FX_DERIVED')
- sale_price must equal base_currency_price × fx_rate × (1 + rounding_rule), rounded to currency minor unit, within tolerance_abs.
- fx_rate must come from approved source and be valid for price_effective_start date.

Warning
11) Outlier detection vs historical price
- Flag if sale_price deviates from the product’s 90-day median by more than max(percent_threshold, absolute_threshold).
  - Example defaults: 50% or 100 units of minor currency.

12) Cross-channel parity (optional)
- For channels that should maintain parity, flag if sale_price deviates by more than parity_threshold_pct from the base channel.

13) Sudden change rate limit
- Flag if day-over-day price change exceeds change_threshold_pct (e.g., >60%) without change_reason.

Implementation Notes (SQL patterns)

A) Currency precision
SELECT p.*
FROM price p
JOIN currency_ref c ON p.currency_code = c.code
WHERE ROUND(p.sale_price, c.minor_unit) <> p.sale_price;

B) Overlapping windows
WITH w AS (
  SELECT p.*,
         LAG(price_effective_end) OVER (
           PARTITION BY product_id, channel_id, currency_code
           ORDER BY price_effective_start
         ) AS prev_end
  FROM price p
)
SELECT *
FROM w
WHERE prev_end > price_effective_start;

C) Floor/ceiling
SELECT p.*
FROM price p
JOIN price_policy pol
  ON p.product_id = pol.product_id
 AND p.currency_code = pol.currency_code
WHERE p.sale_price < pol.price_floor
   OR p.sale_price > pol.price_ceiling;

D) List price relationship
SELECT *
FROM price
WHERE pricing_type = 'SALE'
  AND list_price IS NOT NULL
  AND sale_price > list_price + :tolerance_abs;

E) Margin guardrail
SELECT p.*
FROM price p
JOIN product_cost c
  ON p.product_id = c.product_id
 AND p.currency_code = c.currency_code
WHERE p.sale_price < c.cost * (1 + :min_margin_pct)
  AND COALESCE(p.override_approved, false) = false;

F) MAP compliance
SELECT *
FROM price p
JOIN map_policy m
  ON p.product_id = m.product_id
 AND p.currency_code = m.currency_code
WHERE p.sale_price < m.map_price
  AND COALESCE(p.map_override, false) = false;

Operational Controls
- Run critical and major checks in the staging layer before publishing to downstream systems.
- Enforce critical rules via database constraints where possible (NOT NULL, CHECK, FK, unique with non-overlapping windows if modeled).
- Log all failures with rule_id, record key, observed_value, expected_value, and event_time.
- Track data quality KPIs: failure rate by rule, by source, and by product category; alert on threshold breaches.
- Maintain configuration tables for: currency minor units, price floors/ceilings, min_margin_pct, parity rules, and tolerances to avoid hardcoding.

This rule set provides clear, enforceable validations for sale_price while allowing policy-driven configuration and explicit overrides where business exceptions are required.

Règles de validation du champ « numéro de facture »

Objectif
Garantir l’exactitude, la complétude et la cohérence du numéro de facture (clé fonctionnelle), afin d’assurer l’unicité, la traçabilité et la conformité opérationnelle et, le cas échéant, réglementaire.

Portée
- Données cibles: toutes les factures clients (ou fournisseurs) dans les systèmes source, zones de staging et entrepôts de données.
- Clé: numéro_de_facture (string).
- Référentiels associés: entité légale, année fiscale, séries de numérotation, types de document.

1) Spécification de format (recommandée et paramétrable)
Format standard recommandé: ENT-YYYY-SEQ[CD?]
- ENT: code entité légale ou site, 2 à 4 lettres majuscules, référencées (ex.: FR, FRP, EMEA).
- YYYY: année fiscale sur 4 chiffres.
- SEQ: séquence numérique croissante sur 6 chiffres, zéro-complétée (000001 à 999999).
- CD (optionnel): chiffre de contrôle (Luhn) calculé sur la concaténation YYYY+SEQ.
Expressions régulières:
- Sans chiffre de contrôle: ^[A-Z]{2,4}-\d{4}-\d{6}$
- Avec chiffre de contrôle: ^[A-Z]{2,4}-\d{4}-\d{6}\d$

Extensions contrôlées (optionnelles, selon processus):
- Avoir/crédit: suffixe “-C” autorisé et traçable: ^[A-Z]{2,4}-\d{4}-\d{6}(?:\d)?-C$
- Rectificative: suffixe “-R\d+”: ^[A-Z]{2,4}-\d{4}-\d{6}(?:\d)?-R\d+$

2) Règles de validation (obligatoires)
R1. Non null et présence: numéro_de_facture est requis (NOT NULL) et non vide après trim.
R2. Normalisation: suppression des espaces de tête/queue; conversion en majuscules; aucun double séparateur.
R3. Caractères autorisés: [A-Z0-9-] uniquement; pas d’espaces internes; pas d’accents ni symboles.
R4. Conformité de format: respect d’un et un seul motif autorisé (voir regex ci-dessus), selon la politique de numérotation active.
R5. Unicité:
- Unicité stricte au minimum par (entité_légale, année_fiscale, numéro_de_facture).
- Recommandé: unicité globale dans le périmètre du système si une seule série globale est utilisée.
R6. Cohérence référentielle:
- ENT doit exister dans le référentiel d’entités et correspondre à l’entité de la facture.
- YYYY doit correspondre à l’année fiscale de date_facture (règle de calendrier local).
R7. Séquentialité:
- SEQ doit être strictement croissant à l’intérieur du couple (ENT, YYYY) selon l’ordre de création (date/heure d’émission).
- Interdiction de réutiliser un SEQ déjà attribué, y compris après annulation; les annulations sont tracées par statut, pas par renumérotation.
R8. Valeurs interdites:
- Interdire SEQ “000000”; motifs tests: contient “TEST”, “DUMMY”, “SAMPLE”, “TEMP”.
- Interdire répétitions triviales: SEQ ∈ {111111, 222222, …, 999999}.
R9. Cohérence documentaire (si suffixes activés):
- “-C” (avoir) doit référencer une facture source valide; entité et année compatibles avec la politique interne.
- “-R#” (rectificative) doit référencer la facture rectifiée; le compteur # est croissant.

3) Règles de validation (optionnelles, selon politiques et réglementation locale)
O1. Chiffre de contrôle Luhn activé:
- Calcul sur la chaîne numérique YYYY+SEQ; le CD est le chiffre rendant la somme Luhn multiple de 10.
- Vérification: l’extrémité du numéro correspond au CD attendu.
O2. Séquentialité sans trou:
- Détecter les « gaps » dans SEQ par (ENT, YYYY). Si la loi locale exige la continuité, les gaps doivent être justifiés (réservations, échecs, annulations tracées).
O3. Fenêtre temporelle:
- La date d’émission doit être comprise dans l’année YYYY; pas de SEQ antérieur attribué à une date postérieure.
O4. Longueur totale:
- Limite stricte de longueur, par exemple 12 à 20 caractères selon le motif activé.

4) Algorithme du chiffre de contrôle (si O1 activée)
Luhn sur YYYY+SEQ:
- Entrée: s = concat(YYYY, SEQ).
- Étapes: partir de la droite, doubler 1 chiffre sur 2; si résultat > 9, soustraire 9; sommer; CD = (10 - (somme mod 10)) mod 10.
- Vérif: le dernier chiffre du numéro doit égaler CD.

5) Implémentation (exemples)
Base de données (ex. PostgreSQL):
- Contrainte de format: CHECK (numero ~ '^[A-Z]{2,4}-\d{4}-\d{6}$')
- Unicité: UNIQUE (entite_legale, annee_fiscale, numero)
- Index d’ordre: INDEX (entite_legale, annee_fiscale, seq_extrait(numero)) pour contrôles de séquentialité.
Pipeline de qualité:
- Règles DQ synchrones: R1–R6 bloquantes; R7–R9 et O1–O4 en alerte selon criticité.
- Normalisation en amont (trim/upper), puis validation regex, puis contrôles référentiels et de séquence.

6) Messages d’erreur standardisés
- DQ_INV_001: Numéro vide ou NULL.
- DQ_INV_002: Caractères non autorisés.
- DQ_INV_003: Non-conformité au motif autorisé.
- DQ_INV_004: Doublon détecté (entité/année).
- DQ_INV_005: ENT inconnu ou non aligné avec l’entité.
- DQ_INV_006: Année du numéro ≠ année fiscale de la date.
- DQ_INV_007: Rupture de séquentialité (non croissant).
- DQ_INV_008: Valeur interdite (TEST/000000/etc.).
- DQ_INV_009: Chiffre de contrôle invalide.
- DQ_INV_010: Crédit/rectificative sans référence valide.
- DQ_INV_011: Gap de séquence non justifié.

7) Contrôles de monitoring et seuils
- Taux de conformité regex ≥ 99,9%.
- Taux de doublons = 0.
- Taux de ruptures de séquence ≤ 0,01% (avec justification).
- Taux d’écart année-numéro vs date ≤ 0,05%.
- Couverture de référence ENT = 100%.
- Alertes en temps réel sur DQ_INV_003/004/007/009; rapports hebdo sur gaps.

8) Exemples
Valides:
- FR-2025-000123
- FRP-2025-0456783 (si CD ‘3’ et règle O1 activée)
- FR-2025-000124-C (avoir lié et suffixes activés)
Invalides:
- fr-2025-000123 (minuscules)
- FR-25-000123 (année invalide)
- FR-2025-000000 (SEQ interdit)
- FR-2025-000123 (doublon avec même entité/année)
- FR-2025-000125 (CD incorrect si O1 activée)

Note d’adaptation
- Si un format existant est déjà en place (ex.: purement numérique, préfixes différents), adapter la regex, les contrôles de séquence et la logique de cohérence pour refléter la politique en vigueur tout en conservant les règles fondamentales R1–R6 et l’unicité.