Publish / file · All operations

Write a file of any format

One writer that picks the format from the file name.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
the table sent out, with nothing new to look at
Level
one clause of SQL

The DuckDB step

The writer is chosen from the file name

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>/w.parquet'; COPY orders TO '<your_scratch_dir>/w.json'; COPY orders TO '<your_scratch_dir>/w.csv';
CREATE TEMP TABLE result AS
SELECT (SELECT count(*) FROM glob('<your_scratch_dir>/w.*')) AS files,
       (SELECT count(*) FROM read_parquet('<your_scratch_dir>/w.parquet')) AS parquet_rows,
       (SELECT count(*) FROM read_json('<your_scratch_dir>/w.json')) AS json_rows,
       (SELECT count(*) FROM read_csv('<your_scratch_dir>/w.csv')) AS csv_rows;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT files = 3 AND parquet_rows = 10 AND json_rows = 10 AND csv_rows = 10 FROM result)) THEN 'as expected' ELSE error('a file written by name alone is not in the format its name says') 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 w.csv, w.json and w.parquet 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: “a file written by name alone is not in the format its name says”.
  • 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)
  • Output Dataread 2026-09-27

    Write results to a file or a database; one writer covering every supported format and data source

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

    Write FlowFile content to the local file system

Azure Data Factory (1)
  • Copy Activityread 2026-09-27

    Copy data from a source data store to a sink data store, with format conversion and column mapping

DuckDB (1)
  • COPYread 2026-09-27

    Write a table or query to a file, or load a file into a table, in CSV, Parquet or JSON

Tableau Prep (1)
  • Outputread 2026-09-27

    Write the flow result to a file, a published data source, a database or a Salesforce target

VisiData (1)
  • commit-sheetread 2026-09-23

    Commit deferred adds, edits and deletes back to the source

Background

In DuckDB, the COPY ... TO statement picks the file format from the file extension by default, so a name ending in .parquet gets a Parquet file.[1] A filename extension is a suffix to a file's name, such as .txt, that indicates a characteristic of the file contents or its intended use.[2] In DuckDB, a file whose extension COPY ... TO does not recognise is written as comma-separated values (CSV).[1]

When to use it

In DuckDB, COPY ... TO writes either the contents of a whole table or the result of a query to a file.[1] In DuckDB, the FORMAT option of COPY ... TO names the copy function to use, and its default is selected from the file extension.[1] Where a file name is built from a variable or typed by a person, set FORMAT outright rather than trust the extension. (Meridian's view) In DuckDB, a JavaScript Object Notation (JSON) file written by COPY ... TO is newline-delimited JSON by default, and the ARRAY option writes a JSON array of records instead.[1] Set ARRAY when the file's reader expects one JSON document rather than one record per line. (Meridian's view)

In DuckDB, COPY ... TO overwrites the destination file if it already exists, without raising an error.[1] By default, under its USE_TMP_FILE option, DuckDB writes to a temporary file first and replaces an existing destination only once the write completes successfully.[1] In DuckDB, reading from and writing to the same file in a single statement is not guaranteed to be safe.[1] When a query reads the file it is about to replace, write to a new name and rename the file once the write has finished. (Meridian's view)

A file's extension alone is not a reliable indicator of its type, as the extension can be changed without changing the file's contents.[2] A name chosen at write time records what the writer meant, so a pipeline that receives the file may still want to check what is inside it. (Meridian's view)

The method

In DuckDB, COPY ... TO writes data from DuckDB to external files, and it can be used for CSV, Parquet and JSON files.[1] In DuckDB, the CSV, Parquet and JSON copy functions come with the core system, and extensions can add further copy functions.[1] In DuckDB, the options of COPY ... TO specify how the file is written to disk, and a file it creates can be copied back into the database with COPY ... FROM and a similar set of options.[1]

More than one extension usually represents nested transformations, such as files.tar.gz, a tar archive compressed with gzip.[2] In DuckDB, COPY ... TO detects the compression of a CSV or JSON file from its extension: file.csv.gz uses gzip, file.csv.zst uses zstd and file.csv uses none.[1] In DuckDB, Parquet files written by COPY ... TO are compressed with snappy unless the COMPRESSION option names another codec.[1]

Unix file systems treat filename extensions as part of the filename, without special distinction.[2]

Compared with the alternatives

In PostgreSQL, COPY selects the data format to be read or written with its FORMAT option, one of text, csv or binary, and the default is text.[3] In Apache Spark, a save that names no format uses the default data source, which is parquet unless spark.sql.sources.default configures another.[4] When a script moves between engines, name the format in every write rather than rely on any one engine's default. (Meridian's view)

Sources

  1. [1]COPY Statement – DuckDBdocumentation
  2. [2]Filename extension - Wikipediasecondary source
  3. [3]PostgreSQL: Documentation: 18: COPYdocumentation
  4. [4]Generic Load/Save Functions - Spark 4.3.0 Documentationdocumentation