analyze-data-quality

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Related Skills

Analyze Data Quality

数据质量分析

Assess whether a dataset is trustworthy enough for analysis, modeling, dashboards, experiments, or downstream pipelines. Start with the intended use and grain, run the highest-value checks for the data shape, and report concrete evidence, analytical risk, likely causes, and the smallest useful remediation or automated test.

评估数据集是否足够可信，可用于分析、建模、仪表盘、实验或下游管道。从预期用途和数据粒度入手，针对数据形态执行最高价值的检查，并输出具体证据、分析风险、可能原因，以及最小可行的修复方案或自动化测试建议。

Workflow

工作流程

Clarify the quality question and operating context.

Establish what the dataset represents, the intended unit of analysis, the downstream use, whether the user cares about raw ingestion quality, transformed-model quality, or both, and the comparison baseline such as prior weeks, prior schema, or a trusted reference table. Identify expected grain, primary keys or candidate keys, important date columns, timezone assumptions, domain rules, allowed values, and business thresholds. If context is missing, infer cautiously and label assumptions.
Choose an inspectable analysis path.
When checks require SQL or Python, default to a companion notebook so the user can inspect the exact code behind the findings. Use $jupyter-notebooks when a dedicated notebook scaffold or refactor workflow would help. For queryable tables, use
```
~~structured_data
```
to confirm schema, grain, sample rows, and query rules through the relevant source connector before heavier checks. Use
```
~~operations_logs
```
for freshness and lineage when those checks matter.
Build a compact profile.

Start with row count, column count, column names and types, candidate keys, duplicate rates on likely identifiers, min/max timestamps for relevant date columns, null rates, distinct counts for likely categorical columns, and basic numeric summaries for measure columns. Confirm grain before interpreting anomalies; many apparent quality problems are mixed-grain data, partial backfills, late-arriving data, or duplicated joins.
Run core quality checks.

Select checks that match the dataset and task. Default to the most relevant checks across completeness, uniqueness, validity, consistency, integrity, timeliness, volume, and shape. Compare rates, not just counts, and segment by time, source, country, platform, model version, or other key dimensions when that helps distinguish real issues from expected variation.
Run shape-specific checks.

Adapt the checks to the data shape:
- Event data: duplicate event IDs, future event timestamps, session or user coverage gaps, and abrupt event-mix changes after releases.
- Dimension tables: non-unique business keys, orphan surrogate keys, status changes without corresponding timestamps, and unexpected churn in reference values.
- Fact tables: mixed grain, impossible measures such as negative revenue or quantity, join blowups to dimensions, and late-arriving or partially loaded partitions.
- ML feature or scoring tables: leakage from post-outcome fields, feature sparsity spikes, range shifts after model or feature-store changes, and class-label drift.
- Experiment data: duplicate assignments, variant imbalance beyond expectation, exposure without assignment, and events before assignment timestamp.
Run temporal and distribution checks when history exists.

Prioritize temporal diagnostics when the user mentions "after X date", "suddenly", "recently", or "only started appearing". Check first-seen dates, last-seen dates, daily or weekly null-rate trends, duplicate-rate trends, row count trends, category-share shifts, distribution drift, and change points around launches, migrations, incidents, model changes, or backfills.
Investigate analytical risks and likely causes.

Tie each issue to the downstream risk: broken trusted analysis, biased decisions, broken joins, stale dashboards, incorrect experiments, leakage, unreliable model features, or misleading segments. When possible, identify whether the issue is isolated to a source, segment, partition, time window, release, migration, backfill, or upstream pipeline change.
Recommend fixes or automated tests.

Recommend the smallest set of follow-up fixes, monitoring, or automated tests that would materially reduce risk. Suggest automation only when the rule is stable and worth maintaining. Include or save the notebook/query path when code produced the findings.

明确质量问题和业务上下文。

