生成数据验证检查清单

Below are five high‑value data validation checks for a Sales Metrics Summary table. Each includes the objective, rule, and practical implementation guidance.

1. Source-to-Summary Reconciliation

• Objective: Ensure the summary table faithfully represents the transactional facts.
• Rule: For each reporting grain (e.g., date-product-channel-region), summary totals must match the source fact table aggregated to the same grain within defined tolerances (0 for counts; small absolute/relative tolerance for currency due to rounding).
• How: Aggregate the fact table by the summary grain and compare row counts and sums of Units, Net Sales, Discounts, Returns, COGS, Gross Margin. Log mismatches by key and metric; fail the load if mismatches exceed thresholds.

2. Dimensional Referential Integrity and Valid-Time Alignment

• Objective: Prevent orphan keys and misaligned slowly changing dimensions (SCDs).
• Rule: Every dimension key in the summary exists in its dimension table and is valid for the summary date (effective_from <= date <= effective_to). Exactly one active dimension record per key/date.
• How: Left join summary to each dimension to detect missing keys; validate effective dating windows and “single active row” per key/date. Quarantine records with null dims or multiple active matches.

3. Metric Definition and Arithmetic Coherence

• Objective: Enforce consistency with the metric dictionary and catch arithmetic errors.
• Rule: Recompute key measures and compare:
  • Net Sales = Gross Sales − Discounts − Returns (typically exclude taxes; align with your metric definitions).
  • Gross Margin = Net Sales − COGS.
  • Unit Price = Net Sales / Units (for Units > 0).
  • Margin % = Gross Margin / Net Sales (handle division by zero).
• Sanity: Units ≥ 0 (except controlled negative quantities for returns), Unit Price ≥ 0, Margin % within a reasonable band (e.g., −100% to +100%) unless on an approved exception list.
• How: Derive secondary calculations in QA queries; flag rows outside tolerances and maintain an exception registry for known edge cases (e.g., promotions causing atypical margins).

4. Time-Series Completeness and Cutoff Control

• Objective: Ensure period coverage is complete and adheres to accounting cutoffs.
• Rule: All expected calendar dates in the reporting window are present; no future-dated rows; no gaps or duplicates; summary reflects the intended as-of timestamp (e.g., T−1 23:59 cutoff). Late-arriving data is tracked and backfilled as per policy.
• How: Compare the set of dates in the summary to a calendar table; validate max(date) ≤ processing_date; monitor day-over-day variances for Units and Net Sales versus expected ranges; maintain a late-arrival log and re-aggregation routine.

5. Grain Uniqueness, Duplicates, and Idempotency

• Objective: Prevent double counting and ensure stable, repeatable loads.
• Rule: The summary grain is unique (e.g., one row per date-product-channel-region). No duplicate keys; reruns do not change results unless source data changed.
• How: Enforce primary key/unique constraints; compare total rows to count distinct of the grain; identify duplicates before publish. Use deterministic upsert logic and row-level checksums for metric fields to verify idempotency across runs.

Tip: Operationalize these checks with automated nightly jobs, threshold-based alerts, and a validation dashboard that tracks failures, tolerances, and remediation SLAs.

以下为订单类事件埋点的5项核心数据验证检查，适用于采集端、流处理与入仓阶段的统一质量控制：

1. 事件契约与字段校验（Schema/Contract）

• 必填字段存在且非空：event_name、event_version、order_id、user_id、client_ts、server_ts、currency、items（含unit_price、quantity）、order_amount、channel/app_version。
• 类型与格式正确：金额为数值型且精度合规（按币种小数位），时间戳为UTC ISO 8601或毫秒/秒级整型统一。
• 枚举值合法：currency（ISO 4217）、payment_method、status、channel等受控词表。
• 空值与异常阈值：关键字段空值率≤0.1%；未知枚举≤0.05%。
• 版本兼容：event_version有且仅有有效版本，新增字段须具备默认值与向后兼容策略。

2. 标识符唯一性与可关联性

• 唯一性：order_id全局唯一，重复率≤0.01%（按自然日）。
• 一致性：同一order_id跨事件（order_create/order_submit/payment_success）user_id一致率≥99.99%，跨端存在映射策略（user_id/device_id/session_id）。
• 可关联性：与交易主库（OMS）或支付网关按order_id可连接命中率≥99.9%；无法关联样本需抽样核查（设备离线、补偿上报等原因分类）。
• 冲突检测：同一order_id的currency、region、merchant_id跨事件冲突率≤0.01%。

3. 时间戳合理性与事件序列

• 时间戳规范：server_ts与client_ts均为UTC；client-server偏差|Δ|≤5分钟，超过则标记为时钟漂移。
• 事件顺序：order_create < order_submit ≤ payment_initiated ≤ payment_success ≤ order_paid；若逆序，标记并计入异常率（≤0.05%）。
• 窗口合理：下单到支付成功的中位耗时处于业务预期区间（如P50 1–3分钟，P95≤30分钟，按品类/渠道分层）。
• 延迟监控：采集至入仓端到端延迟P95≤5分钟（流）或≤1小时（批）。

4. 金额与数量的业务规则校验

• 明细到汇总一致：item_amount=unit_price*quantity；order_amount=sum(item_amount)-discount+shipping+tax；误差≤0.01（或≤0.1%）。
• 合法区间：单价、数量、优惠、税费、运费均非负；数量为正整数；极值设上限（按品类设定）。
• 币种与地区：currency与region/商户结算币一致；跨币种订单需有fx_rate与结算金额字段；四舍五入策略一致（银行家/四舍五入），精度按币种。
• 对账一致性：与支付网关/财务对账日级GMV差异≤0.1%，笔数差异≤0.05%；失败支付不得计入已支付GMV。

5. 完整性、去重与投递质量

• 漏斗完整率：从add_to_cart → checkout_start → order_submit → payment_success的链路完整率≥98%（按渠道/端分层监控）。
• 去重策略：按(order_id, event_name, user_id, ts_window)去重；重复上报率≤0.5%；支持幂等键（如event_id）。
• 丢失与重试：采集成功率≥99.5%，端到端丢失率≤0.5%；失败重试与死信队列可追溯。
• 异常告警：上述关键指标设阈值与实时告警；提供样本回溯（原始日志、请求ID）以便根因定位。

执行建议

• 在SDK/边缘网关进行Schema预校验，流处理层做序列与金额规则校验，数仓层做对账与全量一致性校验。
• 为每项检查建立可视化监控与分层阈值（按国家/平台/版本），异常自动分派到对应责任团队并闭环。