创建数据质量指标

以下定义面向订单交易数据集的3个核心数据质量指标，用于数据清洗、验证、分析与持续监控。各指标均应在订单头与订单行（如存在）分别计算，并可按业务日期、来源系统、渠道等维度切分。

1. 关键字段完整率（Mandatory Field Completeness Rate）

• 目的：衡量必填字段是否存在缺失或占位值，控制基础数据缺口。
• 范围：依据数据字典标注的必填字段集合。典型示例：
  • 订单头：order_id、order_date、customer_id、currency_code、payment_status、total_amount
  • 订单行：order_id、line_number、product_id、quantity、unit_price
• 判定规则：
  • 字段值不可为 NULL、空字符串、仅空白、占位值（如“UNKNOWN”“N/A”）、非法默认值（如 id=0）。
  • 业务性缺失：quantity 必须 >0；unit_price ≥0；total_amount 必须存在（若为头表）。
• 计算公式：
  • 完整率 = 完整记录数 / 总记录数
  • 完整记录：该记录所有必填字段均满足判定规则。
• 监控建议：
  • 最低阈值示例：订单头 ≥99.5%，订单行 ≥99.0%（实际阈值需结合历史基线与来源差异确定）。
  • 每日计算并趋势化，低于阈值触发告警与缺失字段明细输出。

2. 业务规则有效率（Business Rule Validity Rate）

• 目的：衡量字段值域与跨字段逻辑的合规性，发现不合理或不一致的取值。
• 范围与规则示例（根据企业规则库配置为“活动规则”）：
  • 值域/枚举：payment_status ∈ {CREATED, PAID, CANCELLED, REFUNDED}；currency_code ∈ ISO 4217 合法代码集合。
  • 时间合理性：order_date 不得晚于处理日（或允许 ≤X 天的滞后/时区差异）；refund_date ≥ payment_date。
  • 类型与格式：主键/外键为符合模式的字符串或数值；日期为有效日历日期。
  • 数值约束：quantity 为正整数；unit_price ≥0；discount_rate ∈ [0,1]。
  • 金额一致性：abs(total_amount − Σ(quantity × unit_price × (1 − discount_rate))) ≤ δ；δ 可按货币最小计价单位或四舍五入策略设定（例如 δ=0.01）。
• 计算公式：
  • 有效率（全规则）= 通过全部活动规则的记录数 / 总记录数
  • 也应输出逐条规则的通过率，便于定位具体违反的规则。
• 监控建议：
  • 按来源系统/渠道分组监控，设定每条规则的独立阈值；金额一致性建议单独告警并输出差异及计算明细。

3. 参照完整率（Referential Integrity Pass Rate）

• 目的：衡量外键与主数据/维表之间的关联有效性，防止“孤儿记录”与跨表不一致。
• 范围与关联示例：
  • 订单头：customer_id 在 Customer 维表存在；store_id 在 Store 维表存在；payment_method_code 在 PaymentMethod 维表存在。
  • 订单行：product_id 在 Product 维表存在；order_id 能在订单头表找到对应记录。
• 判定规则：
  • 以同一数据日期的维表快照为基准；允许“迟到维表”需设定宽限窗口（例如 T+1），超出窗口仍未匹配视为参照失败。
  • 维表键需唯一且生效（状态为有效、在有效期内）。
• 计算公式：
  • 参照完整率 = 通过全部外键匹配的记录数 / 总记录数
  • 可并行输出每个外键的匹配通过率（例如 product_id 匹配率）。
• 监控建议：
  • 当参照完整率下降时，区分来源问题（交易侧漏传/错误）与主数据问题（维表缺失/延迟）；输出未匹配键清单用于回溯与补录。

补充实施要点（适用于所有指标）：

• 指标口径固化：在数据字典与规则库中明确字段清单、判定规则、容差与快照基准时间。
• 分层计算与采样：对全量数据每日计算；高流量场景可先按分区/渠道滚动采样再全量验证。
• 结果可追溯：保留明细失败记录、规则 ID、字段名与原始值，支持复查与修复闭环。

Below are three data quality metrics tailored for a reporting metrics repository dataset (i.e., a catalog of KPIs with business and technical definitions). Each metric includes a precise definition, scope, and a computable formula.

1. Metadata Completeness Rate

