Data Quality & ETL Testing

Software quality does not stop at the API response. A request can return 200 OK and still write the wrong value, drop a row during a load or leave an orphan behind. I test data quality at two levels: the data an application writes (SQL and NoSQL), and the data pipelines that move it (ETL).

Data quality dimensions I check

DimensionQuestionTypical check
CompletenessIs anything missing?Row counts source versus target, null checks on required fields
UniquenessIs anything duplicated?Group by business key with a count above one
ValidityDoes it respect the rules?Types, ranges, formats and allowed values
ConsistencyDo related records agree?Foreign keys, orphan rows, totals across tables
TimelinessIs it fresh enough?Latest load date against the expected schedule
AccuracyIs it the right value?Spot checks of transformed values against the source

Testing the data behind an API with Firestore

My backend suites do not stop at the HTTP status. They connect to Cloud Firestore, a NoSQL database, for two reasons. First, to read the unique identifiers that an endpoint creates but does not return, so the next call in the circuit can use them. Second, to verify that the data was written where and how it should be, and to clean up afterwards so every run starts from a known state. The full pattern is on the backend test automation page.

import os
import firebase_admin
from firebase_admin import credentials, firestore

# The service account key is never committed: its path comes from the environment,
# and in CI the key is written from a repository secret.
cred = credentials.Certificate(os.environ["GOOGLE_APPLICATION_CREDENTIALS"])
firebase_admin.initialize_app(cred)
db = firestore.client()

# Did the endpoint create the document?
verify = db.collection("clients").document(client_id).get()
assert verify.exists, "the endpoint did not create the document"

# Which are the five most recent records?
latest = (
    db.collection("clients")
    .order_by("lastVisit", direction=firestore.Query.DESCENDING)
    .limit_to_last(5)
    .get()
)
for doc in latest:
    print(doc.id)

ETL pipeline testing with Pentaho Data Integration

I have built and run an ETL pipeline with Pentaho Data Integration (Kettle), which is the best way to learn where an ETL can break. A master job orchestrates the stages, and each stage is its own job with transformations behind it:

START
 └─ Pre-flight check          source file exists?
      ├─ missing  → email notification → abort ("source not available")
      └─ available
          ├─ Extraction       filter rows · map values · sort · replace nulls
          ├─ Transformation   CSV input → select and map fields → CSV output
          ├─ Warehouse load   CSV input → table output (SQL Server)
          ├─ Alerts module    notifications on the outcome
          └─ Backups          table input → update · text file output · email · log
     any failure → abort the job

Seeing the pipeline from the inside changes how I test one. The checks I put around each stage:

  • Pre-flight: the job must fail fast, and loudly, when its source is missing. A pipeline that silently loads nothing is worse than one that stops.
  • Extraction and transformation: compare rows read, written and rejected from the job logs, and spot check mapped values against the source.
  • Load: reconcile counts between staging and the warehouse, then check keys and relationships (queries below).
  • Failure paths: deliberately break an input and confirm that the notification is sent and the job aborts, because the unhappy path is where pipelines hide bugs.

SQL reconciliation checks after a load

These are the queries I run first. They are generic examples of the pattern, not tied to a specific schema:

-- Completeness: row counts between staging and the warehouse
SELECT 'staging' AS layer, COUNT(*) AS total FROM stg_customer
UNION ALL
SELECT 'warehouse', COUNT(*) FROM dw_customer;

-- Uniqueness: duplicates on the business key
SELECT customer_id, COUNT(*) AS copies
FROM dw_customer
GROUP BY customer_id
HAVING COUNT(*) > 1;

-- Consistency: facts without a matching dimension row
SELECT f.*
FROM fact_sales f
LEFT JOIN dw_customer d ON d.customer_id = f.customer_id
WHERE d.customer_id IS NULL;

Exploration and analysis

To understand the data I test, I work with it directly: data cleaning and filtering in R with the tidyverse, dashboards in Power BI and network analysis in Gephi. My academic background is an MSc in Big Data and a diploma in Machine Learning. A typical cleaning step looks like this:

library(tidyverse)

data <- read.csv2("data/customers.csv")
str(data)

# Drop the columns the analysis does not use
newData <- select(data, -FAMILYSIZE, -YEAREXPERIENCE, -ANNUALINCOME, -EDUCATIONLEVEL_ID)

# Keep the population under study
newData <- newData[newData$AGE < 55, ]

Evaluation datasets are data too

The same discipline applies to AI testing. The golden dataset of my LLM evaluation framework is a versioned JSON file with expected tools, required facts and forbidden content for every case. Adding a test means adding a record, and the quality of the data decides the quality of the verdict.

Source code: ETL jobs, notebooks and scripts on GitHub.