确定数据集的含义、预期分析单元、下游用途，用户关注的是原始接入质量、转换后模型质量，还是两者都关注，以及对比基准（如前几周、历史Schema或可信参考表）。识别预期粒度、主键或候选键、重要日期列、时区假设、领域规则、允许值和业务阈值。如果上下文缺失，需谨慎推断并明确标注假设。
选择可核查的分析路径。
当检查需要用到SQL或Python时，默认使用配套笔记本，以便用户可以核查发现结果背后的具体代码。如果需要专用的笔记本脚手架或重构工作流，可使用$jupyter-notebooks。对于可查询的表，在进行更深入的检查之前，使用
```
~~structured_data
```
通过相关源连接器确认Schema、粒度、样本行和查询规则。当新鲜度和血缘检查很重要时，使用
```
~~operations_logs
```
。
构建精简数据概况。

从行数、列数、列名与列类型、候选键、可能标识符的重复率、相关日期列的最小/最大时间戳、空值率、可能分类列的去重计数，以及度量列的基础数值统计开始。在解读异常之前先确认粒度；许多看似质量问题的情况，其实是混合粒度数据、部分回填、延迟到达数据或重复连接导致的。
执行核心质量检查。

选择与数据集和任务匹配的检查项。默认覆盖完整性、唯一性、有效性、一致性、参照完整性、时效性、体量与形态等维度的最相关检查。对比占比而非仅对比计数，如有必要，按时间、来源、国家、平台、模型版本或其他关键维度进行拆分，以区分真实问题和预期波动。
执行特定形态数据检查。

根据数据形态调整检查项：
- 事件数据：重复事件ID、未来事件时间戳、会话或用户覆盖缺口、发布后事件构成突变。
- 维度表：非唯一业务键、孤立代理键、无对应时间戳的状态变更、参考值的意外变动。
- 事实表：混合粒度、不合理度量（如负收入或负数量）、与维度表连接后数据膨胀、延迟到达或部分加载的分区。
- ML特征或评分表：来自结果后字段的泄漏、特征稀疏度突增、模型或特征库变更后的范围偏移、类别标签漂移。
- 实验数据：重复分配、超出预期的实验组失衡、未分配先曝光、分配时间戳之前的事件。
当存在历史数据时，执行时间与分布检查。

当用户提到"X日期之后"、"突然"、"最近"或"才开始出现"等表述时，优先进行时间维度诊断。检查首次出现日期、最后出现日期、日度/周度空值率趋势、重复率趋势、行数趋势、类别占比变化、分布漂移，以及发布、迁移、故障、模型变更或回填前后的变化点。
调查分析风险与可能原因。

将每个问题与下游风险关联起来：可信分析失效、有偏决策、连接失效、仪表盘过时、实验结果错误、数据泄漏、模型特征不可靠或具有误导性的分群。尽可能确定问题是否仅出现在特定来源、分群、分区、时间窗口、版本、迁移、回填或上游管道变更中。
提出修复或自动化测试建议。

推荐最小化的后续修复、监控或自动化测试方案，以显著降低风险。仅当规则稳定且值得维护时，才建议自动化。如果发现结果是通过代码生成的，需包含或保存笔记本/查询路径。

Standards

标准规范

Core Checks

核心检查项

Completeness: null rate by column; null rate by partition, segment, and time bucket; unexpected empty strings or sentinel values; required-column population rate.
Uniqueness: exact duplicate rows, duplicate primary keys, duplicate composite keys, and proportion unique for semi-unique fields such as emails or device IDs.
Validity: type conformance after casting; format checks for IDs, emails, URLs, enums, country codes, and timestamps; range checks for measures, percentages, counts, and dates; allowed-values checks for controlled vocabularies.
Consistency: cross-field rule checks, units or currency consistency, status and timestamp alignment, and agreement between duplicated fields from different sources.
Integrity: parent-child key coverage, orphan records, unexpected many-to-many joins, and broken slowly changing dimension joins.
Timeliness: freshness lag from source event time to load time, freshness lag from load time to report time, missing recent partitions, and unexplained historical rewrites or backfills.
Volume and shape: row-count drift, distinct-count drift, distribution drift, share-of-total drift for major categories, and new or disappeared categories.

