Shape / columns · All operations

Stamp rows with load metadata

A variant of Add a calculated column

Add columns recording where and when each row was loaded, such as a timestamp, source file, batch id or change kind.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Stamp rows with when, where and in which batch they were loaded

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

COPY orders TO '<your_scratch_dir>/orders.csv' (HEADER);
CREATE TEMP TABLE result AS
SELECT order_id, now() AS loaded_at, parse_filename(filename) AS source_file, 'batch-001' AS batch_id, 'insert' AS change_kind
FROM read_csv('<your_scratch_dir>/orders.csv', filename = true);
SELECT order_id, loaded_at IS NOT NULL AS stamped, source_file, batch_id, change_kind FROM result ORDER BY order_id LIMIT 3;
SELECT CASE WHEN ((SELECT count(*) = 10 AND count(DISTINCT loaded_at) = 1 AND min(loaded_at) IS NOT NULL AND min(source_file) = 'orders.csv' AND max(source_file) = 'orders.csv' AND min(batch_id) = 'batch-001' AND max(change_kind) = 'insert' FROM result)) THEN 'as expected' ELSE error('each of the ten rows was not stamped with one load time, orders.csv, batch-001 and insert') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

The statement writes orders.csv itself, in <your_scratch_dir>.
It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “each of the ten rows was not stamped with one load time, orders.csv, batch-001 and insert”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Apache SeaTunnel (2)

Metadataread 2026-09-27
Expose row metadata (database, table, row kind, event time, delay, partition) as fields
RowKindExtractorread 2026-09-27
Turn a change stream into append-only rows and keep the original row kind in a new field

AWS Glue (1)

Add Current Timestampread 2026-09-27
Mark each row with the time at which the data was processed

Duckle (1)

Audit Stampread 2026-09-25
append loaded-at, source and batch id columns

Trifacta (1)

filepathread 2026-09-27
New column holding the path of the source file each row came from

VisiData (1)

addcol-audiometadataread 2026-09-23
Add metadata columns for audio files on a Directory Sheet

Background

Stamping rows with load metadata adds columns recording when and from where each row was loaded, which helps with auditing.^[1]^[2] AWS Glue's Add Current Timestamp transform marks rows with the time the data was processed, for auditing or to track latency in the pipeline.^[1] DuckDB's CSV reader can add the path of the file each row came from, as a string column named filename.^[3]

When to use it

In DuckDB, the filename column holds a relative or an absolute path, depending on the path or glob pattern given to read_csv.^[3] DuckDB's parse_filename returns the last component of a path, as Python's os.path.basename does, and can remove the file extension.^[4] Store the file's name rather than the path it was read from, so the stamp stays the same when the same file is loaded from another folder or machine. (Meridian's view)

In DuckDB, now() and current_timestamp return the current date and time at the start of the current transaction.^[5] Prefer a load time fixed for the whole load over a clock read row by row, so every row of one batch carries the same stamp and the batch can be picked out by it. (Meridian's view)

Apache SeaTunnel's Metadata transform can add a row's change type as a field, with +I for insert, -U and +U for before and after an update, and -D for delete.^[2] SeaTunnel's Metadata transform adds metadata fields without modifying the original data fields.^[2] Stamp rows at the step that reads them: once rows from several files or batches have been combined, where each one came from is hard to recover. (Meridian's view)

The method

Since DuckDB 1.3.0, read_csv adds the filename column automatically as a virtual column, and the filename option is kept only for compatibility.^[3] AWS Glue's Add Current Timestamp can add its column as a timestamp or as a formatted date string, under a column name the user may choose.^[1]

In Kimball dimensional modelling, an audit dimension holds the extract, transform and load (ETL) processing metadata known when a fact table row is created.^[6] An audit dimension's attributes can include data-quality indicators, the versions of the ETL code that created the rows, and ETL process execution time stamps.^[6] Recording ETL code versions serves compliance and auditing, because business intelligence tools can then find which rows were created with which version of the ETL software.^[6]

Sources

[1]Using the Add Current Timestamp transform - AWS Gluedocumentation
[2]Metadata | Apache SeaTunneldocumentation
[3]CSV Import – DuckDBdocumentation
[4]Text Functions – DuckDBdocumentation
[5]Timestamp with Time Zone Functions – DuckDBdocumentation
[6]Audit Dimension | Kimball Dimensional Modeling Techniquessecondary source