Test schemas and metadata

Before the data is right, the structure has to be: the tables the design says exist, the columns with the types the design says, the __inserted_at every bronze table must have. The design is a spreadsheet, reality is a catalog view, and both are data sources — so the structure is tested with the same five expectations as the data.

The design against reality #

Most analytical solutions are built from a sheet: a list of tables and columns with types, owners, layers — the spec the developers worked from, kept in Excel or CSV in the repo. That sheet is a data source for CAT (Excel@2 / Csv@2), the catalog of the platform is another (INFORMATION_SCHEMA, sys.columns, Unity Catalog’s system.information_schema), and the test is a comparison between the two.

• Form view
• YAML

Data sources:
- Name: design sheet
  Provider: Excel@2
  Connection string: design/Analytics-Design.xlsx     # in the repo, next to the project file
  Sheets: Columns
  Normalize column names: true
- Name: lakehouse
  Provider: Odbc@1
  Connection string: "%DATABRICKS_CONNECTION_STRING%"

Tests:
- Name: Every designed column exists with its type
  Suite: Structure
  Description: Each column of the silver layer in the design sheet exists in Unity Catalog with the designed type; extra columns in the catalog are allowed.
  First data source: lakehouse
  First query: |
    SELECT  table_schema, table_name, column_name, data_type
    FROM    system.information_schema.columns
    WHERE   table_catalog = 'analytics'
            AND table_schema IN ('silver_sales', 'silver_crm')
    ORDER BY table_schema, table_name, column_name
  Second data source: design sheet
  Second query: |
    SELECT  schema_name, table_name, column_name, data_type
    FROM    design.Columns
    WHERE   layer = 'silver'
    ORDER BY schema_name, table_name, column_name
  Expectation: contains
  Key: 1, 2, 3
  Maximum errors logged: 50

contains with the catalog as the first (super)set: every designed column must exist, extra technical columns may. Swap to sets match when nothing undesigned may exist either. The composite key — schema, table, column — makes the message name the exact column that is missing, and show the designed type next to the real one when they differ. One test per layer; the same with tables only (…information_schema.tables against the design’s table list) is the cheaper first check.

The sheet’s types and the catalog’s have to be spelled the same way (string vs STRING, decimal(18,2) vs DECIMAL(18,2)) — CAT ignores case, not spelling; a LOWER() or a mapping column in the sheet does the rest. See Differences between systems.

Mandatory columns per layer #

“Every table in bronze has __inserted_at and __source_file.” One template over the list of tables, one generated test per table — and the table created next week gets its test on the next open:

• Form view
• YAML

Queries:
- Name: bronze tables
  Data source: lakehouse
  Query: |
    SELECT  table_schema, table_name
    FROM    system.information_schema.tables
    WHERE   table_catalog = 'analytics' AND table_schema LIKE 'bronze%'

Tests:
- Name: Bronze table %table_schema%.%table_name% has the technical columns
  Suite: Structure
  Metadata: bronze tables
  Data source: lakehouse
  Query: |
    SELECT  expected.column_name
    FROM    (SELECT '__inserted_at' AS column_name UNION ALL SELECT '__source_file') AS expected
    WHERE   expected.column_name NOT IN (
              SELECT column_name FROM system.information_schema.columns
              WHERE  table_catalog = 'analytics'
                     AND table_schema = '%table_schema%' AND table_name = '%table_name%')
  Expectation: set is empty

The violation query returns the names of the mandatory columns a table lacks — so the message says which. The list of mandatory columns can itself come from the design sheet instead of the UNION ALL, and the catalog query is the same on SQL Server (INFORMATION_SCHEMA.COLUMNS), PostgreSQL, Snowflake, Fabric. How templates expand and what happens when the metadata query returns nothing: Generate tests from metadata.

Rules about the structure itself #

The catalog is a table like any other; rules about it are violation queries:

• Form view
• YAML

- Name: No column name with a space or a reserved word
  Suite: Structure
  Data source: DWH
  Query: |
    SELECT  TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    FROM    INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA IN ('dim', 'fact')
            AND (COLUMN_NAME LIKE '% %' OR COLUMN_NAME IN ('order', 'user', 'date', 'key'))
  Expectation: set is empty

- Name: Every fact table has a clustered columnstore index
  Suite: Structure
  Data source: DWH
  Query: |
    SELECT  s.name, t.name
    FROM    sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE   s.name = 'fact'
            AND NOT EXISTS (SELECT 1 FROM sys.indexes i WHERE i.object_id = t.object_id AND i.type = 5)
  Expectation: set is empty

- Name: Money columns are DECIMAL(19,4) everywhere
  Suite: Structure
  Data source: DWH
  Query: |
    SELECT  TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE
    FROM    INFORMATION_SCHEMA.COLUMNS
    WHERE   COLUMN_NAME LIKE '%Amount%'
            AND NOT (DATA_TYPE = 'decimal' AND NUMERIC_PRECISION = 19 AND NUMERIC_SCALE = 4)
  Expectation: set is empty

Primary keys present, no nullable key columns, every table documented (a description in the design sheet for every table in the catalog — contains the other way round), every dimension with an IsCurrent column — the same shape each time.

Why this is worth a suite of its own #

Structure breaks before data does: a column renamed in a migration, a type narrowed by a tool, a new bronze table without the technical columns — and the loads run green until something downstream falls over. A structure suite is cheap (catalog views are small), runs in seconds, and can run on every deployment. And because the design sheet is a first-class data source, the sheet stays the single source of truth: the test is the diff between what was agreed and what was built.

Related #

• Generate tests from metadata · Templates
• Test CSV and Excel files — the design sheet as a data source.
• Compare data across systems · Contains