Post-load data quality validation patterns for Qlik Sense. Provides query templates for null-rate analysis, referential integrity checks, value distribution analysis, row-count validation, orphaned-record detection, sparse-field identification, and duplicate-key detection — usable against a live tenant via MCP or any post-load data interface. Also provides patterns for embedding validation checks directly into load scripts so failures surface during reload rather than in production. Load when running data quality checks against a loaded app, writing diagnostic or validation scripts, or designing fail-fast assertions inside a load script.
Post-load data quality validation patterns for Qlik Sense. Provides query templates for null-rate analysis, referential integrity checks, value distribution analysis, row-count validation, orphaned-record detection, sparse-field identification, and duplicate-key detection — usable against a live tenant via MCP or any post-load data interface. Also provides patterns for embedding validation checks directly into load scripts so failures surface during reload rather than in production. Load when running data quality checks against a loaded app, writing diagnostic or validation scripts, or designing fail-fast assertions inside a load script.
user-invocable
false
Overview
Post-load data quality validation catches issues that successful reloads can mask. Scripts reload without errors yet contain incorrect data: synthetic keys, orphaned records, unexpected nulls, duplicate keys, or value anomalies. This skill covers two usage contexts:
Post-load inspection — Run validation queries against loaded data or via MCP database connections, producing a Data Quality Validation Report.
Embedded script checks — Embed validation checks directly in load scripts, reporting issues during reload via TRACE or the error-handling framework.
The skill provides actionable query templates and patterns for both contexts.
Section 1: Validation Categories
1. Null Rate Analysis
What it catches: Fields with unexpected null rates. Key fields should have 0% nulls. Dimension fields may have expected nulls (handled by NullAsValue) or unexpected ones indicating missing source data.
2. Referential Integrity
What it catches: Foreign keys in fact tables that don't match any primary key in the corresponding dimension. Orphaned records that reference non-existent dimension members.
3. Value Distribution
What it catches: Unexpected values ('test', 'TBD', encoding artifacts), values outside expected ranges, categorical fields with unexpected cardinality (too many or too few unique values).
4. Row Count Validation
What it catches: Actual row counts differ significantly from expected counts (from source profile). May indicate incomplete loads, accidental filtering, or incremental load logic errors.
5. Duplicate Detection
What it catches: Records with duplicate primary keys (key uniqueness violation), full-row duplicates (all field values identical), which corrupt dimensional relationships.
6. Sparse Field Analysis
What it catches: Fields populated for <10% of records (configurable threshold). Candidates for removal or NullAsValue handling.
7. Field Type Consistency
What it catches: Fields where Qlik inferred a different type than expected (text loaded as numeric string, or vice versa), indicating data quality issues or mapping errors.
Section 2: Embedded Script Validation Patterns
Validation checks embedded in load scripts run during reload, catching issues before the app is stored.
Row Count Validation Pattern
LET vExpectedRows = 50000; // From source profile
LET vActualRows = NoOfRows('TableName');
IF $(vActualRows) < $(vExpectedRows) * 0.9 THEN
TRACE [WARNING] TableName row count $(vActualRows) is more than 10% below expected $(vExpectedRows);
END IF
Key Uniqueness Check Pattern
[_DupCheck]:
LOAD [Order.Key], Count([Order.Key]) AS _dup_count
RESIDENT [Orders]
GROUP BY [Order.Key];
LET vDupCount = 0;
[_DupSummary]:
LOAD Count([Order.Key]) AS _total_dups
RESIDENT [_DupCheck]
WHERE _dup_count > 1;
LET vDupCount = Peek('_total_dups', 0, '_DupSummary');
DROP TABLES [_DupCheck], [_DupSummary];
IF $(vDupCount) > 0 THEN
TRACE [WARNING] Orders has $(vDupCount) duplicate key values;
END IF
Null Rate Check Pattern
LET vNullCount = 0;
[_NullCheck]:
LOAD NullCount([Order.Key]) AS _null_count
RESIDENT [Orders];
LET vNullCount = Peek('_null_count', 0, '_NullCheck');
DROP TABLE [_NullCheck];
IF $(vNullCount) > 0 THEN
TRACE [CRITICAL] Orders.[Order.Key] has $(vNullCount) null values;
END IF
Note:NullCount() is the idiomatic Qlik aggregation for counting NULL values in a field. Do not use Count(*) here — it is not valid in Qlik LOAD context. To count all rows in a loaded table (regardless of nulls), use NoOfRows('TableName') after the LOAD instead.
Integration with Error Handling
If error-handling.qvs is loaded, use the LogMessage subroutine instead of raw TRACE:
IF $(vDupCount) > 0 THEN
CALL LogMessage('WARNING', 'Data Quality', 'Orders has ' & $(vDupCount) & ' duplicate keys');
END IF
Section 3: Post-Load Validation Queries
When MCP database connectivity or post-load data access is available, deeper analysis is possible. See validation-queries.md for complete query templates.
Queries run against the loaded Qlik data model (via engine API or diagnostic scripts) or against source databases (via MCP) to compare source vs. loaded data. The output is the Data Quality Validation Report defined below.
Section 4: Data Quality Validation Report Format
This output format standardizes how validation findings are reported:
# Data Quality Validation Report**Date:** [date]
**App:** [app name]
**Validation Type:** [Embedded Script | Post-Load | MCP Source Comparison]
## Summary- Tables Validated: [N]
- Critical Issues: [N]
- Warnings: [N]
- Clean: [N]
## Findings### [Table Name]
| Check | Result | Details |
|-------|--------|---------|
| Row Count | [PASS/WARN/FAIL] | Expected: N, Actual: N |
| Key Uniqueness | [PASS/WARN/FAIL] | [N] duplicate keys found |
| Null Rate ([Order.Key]) | [PASS/WARN/FAIL] | [N]% null |
| Null Rate ([Order.Amount]) | [PASS/WARN/FAIL] | [N]% null |
| Value Distribution | [PASS/WARN/FAIL] | [N] unexpected values ('test', 'TBD') found |
| Referential Integrity | [PASS/WARN/FAIL] | [N] orphaned [Customer.Key] references |
| Sparse Fields | [PASS/WARN/FAIL] | [field_list] populated <10% |
### Recommendations
- [List any data quality issues requiring script fixes or downstream handling]
- [List any expected anomalies to document as known limitations]
Section 5: Cross-Reference to Diagnostic Patterns
The qlik-load-script skill includes references/diagnostic-patterns.md, which documents TRACE-based logging and basic row count checks during reload. The data-quality-validator extends beyond those basic patterns with deeper analysis queries.
Distinction:
qlik-load-script → references/diagnostic-patterns.md — Lightweight TRACE milestone tracking, row count logging, file existence checks, error capture during reload. Use for real-time reload monitoring.
data-quality-validator — Comprehensive post-load validation (null rates, referential integrity, value distributions, duplicates). Use for detailed data quality inspection after reload completes.
Cross-reference qlik-load-script → references/diagnostic-patterns.md when embedding simple row count checks; use data-quality-validator when performing detailed QA analysis.
Validation Query Structure
All queries in validation-queries.md follow these conventions:
Qlik Resident queries — Run during reload as RESIDENT LOADs, no SQL syntax