完整性：按列统计空值率；按分区、分群和时间桶统计空值率；意外的空字符串或哨兵值；必填列填充率。
唯一性：完全重复行、重复主键、重复复合键，以及半唯一字段（如邮箱或设备ID）的唯一占比。
有效性：类型转换后的一致性；ID、邮箱、URL、枚举值、国家代码、时间戳的格式检查；度量、百分比、计数、日期的范围检查；受控词汇的允许值检查。
一致性：跨字段规则检查、单位或货币一致性、状态与时间戳对齐、不同来源重复字段的一致性。
参照完整性：父子键覆盖度、孤立记录、意外的多对多连接、缓慢变化维连接失效。
时效性：从源事件时间到加载时间的新鲜度延迟、从加载时间到报告时间的新鲜度延迟、缺失的近期分区、无法解释的历史重写或回填。
体量与形态：行数漂移、去重计数漂移、分布漂移、主要类别的总占比漂移、新增或消失的类别。

Specific Check Guidance

专项检查指南

Duplicates and keys: check exact duplicates, primary key duplicates, composite key duplicates at the intended grain, and near-duplicates caused by whitespace, casing, formatting, or late updates. Report count, share of affected rows, duplicated keys, and whether duplication is isolated to a time range, source, or segment.
Missingness: distinguish acceptable sparsity from broken completeness. Check null rates over time, newly null columns after schema or pipeline changes, and sentinel values such as
```
''
```
,
```
'unknown'
```
,
```
'n/a'
```
,
```
0
```
, or
```
-1
```
.
Domain validity: check malformed identifiers, country codes, timestamps, impossible values, values outside allowed sets, and cross-field contradictions such as
```
is_cancelled = false
```
with a non-null
```
cancelled_at
```
.
Join coverage: when multiple datasets are involved, check foreign keys that do not match a parent table, unexpected one-to-many expansion, coverage loss when joining to dimensions or experiments, and row counts before and after joins.
Freshness and schema drift: check row-count changes against recent history, lag on important date columns, added/removed/retyped columns, and shifts in sparsity or cardinality that suggest upstream changes.
Outliers and distribution shifts: use robust methods such as quantiles, MAD, or IQR before defaulting to z-scores. Check sudden changes in mean, median, variance, zero rate, category share, and long-tail behavior.
Leakage, backfill, and time travel: check features populated before they should exist, future-dated records, late-arriving data causing unstable recent partitions, and backfills that change historical counts without annotation.

重复项与键：检查完全重复、主键重复、预期粒度下的复合键重复，以及由空格、大小写、格式或延迟更新导致的近重复。报告计数、受影响行占比、重复键，以及重复是否仅出现在特定时间范围、来源或分群中。
缺失情况：区分可接受的稀疏性和异常的完整性缺失。检查随时间变化的空值率、Schema或管道变更后新增的空列，以及哨兵值如
```
''
```
、
```
'unknown'
```
、
```
'n/a'
```
、
```
0
```
或
```
-1
```
。
领域有效性：检查格式错误的标识符、国家代码、时间戳、不合理值、超出允许集合的值、跨字段矛盾（如
```
is_cancelled = false
```
但
```
cancelled_at
```
非空）。
连接覆盖度：当涉及多个数据集时，检查与父表不匹配的外键、意外的一对多膨胀、与维度表或实验表连接时的覆盖损失，以及连接前后的行数。
新鲜度与Schema漂移：对照近期历史检查行数变化、重要日期列的延迟、新增/删除/类型变更的列，以及暗示上游变更的稀疏度或基数偏移。
异常值与分布偏移：优先使用分位数、MAD、IQR等稳健方法，而非默认使用z-scores。检查均值、中位数、方差、零值率、类别占比和长尾行为的突变。
泄漏、回填与时间旅行：检查本不应存在却提前填充的特征、未来日期的记录、导致近期分区不稳定的延迟到达数据，以及未标注就更改历史计数的回填。

Severity

严重等级

Critical: breaks trusted analysis, core joins, production dashboards, or key decisions, such as duplicated grain, missing primary keys, or stale production data.
High: materially biases downstream decisions, such as large null spikes, category drift in a core dimension, invalid business-rule values, leakage, or severe join coverage loss.
Medium: localized or explainable issues that still need documentation, monitoring, or owner follow-up.
Low: cosmetic inconsistencies, expected sparsity, or known edge cases that do not materially affect current use.