• Purpose: Ensure each KPI entry contains the required business and technical metadata for unambiguous understanding and correct downstream use.
• Scope: KPI catalog records.
• Required fields (example, adjust to your schema): metric_code, metric_name, business_definition, owner, unit, grain, aggregation_method, calculation_expression, source_system, update_frequency, sla_minutes, valid_from, status.
• Rule: A record is “complete” if all required fields are non-null, non-blank after trim, and constrained fields are within allowed sets (e.g., status in {active, deprecated}).
• Formula: Metadata Completeness Rate = (count of KPI records passing all completeness rules) / (total KPI records)
• Notes:
  • Treat whitespace-only strings as null.
  • Optionally weight critical fields (e.g., calculation_expression, owner) if a weighted completeness score is preferred.
• Example SQL (generic): SELECT SUM(CASE WHEN metric_code IS NOT NULL AND TRIM(metric_code) <> '' AND metric_name IS NOT NULL AND TRIM(metric_name) <> '' AND business_definition IS NOT NULL AND TRIM(business_definition) <> '' AND owner IS NOT NULL AND TRIM(owner) <> '' AND unit IS NOT NULL AND TRIM(unit) <> '' AND grain IS NOT NULL AND TRIM(grain) <> '' AND aggregation_method IS NOT NULL AND TRIM(aggregation_method) <> '' AND calculation_expression IS NOT NULL AND TRIM(calculation_expression) <> '' AND source_system IS NOT NULL AND TRIM(source_system) <> '' AND update_frequency IS NOT NULL AND sla_minutes IS NOT NULL AND valid_from IS NOT NULL AND status IN ('active', 'deprecated') THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(18,6)) AS metadata_completeness_rate FROM kpi_catalog;

2. Metric Identity Integrity Pass Rate

• Purpose: Guarantee that KPI identity and versioning are well-formed: no duplicate records per version and exactly one active version per metric.
• Scope: KPI catalog records with fields supporting identity and lifecycle: metric_code, version, status.
• Rules (evaluated per metric_code): a) Version uniqueness: For each metric_code, each version appears at most once. b) Single-active rule: For each metric_code, exactly one record is status = 'active'.
• Formula: Metric Identity Integrity Pass Rate = (number of metric_code values passing both rules) / (total number of metric_code values)
• Example SQL (two-step, generic): WITH by_metric AS ( SELECT metric_code, SUM(CASE WHEN dup_cnt > 1 THEN 1 ELSE 0 END) AS has_version_dup, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_cnt FROM ( SELECT metric_code, version, status, COUNT() OVER (PARTITION BY metric_code, version) AS dup_cnt FROM kpi_catalog ) x GROUP BY metric_code ) SELECT SUM(CASE WHEN has_version_dup = 0 AND active_cnt = 1 THEN 1 ELSE 0 END) / CAST(COUNT() AS DECIMAL(18,6)) AS metric_identity_integrity_pass_rate FROM by_metric;

3. Calculation Expression Validity Pass Rate

• Purpose: Ensure stored calculation expressions are syntactically valid, reference only registered sources/columns, and compile in the target execution environment(s).
• Scope: KPI calculation metadata: calculation_expression, expression_dialect, referenced_objects, and registry of allowed sources/columns.
• Rules (evaluated per metric record): a) Parse success: The expression parses successfully for its declared dialect. b) Reference resolution: All referenced tables/views/columns exist in the approved registry and meet access policies. c) Optional static checks: Disallow banned functions (e.g., nondeterministic), ensure aggregation matches declared grain, and enforce unit consistency if modeled.
• Formula: Calculation Expression Validity Pass Rate = (count of KPIs passing all expression validity checks) / (count of KPIs with a calculation_expression)
• Implementation notes:
  • Use a dialect-aware parser or dry-run compilation (e.g., EXPLAIN or VALIDATE QUERY) to capture parse/compile errors without executing the query.
  • Maintain a reference catalog of allowed objects and compare referenced identifiers from the parsed AST.
  • Store per-check results for diagnostics (parse_ok, refs_ok, banned_fn_ok, grain_ok).
• Pseudocode outline: For each KPI: parse_ok = parse(expression, dialect) refs_ok = all(referenced_objects ⊆ allowed_objects) banned_fn_ok = not uses_banned_functions(expression) validity_pass = parse_ok AND refs_ok AND banned_fn_ok Pass rate = count(validity_pass) / count(calculation_expression not null)

Operational guidance

• Frequency: Compute daily; re-check on catalog changes.
• Segmentation: Report rates by domain/owner to localize issues.
• Targets (illustrative): Metadata Completeness ≥ 98%; Identity Integrity ≥ 99.9%; Expression Validity ≥ 99%.
• Alerting: Trigger incidents on breaches; attach failing record samples and rule-level diagnostics.