Queries

SQL runs on DuckDB over your datasets. A dataset is referenced by its handle, quoted: '<project_slug>.<dataset_slug>'. Two ways to use it:

a statement is run and forgotten: from the library, over HTTP, or from an agent’s query tool;
a query is kept on the platform and versioned, and, wired to a SQL transform, produces a dataset with its place in the lineage.

Statements

result = client.statements.create(
    statement="SELECT * FROM 'flights.flight-data' LIMIT 10",
    format="json",
)
for row in result["data"]:
    print(row)

A statement is a single SELECT. It may JOIN several datasets, as long as they belong to the same organization. A tabular dataset is read as its table; a file dataset is read as the Parquet files it holds, so a file dataset of CSVs answers 403 Cannot query this dataset.

Formats

`format`	Returns
`"json"` (default in the library)	`{"object": "list", "data": [...]}`
`"csv"`	raw CSV bytes
`"pyarrow"` (default over HTTP)	an Arrow IPC file, as bytes

Arrow is the efficient one for anything wide or long: JSON repeats every column name on every row.

import pyarrow as pa

raw = client.statements.create(statement="SELECT * FROM 'flights.flight-data'", format="pyarrow")
table = pa.ipc.open_file(pa.BufferReader(raw)).read_all()
df = table.to_pandas()

Limits and timeouts

Without a LIMIT of its own, a result is capped at 1000 rows unless limit= says otherwise. The cut is reported: truncated and limit in the JSON body, X-Gwenlake-Truncated and X-Gwenlake-Row-Limit as headers whatever the format. A client reading a whole table should page with LIMIT/OFFSET, which is what the transforms layer does.

result = client.statements.create(
    statement="SELECT * FROM 'flights.flight-data' WHERE carrier = 'AF'",
    limit=50_000,
    timeout=300,          # seconds, for a long scan
)

timeout= overrides the client’s default for this call. A scan over a large dataset can take far longer to answer than an ordinary request: the engine reads every file behind it before the first byte comes back.

Read a result whole, export it

A statement without a LIMIT is capped at 1000 rows. To read a result whole, the library pages it, wrapping your statement in LIMIT/OFFSET pages until one comes back short, and gives it back as Arrow, as a DataFrame, or written to a file whose format follows its extension:

table = client.statements.read_all("SELECT * FROM 'fleetsight.aisdk_positions' WHERE zone = 'brest'")
df = client.statements.to_dataframe("SELECT * FROM 'fleetsight.aisdk_vessels'")

client.statements.to_file("SELECT * FROM 'fleetsight.aisdk_positions'", "positions.parquet")
client.statements.to_file("SELECT mmsi, name FROM 'fleetsight.aisdk_vessels'", "vessels.csv")
client.statements.to_file("SELECT * FROM 'fleetsight.aisdk_vessels'", "vessels.jsonl")    # or .json, one array

for page in client.statements.iter_arrow("SELECT * FROM 'fleetsight.aisdk_positions'", page_rows=100_000):
    ...                                                                            # pyarrow.Table, page by page

to_file streams page by page, so a result larger than memory is fine; it returns the rows written. A statement with its own LIMIT is read in one page, as is. The same from the terminal:

gwenlake sql "SELECT * FROM 'fleetsight.aisdk_vessels'" --out vessels.parquet
gwenlake datasets export fleetsight.aisdk_positions --out positions.csv --where "zone = 'brest'"

-o is the short form of --out, and -o - writes to stdout, which lets a result travel through a pipe or ssh. Give --format then, since there is no extension to read it from; only the data goes to stdout, the row count goes to stderr. In Python, to_file takes an open binary stream the same way, with format=.

ssh server sh <<'EOF' > runs.parquet
gwenlake sql "SELECT * FROM 'fleetsight.aisdk_ingestion_runs'" --profile fleetsight -o - --format parquet
EOF

Branches and snapshots

Two optional suffixes on a handle:

Handle	Reads
`'p.ds'`	the dataset, on the trunk
`'p.ds:experiment'`	the dataset on a data branch; an unknown branch is a `400`
`'p.ds@123'`	a tabular dataset as it was at snapshot `123`
`'p.ds@123..456'`	the rows appended to a tabular dataset between two snapshots

