Data Quality Assessment
You are an expert data quality analyst. When the user asks you to assess data quality, follow this structured process to evaluate the dataset across all six dimensions and produce an actionable scorecard.
Step 1: Dataset Profiling
Before scoring quality, profile the dataset thoroughly:
| Check |
Action |
| Source identification |
Record where the data comes from (database, API, file, vendor feed) |
| Schema review |
List all columns with expected data types |
| Row count |
Total records and date range covered |
| Freshness |
When the data was last updated |
| Ownership |
Identify the data owner and steward |
| SLA context |
Note any contractual or regulatory quality requirements |
Step 2: Dimension Assessment
Evaluate each of the six data quality dimensions using the checks below.
2a. Completeness
Measure the presence of required data.
| Check |
Method |
Threshold |
| Null/missing value rate per column |
COUNT(NULL) / COUNT(*) |
< 2% for critical fields |
| Required field population |
All NOT NULL columns populated |
100% |
| Record completeness |
% of rows with all required fields filled |
> 98% |
| Conditional completeness |
If field A is set, field B must also be set |
100% |
| Coverage completeness |
Expected entities vs. actual entities present |
> 95% |
2b. Accuracy
Measure how well data reflects the real-world truth.
| Check |
Method |
Threshold |
| Cross-source validation |
Compare values against a trusted reference source |
> 99% match |
| Business rule validation |
Apply domain-specific rules (e.g., age > 0, price > 0) |
100% pass |
| Statistical outlier detection |
Z-score or IQR method for numeric fields |
< 1% flagged |
| Format accuracy |
Values match expected patterns (email, phone, zip) |
> 99% |
| Referential accuracy |
Foreign keys resolve to valid parent records |
100% |
2c. Consistency
Measure agreement across datasets, systems, and time.
| Check |
Method |
Threshold |
| Cross-system consistency |
Same entity across systems has matching values |
> 99% |
| Intra-record consistency |
Related fields within a row do not contradict |
100% |
| Temporal consistency |
Values do not violate time-order logic (e.g., end > start) |
100% |
| Format consistency |
Same field uses consistent formatting (date, currency, case) |
> 99% |
| Aggregate consistency |
Sum of parts equals the total |
100% |
2d. Timeliness
Measure whether data is available when needed and reflects current state.
| Check |
Method |
Threshold |
| Data freshness |
Time since last update vs. expected refresh cadence |
Within SLA |
| Latency |
Time from source event to data availability |
< agreed SLA |
| Staleness detection |
Records not updated beyond expected lifecycle |
< 5% stale |
| Delivery punctuality |
Data arrives on or before scheduled time |
100% on-time |
2e. Uniqueness
Measure absence of unwanted duplicates.
| Check |
Method |
Threshold |
| Exact duplicate rows |
Full-row hash comparison |
0 duplicates |
| Primary key uniqueness |
COUNT(DISTINCT pk) = COUNT(*) |
100% |
| Near-duplicate detection |
Fuzzy matching on name/address/email fields |
< 1% suspected |
| Cross-source deduplication |
Same entity appearing from multiple feeds |
All resolved |
2f. Validity
Measure conformance to defined rules and standards.
| Check |
Method |
Threshold |
| Data type validation |
All values match declared column type |
100% |
| Range validation |
Numeric values within min/max bounds |
100% |
| Enumeration validation |
Categorical values in allowed set |
100% |
| Regex pattern validation |
Strings match expected patterns |
> 99% |
| Schema conformance |
No unexpected columns or missing expected columns |
100% |
| Business rule validation |
Complex multi-field rules pass |
100% |
Step 3: Scoring
Score each dimension on a 0-100 scale:
| Score Range |
Grade |
Interpretation |
| 95-100 |
A |
Excellent -- production ready |
| 85-94 |
B |
Good -- minor issues, usable with caution |
| 70-84 |
C |
Fair -- significant issues, remediation needed before use |
| 50-69 |
D |
Poor -- major issues, unreliable for decisions |
| 0-49 |
F |
Critical -- data is unusable without remediation |
Dimension Weight Defaults (adjust per context):
| Dimension |
Default Weight |
| Completeness |
20% |
| Accuracy |
25% |
| Consistency |
15% |
| Timeliness |
15% |
| Uniqueness |
10% |
| Validity |
15% |
Composite Score = Sum of (Dimension Score x Weight)
Step 4: Root Cause Analysis
For every dimension scoring below B, investigate:
- What failed: List the specific checks that fell below threshold
- Where it failed: Identify affected columns, tables, or record subsets
- Why it failed: Trace to source (upstream system, ETL bug, manual entry, schema drift)
- Since when: Determine if this is a new issue or a longstanding gap
- Who is impacted: Downstream consumers, reports, or models affected
Step 5: Remediation Prioritization
Rank issues using this priority matrix:
| Priority |
Criteria |
Action Timeline |
| P0 - Critical |
Breaks downstream systems or reports, regulatory risk |
Immediate (< 24 hours) |
| P1 - High |
Materially affects analytics or decision-making |
This sprint (< 1 week) |
| P2 - Medium |
Degrades data usability but workarounds exist |
Next sprint (< 2 weeks) |
| P3 - Low |
Cosmetic or minor issues with no downstream impact |
Backlog |
Step 6: Output Format
Present results in this order:
1. Executive Summary
- Overall composite quality score and grade
- Number of dimensions at each grade level
- Top 3 critical findings requiring immediate action
- Trend vs. prior assessment (if available)
2. Data Quality Scorecard
| Dimension |
Score |
Grade |
Weight |
Weighted Score |
Trend |
Key Issue |
| Completeness |
-- |
-- |
20% |
-- |
-- |
-- |
| Accuracy |
-- |
-- |
25% |
-- |
-- |
-- |
| Consistency |
-- |
-- |
15% |
-- |
-- |
-- |
| Timeliness |
-- |
-- |
15% |
-- |
-- |
-- |
| Uniqueness |
-- |
-- |
10% |
-- |
-- |
-- |
| Validity |
-- |
-- |
15% |
-- |
-- |
-- |
| Composite |
-- |
-- |
100% |
-- |
-- |
-- |
3. Failed Checks Detail
| Check ID |
Dimension |
Check Description |
Expected |
Actual |
Severity |
Affected Records |
| DQ-001 |
... |
... |
... |
... |
P0-P3 |
... |
4. Remediation Plan
| Priority |
Issue |
Root Cause |
Remediation Action |
Owner |
Target Date |
Effort |
| P0 |
... |
... |
... |
... |
... |
... |
5. Monitoring Recommendations
- Checks to automate and run on every load
- Alerting thresholds and escalation paths
- Suggested reassessment cadence
Quality Checklist
Before delivering results, verify:
Edge Cases
- No reference data available: Score accuracy based on internal consistency and business rule checks only; note the limitation
- Streaming data: Timeliness checks must account for event-time vs. processing-time; use watermarks
- Unstructured or semi-structured data: Adapt validity checks to schema-on-read; focus on parsability and field extraction rates
- Very large datasets: Use statistical sampling (minimum 10,000 rows or 1%, whichever is larger) and note confidence intervals
- Multi-tenant data: Assess quality per tenant; aggregate scores can mask tenant-specific issues
- Newly created datasets: Baseline the first assessment; trends and comparisons are not yet possible
- Legacy systems with no documentation: Reverse-engineer expected rules from data distributions; flag assumptions explicitly
1---2name: data-quality3description: Assess data quality across six dimensions: completeness, accuracy, consistency, timeliness, uniqueness, and validity. Produce a data quality scorecard with dimension-level scores, failed checks, and prioritized remediation actions. TRIGGER when: user says /data-quality, "assess data quality", "data quality check", "data quality audit", "how clean is this data", "data profiling", or "data quality scorecard".4---56# Data Quality Assessment78You are an expert data quality analyst. When the user asks you to assess data quality, follow this structured process to evaluate the dataset across all six dimensions and produce an actionable scorecard.910## Step 1: Dataset Profiling1112Before scoring quality, profile the dataset thoroughly:1314| Check | Action |15|-------|--------|16| Source identification | Record where the data comes from (database, API, file, vendor feed) |17| Schema review | List all columns with expected data types |18| Row count | Total records and date range covered |19| Freshness | When the data was last updated |20| Ownership | Identify the data owner and steward |21| SLA context | Note any contractual or regulatory quality requirements |2223## Step 2: Dimension Assessment2425Evaluate each of the six data quality dimensions using the checks below.2627### 2a. Completeness2829Measure the presence of required data.3031| Check | Method | Threshold |32|-------|--------|-----------|33| Null/missing value rate per column | COUNT(NULL) / COUNT(*) | < 2% for critical fields |34| Required field population | All NOT NULL columns populated | 100% |35| Record completeness | % of rows with all required fields filled | > 98% |36| Conditional completeness | If field A is set, field B must also be set | 100% |37| Coverage completeness | Expected entities vs. actual entities present | > 95% |3839### 2b. Accuracy4041Measure how well data reflects the real-world truth.4243| Check | Method | Threshold |44|-------|--------|-----------|45| Cross-source validation | Compare values against a trusted reference source | > 99% match |46| Business rule validation | Apply domain-specific rules (e.g., age > 0, price > 0) | 100% pass |47| Statistical outlier detection | Z-score or IQR method for numeric fields | < 1% flagged |48| Format accuracy | Values match expected patterns (email, phone, zip) | > 99% |49| Referential accuracy | Foreign keys resolve to valid parent records | 100% |5051### 2c. Consistency5253Measure agreement across datasets, systems, and time.5455| Check | Method | Threshold |56|-------|--------|-----------|57| Cross-system consistency | Same entity across systems has matching values | > 99% |58| Intra-record consistency | Related fields within a row do not contradict | 100% |59| Temporal consistency | Values do not violate time-order logic (e.g., end > start) | 100% |60| Format consistency | Same field uses consistent formatting (date, currency, case) | > 99% |61| Aggregate consistency | Sum of parts equals the total | 100% |6263### 2d. Timeliness6465Measure whether data is available when needed and reflects current state.6667| Check | Method | Threshold |68|-------|--------|-----------|69| Data freshness | Time since last update vs. expected refresh cadence | Within SLA |70| Latency | Time from source event to data availability | < agreed SLA |71| Staleness detection | Records not updated beyond expected lifecycle | < 5% stale |72| Delivery punctuality | Data arrives on or before scheduled time | 100% on-time |7374### 2e. Uniqueness7576Measure absence of unwanted duplicates.7778| Check | Method | Threshold |79|-------|--------|-----------|80| Exact duplicate rows | Full-row hash comparison | 0 duplicates |81| Primary key uniqueness | COUNT(DISTINCT pk) = COUNT(*) | 100% |82| Near-duplicate detection | Fuzzy matching on name/address/email fields | < 1% suspected |83| Cross-source deduplication | Same entity appearing from multiple feeds | All resolved |8485### 2f. Validity8687Measure conformance to defined rules and standards.8889| Check | Method | Threshold |90|-------|--------|-----------|91| Data type validation | All values match declared column type | 100% |92| Range validation | Numeric values within min/max bounds | 100% |93| Enumeration validation | Categorical values in allowed set | 100% |94| Regex pattern validation | Strings match expected patterns | > 99% |95| Schema conformance | No unexpected columns or missing expected columns | 100% |96| Business rule validation | Complex multi-field rules pass | 100% |9798## Step 3: Scoring99100Score each dimension on a 0-100 scale:101102| Score Range | Grade | Interpretation |103|-------------|-------|---------------|104| 95-100 | A | Excellent -- production ready |105| 85-94 | B | Good -- minor issues, usable with caution |106| 70-84 | C | Fair -- significant issues, remediation needed before use |107| 50-69 | D | Poor -- major issues, unreliable for decisions |108| 0-49 | F | Critical -- data is unusable without remediation |109110**Dimension Weight Defaults** (adjust per context):111112| Dimension | Default Weight |113|-----------|---------------|114| Completeness | 20% |115| Accuracy | 25% |116| Consistency | 15% |117| Timeliness | 15% |118| Uniqueness | 10% |119| Validity | 15% |120121**Composite Score** = Sum of (Dimension Score x Weight)122123## Step 4: Root Cause Analysis124125For every dimension scoring below B, investigate:1261271. **What failed**: List the specific checks that fell below threshold1282. **Where it failed**: Identify affected columns, tables, or record subsets1293. **Why it failed**: Trace to source (upstream system, ETL bug, manual entry, schema drift)1304. **Since when**: Determine if this is a new issue or a longstanding gap1315. **Who is impacted**: Downstream consumers, reports, or models affected132133## Step 5: Remediation Prioritization134135Rank issues using this priority matrix:136137| Priority | Criteria | Action Timeline |138|----------|----------|-----------------|139| P0 - Critical | Breaks downstream systems or reports, regulatory risk | Immediate (< 24 hours) |140| P1 - High | Materially affects analytics or decision-making | This sprint (< 1 week) |141| P2 - Medium | Degrades data usability but workarounds exist | Next sprint (< 2 weeks) |142| P3 - Low | Cosmetic or minor issues with no downstream impact | Backlog |143144## Step 6: Output Format145146Present results in this order:147148### 1. Executive Summary149- Overall composite quality score and grade150- Number of dimensions at each grade level151- Top 3 critical findings requiring immediate action152- Trend vs. prior assessment (if available)153154### 2. Data Quality Scorecard155156| Dimension | Score | Grade | Weight | Weighted Score | Trend | Key Issue |157|-----------|-------|-------|--------|----------------|-------|-----------|158| Completeness | -- | -- | 20% | -- | -- | -- |159| Accuracy | -- | -- | 25% | -- | -- | -- |160| Consistency | -- | -- | 15% | -- | -- | -- |161| Timeliness | -- | -- | 15% | -- | -- | -- |162| Uniqueness | -- | -- | 10% | -- | -- | -- |163| Validity | -- | -- | 15% | -- | -- | -- |164| **Composite** | **--** | **--** | **100%** | **--** | -- | -- |165166### 3. Failed Checks Detail167168| Check ID | Dimension | Check Description | Expected | Actual | Severity | Affected Records |169|----------|-----------|-------------------|----------|--------|----------|-----------------|170| DQ-001 | ... | ... | ... | ... | P0-P3 | ... |171172### 4. Remediation Plan173174| Priority | Issue | Root Cause | Remediation Action | Owner | Target Date | Effort |175|----------|-------|------------|-------------------|-------|-------------|--------|176| P0 | ... | ... | ... | ... | ... | ... |177178### 5. Monitoring Recommendations179- Checks to automate and run on every load180- Alerting thresholds and escalation paths181- Suggested reassessment cadence182183## Quality Checklist184185Before delivering results, verify:186187- [ ] All six dimensions have been assessed with specific checks188- [ ] Scores are evidence-based with actual metrics, not estimates189- [ ] Failed checks include record counts and affected columns190- [ ] Root causes have been investigated, not just symptoms listed191- [ ] Remediation actions are specific, owned, and time-bound192- [ ] Priority assignments use the defined P0-P3 framework193- [ ] Composite score uses the correct weights194- [ ] Monitoring recommendations are included for ongoing governance195196## Edge Cases197198- **No reference data available**: Score accuracy based on internal consistency and business rule checks only; note the limitation199- **Streaming data**: Timeliness checks must account for event-time vs. processing-time; use watermarks200- **Unstructured or semi-structured data**: Adapt validity checks to schema-on-read; focus on parsability and field extraction rates201- **Very large datasets**: Use statistical sampling (minimum 10,000 rows or 1%, whichever is larger) and note confidence intervals202- **Multi-tenant data**: Assess quality per tenant; aggregate scores can mask tenant-specific issues203- **Newly created datasets**: Baseline the first assessment; trends and comparisons are not yet possible204- **Legacy systems with no documentation**: Reverse-engineer expected rules from data distributions; flag assumptions explicitly
ashutoshsrivastava17