# Check a table's structure

Shape / inspect

A variant of [Check data quality](https://meridian.online/operations/check-data-quality).

Check that a table has the expected columns, names and types, failing or conforming it when it does not.

Page: <https://meridian.online/operations/check-data-quality/check-a-tables-structure>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

check a table has the expected columns, names and types, and conform it when it does not

```sql
-- what arrived: ids and amounts as text, no order_date, and a column nobody asked for
CREATE TEMP TABLE arrived AS SELECT * FROM (VALUES
  ('1', 'Alice Ng', '120.5', 'first'), ('2', 'Bob Okafor', 'n/a', 'second')) t(order_id, customer, amount, note);
CREATE TEMP TABLE expected_structure AS SELECT * FROM (VALUES
  ('order_id', 'BIGINT'), ('customer', 'VARCHAR'), ('amount', 'DOUBLE'), ('order_date', 'DATE')) t(column_name, column_type);
-- read the structure from DESCRIBE, then compare it with the expectation in both directions
CREATE TEMP TABLE problems AS
SELECT coalesce(e.column_name, a.column_name) AS column_name,
       CASE WHEN a.column_name IS NULL THEN 'missing'
            WHEN e.column_name IS NULL THEN 'unexpected'
            ELSE 'wrong type: ' || a.column_type END AS problem
FROM expected_structure e FULL OUTER JOIN (SELECT column_name, column_type FROM (DESCRIBE arrived)) a USING (column_name)
WHERE a.column_name IS NULL OR e.column_name IS NULL OR a.column_type <> e.column_type;
-- conform: cast to the expected types (a value that does not fit becomes NULL), add the missing column, drop the extra one
CREATE TEMP TABLE conformed AS
SELECT TRY_CAST(order_id AS BIGINT) AS order_id, customer, TRY_CAST(amount AS DOUBLE) AS amount, CAST(NULL AS DATE) AS order_date FROM arrived;
CREATE TEMP TABLE problems_after AS
SELECT coalesce(e.column_name, a.column_name) AS column_name FROM expected_structure e
FULL OUTER JOIN (SELECT column_name, column_type FROM (DESCRIBE conformed)) a USING (column_name)
WHERE a.column_name IS NULL OR e.column_name IS NULL OR a.column_type <> e.column_type;
SELECT * FROM problems ORDER BY column_name;
SELECT * FROM conformed ORDER BY order_id;
SELECT CASE WHEN ((SELECT list((column_name, problem) ORDER BY column_name) FROM problems) = [('amount', 'wrong type: VARCHAR'), ('note', 'unexpected'), ('order_date', 'missing'), ('order_id', 'wrong type: VARCHAR')] AND (SELECT count(*) FROM problems_after) = 0 AND (SELECT list(column_name) FROM (DESCRIBE conformed)) = ['order_id', 'customer', 'amount', 'order_date'] AND (SELECT list((order_id, amount) ORDER BY order_id) FROM conformed) IS NOT DISTINCT FROM [(1, 120.5), (2, NULL)]) THEN 'as expected' ELSE error('the arrived table should have four structure problems, and the conformed table none, with the amount n/a turned to NULL') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- AWS Glue: [`ColumnCount`](https://docs.aws.amazon.com/glue/latest/dg/dqdl-rule-types.html) (read 2026-09-27)
- AWS Glue: [`ColumnExists`](https://docs.aws.amazon.com/glue/latest/dg/dqdl-rule-types.html) (read 2026-09-27)
- AWS Glue: [`ColumnNamesMatchPattern`](https://docs.aws.amazon.com/glue/latest/dg/dqdl-rule-types.html) (read 2026-09-27)
- AWS Glue: [`SchemaMatch`](https://docs.aws.amazon.com/glue/latest/dg/dqdl-rule-types.html) (read 2026-09-27)
- KNIME Analytics Platform: [`Reference Table Validator`](https://api.hub.knime.com/nodes/*Y4pjF_CT2oL_sTHE?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`Table Structure Validator`](https://api.hub.knime.com/nodes/*PYO9539EU9baYWtO?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`Table Validator`](https://api.hub.knime.com/nodes/*klw7X6aAyIqQvpF8?details=full) (read 2026-09-27)
- Polars: [`DataFrame.match_to_schema`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27)
- Power Query M: [`Table.HasColumns`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#column-operations) (read 2026-09-27)
- RapidMiner Studio: [`Check Model Conformance`](https://docs.rapidminer.com/latest/studio/operators/extensions/Operator%20Toolbox/models/check_model_conformance.html) (read 2026-09-27)