Metadata Testing in ETL: What to Check and How

Before you check a single row of data, check the container it lands in. Metadata testing (also called metadata validation or a metadata check) verifies that target tables have the structure the design promised: the right columns, data types, lengths, nullability, keys and names. It is usually the first test executed after deployment, because a wrong data type invalidates every test that follows.

What Is Metadata Testing in ETL?

Metadata is "data about data": the definition of each table and column. In ETL testing, metadata testing compares that definition in the target database with the source-to-target mapping document and with the source structure. It catches problems such as a VARCHAR(50) target for an 80-character source field, which would silently truncate data.

Metadata Checks to Run

Metadata Testing SQL Queries

INFORMATION_SCHEMA is available in SQL Server, PostgreSQL, MySQL, Snowflake and BigQuery (Oracle uses ALL_TAB_COLUMNS). List the target structure:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dw' AND TABLE_NAME = 'dim_customer'
ORDER BY ORDINAL_POSITION;

Check primary keys and unique constraints:

SELECT tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
  ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
 AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE tc.TABLE_SCHEMA = 'dw' AND tc.TABLE_NAME = 'dim_customer'
  AND tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE');

Comparing Source and Target Structure

This query lists columns where the target is missing or smaller than the source, which are the metadata defects that lose data:

SELECT s.COLUMN_NAME,
       s.DATA_TYPE AS src_type, t.DATA_TYPE AS tgt_type,
       s.CHARACTER_MAXIMUM_LENGTH AS src_len, t.CHARACTER_MAXIMUM_LENGTH AS tgt_len
FROM INFORMATION_SCHEMA.COLUMNS s
LEFT JOIN INFORMATION_SCHEMA.COLUMNS t
  ON t.TABLE_SCHEMA = 'dw' AND t.TABLE_NAME = 'dim_customer'
 AND t.COLUMN_NAME = s.COLUMN_NAME
WHERE s.TABLE_SCHEMA = 'stg' AND s.TABLE_NAME = 'customers'
  AND (t.COLUMN_NAME IS NULL
       OR t.CHARACTER_MAXIMUM_LENGTH < s.CHARACTER_MAXIMUM_LENGTH);
-- Expected: 0 rows, or only columns the mapping intentionally drops

This only works when source and target are on the same database. Across platforms, export both structures (or query each catalog) and compare them in a spreadsheet or Python.

Checking Against the Mapping Document

The mapping is the real expected result. A practical approach: load the mapping sheet into a small table (qa.mapping_spec with column name, expected type, length and nullability), then compare it with the catalog:

SELECT m.column_name, m.expected_type, c.DATA_TYPE AS actual_type,
       m.expected_length, c.CHARACTER_MAXIMUM_LENGTH AS actual_length,
       m.expected_nullable, c.IS_NULLABLE AS actual_nullable
FROM qa.mapping_spec m
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
  ON c.TABLE_SCHEMA = 'dw' AND c.TABLE_NAME = m.table_name
 AND c.COLUMN_NAME = m.column_name
WHERE c.COLUMN_NAME IS NULL
   OR c.DATA_TYPE <> m.expected_type
   OR COALESCE(c.CHARACTER_MAXIMUM_LENGTH, 0) <> COALESCE(m.expected_length, 0)
   OR c.IS_NULLABLE <> m.expected_nullable;

Re-run it after every deployment. It turns metadata testing into a one-second regression check.

Typical Metadata Defects

Once the structure passes, move on to the contents with data profiling and the checks in the ETL testing checklist.

ETL testing process & templates: ETL testing process · ETL test plan template · ETL testing checklist · SCD testing · data profiling · ETL test cases

Frequently Asked Questions

What is metadata testing in ETL?

Metadata testing verifies the structure of target tables against the mapping document and source: table and column existence, data types, lengths and precision, nullability, keys, constraints, defaults and naming.

What is a metadata check in ETL testing?

A metadata check is a test that compares a table definition with the expected definition, usually by querying INFORMATION_SCHEMA.COLUMNS and comparing data types, lengths and nullability with the mapping.

Why is metadata testing done first?

Because structural problems such as a short column or a wrong data type corrupt the data during the load. If the structure is wrong, data tests produce misleading results, so metadata is validated first.

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures