Data Quality in an ETL Process — Catching Technical and Business Errors Before They Reach the Target System

Published Mar 2, 2024 · Updated Aug 31, 2026
by

A single value that cannot be converted — a date in the wrong format, a number with the wrong decimal separator — and the entire ETL run aborts. Data quality in an ETL process means catching such errors proactively: identifying, logging and isolating them before they reach the target system. This article is the entry point to a series that implements exactly that as a design pattern.

TL;DR — what this article covers:

  • Technical vs. business data quality — the two aspects every ETL process must handle separately.
  • The five core tasks — check technically, check on business rules, log, flag, exclude.
  • The WHERE-clause principle — how to find data errors systematically with rule-based queries.
  • Where this fits in the overall pattern — how this overview interlocks with the architecture and the logging articles.

Prerequisite. A basic understanding of ETL processes. This is a conceptual article — not a step-by-step tutorial. The concrete code building blocks are provided by the linked sibling articles of the series.

Contents

Overview: What data quality means in an ETL process

There is a lot of good specialist literature on the subject of data quality. Many treatments focus on quality dimensions and definitions. How to integrate the checking and handling of bad data into an ETL process, however, is often left open. This series closes that gap: this article defines the check classes and the basic approach. The concrete implementation is provided by the linked sibling articles.

The design pattern of this series pragmatically divides data quality checks into two classes — a deliberate working model, not a general taxonomy of data quality:

  • Technically, you first check whether the extracted data can be converted into the data types of the target system and whether it complies with formal constraints such as value ranges and notation.
  • From a business point of view, it is about ensuring, for example, the completeness, consistency and timeliness of the data. These and further quality characteristics are also described by formal data quality models such as ISO/IEC 25012 (15 characteristics).

The article groups the two classes under the terms technical data quality and business data quality.

Checking both reduces the risk that faulty data triggers runtime errors during loading — in the worst case the ETL process aborts — or that incorrect business values slip into the target system unnoticed. An ETL process should therefore be designed so that technical and business data errors are identified, logged and handled proactively. Data identified as faulty is not loaded into the target system. From this, five core tasks emerge:

  1. Checking technical data quality — can the value be converted into the target data type?
  2. Checking business data quality — is the value complete, consistent and plausible?
  3. Logging all errors found in an error table.
  4. Flagging records that were delivered with errors.
  5. Excluding the flagged records from further processing.

This turns an abstract data quality requirement into a reproducible processing chain: define the rule, detect the violation, log the error, flag the record, keep faulty data out of the target system.

This article focuses on the two checking tasks. How logging, flagging and exclusion are implemented in detail is covered by the sibling articles of the series (see Where this fits in the ETL design pattern). The technical foundations of the pattern are described in the article Design Pattern // The Architecture of an ETL Process.

Checking technical data quality

Checking technical data quality determines whether the data to be processed can be converted into the respective target data types. This is especially necessary when the data has to be read untyped from a text file (CSV, XML, JSON) or from Excel. The prerequisite is a safe type conversion: functions like TRY_CONVERT return NULL for values they cannot interpret instead of raising a runtime error, catching the expected conversion failures. They do not catch every error — explicitly disallowed type combinations still raise a runtime error even with TRY_CONVERT. Nor do these functions replace general error handling: they defuse type conversion specifically, no other sources of failure. At the same time, the result of the conversion should enable proactive identification of problems.

The basics of safe type conversion are described in the TRY_CONVERT articles. The actual check then runs as rule-based queries on the converted data: each check rule is expressed as a condition of a WHERE clause — in the simplest form one query per converted column. Several rules can also be bundled into a single query. If the query returns records, those are conversion errors. All errors found are logged in an error table.

A minimal example — the staging table holds the delivered raw value, and the check probes the conversion into the target data type date:

  1: SELECT
  2:     raw_birth_date
  3: FROM
  4:    staging.customer
  5: WHERE
  6:        raw_birth_date IS NOT NULL
  7:    AND TRY_CONVERT(date, raw_birth_date, 104) IS NULL;

The condition in line 6 excludes exactly the source NULL values: a NULL in the source is not a conversion error but a case for the mandatory-field check. An empty string ('') is a separate matter: IS NOT NULL lets it pass, and SQL Server silently converts it to 1900-01-01. The query therefore does not report it as an error. Whether the empty string counts as a missing value or is checked separately is a convention the pattern defines up front. Line 7 finds the values that cannot be converted into a date. Style code 104 stands for the German date format dd.mm.yyyy.

