创建数据质量培训指南

Data Quality Best Practices Training Guide for BI Teams

1. Purpose and Scope

• Provide a practical, repeatable approach to ensure data accuracy, completeness, consistency, validity, timeliness, and reliability across BI assets (datasets, models, dashboards, reports).
• Applicable to all stages of the BI data lifecycle: ingestion, transformation, modeling, visualization, and ongoing monitoring.
• Intended for BI analysts, data engineers, data stewards, and report developers.

2. Core Data Quality Dimensions (Definitions)

• Accuracy: Values reflect the real-world truth, within defined tolerances.
• Completeness: Required fields are populated; records are not missing.
• Consistency: Data is coherent across datasets and time; definitions and formats align.
• Validity: Values conform to permitted formats, ranges, types, and domains.
• Uniqueness: No unintended duplicates within the defined business key.
• Timeliness/Freshness: Data is available within expected latency windows.
• Integrity: Relationships (keys) between datasets are intact; no orphan records.
• Lineage/Traceability: Data origins and transformations are documented and reproducible.

3. Roles and Responsibilities

• Data Owner: Accountable for business rules, criticality, SLAs, and exception approvals.
• Data Steward: Maintains reference data, definitions, and quality policies; reviews exceptions.
• BI Analyst/Developer: Designs models and reports; implements quality checks and validation.
• Data Engineer: Builds pipelines; enforces schema and test coverage; automates monitoring.
• QA Lead: Oversees test plans, coverage, and release gates for data products.
• Incident Manager: Coordinates triage and root cause analysis for data quality incidents.

4. Standards and Policies

• Naming conventions: Use consistent, descriptive names; include grain and subject area.
• Data types: Enforce strict typing (e.g., numeric for measures); prohibit mixed-type columns.
• Null policy: Explicitly define nullable fields and default behavior; avoid ambiguous sentinel values.
• Code sets: Maintain authoritative lists for categorical domains (e.g., country codes).
• IDs and keys: Define business keys and surrogate keys; prohibit composite keys in fact tables unless necessary.
• Time handling: Use ISO 8601 formats; store timestamps in UTC; define time zone display rules.
• PII handling: Mask or tokenize sensitive fields; implement role-based access; log access.
• Versioning: Track schema versions, transformation versions, and semantic definitions.

5. Data Lifecycle Controls

• Requirements: Document grain, business rules, acceptance criteria, and data sources. Establish SLAs for freshness and accuracy.
• Ingestion: Validate schema, types, and mandatory fields at entry. Reject or quarantine invalid records.
• Transformation: Implement deterministic logic; version SQL/ETL; ensure idempotency; add tests at step boundaries.
• Modeling: Define the semantic layer (dimensions, facts, measures) with consistent grain; prevent double counting; document measure definitions.
• Visualization: Validate totals, filters, and drill-down behavior; ensure labeling, units, and footnotes reflect logic and limitations.
• Adoption and Monitoring: Track usage, continuously measure DQ KPIs, and respond to alerts.

6. Data Profiling and Assessment

• Profile new and changed datasets before use. Compute:
  • Row counts, distinct counts, null rates, min/max, value ranges.
  • Uniqueness ratio: count(distinct business_key) / count(*).
  • Referential integrity gaps: unmatched foreign keys.
  • Distribution and outliers: compare to historical distributions.
• Identify anomalies via baseline comparison and control charts for key measures.

Example SQL profiling snippets:

• Null rate: SELECT COUNT() AS total, SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) AS nulls, SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) * 1.0 / COUNT() AS null_rate FROM dataset;
• Uniqueness: SELECT COUNT() AS cnt, COUNT(DISTINCT business_key) AS distinct_cnt, COUNT(DISTINCT business_key) * 1.0 / COUNT() AS uniqueness_ratio FROM dataset;
• Referential integrity: SELECT f.business_key FROM fact f LEFT JOIN dim d ON f.dim_key = d.dim_key WHERE d.dim_key IS NULL;

7. Validation and Testing (Data Contracts)

• Schema tests: Types, nullable flags, primary keys, unique constraints, default values.
• Domain checks: Controlled vocabularies; valid ranges (e.g., percentages 0–100).
• Referential tests: Foreign keys must match dimension keys; scheduled anti-joins.
• Reconciliation: Compare aggregates to source system totals; verify control totals by day or batch.
• Freshness checks: Max event timestamp vs current time; ensure lag within SLA.
• Transformation unit tests: Assert business rules (e.g., discount <= price; revenue = price × quantity).
• Regression tests: Detect changes versus previous release baselines.

Sample acceptance thresholds (set per criticality):

