🔍 DQ Checks

Data Quality checks confirm that your data meets the expected business and technical rules at every layer of the warehouse. Commonly referred as Data Validation, Data Integrity, or Data Consistency, Data Quality checks are a critical part of data management and ensure that your data is accurate, reliable, and consistent.

---

Why?

• Prevents reporting errors, analytical inconsistencies, and loss of trust in data.
• Detects anomalies and issues early at every stage of processing.

Where?

• Applicable at every layer — from landing, through core, to semantic and reporting.

---

Data Quality Checks

Below are the most commonly used Data Quality checks in X-AutoMate, grouped by category. Each check maps to a corresponding Test Approach.

---

🔍 Emptiness

1. Null Count — value_missing_null

Counts the number of records with a NULL value in a given column.

Description: Identifies missing, incomplete, or improperly loaded data. Especially important for mandatory fields like customer names, transaction amounts, or key business dates.

Why important? Missing values can cause wrong conclusions, break business processes, or distort KPIs.

Example:

SELECT COUNT(*)
FROM customer
WHERE first_name IS NULL;

---

2. Empty Values — value_empty

Detects empty or blank string values where content is expected.

Example:

SELECT *
FROM customer
WHERE TRIM(first_name) = '' OR first_name IS NULL;

---

3. Custom Empty — value_empty_custom

Checks emptiness using a custom definition (e.g., '', whitespace, 'N/A', sentinel values).

Example:

SELECT *
FROM customer
WHERE email IN ('', 'N/A', 'UNKNOWN') OR email IS NULL;

---

🔢 Numeric

1. Value Range — value_out_of_range

Checks if numeric values are within a defined range.

Example:

SELECT *
FROM products
WHERE price NOT BETWEEN 1 AND 9999;

---

2. Maximum — value_maximum

Ensures values do not exceed a defined maximum threshold.

Example:

SELECT *
FROM orders
WHERE discount_pct > 100;

---

3. Minimum — value_minimum

Ensures values are not below a defined minimum threshold.

Example:

SELECT *
FROM products
WHERE price < 0;

---

4. Illegal Values — value_illegal

Ensures numeric values are only from a predefined set. Flags values that violate allowed sets or patterns.

Example:

SELECT *
FROM orders
WHERE status NOT IN (1, 2, 3);

---

5. Uniqueness — value_unique

Detects duplicate values in a numeric column.

Example:

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

---

🕒 DateTime

1. Null Count — value_missing_null

Detects missing timestamps.

Example:

SELECT COUNT(*)
FROM visits
WHERE visit_time IS NULL;

---

2. Value Range — value_out_of_range

Validates timestamps are within a logical range.

Example:

SELECT *
FROM orders
WHERE order_date NOT BETWEEN '2024-01-01' AND CURRENT_DATE;

---

3. Structure Discovery — structure_discovery_datetime

Validates datetime columns follow expected formats and constraints.

Example:

SELECT *
FROM orders
WHERE order_date IS NULL
   OR order_date > CURRENT_DATE;

---

4. Uniqueness — value_unique

Finds duplicate timestamps.

Example:

SELECT event_time, COUNT(*)
FROM logs
GROUP BY event_time
HAVING COUNT(*) > 1;

---

🏷️ Categorical

1. Illegal Values — value_illegal

Ensures string values match a valid list.

Example:

SELECT *
FROM customer
WHERE country NOT IN ('PL', 'NO', 'SE');

---

2. Uniqueness — value_unique

Finds duplicate strings in a column.

Example:

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

---

3. Missing Values — value_empty

Detects empty or NULL string values.

Example:

SELECT *
FROM customer
WHERE TRIM(first_name) = '' OR first_name IS NULL;

---

4. Email Validation — structure_discovery_email

Validates email fields follow the expected structure.

Example:

SELECT *
FROM users
WHERE email NOT REGEXP '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$';

---

5. Phone Validation — structure_discovery_phone

Validates phone fields follow the expected structure (e.g., country code, length, allowed characters).

Example:

SELECT *
FROM contacts
WHERE phone NOT REGEXP '^\+?[0-9]{7,15}$';

---

6. Custom Validation — value_custom

Applies custom business rules (including cross-field checks/lookups) to flag invalid values.

Example:

SELECT *
FROM orders
WHERE ship_date < order_date;

---

✅ Using these DQ checks ensures high data quality across all stages — from loading to reporting. See the Test Approaches Catalog for the full list of available approaches.