fabricator

A DuckDB extension that connects to Microsoft SQL Server by hosting a C# layer (via CoreCLR) in-process and exchanging data + metadata as Apache Arrow over the Arrow C Stream Interface (ArrowArrayStream). It is a direct, in-process replacement for the Arrow-Flight transport used by the "Airport" extension.

How it differs from the native-TDS mssql extension: that extension speaks the TDS wire protocol directly in C++. This one delegates all SQL Server I/O to C# using Microsoft.Data.SqlClient — the C++ extension only registers DuckDB functions and ingests the Arrow streams the bridge produces. Connection strings are therefore plain Microsoft.Data.SqlClient strings, and connection pooling / Windows & Azure auth come from SqlClient natively. The C++ core (fabricator) and managed Fabricator.Bridge are transport- and backend-agnostic, intended for reuse by a future Power BI / DAX connector.

Works against box SQL Server, Azure SQL Database, and the Microsoft Fabric / Synapse warehouse family. The extension detects a server capability profile at ATTACH and adapts connection behavior (MARS, isolation) and type mapping (collation-driven VARCHAR/NVARCHAR, datetime2 scale, …) to the engine — including Fabric Warehouse over an Entra service principal. See Microsoft Fabric & Synapse.

One binary, multiple providers. The C++ core (fabricator) and managed Fabricator.Bridge are provider-agnostic, and the same extension hosts several backends selected at ATTACH via PROVIDER (or inferred from the connection scheme):

• sqlserver (default) — Microsoft SQL Server / Azure SQL / Fabric & Synapse warehouse (this document).
• delta — a Delta Lake folder/lakehouse as a read-write catalog (local, S3, and Fabric OneLake — the abfss target), with DML, time travel, snapshots, Change Data Feed, and liquid clustering (SORTED BY, bucket()/hilbert_index(), clustered OPTIMIZE). See Delta Lake provider.
• dax — Power BI / Analysis Services semantic models over ADOMD (read-only DAX). See docs/dax-provider.md.

The providers compose. CREATE TABLE … WITH (…) sets per-table Delta properties + write tuning (WITH options), and — the headline — a SQL Server external table over S3 becomes writable through this extension: INSERT / UPDATE / DELETE on an S3 Delta (or Parquet) external table route to storage via the Delta writer, and one CREATE TABLE … WITH (location=…, table_type='DELTA') AS … writes the data + auto-provisions the external table — capabilities SQL Server has no native equivalent for (it cannot INSERT into an S3 external table, and cannot write Delta at all). See SQL Server external tables on S3.

Design notes live under docs/ — the full warehouse design in docs/warehouse-support.md, the Delta catalog design in docs/delta-catalog.md, and what does not work (measured) in docs/known-limitations.md.

Feature Status

✅ implemented & verified · ⚠️ partial / via SqlClient · ❌ not yet

Install

fabricator ships as one file — a fabricator.duckdb_extension that carries the C++ extension and the .NET bridge inside it and unpacks them into DuckDB's own extension directory the first time it is loaded. There is nothing to unzip and no environment variable to set.

There are no published downloads yet, and fabricator is not in the DuckDB community repository (its CI builds C/C++/Rust from source and cannot produce or host a .NET payload), so for now you build the artifact yourself — see the collapsed section at the end of this chapter.

-- DuckDB must be started with allow_unsigned_extensions (see below)
LOAD '/path/to/fabricator.duckdb_extension';
SELECT fabricator_version();

The first load takes about two seconds (unpacking); every later load takes about ten milliseconds, because it only checks a marker file and loads the already-extracted core. Nothing is written outside DuckDB's extension directory.

Pick the artifact matching your DuckDB build. The inner extension is compiled against DuckDB's C++ internals, so it is tied to an exact DuckDB version and platform — v1.5.5 / windows_amd64, v1.5.5 / linux_amd64, and so on. A mismatch is refused immediately, with a message naming the version you are running, before anything is written to disk. Two flavours per platform:

(Sizes are per platform as well as per flavour — the Linux core is larger than the Windows one, so the two figures are not a like-for-like comparison of the flavours.)

allow_unsigned_extensions is required — fabricator is not signed by DuckDB, and this is a start-up option, not a SET:

duckdb -unsigned

duckdb.connect(config={"allow_unsigned_extensions": True})

# dbt profiles.yml (dbt-duckdb) — the artifact can be listed as an extension to load
config_options:
  allow_unsigned_extensions: true
extensions:
  - '/path/to/fabricator.duckdb_extension'

INSTALL '/path/to/fabricator.duckdb_extension' also works if you prefer DuckDB to copy the file into its extension directory first, after which a plain LOAD fabricator resolves it by name.

./scripts/pack-distribution.ps1 -Sku Standalone -Rid win-x64
# -> build/distribution/windows_amd64/fabricator.duckdb_extension

⚠ Upgrading past 2026-09-03: check a persistent database for a stale input view

Builds before 2026-09-03 registered the Arrow inputs of an internal host query as ordinary catalog views instead of temporary ones. On an in-memory database they simply piled up and vanished on exit. On a file-backed database they were written into the file — and because such a view holds a pointer into a stream that is long gone, scanning one crashes the process, and it keeps doing so after a restart.

Nothing creates them any more. If you have a .duckdb file that a pre-2026-09-03 build wrote to, check once and drop what you find:

SELECT view_name FROM duckdb_views()
WHERE view_name = 'in0' OR starts_with(view_name, '__fab');
-- for each, if any:
DROP VIEW IF EXISTS "<name>";

They are only ever created by the extension, never by you, so anything this returns is safe to drop. An in-memory database (the default) is unaffected.

Quick Start

With the extension loaded (Install):

-- Connection string (a valid Microsoft.Data.SqlClient string)
ATTACH 'Server=host,1433;Database=db;User Id=sa;Password=***;TrustServerCertificate=true;Encrypt=true'
  AS mssql (TYPE fabricator);

SELECT * FROM mssql.dbo.people WHERE id > 1;   -- automatic streaming scan + filter pushdown
SELECT count(*) FROM mssql.dbo.people;

DETACH mssql;

⚠ Set your session timezone to UTC.

SET TimeZone = 'UTC';

DuckDB's TimeZone defaults to the system zone once icu is loaded, not to UTC. The Delta protocol stores and accepts UTC, and several surfaces here read the session zone — fabricator_host_query inherits it, TIMESTAMPTZ values are interpreted in it, and a TIMESTAMPTZ → DATE conversion resolves in it — so a session left on local time can silently shift a date by a day. Setting UTC once makes all of that agree.

Connection Configuration

A fabricator connection is given either a Microsoft.Data.SqlClient connection string, the mssql:// URI convenience form, or a stored secret.

-- ADO.NET / SqlClient connection string
ATTACH 'Server=host,1433;Database=db;User Id=sa;Password=***;TrustServerCertificate=true;Encrypt=true'
  AS mssql (TYPE fabricator);

-- mssql:// URI (encrypt defaults on; ?encrypt=false to disable)
ATTACH 'mssql://sa:***@host:1433/db' AS mssql (TYPE fabricator);

-- Restrict catalog discovery on large servers (case-insensitive regex, partial match)
ATTACH 'Server=...;Database=...' AS mssql
  (TYPE fabricator, schema_filter '^(dbo|sales)$', table_filter '^fact_');

-- Per-catalog opt-out of buffering a scan of the catalog being written (see mssql_materialize).
-- Keeps INSERT INTO t SELECT ... FROM t STREAMING; needs ALLOW_SNAPSHOT_ISOLATION on the database.
ATTACH 'Server=...;Database=...' AS mssql (TYPE fabricator, materialize false);

-- Per-catalog NULL-write semantics (see mssql_keep_nulls). Default true = a NULL is stored as NULL;
-- false restores the older behaviour, where SqlBulkCopy replaces it with the column's DEFAULT.
ATTACH 'Server=...;Database=...' AS mssql (TYPE fabricator, keep_nulls false);