Typical checks at the technical level:

  • Convertibility into the target data type (date, decimal, int, …).
  • Value range — does the number fit the target type without overflowing?
  • Notation/format — phone numbers or postal codes following a defined pattern, for instance.
  • Mandatory field — was a value delivered at all where one is expected?

Notation and mandatory field sit at the boundary to the business check: the rule itself is a business requirement of the data source or domain, but it can be checked as a formal property of the individual value. For practical reasons, this pattern assigns both to the technical level — as a convention of this pattern, not as a universally valid classification.

Checking business data quality

Checking business data quality is, in principle, a complex task. Quality, however, can already be improved considerably with simple measures. A first check can run directly on the extracted data. Further checks follow after the data has been transformed.

Here, too, the approach described above applies — identification via WHERE clauses and logging of the data errors. Typical business checks:

  • Duplicates — does a key that the delivery contract defines as unique appear more than once?
  • Foreign-key plausibility — does the referenced value exist at all?
  • Business logic — a date of birth must not lie in the future, an order value must not be negative.
  • Cross-field consistency — do dependent values match each other?
  • Timeliness — does the delivery or posting timestamp fall within the expected time window, or is the record stale?

The WHERE pattern stays the same — here for the business rule “a date of birth must not lie in the future”:

  1: SELECT
  2:     birth_date
  3: FROM
  4:    staging.customer
  5: WHERE
  6:    birth_date > CAST(GETDATE() AS date);

The technical conversion is already completed and checked at this point — the business rule works on the typed column birth_date, not on the raw value.

The boundary between technical and business checks is fluid and ultimately a matter of definition. What matters is that both classes of errors are detected, logged and handled separately.

Where this fits in the ETL design pattern

This article is the starting point of a series that deepens the topic step by step:

  • Data quality (this article) — the why and the what: the two aspects, the five core tasks, the WHERE-clause principle.
  • The Architecture of an ETL Process — the how: work packages, schema layering E0–L2, which check applies at which schema boundary.
  • Logging an ETL Process — the with what: three-tier logging tables and stored procedures that make run, component and action analyzable.
  • Safe type conversion — the concrete code building block for the technical check (see the TRY_CONVERT series below).

If you want to build a robust ETL process from the ground up, read the series in this order.

FAQ

What is the difference between technical and business data quality?

Technical data quality checks whether a delivered value can be converted into the target data type at all and whether it meets formal constraints such as value range or notation — a property of the individual value. Business data quality goes further and checks whether the value is plausible, complete and consistent in content (e.g. a date of birth that does not lie in the future). The two classes are detected and logged separately because they have different causes and different fixes.

Where do I start if I want to add data quality to my ETL process?

A pragmatic entry point is the technical check: type and format errors can be found deterministically and with little effort in an automated way. A safe type conversion plus one WHERE clause per converted column already catches the most common causes of aborts. The business checks come afterwards, starting with simple business logic such as mandatory-field and plausibility checks.

How does the WHERE-clause principle work in practice?

After conversion, both the raw value and the converted value are available per column. A WHERE clause finds the records where the raw value contains a value but the conversion returns NULL — exactly then the conversion has failed. A raw value delivered as NULL or empty does not count: it is a case for the mandatory-field check. Business conditions can be checked following the same pattern. Every hit is a rule violation and is written to an error table. Details and examples are provided by the TRY_CONVERT series.

How does data quality checking in Postgres differ from SQL Server?

The principle is database-agnostic — safe conversion, WHERE-clause checks and error logging can be implemented in any relational database. The concrete implementation, however, differs per engine. The code in this series is T-SQL (TRY_CONVERT, SSIS). Postgres has no built-in try_cast — not even Postgres 18. One possible implementation is a hand-written wrapper function with exception handling, as shown in the Postgres bridges of the TRY_CONVERT articles. For large data volumes, its performance needs to be assessed separately, because row-by-row exception handling has a cost. The overarching pattern stays identical.

What is the difference between data quality and data integrity?

Data quality describes how well data fits its intended use. Formal models such as ISO/IEC 25012 group characteristics like accuracy, completeness, consistency and currentness under this term. Data integrity, by contrast, refers to the intactness and non-contradiction of stored data as a database enforces it via constraints, keys and transactions. The business checks of this pattern address quality characteristics — they do not replace the database’s integrity mechanisms.

Basics

Safe type conversion (TRY_CONVERT series)