• High critical datasets: Accuracy ≥ 99.5%, Completeness ≥ 99%, Uniqueness ≥ 99.9%, Freshness lag ≤ SLA.
• Medium: Accuracy ≥ 98%, Completeness ≥ 97%.
• Low: Calibrate based on business tolerance.

8. Cleansing and Standardization

• Standardize formats: Dates (ISO), currencies with 3-letter ISO codes, country names via ISO 3166.
• Deduplication: Use deterministic keys; apply match rules (exact match + fuzzy fields if needed); log merges.
• Imputation: Prefer business-approved defaults; document imputation rules; avoid silent guessing for critical fields.
• Outliers: Flag and quarantine extreme values; use robust checks (median/IQR) rather than only mean/SD when distributions are skewed.
• Text normalization: Trim whitespace; normalize case; remove control characters; standardize known aliases.
• Error handling: Route invalid records to quarantine tables with error codes; track and remediate.

9. Master and Reference Data Management

• Maintain authoritative reference tables; implement change control and versioning.
• Synchronize code sets and hierarchies (e.g., product taxonomy); publish effective dates and deprecation plans.
• Validate against reference data at ingestion and before publish.

10. Semantic Layer and BI Modeling

• Define grain: Each fact table must state the atomic level (e.g., order line, daily account balance).
• Measures: Document calculation, aggregation behavior (sum, average), filters, and exclusions.
• Dimensions: Conform across facts; ensure surrogate keys; maintain slowly changing dimension strategy (type 1 vs type 2) with rationale.
• Join logic: Explicitly define yes/no join paths; avoid fan traps and chasm traps by modeling bridge tables or aggregated facts as needed.
• Time series: Use canonical date dimension; ensure consistent week, month, fiscal calendar definitions.

11. Visualization Quality Assurance

• Verify totals: Cross-foot and control totals must reconcile to underlying tables.
• Filters and prompts: Default values documented; avoid implicit filters that change counts unexpectedly.
• Drill paths: Maintain hierarchical consistency (e.g., Region → Country → State → City).
• Units and context: Display currency, time zone, and data as-of date; annotate known limitations.
• Sampling and caching: Clearly indicate when data is sampled or cached; validate sample representativeness.

12. Monitoring, KPIs, and Alerts

• DQ KPIs per dataset:
  • Error rate = invalid_records / total_records.
  • Null rate per critical column.
  • Uniqueness ratio for business keys.
  • Integrity gap rate = orphan_fact_rows / total_fact_rows.
  • Freshness lag = now - max(event_timestamp).
  • Reconciliation variance = |reported_total - source_total| / source_total.
• Composite DQ Score (weighted): DQ_Score = w_accAccuracy + w_compCompleteness + w_consConsistency + w_valValidity + w_uniqUniqueness + w_freshFreshness + w_int*Integrity Weights set by business criticality and risk.
• Alerting:
  • Threshold-based alerts with severity levels (warning vs critical).
  • Rate-limited notifications; include context and probable affected reports.
  • Auto-open incidents for critical breaches; attach recent lineage and changes.

13. Incident Management and Root Cause Analysis (RCA)

• Triage checklist:
  • Confirm breach with direct query.
  • Identify scope: datasets, columns, time window, impacted dashboards/users.
  • Check lineage for recent changes (schema, code, source feed).
  • Examine quarantine/error logs for spike in specific error codes.
• RCA template:
  • Summary of issue and detection time
  • Impact assessment (reports, decisions, SLAs breached)
  • Timeline of events
  • Root cause (technical, process, source change)
  • Corrective actions (fixes applied)
  • Preventive actions (tests, controls, contract updates)
• Post-incident review with stakeholders; update tests, documentation, and thresholds.

14. Change Management and Release Controls

• Data contracts: Define expected schema, domains, keys, freshness, and SLAs with source owners.
• Backward compatibility: Support transitional periods; deprecate fields with clear timelines.
• Staging validation: Run full test suite in non-production; reconcile against golden datasets.
• Schema migration: Use versioned migrations with rollbacks; publish release notes.
• Shadow deployments: Validate new pipelines in parallel before cutover.

15. Security, Privacy, and Compliance

• Least privilege access: Role-based controls for raw vs curated vs PII.
• Masking/tokenization: Apply to PII in non-production and user-facing datasets as needed.
• Audit logs: Track data access, changes to pipelines, and test result changes.
• Retention and deletion: Follow policy; document data retention periods and deletion procedures.

16. Checklists

• Ingestion checklist:
  • Source contract in place; schema validated.
  • Mandatory fields present; type and format checks pass.
  • Reference data checks pass; quarantine enabled.
  • Freshness monitored; SLA defined.
