Write query files

Each published DuckDB table comes from one checked-in SQL file. Skiff parses that file before connecting and refuses query shapes that can write data or escape the configured database.

Map one file to one table

[[queries]]
source = "reporting"
file = "queries/incidents.sql"
table = "incidents"

The source must already exist in [[sources]]. Output table names must be unique, are matched case-insensitively, and may contain only letters, digits, and underscores.

Write ordinary SQL Server SQL in the referenced file:

SELECT
    i.id,
    i.opened_at,
    i.priority
FROM dbo.incidents AS i
WHERE i.cancelled = 0;

Name output columns explicitly. Stable names keep the DuckDB contract readable and make schema-drift reports useful.

Accepted query boundary

Skiff requires exactly one parsed query statement. It accepts read-only query bodies, including CTEs, subqueries, and set operations.

Skiff rejects:

• multiple statements in one file;
• INSERT, UPDATE, DELETE, DDL, and other non-query statements;
• SELECT INTO;
• three- or four-part relation names that reach another database or linked server;
• more than one result set;
• a result with no output columns.

The configured SQL login remains the final authority over which objects the query can read.

Row and time limits

Every query uses the project-wide [limits]. If a result exceeds max_rows or timeout_secs, the refresh fails and no new DuckDB is published. Set max_rows = -1 only when a reviewed query is intentionally unbounded; the timeout remains active.

Limits apply independently to each query. They are safety ceilings, not pagination; narrow the SQL or increase the reviewed project limit when a legitimate result is larger.

Result columns

Unnamed output columns receive names such as column_1. Duplicate names receive _2, _3, and later suffixes. Prefer deliberate aliases so consumers do not depend on generated names.

Skiff maps SQL Server values into these DuckDB families:

Decimal and numeric values are exact through scale 9. Values with a larger scale are rounded when cast to DECIMAL(38,9). SQL Server money is recovered at scale 4 within the driver's safe numeric range.

Add a fixed check

Checks are DuckDB SQL, not source SQL. They run after all tables have loaded into the private generation:

[[checks]]
name = "incident ids are unique"
file = "checks/incident_ids.sql"

SELECT id
FROM incidents
GROUP BY id
HAVING count(*) > 1;

A check passes only when it returns zero rows. Use checks for invariants that must hold across tables or sources before readers see the new file.

For large append-only tables, continue with incremental loads.