Data files

Execute DuckDB with duckdb. For Shapefile, GeoPackage, or FlatGeobuf, see duckdb-spatial. GeoJSON reads natively through read_json. For database catalogs and multi-database attachments, see duckdb-session or duckdb-lakehouse. For semi-structured VARIANT columns, see duckdb-variant.

Never build a single unified macro for all readers. A missing extension fails the entire statement. Pick the reader from the file path. When resolving a bare filename, search under the working tree and skip .git. In new SQL, write canonical names read_csv, read_json, and read_parquet; read_csv_auto, read_json_auto, and parquet_scan are legacy aliases.

Reader dispatch

Preview and metadata

Inspect schema, initial rows, and total row count:

DESCRIBE FROM read_parquet('path');
FROM read_parquet('path') LIMIT 20;
SELECT count(*) AS n FROM read_parquet('path');

When inspecting a large remote Parquet file, skip count(*) to avoid downloading data pages. Query file metadata directly:

SELECT num_rows, file_size_bytes
FROM parquet_file_metadata('path');

Never sum row_group_num_rows from parquet_metadata. The row count repeats for every column in each row group, so a 5-row, 3-column file reports 15 rows. Use parquet_file_metadata, run count(*), or query distinct groups:

SELECT sum(row_group_num_rows)
FROM (SELECT DISTINCT file_name, row_group_id, row_group_num_rows FROM parquet_metadata('path'));

To list remote bucket contents or directories without downloading payload bodies, read metadata with read_blob:

SELECT filename, size, last_modified
FROM read_blob('s3://bucket/prefix/*')
ORDER BY filename
LIMIT 50;

When preserve_insertion_order = false and multiple threads run, never page with bare LIMIT and OFFSET. Concurrent pipeline sinks reorder batches, causing queries to duplicate and drop rows while returning the expected row total. Page on a unique key (WHERE id > :last_id ORDER BY id LIMIT :page_size) or on (filename, file_row_number). For the pagination contract and proof, see references/pagination.md.

Convert

To convert formats, write with COPY ... TO:

COPY (FROM 'input.csv') TO 'output.parquet' (FORMAT parquet);

When writing partition directories, tune output behavior with these options:

• PARTITION_BY (col) splits writes into hive-partitioned directory trees for Parquet, CSV, or JSON.
• OVERWRITE deletes the target directory before writing a full rebuild.
• OVERWRITE_OR_IGNORE overwrites matching paths and leaves stale partitions from earlier runs. Use it only when the partition set is unchanged.
• APPEND adds files into existing partition directories without deleting prior files.
• FILENAME_PATTERN 'data_{i}' sets the output filename template.
• PER_THREAD_OUTPUT true writes one file per thread concurrently.
• FILE_SIZE_BYTES 134217728 rolls to a new file when the target size (e.g. 128 MB) is reached.
• ORDER_BY (col) sorts rows within each output file.
• RETURN_FILES true returns written file paths and row counts in the query result.

For full parameter listings, reader options, and types, see references/formats.md.

Ingest a batch

When running a repeatable production load instead of a one-off conversion, follow the batch pipeline in references/ingest.md.

Follow the six-step ingest contract:

1. Read raw: Read text inputs with all_varchar = true and store_rejects = true to preserve original strings and capture syntax errors.
2. Cast explicitly: Define schema types in SQL; avoid automatic type sniffing in production pipelines.
3. Split rows: Route valid records to good and failed casts or business-rule violations to bad alongside failure reasons.
4. Persist valid data: Write good rows to hive-partitioned Parquet files.
5. Persist quarantined data: Store bad rows and parser rejects in separate quarantine tables or files.
6. Prove row conservation: Confirm that input_rows = good_rows + bad_rows + count(DISTINCT reject_line).

Never use ignore_errors = true to repair a pipeline. Silently dropping malformed rows invalidates data completeness proofs.

Remote files and secrets

Load the remote file extension before accessing cloud storage or HTTP:

LOAD httpfs;

Configure credentials using secrets:

PROVIDER credential_chain autoloads the aws extension and fails closed if no AWS profile is found. Public buckets require no credential secret; a region pin is configuration, not a credential. For Azure service principals, use PROVIDER service_principal with TENANT_ID, CLIENT_ID, and CLIENT_SECRET.

Never place access keys in URLs. DuckDB 2.0 treats ?s3_access_key_id=... as part of the object key path. To restore the legacy behavior during migrations, run SET s3_enable_deprecated_url_parameters = true;.

Asynchronous I/O accelerates Parquet, seekable uncompressed UTF-8 CSV, and native DuckDB storage (including DuckLake scans). JSON reads remain synchronous. Default settings are async_threads = 4 * threads (capped at 256), read_ahead_depth = -1, http_retries = 3, and http_retry_backoff = 4.0. Retain defaults unless tuning for high-bandwidth networks (see references/formats.md).

Sandbox local files

To restrict filesystem and network access for untrusted queries, load all necessary extensions first, then restrict the session in order:

-- Step 1: Allow specific directories or exact paths
SET allowed_directories = ['/absolute/input'];
-- or exact files: SET allowed_paths = ['/absolute/file.csv'];

-- Step 2: Disable external access and lock configuration
SET enable_external_access = false;
SET allow_persistent_secrets = false;
SET lock_configuration = true;

Execution order is critical:

• Always set allowed_directories or allowed_paths before setting enable_external_access = false. Once enable_external_access is false, DuckDB rejects any changes to allow-lists.
• allowed_directories permits an entire directory tree. allowed_paths permits exact files.
• To block local disk while permitting remote network reads, set disabled_filesystems = 'LocalFileSystem'. Preload httpfs before disabling local access; HTTPS operations require local disk access to read the system CA certificate bundle.
• Setting enable_external_access = false blocks all network operations and all local filesystem paths outside allowed_directories or allowed_paths. Remote URL schemes (s3://, gs://, r2://, azure://, https://) bypass the local allow-list but fail once external access is disabled.
• Never rely on the CLI -safe flag for fine-grained sandboxing. The -safe flag disables all external file access unconditionally (blocking even user-provided input files), permitting reads only from the database file opened at launch. For full security matrices, see duckdb.

Verify

Run the verification pipeline to test CSV reading and Parquet writing:

tmp=$(mktemp -d)
printf 'id,name\n1,Ada\n2,Grace\n' > "$tmp/in.csv"
duckdb -agent -c "COPY (FROM '$tmp/in.csv') TO '$tmp/out.parquet' (FORMAT parquet); DESCRIBE FROM '$tmp/out.parquet'; SELECT count(*) FROM '$tmp/out.parquet';"
rm -rf "$tmp"

The schema must display id and name. The row count must return 2.

References

• Data import documents format overviews and readers.
• CSV documents CSV sniff and import options.
• JSON documents JSON auto-detection and shapes.
• Parquet documents Parquet reader, writer, and compression options.
• COPY documents output formatting and partitioning parameters.
• Secrets manager documents secret scopes and cloud providers.
• I/O documents asynchronous I/O settings and tuning.
• Reader dispatch replaces the read_any macro in https://github.com/duckdb/duckdb-skills (MIT). DuckDB 2.0.0 supports keyless TYPE s3, PROVIDER config, REGION secrets.