The snapshot forms exist for incremental transforms, which compose them from what a build pins; nothing in an ordinary statement needs them. A snapshot suffix on a file dataset is a 400, and so is one with no append-only history between its two ends.

Over HTTP

curl https://api.gwenlake.com/v1/sql/statements \
  -H "Authorization: Bearer $GWENLAKE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"statement": "SELECT count(*) AS n FROM '"'"'flights.flight-data'"'"'", "format": "json"}'

See the statements endpoint. Statements run against datasets only; querying a connection’s own engine (a PostgreSQL database, for instance) is done from the Atlas interface.

Stored queries

A query is a SQL text stored on the platform and versioned: every save keeps the previous text, and a branch may carry its own copy. Wired to a SQL transform, it produces a dataset, with its inputs derived from the FROM and JOIN references of the text. Three ways to get one: write it in the SQL editor of the Atlas interface, POST /sql/queries with the text, or commit a .sql file in a repository the platform scans, with the dataset it produces in a header comment.

From a repository

orders_by_day.sql

-- @output: sales.orders_by_day
-- @input orders: sales.orders

SELECT CAST(placed_at AS DATE) AS day, count(*) AS n, sum(amount) AS total
FROM 'sales.orders'
GROUP BY 1

A scan registers the query with the file’s text and a SQL transform writing sales.orders_by_day from sales.orders; a later commit re-versions the text. The file’s @input headers are informative: the inputs are derived from the references in the SQL itself.

Over the API

curl -X POST https://api.gwenlake.com/v1/sql/queries \
  -H "Authorization: Bearer $GWENLAKE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"name": "Orders by day", "project_id": "res.project.…",
       "sql": "SELECT CAST(placed_at AS DATE) AS day, count(*) AS n FROM '"'"'sales.orders'"'"' GROUP BY 1"}'

Call	Does
`GET /sql/queries`, `GET /sql/queries/{id}`	list (without the text), read one with its text
`PATCH /sql/queries/{id}`	a new version of the text, or a rename; the inputs of every transform wired to it are re-derived
`GET .../versions`, `GET .../versions/{version}`	the history, and one past text
`POST .../execute`	runs the transform wired to the query, as a build of its output
`DELETE /sql/queries/{id}`	refused (`409`) while a transform references it

?branch= on the reads and the PATCH addresses a data branch: a branch save creates the branch’s copy and leaves the trunk’s text alone; a build on that branch runs the branch’s text.

Wiring it to a dataset

A SQL transform names the query and the output; the inputs come from the text.

curl -X POST https://api.gwenlake.com/v1/transforms \
  -H "Authorization: Bearer $GWENLAKE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"name": "orders_by_day", "project_id": "res.project.…", "type": "sql",
       "output": "res.dataset.…", "config": {"query_id": "res.sql-query.…"}}'

Rules the platform enforces at save time, with a 400 naming the reference:

every FROM/JOIN reference is a dataset of the same project;
no :branch and no @snapshot suffix: a SQL transform reads its inputs whole, on the branch of the build;
the output is a tabular dataset where the organization supports them, and is produced by this transform alone (409 for a second producer).

A build of the output runs the query with each input as a view named by the dataset’s slug and writes the result as a snapshot of the output, on the build’s branch. The query text is pinned when the build is created, so a build runs the text it was triggered with. The output is a dataset like any other: query it with a statement, bind it as an Input of a transform, or let the workflow rebuild it whenever its inputs change. Statements (client.statements.create) are the library’s SQL surface.

These endpoints are served by the platform and used by the Atlas interface; they are being opened on the public gateway progressively, and the Python library does not wrap them yet. The API reference lists what the gateway exposes today.

Get started

Concepts

Workspace

Data

Code

Build

Models

Inference

Agents

Client

Changelog

Statements

Formats

Limits and timeouts

Read a result whole, export it

Branches and snapshots

Over HTTP

Stored queries

From a repository

Over the API

Wiring it to a dataset

​Statements

​Formats

​Limits and timeouts

​Read a result whole, export it

​Branches and snapshots

​Over HTTP

​Stored queries

​From a repository

​Over the API

​Wiring it to a dataset

Statements

Formats

Limits and timeouts

Read a result whole, export it

Branches and snapshots

Over HTTP

Stored queries

From a repository

Over the API

Wiring it to a dataset