Projects

SQLQuack needs to know a table’s columns and their Snowflake types to convert a query that reads it. You declare them once in a project folder, and every command run there reads them.

A project is a folder holding a sources/ folder of YAML files. Here is one:

sources/shop.yml

sources:
  - name: orders
    columns:
      - name: order_id
        type: NUMBER(38,0)
      - name: status
        type: VARCHAR
      - name: total
        type: NUMBER(12,2)

Run convert in the folder and the query’s table is known:

$ sqlquack convert revenue.sql
sqlquack convert: converting…
SELECT
    "ORDERS"."STATUS" AS "STATUS",
    CAST(sum("ORDERS"."TOTAL") AS DECIMAL(24, 2)) AS "REVENUE"
FROM "ORDERS" AS "ORDERS"
GROUP BY "ORDERS"."STATUS"
sqlquack convert: done, converted

convert, run, census, index and function all read the project in the current folder. To use one elsewhere, pass --project path/to/folder.

Source files

Every .yml or .yaml file under sources/ is read. Each source has a name and its columns, each with a name and a type. Other keys, such as description, are ignored, so a SQLBuild project’s source files work unchanged.

A source with an expression is a query rather than a table, and is skipped.

A source with a column that has no type, or a type SQLQuack can’t read, is left out whole, so a SELECT * never misses a column. SQLQuack warns about it:

sqlquack convert: warning: ./sources/more.yml: the source customers is left out, because its column email has no type

A table declared in two files is an error naming both.

Naming tables

Names resolve as Snowflake resolves them. An unquoted name is upper-cased, so orders declares ORDERS and FROM orders reads it. A name in double quotes keeps its case, so '"orders"' declares a different table from ORDERS. When a name in the SQL matches a declared one only ignoring case, the diagnostic says so and suggests the spelling to declare.

database and schema qualify a table:

sources:
  - name: orders
    database: analytics
    schema: sales
    columns:
      - name: order_id
        type: NUMBER(38,0)

This declares ANALYTICS.SALES.ORDERS. A name in the SQL reads the declared table whose parts agree with it:

Declared	SQL	Reads
ORDERS	orders, sales.orders, analytics.sales.orders	ORDERS
ANALYTICS.SALES.ORDERS	orders, sales.orders, Analytics.Sales.Orders	ANALYTICS.SALES.ORDERS
ANALYTICS.SALES.ORDERS and ANALYTICS.ARCHIVE.ORDERS	sales.orders	ANALYTICS.SALES.ORDERS
ANALYTICS.SALES.ORDERS and ANALYTICS.ARCHIVE.ORDERS	orders	refused: it matches both

The session’s DATABASE and SCHEMA complete a name written without them, as in Snowflake: with --session SCHEMA=archive, orders reads ANALYTICS.ARCHIVE.ORDERS. See Sessions.

These are refused with SQ0004, naming the tables involved:

• A name matching several declared tables that no session default settles.
• A qualified name whose declared table a CTE of the same name hides.
• Two differently qualified names in one statement that read the same declared table. Declare each under its qualified name.

The converted SQL reads a qualified table from the database you attach to DuckDB under that name, such as ATTACH 'analytics.duckdb' AS analytics.

Column details

Two more column keys tell SQLQuack what a column’s values hold:

      - name: placed_at
        type: TIMESTAMP_TZ(9)
        offset: "+00:00"
      - name: payload
        type: VARIANT
        typed_leaves: true

offset says every value of a TIMESTAMP_TZ column has that offset, +00:00 or UTC, so conversion can rely on it, in strict mode too, as a fact the schema states. typed_leaves says a VARIANT column holds typed values, such as dates, beside JSON.

Choosing the folders

Put a sqlquack.toml in the project to keep the sources somewhere else:

[project]
sources = "warehouse/tables"

Paths are relative to the project folder, and a folder you name must exist. functions and overrides work the same way.

A sqlquack.toml holding only [project] keeps the defaults. To change them, add [allow] or [replace] to the same file.

Functions

Your own SQL functions go in functions/sql/, one file each, named after the function. The file starts with a header giving the arguments and the return type, then the body:

-- functions/sql/is_shipped.sql
FUNCTION (
  arguments (order_status VARCHAR),
  returns BOOLEAN,
);

order_status = 'shipped'

Now a query can call is_shipped(status). returns table (name TYPE, ...) declares a table function. volatility (volatile or immutable) and null_input (called or returns_null) describe how calls behave; both are optional.

Python functions go in functions/python/, marked with @udf:

functions/python/label.py

from sqlquack import udf


@udf(arguments={"status": "VARCHAR"}, returns="VARCHAR")
def main(status):
    return None if status is None else status.title()

SQLQuack reads the decorator without running the file. Besides SQLBuild’s keys, @udf takes volatility, null_input and vectorized=True. A Python function only runs where a Python runtime registers it, so the defaults refuse calls to it. Where one does, say so once:

[project]
python_runtime = true

Overrides

Files under overrides/ replace a Snowflake built-in with your own DuckDB SQL. See convert.

Session settings

[project.session] holds your Snowflake session parameters, so you don’t pass --session every time:

[project.session]
TIMEZONE = "UTC"

A --session flag replaces the project’s value for one run.