Zax SQL Beta

Zax SQL exposes your Arraylake data as ordinary SQL tables, served over the Postgres wire protocol and over Arrow Flight SQL. You query it with a Postgres driver or an Arrow Flight SQL client.

Speaking one of those protocols is not on its own enough for a client to work. Each one sends its own set of queries to initialize a session, and that is where clients differ from each other — so treat Tested Zax SQL clients as the list of what works, rather than assuming any Postgres-compatible tool will.

Nothing is copied or converted first. Queries read the Zarr chunks in your Icechunk repo directly, and a query that selects a small region of a large dataset reads only the chunks covering that region. See Filter pushdown for how that works and how to write queries that benefit from it.

Data Model

A dataset and a SQL table describe data in two different shapes. A Zarr group is a set of named variables over shared, named dimensions; a table is a set of rows. The SQL service serves the former as the latter by applying one mapping.

• Every variable becomes one column. Coordinate variables and data variables are not distinguished — time, latitude and temperature are all just variables, and all three become columns.
• The table has one row per point on the grid. The row count is the product of every dimension length in the group. A group with dimensions {time: 100, lat: 180, lon: 360} is a table of 6,480,000 rows.
• Lower-dimensional variables are broadcast across the dimensions they lack. latitude(lat) repeats for every time and lon; temperature(time, lat, lon) is already full length.

This is the same table you would get from xarray.Dataset.to_dataframe(), and you can use that locally to see exactly what a group will look like in SQL:

import xarray as xr

ds = xr.Dataset(
    {"foo": (("y", "x"), [[0, 1, 2], [3, 4, 5]])},
    coords={"x": ["a", "b", "c"], "y": [10, 20]},
)

ds.to_dataframe().reset_index()
#     y  x  foo
# 0  10  a    0
# 1  10  b    1
# 2  10  c    2
# 3  20  a    3
# 4  20  b    4
# 5  20  c    5

The difference is that to_dataframe() materializes every value in memory and broadcasts the short columns eagerly, which is not an option for a dataset of any real size. The service broadcasts lazily instead, which is what makes the mapping practical — and what makes filter pushdown possible.

info

The mapping runs one way only. It takes a dataset to a table, not a table back to a dataset, so a query result is a table and stays one. There is no SQL you can write that returns a Zarr group.

Each variable's Zarr dtype determines the type of its column:

Zarr dtype	SQL / Arrow type
int8, int16, int32, int64	Int8, Int16, Int32, Int64
uint8, uint16, uint32, uint64	UInt8, UInt16, UInt32, UInt64
float32, float64	Float32, Float64
datetime64	Timestamp (nanoseconds, no timezone)
timedelta64	Duration (nanoseconds)
Fixed-length Unicode	Utf8

Every column is nullable.

Managing your Zax SQL service

Zax SQL is a Compute service and is managed like any other. It is not enabled automatically:

al compute enable {org} sql

By default the service is reachable only by those who can already read the repo being queried. Pass --is-public to serve it without authentication. See Authentication for what that means in practice.

To see what is running, and to turn a service off:

al compute list {org}
al compute disable {org} {service_name}

A Zax SQL service runs on a single node, not on a scalable cluster like the other Compute protocols, so there is no replica range to size. It draws on the same pool of credits as every other service.

Credits are consumed for as long as the service is up — not per query, and not per connected client. Disable a service you are not using.

For logs and per-service metrics, see Compute Administration and Logs.

Connecting to your Zax SQL service

The service answers on two protocols. Both need the hostname from al compute list {org}, shown below as {host}.

Your password is an Arraylake API token. The username is not checked — the server asks the client for a cleartext password and validates that against Arraylake — but clients insist on one, so use arraylake. Generate a token from the Access → API Clients page of your organization in the web app.

Postgres

Field	Value
Host	{host}
Port	5432
Database	your organization name
Username	arraylake
Password	your Arraylake API token
SSL mode	require

psql "postgresql://arraylake:{token}@{host}:5432/{org}?sslmode=require"

Flight SQL

Field	Value
URI	grpc+tls://{host}:32010
Authorization	Bearer {token}

# pip install adbc-driver-flightsql pyarrow
from adbc_driver_flightsql import DatabaseOptions, dbapi

