Shape / columns · All operations

Fit columns to a layout

Fit columns to a target layout.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
several clauses of SQL, or a window

The DuckDB step

Fit columns to a target layout from a mapping

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TABLE target_layout (customer_name VARCHAR, order_total DOUBLE, placed DATE);
INSERT INTO target_layout BY NAME SELECT order_date AS placed, amount AS order_total, customer AS customer_name FROM orders;
CREATE TEMP TABLE result AS
SELECT count(*) AS n, count(order_total) AS totals, min(placed) AS first_placed, max(customer_name) FILTER (WHERE order_total = 220) AS who FROM target_layout;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT n = 10 AND totals = 9 AND first_placed = DATE '2026-01-03' AND who = 'Alice Ng' FROM result)) THEN 'as expected' ELSE error('the columns did not land in the target by name') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the columns did not land in the target by name”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
  • Schema Fitread 2026-09-27

    Transform source data to match the column layout of target data, backed by an LLM connection

Apache NiFi (1)
  • ConvertRecordread 2026-09-27

    Convert records from one data format to another via a record reader and a record writer

OpenRefine (1)
  • Edit Wikidata schemaread 2026-09-27

    Map columns to Wikibase items, statements and terms by drag and drop, as a template for edits

RapidMiner Studio (1)
  • Supersetread 2026-09-27

    Add the columns each of two tables lacks, as nulls, so both share one column set

Background

Data mapping creates mappings between the data elements of two distinct data models, a first step in moving data from a source to a destination.[1] In DuckDB, INSERT INTO ... BY NAME matches the column names of a SELECT against those of the table, so the two can list their columns in different orders.[2]

When to use it

Data mapping is used to consolidate several databases into one, identifying redundant columns of data to consolidate or eliminate.[1] A company exchanging purchase orders and invoices with other companies might map its own data to standardised X12 messages for those documents.[1] Reach for a fit whenever data arrives in one layout and has to land in a table whose layout is already fixed. (Meridian's view)

In DuckDB, a column left out of an INSERT is filled with its declared default value, or NULL if it has none.[2] In DuckDB, a column name in INSERT INTO ... BY NAME that is misspelled or does not exist in the table raises an error.[2] In DuckDB, when a value inserted into a column is not of the column's data type, automatic type conversion is attempted.[2] After a fit, count the non-null values in each target column, to catch a column the mapping left out. (Meridian's view)

The method

Data mappings can be written by hand in procedural code, or drawn in graphical tools that generate executable transformation programs.[1] Some graphical data mapping tools can auto-connect a source to a destination, a feature that depends on the data element names being the same.[1] Semantic mapping consults a metadata registry for synonyms, so a source's FirstName can still be mapped to a destination's PersonGivenName.[1] Semantic mapping only discovers exact matches between columns of data, and finds no transformation logic or exceptions between them.[1] Data-driven mapping evaluates the actual values in two data sources with heuristics and statistics, discovering substrings, concatenations, arithmetic and case statements.[1]

In general, the correspondences between two schemas cannot be determined fully automatically, primarily because their semantics differ and are often not documented.[3] Schema-level matchers consider only schema information, such as the name, description, data type and constraints of each element, and not instance data.[3] Instance-level matchers use the data itself to gain insight into the contents and meaning of schema elements.[3]

In Polars, DataFrame.match_to_schema allows columns to be freely reordered to a target schema, and by default returns an error unless the input schema matches it exactly.[4] In Polars, match_to_schema can instead insert missing columns, from an expression per column if given, and ignore extra columns.[4]

Compared with the alternatives

In DuckDB, UNION BY NAME combines rows from queries by column name rather than position, filling a column found in only one query with NULL for the other.[5] In RapidMiner, the Superset operator adds the attributes each of two example sets lacks, as missing values, so both have the same set of regular attributes.[6] Aligning two inputs with each other is a different task from bending one input to a target layout that is already fixed. (Meridian's view)

Sources

  1. [1]Data mapping - Wikipediasecondary source
  2. [2]INSERT Statement – DuckDBdocumentation
  3. [3]Schema matching - Wikipediasecondary source
  4. [4]polars.DataFrame.match_to_schema — Polars documentationdocumentation
  5. [5]Set Operations – DuckDBdocumentation
  6. [6]Superset - Rapidminer Documentationdocumentation