-- Per-catalog MARS mode: auto (default, the engine's capability) | true | false.
-- Use this rather than SET mssql_mars when two catalogs must differ — a SET applies to the whole
-- DuckDB connection, so it would govern every catalog attached in it.
ATTACH 'Server=...;Database=...' AS mssql (TYPE fabricator, mars 'false');

On a Fabric SQL endpoint the catalog also gains a fabric schema of platform functions, which need no extra options (they derive the workspace and item from the connection string; API_WORKSPACE / API_ITEM override them) — see Microsoft Fabric platform functions.

Secrets

-- SQL auth. Password/access_token are redacted in duckdb_secrets().
CREATE SECRET sql1 (TYPE mssql,
  host 'host', port 1433, database 'db', user 'sa', password '***', use_encrypt true);
ATTACH '' AS mssql (TYPE fabricator, SECRET sql1);
SELECT * FROM fabricator_query('sql1', 'SELECT 1');

Secret field names mirror the native mssql extension for cross-compatibility: host, port, database, user, password, use_encrypt, access_token, authentication, azure_tenant_id, azure_secret, schema_filter, table_filter, application_name.

azure_tenant_id matters only for the Fabric platform functions: SqlClient infers the tenant from the server it connects to, but minting a Fabric API token cannot, so a service-principal secret needs it spelled out.

Azure Entra ID (Microsoft Fabric SQL endpoints, which require Entra)

Microsoft.Data.SqlClient handles Entra natively via the Authentication= keyword, so most variants need no extra code:

CREATE SECRET fab_sp (TYPE mssql,            -- service principal
  host 'xxx.datawarehouse.fabric.microsoft.com', database 'wh',
  authentication 'service_principal', user '<app-client-id>', password '<client-secret>');
CREATE SECRET fab_mi (TYPE mssql,            -- (user-assigned) managed identity
  host '...', database 'wh', authentication 'managed_identity', user '<mi-client-id>');
CREATE SECRET fab_def (TYPE mssql,           -- DefaultAzureCredential chain
  host '...', database 'wh', authentication 'default');
CREATE SECRET fab_tok (TYPE mssql,           -- bring-your-own Entra token
  host '...', database 'wh', access_token '<jwt>');
-- also: interactive, device_code, workload_identity, password.
ATTACH '' AS fab (TYPE fabricator, SECRET fab_sp);

Integrated / Windows authentication

Use SqlClient's native keywords directly — Trusted_Connection=true or Integrated Security=SSPI (Windows). We do not implement the native extension's bespoke authenticator=krb5 / krb5-* connection-string dialect (see Differences).

Connection validation

ATTACH validates the connection up front and creates no catalog on failure (fail-fast, password never leaked):

ATTACH 'Server=nonexistent.host,1433;Database=db;User Id=sa;Password=pass' AS db (TYPE fabricator);
-- Error: MSSQL connection validation failed: <cause>

Microsoft Fabric & Synapse (warehouse)

Beyond box SQL Server, the extension connects to Fabric Warehouse, the Lakehouse SQL analytics endpoint, and Synapse pools. At ATTACH it detects a server capability profile (engine edition + product version + database collation) once and adapts automatically — no configuration needed. Inspect it with fabricator_server_info(catalog):

-- Fabric Warehouse via an Entra service principal:
CREATE SECRET fab (TYPE mssql,
  host 'xxxxx.datawarehouse.fabric.microsoft.com', database 'My Warehouse',
  authentication 'service_principal', user '<app-client-id>', password '<client-secret>');
ATTACH '' AS wh (TYPE fabricator, SECRET fab);

SELECT property, value FROM fabricator_server_info('wh');
-- engine_edition=11, supports_mars=false, has_nvarchar=false, has_datetimeoffset=false,
-- max_datetime2_scale=6, is_utf8_collation=true, is_binary_collation=true, default_write_isolation=snapshot,
-- mars_enabled=false

What the profile drives automatically:

• Connection mode. Fabric/Synapse reject MARS, so it's auto-disabled (mssql_mars=auto); with MARS off, data scans use pooled connections and the write transaction runs at SNAPSHOT isolation. Override with SET mssql_mars='true'|'false'|'auto' before ATTACH. (Read-your-writes for scans is given up on MARS-off engines — a documented trade-off — but metadata reads still see your own uncommitted DDL, so CREATE TABLE then immediate use works.)
  • Exception, since the scan-materialisation fix: a scan that feeds a write into the same catalog (INSERT INTO t SELECT … FROM t, or a CTAS reading that catalog) does see the transaction's own uncommitted rows on Fabric/Synapse, because it is buffered and can therefore run on the pinned connection. A standalone SELECT inside the transaction is unaffected and still reads committed state.
• Type mapping is collation/edition-driven: VARCHAR (UTF-8, not NVARCHAR), DATETIME2(6), and UTC DATETIME2 for tz instants (Fabric has no datetimeoffset). See the type table below.
• Constraints. Fabric accepts PRIMARY KEY/UNIQUE only as NONCLUSTERED NOT ENFORCED hints added via ALTER, so CREATE TABLE … PRIMARY KEY is automatically split into CREATE + ALTER TABLE ADD CONSTRAINT … NONCLUSTERED NOT ENFORCED. They aren't enforced by Fabric, but they seed rowid discovery so UPDATE/DELETE work.
• String ORDER BY pushdown turns on under Fabric's binary (BIN2_UTF8) collation (byte-order sort matches DuckDB); it stays local under case-insensitive collations (still correct).
• Data-layout clustering. A native CREATE TABLE … SORTED BY (cols) (the clause precedes AS for CTAS) is mapped to Fabric Warehouse / Synapse WITH (CLUSTER BY (cols)) — DuckDB has no CLUSTER BY syntax, so its SORTED BY serves the same purpose. Emitted only on a warehouse profile (a no-op on box SQL Server). The mssql_cluster_by setting is a session fallback. Example:

  CREATE TABLE wh.dbo.Sales (SaleID INT, CustomerID INT, SaleDate DATE, Amount DECIMAL(10,2))
    SORTED BY (CustomerID, SaleDate);   -- → CREATE TABLE … WITH (CLUSTER BY (CustomerID, SaleDate))
  

• Time travel. FROM t AT (TIMESTAMP => ts) maps to the statement-level OPTION (FOR TIMESTAMP AS OF '<ts>') hint on a warehouse (works on any table; UTC, truncated to milliseconds), and to FOR SYSTEM_TIME AS OF on box SQL Server (which needs a system-versioned/temporal table). AT (VERSION => n) is not supported (no SQL Server equivalent).

  SELECT * FROM wh.dbo.dimension_customer AT (TIMESTAMP => '2024-05-02 20:44:13.700');
  

• IDENTITY / surrogate keys. DuckDB has no IDENTITY, so a generated column (col BIGINT AS (0)) is used as a marker → the provider emits an IDENTITY column (box IDENTITY(1,1); Fabric bare BIGINT IDENTITY). Or set add_identity (ATTACH option / SET mssql_add_identity) to auto-add a <table>_id BIGINT IDENTITY surrogate key to created tables (CREATE + CTAS; skipped if the name already exists). The engine assigns values on INSERT (the identity column is absent from the source, so it's left for the engine).

  CREATE TABLE wh.dbo.Orders (OrderID BIGINT AS (0), CustomerID INT, Amount DECIMAL(10,2));  -- OrderID → IDENTITY
  ATTACH '...' AS wh (TYPE fabricator, add_identity true);   -- every created table gets <table>_id
  

  An IDENTITY column is also usable as the rowid, so UPDATE/DELETE work on a table whose only key is its identity column (no PK/unique). Precedence flips by engine: a warehouse prefers the identity (Fabric PK/UNIQUE are non-enforced hints, but IDENTITY is engine-unique); box / Azure SQL prefer the PK/unique and fall back to the identity only when there's no key constraint.
• Functions. Discovered scalar UDFs, TVFs, and stored procedures work on Fabric (proc result sets resolved via sp_describe_first_result_set), as do custom C# functions and the fn_each table-in-out exchange.

Collation reality: no single collation is ideal across the whole OneLake stack (DuckDB + the SQL endpoint are case-sensitive/binary; the DAX/Vertipaq engine is case-insensitive). The extension is adaptive — correct under any collation — but does not prescribe one. Full design: docs/warehouse-support.md.

Catalog Integration

-- Browse with standard DuckDB catalog functions
SELECT schema_name FROM duckdb_schemas() WHERE database_name = 'mssql';
SELECT table_name  FROM duckdb_tables()  WHERE database_name = 'mssql' AND schema_name = 'dbo';

-- Three-part naming + cross-catalog joins with local DuckDB tables
SELECT s.name, o.amount
  FROM mssql.dbo.people s JOIN mssql.sales.orders o ON o.order_id = 1000 + s.id;

Objects the server cannot describe are skipped, not fatal. The commonest case is an invalid view on SQL Server — one whose underlying table or column has been dropped or renamed. Such an object is left out of the listing and a warning naming it is written to duckdb_logs; everything else enumerates normally. Referencing it directly still fails, with SQL Server's own message naming the object that is really missing:

fabricator: 'dbo.vwSPRatings' exists but cannot be described — it is most likely an INVALID view
(a table or column it references has been dropped or renamed).
SQL Server said: Invalid object name 'dbo.SomeDroppedTable'.

To see what was skipped: SET logging_storage = 'memory'; then SELECT message FROM duckdb_logs WHERE message LIKE '%skipped during enumeration%';

Query Execution & Pushdown

Results stream directly into DuckDB. The extension pushes work to SQL Server:

-- Projection + filter pushdown: only [id]/[name] cross the wire and
-- `id >= 10 AND name = 'Bob'` is sent as a parameterized WHERE.
SELECT id, name FROM mssql.dbo.people WHERE id >= 10 AND name = 'Bob';

-- Bare LIMIT (no ORDER BY / filter) → SELECT TOP (100)
SELECT * FROM mssql.dbo.big_table LIMIT 100;

-- ORDER BY + LIMIT (top-N) → SELECT TOP (10) ... ORDER BY, when keys are safe
SELECT * FROM mssql.dbo.events ORDER BY id DESC LIMIT 10;

Filter pushdown is best-effort: DuckDB always re-applies every predicate, so anything not pushed is still filtered correctly. Pushed shapes: =, <>, <, <=, >, >=, IS [NOT] DISTINCT FROM, IS [NOT] NULL, IN, BETWEEN, AND/OR. VARCHAR predicates push only = / IN / IS NOT DISTINCT FROM (SQL Server collation is a looser superset; ordering comparisons stay local). TopN is pushed only when there is no pushed filter and every order key is safe for the target. On SQL Server that means a non-string key (or any key under a binary collation) whose NULL ordering matches the server's fixed convention (ASC = NULLS FIRST, DESC = NULLS LAST) — T-SQL cannot spell the other one. On Delta every key qualifies, including strings and any NULL placement, because the generated SQL is executed by DuckDB itself (see Delta Lake provider). Either way the DuckDB sort node is kept, so results are always correct.

One thing that quietly turns TopN pushdown off (on any provider): ordering by a collated key — an explicit COLLATE, or a session SET default_collation to anything other than binary. DuckDB then sorts by a rule the pushed-down query would not reproduce, so the key is left local. The answer is unchanged; only the row-trimming optimization is lost.

The optimizer also receives each table's approximate row count (from partition stats) and per-column NDV (distinct-value estimate) for better join ordering. Min/max bounds are intentionally not reported (DuckDB would prune filters on them, and SQL Server stats are sampled/stale).

The Delta provider feeds the same channel from a different source: its row count is summed from the transaction log — each file's recorded numRecords minus each deletion vector's stated cardinality — so it costs no data-file read and is exact rather than sampled. It reports no NDV, because a Delta add records min/max and null counts per column but no distinct-value estimate.

⚠ Statistics are read once per table and cached for the catalog entry's lifetime, on every provider. They are fetched lazily, the first time a table is scanned (never during catalog enumeration), and nothing re-reads them afterwards — not your own DML, and certainly not another process's. So after a bulk load, a large external INSERT, or a UPDATE STATISTICS elsewhere, plans in an existing session are still costed against the numbers from the first scan.

This affects plan quality only, never your results: what is reported is an estimate — the row count is never given to DuckDB as an upper bound, and min/max are deliberately not reported at all — so a stale value can pick a worse join order but cannot drop or duplicate a row.

To refresh them, invalidate the entry: SELECT fabricator_refresh_cache('<catalog>'), or the scoped SELECT fabricator_invalidate_cache('<catalog>', '<regex>') when you know which tables changed. Both rebuild the catalog entry, and the next scan re-reads its statistics.

Row identity (rowid)

Tables with a primary key (or unique index) expose a virtual rowid (scalar for a single key column, STRUCT for a composite key). It backs UPDATE/DELETE and can be selected:

SELECT rowid, name FROM mssql.dbo.products LIMIT 5;

Transactions

BEGIN/COMMIT/ROLLBACK map to a real SQL Server transaction. A connection is pinned (lazily, on the first write); all DML — catalog INSERT/UPDATE/DELETE and fabricator_exec — runs on it, and reads inside the transaction use it too (read-your-writes), so ROLLBACK undoes everything.

BEGIN;
INSERT INTO mssql.dbo.people (id, name) VALUES (9, 'temp');
SELECT fabricator_exec('mssql', 'UPDATE dbo.people SET salary = 0 WHERE id = 1');
SELECT count(*) FROM mssql.dbo.people;   -- sees the uncommitted INSERT
ROLLBACK;                                -- both statements undone

⚠ A transaction gives you read-your-writes, NOT a stable view of the rest of the world. Two identical SELECTs inside one BEGIN … COMMIT can return different answers if another session commits in between, on box and on Fabric — no setting changes it. On box SQL Server without READ_COMMITTED_SNAPSHOT, even a single statement is not a boundary: a statement referencing one table twice can report two different states of it, and one scan can return part of a concurrent transaction's rows. Enabling database-level READ_COMMITTED_SNAPSHOT fixes the single-statement case (Fabric / Synapse-serverless are already snapshot-isolated). For a stable view across statements — and for the single-statement case too, if you cannot change the database — there is an opt-in:

-- every read inside a transaction joins it, at SNAPSHOT — so the two SELECTs agree
SET mssql_read_isolation = 'snapshot';
BEGIN;
SELECT count(*) FROM mssql.dbo.people;   -- another session commits an insert here …
SELECT count(*) FROM mssql.dbo.people;   -- … and this still returns the same count
COMMIT;

It is off by default because it holds a connection and an open transaction for the whole DuckDB transaction's life (and on Fabric/Synapse it buffers reads instead of streaming them). The alternative is to materialise once into DuckDB (CREATE TEMP TABLE … AS SELECT). Details and measurements: known-limitations.md 1.16–1.17.

INSERT / UPDATE / DELETE

INSERT INTO mssql.dbo.target (id, name) VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO mssql.dbo.target SELECT id, name FROM read_csv('data.csv');

-- INSERT … RETURNING uses OUTPUT INSERTED, so server-side IDENTITY/DEFAULT values come back.
INSERT INTO mssql.dbo.target (name) VALUES ('Carol') RETURNING id, name;

-- UPDATE / DELETE need a rowid (PK or unique index; compound keys supported).
UPDATE mssql.dbo.people SET salary = salary * 1.1 WHERE id = 1;
DELETE FROM mssql.dbo.people WHERE id = 3;

INSERT streams through SqlBulkCopy with CheckConstraints enabled, so a CHECK / FOREIGN KEY violation fails just like a classic INSERT (CTAS/COPY skip constraint checking for bulk-load speed). IDENTITY columns are preserved when the source supplies them (KeepIdentity) and auto-generated otherwise. UPDATE/DELETE require a primary key or unique index (otherwise use fabricator_exec); they do not support RETURNING.

UPDATE … SET col = DEFAULT is not supported — write the default value explicitly.

Loading a Fabric Warehouse with COPY INTO (opt-in)

SqlBulkCopy streams rows over TDS one buffer at a time, which against a Fabric Warehouse is a round trip per buffer. Name a storage location this extension may stage temporary parquet in and a bulk write takes a different route instead: DuckDB writes the whole result there with its own parallel parquet writer, and the warehouse ingests the folder with one COPY INTO — the rows never cross TDS.

-- ⚠ the OneLake workspace and item must be GUIDs, not display names (see below)
ATTACH 'Server=<ep>.datawarehouse.fabric.microsoft.com;Database=MyWH' AS wh
  (TYPE fabricator, SECRET fabric_sp,
   copy_into_staging 'abfss://<workspaceId>@onelake.dfs.fabric.microsoft.com/<lakehouseId>/Files/staging');

-- staged as parquet, then loaded with a single COPY INTO
CREATE TABLE wh.dbo.big AS SELECT * FROM read_parquet('local/*.parquet');

SET mssql_copy_into_staging = '…' is the session form and overrides the ATTACH option. Either spelling of the location works — abfss://<fs>@<host>/<path> or https://<host>/<fs>/<path> — because the two engines need different ones and this translates between them.

• It is off unless you name a location, and there is no size threshold: COPY INTO has seconds of fixed cost, so it is the wrong choice for a small INSERT and the decision stays yours.
• ⚠ A OneLake staging path must name its workspace and item by GUID. Display names stage fine and then fail the load with 13840 … unsupported URL, so they are refused at ATTACH instead. Get the ids from fabric.workspaces() and fabric.items() on a OneLake attach.
• The identity running the statement needs read access to the staging area. OneLake as a COPY INTO source supports Entra ID only — no SAS, no account key.
• Staged files are removed after the load. A cleanup failure is logged, never raised: it cannot make a successful load look failed.
• Ignored on engines without COPY INTO (box SQL Server, Azure SQL) — those keep SqlBulkCopy, so one session setting can span a mixed set of attaches safely.
• An INSERT that supplies explicit IDENTITY values is refused on this path (COPY INTO cannot preserve them); unset the option for that statement.
• The load runs inside your transaction, so BEGIN … ROLLBACK still governs it.
• The source scan streams and reads a committed snapshot, so a staged load does not see rows the same transaction wrote moments earlier. That is deliberate: this path is for bulk movement, and streaming rather than buffering the whole source is what makes it fast (measured ~11% faster wall clock, ~27% less CPU and 484 MB less allocation on a 1M-row load). Unset the staging option for a statement that needs to read its own uncommitted writes.

MERGE INTO

MERGE INTO works on every provider that supports UPDATE/DELETE (SQL Server and Delta), including WHEN NOT MATCHED BY SOURCE, per-action AND conditions, DO NOTHING and ERROR:

BEGIN;
MERGE INTO mssql.dbo.target AS t USING staging AS s ON t.id = s.id
  WHEN MATCHED AND s.deleted THEN DELETE
  WHEN MATCHED               THEN UPDATE SET name = s.name
  WHEN NOT MATCHED           THEN INSERT (id, name) VALUES (s.id, s.name);
COMMIT;

Each action is executed by the same rowid machinery as the standalone statement, so the same requirements apply: the target needs a primary key or unique index (row identity is how DuckDB tells a matched row from an unmatched one, so this holds even for a merge whose only action is an INSERT), and MERGE … RETURNING is not supported.

On Delta, a merge carrying two or more UPDATE/DELETE actions is FUSED into one commit — in autocommit as well as inside a transaction — and that is a correctness mechanism, not a convenience. Those actions all address rows located by one scan of the join, so applying them one commit at a time is unsound: a copy-on-write delete renumbers the rows the other action already located. Such a merge is therefore always staged against a single snapshot.

The rule counts UPDATE/DELETE actions only, because an INSERT addresses no existing rows:

The refusal is the price of the guarantee: fusing requires deletion vectors, so on a table created with deletion_vectors=false a merge with two such actions reports "requires deletion vectors on the table when it is buffered … Enable deletion vectors on the table, or use at most one UPDATE/DELETE action per merge outside a transaction." Deletion vectors are on by default, so this only affects tables that switched them off — typically for readers that cannot consume them, such as SQL Server's external-table Delta reader.

(DuckLake draws the same line and stops there — it refuses two such actions outright. We serve them by fusing, and refuse only when the table cannot be buffered at all.)

⚠ A fused merge reports operation = 'TRANSACTION', never 'MERGE'. Another engine's DESCRIBE HISTORY will not match on MERGE. Wrap any merge in BEGIN … COMMIT if you want it atomic regardless of how many actions it carries.

On SQL Server the actions run as per-row DML on the transaction's pinned connection (this is not translated into a server-side T-SQL MERGE), so an explicit transaction is likewise what makes the statement atomic.

INSERT … ON CONFLICT is not supported. DuckDB lowers it to a MERGE keyed on a unique constraint, and fabricator tables advertise no indexes, so it fails at bind with "no UNIQUE/PRIMARY KEY constraints that refer to this table". On Delta that is also semantically right — Delta enforces no uniqueness on user columns, so there is nothing to conflict against. Use MERGE INTO instead.

CREATE TABLE AS / COPY TO

CREATE TABLE AS and COPY TO stream the result to SQL Server via SqlBulkCopy, auto-creating the table from the Arrow schema. Both are bounded-memory (the dataset is never fully buffered).

CREATE TABLE mssql.dbo.summary AS SELECT region, count(*) AS n FROM big GROUP BY region;

COPY (SELECT * FROM src) TO 'mssql://mssql/dbo/target' (FORMAT mssql);
COPY src TO 'mssql.dbo.target'  (FORMAT mssql);
COPY src TO 'mssql.dbo.target'  (FORMAT 'bcp');               -- 'bcp' is an accepted alias
COPY src TO 'mssql.dbo.target'  (FORMAT 'bcp', REPLACE true); -- drop + recreate

COPY target = mssql://catalog/schema/table or catalog.schema.table (3-part only — temp-table / empty-schema syntax is not supported). Options: CREATE_TABLE (default true), REPLACE (default false). The target is registered in the catalog (queryable immediately). (This is the FORMAT mssql catalog COPY; to write a Delta table to a raw path with no ATTACH, use FORMAT delta — see COPY … TO a Delta path.)

Write parallelism (SET threads)

Writing to a fabricator table uses all the threads DuckDB is configured for — the scan, the filter, the projection and the load run on several tasks at once. Measured on 2 M rows, SET threads=1 against threads=4:

(Before 2026-08-22 every one of those was flat in SET threads, because the whole pipeline ran on one task.) A DELETE gains the most because its entire cost is that pipeline; an UPDATE's remainder is the row read-back and post-image write, which the provider does once at the end.

Two shapes keep a serial load, deliberately:

• an explicit ORDER BY in the write — INSERT INTO t SELECT … ORDER BY x keeps its rows arriving in order. ⚠ This costs far less than it sounds: only the step that feeds the load is single-threaded, while the scan and the projection run in the sort's own pipeline and still use every thread — measured 3.6 s → 1.9 s at threads 1 vs 4 on the same 2 M rows, i.e. essentially the unordered speed. Ordering declared on the table (SORTED BY, the fabricator.sortedBy property, a clustered Delta table) is unaffected either way: the provider applies it after the rows are collected, not by arrival order;
• INSERT … RETURNING, whose returned rows would otherwise come back in an arbitrary order.

⚠ One behaviour to know about if you write them: an UPDATE … FROM other whose join matches the same target row twice is last-write-wins, and which of the two wins is now arbitrary. It was never a documented order (it followed a hash join's probe order), but it used to be stable run to run. The reported row count is unaffected.

COPY … TO is the exception in the other direction: it is serial unless you set preserve_insertion_order=false (3.4 s → 2.1 s on the same shape). DuckDB tells a copy function only whether insertion order must be preserved, not why, so a copy cannot distinguish your ORDER BY from the default setting the way an INSERT can — and silently dropping an ORDER BY would be the worse trade.

⚠ What parallel writing costs: which rows land in which parquet file is no longer deterministic, so a table that was getting useful file clustering incidentally from its source order stops getting it. That affects pruning speed, never correctness. Declare SORTED BY if you want a layout, or write with an ORDER BY.

Type mapping (DuckDB → SQL Server, for CREATE / CTAS / COPY)

The write mapping is profile-adaptive — it follows the connected engine's collation, version, and edition:

• Text type is driven by the collation (a _UTF8 collation → VARCHAR, else NVARCHAR), not the edition. Length n comes from mssql_default_varchar_length (unset ⇒ MAX); mssql_ctas_text_type is a whole-type override (e.g. 'VARCHAR(64)'). MAX columns can't be a PRIMARY KEY/UNIQUE key, so set a length for indexable string keys.
• datetime2/time scale is the engine max (7 on box, 6 on Fabric).

On reads, SQL Server types map to DuckDB via SqlClient + Arrow: int/tinyint→UINTEGER/UTINYINT, decimal(p,s)/money→DECIMAL, bit→BOOLEAN, date→DATE, time(≤6)/datetime2(≤6)→TIME/TIMESTAMP (µs), time(7)/datetime2(7)→TIME_NS/TIMESTAMP_NS (the 100 ns digit is preserved), datetimeoffset→TIMESTAMPTZ, uniqueidentifier→UUID, json→JSON (SQL Server 2025; via the arrow.uuid/arrow.json Arrow extensions), varbinary→BLOB.

DDL

CREATE SCHEMA mssql.staging;
CREATE TABLE mssql.staging.t (id INTEGER NOT NULL, name VARCHAR, amount DECIMAL(10,2));
CREATE TABLE IF NOT EXISTS mssql.staging.t (id INTEGER);

-- PRIMARY KEY / UNIQUE (a PK enables rowid → UPDATE/DELETE); literal DEFAULTs are carried over.
CREATE TABLE mssql.staging.k (
  id INTEGER PRIMARY KEY, a INTEGER, b INTEGER,
  status VARCHAR DEFAULT 'active', qty INTEGER DEFAULT 0,
  UNIQUE (a, b));

-- ALTER TABLE: rename table/column, add/drop column, change type, toggle NOT NULL, set/drop DEFAULT.
ALTER TABLE mssql.staging.t ADD COLUMN note VARCHAR;
ALTER TABLE mssql.staging.t ALTER COLUMN id TYPE BIGINT;
ALTER TABLE mssql.staging.t ALTER COLUMN note SET NOT NULL;
ALTER TABLE mssql.staging.t ALTER COLUMN note SET DEFAULT 'n/a';
ALTER TABLE mssql.staging.t RENAME COLUMN note TO comment;
ALTER TABLE mssql.staging.t RENAME TO t_renamed;

-- COMMENT ON: stored where the target engine keeps a comment, so other tools see it. IS NULL removes one.
COMMENT ON TABLE mssql.staging.t_renamed IS 'staging rows, reloaded nightly';
COMMENT ON COLUMN mssql.staging.t_renamed.id IS 'the source system''s key';
COMMENT ON COLUMN mssql.staging.t_renamed.id IS NULL;

DROP TABLE mssql.staging.t_renamed;
DROP SCHEMA mssql.staging;

ALTER TABLE … ADD COLUMN … DEFAULT <literal> applies the default to LATER inserts and backfills the rows already there, matching what DuckDB's own tables do for the same statement. SQL Server would not backfill on its own, so this emits WITH VALUES — which touches every existing row, so on a large table it is a real write rather than a metadata-only change.

⚠ Write the literal BARE, or as a string. DEFAULT 10, DEFAULT 1.5, DEFAULT 'x' and DEFAULT '2024-01-01' are carried; DEFAULT true, DEFAULT DATE '2024-01-01', DEFAULT TIMESTAMP '…' and DEFAULT …::BLOB are refused by name, because DuckDB discards a cast-wrapped literal before the catalog ever sees it and the loss is indistinguishable from an intentional DEFAULT NULL. Refusing beats storing the wrong default silently. For those types, add the column and then ALTER TABLE … ALTER COLUMN … SET DEFAULT …, which carries every type correctly.

⚠ On the Delta provider a DEFAULT is refused outright: Delta records column defaults through a writer feature this engine does not implement, so the value could neither be stored nor applied to later inserts. Add the column, then set the values with an UPDATE.

COMMENT ON TABLE / COMMENT ON COLUMN store the comment where the target engine keeps one, so other tools read it back. IS NULL removes a comment, and removing one that is not there is a no-op.

They are also read BACK, so duckdb_tables().comment and duckdb_columns().comment report them — including a comment somebody else set (SSMS's Description box, or Spark's COMMENT ON), not just one written here:

SELECT table_name, comment FROM duckdb_tables() WHERE database_name = 'mssql';
SELECT column_name, comment FROM duckdb_columns() WHERE table_name = 't';

⚠ A comment changed OUT OF BAND while a catalog is attached is not picked up immediately — the same staleness as the rest of the catalog cache. fabricator_refresh_cache('<catalog>') or a re-ATTACH refreshes it; a comment written THROUGH the catalog is visible at once.

⚠ Refused on Fabric Warehouse, the Fabric Lakehouse SQL endpoint and Synapse dedicated pools, which do not provide extended properties. The refusal names the engine; fabricator_server_info(catalog) reports it as supports_extended_properties. It is a capability check made BEFORE any statement is sent, deliberately: on those engines a failing statement inside an explicit transaction aborts the whole transaction, so attempting it and letting the server refuse would take your transaction down with it.

⚠ COMMENT ON VIEW is refused for a provider-declared view, and COMMENT ON is not supported at all on the DAX provider (read-only) — in both cases there is nowhere on the far side to store it.

Column defaults are reported too, in information_schema.columns.column_default (and duckdb_columns()), for a SQL Server catalog:

ALTER TABLE mssql.staging.t ALTER COLUMN qty SET DEFAULT 10;
SELECT column_name, column_default FROM information_schema.columns
 WHERE table_catalog = 'mssql' AND table_name = 't';
-- qty | 10

⚠ LITERALS ONLY. A default that is an expression — getdate(), newid(), CONVERT(...) — reads back as NULL, i.e. it looks like a column with no default. That is deliberate: DuckDB's INSERT binder SUBSTITUTES a reported default for a column your statement omits, so reporting getdate() would stamp the rows with your client's clock instead of the server's. Withholding costs nothing — the server still applies its own default, exactly as before. Delta reports no defaults at all, because it cannot store one.

⚠ A default changed OUTSIDE this session (or a table created outside it) is picked up on re-ATTACH or after fabricator_refresh_cache('<catalog>'), not immediately — the whole database's defaults and comments are read once per catalog and cached.

For SQL Server-specific features (IDENTITY, CHECK, indexes, FKs, non-literal DEFAULTs), use fabricator_exec.

Temporary tables

CREATE TEMPORARY TABLE is not mapped to SQL Server #temp tables — DuckDB routes every temp table to its own in-memory/spillable temp catalog and forbids qualifying one with an attached catalog (TEMPORARY table names can only use the "temp" catalog), so it never reaches the provider. Just use DuckDB's native temp tables for transient data (faster — no round-trip, no connection affinity). If you specifically need a server-side #temp (e.g. staging for a complex EXEC), create and use it via fabricator_exec inside a DuckDB BEGIN so all calls share the one pinned connection — and only on a MARS engine (box SQL Server): #temp tables are connection-scoped, so they don't survive across autocommit calls, and on Fabric/Synapse (MARS off → pooled reads) they aren't visible to subsequent statements.

Function Reference

fabricator_query(context, sql) -> TABLE

Stream a raw T-SQL query. context may be a connection string, a secret name, or an attached-catalog name (reuses that catalog's connection).

SELECT id, name FROM fabricator_query('Server=...;Database=...', 'SELECT id, name FROM dbo.people');
SELECT * FROM fabricator_query('mssql', 'SELECT id, name FROM dbo.people');   -- attached catalog

Use fabricator_exec for anything that writes. fabricator_query is a table function, so DuckDB needs its column types before it can plan — which the SQL Server provider answers with a describe (sp_describe_first_result_set, no execution). A few statement shapes cannot be described (a batch that creates and reads a temp table is the one we've measured), and those fall back to executing once to learn the schema and once for the rows. So a side-effecting statement put through fabricator_query may run twice; put through fabricator_exec it runs once and tells you how many rows it touched.

⚠ A broken query now fails while binding, with SQL Server's own message (Invalid column name 'x') — one phase earlier than before.

⚠ Inside an explicit BEGIN … COMMIT, fabricator_query does run on the transaction's connection once one exists, so it sees the transaction's own uncommitted writes. Note the asymmetry with fabricator_exec below, which joins only if a write has already pinned that connection.

fabricator_exec(context, sql) -> BIGINT

Execute arbitrary T-SQL (DDL/DML/EXEC); returns rows affected (0 for DDL / no-row statements).

SELECT fabricator_exec('mssql', 'UPDATE dbo.people SET salary = salary + 1 WHERE id <= 2');

Multiple statements separated by ; (including multiline) run as one batch in a single call (return value = aggregate rows affected). GO is not supported — it's a sqlcmd/SSMS client directive, not T-SQL (use ;, or separate calls). For cross-statement atomicity, wrap in BEGIN…COMMIT or a DuckDB BEGIN.

SELECT fabricator_exec('mssql', 'CREATE TABLE dbo.t (id int); INSERT INTO dbo.t VALUES (1),(2)');

⚠ Inside an explicit BEGIN … COMMIT, whether this joins the transaction depends on whether something already wrote. fabricator_exec joins the transaction's pinned provider connection only if one already exists — i.e. only if a DuckDB-managed write has pinned it. Otherwise it autocommits on its own connection, because a raw string-target exec never triggers the catalog's transaction lifecycle and nothing would ever commit that connection. If you need a statement to be reliably part of the transaction, make sure the transaction has written first, or use a purpose-built function (db.dbo.fabricator_session_tag() exists for exactly this reason).

Parameters

Both functions take an optional parameter bag — values that reach the provider as real bound parameters, never as text spliced into the statement. fabricator_query names it (params :=); fabricator_exec takes it as a third positional argument, because it is a scalar function and DuckDB scalars have no named parameters.

SELECT * FROM fabricator_query('mssql', 'SELECT * FROM dbo.people WHERE dept = @d', params := {'d': 'eng'});
SELECT * FROM fabricator_query('mssql', 'SELECT @a AS a', params := '{"a": 7}');   -- JSON also accepted
SELECT fabricator_exec('mssql', 'UPDATE dbo.people SET dept = @new WHERE dept = @old',
                       {'new': 'platform', 'old': 'eng'});

Each key is a parameter NAME, without the provider's sigil — write {'d': …} for @d, not {'@d': …}. The sigil belongs to the statement's dialect, so the provider adds it. An absent argument and params := NULL both mean "no parameters".

⚠ Prefer the STRUCT form. A struct's fields keep their DuckDB types exactly — precision, scale and temporal resolution all survive — while a JSON bag has only four scalar kinds, so it can carry BIGINT / DOUBLE / VARCHAR / BOOLEAN and nothing else. A DECIMAL or a typed timestamp needs the struct.

⚠ Only providers with a parameterised statement form accept a bag — today SQL Server (@name) and DAX (ADOMD @name, the same bag daxeval takes). Anything else refuses by name rather than running your statement with the values quietly dropped.

fabricator_refresh_cache(catalog) / fabricator_invalidate_cache(catalog [, schema [, table]])

Refresh cached catalog metadata after creating/dropping tables out-of-band (e.g. via fabricator_exec). fabricator_refresh_cache / fabricator_invalidate_cache are aliases.

SELECT fabricator_exec('mssql', 'CREATE TABLE dbo.t (id INT)');
SELECT fabricator_refresh_cache('mssql');
SELECT * FROM mssql.dbo.t;

-- ...or auto-invalidate after DDL run via fabricator_exec (off by default; DDL detected in C#):
SET mssql_exec_invalidate_cache = true;
SELECT fabricator_exec('mssql', 'CREATE TABLE dbo.t2 (id INT)');
SELECT * FROM mssql.dbo.t2;   -- visible immediately

Three things about the automatic mode that are easy to trip over:

• The first argument must be an ATTACHED CATALOG. Pass a connection string or a secret name and there is no cached catalog to refresh, so the setting appears to do nothing. Only fabricator_exec('<catalog>', …) triggers it.
• DDL detection is a keyword match, so it over-triggers: a statement merely containing CREATE, DROP, ALTER, TRUNCATE, RENAME or EXEC counts — including UPDATE t SET created_at = …. Harmless on SQL Server (a metadata re-read), but on a Delta catalog over OneLake/S3 re-discovery re-lists the store, so leave the setting off there and invalidate explicitly.
• Prefer the scoped form when you know what changed — fabricator_invalidate_cache('mssql', 'dbo', 't') (or a name regex) keeps the rest of the cache warm, where the automatic path re-discovers everything.

Without the setting, one asymmetry is worth knowing: a table created out-of-band is invisible until you refresh, while a table dropped out-of-band is handled gracefully only if you had not already read it in this session (you get a clean "table does not exist"). If you had read it, the drop surfaces later as the server's own error — e.g. SQL Server 208: Invalid object name. Refresh after out-of-band DDL and neither case arises.

fabricator_server_info(catalog) -> TABLE(property, value)

The detected server capability profile for an attached catalog (edition, version, collation, and the derived flags: supports_mars, has_nvarchar, has_datetimeoffset, max_datetime2_scale, has_native_json, supports_cdc, is_utf8_collation, is_binary_collation, default_write_isolation, …). See Microsoft Fabric & Synapse.

Plus one row that is not a server property: mars_enabled — whether this catalog actually uses MARS, i.e. supports_mars combined with mssql_mars / the mars ATTACH option. Since that is resolved once, at the catalog's first connection, it is the way to confirm a SET mssql_mars=… was in force early enough to take effect:

SELECT property, value FROM fabricator_server_info('db')
WHERE property IN ('supports_mars', 'mars_enabled');

db.cdc.* — change data capture (SQL Server only)

Set capture up, inspect it, and READ the change stream — over SQL Server's change data capture, with no message queue anywhere in it. They live in the catalog's cdc schema, so they resolve as db.cdc.changes('dbo.orders') and so on.

⚠ SQL Server only. CDC is a SQL Server engine feature: Fabric Warehouse, the Fabric Lakehouse SQL endpoint and Synapse dedicated pools do not have it, and on those engines these functions are not registered at all — db.cdc.max_position() reports that it does not exist. Check with SELECT value FROM fabricator_server_info('db') WHERE property = 'supports_cdc'.

The four setup functions each return one report row — target, changed, detail. changed is separate from success: a call that found the work already done succeeds and reports changed = false, so a setup script is safe to re-run.

-- Set capture up from SQL, no sqlcmd needed.
SELECT * FROM db.cdc.enable_database();
SELECT * FROM db.cdc.enable('dbo.orders');

-- …then read the changes. The objects the enable created are usable IMMEDIATELY, in the same session —
-- no re-ATTACH, no refresh.
SELECT * FROM db.cdc.changes('dbo.orders');

-- What is captured, and where the log currently stands?
SELECT source_table, capture_instance, fabricator_source FROM db.cdc.tables();
SELECT db.cdc.max_position() AS pos, db.cdc.min_position('dbo.orders') AS retention_floor;

-- Why is nothing arriving?
SELECT * FROM db.cdc.health();

Notes that will save you an afternoon:

• NULL is a state, not an error. max_position() is NULL when CDC is not enabled on the database and when it is enabled but the capture job has not run yet. health() tells you which. (Calling sys.fn_cdc_get_max_lsn() yourself in the first case raises 208 Invalid object name 'cdc.lsn_time_mapping' — an error about an object you never mentioned, which is why these wrappers exist.)
• cdc.enable / cdc.disable refresh the catalog for you. Enabling capture is DDL: it creates a change table and two table functions, and until the catalog knows about them they are not merely missing from duckdb_tables() — they are unreachable, even by name. These functions report the change and the catalog rebuilds before your next statement, so nothing extra is needed. ⚠ One gap: inside a single explicit BEGIN … COMMIT, a later statement in that same transaction will not see them; call fabricator_refresh_cache('db') if you need that. Enabling capture via raw fabricator_exec instead needs the refresh either way.
• There is no disable_database() on purpose. sp_cdc_disable_db drops every capture instance in the database at once, destroying all recorded history — a bigger hammer than anything else here. Use fabricator_exec if you mean it.
• ⚠ cdc.capture_now() is a maintenance action, not a per-query one. It forces the capture log scan immediately instead of waiting a polling interval, which costs CPU that belongs to your DBA's budget. It also contends with the capture job for the database's single log-scan session, so it can fail — the error says to stop the capture job for the duration or simply wait. Useful for tests and for a container with no SQL Server Agent. It does not read anything: it moves changes into the change tables, and nothing out.
• "Enabled" and "happening" are independent. sp_cdc_enable_db / sp_cdc_enable_table both succeed with SQL Server Agent stopped, so a table can look captured and never produce a row. That is what health()'s agent_status is for — and it reports unknown rather than guessing when the connection lacks VIEW SERVER STATE.
• Capture-instance names are generated and you never need to type one. cdc.enable('dbo.orders') creates fab_<16 hex> — 20 characters whatever your table is called, which also fixes a real failure: SQL Server's own default is <schema>_<table>, the limit is 100 characters, and a long table name therefore failed with Msg 22927 about a name you never chose. Pass capture_instance := if you want to name one yourself. cdc.tables() lists them, and everything else takes the TABLE name.
• cdc.enable on an already-captured table is a no-op, and that is deliberate. It reports changed = false and names the existing instance rather than adding a second. A second instance is a real change to what cdc.changes returns for that table — the declared columns become the union of both, and rows start carrying different _capture_instance values — and that is not something a bare enable should do silently. An explicit capture_instance := is how you deliberately create the second (that is how a schema change is absorbed).
• fabricator_source in cdc.tables() tells you which instances this extension created. It is an extended property written atomically with the enable; NULL means someone else made that instance, and nothing here will manage it for you.
• min_position takes the capture instance OR the table. A table may have two capture instances (how a schema change is absorbed) and their retention floors can differ; for a table name it reports the older one's, which is where the range cdc.changes reads begins. If either floor is momentarily unknown the answer is NULL rather than the other one. cdc.tables() lists the instance names.
• max_lsn_age_seconds is not lag. It is the age of the newest captured transaction, so on an idle database it grows without bound while capture is perfectly current.
• SQL Server's own per-instance functions (cdc.fn_cdc_get_all_changes_<instance>, and the _each per-row form this extension adds for every discovered table function) are ordinary discovered functions and are callable directly.

Reading the change stream — db.cdc.changes(...)

FROM db.cdc.changes('dbo.orders'                    -- a <schema>.<table> or a capture-instance name
      [, starting_position := <BLOB>]               -- EXCLUSIVE lower bound: a 10-byte LSN or a 21-byte _position
      [, ending_position   := <BLOB>]               -- INCLUSIVE upper bound; default db.cdc.max_position()
      [, starting_timestamp := <TIMESTAMP>]         -- INCLUSIVE lower bound in wall-clock time
      [, ending_timestamp   := <TIMESTAMP>]         -- INCLUSIVE upper bound
      [, capture_instance  := '<name>']             -- read ONE instance of a table that has two
      [, images            := 'after']              -- 'after' (default) | 'both'
      [, commit_timestamp  := false]                -- opt-in, see below
      [, enable            := false]                -- capture the table on first read
      [, on_schema_change  := 'error']              -- 'error' (default) | 'ignore' | 'resync' | 'null' | 'fill'
      [, include           := 'changes'])           -- 'changes' (default) | 'snapshot' | 'snapshot+changes'

The resumable idiom. Take the window END first, read a closed window, then advance the cursor to that end — never to max(_position) of what came back, which is lower than what you read whenever you filtered and NULL whenever the window was empty:

-- 1. the window end, taken BEFORE the read and stored whatever the read returns
SET VARIABLE cdc_end = (SELECT db.cdc.max_position());

-- 2. a closed window, resuming from your own cursor
SET VARIABLE cdc_cur = (SELECT cur FROM my_cursors WHERE tbl = 'dbo.orders');
INSERT INTO staging
SELECT * FROM db.cdc.changes('dbo.orders',
                             starting_position := getvariable('cdc_cur'),
                             ending_position   := getvariable('cdc_end'));

-- 3. advance to the WINDOW END
UPDATE my_cursors SET cur = getvariable('cdc_end') WHERE tbl = 'dbo.orders';

⚠ SET VARIABLE + getvariable() is not a stylistic choice — it is the only spelling that binds. An inline scalar subquery is refused as a table-function argument (Binder Error: Table function cannot contain subqueries) and as an EXECUTE argument (Only scalar parameters, named parameters or NULL supported for EXECUTE). A scalar function call is fine in an EXECUTE, so EXECUTE q(db.cdc.max_position()) works too.

Notes:

• An empty window is empty, not an error. A cursor sitting at the window end returns zero rows; so does a capture instance the job has not scanned yet.

• ⚠ A cursor below the retention floor is refused, loudly, and that is the point of the function. SQL Server answers every bad window — a purged cursor, a bound above the watermark, an inverted window, even a misspelled option — with the same message: Msg 313: An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ... . on a call with three arguments. It names neither the cause nor your table (the ... is a real placeholder object SQL Server calls to raise it), and it sends you to inspect a call site while the real news is that your pipeline has lost data. This function checks the window itself and says so.

• ⚠ A schema change inside your window is refused by default, and that is the point. If someone runs ALTER TABLE … ADD while your window is open, the new column is not captured — the read would simply omit it and your pipeline would lose a field with nothing failing. cdc.changes checks cdc.ddl_history before returning rows and refuses, naming the statement and its LSN. Read up to the change and re-bind, or pass on_schema_change := 'ignore' to read anyway (which also saves the check's round trip). A window that starts after the change is clean again.

• on_schema_change := 'resync' repairs the one case a re-capture can repair. If the source has columns the capture instance does not — someone ran ALTER TABLE … ADD — a resync creates a fresh capture instance and answers with a baseline of the whole table in the new shape, plus a handoff to resume from. It keys on a metadata comparison, not on your window, so DESCRIBE already shows the new column; the DDL itself happens at execute, so an EXPLAIN or a CREATE VIEW captures nothing. It is deliberately not a default, and it declines rather than guesses in three cases: a lower bound beside it (a cursor and a fresh baseline are two different reads), an instance this extension did not create (a full-table snapshot on someone else's configuration is not a SELECT's call), and a table that already has two capture instances — SQL Server's maximum, so making room means disabling the older one and destroying whatever history nobody has read. The message names that operator action. When nothing is stale, resync behaves exactly like error: a drift it cannot repair (a type change, a drop) is still refused rather than swallowed.

• ⚠ A resync is NOT re-runnable if its rows fail to land, and this is the sharp edge of the feature. The new capture instance is DDL and commits immediately; the baseline rows are just a result set. So if your INSERT INTO … SELECT … FROM cdc.changes(…, on_schema_change := 'resync') fails while writing — disk, network, a constraint — the instance is created and the rows are not, and re-running the identical statement is refused, because the table now has two capture instances. Measured, not theorised.

  • What to do instead: re-read with on_schema_change := 'error' (or 'ignore') and include := 'snapshot+changes'. Both instances exist now, so an ordinary read already spans them as one stream in the aligned shape, and a fresh snapshot leg gives you a new baseline and a new handoff. Nothing is lost — you just take a different snapshot, at the retry's instant rather than the original's.
  • Do NOT reach for cdc.retire_previous_instance here. The refusal's message suggests disabling the older instance, which is right when consumers have caught up and wrong in exactly this case: your consumer never got the baseline, so its last good cursor is still inside the OLD instance, and retiring it destroys the history between that cursor and the resync. Retire once everyone is past the boundary, not to make a failed retry go through.

• A plain snapshot leg, by contrast, IS fully re-runnable. include := 'snapshot' and 'snapshot+changes' without a resync perform no DDL and consume nothing — nothing server-side records that you took one. Re-running gives a fresh snapshot at a new instant with a later handoff. What you cannot do is resume a half-written snapshot: it is all-or-nothing per attempt, and the handoff rides in the rows, so if the rows do not land neither does the cursor.

• An UPDATE produces ONE row by default (the after-image); images := 'both' surfaces the pair as update_preimage + update_postimage and adds _update_mask — see below.

• To bound how much you take in one pass, use LIMIT — there is no max_rows and there will not be one. LIMIT + ORDER BY _position + a cursor taken from max(_position) is a complete resumable pagination, with no gap and no duplicate between pages:

  SET VARIABLE cur = <your stored cursor>;   -- or db.cdc.min_position('<instance>') to start
  CREATE OR REPLACE TEMP TABLE page AS
    SELECT * FROM db.cdc.changes('dbo.orders', starting_position := getvariable('cur'))
    ORDER BY _position LIMIT 10000;
  -- …consume `page`, then advance. An EMPTY page gives NULL: do not advance, you are caught up.
  SET VARIABLE cur = coalesce((SELECT max(_position) FROM page), getvariable('cur'));
  

  ⚠ A row-count bound can land mid-transaction. Nothing is lost — the 21-byte cursor resumes exactly — but a consumer that needs whole transactions should extend the page to a _commit_lsn boundary rather than cutting at the row count. That caveat is why a built-in max_rows would have been worse than this: it would have had to round down to a transaction boundary and then not return the number of rows its name promises.

• Wall-clock bounds are INCLUSIVE, and a _position is not — that asymmetry is deliberate. A _position is a resume token, so the row it names has already been read and the bound is EXCLUSIVE; a timestamp is an instant you have read nothing of, so starting_timestamp means at or after, and ending_timestamp at or before. Passing both kinds on the same side is refused rather than reconciled — "the tighter of the two" is not a rule you could predict. Mixing sides is fine (starting_position := <cursor>, ending_timestamp := <instant>). Two things to know: the timestamp is naive on both sides, so it means that instant on the SQL Server host's clock, not yours; and the resolution is datetime (~3.33 ms), so two transactions inside one tick are indistinguishable by design.

  ⚠ The clock gap is real and it is silent. DuckDB's now() is your wall clock: a session on Europe/Berlin reading a server on UTC produces now()::TIMESTAMP two hours ahead of anything that server has recorded, so starting_timestamp := now() - INTERVAL 1 HOUR names a window in the server's future and quietly returns nothing. Take the instant from the data (a row's _commit_timestamp, which is already the server's clock) or read the server's own — SELECT * FROM fabricator_query('db', 'SELECT SYSDATETIME()') — rather than from the client. A bound that maps to no captured transaction is an empty window, not an error; one that maps below the retention floor is refused, because the reader cannot tell "those changes were purged" from "this capture instance did not exist yet" and a wrong guess would return a short answer silently.

• ⚠ images := 'both' gives you the before-image — and a trap that comes with it. SQL Server does not store a varchar(max) / nvarchar(max) / varbinary(max) column in an update's before-image unless that update touched it, so such a column reads NULL there whatever the row held. We deliberately do not substitute a placeholder — a placeholder is a value, so it cannot be told apart from a row that genuinely holds it. Read _changed_columns instead: if the column is not in the list, the value was not recorded; if it is, the NULL is genuine.

  SELECT _change_type, notes, list_contains(_changed_columns, 'notes') AS notes_recorded
  FROM db.cdc.changes('dbo.orders', images := 'both');
  

  _update_mask carries the same information as SQL Server's raw bit mask, if you want it. ⚠ If you decode it yourself, count the bit from the RIGHT END of the whole mask — it is a big-endian bit string over the entire varbinary, so on a table with more than eight captured columns the intuitive "byte (ordinal-1)/8" picks the wrong end and silently reports the wrong column: get_bit(_update_mask::BIT, (8 * octet_length(_update_mask) - <ordinal>)::INTEGER), with ordinals from db.cdc.captured_columns joined to db.cdc.change_tables on object_id. ⚠ A mask is only decodable against the instance that produced it, which is what _capture_instance is for — and what _changed_columns already accounts for.

• commit_timestamp is opt-in because it costs a join. It is the only column that needs cdc.lsn_time_mapping, DuckDB will not eliminate that join when nothing selects from it, and the value is a datetime (~3.33 ms) — metadata, never an ordering key. _position is the ordering key.

• ⚠⚠ A table with two capture instances reads as ONE stream, and the NULLs in it mean two different things. A second instance is how a schema change is absorbed: the older one keeps the old column set, the newer one has the new one, and both capture every change from the moment the newer exists — so reading both naively would return every later change twice. cdc.changes splits the window at the boundary (the newer instance's retention floor, which SQL Server records nowhere else) and reads each side once. The declared columns are the union of both instances, by name: the newer instance's columns first, then any the older one alone has, with missing ones NULL-filled.

  Read _capture_instance before you read a NULL. A NULL in a column the row's instance did not capture means "this predates the column"; the same NULL on a row from the instance that does capture it means the value really was NULL. Nothing else can tell you which.

  Two consequences worth knowing:

  • A schema change below the boundary does not trigger on_schema_change. The second instance exists because of it, and the union already carries both shapes. One issued after the newest instance still refuses — nobody re-captured for that one.
  • A column dropped and re-added with a different type is refused, naming both types and both instances. It is the only way two instances can disagree on a type (an ALTER COLUMN is propagated to both), and a union would coerce one era to the other's type — silently, wherever the values happen to convert. Read each instance on its own with capture_instance :=.

  ⚠ For up to one polling interval after you create a second instance, the boundary does not exist yet and the read says so and asks you to retry. capture_instance := works immediately.

• Ordering is yours. Rows arrive in no promised order; ORDER BY _position is correct replay order, and _position values compare as unsigned bytes so min()/max() work.

• enable := true captures the table on first read, so a pipeline needs no separate setup step. The DDL happens when the query RUNS, never when it is planned — EXPLAIN, DESCRIBE and CREATE VIEW over it capture nothing, because the output schema is derived from the source table.

  • ⚠ It does not backfill. Capture starts at the moment of the enable, so rows written before it are invisible and an include := 'changes' read returns zero rows — not an error, because that is a fact rather than a guess. Pair it with include := 'snapshot+changes' to get what is already in the table.
  • ⚠ It is idempotent: on an already-captured table it is a no-op and the changes simply arrive.

Starting from nothing — include := 'snapshot' and 'snapshot+changes'

A change stream can only tell you what CHANGED. To fill an empty sink you also need the state that was already there, joined to the stream at a position where nothing is missed and nothing arrives twice:

-- capture the table AND read everything already in it, in one statement
FROM db.cdc.changes('dbo.orders', enable := true, include := 'snapshot+changes');

-- or take the baseline alone and keep its handoff for the poller
SET VARIABLE cdc_cur = (SELECT DISTINCT _position
                        FROM db.cdc.changes('dbo.orders', include := 'snapshot'));

Baseline rows arrive first, then the changes after them. A baseline row has _change_type = 'insert' (the Delta change-feed spelling, so an existing consumer needs no new branch) and _capture_instance IS NULL — that is how you tell a baseline row from a change. Its _commit_lsn, _seq_val and _commit_timestamp are NULL, because a baseline row is state rather than an event. Every baseline row carries the same _position: the handoff, which is what you resume from.

How it works, and what it costs: one connection takes a shared table lock on the source — writers are frozen, ordinary readers are not — reads the handoff position, and a second connection pins a SNAPSHOT view inside that window. The lock is then released, and the table is read at leisure from the pinned view. The freeze lasts milliseconds, not the length of the read.

⚠ It needs ALLOW_SNAPSHOT_ISOLATION ON for the database. Without it the read refuses and names the ALTER DATABASE to run. READ_COMMITTED_SNAPSHOT is not required.

Exactly-once, or at-least-once, and you can tell which. The capture job is asynchronous, so a transaction that committed just before the lock may not be captured yet — its rows would be in the baseline and above the handoff, i.e. delivered twice. Closing that gap needs sys.sp_cdc_scan to run inside the lock window, and the capture job holds the log-scan session continuously while it is running, so on a database with a running capture job that call is refused and the read is at-least-once. It says so at WARNING level (visible in duckdb_logs), it never loses a row, and it is the guarantee the usual tools for this handoff give unconditionally. Stop the capture job (sys.sp_cdc_stop_job) if the duplicates matter.

What it refuses, and why:

• starting_position beside a snapshot — a snapshot is the starting point. A cursor next to it either replays everything since that cursor or skips what came before it; neither is what anyone means.
• ending_position with 'snapshot' alone — a snapshot is one instant, not a window. It is accepted with 'snapshot+changes', where it bounds the changes half.
• A captured column that no longer exists on the source. A snapshot reads the source table, so a column dropped from it has no value to read, while the change table keeps the column and reads NULL. Read include := 'changes', or capture the table afresh.
• A transaction that has written the table you are snapshotting. The lock would wait for locks only your own transaction can release. COMMIT or ROLLBACK first, or read include := 'changes', which takes no lock.

⚠ An EMPTY table snapshots to no rows, and therefore to no handoff. SELECT DISTINCT _position FROM … is then NULL, and a NULL cursor means "no lower bound" rather than an error — so the next read starts at the retention floor and replays. Prefer include := 'snapshot+changes', where the handoff never leaves the reader; if you do take the baseline alone, fall back to db.cdc.max_position() taken before the read, which is at worst a small replay and never a skip.

• on_schema_change := 'null' and 'fill' answer the added-column drift instead of refusing it. 'null' projects the uncaptured column with the source column's type and NULL on every row — decidable, because _capture_instance says which instance produced the row. 'fill' looks the value up from the live source by key. ⚠⚠ 'fill' produces a torn row and this is not a rough edge, it is what the mode is. The captured columns are as of the change's LSN; the filled column is as of now — and since that column is not captured, no later change event will ever correct it. A delete row fills NULL, because the source row is gone. It also needs a key: with no primary key and no unique index there is nothing to correlate on, and it refuses (use 'null', which needs no key). Both modes leave the capture instance alone — use 'resync' if you want the columns genuinely captured from now on.

db.dbo.fabricator_session_tag(key, value) -> TABLE (SQL Server / Fabric)

Tags the transaction's provider connection with a session-context key, so the statements it causes can be correlated afterwards — e.g. attributing a dbt run's cost in Fabric's query insights. Returns one row: connection_id, session_id, dist_statement_id, tag_key, tag_value.

BEGIN;
SELECT * FROM db.dbo.fabricator_session_tag('dbt_run_id', '7f3c…');   -- e.g. a dbt pre-hook
CREATE OR REPLACE TABLE db.dbo.orders AS SELECT * FROM staged;        -- attributed to that tag
COMMIT;

On a Fabric Warehouse, every statement of that transaction — including the bulk load — then shares one connection_id, so you can total its cost:

SELECT count(*) AS statements, SUM(allocated_cpu_time_ms) AS cpu_ms
FROM fabricator_query('db',
  'SELECT allocated_cpu_time_ms FROM queryinsights.exec_requests_history
    WHERE connection_id = ''<the connection_id returned above>''');

• Must be inside an explicit BEGIN…COMMIT. In autocommit the connection it tags is released with that statement, so the tag would apply to nothing — the function raises instead of silently doing nothing.
• SELECT fabricator_exec(…, 'EXEC sp_set_session_context …') does not work as a substitute: a raw exec joins an existing pinned connection or takes a throwaway one, and nothing is pinned yet when a pre-hook runs.
• Key ≤ 128 bytes, value ≤ 8000 (the sp_set_session_context limits). Values are passed as parameters; the tag is also written into the statement text as a comment so it stays findable in the query history.
• ⚠ Not reliable as a dbt pre-hook at --threads > 1. Measured: the tag a model's own hook set was frequently not what its body saw — a stale value from an earlier run, which silently mis-attributes cost. It is dependable single-threaded, and within one explicit transaction generally.
• To attribute a whole run, set application_name on the secret instead. It is a connection-string property, so every connection the run opens carries it and it cannot go stale — and it needs no SQL changes. Combine it with OPTION (LABEL = '…') for per-statement grain. Details and the CU story: docs/consumption-monitoring.md.

fabricator_version() -> VARCHAR

Extension version (compatibility shim). fabricator_managed_dir() / fabricator_test_scan('x') are diagnostics for the CoreCLR + Arrow spine.

fabricator_wait(rows, millis [, threads := …] [, hold_lock := …] [, async_wait := …]) -> TABLE(id BIGINT)

A DIAGNOSTIC source: emits rows BIGINTs in 2048-row chunks, sleeping millis before each chunk. It contains no Arrow, no .NET and no provider — so it answers scheduling questions ("does SET threads reach this plan?", "do these UNION ALL branches overlap?") without the extension's own machinery as a rival explanation.

SET threads=4;
SELECT count(*) FROM fabricator_wait(8192, 500);            -- 4 chunks x 500 ms over 4 threads: ~0.5 s
SELECT count(*) FROM fabricator_wait(8192, 500, threads := 1);  -- the same work serialized: ~2.0 s

threads overrides what the scan reports to the planner; hold_lock and async_wait reproduce and then fix, in pure C++, the worker-starvation shape described in docs/scan-concurrency.md §5. A negative argument is REFUSED rather than clamped — a sleep that never returns would hang rather than fail.

fabricator_plugins() -> TABLE(root, path, status, provider, detail)

What the plugin scan looked at and what it decided — one row per configured plugin root, plus one per candidate assembly under it. This is the first place to look when a plugin does not appear.

SELECT status, provider, detail FROM fabricator_plugins() WHERE status <> 'shared';

Where plugins are looked for. FABRICATOR_PLUGIN_DIR (a comma-separated list of directories) if set — which replaces the defaults rather than adding to them — otherwise two roots, in this order:

1. ~/.duckdb/fabricator/plugins, the per-user root that fabricator_install_plugin() writes to;
2. the bundled root, plugins/ inside the extension's managed directory (what fabricator_managed_dir() reports) — a place a plugin may be dropped so it travels with the extension. ⚠ Nothing ships there today. The Fluid template engine used to, and became part of the extension proper on 2026-09-02 — precisely because FABRICATOR_PLUGIN_DIR replaces this root rather than extending it, so setting it to load your own plugin silently cost you fluid_render.

The first root wins. If the same plugin is present in both, the one in (1) loads and the other is reported rejected with a provider-name collision — so installing your own copy of a bundled plugin takes precedence, and fabricator_plugins() tells you the shipped copy was set aside rather than silently ignored. The search is recursive, so a plugin may sit in a nested folder such as ~/.duckdb/fabricator/plugins/myplugin/1.0.0/windows_amd64/.

A plugin's global functions are registered while the extension loads, so a plugin added to the folder becomes available the next time DuckDB loads fabricator — not in the running session. See docs/plugin-system.md.

Templates — fluid_render(...), fluid_replacement_query(...), fluid_query(...), fluid_scalar(...), fluid_aggregate(...), fluid_query_batch(...), fluid_query_inout(...) and fluid_query_lateral(...)

The Fluid / Liquid template engine is built in — it ships inside the extension and needs no configuration. It contributes eight global functions, all taking a params bag that is a DuckDB STRUCT (preferred — typed, no quoting), a MAP, a JSON string, a LIST, or a plain scalar.

fluid_render(template, params) renders a template to text:

SELECT fluid_render('Hello {{ params.name }}, you have {{ params.n }} messages', {'name':'world','n':3});
-- Hello world, you have 3 messages

SELECT fluid_render('{% if params.x > 1 %}big{% else %}small{% endif %}', '{"x":5}');
-- big

fluid_replacement_query(template [, params := …]) renders a template to SQL — the result is a relation, and the rendered text is the statement:

SELECT * FROM fluid_replacement_query('SELECT {{ params.n }} AS n', params := {'n': 7});
-- 7

-- The COLUMN LIST comes from the argument, so the output schema differs per call:
SELECT * FROM fluid_replacement_query(
  'SELECT {% for c in params.cols %}{{ c | sql_ident }}{% unless forloop.last %}, {% endunless %}{% endfor %}
   FROM (SELECT 1 AS a, 2 AS b, 3 AS c)',
  params := {'cols': ['a','c']});
-- a=1, c=3

The name says which mechanism it is. DuckDB calls this a replacement scan: the call is replaced at bind by the statement the template generated. It was called fluid_query until 2026-09-13; that name now belongs to a different function (below), so this is a breaking rename with no alias.

params is optional, so fluid_replacement_query('SELECT 1') works. The call disappears at bind time: DuckDB substitutes the generated statement for it, so the generated SQL's own scans keep their full projection and filter pushdown, parallelism and join reordering, and nothing streams through the bridge at execution. That also means the generator runs during binding, repeatedly and without executing anything — an EXPLAIN, a DESCRIBE or a CREATE VIEW re-renders the template.

⚠ {{ x }} is interpolated RAW into the SQL. That is deliberate: a template must be able to emit table names, predicates and whole fragments, which is the only reason to generate SQL from a template. For values that are data, use the two filters the plugin registers:

• {{ v | sql }} — a SQL literal (quoted string, invariant-culture number, typed date/time, NULL)
• {{ n | sql_ident }} — a quoted identifier
• {{ n | unquote_ident }} — the inverse, for a name that arrives ALREADY quoted

The first two are allow-lists: a value with no provably safe rendering is refused by name rather than interpolated.

unquote_ident turns "my ""odd"" tbl" back into my "odd" tbl, so {{ n | unquote_ident | sql_ident }} normalises a caller-supplied identifier however it was written. ⚠ It declines what it cannot decide, which is the point: "a"."b" is TWO identifiers, so it is left alone rather than mangled into a"."b, and a qualified db.schema.t keeps all its parts (a dot can live inside quotes, so reducing it to a last part needs a real parser).

⚠ Do not reach for | json to quote an identifier. It escapes as "od"d" — a different identifier — and it looks correct for every name that happens to contain no quote.

SELECT * FROM fluid_replacement_query('SELECT {{ params.v | sql }} AS v', params := {'v': 'O''Brien'});
-- O'Brien   (not a syntax error, and not an injection)

fluid_query(template, params, arg0, arg1, …) also renders a template to SQL, but runs the statement itself — which is what lets it hand the template the scan's pushed filter and projection:

SELECT * FROM fluid_query(
  '{% if is_bind %}SELECT 0::BIGINT AS k, ''''::VARCHAR AS note
   {% else %}SELECT k, {{ filter_sql | sql }} AS note FROM t{% endif %}', NULL)
WHERE k > 7;
-- note = "k" > 7

⚠ This does not buy you "pushdown" — fluid_replacement_query already has more of it. That one's call disappears at bind, so DuckDB pushes straight into the generated statement. What fluid_query buys is that the template sees the predicate and can act on it, which matters exactly where the optimiser cannot see through what the template generated: an aggregate, a volatile call, a remote read. Use it to fold the predicate into a remote WHERE, a partition choice or a narrower scan — otherwise prefer fluid_replacement_query, which is cheaper on every axis.

At scan time (not at bind — the planner has not run, so branch on is_bind) the template gets:

-- act on one conjunct: read ONLY the partition the predicate names
CREATE TABLE part_20260913 AS SELECT DATE '2026-09-13' AS day, 7::BIGINT AS v;
CREATE TABLE part_20260912 AS SELECT DATE '2026-09-12' AS day, 99::BIGINT AS v;

SELECT * FROM fluid_query(
  '{% if is_bind %}SELECT NULL::DATE AS day, 0::BIGINT AS v
   {% else %}SELECT * FROM {% for f in filters %}{% if f.column == "day" %}part_{{ f.value | date: "%Y%m%d" }}{% endif %}{% endfor %}{% endif %}', NULL)
WHERE day = DATE '2026-09-13';
-- 2026-09-13 | 7     — part_20260912 is never opened

⚠⚠ It is a hint you may ignore, and only the top-level AND conjuncts are offered. DuckDB re-applies every pushed predicate whatever the template did, so a template that never mentions filters is correct, just slower. A branch of an OR is deliberately absent from filters: it is not true of every row, so narrowing by it would drop rows. filter_sql still carries the whole predicate, where splicing it is safe.

⚠ Unlike fluid_replacement_query, this one has a dry run. There every render is the bind, so an EXPLAIN re-renders the template and any {% exec %} in it writes. Here EXPLAIN resolves the schema from the is_bind render and never performs the scan render, so a template's side effects stay put.

The tail arguments are bind-time constants, staged as input_table under arg_0, arg_1, … ⚠ Unlike fluid_scalar, they cannot be renamed with name := v: DuckDB reads that as a named parameter on a table function, so the value never reaches the call. params is positional here (matching fluid_query_lateral), so write fluid_query('SELECT 1', NULL).

fluid_scalar(template, params, arg0, arg1, …) renders a template to a SQL SELECT that DuckDB evaluates over the per-row arguments — and the template declares its own return type:

SELECT n, fluid_scalar(
  '{% if is_bind %}select NULL::STRUCT(a INTEGER)'
  '{% else %}select {''a'': arg_0 + 1} from input_table{% endif %}', NULL, n) FROM t;
-- {'a': 2}, {'a': 3}, …   a real STRUCT(a INTEGER), not text

template and params are bind-time constants; everything after them is per-row and is staged as input_table. Name an argument and the column takes that name — counter := i is readable as counter rather than arg_0:

SELECT i, fluid_scalar(
  '{% if is_bind %}select NULL::BIGINT'
  '{% else %}select counter * 2 from input_table{% endif %}', NULL, counter := i) FROM range(5) t(i);

A plain column reference names itself too (n is n); anything else — a computed expression, a function call, a literal — is arg_0, arg_1, … counted over the whole tail.

input_table is also available at bind, empty but with the real column names and types, so a template can build its SELECT list — and its result type — from the input schema rather than hard-coding either:

SELECT typeof(fluid_scalar(
  '{% query q %}describe select * from input_table{% endquery %}'
  'select NULL::{{ q[3].column_type }} from input_table', NULL, n, d, m)) FROM t;
-- the type of the third argument, whatever it is
``` Under `is_bind` the template renders a SELECT of the
result type — `select NULL::<type>` is the usual answer — and DuckDB binding that statement is what
determines the type. Any type DuckDB can express works, with nothing lost: `STRUCT(a INTEGER, b VARCHAR)`,
`DECIMAL(9,2)` with its scale, `INTEGER[]`, `MAP(VARCHAR, INTEGER)`. The template writes its own `select`;
nothing is prepended for you, so the `is_bind` render is a statement you can paste into a shell and run.

Because the type comes from the *template*, one registered function serves many result types — including a
type chosen by the constant params:

```sql
SELECT typeof(fluid_scalar(
  '{% if is_bind %}select NULL::{{ params.t }}{% else %}select arg_0 from input_table{% endif %}',
  {'t': 'DECIMAL(9,2)'}, 7));
-- DECIMAL(9,2)

⚠ The template renders one SQL expression per chunk, not once per row: Liquid decides the shape of the computation from the constant params, and DuckDB computes every row. So the template cannot branch on a row value in Liquid — emit SQL that branches instead. Use fluid_render when you want Liquid itself to run per row.

⚠ A scalar owes exactly one value per row, in input order, and with a rendered statement that is partly your contract. Enforced for you: exactly one output column, and a row count equal to the chunk's — a statement that changes cardinality is refused by name. Not enforced: order. A plain projection over input_table preserves it; a statement that can reorder should order by a key of its own — pass one as an argument, e.g. rn := row_number() over (), then order by rn.

⚠ The declared type is authoritative: the expression is cast to it, so 42 under a declared BIGINT is fine. A conversion that cannot happen fails with DuckDB's own message; a lossy but legal one (a DOUBLE expression under a declared BIGINT) truncates silently, exactly as a declared column type does.

⚠ A named argument documents an argument; it does not move it. fluid_scalar(tpl, counter := 7, 2) still binds 7 as the params bag — DuckDB discards the name for dispatch on any scalar, built-ins included (upper(zzz := 'a') returns A). Order is always positional.

⚠ The template's SQL runs on its own connection, so it cannot see your TEMP tables — use a regular table. The params bag is available both as {{ params.x }} and as getvariable('params').

The bag is bound WHOLE, under the name params — {{ params.n }}, {{ params[0] }}, {{ params.size }}:

SELECT fluid_render('dot={{ params.n }} ord={{ params[0] }} size={{ params.size }}', {'n': 7, 'z': 9});
-- dot=7 ord=7 size=2

⚠⚠ BREAKING (0.0.14): a bag's members are no longer ALSO bound as bare variables. {{ n }} used to work alongside {{ params.n }} and now renders empty; there is no alias. One spelling means a member can no longer shadow, or be shadowed by, anything else in the template's namespace. Rewrite {{ x }} as {{ params.x }} — including inside {% if %}, {% for %} and block-tag arguments ({% query r xs: params.v %}).

That is what makes a bag with no named members usable at all — a JSON array, a DuckDB LIST, or a bare scalar:

SELECT fluid_render('first={{ params[0] }} size={{ params.size }}', [10, 20, 30]);
-- first=10 size=3

SELECT fluid_render('v={{ params | plus: 1 }}', 41);
-- v=42

⚠ A member literally named params does not win: {{ params }} is always the bag, and the member is reachable as {{ params.params }}. A NULL bag binds nothing at all, params included, so {% if params %} is how you ask whether one was passed.

⚠ A VARCHAR bag is JSON when it is JSON, and a plain string when it is not — so '{"x":5}' is an object and 'result' is the string result. The one exception: text beginning { or [ is plainly an object or array attempt, so a malformed one ('{"x": 5') is an error rather than a string — otherwise every {{ params.x }} would render empty with nothing failing.

The bag is also a DuckDB variable, so the template's own SQL can read it — getvariable('params') inside any {% query %} or {% exec %} block, without interpolating it as text or re-passing it as a block argument:

SELECT fluid_render(
  '{% query r %}SELECT getvariable(''params'').region AS g{% endquery %}{{ r[0].g }}',
  {'region': 'eu', 'n': 7});
-- eu

The value keeps its type: a DATE stays a DATE and a DECIMAL(9,2) stays exact, because the bag is handed to DuckDB as a value rather than rendered into the statement. A bag with no members works too — getvariable('params')[1] for a LIST, and a bare scalar arrives as a scalar.

⚠ A JSON-string bag stays a VARCHAR here, while Liquid parses it — so {{ params.region }} works for both spellings and getvariable('params').region only for the STRUCT one. Prefer the STRUCT form. For a JSON bag, cast it yourself: getvariable('params')::JSON->>'region', or re-declare it once with {% exec %}SET VARIABLE pj = getvariable('params')::JSON{% endexec %}. It is deliberately not cast for you -- dot access on a JSON value returns a quoted scalar ("eu", not eu), and the cast needs the json extension loaded.

⚠ The variable is scoped to that render's own connection: it cannot collide with a variable of yours (yours is not visible inside a render either), and it is gone when the render ends. With no bag it is never set, and an unset variable reads as NULL — so getvariable('params') IS NULL asks in SQL what {% if params %} asks in Liquid.

⚠ In fluid_replacement_query it is readable from an explicit {% query %} / {% exec %} block, not from the statement the template generates — that statement is bound by your own connection. Interpolate params into generated SQL directly ({{ params.n }}), which is what the template is for. In fluid_query_batch, fluid_query_lateral and fluid_query_inout the generated statement does see it, because those run it on the template's own connection.

If the params have to reach a relation there, stage it in {% exec %} — which does see the variable — and publish() the result.

The bag can come from SQL rather than a literal — params := ? in a prepared statement (DuckDB re-binds every EXECUTE, so the template is re-rendered and even the column list may differ between two executes of one prepared statement), or params := {'cols': getvariable('cols')} to drive it from a session variable.

A template can read from the database. query(sql) runs a SELECT on the DuckDB you are already connected to and hands the template its rows, addressable by name, by index and by .size:

SELECT fluid_render(
  '{% assign rs = query("SELECT region, sum(amt) AS total FROM orders GROUP BY region ORDER BY region") %}
   {% for r in rs %}{{ r.region }}={{ r.total }} {% endfor %}', NULL);
-- eu=15 us=25

Inside fluid_replacement_query this runs while the statement is being bound, so the database can decide what the generated SQL — and therefore the result's own column list — should be:

CREATE TABLE wanted AS SELECT * FROM (VALUES ('region',1),('amt',2)) v(col,ord);

SELECT * FROM fluid_replacement_query(
  'SELECT {% assign rs = query("SELECT col FROM wanted ORDER BY ord") %}
   {% for r in rs %}{{ r.col | sql_ident }}{% unless forloop.last %}, {% endunless %}{% endfor %}
   FROM orders ORDER BY amt');
-- columns: region, amt

Parameters bind by name. Write query as a filter and give it named arguments; the statement references them as $name:

SELECT fluid_render(
  '{% assign rs = "SELECT sum(amt) AS total FROM orders
                   WHERE region = $region AND amt > $min" | query: region: "eu", min: 6 %}
   {% for r in rs %}{{ r.total }}{% endfor %}', NULL);
-- 10

⚠ It is a filter, not a second argument to query(...) — because that is where Liquid puts named arguments. query('sql', a: 5) is a parse error, and Liquid has no dictionary literal, so sql | query: a: 5 is the spelling that works.

The values are bound, never spliced, so a parameter can never become SQL:

SELECT fluid_render(
  '{% assign rs = "SELECT count(*) AS n FROM orders WHERE region = $r"
    | query: r: "eu'' OR 1=1 --" %}{% for r in rs %}{{ r.n }}{% endfor %}', NULL);
-- 0   (one value that matches no region — not 3, which splicing would have given)

Numbers, strings, booleans, dates and nil can be bound; an integral number binds as BIGINT and a fractional one keeps its exact scale. Every parameter must be named: a positional argument is an error rather than silently ignored, and a parameter the statement wants but you did not supply is reported by DuckDB, naming it.

A list binds too, so you can pass a set of values without building it in SQL:

SELECT fluid_render(
  '{% query r xs: params.v %}SELECT count(*) AS n FROM orders WHERE region IN (SELECT unnest($xs))
   {% endquery %}{{ r[0].n }}', {v: ['eu','us']});

Its elements must all be of one kind — numbers, strings, booleans, dates, or nested lists and structs, with nils allowed anywhere among them. A mixed list is refused by name rather than coerced, because the only shape they all share is text and turning 5 into '5' would quietly change what the statement compares.

Lists and structs nest, so a template can hand a statement a whole small table:

SELECT fluid_render(
  '{% query rows xs: params.people %}SELECT string_agg(u.label, '','') AS s
   FROM (SELECT unnest($xs) AS u){% endquery %}{{ rows[0].s }}',
  {people: [{'label':'a'}, {'label':'b'}]});
-- a,b

Structs in one list must share their field names and order — unioning them with NULLs would invent members you never wrote. A DuckDB MAP comes back as a struct, because nothing at the template level distinguishes the two.

⚠ query() runs SELECT statements only, and this is not a policy you can turn off. A template is rendered while a statement is being bound, and binding repeats and happens without executing — so a write here would fire on an EXPLAIN of a statement that never runs, and again every time a view over it is used. Anything else is refused before it runs, by DuckDB's own parser:

SELECT fluid_render('{% assign r = query("DELETE FROM orders") %}x', NULL);
-- Error: query() runs SELECT statements only ... Only SELECT statements can be serialized to json!

WITH-prefixed writes and COPY … TO are refused too, as is any multi-statement string containing a write (a string of nothing but SELECTs is allowed and harmless). DESCRIBE, SUMMARIZE, VALUES, TABLE t, FROM t, CTEs and set operations all work; PIVOT and EXPLAIN are refused although they are read-only — define a view outside the template if you need them. To write, use exec() below (which works on both surfaces, with the bind-repetition caveat noted there).

⚠ query() reads COMMITTED data. It runs on its own connection, so inside an explicit transaction it does not see that transaction's uncommitted rows — a template cannot observe the writes of the statement running it. Rows are held in memory (a template may loop over them repeatedly), and a result above one million rows is refused rather than truncated.

It does inherit your session's TimeZone and search_path (and with them current_catalog() and current_schema()), so an unqualified FROM t resolves the way it does in the statement that rendered the template, and a timestamp renders in the zone you set. Name and time resolution are inherited; the transaction is not.

A template can also WRITE — exec(sql). It is query()'s mirror image: query() runs SELECT statements only, exec() runs everything else, and both decide with DuckDB's own parser. It returns the affected-row count, so a template can report what it did:

SELECT fluid_render('inserted={{ exec("INSERT INTO audit VALUES (1), (2), (3)") }}', NULL);
-- inserted=3

Parameters bind by name through the filter form, exactly as for query() — and, exactly as for query(), the value is bound rather than spliced:

SELECT fluid_render('deleted={{ "DELETE FROM orders WHERE region = $r" | exec: r: "eu" }}', NULL);
-- deleted=2

Unlike query(), the plain form takes several statements in one call, and the count is the last one's:

SELECT fluid_render('{{ exec("CREATE TABLE m AS SELECT 1 AS c; INSERT INTO m VALUES (2)") }}', NULL);
-- 1        (and m now has 2 rows)

⚠⚠ exec() works in fluid_replacement_query too — and there a write MULTIPLIES. A fluid_replacement_query template is rendered while DuckDB is binding, and binding repeats and happens without executing. Measured, one counter through four steps that execute nothing you wrote:

step	rows written
EXPLAIN SELECT * FROM fluid_replacement_query('… {{ exec("INSERT …") }} …')	1
merely CREATE VIEW v AS SELECT * FROM fluid_replacement_query(…)	2
one SELECT … FROM v	3
a second SELECT … FROM v	4

The last two rows are the ones that bite. A writing template behind a view writes on every use, and it looks fine in testing, where the statement runs once. EXPLAIN writing is merely startling.

So put writes in fluid_render unless you specifically want one per bind. It is a volatile scalar, so it is never folded into a plan (an EXPLAIN does not run it) — its multiplier is rows, which you control by choosing the statement's cardinality. SELECT fluid_render(…) with no FROM is one row.

⚠⚠ A statement cannot see the write its own template made — so "prepare, then select" does not work. This is the pattern most people will try first, and it fails in the least obvious way: the write really happens, and the generated SQL reads the state before it.

SELECT c FROM fluid_replacement_query('{% assign _ = exec("UPDATE t SET c = 42") %}SELECT c FROM t');
-- 1     the generated SQL reads the old value
SELECT c FROM t;
-- 42    the write was real

exec() runs on the RENDER's own connection, and the outer statement's snapshot predates the commit — the same one-connection rule that makes query() read committed data, in the other direction. A template therefore cannot create a table the same statement selects from: the CREATE succeeds and the statement still reports Table with name t does not exist! (helpfully adding "Did you mean memory.t" — it exists, just not for that statement).

Use two statements: exec() in one, the read in the next. So exec() inside fluid_replacement_query is for side effects the generated SQL does not itself read — an audit row, a log line, staging for later.

⚠ But the template's OWN query() does see it — see the next section. What cannot see it is the SQL you generate, because that runs on the outer statement's connection.

⚠ Write it as {% assign _ = exec(…) %}, not {{ exec(…) }} — the latter interpolates the row count into your SQL (1SELECT c FROM t, which is a parser error).

⚠ A SELECT is refused by exec(), and the reason is a wrong number. The count is read from the statement's first column, so exec('SELECT count(*) FROM t') would report the count of rows in t as though that many rows had been affected. It is refused by name instead, pointing you at query().

Note this makes exec() disagree with fabricator_host_exec on one shape: a CREATE TABLE … AS SELECT reports the rows it created here (7) and 0 there, because only the SQL-level function can ask the engine to classify the statement. Plain DDL is 0 in both.

⚠ fluid_render is a scalar, so exec() runs once PER ROW. Rendering a writing template over three rows performs the write three times — measured. That is the multiplier to watch on this surface, as bind repetition is on the other one.

For a real statement, use the {% exec %} BLOCK instead. The body is rendered to a separate buffer, executed as SQL, and nothing is written to the output — so the statement is ordinary template text: multi-line, with {% for %} and {% if %} inside it, and no quote-escaping.

SELECT fluid_render('{% exec %}
INSERT INTO audit VALUES
{% for r in rows %}({{ r.id }}, {{ r.name | sql }}){% unless forloop.last %},{% endunless %}
{% endfor %}
{% endexec %}done', {'rows': [{'id': 1, 'name': 'a'}, {'id': 2, 'name': 'O''Brien'}]});
-- done          (the block renders nothing; both rows land, with the quote escaped by | sql)

Because the block is a statement rather than an argument, it is naturally conditional — an unreached {% exec %} runs nothing:

SELECT fluid_render('{% if params.go %}{% exec %}DELETE FROM staging{% endexec %}cleared{% else %}skipped{% endif %}', {'go': false});
-- skipped       (and nothing was deleted)

⚠ Interpolation inside the block is RAW, exactly as in fluid_replacement_query and for the same reason — a template must be able to emit object names and whole fragments. Use {{ v | sql }} for a value and {{ n | sql_ident }} for an identifier. Splicing a raw string containing a quote gives a parser error rather than a silent injection, but that is not a substitute.

{% print %} renders a result set directly — {% query %} with the destination changed, for when you want the rows in the output rather than in a variable:

SELECT fluid_render('{% print delim: ", " %}
SELECT i AS n, i*i AS sq FROM range(1, 4) t(i)
{% endprint %}', NULL);
-- 1, 1
-- 2, 4
-- 3, 9

delim joins the values of a row (default a space) and rowdelim joins the rows (default a newline); both are joiners, so nothing is written before the first row or after the last.

sql_literal: true renders each value as a SQL literal rather than as text — the same conversion {{ v | sql }} performs — which is what makes a result set into a VALUES list:

SELECT fluid_render('INSERT INTO t VALUES ({% print sql_literal: true, delim: ", ", rowdelim: "), (" %}
SELECT i, ''v''||i FROM range(1, 3) t(i)
{% endprint %});', NULL);
-- INSERT INTO t VALUES (1, 'v1'), (2, 'v2');

Like the filter it is an allow-list, not an escaper: a list or struct cell is refused by name rather than stringified. Any other named argument is a bound parameter, exactly as in {% query %} — so a statement cannot have a parameter called delim, rowdelim or sql_literal, and DuckDB will say so by name if you try.

Name the {% exec %} block to get the affected-row count, the same way {% query name %} names its result:

SELECT fluid_render('{% exec n %}DELETE FROM staging WHERE loaded{% endexec %}removed {{ n }} rows', NULL);

The name is optional — a bare {% exec %} still renders nothing and binds nothing — and named arguments follow it exactly as in {% query %}: {% exec n limit: 100 %}. The number is the same one the exec(...) function and the | exec: filter return.

⚠ It obeys the same rules as the function: it refuses a SELECT, it runs on the render's own connection (so a {% exec %}CREATE TEMP TABLE …{% endexec %} is readable by a later query() in the same template), and inside fluid_replacement_query it still runs at bind time, with the multiplication described above. A {% break %} inside the block leaves a half-rendered statement, which is discarded rather than executed.

{% provider_query %} / {% provider_exec %} run the body on ANOTHER engine. The body is written in that provider's dialect (T-SQL, DAX) and runs against an attached fabricator catalog, while the result comes back as an ordinary Liquid value:

SELECT fluid_render('{% provider_query "mssql" r region: "eu" %}
                       SELECT id, region
                       FROM dbo.people
                       WHERE region = @region
                     {% endprovider_query %}{{ r.size }} rows, first {{ r[0].id }}', NULL);

SELECT fluid_render('{% provider_exec "mssql" n old: "us", new: "uk" %}
                       UPDATE dbo.people SET region = @new WHERE region = @old
                     {% endprovider_exec %}updated {{ n }}', NULL);

The tag's named arguments become the provider's parameters — @region for SQL Server and DAX — and they are bound, never spliced, so the dialect difference between DuckDB and the provider cannot leak into a value. The catalog is an expression, so "mssql" and params.cat both work; a bare word is a Liquid variable and is refused by name rather than reaching the provider as an empty catalog. The name is required on provider_query and optional on provider_exec, matching {% query %} and {% exec %}.

⚠ The SELECT-only guard does not apply here, and that is deliberate. {% query %} refuses a non-SELECT by asking DuckDB's parser; there is no such parser for another engine's dialect, so a {% provider_exec %} inside fluid_replacement_query runs at bind time — which repeats, including on EXPLAIN and on every use of a view built over it. Use fluid_render for a write unless you want that multiplication, and treat the two tag names as your own assertion about what the body does.

What the tags buy over {% query r %}SELECT * FROM fabricator_query('mssql', '…'){% endquery %} — which has always worked — is that the body stops being a quoted string argument: multi-line, with {% for %} and {% if %} inside it, and no escaping.

{% query name materialize: 'view' %} also leaves the result on the connection — as a TEMP view or table the rest of the template can read as SQL, while the name stays usable in Liquid too:

SELECT fluid_render('{% query v materialize: "view" %}SELECT 3 AS a{% endquery %}'
                 || '{% query u %}SELECT a FROM v{% endquery %}sql={{ u[0].a }} fluid={{ v[0].a }}', NULL);
-- sql=3 fluid=3

materialize: null is the default and means today's behaviour: the rows come back into Liquid and nothing is left on the connection. With materialize: set the rows stay in DuckDB — nothing crosses into the template unless you actually read the variable, and then it is fetched once and cached. Both kinds are TEMP: they live on the render's own connection and die with it.

⚠ A 'view' is evaluated when you read it, a 'table' when the block ran. So an {% exec %} between the block and the first {{ v[0].… }} is visible through a view and not through a table. That is what the two words mean in SQL, and it is the reason to pick one.

⚠ A 'table' can take the block's named parameters and a 'view' cannot — a view stores its body, so DuckDB refuses a bound parameter inside one. {% query t materialize: 'table', x: 5 %}SELECT $x … works; the same with 'view' is refused, naming the fix.

⚠ A temp object shadows a catalog table of the same name for the rest of the render — that is DuckDB's rule for temporaries, and the name is the one you wrote. Your catalog table is untouched; the temp one dies with the render.

⚠ materialize is a reserved argument name, so a statement cannot have a parameter called that. The body is still SELECT-only.

{% ret %} ends the render there and keeps what was written — the guard clause Liquid has no other way to write. Everything after it, in the whole template, neither renders nor runs.

SELECT fluid_render('A{% ret %}B', NULL);            -- A
SELECT fluid_render('A{% break %}B', NULL);          -- AB  (break is ignored outside a loop)

SELECT * FROM fluid_replacement_query('SELECT 7 AS v{% ret %} WHERE 1=0');
-- 7        -- the WHERE was never rendered, so the statement is `SELECT 7 AS v`

It is a return, not a break: inside a {% for %} it leaves the whole render, not the loop.

SELECT fluid_render('{% for i in (1..5) %}{{ i }}{% if i > 2 %}{% ret %}{% endif %}{% endfor %}Z', NULL);
-- 123     -- no Z

⚠ Stop means stop. A statement after {% ret %} does not merely render nothing — it does not run. An {% exec %} below the tag performs no write, and a {% ret %} inside an {% exec %} or {% query %} body discards that body rather than executing half a statement.

⚠ Inside an {% include %} it ends the WHOLE render, not just the included page. This differs from Scriban's ret, which our tag otherwise mirrors: Fluid renders an include as a nested template, so there is no boundary to stop at without re-implementing {% include %}. {% render %} behaves the same, even though standard Liquid isolates its scope.

It takes no arguments ({% ret 1 %} is a parse error) and works on every surface — fluid_render, fluid_replacement_query, fluid_query_batch and fluid_query_lateral.

publish(name) hands a table the template STAGED to the SQL it is generating. This is the piece that makes multi-step staging inside fluid_replacement_query useful: stage with {% exec %}, then scan the result.

SELECT * FROM fluid_replacement_query('
{% exec lo: 1, hi: 5 %}
  CREATE TEMP TABLE _result AS SELECT i AS n, i * i AS sq FROM range($lo, $hi) t(i)
{% endexec %}
SELECT * FROM {{ publish(''_result'') }}
');
-- n | sq
-- 1 | 1
-- 2 | 4
-- 3 | 9
-- 4 | 16

publish renders a fabricator_scan('<token>') call, so it must be interpolated raw — {{ publish(…) }}, never {{ publish(…) | sql }}. Values keep their types: a DATE stays a DATE, a list stays a list, a struct stays a struct — which is the difference from building a VALUES list with {% print sql_literal %}, where every temporal becomes a TIMESTAMPTZ and a list is refused.

⚠ Nothing simpler works, and it is worth knowing why. A CREATE TEMP TABLE belongs to the render's own connection, so the caller cannot see it at all; and a real table created by {% exec %} is invisible to the statement being bound, because that statement's catalog snapshot predates the commit. publish sidesteps both by handing the rows over as a table function instead of as a name.

⚠ It is LAZY — nothing runs until the scan pulls, and the rows then stream. There is no row cap, so a staged relation larger than memory is fine, and the scan's own completion releases it.

⚠ A publication is scanned ONCE, and a second scan fails saying so rather than returning zero rows. Call publish per reference; reusing one token twice is an error.

⚠ Two publications from the SAME template cannot be scanned in one statement — both stream from the render's single connection, which allows one live result at a time, so it fails (loudly, never silently). Two ways round it, whichever fits: put the join in {% exec %} and publish one relation — better anyway, since the work stays inside DuckDB — or use two separate fluid_replacement_query calls, which are two connections.

⚠ A publication that is never scanned — an EXPLAIN renders the template and never runs it — keeps the render's connection, and the staged table with it, until 32 further publications reclaim it; a scan of a reclaimed one says so. Publish in the statement that scans it; do not keep a token across statements.

⚠ For a single query a WITH … AS MATERIALIZED CTE inside the generated SQL is still better — it needs no publication at all and keeps the whole relation inside one DuckDB plan:

SELECT * FROM fluid_replacement_query('
WITH _result AS MATERIALIZED (SELECT i AS n, i*i AS sq FROM range(1, 5) t(i))
SELECT * FROM _result');

publish is for a relation computed in several steps, which a CTE cannot express.

And {% query name %} is its read-side twin — the body is SQL, and the result is a result set. Like {% capture %}, it binds to a name; unlike it, what you get back is rows you can index and iterate, not text.

SELECT fluid_render('{% query result %}
SELECT i AS n, i * i AS sq FROM range(1, 5) t(i)
{% endquery %}{% for r in result %}{{ r.n }}^2={{ r.sq }};{% endfor %}', NULL);
-- $1^{2}$=1;$2^{2}$=4;$3^{2}$=9;$4^{2}$=16;

It is the same value query(...) returns — {{ result[0].n }}, {{ result.size }}, arithmetic and comparisons on the columns all work — so the two spellings differ only in how you write the SQL. Together with {% exec %} and the per-render connection, a template can stage and then read its own data:

SELECT fluid_render('{% exec %}
CREATE TEMP TABLE stg AS SELECT 10 AS v UNION ALL SELECT 32
{% endexec %}{% query t %}
SELECT sum(v) AS total FROM stg
{% endquery %}total={{ t[0].total }}', NULL);
-- total=42

⚠ The block takes optional named arguments, and they become BOUND parameters — {% query t region: 'eu', min: 10 %} binds $region and $min, so a value crosses as a value rather than as SQL text. Commas separate them, as everywhere else in Liquid. Interpolation with {{ v | sql }} still works and is what you need for an object name or a fragment, which a parameter cannot carry.

⚠ It obeys the same rules as query(...): SELECT statements only, committed reads on the render's own connection, and a result above one million rows is refused rather than truncated.

exec() and query() in one template share ONE connection — so a template can stage its own data. Everything a single render runs goes through one pinned DuckDB connection, which means the template's own later query() sees what its earlier exec() created. A temporary table is the natural scratch space: nothing outside the render can see it, and it disappears when the render ends — no name in your catalog, nothing to clean up.

SELECT fluid_render(
  '{% assign _    = exec("CREATE TEMP TABLE scratch AS SELECT 7 AS v") %}'
  '{% assign rows = query("SELECT v FROM scratch") %}'
  'v={{ rows[0].v }}', NULL);
-- v=7

This is what makes multi-step generation work on the fluid_replacement_query surface: stage, read the result back, and interpolate it into the SQL you generate.

SELECT * FROM fluid_replacement_query(
  '{% assign _    = exec("CREATE TEMP TABLE s AS SELECT 3 AS a UNION ALL SELECT 4") %}'
  '{% assign rows = query("SELECT sum(a) AS t FROM s") %}'
  'SELECT {{ rows[0].t }} AS staged');
-- staged = 7

⚠ The scope is ONE render. For fluid_replacement_query that is one bind; for fluid_render, which is a scalar, it is one row — so three rows are three connections, and a temp table made by one row is invisible to the next. That is deliberate: it is what keeps a per-row scalar from accumulating state, and what makes rendering on several threads safe.

⚠ The staged table is still invisible to the SQL you generate. The generated statement is executed by the outer query, on its own connection — so the distinction is: your template may read staged data and inline the values (above), but the generated SQL cannot SELECT FROM the staged table. Stage for the template, not for the statement.

⚠ Nothing is opened unless the template actually calls query() or exec(), so a template that runs no SQL costs nothing.

⚠⚠ And staging into a temporary table is what makes this safe on the fluid_replacement_query surface. A writing template behind a view writes on every use (the table above) — but each bind gets its own connection and its own temporary catalog, so the same CREATE TEMP TABLE simply runs again. Measured, a view over such a template used twice:

CREATE VIEW v AS SELECT * FROM fluid_replacement_query(
  '{% assign _ = exec("CREATE TEMP TABLE st AS SELECT 5 AS a") %}'
  '{% assign r = query("SELECT a FROM st") %}'
  'SELECT {{ r[0].a }} AS staged');
SELECT staged FROM v;   -- 5
SELECT staged FROM v;   -- 5   (a REAL table fails here: Table with name "realst" already exists!)

So bind repetition, which is a footgun for a template that writes to your catalog, is a non-issue for one that stages into a temp table.

exec() gives a template no authority you did not already have — anyone who can call fluid_render can call fabricator_exec — and it reads and writes on its own connection, so the same committed-data rule as query() applies.

Liquid control flow ({% if %}, {% for %}) works, comparisons and arithmetic filters work on numbers from either kind of bag, and nested STRUCT/MAP/LIST members are reachable by name, by index and by .size. Parentheses group conditions — {% if (a or b) and c %} — which you will want, because Liquid has no operator precedence and evaluates strictly right to left: without them a or b and c means a or (b and c), and the grouped condition is not expressible at all. Fluid is secure-by-default: only the variables you pass are reachable, and the only registered filters and functions are the two SQL filters (sql, sql_ident) and query / exec above.

⚠ Dates and times. A DATE/TIMESTAMP/TIMESTAMPTZ renders in UTC (2026-09-01 00:00:00Z), and {{ d | date: '%Y-%m-%d' }} formats it. Two things to know:

• Fluid does not ORDER temporal values — {% if a > b %} between two dates is always false, so compare formatted strings instead: {% assign x = a | date: '%Y-%m-%d' %} … {% if x > y %}. ISO-8601 sorts lexicographically, so that is exact.
• {{ d | sql }} always emits a TIMESTAMPTZ literal (Fluid keeps one date type internally). With the session on UTC as recommended above that is unambiguous. In a session left on local time it is a trap: ::DATE, ::TIMESTAMP::DATE, date_trunc, strftime and extract all read it in the session's zone, so west of UTC they silently give the previous day. Either of these is safe in any session — {{ d | date: '%Y-%m-%d' | sql }}::DATE (no timezone in play at any step), or ({{ d | sql }} AT TIME ZONE 'UTC')::DATE (names the zone; also right for a TIMESTAMP).

A BLOB arrives as a lowercase hex string.

⚠ Numbers are DECIMAL inside a template. JSON integers and decimals are carried exactly, but a value outside DuckDB DECIMAL's range or below its resolution (roughly beyond ±7.9e28, or under ~1e-28) cannot be represented and is refused with an error naming the value — rather than rendering as 0 or failing deep inside the engine. Pass such a value as a string if you only need to print it. A DOUBLE with more than 15 significant digits renders rounded.

Reusing templates from files — {% include %} and {% render %}

A template can pull in another one from any storage the extension can read — a local directory, s3://, abfss://, onelake:// — so shared SQL fragments and macros live in files under version control instead of inside SQL string literals:

SET fluid_template_root = 's3://analytics/templates';

-- templates/dims/customer.liquid holds:  SELECT id, name FROM customers WHERE region = {{ region | sql }}
SELECT * FROM fluid_replacement_query('{% include ''dims/customer'' %}', params := {'region': 'eu'});

The included template shares the caller's variables, and it may include others in turn.

⚠ An absolute path needs no root at all — {% include 's3://bucket/templates/dims.liquid' %} works whether or not fluid_template_root is set, which is the escape when a process-wide setting is the wrong granularity. The root is a convenience for writing short names, not a sandbox: a template that can {% include %} is being rendered by someone who can already run SQL here, and query() can read any path the host can open. What is refused is refused for predictability — .. in a relative path, and the glob characters *, ?, [, ], because one include must name one file.

⚠ Use SET GLOBAL for the root if the template runs through fluid_query_batch or fluid_query_lateral. A plain session-scoped SET fluid_template_root reaches fluid_render and fluid_replacement_query, but those two resolve part of their work where the session-scoped value is not visible, and a relative include then fails with "no root is set" — at bind for the batch form, at call time for the lateral. SET GLOBAL works on every surface, and an absolute include path needs no root at all.

⚠ Name the extension. {% include 'dims/customer' %} asks storage for dims/customer and then for dims/customer.liquid — two round trips on remote storage where {% include 'dims/customer.liquid' %} costs one. If both files exist, the one without the extension wins.

The file is read the same way query() runs SQL — on its own connection — so a location authorised by a persistent secret works, while one authorised by a CREATE SECRET of the calling session does not. A template above 1 MiB is refused, and a missing include reports every path it asked for.

fluid_aggregate(template, params, value) renders a template once per group, with that group's rows in hand, reducing them to one value whose type the template declares:

CREATE TABLE t AS SELECT i % 3 AS g, i::BIGINT AS a, 'v' || i AS b FROM range(9) r(i);

SELECT g, fluid_aggregate(
  '{% if is_bind %}select NULL::VARCHAR
   {% else %}{% capture s %}{% for r in rows %}{{ r.a }}|{{ r.b }};{% endfor %}{% endcapture %}
   select md5({{ s | sql }}){% endif %}',
  NULL, struct_pack(a := a, b := b) ORDER BY a) AS h
FROM t GROUP BY g ORDER BY g;
-- 0 | 79172c4e532577b57f10d3513d40fe6a
-- 1 | 151b6c04cb18b03a93142cf2fd9bdf21
-- 2 | bbcbd31a7317cc77aa53255f9f088604

Like every other Fluid surface, the non-bind render is a SELECT that gets executed — the value is whatever DuckDB computes, so the reduction can use any DuckDB function and nothing is parsed back out of text. Every group must render one, including an empty group: {% if rows.size == 0 %}select NULL{% else %}…{% endif %}.

The group's rows are rows; template and params are bind-time constants. It works under GROUP BY and under OVER (...), including a moving frame:

SELECT a, fluid_aggregate('{% if is_bind %}select NULL::BIGINT{% else %}{{ rows.size }}{% endif %}', NULL, a)
  OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS n FROM t ORDER BY a;
-- 1, 2, 2, 2, …

⚠⚠ An aggregate is unordered, so write ORDER BY if the result depends on row order — as a hash does. fluid_aggregate(tpl, NULL, v ORDER BY k) reaches the template in that order; without it, parallel aggregation decides, and the value changes between runs with nothing failing.

Like fluid_scalar, the is_bind render declares the result type — and it is a real DuckDB type, so DECIMAL(9,2) keeps its scale and a STRUCT(n INTEGER, s VARCHAR) comes back as a struct, not text.

The group's rows are also a SQL relation called rows, so the reduction can happen in DuckDB rather than in Liquid — which is usually what you want:

SELECT g, fluid_aggregate('select max(t) from rows t',
                          NULL, struct_pack(a := a, b := b) ORDER BY a) AS h
FROM t GROUP BY g ORDER BY g;
-- 0 | {'a': 6, 'b': v6}   …

Note there is no is_bind branch: the result type is worked out by binding that same statement against an empty, fully typed rows, so a template only needs is_bind when its type cannot be derived from the input. A struct_pack(...) argument arrives as columns ({{ r.a }} in Liquid is a in SQL); any other argument is a single column named value.

⚠⚠ Prefer the SQL form for anything heavy. The md5 example above, written in Liquid and in SQL over rows, produces identical hashes — and over 2000 groups takes ~2.6 s in Liquid versus ~0.01 s in SQL. The SQL spelling is: select md5(string_agg(a || '|' || b, ';' ORDER BY a) || ';') from rows

⚠ One per-row argument. DuckDB does not allow a variadic aggregate here, so pass several columns as one struct_pack(a := a, b := b) and read r.a, r.b in the template.

⚠ It runs one statement per group, and holds each group's rows in memory — measured at ~0.5 ms per group for a hashing template, and it cannot spill. Both point the same way: a few thousand groups is fine, a hundred thousand is not what this is for.

⚠ Where the reduction is really "render per row, then join", prefer what already works: md5(string_agg(fluid_render(...), ';' ORDER BY a)) is cheaper. fluid_aggregate earns its keep when the template needs the whole group at once, or a result type that is not VARCHAR.

fluid_query_batch(template, <input> [, params := …] [, batchsize := …]) renders a template WITH A RELATION IN HAND and runs the statement it produces. Where fluid_replacement_query renders from constants at bind time, this renders from data at execution time — so the SQL text itself can depend on the rows.

CREATE TABLE orders  AS SELECT * FROM range(3);
CREATE TABLE returns AS SELECT * FROM range(1);
CREATE TABLE manifest AS SELECT unnest(['orders','returns']) AS tbl;

SELECT * FROM fluid_query_batch('
{% if is_bind %}SELECT NULL::VARCHAR AS src, 0::BIGINT AS n LIMIT 0
{% else %}
  {% query rows %}SELECT tbl FROM input_table ORDER BY tbl{% endquery %}
  {% for r in rows %}
    SELECT {{ r.tbl | sql }} AS src, count(*)::BIGINT AS n FROM {{ r.tbl | sql_ident }}
    {% unless forloop.last %} UNION ALL {% endunless %}
  {% endfor %}
{% endif %}', (SELECT tbl FROM manifest)) ORDER BY src;
-- src     | n
-- orders  | 3
-- returns | 1

The input arrives as a temporary table called input_table, on the template's own connection — read it with {% query %}, join it in the generated statement, or ignore it. Its columns are the input relation's own.

input_table is also a Liquid value, so the same rows are reachable either way under one name:

SELECT sql_n, fluid_n FROM fluid_query_batch(
  'SELECT (SELECT count(*) FROM input_table)::BIGINT AS sql_n, {{ input_table.size }}::BIGINT AS fluid_n',
  (SELECT i FROM range(5) t(i)), batchsize := 2);
-- 2  2
-- 2  2
-- 1  1

It is read lazily and cached per render: a template that only writes SQL over input_table pays nothing for this, and one that reads it in Liquid pays a single round trip. {% for r in input_table %} and {{ input_table.size }} see this group's rows (this chunk's, for fluid_query_lateral) — the SQL view and the Liquid value are repointed together. Your own {% assign input_table = … %} shadows the Liquid name; the SQL object is a separate namespace and keeps the rows.

fluid_query_inout(template, <input> [, params := …]) is the STREAMING sibling — the same body on the table-in-out exchange instead of the collector. It renders once per input chunk and emits that chunk's rows before reading the next, so only one chunk is held at a time. Reach for it when the input is large and each chunk can be handled on its own:

SELECT count(*) AS renders, sum(chunk_rows)::BIGINT AS rows
FROM fluid_query_inout(
  'SELECT (SELECT count(*) FROM input_table)::BIGINT AS chunk_rows',
  (SELECT * FROM range(5000) t(n)));
-- renders | rows
-- 3       | 5000        -- fluid_query_batch over the same input: 1 render, 5000 rows

⚠ There is no batchsize, and that is inherent rather than an omission. The streaming operator cannot hold output back until the input ends — its all-input-done hook is handed no data, so anything held back is discarded — so a render is exactly one DuckDB chunk and how the input divides into chunks is not yours to choose. If the statement must see the whole relation, or an exact row count per render, use fluid_query_batch, which buffers the input and can therefore group it.

⚠ An empty input renders nothing here, and once on fluid_query_batch. With no rows there are no chunks; the collector still renders once, because a template is a statement generator whose output need not depend on the rows. If you need the generator to fire regardless, use the collector.

Everything else matches: params, input_table as both a temp table and a Liquid value, is_bind, projection, the refusal of publish(), and SQL state carrying between renders while Liquid state does not.

⚠ It only computes the columns you select, as on the other two surfaces: the generated statement is narrowed to the columns your query actually reads, so an expensive expression in a column you did not select is never evaluated, and the template can read the list as projected (unset during the schema probe, so branch on is_bind).

is_bind is true for exactly one render: the one that determines the output columns. DuckDB needs a table function's schema before it runs anything, so the template is rendered once against an empty input_table and the resulting statement is bound to learn its shape. You do not have to branch — a template that simply reads input_table works, because an empty input still produces the right columns — but branching lets you skip expensive setup, and it is the idiom when the real body is costly. Whatever the branch renders is what gets bound: the columns are never taken on trust, and a later render that produces a different shape is refused rather than misread.

The output columns can depend on the INPUT columns, not just on params. input_table exists during the schema probe — empty, but with its real columns — so a template can inspect it and build its own SELECT list:

SELECT * FROM fluid_query_batch('
{% query c %}SELECT column_name FROM (DESCRIBE SELECT * FROM input_table){% endquery %}
SELECT {% for col in c %}{{ col.column_name }} AS out_{{ col.column_name }}{% unless forloop.last %}, {% endunless %}{% endfor %}
FROM input_table', (SELECT 7 AS gamma));
-- out_gamma
-- 7

The same template over a different input produces differently named columns. It works in fluid_query_lateral too, where the input columns are named after the expressions you passed.

Without batchsize the template is rendered ONCE for the whole input. With it, once per that many rows — exactly that many, not "at least":

SELECT n, lo, hi FROM fluid_query_batch(
  'SELECT count(*) AS n, min(i) AS lo, max(i) AS hi FROM input_table',
  (SELECT * FROM range(5) t(i)), batchsize := 2) ORDER BY lo;
-- 2 | 0 | 1
-- 2 | 2 | 3
-- 1 | 4 | 4

Groups run in order on one connection, so SQL state carries between them — a temp table one group stages is there for the next, which is how you accumulate across a large input:

SELECT groups_so_far, n FROM fluid_query_batch('
{% exec %}CREATE TEMP TABLE IF NOT EXISTS acc(n BIGINT){% endexec %}
{% exec %}INSERT INTO acc SELECT count(*) FROM input_table{% endexec %}
SELECT (SELECT count(*) FROM acc)::BIGINT AS groups_so_far, count(*)::BIGINT AS n FROM input_table',
  (SELECT * FROM range(5) t(i)), batchsize := 2) ORDER BY groups_so_far;
-- 1 | 2
-- 2 | 2
-- 3 | 1

⚠ Liquid variables do NOT carry between groups, only SQL state does. Fluid renders each group in its own scope, so a {% assign %} starts fresh every time. Accumulate in a temp table, as above.

⚠ It only computes the columns you select. As for fluid_query_lateral, the generated statement is narrowed to the columns your query actually reads, so an expensive expression in a column you did not select is never evaluated — and the template can read the list itself as projected (unset during the schema probe, so branch on is_bind).

⚠ The whole input is buffered before the first render, and batchsize does not change that — it controls how many rows each render sees, never how much memory the function uses. This is inherent: a function that may render once over everything cannot know it has everything until the input ends.

⚠ publish() is refused here, and unlike in fluid_replacement_query you do not need it: this function runs the generated statement on the template's own connection, so a table {% exec %} staged is simply in scope — SELECT * FROM my_staged_table. (publish exists to carry a relation to a different connection.)

⚠ Prefer fluid_replacement_query plus an ordinary join when the generated SQL does not depend on the data. This function does not disappear into the caller's plan the way fluid_replacement_query does, so its rows cross the managed boundary and it gets no pushdown. It earns its cost only when the SQL text must vary.

⚠ An input column whose name begins __fab is refused — the function needs that prefix for its own staging columns. Alias it in the input query.

fluid_query_lateral(template, params, <per-row columns…>) is the correlated sibling. Where fluid_query_batch takes a whole table, this takes ordinary columns, so it sits inside your query: the template is rendered once per chunk of input rows, its statement is run, and your own columns are stamped onto every output row.

CREATE TABLE people AS SELECT * FROM (VALUES (1, 'ab', 2), (2, 'cde', 0)) v(id, name, n);

SELECT p.id, f.*
FROM people p, fluid_query_lateral(
  'SELECT __fab_row, upper(name) AS u, length(name) AS n FROM input_table', 'null', p.name) f
ORDER BY p.id;
-- id | u   | n
-- 1  | AB  | 2
-- 2  | CDE | 3

The rows arrive as input_table again, on the template's own connection, and is_bind works exactly as above. Two things are different, and both are load-bearing:

⚠ The generated statement MUST project __fab_row. One render sees a whole chunk of input rows and may return any number of output rows, so the extension has to be told which input row each output row belongs to before it can stamp your columns onto it. input_table's first column is __fab_row — carry it through and it is removed from the result. Omitting it is refused when the query is bound, naming the column. That requirement is also what buys you fan-out and filtering:

-- one input row -> N output rows, or none at all
SELECT p.id, f.k
FROM people p, fluid_query_lateral(
  'SELECT i.__fab_row, r.range AS k FROM input_table i, range(i.n) r', 'null', p.n) f;
-- id | k
-- 1  | 0
-- 1  | 1        -- id 2 has n = 0, so it produces no rows at all

⚠ params is positional and cannot be NULL — write 'null' for "no bag" (or '{}' for an empty one; {% if params %} tells them apart). Both template and params are bind-time constants, which is forced: DuckDB drops named arguments in a correlated call, so params := … does not bind there. Any constant expression works — a literal, a cast, a struct such as {'mul': 10}, getvariable('x').

Naming. DuckDB names each input column after the expression you passed: p.name arrives as name, p.n + 1 as (p.n + 1), and a struct as main.struct_pack(...). In a call whose arguments are all literals there is no expression text at all, so they are named by argument POSITION — in fluid_query_lateral('…', 'null', 7, 'x') they are col2 and col3, since the two constants come first. If that is awkward, name them yourself with a positional alias, which needs no knowledge of any of the above:

SELECT p.id, f.s
FROM people p, fluid_query_lateral(
  'SELECT r AS __fab_row, a * b AS s FROM (SELECT * FROM input_table) AS q(r, a, b)',
  'null', p.id, p.n) f;
-- id | s
-- 1  | 2
-- 2  | 0

⚠ It only computes the columns you select. DuckDB tells the function which of its output columns your query actually reads, and the generated statement is narrowed to those — so an expensive expression in a column you did not select is never evaluated. Nothing to configure. A template can also read the list itself, as projected, to skip work that narrowing the SQL cannot reach (an {% exec %}, a {% query %}, a join it would otherwise write); it is unset during the schema probe, so branch on is_bind: {% if is_bind %}…{% else %}SELECT __fab_row, {{ projected | join: ", " }} FROM input_table{% endif %}.

⚠ Nothing carries between chunks here. The lateral runs in PARALLEL — each thread gets its own connection and its own temporary catalog — so a temp table one render stages may or may not be there for the next. Use fluid_query_batch when you need to accumulate; it is sequential by construction.

⚠ publish() is refused here too, for the same reason and with the same answer: select the staged table directly.

Which to reach for. fluid_replacement_query when the SQL does not depend on the data (it disappears into your plan, and nothing beats that). fluid_query_lateral when the statement is per row and the result belongs beside the outer row. fluid_query_batch when the template needs the whole relation, or needs to accumulate across it.

It is always available, in a released artifact and in a build from source alike. It used to ship as a bundled plugin, which had one sharp edge worth knowing about if you remember it that way: setting FABRICATOR_PLUGIN_DIR to pick up your own plugin replaces the default roots and therefore used to make these functions disappear. That cannot happen now — they are part of the extension, not of any plugin root.

⚠ ATTACH … (PROVIDER 'fluid') is refused, deliberately. Templates are global functions; there is no catalog to attach. Call them directly.

fabricator_install_plugin(archive [, root := …] [, replace := …]) -> TABLE

Unpack a plugin archive into a plugin root. The plugin's provider becomes usable immediately, in the same session; its global functions appear at the next start (see the note above — that is a DuckDB constraint, not a limitation of the installer).

-- Off by default: an installed plugin runs in-process with the extension's privileges.
SET fabricator_allow_plugin_install = true;

SELECT name, version, destination, files, providers, activated
FROM fabricator_install_plugin('/tmp/myplugin-1.0.0.zip');

The archive layout. A fabricator-plugin.json manifest at the root, plus an any/ directory (platform-independent) and/or one named for a DuckDB platform. The install is their merge, with the platform directory overlaying any/:

fabricator-plugin.json
any/MyPlugin.dll
windows_amd64/MyPlugin.Native.dll
linux_amd64/MyPlugin.Native.so

{
  "formatVersion": 1,
  "name": "myplugin",
  "version": "1.0.0",
  "entryAssembly": "MyPlugin.dll",
  "abstractionsVersion": "1.0.0"
}

A flat archive — assemblies at the root, no directories — is refused rather than guessed at: the alternative would be recognising a platform directory by its name, under which an archive shipping only linux_amd64/ would look flat on Windows and its Linux binaries would be installed.

Upgrades. The install directory is version-stamped, so a new version is written beside the running one and takes effect at the next start; a loaded assembly can never be replaced in place. replace := true re-installs the same version (it moves the old directory aside rather than deleting it, because a locked file can be renamed but not removed).

Remote URLs are refused — download the archive first.

A plugin may not take a provider name that is already registered. An IBackend declaring sqlserver, delta, dax or deltars is refused, and fabricator_plugins() reports it as rejected naming both what it claimed and who already had it. Registration is a plain overwrite underneath, so without the refusal such a plugin would silently replace the first-party provider and every later ATTACH would go somewhere you did not choose.

fabricator_uninstall_plugin(name [, version := …] [, root := …]) -> TABLE

Takes a plugin out of the scan, and reclaims its files when it can. One row per installed version; omitting version removes them all.

SET fabricator_allow_plugin_install = true;      -- the same switch gates uninstalling
SELECT name, version, removed, purged, detail FROM fabricator_uninstall_plugin('myplugin');

Why those are two columns. An assembly loaded from a file is locked on Windows and the extension's load context cannot be unloaded, so a plugin that has been used in this process cannot be deleted — but it can be renamed. Uninstalling moves the version directory into a hidden .trash folder under the plugin root, which takes it out of the scan at once; the bytes are swept on a later install or uninstall, by which time a restart has usually released the lock.

⚠ The provider stops resolving, but the code stays in memory until the process exits — nothing can unload it. The re-scan simply stops finding a candidate, so it is no longer registered.

fabricator_http_request(url [, method := …] [, headers := …] [, body := …]) -> TABLE

Make an HTTP request through DuckDB's own HTTP stack, so it picks up DuckDB's configuration rather than .NET's: the TYPE http secret whose scope covers the URL, ca_cert_file, enable_curl_server_cert_verification, http_proxy*, http_timeout and the retry settings.

CREATE SECRET api (TYPE http, BEARER_TOKEN 'eyJhbGciOi…', SCOPE 'https://api.example.com');

SELECT status, reason, body
FROM fabricator_http_request('https://api.example.com/v1/things');

Needs the httpfs extension — it is what gives DuckDB a TLS-capable HTTP client and the TYPE http secret reader. It is auto-loaded on the first request; if it is neither loaded nor installable you get a message telling you to INSTALL httpfs; LOAD httpfs; rather than a confusing scheme error.

Returns one row: status, reason, headers (a JSON object), body_bytes and body. An HTTP error status is a row, not an error — a 401 is something you look at — while a transport failure (DNS, connect, TLS) raises. body is NULL if the response is not valid UTF-8.

It exists mainly to make the transport observable: the same path is what a plugin uses for a REST API (DuckDbHttpHandler, an ordinary .NET HttpMessageHandler), and none of the inheritance above is visible from inside the plugin. Reach for it first when a plugin's calls come out unauthenticated.

Limits, all inherited from DuckDB's HTTP layer: methods are GET / PUT / HEAD / DELETE / POST only (a PATCH is refused by name rather than sent as something else); one value per header name in both directions, so repeated headers such as Set-Cookie cannot be carried; bodies are fully buffered, so a large result must be paged rather than streamed; redirects are followed; responses are not decompressed, so do not send an Accept-Encoding you cannot handle yourself.

⚠ TYPE http carries a static credential — BEARER_TOKEN or EXTRA_HTTP_HEADERS. It does not perform an OAuth2 client-credentials exchange, and CLIENT_ID/CLIENT_SECRET are not fields of it. An API using OAuth2 needs its own secret type and its own token exchange; the transport still gives it everything else.

⚠ It is unrestricted, deliberately, and worth knowing about. Anyone who can run SQL in this session can send any of the five methods to any URL, with whatever TYPE http secret matches it — including PUT, POST and DELETE. That is a step beyond what httpfs alone exposes (which reads URLs but does not offer arbitrary writes), though it is well within what this extension already permits: fabricator_exec runs arbitrary SQL on an attached server, and fabricator_install_plugin loads code into the process. There is no setting gating it today; say so if you want one.

fabricator_host_query(sql) -> TABLE

Run a query on DuckDB itself and stream the result back through the extension. Mostly an internal service — it is how the Delta provider reuses DuckDB's parquet reader — but it is callable, and useful for seeing what the bridge sees.

SELECT * FROM fabricator_host_query('SELECT 1 AS a, ''x'' AS b');

It runs on a fresh connection, which has two visible consequences. It inherits your session's active catalog/schema and TimeZone, so unqualified names and timestamps mean what they mean in your session:

USE lake.main;
SELECT * FROM fabricator_host_query('SELECT count(*) FROM t');   -- resolves lake.main.t

But it runs in its own transaction, so it sees only committed data — inside a BEGIN, your own uncommitted INSERTs are invisible to it. That is deliberate (reusing the in-flight connection would corrupt the outer query), not a bug to work around.

fabricator_host_exec(sql) -> TABLE(affected BIGINT)

The DDL/DML sibling. Same fresh connection and committed-reads semantics; one row, one column — the engine's affected-row count.

SELECT * FROM fabricator_host_exec('INSERT INTO t VALUES (1),(2),(3)');   -- affected = 3

Use it whenever you do not need a result set. fabricator_host_query has to describe your SQL to declare its output columns, which DuckDB cannot do for several statements in one string — so those run twice there, and a non-idempotent one fails outright. fabricator_host_exec declares a fixed schema, so it describes nothing and runs your SQL exactly once whatever it is:

-- fails through fabricator_host_query ("Table with name t already exists!"); works here
SELECT * FROM fabricator_host_exec('CREATE TABLE t AS SELECT 1 AS c; INSERT INTO t VALUES (2)');

The count comes from the statement itself, so a SELECT reports 0 rather than its first value, and a CREATE reports 0 even when it created rows. For several statements it is the last statement's count.

It is also available as a scalar, for symmetry with fabricator_exec:

SELECT fabricator_host_exec('INSERT INTO t VALUES (1),(2)');   -- 2

⚠ Prefer the table form for DDL. The scalar is evaluated per row, so SELECT fabricator_host_exec('…') FROM range(1000) runs your statement a thousand times; the table form runs it once per scan whatever the cardinality. (Both are safe from the other hazard — the scalar is VOLATILE, so an EXPLAIN does not execute it.) A NULL statement yields NULL, not 0.

fabricator_delta_scan(path) -> TABLE

Read a Delta table by path, with no ATTACH — the quickest way to look at one:

SELECT * FROM fabricator_delta_scan('s3://bucket/lake/trips') LIMIT 10;
SELECT * FROM fabricator_delta_scan('abfss://ws@onelake.dfs.fabric.microsoft.com/LH.Lakehouse/Tables/t');

Filesystem credentials come from DuckDB secrets exactly as for read_parquet. For a whole folder of tables, plus writes and DML, ATTACH the Delta provider instead.

fabricator_delta_native_scan(path) -> TABLE

The same read with DuckDB's own parquet reader instead of the C# one — same argument, same rows, so it is a drop-in swap when you want DuckDB's reader and its external file cache:

SELECT * FROM fabricator_delta_native_scan('abfss://ws@onelake.dfs.fabric.microsoft.com/LH.Lakehouse/Tables/t');

Both functions read the Delta log the same way — deletion vectors, partition columns, column mapping and schema evolution are all applied — and both prune columns: naming three columns of forty reads three, and COUNT(*) reads one. Filters are used to skip whole files and row groups (by partition value and by column statistics), and DuckDB re-applies the predicate above the scan, so the answer is exact either way. Which of the two is faster depends on the table and the storage; measure rather than assume.

Provider macros

The provider ships DuckDB macros — SQL templates expanded by the binder, so nothing crosses into C# when they run. They come in two flavours.

Global, under a bare name, available in every database with no ATTACH:

SELECT fabricator_bucket_of('alice', n := 16);          -- named parameter with a default
SELECT fabricator_rowid_parts(rid);                     -- scalar -> struct
SELECT * FROM fabricator_delta_head('/data/lake/t', n := 20);   -- a TABLE macro

Catalog-bound, resolved as db.schema.name(...), so two attached catalogs can expose differently shaped helpers under the same short name:

ATTACH '/data/lake' AS lake (TYPE fabricator, PROVIDER 'delta');
SELECT lake.main.fab_pct(25, 200);                 -- 12.5
SELECT lake.main.fab_clamp(150, hi := 120);        -- 120
SELECT * FROM lake.main.fab_numbers(5);            -- a TABLE macro

A catalog-bound macro is not callable under its bare name — that is the point of binding it to a schema. Both flavours appear in duckdb_functions() with function_type macro or table_macro.

One thing to know if you write your own: a schema gives namespacing, not name resolution. DuckDB expands a macro in the caller's context, so an unqualified table reference inside a macro body resolves against whatever catalog/schema the caller has active — not against the macro's own catalog. Keep bodies self-contained (expressions, or queries over built-ins), and use a SQL-generating table function when the body must reach into its own catalog's tables.

Relation zipping: table_zip / query_zip

Two global table macros that POSITIONAL JOIN several relations side by side — pandas.concat(axis=1) for DuckDB. They ship in Fabricator.Functions, whose bodies live as real .sql files under dotnet/Fabricator.Functions/Macros/.

CREATE TABLE za AS SELECT i AS id, 'a' || i AS v FROM range(3) r(i);
CREATE TABLE zb AS SELECT i * 10 AS id, 'b' || i AS v FROM range(3) r(i);

SELECT * FROM table_zip(['^z.$']);
-- ┌───────┬─────────┬───────┬─────────┐
-- │  id   │    v    │ id_1  │   v_1   │
-- │     0 │ a0      │     0 │ b0      │
-- │     1 │ a1      │    10 │ b1      │
-- │     2 │ a2      │    20 │ b2      │

Duplicate column names need no handling: the generated statement is bound as a subquery, so DuckDB uniquifies the names itself (id, id_1, …) — collision-safe even against a real column already named id_1.

table_zip(patterns [, similar_to := true] [, as_structs := false] [, rowid_begin := …] [, rowid_end := …])

Relations are ordered by position in the list, then by name within one pattern; a relation matching several patterns appears once, at its earliest position.

query_zip(queries [, as_structs := false]) is the same thing over subqueries. A LIST zips them anonymously; a MAP names them, and the name becomes a column prefix — or the struct name under as_structs:

SELECT * FROM query_zip(['SELECT 1 AS a', 'SELECT 2 AS b']);            -- a | b
SELECT * FROM query_zip({'x': 'SELECT 1 AS a', 'y': 'SELECT 2 AS b'});  -- x_a | y_b
SELECT * FROM query_zip({'x': 'SELECT 1 AS a'}, as_structs := true);    -- x  STRUCT(a INTEGER)

⚠⚠ A positional join pairs rows by POSITION, and it is your job to know that means something. There is no join condition, so the relations must be row-aligned — typically because they were produced together. Nothing checks this, and a mismatch is a wrong answer rather than an error.

⚠ rowid_begin / rowid_end are INCLUSIVE — BETWEEN semantics. rowid_begin := 3, rowid_end := 6 yields rowids 3, 4, 5 and 6, so adjacent chunks do not compose: the next range must start at 7, not reuse 6. The filter is applied to every relation, not one — a positional join has nothing for a predicate to propagate through.

⚠ A view works with rowid_* only if it exposes a rowid column (CREATE VIEW v AS SELECT rowid AS rowid, * FROM t); otherwise DuckDB says so by name.

⚠ Under similar_to := true the catalog is read on the render's own connection, so the caller's TEMP relations are invisible to the pattern match. Name them with similar_to := false, which renders them verbatim into a statement the caller binds.

Decision tables: decision_render

A decision table is a relation: one row per rule, columns split into inputs and outputs, plus three metadata rows. decision_render turns one into the SQL text of a DuckDB TABLE MACRO that evaluates it — DMN-like, with hit policies. It returns text and creates nothing, so you can read it before running it.

CREATE TABLE decision_rules (rulepos INT, region VARCHAR, age VARCHAR, amount VARCHAR,
                             decision VARCHAR, risk VARCHAR, label VARCHAR, Comment VARCHAR);
INSERT INTO decision_rules VALUES
    (-2, 'in',      'in',         'in',      'out',     'out',              'out',              NULL),
    (-1, 'VARCHAR', 'FLOAT',      'FLOAT',   'VARCHAR', 'DOUBLE',           'VARCHAR',          NULL),
    ( 0,  NULL,     'floor(?)',   '?',       NULL,      NULL,               NULL,               NULL),
    ( 1, 'EU, US',  '> 18',       '< 10000', 'approve', '@amount * 0.0001', '@region',          NULL),
    ( 2, 'US',      '[21 .. 65]', '> 5000',  'review',  '@age * 0.01',      '@age::varchar',    NULL),
    ( 3, 'not RU',  '',           '<= 1000', 'approve', '0.2',              'low',              NULL),
    ( 4, '',        '',           '> @age',  'flag',    '@amount / @age',   '@amount::varchar', NULL),
    ( 5, '',        '',           '',        'reject',  '1.0',              'default',          NULL);

-- Look at the generated macro ...
SELECT decision_render('decision_rules', name := 'evaluate_row');

-- ... then create it and evaluate.
SELECT fabricator_host_exec(decision_render('decision_rules', name := 'evaluate_row'));

SELECT rulepos, decision, risk, label FROM evaluate_row('JP', 40, 500);
--   3 | approve | 0.2 | low
SELECT rulepos, decision FROM evaluate_row('JP', 40, 500, hit_policy := 'RuleOrder');
--   3 | approve     4 | flag     5 | reject

Each cell is a small expression language:

Outputs are value expressions (approve, 42, @amount * 0.01, @age::varchar). The generated macro takes a hit_policy parameter — First (default), RuleOrder, Unique, Any — and returns _total_hits_, _any_hitpolicy_violations_, _model_error_ and a decisiontable_hk identity hash beside your outputs. When an output's rulepos = 0 expression contains an aggregate (max(?), arg_max(?,risk), EXP(SUM(LOG(?)))), the table switches to aggregate mode: every matching rule collapses into one row and the default policy becomes RuleOrder.

Where the rules come from. decision_render accepts four forms and picks between the first three itself:

SELECT decision_render('decision_rules');                  -- a relation name, or db.main.rules
SELECT decision_render('/data/rules.csv');                 -- a file: csv, parquet, json, ...
SELECT decision_render('[{"rulepos":-2,"region":"in"}]');  -- JSON text (array of objects)
SELECT decision_render('read_xlsx(''r.xlsx'')', source := 'sql');   -- any relation expression

source := 'auto'|'table'|'file'|'json'|'sql' overrides the choice; hit_policy := changes the default baked into the generated macro. dialect := selects the target — the cell parser already answers for tsql, but only the duckdb statement template exists, so decision_render(..., dialect := 'tsql') is refused by name rather than handing back a DuckDB macro that pretends to be T-SQL.

The parser is also callable on its own, which is the quickest way to see what a cell means:

SELECT dmn_condition('[21 .. 65]', 'age', 'duckdb', '');   -- age >= 21 AND age <= 65
SELECT dmn_value('@amount * 0.01', 'duckdb', '');          -- amount * 0.01
SELECT dmn_is_aggregate('EXP(SUM(LOG(?)))');               -- true
SELECT dmn_relation('rules.csv', 'auto');                  -- read_csv('rules.csv', all_varchar = true)

⚠⚠ A decision table is CODE, not data. An expression cell is passed through into the generated SQL, so a rule may call any function the engine has — that is the feature, and it means the rules relation is a trusted authoring surface, exactly like a template. Never build one from user input.

⚠ The column is implicit on the left of a cell — > 18 means age > 18, and so do IS NULL, LIKE 'E%', between 1 and 5 and in (1,2). Writing it out explicitly (@age IS NULL) means the same thing, which is useful when the condition compares two columns.

⚠ A cell is recognised as a condition — rather than a value to compare against — when it carries a comparison operator or one of BETWEEN, LIKE, ILIKE, GLOB, SIMILAR TO, REGEXP, IN, IS [NOT] NULL, IS [NOT] DISTINCT FROM. The list is a union across dialects and is a detector, not a translator: a keyword the target lacks just means the cell passes through and the target complains. The cost is that a bare data value containing one of those words is read as an operator and refused at CREATE time — quote the cell ('MADE IN USA') to force it back to a literal.

⚠ A decision column becomes a macro parameter, so its name must be a bare identifier. decision_render

refuses by name otherwise rather than emitting a CREATE MACRO that cannot parse.

⚠ Rules held in a TEMP table are invisible: the introspection runs on the render's own connection. Use a regular table, a file, or JSON.

⚠ source := 'table' still reads a dotted name as qualified — rules.csv becomes "rules"."csv". A relation literally named rules.csv needs source := 'sql' with the name pre-quoted.

⚠ Rendering costs three small queries and DuckDB repeats binds, so do not put a decision_render behind a view and expect it to run once. Render once, create the macro, then call the macro.

Provider views

A provider can also ship views bound into an attached catalog's schemas. Unlike a macro, a view is a relation: it shows up in duckdb_views() and duckdb_columns(), tools that enumerate the catalog find it, and it can be used anywhere a table can.

ATTACH '/data/lake' AS lake (TYPE fabricator, PROVIDER 'delta');
SELECT * FROM lake.main.fab_view_info;             -- a declared view
SELECT view_name FROM duckdb_views() WHERE database_name = 'lake';

This is the form to use when the body must read the catalog's own tables. DuckDB binds a view body against the view's catalog and schema, so an unqualified table reference inside it resolves there — which is exactly what the macro note above says a macro body cannot rely on. Nothing is bound until the view is first used, so a body may reference a table that does not exist yet; a missing one is an ordinary binder error at that point, and listing views never resolves anything.

A declared view cannot share a name with a table in the same schema. Both would resolve through the same catalog lookup, so rather than silently prefer one, that name is refused with an error naming both sides — every other view and table in the catalog keeps working.

SQL-generating table functions

Some provider functions rewrite themselves into SQL at bind time instead of returning rows over the bridge: the call disappears from the plan and what executes is a plain DuckDB query. Nothing crosses the bridge at execution, and the generated SQL keeps its own pushdown. They are used where the SQL text has to depend on the arguments (object names, UNION fan-out, a bind-time metadata lookup) — which a macro cannot do, since a macro substitutes arguments as expressions with its structure fixed at declaration time.

They are called like any table function, and fabricator_functions('db') reports them as kind='table_sql'. Because the rewrite happens at bind, EXPLAIN shows the generated plan rather than a function call.

Microsoft Fabric platform functions (OneLake and Fabric SQL attaches)

When a catalog is attached over OneLake (Delta) or to a Fabric SQL endpoint (T-SQL), it additionally hosts functions that call the Fabric REST API, so platform operations Microsoft offers no T-SQL for can be driven from SQL. They inherit the workspace, the item and the credential from the ATTACH — which is why most of them take no arguments at all.

They live in a dedicated fabric schema on the attached catalog: <catalog>.fabric.<name>.

ATTACH 'abfss://MyWS@onelake.dfs.fabric.microsoft.com/MyLH.Lakehouse/Tables' AS lake
  (TYPE fabricator, PROVIDER 'delta', SECRET fabric_sp, READ_ONLY false);

SELECT table_name, status FROM lake.fabric.refresh_sql_endpoint();
SELECT * FROM lake.fabric.sessions();

-- What this attach resolved to (root, engine defaults, whether it is OneLake). Note this one is NOT
-- a Fabric REST function — it describes the Delta catalog itself, so it lives in the data schema:
SELECT * FROM lake.dbo.fab_delta_info();

That schema is a function namespace, not storage: it holds no tables, is backed by no folder, and CREATE TABLE <catalog>.fabric.t is refused rather than quietly creating a fabric/ directory next to your data. It appears beside the data schemas (dbo, dbt, …) and only where the functions themselves are registered.

They are registered only where there is a Fabric platform to talk to — a OneLake root, or a SQL attach whose server is *.fabric.microsoft.com. A local or S3 Delta attach, or a plain SQL Server, has no workspace and no REST endpoint, so it has no fabric schema at all.

On a Fabric SQL attach (Warehouse or Lakehouse SQL endpoint)

Usually nothing extra is needed — the workspace and item are derived from the connection string itself:

CREATE SECRET fabric_sp (TYPE azure, PROVIDER service_principal,
                         TENANT_ID '…', CLIENT_ID '…', CLIENT_SECRET '…');

ATTACH 'Server=<endpoint>.datawarehouse.fabric.microsoft.com;Database=MyLH' AS w
  (TYPE fabricator, SECRET fabric_sp);

SELECT table_name, status FROM w.fabric.refresh_sql_endpoint();

The item comes from Database (on a Fabric SQL endpoint the database is the lakehouse or warehouse), and the workspace is decoded from the endpoint host, which encodes it. Override either only when you want the functions to act on something other than what you attached:

These options scope the FUNCTIONS, not the attach — hence the API_ prefix. They do not change what you queried: with Database=MyLH, API_ITEM 'OtherLH', w.dbo.orders still reads MyLH while w.fabric.refresh_sql_endpoint() syncs OtherLH. That is the point — it lets a dbt project attached to a Fabric Warehouse refresh a lakehouse endpoint without a second, Delta-only attach.

The earlier names WORKSPACE / ITEM are rejected with a migration message rather than ignored, because silently dropping them would retarget the functions at the Database default without saying so.

On a OneLake (Delta) attach neither option exists or is needed — the root names both: abfss://<workspace>@onelake.dfs.fabric.microsoft.com/<item>.Lakehouse/Tables. Both segments accept a GUID as well as a name, and a GUID costs no lookup:

ATTACH 'abfss://<workspaceId>@onelake.dfs.fabric.microsoft.com/<itemId>/Tables' AS lake
  (TYPE fabricator, PROVIDER 'delta', SECRET fabric_sp, READ_ONLY false);

Credential: use a service-principal or managed-identity secret. A pre-minted ACCESS_TOKEN secret authenticates SQL but cannot be reused for the Fabric API — the two need different token audiences — so with one of those the functions fall back to the ambient Azure credential chain (which is what works on Fabric compute, and via az login / environment variables off it). On a TYPE mssql secret using authentication = 'Active Directory Service Principal', also set azure_tenant_id: SqlClient infers the tenant from the server, but minting a Fabric token cannot.

Everything is optional except where noted, because the attach supplies the defaults. Where a function accepts workspace := / item :=, those OVERRIDE the attached ones — so one attach can drive several lakehouses, which is the common case for a dbt project that writes to more than one:

SELECT * FROM lake.fabric.refresh_sql_endpoint();                  -- the attached lakehouse
SELECT * FROM lake.fabric.refresh_sql_endpoint(item := 'OtherLH'); -- a different one