with dbapi.connect(
    "grpc+tls://{host}:32010",
    db_kwargs={DatabaseOptions.AUTHORIZATION_HEADER.value: "Bearer {token}"},
) as conn:
    with conn.cursor() as cur:
        cur.execute("""SELECT * FROM "my-repo"."root" LIMIT 10""")
        result = cur.fetch_arrow_table()

print(result.to_pandas())

The organization's Zax SQL service page in the web app shows these fields filled in for your own deployment, with the table names of the repo you are looking at.

Tested Zax SQL clients

Every client sends its own set of queries when it opens a session, so Postgres-compatibility alone does not predict whether one works. These are the clients we test against for Zax SQL:

Client	Protocol	Tested
psql	Postgres	✓
psycopg2, psycopg3	Postgres	✓
SQLAlchemy	Postgres	✓
asyncpg	Postgres	✓
pandas	Postgres	✓
polars	Postgres	✓
pgjdbc	Postgres	✓
pgx	Postgres	✓
node-postgres	Postgres	✓
postgres.js	Postgres	✓
Npgsql 4.0.17 — the version Power BI ships	Postgres	✓
ADBC Flight SQL (Python)	Flight SQL	✓
Go ADBC	Flight SQL	✓

A client that is not on this list is untested rather than known to work. If you need one that is missing, tell us — support is added client by client and the list moves.

Using SQL

Accessing Data

Mapping Repos to Tables

The dataset hierarchy maps onto the SQL namespace:

Arraylake	SQL
Organization	database / catalog
Repository	schema
Dataset group	table — the root group is named root
Variable	column

One table is one group on one grid. Groups whose arrays do not share a consistent set of dimensions cannot be broadcast to a single row count, and are left out of the listing rather than served as a broken table.

Repository and group names are frequently not valid bare SQL identifiers — a dash, an uppercase letter or a / in a nested group path all need quoting — so quote them:

SELECT * FROM "my-repo"."surface/hourly" LIMIT 10;

Querying a specific version

An unqualified table reads the tip of the repo's main branch. An AT(...) clause after the table name reads something else instead. There are four keywords, and every value is a single-quoted string literal:

SELECT * FROM "my-repo"."root" AT(BRANCH => 'dev') LIMIT 10;
SELECT * FROM "my-repo"."root" AT(TAG => 'v1.0') LIMIT 10;
SELECT * FROM "my-repo"."root" AT(SNAPSHOT => 'K0RSJ5F1XCRJP8CVXY0G') LIMIT 10;
SELECT * FROM "my-repo"."root" AT(TIMESTAMP => '2026-01-01') LIMIT 10;

Keyword	Value
BRANCH	branch name
TAG	tag name
SNAPSHOT	a snapshot ID — 20 characters of Crockford base32
TIMESTAMP	RFC 3339 ('2026-01-01T06:00:00Z') or a bare date ('2026-01-01'), read as midnight UTC

Only BRANCH and TIMESTAMP may be combined, which reads that branch as of that moment. TIMESTAMP on its own means main:

SELECT * FROM "my-repo"."root" AT(BRANCH => 'dev', TIMESTAMP => '2026-01-01');

Nothing here is relative — there is no "yesterday" — so a query carrying a ref is a stable citation you can put in a paper or a ticket and re-run later.

The clause attaches to a table, not to the query, so each table in a join can be pinned differently. Comparing one dataset against its own past is a self-join with two different pins:

SELECT n.time, n.temperature - o.temperature AS delta
FROM "my-repo"."root" AT(BRANCH => 'main') n
JOIN "my-repo"."root" AT(TAG => 'v1.0') o USING (time, lat, lon);

A snapshot ID is directly addressable for as long as Icechunk still stores it. Rolling it off every branch does not revoke SQL access to it; only garbage collection does.

You do not need to reconnect to see new data. An unqualified table resolves the branch tip while the query is being planned, so a commit from any other session — new chunks, a new variable, a whole new group — shows up in your next query. An unchanged tip costs nothing to check.

A few things will surprise you.

at and before are effectively reserved before a parenthesis. The parser claims both, so a table alias spelled at is read as version syntax and fails — FROM foo at(x) is an error. Quote it if you need that alias: FROM foo "at"(x).

BEFORE(...) and FOR SYSTEM_TIME AS OF are rejected. Both parse and both return an error pointing at AT(...), rather than being silently ignored.

