Publishing strategy: publish one check at a time. Each post connects a technical validation with a business failure that can damage ETL, dashboards, operational reporting, governance, or executive decisions.
Advanced Data Quality Check #01
Schema Drift Detection
Business Scenario
A nightly customer feed changes without warning. The column customer_state disappears, region is added, and annual_revenue changes from DECIMAL to VARCHAR. The pipeline may still load, but downstream models can break or silently misinterpret the data.
Sample Data
Yesterday schema
customer_id INT
customer_name VARCHAR(100)
customer_state CHAR(2)
annual_revenue DECIMAL(12,2)
Today schema
customer_id INT
customer_name VARCHAR(100)
region VARCHAR(50)
annual_revenue VARCHAR(30)
load_date DATE
Expected Result
column_name expected_type current_type schema_status
customer_state char NULL MISSING COLUMN
region NULL varchar NEW COLUMN
annual_revenue decimal varchar TYPE CHANGED
load_date NULL date NEW COLUMN
SQL Check
-- SQL Server example: compare expected vs. current schema
WITH expected_schema AS (
SELECT 'customer_id' AS column_name, 'int' AS data_type
UNION ALL SELECT 'customer_name', 'varchar'
UNION ALL SELECT 'customer_state', 'char'
UNION ALL SELECT 'annual_revenue', 'decimal'
),
current_schema AS (
SELECT
COLUMN_NAME AS column_name,
DATA_TYPE AS data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'customers_staging'
)
SELECT
COALESCE(e.column_name, c.column_name) AS column_name,
e.data_type AS expected_type,
c.data_type AS current_type,
CASE
WHEN e.column_name IS NULL THEN 'NEW COLUMN'
WHEN c.column_name IS NULL THEN 'MISSING COLUMN'
WHEN e.data_type <> c.data_type THEN 'TYPE CHANGED'
ELSE 'MATCH'
END AS schema_status
FROM expected_schema e
FULL OUTER JOIN current_schema c
ON e.column_name = c.column_name
WHERE e.column_name IS NULL
OR c.column_name IS NULL
OR e.data_type <> c.data_type;
Why It Matters
Schema drift can break ETL, semantic models, joins, measures, machine-learning features, and scheduled refreshes. The most dangerous failures are the ones that do not produce an obvious error.
Common Mistake
Waiting for a production failure instead of validating metadata before loading the data.
BI Impact
A Power BI model may refresh with renamed or text-typed fields, causing blank visuals, broken measures, or incorrect aggregations.
Key Takeaway
The schema is also data. Validate it before trusting the rows.
Ready-to-Copy LinkedIn Post
Advanced Data Quality Check #01: Schema Drift Detection
Business scenario:
A nightly customer feed changes without warning. The column customer_state disappears, region is added, and annual_revenue changes from DECIMAL to VARCHAR. The pipeline may still load, but downstream models can break or silently misinterpret the data.
SQL check:
-- SQL Server example: compare expected vs. current schema
WITH expected_schema AS (
SELECT 'customer_id' AS column_name, 'int' AS data_type
UNION ALL SELECT 'customer_name', 'varchar'
UNION ALL SELECT 'customer_state', 'char'
UNION ALL SELECT 'annual_revenue', 'decimal'
),
current_schema AS (
SELECT
COLUMN_NAME AS column_name,
DATA_TYPE AS data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'customers_staging'
)
SELECT
COALESCE(e.column_name, c.column_name) AS column_name,
e.data_type AS expected_type,
c.data_type AS current_type,
CASE
WHEN e.column_name IS NULL THEN 'NEW COLUMN'
WHEN c.column_name IS NULL THEN 'MISSING COLUMN'
WHEN e.data_type <> c.data_type THEN 'TYPE CHANGED'
ELSE 'MATCH'
END AS schema_status
FROM expected_schema e
FULL OUTER JOIN current_schema c
ON e.column_name = c.column_name
WHERE e.column_name IS NULL
OR c.column_name IS NULL
OR e.data_type <> c.data_type;
Expected result:
column_name expected_type current_type schema_status
customer_state char NULL MISSING COLUMN
region NULL varchar NEW COLUMN
annual_revenue decimal varchar TYPE CHANGED
load_date NULL date NEW COLUMN
Why it matters:
Schema drift can break ETL, semantic models, joins, measures, machine-learning features, and scheduled refreshes. The most dangerous failures are the ones that do not produce an obvious error.
Common mistake:
Waiting for a production failure instead of validating metadata before loading the data.
BI impact:
A Power BI model may refresh with renamed or text-typed fields, causing blank visuals, broken measures, or incorrect aggregations.
Key takeaway:
The schema is also data. Validate it before trusting the rows.
More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com
#SQL #DataQuality #SchemaDrift #DataEngineering #BusinessIntelligence #ETL #LearningByDoing
Advanced Data Quality Check #02
Regex Pattern Validation
Business Scenario
A customer table contains malformed emails, ZIP codes, phone numbers, and employee IDs. The values are not NULL, but they cannot be trusted for communication, matching, or workflow automation.
customer_id | issue
1002 | invalid email, ZIP, employee ID
1003 | invalid email, ZIP, employee ID
SQL Check
-- SQL Server pattern validation
SELECT *
FROM customers
WHERE email NOT LIKE '%_@_%._%'
OR zip_code NOT LIKE '[0-9][0-9][0-9][0-9][0-9]'
OR employee_id NOT LIKE 'EMP-[0-9][0-9][0-9][0-9]';
Why It Matters
Pattern validation protects contact data, identity keys, routing codes, and integration fields from values that look populated but are structurally invalid.
Common Mistake
Treating a non-NULL string as valid simply because it contains characters.
BI Impact
Bad patterns create unmatched customers, failed notifications, invalid geographic segmentation, and duplicate identities.
Key Takeaway
Completeness is not enough. A value must also follow the expected pattern.
Ready-to-Copy LinkedIn Post
Advanced Data Quality Check #02: Regex Pattern Validation
Business scenario:
A customer table contains malformed emails, ZIP codes, phone numbers, and employee IDs. The values are not NULL, but they cannot be trusted for communication, matching, or workflow automation.
SQL check:
-- SQL Server pattern validation
SELECT *
FROM customers
WHERE email NOT LIKE '%_@_%._%'
OR zip_code NOT LIKE '[0-9][0-9][0-9][0-9][0-9]'
OR employee_id NOT LIKE 'EMP-[0-9][0-9][0-9][0-9]';
Expected result:
customer_id | issue
1002 | invalid email, ZIP, employee ID
1003 | invalid email, ZIP, employee ID
Why it matters:
Pattern validation protects contact data, identity keys, routing codes, and integration fields from values that look populated but are structurally invalid.
Common mistake:
Treating a non-NULL string as valid simply because it contains characters.
BI impact:
Bad patterns create unmatched customers, failed notifications, invalid geographic segmentation, and duplicate identities.
Key takeaway:
Completeness is not enough. A value must also follow the expected pattern.
More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com
#SQL #DataQuality #Regex #DataValidation #DataEngineering #BusinessIntelligence #LearningByDoing
Advanced Data Quality Check #03
String Quality Validation
Business Scenario
A department field appears correct, but hidden spaces, tabs, inconsistent case, and non-printable characters split one business category into multiple values.
SELECT
employee_id,
department,
LEN(department) AS visible_length,
DATALENGTH(department) AS stored_bytes,
LTRIM(RTRIM(REPLACE(department, CHAR(9), ''))) AS normalized_department
FROM employees
WHERE department <> LTRIM(RTRIM(department))
OR department LIKE '%' + CHAR(9) + '%'
OR department COLLATE Latin1_General_CS_AS
<> UPPER(department) COLLATE Latin1_General_CS_AS;
Why It Matters
Invisible characters and inconsistent casing can break grouping, joins, filters, deduplication, and accepted-value checks.
Common Mistake
Fixing only leading and trailing spaces while ignoring tabs, line breaks, Unicode variants, and non-breaking spaces.
BI Impact
One department may appear as several categories in slicers and charts, fragmenting KPIs and confusing users.
Key Takeaway
Strings should be profiled, normalized, and validated—not merely displayed.
Ready-to-Copy LinkedIn Post
Advanced Data Quality Check #03: String Quality Validation
Business scenario:
A department field appears correct, but hidden spaces, tabs, inconsistent case, and non-printable characters split one business category into multiple values.
SQL check:
SELECT
employee_id,
department,
LEN(department) AS visible_length,
DATALENGTH(department) AS stored_bytes,
LTRIM(RTRIM(REPLACE(department, CHAR(9), ''))) AS normalized_department
FROM employees
WHERE department <> LTRIM(RTRIM(department))
OR department LIKE '%' + CHAR(9) + '%'
OR department COLLATE Latin1_General_CS_AS
<> UPPER(department) COLLATE Latin1_General_CS_AS;
Expected result:
employee_id | department | normalized_department
1002 | Finance[space] | Finance
1003 | FINANCE | FINANCE
1004 | Finance[TAB] | Finance
1005 | [space]Finance | Finance
Why it matters:
Invisible characters and inconsistent casing can break grouping, joins, filters, deduplication, and accepted-value checks.
Common mistake:
Fixing only leading and trailing spaces while ignoring tabs, line breaks, Unicode variants, and non-breaking spaces.
BI impact:
One department may appear as several categories in slicers and charts, fragmenting KPIs and confusing users.
Key takeaway:
Strings should be profiled, normalized, and validated—not merely displayed.
More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com
#SQL #DataQuality #DataCleaning #StringValidation #BusinessIntelligence #Analytics #LearningByDoing
Advanced Data Quality Check #04
Hierarchy Validation
Business Scenario
A location hierarchy contains valid-looking values that do not belong together: a city is assigned to the wrong county, or a county is linked to the wrong state.
Sample Data
country | state | county | city
USA | FL | Miami-Dade | Hialeah
USA | FL | Broward | Hialeah
USA | TX | Miami-Dade | Miami
USA | FL | Miami-Dade | Miami
Expected Result
country | state | county | city
USA | FL | Broward | Hialeah
USA | TX | Miami-Dade | Miami
SQL Check
SELECT f.*
FROM fact_locations f
LEFT JOIN dim_location_hierarchy h
ON f.country = h.country
AND f.state = h.state
AND f.county = h.county
AND f.city = h.city
WHERE h.city IS NULL;
Why It Matters
Hierarchical consistency is essential for geographic, organizational, product, account, and cost-center reporting.
Common Mistake
Validating each column independently without checking whether the combination is legitimate.
BI Impact
Dashboards may assign revenue, incidents, employees, or customers to the wrong region or management structure.
Key Takeaway
A value can be valid alone and still be invalid in context.
Ready-to-Copy LinkedIn Post
Advanced Data Quality Check #04: Hierarchy Validation
Business scenario:
A location hierarchy contains valid-looking values that do not belong together: a city is assigned to the wrong county, or a county is linked to the wrong state.
SQL check:
SELECT f.*
FROM fact_locations f
LEFT JOIN dim_location_hierarchy h
ON f.country = h.country
AND f.state = h.state
AND f.county = h.county
AND f.city = h.city
WHERE h.city IS NULL;
Expected result:
country | state | county | city
USA | FL | Broward | Hialeah
USA | TX | Miami-Dade | Miami
Why it matters:
Hierarchical consistency is essential for geographic, organizational, product, account, and cost-center reporting.
Common mistake:
Validating each column independently without checking whether the combination is legitimate.
BI impact:
Dashboards may assign revenue, incidents, employees, or customers to the wrong region or management structure.
Key takeaway:
A value can be valid alone and still be invalid in context.
More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com
#SQL #DataQuality #HierarchyValidation #DataModeling #BusinessIntelligence #DataEngineering #LearningByDoing
Advanced Data Quality Check #05
Timeliness and SLA Validation
Business Scenario
A payroll file is expected by 2:00 AM every business day. Today it arrived at 5:47 AM. The file exists, but the delay may affect payroll processing and downstream reports.
feed_name | sla_status | minutes_late
Payroll_Daily | LATE | 227
CRM_Customers | ON TIME | -8
Orders_Daily | MISSING | NULL
SQL Check
SELECT
feed_name,
expected_time,
actual_arrival,
CASE
WHEN actual_arrival IS NULL THEN 'MISSING'
WHEN actual_arrival > expected_time THEN 'LATE'
ELSE 'ON TIME'
END AS sla_status,
CASE
WHEN actual_arrival IS NULL THEN NULL
ELSE DATEDIFF(MINUTE, expected_time, actual_arrival)
END AS minutes_late
FROM data_feed_log;
Why It Matters
Fresh data can still violate an operational SLA. Timeliness must be measured against the business deadline, not only against the current date.
Common Mistake
Checking only whether the table refreshed, not whether it refreshed on time.
BI Impact
Late feeds can cause incomplete morning dashboards, delayed payroll, incorrect staffing views, and missed executive reporting windows.
Key Takeaway
Freshness answers “how old?” Timeliness answers “was it delivered when required?”
Ready-to-Copy LinkedIn Post
Advanced Data Quality Check #05: Timeliness and SLA Validation
Business scenario:
A payroll file is expected by 2:00 AM every business day. Today it arrived at 5:47 AM. The file exists, but the delay may affect payroll processing and downstream reports.
SQL check:
SELECT
feed_name,
expected_time,
actual_arrival,
CASE
WHEN actual_arrival IS NULL THEN 'MISSING'
WHEN actual_arrival > expected_time THEN 'LATE'
ELSE 'ON TIME'
END AS sla_status,
CASE
WHEN actual_arrival IS NULL THEN NULL
ELSE DATEDIFF(MINUTE, expected_time, actual_arrival)
END AS minutes_late
FROM data_feed_log;
Expected result:
feed_name | sla_status | minutes_late
Payroll_Daily | LATE | 227
CRM_Customers | ON TIME | -8
Orders_Daily | MISSING | NULL
Why it matters:
Fresh data can still violate an operational SLA. Timeliness must be measured against the business deadline, not only against the current date.
Common mistake:
Checking only whether the table refreshed, not whether it refreshed on time.
BI impact:
Late feeds can cause incomplete morning dashboards, delayed payroll, incorrect staffing views, and missed executive reporting windows.
Key takeaway:
Freshness answers “how old?” Timeliness answers “was it delivered when required?”
More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com
#SQL #DataQuality #SLA #DataFreshness #ETL #BusinessIntelligence #LearningByDoing
Advanced Data Quality Check #06
Volume Anomaly Detection
Business Scenario
A daily transaction table usually receives between 1.1 and 1.3 million rows. Today it received only 14,832. No load error was reported.
WITH daily_volume AS (
SELECT load_date, COUNT(*) AS row_count
FROM transactions
GROUP BY load_date
),
baseline AS (
SELECT
AVG(CAST(row_count AS FLOAT)) AS avg_count,
STDEV(CAST(row_count AS FLOAT)) AS sd_count
FROM daily_volume
WHERE load_date < CAST(GETDATE() AS DATE)
)
SELECT
d.load_date,
d.row_count,
b.avg_count,
CASE
WHEN d.row_count < b.avg_count - (3 * b.sd_count)
OR d.row_count > b.avg_count + (3 * b.sd_count)
THEN 'ANOMALY'
ELSE 'NORMAL'
END AS volume_status
FROM daily_volume d
CROSS JOIN baseline b
WHERE d.load_date = CAST(GETDATE() AS DATE);
Why It Matters
A pipeline can complete successfully while loading only part of a file, one partition, or one source system.
Common Mistake
Using a fixed minimum forever instead of comparing volume against historical behavior, seasonality, and business calendar effects.
BI Impact
A dashboard may refresh without errors while showing only a fraction of the real activity.
Key Takeaway
Successful execution does not guarantee complete delivery.
Ready-to-Copy LinkedIn Post
Advanced Data Quality Check #06: Volume Anomaly Detection
Business scenario:
A daily transaction table usually receives between 1.1 and 1.3 million rows. Today it received only 14,832. No load error was reported.
SQL check:
WITH daily_volume AS (
SELECT load_date, COUNT(*) AS row_count
FROM transactions
GROUP BY load_date
),
baseline AS (
SELECT
AVG(CAST(row_count AS FLOAT)) AS avg_count,
STDEV(CAST(row_count AS FLOAT)) AS sd_count
FROM daily_volume
WHERE load_date < CAST(GETDATE() AS DATE)
)
SELECT
d.load_date,
d.row_count,
b.avg_count,
CASE
WHEN d.row_count < b.avg_count - (3 * b.sd_count)
OR d.row_count > b.avg_count + (3 * b.sd_count)
THEN 'ANOMALY'
ELSE 'NORMAL'
END AS volume_status
FROM daily_volume d
CROSS JOIN baseline b
WHERE d.load_date = CAST(GETDATE() AS DATE);
Expected result:
load_date | row_count | avg_count | volume_status
2026-07-11 | 14,832 | 1,214,232 | ANOMALY
Why it matters:
A pipeline can complete successfully while loading only part of a file, one partition, or one source system.
Common mistake:
Using a fixed minimum forever instead of comparing volume against historical behavior, seasonality, and business calendar effects.
BI impact:
A dashboard may refresh without errors while showing only a fraction of the real activity.
Key takeaway:
Successful execution does not guarantee complete delivery.
More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com
#SQL #DataQuality #AnomalyDetection #ETL #DataEngineering #BusinessIntelligence #LearningByDoing
Advanced Data Quality Check #07
Distribution Drift Detection
Business Scenario
The row count is normal, but customer revenue has shifted dramatically. The average doubled, the median barely changed, and the 95th percentile increased tenfold.
-- SQL Server 2022 example
WITH stats AS (
SELECT
load_date,
AVG(CAST(revenue AS FLOAT)) AS avg_revenue,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue)
OVER (PARTITION BY load_date) AS median_revenue,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY revenue)
OVER (PARTITION BY load_date) AS p95_revenue
FROM sales
)
SELECT DISTINCT
load_date,
avg_revenue,
median_revenue,
p95_revenue
FROM stats
ORDER BY load_date;
Why It Matters
Distribution drift can reveal unit changes, mapping errors, duplicated high-value records, decimal shifts, population changes, or genuine business events.
Common Mistake
Comparing only row counts and averages while ignoring median, percentiles, spread, and category distribution.
BI Impact
KPIs may look plausible at total level while the underlying population has changed in a way that invalidates comparisons.
Key Takeaway
Data quality includes the shape of the data—not only individual values.
Ready-to-Copy LinkedIn Post
Advanced Data Quality Check #07: Distribution Drift Detection
Business scenario:
The row count is normal, but customer revenue has shifted dramatically. The average doubled, the median barely changed, and the 95th percentile increased tenfold.
SQL check:
-- SQL Server 2022 example
WITH stats AS (
SELECT
load_date,
AVG(CAST(revenue AS FLOAT)) AS avg_revenue,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue)
OVER (PARTITION BY load_date) AS median_revenue,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY revenue)
OVER (PARTITION BY load_date) AS p95_revenue
FROM sales
)
SELECT DISTINCT
load_date,
avg_revenue,
median_revenue,
p95_revenue
FROM stats
ORDER BY load_date;
Expected result:
load_date | avg_revenue | median_revenue | p95_revenue
2026-07-10 | 420 | 210 | 1,200
2026-07-11 | 870 | 225 | 12,900
Why it matters:
Distribution drift can reveal unit changes, mapping errors, duplicated high-value records, decimal shifts, population changes, or genuine business events.
Common mistake:
Comparing only row counts and averages while ignoring median, percentiles, spread, and category distribution.
BI impact:
KPIs may look plausible at total level while the underlying population has changed in a way that invalidates comparisons.
Key takeaway:
Data quality includes the shape of the data—not only individual values.
More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com
#SQL #DataQuality #DistributionDrift #AnomalyDetection #Analytics #BusinessIntelligence #LearningByDoing
Advanced Data Quality Check #08
Audit Column Validation
Business Scenario
A record shows ModifiedDate earlier than CreatedDate, a deleted row has no DeletedDate, and some updates have no ModifiedBy user.
record_id | audit_issue
1002 | modified before created; missing modified_by
1003 | deleted flag without deleted_date
SQL Check
SELECT *
FROM customer_master
WHERE modified_date < created_date
OR (modified_date IS NOT NULL AND modified_by IS NULL)
OR (deleted_flag = 1 AND deleted_date IS NULL)
OR (deleted_flag = 0 AND deleted_date IS NOT NULL);
Why It Matters
Audit columns support traceability, accountability, change history, slowly changing dimensions, and regulatory review.
Common Mistake
Adding audit columns to the table design but never validating whether they follow consistent rules.
BI Impact
Incorrect audit metadata can break incremental refresh, active/inactive logic, historical reporting, and compliance evidence.
Key Takeaway
Audit columns are controls only when their relationships are validated.
Ready-to-Copy LinkedIn Post
Advanced Data Quality Check #08: Audit Column Validation
Business scenario:
A record shows ModifiedDate earlier than CreatedDate, a deleted row has no DeletedDate, and some updates have no ModifiedBy user.
SQL check:
SELECT *
FROM customer_master
WHERE modified_date < created_date
OR (modified_date IS NOT NULL AND modified_by IS NULL)
OR (deleted_flag = 1 AND deleted_date IS NULL)
OR (deleted_flag = 0 AND deleted_date IS NOT NULL);
Expected result:
record_id | audit_issue
1002 | modified before created; missing modified_by
1003 | deleted flag without deleted_date
Why it matters:
Audit columns support traceability, accountability, change history, slowly changing dimensions, and regulatory review.
Common mistake:
Adding audit columns to the table design but never validating whether they follow consistent rules.
BI impact:
Incorrect audit metadata can break incremental refresh, active/inactive logic, historical reporting, and compliance evidence.
Key takeaway:
Audit columns are controls only when their relationships are validated.
More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com
#SQL #DataQuality #AuditColumns #DataGovernance #Compliance #BusinessIntelligence #LearningByDoing
Advanced Data Quality Check #09
Cross-System Reconciliation
Business Scenario
HR reports 3,842 active employees, payroll reports 3,817 paid employees, and benefits reports 3,901 enrolled members. The difference may be legitimate—or it may reveal missed terminations, delayed hires, or mapping issues.
Sample Data
system | employee_id | status
HR | E1001 | ACTIVE
Payroll | E1001 | ACTIVE
Benefits | E1001 | ACTIVE
HR | E1002 | ACTIVE
Benefits | E1002 | ACTIVE
Payroll | E1003 | ACTIVE
SELECT
COALESCE(h.employee_id, p.employee_id, b.employee_id) AS employee_id,
CASE WHEN h.employee_id IS NOT NULL THEN 1 ELSE 0 END AS in_hr,
CASE WHEN p.employee_id IS NOT NULL THEN 1 ELSE 0 END AS in_payroll,
CASE WHEN b.employee_id IS NOT NULL THEN 1 ELSE 0 END AS in_benefits
FROM hr_active h
FULL OUTER JOIN payroll_active p
ON h.employee_id = p.employee_id
FULL OUTER JOIN benefits_active b
ON COALESCE(h.employee_id, p.employee_id) = b.employee_id
WHERE h.employee_id IS NULL
OR p.employee_id IS NULL
OR b.employee_id IS NULL;
Why It Matters
Enterprise truth is often distributed across multiple systems. Reconciliation reveals timing gaps, integration defects, ownership confusion, and inconsistent definitions.
Common Mistake
Comparing only grand totals without identifying the exact records that explain the difference.
BI Impact
Different dashboards may show conflicting headcount, payroll, customer, inventory, or revenue figures.
Key Takeaway
Reconciliation requires both total comparison and record-level explanation.
Ready-to-Copy LinkedIn Post
Advanced Data Quality Check #09: Cross-System Reconciliation
Business scenario:
HR reports 3,842 active employees, payroll reports 3,817 paid employees, and benefits reports 3,901 enrolled members. The difference may be legitimate—or it may reveal missed terminations, delayed hires, or mapping issues.
SQL check:
SELECT
COALESCE(h.employee_id, p.employee_id, b.employee_id) AS employee_id,
CASE WHEN h.employee_id IS NOT NULL THEN 1 ELSE 0 END AS in_hr,
CASE WHEN p.employee_id IS NOT NULL THEN 1 ELSE 0 END AS in_payroll,
CASE WHEN b.employee_id IS NOT NULL THEN 1 ELSE 0 END AS in_benefits
FROM hr_active h
FULL OUTER JOIN payroll_active p
ON h.employee_id = p.employee_id
FULL OUTER JOIN benefits_active b
ON COALESCE(h.employee_id, p.employee_id) = b.employee_id
WHERE h.employee_id IS NULL
OR p.employee_id IS NULL
OR b.employee_id IS NULL;
Expected result:
employee_id | in_hr | in_payroll | in_benefits
E1002 | 1 | 0 | 1
E1003 | 0 | 1 | 0
Why it matters:
Enterprise truth is often distributed across multiple systems. Reconciliation reveals timing gaps, integration defects, ownership confusion, and inconsistent definitions.
Common mistake:
Comparing only grand totals without identifying the exact records that explain the difference.
BI impact:
Different dashboards may show conflicting headcount, payroll, customer, inventory, or revenue figures.
Key takeaway:
Reconciliation requires both total comparison and record-level explanation.
More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com
#SQL #DataQuality #Reconciliation #DataGovernance #ERP #BusinessIntelligence #LearningByDoing
Advanced Data Quality Check #10
Enterprise Data Quality Score
Business Scenario
Executives want one score per table, but not every rule has the same business importance. Missing customer IDs should weigh more than inconsistent capitalization.
WITH rule_scores AS (
SELECT
rule_name,
weight,
CASE
WHEN total_rows = 0 THEN 0
ELSE 1.0 - (CAST(failed_rows AS FLOAT) / total_rows)
END AS pass_rate
FROM data_quality_results
)
SELECT
CAST(SUM(pass_rate * weight) * 100 AS DECIMAL(5,2)) AS data_quality_score
FROM rule_scores;
Why It Matters
A weighted score helps prioritize remediation, communicate risk, track improvement, and compare tables or domains consistently.
Common Mistake
Creating one percentage that hides critical failures, uses equal weights blindly, or mixes rules with different denominators.
BI Impact
A score can become an executive KPI, but it must always allow drill-through to the failed rules and affected records.
Key Takeaway
A score summarizes quality; it must never replace the underlying evidence.
Ready-to-Copy LinkedIn Post
Advanced Data Quality Check #10: Enterprise Data Quality Score
Business scenario:
Executives want one score per table, but not every rule has the same business importance. Missing customer IDs should weigh more than inconsistent capitalization.
SQL check:
WITH rule_scores AS (
SELECT
rule_name,
weight,
CASE
WHEN total_rows = 0 THEN 0
ELSE 1.0 - (CAST(failed_rows AS FLOAT) / total_rows)
END AS pass_rate
FROM data_quality_results
)
SELECT
CAST(SUM(pass_rate * weight) * 100 AS DECIMAL(5,2)) AS data_quality_score
FROM rule_scores;
Expected result:
data_quality_score
82.64
Why it matters:
A weighted score helps prioritize remediation, communicate risk, track improvement, and compare tables or domains consistently.
Common mistake:
Creating one percentage that hides critical failures, uses equal weights blindly, or mixes rules with different denominators.
BI impact:
A score can become an executive KPI, but it must always allow drill-through to the failed rules and affected records.
Key takeaway:
A score summarizes quality; it must never replace the underlying evidence.
More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com
#SQL #DataQuality #DataGovernance #DataQualityScore #BusinessIntelligence #DataEngineering #LearningByDoing
BONUS
Enterprise Data Quality Checklist
Use this consolidated checklist before trusting a production pipeline, semantic model, or enterprise dashboard.