--- title: "Test schemas and metadata" description: "Compare the design — the CSV or Excel sheet the solution was built from — with what really exists in INFORMATION_SCHEMA or Unity Catalog; mandatory columns per layer from one template; naming and type rules" url: "https://docs.justcat.it/how-to-guides/test-patterns/test-schemas-and-metadata/" --- # Test schemas and metadata ## 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](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "Excel@2") / [Csv@2](https://docs.justcat.it/reference/data-sources/providers/csv-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. **Properties** **Data sources** Name : design sheet Provider : Excel@2 Connection string : design/Analytics-Design.xlsx 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 : ```sql 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 : ```sql 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 **YAML** ```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](https://docs.justcat.it/how-to-guides/test-patterns/differences-between-systems/ "Differences between systems"). ## Mandatory columns per layer "Every table in bronze has `__inserted_at` and `__source_file`." One [template](https://docs.justcat.it/reference/tests/templates/ "Templates") over the list of tables, one generated test per table — and the table created next week gets its test on the next open: **Properties** **Queries** Name : bronze tables Data source : lakehouse Query : ```sql 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 : ```sql 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 **YAML** ```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](https://docs.justcat.it/how-to-guides/organize-and-run-tests/generate-tests-from-metadata/ "Generate tests from metadata"). ## Rules about the structure itself The catalog is a table like any other; rules about it are violation queries: **Properties** Name : No column name with a space or a reserved word Suite : Structure Data source : DWH Query : ```sql 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 : ```sql 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 : ```sql 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 **YAML** ```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](https://docs.justcat.it/how-to-guides/organize-and-run-tests/generate-tests-from-metadata/ "Generate tests from metadata") · [Templates](https://docs.justcat.it/reference/tests/templates/ "Templates") * [Test CSV and Excel files](https://docs.justcat.it/how-to-guides/data-platforms/test-csv-and-excel-files/ "Test CSV and Excel files") — the design sheet as a data source. * [Compare data across systems](https://docs.justcat.it/how-to-guides/test-patterns/compare-data-across-systems/ "Compare data across systems") · [Contains](https://docs.justcat.it/reference/tests/expectations/contains/ "Contains")