• Transformation/model checklist:
  • Grain defined; keys unique; SCD strategy documented.
  • Business rules unit tests pass; reconciliation against source totals passes.
  • Null policy and imputation documented; duplicates resolved.
  • Semantic definitions published; lineage updated.
• Visualization checklist:
  • Totals reconcile; filters and prompts verified.
  • Context annotations present (as-of date, currency, TZ).
  • Access controls validated; refresh schedules aligned with SLAs.
  • Performance acceptable; caching documented.

17. Templates and Examples

• Example data test cases (conceptual):
  • Schema: Column “order_id” non-null and unique.
  • Domain: “country_code” in ISO list; “discount_pct” between 0 and 100.
  • Integrity: All “customer_id” in fact exist in dim_customer.
  • Freshness: max(event_time) within 2 hours of now for real-time feeds.
• Example reconciliation query: SELECT SUM(amount) AS bi_total FROM fact_payments WHERE payment_date = '2025-09-01'; -- Compare against source control total logged for same date
• Freshness lag: SELECT EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - MAX(event_ts))) AS freshness_seconds FROM source_events;

18. Calibration and Governance

• Set thresholds per dataset based on business risk, usage frequency, and decision criticality.
• Maintain a DQ catalog documenting dimensions, thresholds, owners, tests, and SLAs.
• Review DQ metrics in monthly governance meetings; adjust weights and thresholds as needed.
• Embed DQ objectives in team KPIs (e.g., reduction in integrity gaps, improved freshness compliance).

19. Anti-Patterns to Avoid

• Silent data fixes without documentation or tests.
• Over-aggregation that masks data issues (e.g., only validating totals).
• Reliance on manual spot checks without automated monitoring.
• Mixing semantic definitions across reports (e.g., different revenue definitions).
• Ignoring source system changes and breaking contracts.

20. Implementation Guidance

• Automate tests close to the data: SQL assertions, pipeline validators, and scheduled checks.
• Store test results and DQ KPIs over time to detect trends and drift.
• Quarantine invalid records; never silently drop without logging and review.
• Publish clear data dictionaries, lineage graphs, and measure definitions accessible to BI users.
• Create a feedback loop: capture user-reported anomalies and incorporate new tests.

This guide provides a structured foundation for BI teams to design, validate, and monitor high-quality data assets. Adopt these practices across the lifecycle, calibrate thresholds with stakeholders, and audit outcomes regularly to maintain trust in BI deliverables.

数据质量最佳实践培训指南（平台组）

一、目标与范围

• 目标：为平台组提供可落地的数据质量标准、工具与流程，使数据在采集、存储、加工与服务全链路保持准确性、完整性与可靠性。
• 范围：包含数据质量维度与术语、平台级能力、数据合同与标准、端到端控制点、检测与验证方法、指标与SLO、事件管理、变更治理、元数据与血缘、合规与安全、性能与成本，以及实施清单。

二、核心数据质量维度与术语

• 准确性（Accuracy）：数据值与真实世界或业务定义的吻合程度。
• 完整性（Completeness）：必要字段是否存在、是否覆盖所有应到记录。
• 一致性（Consistency）：跨系统、跨表、跨时间的逻辑一致与格式一致。
• 及时性（Timeliness/Freshness）：数据到达与更新的延迟是否满足时效要求。
• 唯一性（Uniqueness）：主键或业务键无重复。
• 有效性（Validity）：数据值符合规则/域约束（类型、范围、枚举、正则等）。
• 完整性约束（Integrity）：外键、引用关系、层级关系是否有效。
• 可追溯性（Traceability）：数据血缘与变更历史可查询与审计。

三、平台级能力与架构蓝图

• 能力清单：
  • 统一规则引擎与SDK（批处理与流式场景均支持）。
  • 数据合同与模式治理（Schema Registry/合同仓库）。
  • 元数据与血缘服务（如 DataHub、OpenMetadata，支持 OpenLineage）。
  • 质量指标与SLO平台（集中采集SLI、可视化与告警）。
  • 校验门禁与隔离区（质量不达标禁止入仓或进入下游主题）。
  • 事件管理与工单集成（根因分析、修复与复盘闭环）。
• 参考集成：
  • 任务编排：Airflow、Dagster、Argo。
  • 验证工具：dbt tests、Great Expectations、Soda Core、AWS Deequ（批）。
  • 流式校验：在 Flink/Kafka Streams 中实现窗口级与规则检查；Schema Registry 校验 Avro/Protobuf。
  • 血缘：OpenLineage（与 Airflow/Dagster/Marquez 集成）或 DataHub/OpenMetadata。