With a Postgres driver, when the ref resolves depends on the protocol. Simple queries resolve on each execution. Prepared statements — the extended protocol — resolve the ref once, at Parse, so re-executing a prepared statement keeps reading the snapshot it first resolved; preparing the SQL again resolves it afresh. That is what makes a prepared query reproducible.

Version receipts only come back over the simple protocol, as NOTICE messages. Prepared statements are pinned identically but emit no receipt today, so use Flight SQL if you need the resolved snapshot reported back.

Examples

Filter pushdown

Filter pushdown is what makes SQL over array data practical rather than merely possible. A WHERE clause on a dimension is turned into a slice of that dimension and applied before any chunk is read, so the query reads only the chunks covering the region you asked for. This is the same thing that makes xarray's .sel() fast, and it is the difference between a query that touches a few megabytes and one that scans the whole dataset.

Two pushdowns happen, and they compose:

• Projection pushdown — only the variables your query names are read. Zarr stores each variable separately, so this is free. SELECT temperature FROM … never touches the other variables' chunks.
• Filter pushdown — a comparison against an indexed dimension is resolved through that dimension's coordinate index into an integer range, and becomes a slice.

Which columns can be pushed down

A column supports filter pushdown when it is a dimension that has a coordinate variable of the same name, and that variable has an orderable dtype:

Coordinate dtype	Pushdown
float32, float64	yes
Signed and unsigned integers	yes
datetime64	yes
timedelta64	yes
Fixed-length Unicode	no — there is no ordered index for strings

Everything else is filtered normally, after the data is read. In particular, a filter on a data variable is not a pushdown:

-- Pushed down: lat is an indexed dimension. Reads one row of chunks.
SELECT * FROM "repo"."root" WHERE lat = 30.0;

-- Not pushed down: temperature is a data variable, so every chunk is read
-- and the filter is applied to the rows afterwards.
SELECT * FROM "repo"."root" WHERE temperature > 40.0;

Mixing the two is fine and worth doing — the dimension filter still reduces what gets read, and the data-variable filter runs on what comes back:

SELECT * FROM "repo"."root" WHERE lat = 30.0 AND temperature > 40.0;

Which comparisons can be pushed down

SQL	Pushed down as
col = value	equality
col > value	greater than
col >= value	greater or equal
col < value	less than
col <= value	less or equal
col BETWEEN lo AND hi	closed range
col >= lo AND col <= hi	closed range

Writing the literal first (30.0 = lat, 144.0 > lon) is normalized automatically and pushes down the same way.

When several filters constrain the same dimension, the ranges are intersected and a single slice is applied, so lat >= -30 AND lat <= 30 reads exactly the band between them rather than one bound and then the other.

A filter that selects nothing is an empty result, not an error. Both WHERE lat = 999.0 (no such coordinate value) and WHERE lat >= 30 AND lat <= -30 (an empty intersection) return zero rows without reading any chunks.

SQL Dialect Reference

Queries are parsed and planned by Apache DataFusion, so DataFusion's SQL dialect is what you are writing — not PostgreSQL's, even when you connect with a Postgres client over the Postgres wire protocol. The wire protocol and the dialect are separate things: the protocol is how your client talks, and DataFusion decides what the query means.

In practice, if you are writing ordinary analytic SELECTs you will not notice the difference. What is worth knowing:

The service is read-only. SELECT is supported. There is no INSERT, UPDATE, DELETE, CREATE, or any other statement that would write — data gets into a repo through Icechunk, not through SQL.

Everything above the scan is ordinary DataFusion. The service's job is to present each group as a stream of rows; projection, filtering, limits and version pinning are the parts it implements itself, and aggregation, grouping, joins and window functions are then handled by DataFusion exactly as they would be over any other table. Aggregates are computed across partitions as the scan streams, so SELECT AVG(temperature) FROM … does not require the dataset to fit in memory.

information_schema is available for discovery, which is also what populates the table list in a GUI client's sidebar:

-- Which repos and groups can I query?
SELECT table_schema, table_name FROM information_schema.tables;

-- What are the columns of one group, and their types?
SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema = 'my-repo' AND table_name = 'root';

Version pinning is a non-standard extension, covered under Querying a specific version. Nothing else in the dialect is Arraylake-specific.

For the full list of supported functions, operators, and syntax, see the DataFusion SQL reference.