Acquire / filesystem · All operations

Read the properties of given files

A variant of List files and folders

Look up size, timestamps and other metadata for files whose paths are already known.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Look up the size and modified time of files whose paths are known

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 (SELECT * FROM orders) TO '<your_scratch_dir>/known_a.csv' (HEADER);
COPY (SELECT order_id FROM orders) TO '<your_scratch_dir>/known_b.csv' (HEADER);
COPY (SELECT 1) TO '<your_scratch_dir>/known_c.csv';
CREATE TEMP TABLE result AS
SELECT replace(f.filename, '<your_scratch_dir>/', '') AS file, f.size, f.last_modified >= now() - INTERVAL 1 HOUR AS recent
FROM read_blob(['<your_scratch_dir>/known_a.csv', '<your_scratch_dir>/known_b.csv']) f;  -- the named files only; known_c.csv is in the folder and not listed
SELECT * FROM result ORDER BY file;
SELECT CASE WHEN ((SELECT list(file ORDER BY file) FROM result) = ['known_a.csv', 'known_b.csv'] AND (SELECT bool_and(recent) FROM result) AND (SELECT size FROM result WHERE file = 'known_b.csv') = 30 AND (SELECT size FROM result WHERE file = 'known_a.csv') > (SELECT size FROM result WHERE file = 'known_b.csv')) THEN 'as expected' ELSE error('the two named files did not come back with their sizes and a recent modified time') 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 known_a.csv, known_b.csv and known_c.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: “the two named files did not come back with their sizes and a recent modified time”.
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).

Azure Data Factory (1)

Get Metadataread 2026-09-27
Retrieve metadata of data, such as of a file, folder or table, for later activities to use

KNIME Analytics Platform (1)

Files/Folders Meta Inforead 2026-09-27
Append size, timestamps and other metadata for each path in a column

Talend (1)

tFilePropertiesread 2026-09-27
Output one row holding the main properties of a file

Background

The Unix stat() system call queries the file system for metadata about a file, including its type, size, ownership, permissions and timestamps.^[1] In KNIME Analytics Platform, the Files/Folders Meta Info node adds meta information about the files and folders referenced by a selected column of paths.^[2] In DuckDB, read_blob and read_text accept a filename or a list of filenames and output each file's size and last modified time.^[3]

When to use it

The timestamps that stat() returns include mtime, when a file was last modified, atime, when it was last accessed, and ctime, when its status last changed.^[1] Read mtime, not ctime, when the question is when a file's contents last changed. (Meridian's view) For a path that is a symbolic link, stat() returns the attributes of the link's target, whereas lstat() returns those of the link itself.^[1]

In DuckDB's read_blob and read_text, a metadata cell is set to NULL when the underlying file system cannot provide that data.^[3] Azure Data Factory's Get Metadata activity returns no created time for files or folders in Amazon S3, Google Cloud Storage or Oracle Cloud Storage.^[4] Azure Data Factory's Get Metadata activity does not support a wildcard filter on folders or files.^[4] Expect a property to be missing on some stores, and decide before the run whether a missing value stops the work or is carried as unknown. (Meridian's view)

KNIME's Files/Folders Meta Info node fails if any referenced file or folder does not exist, when its Fail if file/folder does not exist option is checked.^[2] KNIME's Files/Folders Meta Info node can calculate the overall size of a folder, the folder plus everything within it, instead of the folder's own size alone.^[2]

The method

In DuckDB, the tables returned by read_text and read_blob have the same schema: filename, content, size and last_modified.^[3] The stat() system call appeared in Version 1 Unix, and is among the few original Unix system calls to change, with Version 4's addition of group permissions and larger file size.^[1] The stat() and lstat() functions take a path, while fstat() takes a file descriptor instead.^[1]

Compared with the alternatives

Azure Data Factory's Get Metadata activity, pointed at a folder, can return childItems, a list of the name and type of each subfolder and file in the folder.^[4] With a modified-date filter on a folder, Get Metadata enumerates all the files in the folder to check their modified time, and pointing it at a folder with a large number of files is to be avoided even if few files are expected to qualify.^[4] Read the properties of known paths when the paths are already in hand, and list the folder only when the question is what it contains. (Meridian's view)

Sources

[1]stat (system call) - Wikipediasecondary source
[2]Files/Folders Meta Infodocumentation
[3]Directly Reading Filesdocumentation
[4]Get Metadata activity - Azure Data Factory & Azure Synapsedocumentation