• 角色分工：
  • 平台组：提供框架与强制控制点、可观测性、标准化集成与自助能力。
  • 数据域团队：定义业务规则与阈值，维护合同与测试用例，负责修复。
  • 数据治理与安全：合规策略、访问控制、敏感数据管理与审计。

四、数据合同与标准（Data Contract）

• 合同内容：
  • 模式：字段名、类型、可空、默认、枚举/域值、约束（主键/外键）。
  • 语义：字段描述、单位、时区、事件时间与业务键定义。
  • 质量SLO：新鲜度、完整率、错误率、重复率阈值。
  • 可用性与交付频率：批次窗口或流式延迟目标。
  • 变更策略：版本号、兼容性（后向/前向）、弃用期与通知窗口。
• 执行机制：
  • 入口门禁：仅允许合同签署与有效版本的数据入湖/入仓/入流。
  • 模式注册：Kafka Schema Registry（Avro/Protobuf/JSON Schema）；数据仓库层使用 schema evolution 策略。
  • 变更管理：PR评审、自动化契约测试、分级发布（影子/灰度/回滚）。

五、端到端质量控制点

• 采集层（Ingress）：
  • 输入验证：类型、长度、枚举、必填；拒绝或标记异常记录。
  • 去重策略：基于业务键+事件时间的幂等处理。
  • 时间字段标准化：统一时区与格式；保留原始事件时间与接收时间。
  • 采集日志：原始负载、来源ID、批次ID、校验结果。
• 存储层（Data Lake/Warehouse）：
  • 分层设计：Raw（只读）、Staging（清洗）、Curated（可信）、Mart（面向分析/服务）。
  • 分区与排序：按事件时间分区；明确分区水位与延迟容忍。
  • 约束与索引：主键唯一、非空、外键覆盖率（统计/软约束）。
  • 隔离区（Quarantine）：不达标记录入隔离表/主题，带原因标签。
• 处理层（Transform）：
  • 可重复与幂等：明确输入快照与输出版本；避免非确定性。
  • 业务规则实现：在代码与规则引擎中双轨落地；版本化与审计。
  • 单元测试与数据测试：逻辑覆盖、边界值、异常路径；dbt tests/GE/Soda。
  • 缓慢变化维（SCD）处理：保证历史正确性与当前一致性。
• 服务层（Egress/API/BI）：
  • 提供数据契约与SLI说明；下游消费前置校验。
  • 新鲜度与一致性指标对外暴露；变更通知与弃用策略。

六、检测与验证方法

• 规则类型：
  • 模式规则：类型、可空、长度、正则、枚举。
  • 统计规则：空值率、重复率、唯一性、分布与异常检测。
  • 关系规则：主外键覆盖率、跨表一致性、跨分区一致性。
  • 时间规则：新鲜度、迟到记录比例、时序单调性/跳变。
  • 业务规则：价格≥0、状态机合法、汇总平衡（借贷平衡等）。
• 阈值设定：
  • 基线学习：通过历史数据建立基线；异常检测采用简单控制图或分位数。
  • 风险分级：关键资产严格阈值；非关键资产宽松阈值与告警抑制。
• 示例（简化）：
  • SQL：select count(*) from fact_orders where order_id is null; 阈值=0。
  • dbt tests：unique(order_id), not_null(customer_id), relationships(fact_orders.customer_id -> dim_customer.id)。
  • Great Expectations：expect_column_values_to_be_in_set(status, [‘PENDING’, ‘PAID’, ‘CANCELLED’])；自动生成Data Docs。
  • Soda Core：checks.yml 定义 freshness < 15m、missing_count < 100、duplicate_count(order_id)=0。
  • Deequ：计算指标（Completeness、Uniqueness）并应用约束。
• 采样策略：
  • 批处理：全量+分区采样结合；大表采用分层抽样。
  • 流式：窗口采样与滑动统计；高吞吐场景采用异步旁路验证。

七、质量指标、SLI/SLO与监控

• 常用SLI：
  • Freshness（最大事件时间到当前的延迟）。
  • Completeness（实到记录/应到记录，或非空字段比例）。
  • Accuracy（与参考源或回算规则的一致率）。
  • Consistency（跨表对齐率、维度对齐率）。
  • Uniqueness（重复率）。
  • Validity（规则通过率）。
• SLO设定：
  • 示例：订单事实表新鲜度<=15分钟；重复率=0；外键覆盖率>=99.5%。
• 监控与告警：
  • 指标采集与可视化：统一指标平台（如 Prometheus+Grafana 或数据质量控制台）。
  • 告警分级：P1（阻断入仓）、P2（影响下游）、P3（观察）；支持静音与窗口聚合。
  • 报表：每日/每周质量健康报表与趋势分析。