Do not dump raw profiling output without interpretation. Tie each finding to an analytical risk and likely impact.

严重（Critical）：导致可信分析失效、核心连接失败、生产仪表盘故障或关键决策出错，例如粒度重复、主键缺失或生产数据过时。
高（High）：会对下游决策造成实质性偏差，例如空值率大幅突增、核心维度的类别漂移、无效业务规则值、泄漏或严重的连接覆盖损失。
中（Medium）：局部或可解释的问题，但仍需记录、监控或负责人跟进。
低（Low）：表面不一致、预期内的稀疏性或已知的边缘情况，不会对当前使用造成实质性影响。

请勿在未加解读的情况下直接抛出原始概况输出。每项发现都应关联对应的分析风险和潜在影响。

Automated Test Guidance

自动化测试指南

Good candidates for automation: primary key uniqueness, not-null checks on required columns, accepted values for stable enums, referential integrity, freshness thresholds, and seasonality-aware row-count or volume bounds.
Use caution with hard-coded distribution thresholds on volatile product metrics, strict uniqueness in messy entity-resolution use cases, and recent partitions when late-arriving data is normal.
Suggest automated tests only when the expected rule is stable, important, and maintainable.

适合自动化的场景：主键唯一性、必填列非空检查、稳定枚举值的可接受值校验、参照完整性、新鲜度阈值，以及考虑季节性的行数或体量边界。
需谨慎的场景：波动较大的产品指标的硬编码分布阈值、实体解析复杂场景下的严格唯一性要求、存在延迟到达数据常态的近期分区检查。
仅当预期规则稳定、重要且可维护时，才建议添加自动化测试。

Output Standards

输出标准

For stakeholder-facing data-quality work, pass the completed findings to $build-report by default so the runtime produces an MCP app or HTML report. Do not stop at profiling output, a notebook, or chat-only findings unless the user explicitly requested a quick inline answer or another primary artifact. The structure below defines the report content.

Structure the response with:

dataset and grain summary
checks performed
findings
temporal or trend anomalies
likely causes and impacted use cases
recommended fixes or automated tests
assumptions and open questions

For each finding, include:

what failed
evidence: counts, rates, segments, and dates
why it matters
severity and confidence level
likely cause when known
suggested remediation or automated test

When code was used, include or save a notebook containing the key SQL and Python checks and make the notebook path easy to find.

对于面向利益相关方的数据质量工作，默认将完成的发现结果传递给$build-report，以便运行时生成MCP应用或HTML报告。不要仅停留在概况输出、笔记本或纯聊天发现的层面，除非用户明确要求快速内联回答或其他主要产出物。以下结构定义了报告的内容。

响应应包含以下结构：

数据集与粒度摘要
已执行的检查项
发现的问题
时间或趋势异常
可能原因与受影响的用例
建议的修复方案或自动化测试
假设与待解决问题

对于每项发现，需包含：

问题内容
证据：计数、占比、分群和日期
重要性说明
严重等级与置信度
已知情况下的可能原因
建议的修复方案或自动化测试

如果使用了代码，需包含或保存一个包含关键SQL和Python检查的笔记本，并确保笔记本路径易于找到。

Defaults

默认规则

Prefer small, high-signal tables over exhaustive dumps.
Compare rates, not just counts.
Break checks out by time and key segments whenever possible.
Normalize strings before judging duplicates or distinct-count spikes.
Treat recent partitions carefully when data arrives late.
Call out when an anomaly could be caused by a legitimate product launch, experiment, migration, incident, model change, or backfill.
Preserve inspectable evidence: SQL, query links, notebook paths, source paths, sample rows, chart outputs, and calculation notes.

优先使用精简的高信号表格，而非详尽的全量输出。
对比占比，而非仅对比计数。
尽可能按时间和关键分群拆分检查项。
在判断重复或去重计数突增前，先对字符串进行标准化处理。
当存在数据延迟到达的情况时，谨慎处理近期分区。
当异常可能由合法的产品发布、实验、迁移、故障、模型变更或回填导致时，需明确指出。
保留可核查的证据：SQL、查询链接、笔记本路径、源路径、样本行、图表输出和计算说明。