143 ) What are Data Quality Checks?

 

What are Data Quality Checks?

Data quality checks are automated validation rules, scripts, or queries run against a database or data pipeline to verify that stored or ingested data meets specific business and technical standards. They act as automated guardrails to catch corrupted, missing, or malformed data before it pollutes reporting dashboards, machine learning models, or downstream operational systems.

How to Test the 7 Dimensions of Data Quality (With Database Examples)

1. Consistency

• What it means: Data across different tables, systems, or related attributes does not contradict itself and adheres to defined business logic rules.

• How to check: Write validation queries comparing dependent columns or cross-referencing parent-child tables.

• Database Example: An order's total amount must equal the sum of its line items plus tax.

  SQL

  SELECT order_id 
  FROM orders o
  JOIN (
      SELECT order_id, SUM(item_price * quantity) + tax_amount AS calculated_total
      FROM order_items 
      GROUP BY order_id, tax_amount
  ) calc ON o.order_id = calc.order_id
  WHERE o.total_amount <> calc.calculated_total;
  

2. Accuracy

• What it means: The data correctly represents the real-world entity or event it models, falling within expected logical bounds or valid domains.

• How to check: Use range validation, regex pattern matching, or reference data lookups.

• Database Example: Ensuring an employee's age or birth date falls within a realistic human range, or checking that a percentage value is between 0 and 100.

  SQL

  SELECT employee_id, birth_date 
  FROM employees 
  WHERE birth_date < '1900-01-01' OR birth_date > CURRENT_DATE;
  

3. Completeness

• What it means: There are no missing critical values or unpopulated fields where data is mandatory.

• How to check: Scan for NULL values, empty strings, or unpopulated tracking attributes in mandatory columns.

• Database Example: Ensuring active customer accounts always have a registered email address.

  SQL

  SELECT customer_id 
  FROM customers 
  WHERE status = 'ACTIVE' AND (email IS NULL OR TRIM(email) = '');
  

4. Auditability

• What it means: Data changes can be tracked, traced, and verified back to their source, ensuring proper lineage and accountability.

• How to check: Verify that system logging, metadata tracking columns (like created_at, updated_by), or change-data-capture (CDC) tables are correctly populated on every write operation.

• Database Example: Ensuring every updated financial record has a corresponding log entry identifying who modified it and when.

  SQL

  SELECT account_id 
  FROM financial_accounts 
  WHERE updated_at IS NOT NULL AND updated_by IS NULL;
  

5. Orderliness

• What it means: Sequential data (such as timestamps, version numbers, or status transitions) follows a logical chronological or hierarchical sequence without breaking flow.

• How to check: Test that dependent chronological events occur in the correct order.

• Database Example: An order's shipping date cannot occur before its order placement date.

  SQL

  SELECT order_id 
  FROM orders 
  WHERE shipped_date < order_date;
  

6. Uniqueness

• What it means: Records or key identifiers that should be distinct do not contain accidental duplicates.

• How to check: Group by the unique identifier column and count occurrences where the count exceeds 1.

• Database Example: Checking for duplicate user accounts registered with the exact same tax ID or username.

  SQL

  SELECT tax_id, COUNT(*) 
  FROM users 
  GROUP BY tax_id 
  HAVING COUNT(*) > 1;
  

7. Timeliness

• What it means: Data is up to date, available on time, and arriving within its expected Service Level Agreement (SLA) window.

• How to check: Compare ingestion timestamps against the current system time to flag stale data or delayed pipeline batch loads.

• Database Example: Checking if transaction logs for the current day have failed to update within the last 2 hours.

  SQL

  SELECT MAX(transaction_timestamp) AS latest_transaction 
  FROM transactions 
  HAVING MAX(transaction_timestamp) < CURRENT_TIMESTAMP - INTERVAL '2 HOUR';