八、数据质量事件管理与根因分析

• 事件流程：
  • 检测→告警→自动隔离→工单创建→定位→修复→复盘→规则/流程更新。
• 根因分析要点：
  • 变更审计：最近代码、合同、上游发布与配置变更。
  • 血缘图：受影响资产与传播范围。
  • 样本比对：隔离记录与正常记录差异。
  • 运行环境：资源异常、失败重试导致重复。
• 复盘与防回归：
  • 增加前置校验或阈值调整；引入契约测试；完善回滚与影子发布。

九、数据修复策略

• 修复方法：
  • 重跑与回填：按分区水位与版本控制执行；保证幂等。
  • 更正与补齐：字段重算、维表补充；记录修订需审计。
  • 删除或重定向：重复或不可修复记录标记为无效并隔离。
• 风险控制：
  • 在隔离区操作，验证通过后迁回；建立修复影响评估与签署流程。

十、变更管理与模式演进

• 变更类型：新增字段（向后兼容）、字段弃用（留存一段兼容期）、约束收紧（需灰度与测试）。
• 管理流程：
  • 需求评审→合同更新→契约测试→影子发布→灰度→全面切换→监控复核→文档更新。
• 兼容策略：
  • Schema Registry 版本化；避免破坏性变更直接上线；为下游提供过渡期。

十一、元数据、血缘与可观测性

• 元数据标准：
  • 资产标识、所有者、数据域、保密等级、SLO、刷新频率、业务字典。
• 血缘采集：
  • 使用 OpenLineage 与编排器集成；在 DataHub/OpenMetadata 展示任务、表、列级血缘。
• 可观测性：
  • 引入数据质量日志、处理审计日志、指标与事件统一采集；支持按资产、分区、任务维度查询。

十二、安全与合规

• 敏感数据分类（PII/PHI/财务）：字段级标签与访问策略。
• 脱敏与最小权限：静态/动态脱敏、列级权限、行级过滤。
• 审计与留存：访问审计、变更审计、数据留存策略与删除流程符合合规要求。

十三、性能与成本优化

• 规则执行位置：
  • 入口执行基本规则（低成本高收益）；复杂规则在加工层批量执行。
• 资源策略：
  • 大表采用分区级验证与并行；缓存统计指标；错峰执行。
• 成本控制：
  • 优先检测高风险资产；设定采样与频率；避免重复全表扫描。

十四、实施清单（平台组）

• 建设与治理：
  • 建立统一数据合同仓库与模式注册流程。
  • 提供标准化验证SDK，支持 SQL/dbt/GE/Soda/Deequ。
  • 部署质量门禁与隔离区，强制执行关键资产SLO。
  • 集成血缘与元数据平台，固化变更审计与发布流程。
  • 建立统一指标与告警平台，标准化告警分级与工单流程。
• 项目落地：
  • 为每个数据资产定义质量维度与SLO。
  • 编写最小可用检查集（模式、唯一、非空、新鲜度、外键覆盖）。
  • 配置准入门槛与失败策略（阻断/隔离/降级）。
  • 建立基线与阈值评估方法（历史+业务输入）。
  • 维护质量文档与仪表板；定期复盘。

十五、常见反模式与避免

• 无合同直连：避免无模式、无约束的数据直接入湖/入仓。
• 只做下游可视化：质量控制需前置，不能仅依赖BI层补救。
• 无审计的修复：修复必须留痕与可回滚。
• 阈值拍脑袋：基于历史与风险分级设定阈值，并定期校准。
• 忽视流式场景：为实时管道建立窗口与乱序容忍策略。

十六、示例模板（简化）

• 资产质量配置模板：
  • 资产：warehouse.fact_orders
  • 所有者：订单域团队；等级：关键
  • SLO：freshness<=15m；uniqueness(order_id)=100%；fk_coverage(customer_id in dim_customer)>=99.5%
  • 规则：
    • not_null: order_id, customer_id, order_ts
    • validity: status in [PENDING, PAID, CANCELLED]
    • consistency: currency in ISO 4217; amount>=0
  • 失败策略：P1阻断入仓；异常记录入 quarantine.fact_orders_invalid
• 变更策略示例：
  • 新增字段 device_type（可空），版本+1，兼容；弃用 old_status，公告期30天，双写与对比，完成后下游切换。

结语

• 数据质量是平台与数据域的共同责任。平台组需提供标准化、可扩展、可审计的能力与流程，将质量管理嵌入数据全生命周期，形成检测—隔离—修复—复盘—改进的闭环，持续提升数据可信度与使用效率。