SQE: Sovereign Query Engine

SQE is a Rust-based distributed SQL query engine for Apache Iceberg tables. It replaces a patched Trino fork with a purpose-built engine based on Apache DataFusion and iceberg-rust.

graph LR
    Client["JDBC / Flight SQL Client"] --> Coordinator
    Coordinator --> Worker1["Worker 1"]
    Coordinator --> Worker2["Worker 2"]
    Coordinator --> WorkerN["Worker N"]
    Worker1 --> S3["S3 / MinIO"]
    Worker2 --> S3
    WorkerN --> S3
    Coordinator --> Polaris["Polaris Catalog"]
    Coordinator --> Keycloak["Keycloak OIDC"]

Key Properties

• No service account. Every query runs as the authenticated user. The user’s bearer token passes through to the Polaris catalog (metadata) and on the write path. Read-path S3 access currently uses the configured [storage] credentials; per-user read-credential vending is on the roadmap (see S3 credential vending).
• Arrow-native — columnar data flows from Parquet files through the entire query pipeline to the client. No row-based serialization anywhere.
• Iceberg-native — built on iceberg-rust, not a connector bolted onto a generic engine. Partition pruning, metadata caching, and Iceberg v3 support are first-class.
• Fine-grained security — row filters and column masks enforced at the logical plan level, before the optimizer runs. Invisible columns, transparent row filtering, no information leakage.
• Rust performance — single binary, no JVM, no GC pauses, predictable memory usage, fast startup.

Quick Start (embedded, no server)

cargo install --path crates/sqe-cli
sqe-cli --embedded                    # ~/.sqe/warehouse persistent Iceberg catalog

sqe> SELECT * FROM '/data/sales.parquet' LIMIT 5;
sqe> SELECT * FROM read_csv('s3://bucket/orders.tsv.gz');
sqe> SELECT * FROM 'hf://datasets/squad/plain_text/train-00000-of-00001.parquet';
sqe> SELECT * FROM read_delta('/data/delta/sales', version => '5');

Full embedded reference: Using the CLI. DuckDB comparison: getsqe.com/compare/duckdb.

Quick Start (cluster mode)

# Build
cargo build --release --bin sqe-server --bin sqe-cli

# Start coordinator (sqe-server runs as coordinator by default)
SQE_CONFIG=sqe.toml ./target/release/sqe-server

# Connect
./target/release/sqe-cli --host localhost --port 50051

Project Status

SQE is production-ready for the data path against Apache Iceberg; the coordinator currently runs as a single replica (a single point of failure), and coordinator HA is on the roadmap (see Kubernetes & Helm). The cluster mode runs distributed (coordinator + stateless workers) with OIDC bearer-token passthrough, Polaris / Nessie / Glue / HMS / S3 Tables / JDBC / Hadoop catalogs, and 167/189 (88.4%) on the public Iceberg matrix scoreboard. The embedded mode (V8 through V12.1) adds DuckDB-style file-format TVFs (read_csv, read_json, read_delta), HuggingFace hf:// URLs, and a single-binary CLI for laptop analytics.

Why SQE

The Problem

Our data platform runs on Apache Iceberg tables stored in S3, cataloged by Apache Polaris (Iceberg REST Catalog), with authentication through Keycloak OIDC. We need a SQL query engine that can:

1. Authenticate users through Keycloak
2. Pass the user’s bearer token through to Polaris and on the write path (no service account). Read-path S3 access currently uses the configured [storage] credentials; per-user read vending is on the roadmap (see S3 credential vending)
3. Enforce fine-grained security (row filters, column masks) per user
4. Support full SQL for analytics, dbt transformations, and ad-hoc queries
5. Run on Kubernetes with minimal operational overhead

Why Not Trino?

We started with Trino, the industry-standard SQL engine for data lakehouses. It works, but:

Why DataFusion + Rust?

graph TB
    subgraph "Old: Trino Fork"
        T[Trino JVM] -->|service account| P1[Polaris]
        T -->|service account| S1[S3]
        T -.- Fork[DCAF Fork<br/>maintenance burden]
    end

    subgraph "New: SQE"
        C[SQE Coordinator<br/>Rust binary] -->|user bearer token| P2[Polaris]
        W[SQE Workers] -->|"writes: user creds, reads: storage key"| S2[S3]
        C --> W
    end

    style Fork fill:#f96,stroke:#333
    style C fill:#6f9,stroke:#333
    style W fill:#6f9,stroke:#333

Apache DataFusion is a Rust-native query engine that gives us:

• Extensible query planning: we can inject security filters into the LogicalPlan before optimization, which is exactly where row filters and column masks need to go
• iceberg-rust integration: native Rust Iceberg library, no JNI bridge, no serialization overhead
• Per-query context: each query gets its own SessionContext with the user’s bearer token. No shared service account.
• Single binary: the coordinator and worker ship as one ~50MB binary. Starts in milliseconds.
• No GC: predictable latency, no stop-the-world pauses during large scans

The Name

Sovereign — because every query runs with the identity and permissions of the user who submitted it. No service account intermediary. No privilege escalation. The user’s token is sovereign.

The scope is precise. The user’s bearer token reaches the Polaris catalog (metadata) and the write path per user. The read path currently reads S3 with the configured [storage] credentials; per-user read-credential vending is on the roadmap (see S3 credential vending). Catalog permissions and the plan-rewriting policy engine still gate what each user can see, so identity drives authorization even where the read path shares a storage key.

From Trino to DataFusion

Architecture Comparison

graph LR
    subgraph Trino
        TC[Coordinator<br/>JVM ~2GB heap] --> TW1[Worker<br/>JVM ~8GB heap]
        TC --> TW2[Worker<br/>JVM ~8GB heap]
        TC -->|Hive Metastore<br/>protocol| HMS[Hive Metastore<br/>or Polaris]
        TW1 -->|service account| TS3[S3]
        TW2 -->|service account| TS3
    end

    subgraph SQE
        SC[sqe-server<br/>coordinator<br/>~50MB binary] --> SW1[sqe-server<br/>worker]
        SC --> SW2[sqe-server<br/>worker]
        SC -->|user bearer token<br/>Iceberg REST| POL[Polaris]
        SW1 -->|user credentials| SS3[S3]
        SW2 -->|user credentials| SS3
    end

What Changes

What Stays the Same

• Apache Iceberg as the table format
• Apache Polaris as the REST catalog
• Keycloak as the identity provider
• S3 as the storage layer
• dbt as the transformation framework (new native adapter instead of Trino adapter)
• JDBC connectivity (via Arrow Flight SQL JDBC driver instead of Trino JDBC)

Migration Path

SQE includes an optional Trino-compatible HTTP endpoint (/v1/statement) that speaks enough of the Trino wire protocol to support existing dashboards and tools during the migration period. This is not a full Trino emulation. It covers SELECT, SHOW, and basic DDL, enough to keep things running while teams migrate to Flight SQL.

timeline
    title Migration Timeline
    Phase 1 : SQE single-node : Flight SQL : CLI
    Phase 2 : Write path : dbt-sqe adapter : Views
    Phase 3 : Distributed execution : Workers
    Phase 4 : Trino compat layer : Dashboard migration
    Phase 5 : Security policies : Row filters : Column masks
    Phase 6 : Decommission Trino fork

The Auth Challenge

The core design constraint that drove us to build SQE: no service account.

The Problem with Service Accounts

In a typical data platform, the query engine authenticates to the catalog and storage with a service account, a single identity with broad permissions. The engine then enforces per-user access control internally.

sequenceDiagram
    participant User
    participant Trino
    participant Polaris
    participant S3

    User->>Trino: Query (user token)
    Note over Trino: Validates user token
    Trino->>Polaris: List tables (SERVICE ACCOUNT)
    Polaris-->>Trino: Table metadata
    Trino->>S3: Read Parquet (SERVICE ACCOUNT IAM role)
    S3-->>Trino: Data
    Trino-->>User: Results

This means:

• Polaris sees one identity for all queries: audit logs show the service account, not the actual user
• S3 access is all-or-nothing: the service account can read everything, security depends entirely on the engine enforcing it correctly
• Credential rotation is a blast-radius event: rotating the service account key affects all users simultaneously
• Compliance gap: auditors want to see that Alice read table X, not that sqe-service-account did

SQE’s Approach: Bearer Token Passthrough

SQE never stores or uses a service account for data access. Instead, the user’s Keycloak bearer token flows through the entire stack:

sequenceDiagram
    participant User
    participant SQE as SQE Coordinator
    participant KC as Keycloak
    participant Polaris
    participant S3

    User->>SQE: Handshake (username, password)
    SQE->>KC: OIDC Password Grant
    KC-->>SQE: Access token + refresh token
    SQE-->>User: Session (bearer token)

    User->>SQE: Query (bearer token)
    SQE->>Polaris: List tables (USER's bearer token)
    Polaris-->>SQE: Table metadata + S3 credentials
    Note over Polaris: Polaris vends scoped S3<br/>credentials for THIS user
    SQE->>S3: Read Parquet (user-scoped credentials)
    S3-->>SQE: Data
    SQE-->>User: Arrow Flight results

Key Implications

Per-Session Catalog

Each user session gets its own SessionCatalog instance, initialized with the user’s bearer token:

graph TB
    subgraph "Session: alice"
        SC1[SessionCatalog<br/>token: alice_jwt] --> P[Polaris REST]
    end

    subgraph "Session: bob"
        SC2[SessionCatalog<br/>token: bob_jwt] --> P
    end

    P -->|alice's token| S3A[S3: alice sees<br/>tables A, B, C]
    P -->|bob's token| S3B[S3: bob sees<br/>tables A, B only]

Polaris enforces catalog-level access control based on the token. If Alice has access to tables A, B, C but Bob only has access to A and B, this is enforced at the catalog level. SQE doesn’t need to duplicate this logic.

Token Lifecycle

SQE manages token refresh transparently. A background task checks all active sessions every 10 seconds and refreshes tokens that are about to expire:

stateDiagram-v2
    [*] --> Active: Handshake (ROPC grant)
    Active --> Refreshing: Token expiry - 60s buffer
    Refreshing --> Active: New token from Keycloak
    Refreshing --> Expired: Refresh fails
    Active --> Expired: Session timeout
    Expired --> [*]: Session removed

    note right of Active: Queries use current token
    note right of Refreshing: Background task (10s interval)

The token fingerprint (last 8 characters of the access token) is used to invalidate iceberg-rust’s internal catalog session cache when a token is refreshed, ensuring the catalog client always uses the current token.

System Overview

Components

graph TB
    subgraph Clients
        JDBC["JDBC / ODBC<br/>(Flight SQL driver)"]
        CLI["sqe-cli"]
        DBT["dbt-sqe adapter"]
        DASH["Dashboards<br/>(Trino compat)"]
    end

    subgraph "SQE Cluster"
        subgraph "Coordinator (sqe-server --mode coordinator)"
            FLS["Flight SQL Server<br/>:50051"]
            TH["Trino HTTP<br/>:8080"]
            SM["Session Manager"]
            QH["Query Handler"]
            PE["Policy Enforcer"]
            SCHED["Scheduler"]
        end

        subgraph "Workers (sqe-server --mode worker)"
            W1["Worker 1<br/>DataFusion executor"]
            W2["Worker 2<br/>DataFusion executor"]
            WN["Worker N<br/>DataFusion executor"]
        end
    end

    subgraph "External Services"
        KC["OIDC provider<br/>(Keycloak / Auth0 / Entra)"]
        CAT["Catalog backend<br/>(Polaris / Nessie / Glue REST /<br/>S3 Tables / Unity / HMS / JDBC)"]
        S3["S3-compatible storage<br/>(AWS / Ceph / R2 / rustfs)"]
    end

    JDBC --> FLS
    CLI --> FLS
    DBT --> FLS
    DASH --> TH

    SM --> KC
    QH --> PE
    QH --> SCHED
    SCHED --> W1
    SCHED --> W2
    SCHED --> WN

    QH --> CAT
    W1 --> S3
    W2 --> S3
    WN --> S3

The catalog backend is selectable at runtime. Polaris is the primary target and the only one verified end-to-end for production write paths today. Nessie, AWS Glue, AWS S3 Tables, Unity Catalog OSS, Hive Metastore, JDBC (Postgres), and Hadoop storage-only are all reachable through the same iceberg::Catalog trait, with live integration tests in sqe-catalog/tests/backends_integration.rs. AWS endpoints share the OSS Iceberg REST code path through the aws-sigv4 cargo feature on the vendored iceberg-catalog-rest crate. See features/iceberg.md for the catalog-by-catalog state.

The coordinator currently runs as a single replica. It is a single point of failure: a restart drops in-flight queries and session state, which is process-local. Workers are stateless and scale horizontally. Coordinator high availability is on the roadmap. See Kubernetes & Helm for the deployment topology.

Request Flow

A query flows through SQE in these stages:

sequenceDiagram
    participant C as Client
    participant F as Flight SQL Server
    participant SM as Session Manager
    participant QH as Query Handler
    participant PE as Policy Enforcer
    participant DF as DataFusion
    participant CAT as Polaris Catalog
    participant S3 as S3 Storage

    C->>F: do_handshake(user, pass)
    F->>SM: authenticate(user, pass)
    SM->>SM: Keycloak OIDC → session
    F-->>C: bearer token

    C->>F: execute(SQL, token)
    F->>SM: get_session(token)
    F->>QH: execute(session, SQL)

    QH->>QH: parse & classify SQL
    QH->>CAT: create SessionCatalog(user_token)
    QH->>DF: plan SQL → LogicalPlan
    QH->>PE: enforce(user, plan)
    PE-->>QH: secured plan (row filters, column masks)
    QH->>DF: optimize & execute
    DF->>S3: read Parquet files
    S3-->>DF: Arrow RecordBatches
    DF-->>QH: results
    QH-->>F: RecordBatches
    F-->>C: Arrow Flight stream

Single-Node vs Distributed

SQE starts in single-node mode by default. The coordinator executes queries locally using DataFusion. No workers needed.

For larger deployments, enable workers:

graph LR
    subgraph "Single-Node (default)"
        C1[sqe-server] -->|local DataFusion| S1[S3]
    end

    subgraph "Distributed"
        C2[Coordinator] -->|plan fragments| W1[Worker 1]
        C2 --> W2[Worker 2]
        W1 --> S2[S3]
        W2 --> S2
    end

Ports

Caching

SQE caches at five layers, each falling through to the next on a miss. The first two layers (session and catalog) live in memory and are short-lived; the last three (table metadata, manifest, footer) hold immutable or near-immutable Iceberg data.

graph TD
    SC["Layer 1: SessionContext cache<br/>moka, SHA-256 token fingerprint<br/>TTL 5 min, max 100 entries"]
    RC["Layer 2: RestCatalog cache<br/>moka, per-warehouse<br/>TTL 5 min"]
    TMC["Layer 3: Table metadata cache<br/>moka, ETag validation<br/>TTL configurable (default 30s)"]
    MC["Layer 4: Manifest file cache<br/>moka, content-addressed<br/>no TTL, default 512 MB"]
    FC["Layer 5: Parquet footer cache<br/>object_store, byte-range<br/>default 256 MB"]

    SC -->|miss| RC
    RC -->|miss| TMC
    TMC -->|miss| MC
    MC -->|miss| FC
    FC -->|miss| S3["S3 storage"]

    DDL["DDL (CREATE / DROP / ALTER)"] -.->|invalidates| SC

Invalidation follows the Iceberg data model:

• The session cache is invalidated after any DDL statement (CREATE TABLE, DROP TABLE, ALTER TABLE).
• Table metadata uses ETag-based conditional requests. Polaris returns 304 Not Modified when metadata has not changed, so a cache hit costs one cheap round-trip rather than a full metadata fetch.
• Manifest files are immutable by Iceberg specification, so they need no TTL-based expiry. They evict only under memory pressure.
• The footer cache evicts on an LRU basis within its configured memory budget.

Coordinator

The coordinator is the brain of SQE. It handles SQL parsing, query planning, security enforcement, and result delivery. In single-node mode, it also executes queries directly.

Responsibilities

graph TB
    subgraph Coordinator
        FLS["Flight SQL Server"] --> SM["Session Manager"]
        FLS --> QH["Query Handler"]

        QH --> PARSE["SQL Parser<br/>(sqlparser-rs)"]
        QH --> CLASS["Statement Classifier"]
        QH --> PLAN["Query Planner<br/>(DataFusion)"]
        QH --> PE["Policy Enforcer"]
        QH --> CAT["Catalog Ops<br/>(DDL)"]
        QH --> WH["Write Handler<br/>(CTAS, INSERT,<br/>DELETE, UPDATE,<br/>MERGE — CoW)"]

        SM --> AUTH["Authenticator<br/>(Keycloak OIDC)"]
        SM --> TC["Token Cache"]

        PLAN --> DF["DataFusion<br/>SessionContext"]
        DF --> SC["SessionCatalog<br/>(per-user token)"]
    end

Statement Routing

The coordinator classifies every SQL statement and routes it to the appropriate handler:

Session Context

Each query gets a fresh DataFusion SessionContext with the user’s catalog:

sequenceDiagram
    participant QH as Query Handler
    participant SC as SessionCatalog
    participant POL as Polaris
    participant DF as DataFusion

    QH->>SC: new(catalog_url, warehouse, user_token)
    SC->>POL: list_namespaces() [user_token]
    POL-->>SC: [ns1, ns2, ns3]
    QH->>DF: register_catalog(warehouse, CatalogProvider)
    QH->>DF: sql("SELECT * FROM ns1.table1")
    DF->>SC: get_table("ns1", "table1")
    SC->>POL: load_table("ns1.table1") [user_token]
    POL-->>SC: table metadata + S3 location
    SC-->>DF: TableProvider (Iceberg scan)

This means two users running the same query may see different tables, schemas, or data, depending on what Polaris grants them.

Worker

Workers are stateless DataFusion executors. They receive plan fragments (scan tasks) from the coordinator, read Parquet files from S3, and stream Arrow results back.

Architecture

graph TB
    subgraph Worker["sqe-server --mode worker"]
        FS["Flight Service<br/>:50052"]
        EX["Executor"]
        PR["Parquet Reader"]
    end

    COORD["Coordinator"] -->|ScanTask ticket| FS
    FS --> EX
    EX --> PR
    PR -->|read| S3["S3 / MinIO"]
    PR -->|Arrow RecordBatches| FS
    FS -->|Flight stream| COORD

Scan Task

The coordinator sends workers a ScanTask, a lightweight JSON message containing everything the worker needs:

{
  "fragment_id": "frag-001",
  "data_file_paths": [
    "s3://warehouse/ns/table/data/00001.parquet",
    "s3://warehouse/ns/table/data/00002.parquet"
  ],
  "projected_columns": ["id", "name", "amount"],
  "s3_endpoint": "http://s3:9000",
  "s3_region": "us-east-1",
  "s3_access_key": "...",
  "s3_secret_key": "...",
  "s3_path_style": true
}

Workers don’t need access to Polaris or Keycloak. The coordinator resolves table metadata, applies security, and provides the worker with direct S3 credentials and file paths.

Health Checking

The coordinator monitors workers with a background health check task:

stateDiagram-v2
    [*] --> Unhealthy: Registered
    Unhealthy --> Healthy: Health check OK
    Healthy --> Healthy: Health check OK
    Healthy --> Degraded: 1-2 consecutive failures
    Degraded --> Unhealthy: 3 consecutive failures
    Degraded --> Healthy: Health check OK
    Unhealthy --> Healthy: Health check OK

• Health checks run every 5 seconds via Flight Action("health_check")
• A worker is marked unhealthy after 3 consecutive failures
• Unhealthy workers are excluded from query scheduling
• Recovery is automatic: a healthy response resets the failure counter

Scaling

Workers are stateless, so scaling is just changing the replica count:

# Helm
helm upgrade sqe deploy/helm/sqe/ --set worker.replicas=5

# kubectl
kubectl scale deployment sqe-worker --replicas=5

The coordinator discovers workers from the config or service discovery. No re-registration needed.

Authentication Flow

SQE supports two OAuth2 flows for initial authentication, then manages token lifecycle transparently:

• OIDC Password Grant (ROPC) – for user-interactive authentication where Flight SQL sends username and password. Works with Keycloak or any OIDC provider that supports the Resource Owner Password Credentials grant.
• OAuth2 Client Credentials – for service-to-service auth, test environments, or OIDC providers that do not support ROPC. Configured by setting token_endpoint directly instead of keycloak_url.

The mode is selected automatically based on configuration: if keycloak_url is set, SQE uses ROPC; if token_endpoint is set (and keycloak_url is empty), SQE uses client credentials.

Why ROPC?

Flight SQL’s handshake sends username and password directly. There’s no browser redirect flow possible over gRPC. ROPC is the standard mechanism for non-interactive clients (JDBC drivers, CLI tools, dbt adapters).

Client Credentials Mode

When token_endpoint is set (and keycloak_url is empty), SQE uses the client_credentials grant instead of ROPC. In this mode:

• The coordinator obtains a service token using client_id + client_secret posted directly to the configured token endpoint.
• The username from the Flight SQL handshake is informational only – it is used for session labeling and audit logs, but is not sent to the token endpoint.
• There is no refresh_token in client credentials responses. When a token nears expiry, SQE re-fetches a new token via another client_credentials request.
• This is the mode used by the lightweight test stack (Polaris built-in OAuth), where Polaris itself acts as the token issuer.

Example Configuration

[auth]
token_endpoint = "http://polaris:8181/api/catalog/v1/oauth/tokens"
client_id = "root"
client_secret = "s3cr3t"

Client Credentials Sequence

sequenceDiagram
    participant Client
    participant SQE as SQE Coordinator
    participant TE as Token Endpoint
    participant POL as Polaris
    participant S3

    Note over Client,TE: Authentication
    Client->>SQE: Flight Handshake<br/>Basic auth (user:pass)
    SQE->>TE: POST /token<br/>grant_type=client_credentials<br/>client_id, client_secret
    TE-->>SQE: access_token, expires_in
    SQE->>SQE: Create Session<br/>(username from handshake,<br/>token from endpoint)
    SQE-->>Client: Bearer token (session_id)

    Note over Client,S3: Query Execution
    Client->>SQE: execute(SQL)<br/>Authorization: Bearer session_id
    SQE->>SQE: Lookup session → get access_token
    SQE->>POL: GET /namespaces<br/>Authorization: Bearer access_token
    POL-->>SQE: [namespace list]
    SQE->>POL: POST /tables/load<br/>Authorization: Bearer access_token
    POL-->>SQE: table metadata + S3 credentials
    SQE->>S3: GetObject (vended credentials)
    S3-->>SQE: Parquet data
    SQE-->>Client: Arrow Flight stream

    Note over SQE,TE: Background Token Re-fetch
    SQE->>TE: POST /token<br/>grant_type=client_credentials<br/>client_id, client_secret
    TE-->>SQE: new access_token, expires_in
    SQE->>SQE: Update session token

ROPC Flow

The following sections describe the ROPC (password grant) flow in detail.

Complete Flow

sequenceDiagram
    participant Client
    participant SQE as SQE Coordinator
    participant KC as Keycloak
    participant POL as Polaris
    participant S3

    Note over Client,KC: Authentication
    Client->>SQE: Flight Handshake<br/>Basic auth (user:pass)
    SQE->>KC: POST /token<br/>grant_type=password<br/>username, password, client_id
    KC-->>SQE: access_token, refresh_token, expires_in
    SQE->>SQE: Create Session<br/>(id, user, roles, tokens)
    SQE-->>Client: Bearer token (session_id)

    Note over Client,S3: Query Execution
    Client->>SQE: execute(SQL)<br/>Authorization: Bearer session_id
    SQE->>SQE: Lookup session → get access_token
    SQE->>POL: GET /namespaces<br/>Authorization: Bearer access_token
    POL-->>SQE: [namespace list]
    SQE->>POL: POST /tables/load<br/>Authorization: Bearer access_token
    POL-->>SQE: table metadata + S3 credentials
    Note over POL: Polaris vends S3<br/>credentials scoped to<br/>this user + table
    SQE->>S3: GetObject (vended credentials)
    S3-->>SQE: Parquet data
    SQE-->>Client: Arrow Flight stream

    Note over SQE,KC: Background Token Refresh
    SQE->>KC: POST /token<br/>grant_type=refresh_token
    KC-->>SQE: new access_token, new refresh_token
    SQE->>SQE: Update session tokens

Token Refresh

A background task runs every 10 seconds, scanning all active sessions:

#![allow(unused)]
fn main() {
// Pseudocode
loop {
    sleep(10 seconds);
    for session in sessions_expiring_within(60 seconds) {
        match keycloak.refresh_token(session.refresh_token) {
            Ok(new_tokens) => session.update(new_tokens),
            Err(_) => session.mark_expired(),
        }
    }
}
}

The 60-second buffer ensures tokens are refreshed well before expiry, avoiding mid-query auth failures.

Client Credentials mode: There is no refresh_token in client credentials responses. The background task detects this and re-fetches a fresh token via a new client_credentials request to the token endpoint when the current token is near expiry. The same 60-second buffer applies.

Token Fingerprinting

When a token is refreshed, the iceberg-rust catalog client’s internal HTTP session cache still holds the old token. SQE uses a token fingerprint (last 8 characters of the access token) as part of the catalog session key. When the fingerprint changes, a new catalog session is created with the fresh token.

graph LR
    T1["Token: ...abc12345<br/>fingerprint: abc12345"] -->|refresh| T2["Token: ...xyz98765<br/>fingerprint: xyz98765"]
    T1 --> CS1["CatalogSession 1"]
    T2 --> CS2["CatalogSession 2<br/>(new, fresh token)"]

Role Extraction

SQE extracts user roles from the JWT realm_access.roles claim. These roles are stored in the session and used for policy evaluation:

{
  "realm_access": {
    "roles": ["data-analyst", "finance-reader", "admin"]
  }
}

Roles flow through to the Policy Enforcer, which uses them to determine row filters and column masks for each query.

Client Credentials mode: Role extraction only applies in OIDC (ROPC) mode, where the JWT contains user-specific claims. In client credentials mode, the token represents the service itself and typically does not carry realm_access.roles. The session’s role list is empty, and all authorization decisions are delegated to Polaris (which enforces access based on the service principal’s catalog grants).

Security & Policy

SQE enforces fine-grained security through LogicalPlan rewriting, injecting row filters and column masks into the query plan before DataFusion’s optimizer runs.

Status: The plan-rewriting policy enforcer is implemented and pluggable, and it is off by default. The default policy engine is passthrough (PassthroughEnforcer), which returns plans unmodified, so enforcement is opt-in. The one enforcement backend is Apache Ranger, with an in-memory store for dev and tests. A default open-source deployment runs without any of this. See GRANT and REVOKE for the SQL surface and the Chameleon / SBP note.

Design Principle

Security enforcement happens at the logical plan level, not at the data level:

graph TB
    SQL["SQL: SELECT * FROM sales"] --> PARSE["Parse"]
    PARSE --> PLAN["LogicalPlan<br/>Projection → TableScan(sales)"]
    PLAN --> POLICY["Policy Enforcer<br/>inject row filter + column mask"]
    POLICY --> SECURED["Secured LogicalPlan<br/>Projection → Filter(region='EU') → TableScan(sales)<br/>+ mask(ssn)"]
    SECURED --> OPT["DataFusion Optimizer"]
    OPT --> EXEC["Execute"]

    style POLICY fill:#f96,stroke:#333

This approach means:

• Row filters are transparent. The user doesn’t know they exist
• Column masks block predicate pushdown on raw values. You can’t WHERE ssn = '123-45-6789' to probe masked data
• Denied columns are invisible. They don’t appear in SELECT *, not as errors
• The optimizer can push user predicates through row filters but not through column masks

Policy Enforcer Trait

#![allow(unused)]
fn main() {
#[async_trait]
pub trait PolicyEnforcer: Send + Sync {
    async fn evaluate(
        &self,
        user: &SessionUser,
        plan: LogicalPlan,
    ) -> Result<LogicalPlan>;
}
}

Implementations:

• PassthroughEnforcer: returns plan unchanged (default; enforcement opt-in)
• Ranger: reads row-filter and column-mask policies from Apache Ranger and feeds the plan rewriter (shipped, wired)
• InMemory: grants stored in a hash map for dev and tests (shipped, wired)

The Ranger enforcer reads the same hive service-def that Apache Spark reads through its Kyuubi authorization plugin, so one policy written once in Ranger enforces byte-identically in SQE and in Spark. See Fine-grained access control for the how-to and Spark / Ranger Parity for the validated result.

SQL Extensions

-- Grant row filter
GRANT SELECT ON sales TO ROLE analyst
  ROWS WHERE region = 'EU';

-- Grant column mask
GRANT SELECT ON customers TO ROLE support
  MASKED WITH (ssn AS '***-**-' || RIGHT(ssn, 4));

-- View effective grants
SHOW EFFECTIVE GRANTS FOR USER "alice";

-- View grants
SHOW GRANTS ON sales;

No Information Leakage

Following the PostgreSQL RLS model:

Runtime Security Controls

SQE includes several runtime security mechanisms that are active by default or can be enabled via configuration.

Rate Limiting

Throttles query submission to prevent abuse or runaway clients. Uses a token-bucket algorithm (via the governor crate).

[rate_limit]
enabled = true
per_user_queries_per_minute = 60
global_queries_per_minute = 1000

When a limit is exceeded, the client receives a RESOURCE_EXHAUSTED Flight error. Rate limiting is disabled by default.

Query Timeouts

Every query is subject to an execution timeout. If the query exceeds the limit, it is cancelled and the client receives an error.

[query]
timeout_secs = 300              # Default: 5 minutes

[query.role_overrides]
admin = 3600                    # Admins get 1 hour
analyst = 600                   # Analysts get 10 minutes

Role overrides allow different timeout limits per role. The user’s longest-matching role timeout wins.

Session Lifecycle

Sessions have both idle and absolute timeouts. A background sweeper runs every 60 seconds to clean up expired sessions.

[session]
idle_timeout_secs = 900         # 15 min idle timeout
absolute_timeout_secs = 28800   # 8 hour hard cap

• Idle timeout: sessions with no query activity for this long are expired
• Absolute timeout: sessions older than this are expired regardless of activity

Query Cancellation

SQE supports Arrow Flight’s native cancellation mechanism. When a client cancels a query (or disconnects), the CancellationToken is triggered and propagated to workers, stopping execution promptly.

Error Sanitization

In production mode (debug = false, the default), error messages returned to clients are sanitized:

• Internal details (stack traces, file paths, internal error types) are stripped
• Clients receive a short error message and a request ID for correlation
• Full details are logged server-side for debugging

Enable debug = true during development to see full error details:

[coordinator]
debug = true

TLS Encryption

Flight SQL connections can be encrypted with TLS. Optional mTLS adds client certificate verification.

[coordinator.tls]
cert_file = "/etc/sqe/server.crt"
key_file  = "/etc/sqe/server.key"
ca_file   = "/etc/sqe/ca.crt"    # Optional: mTLS

See Configuration for details.

Streaming Execution

SQE’s streaming execution engine enables 1TB-scale queries on memory-constrained servers. The implementation is split into two phases: Phase A (safe) handles single-node memory management and scan optimization, while Phase B (fast) distributes computation across workers via Arrow Flight DoExchange.

The Problem

A coordinator-centric query engine hits a hard wall: every intermediate result flows through one process. An ORDER BY on 1TB of data requires 1TB of memory (or spill space) on the coordinator, regardless of how many workers scanned the data. A four-way hash join between large tables can exhaust coordinator memory long before the result set is assembled.

The fundamental tension is between sovereignty (run on your own hardware, which may be small) and scale (query datasets that don’t fit in memory). SQE solves this in two stages: first, make the coordinator survive large queries through spill-to-disk and scan optimization (Phase A); then, push computation to workers so the coordinator handles only final aggregation (Phase B).

Phase A: Safe (Single-Node)

Phase A ensures that a single coordinator with limited memory (e.g., 512MB) can execute large analytical queries without OOM kills.

Coordinator Spill-to-Disk

DataFusion’s FairSpillPool divides available memory across all active operators. When an operator (sort, hash aggregate, hash join) exceeds its share, it spills intermediate results to disk as sorted runs.

Key components:

• FairSpillPool – configured via memory_limit in sqe.toml. Divides memory equally among registered MemoryConsumer instances. Triggers spill when any consumer exceeds its fair share.
• Watermark system – four levels (green/yellow/orange/red) based on pool utilization percentage. Green (<60%) allows normal execution. Yellow (60-75%) triggers advisory warnings. Orange (75-90%) forces spillable operators to spill. Red (>90%) activates admission control, queueing new queries until memory drops below the orange threshold.
• Admission control – when the pool is in the red zone, new queries wait in a bounded queue rather than competing for memory. This prevents cascade failures where N concurrent queries each grab 1/N of memory and all spill simultaneously.
• External merge sort – when a SortExec spills, it writes sorted runs to spill_dir (default: /tmp/sqe-spill). On final output, a k-way merge reads all runs simultaneously, producing a globally sorted stream with constant memory overhead.

Configuration in sqe.toml:

[coordinator]
memory_limit = "512MB"
spill_dir = "/tmp/sqe-spill"
spill_compression = "zstd"  # lz4, zstd, or none

Late Materialization

Standard Parquet scans read all projected columns from every row group. Late materialization splits this into two phases:

1. Predicate phase – read only the columns referenced in WHERE clauses. Apply filters. Produce a set of surviving row indices.
2. Projection phase – for surviving rows only, read the remaining projected columns.

This is implemented as a two-phase RowFilter scan in the Iceberg scan planning layer. For queries with selective predicates (e.g., WHERE status = 'CLOSED' on a table where 5% of rows match), late materialization reduces I/O by up to 95% on the non-predicate columns.

The optimization is transparent to the rest of the plan – the TableScan still produces the same Arrow schema. The difference is entirely in how many bytes are read from Parquet.

Iceberg Scan Planning

Three optimizations happen before any Parquet data is read:

• File-level min/max pruning – Iceberg manifest files contain per-column min/max statistics for each data file. SQE reads these statistics and skips files where the predicate cannot match. For example, WHERE order_date > '2025-01-01' skips any file whose order_date max is before 2025.
• Sort-order detection – Iceberg metadata records the sort order of each data file. When a query includes ORDER BY on the sort column, SQE can skip the sort operator entirely and produce output directly from the pre-sorted scan. When multiple sorted files need merging, a merge-sort is cheaper than a full re-sort.
• PageIndex pruning – for Parquet files with page-level statistics (column index), SQE prunes individual pages within a row group, further reducing I/O for selective predicates.
• TopK optimization – ORDER BY ... LIMIT N queries use a heap-based TopK operator that maintains only N rows in memory, avoiding a full sort and spill.

S3 I/O Pipeline

Reading Parquet files from S3 involves many small HTTP GET requests (one per column chunk per row group). SQE optimizes this with:

• Request coalescing – adjacent byte ranges within coalesce_threshold (default: 1MB) are merged into a single GET request. This reduces the number of HTTP round-trips, which dominate latency on high-latency S3 endpoints.
• Footer cache – Parquet file footers (schema, row group metadata, column chunk offsets) are cached in a footer_cache_size-bounded LRU cache. Repeated queries against the same table skip the footer read entirely.
• Prefetch – while the executor processes the current row group, the next row group’s column chunks are fetched in the background, hiding S3 latency behind compute.

Configuration:

[storage]
coalesce_threshold = "1MB"
footer_cache_size = 256  # number of footers

SortMergeJoin Fallback

DataFusion’s default join strategy is hash join, which builds a hash table from the build side in memory. For large joins, this hash table can exceed the memory limit. DataFusion does not yet support hash join spill-to-disk upstream.

SQE registers a SortMergeJoin fallback: when the estimated build-side size exceeds hash_join_memory_threshold, the optimizer rewrites the join as a sort-merge join. Both sides are sorted (spilling to disk if needed via the external merge sort) and then merged with constant memory. This is slower than an in-memory hash join but avoids OOM on large joins.

[optimizer]
hash_join_memory_threshold = "256MB"

Phase B: Fast (Distributed)

Phase B pushes computation past the scan boundary. Instead of workers sending raw Arrow batches to the coordinator for all processing, workers perform filters, partial aggregations, partial sorts, and join probes locally.

DoExchange Shuffle

Arrow Flight’s DoExchange RPC enables bidirectional streaming between workers. SQE uses this to implement a hash-partitioned shuffle:

1. The coordinator decomposes the physical plan into stages separated by shuffle boundaries (e.g., a hash join requires both sides to be hash-partitioned on the join key).
2. Each stage runs on a set of workers. When a stage completes, its output is hash-partitioned by the shuffle key and streamed to the next stage’s workers via DoExchange.
3. The partitioning function uses the same hash(key) % num_partitions scheme as DataFusion’s RepartitionExec, ensuring compatibility with the existing hash join and hash aggregate operators.

Distributed Sort (Range-Partition)

A distributed ORDER BY proceeds in three steps:

1. Sample – each worker samples its local partition and sends the sample to the coordinator.
2. Range boundaries – the coordinator computes quantile boundaries from the samples, producing N-1 split points for N workers.
3. Range-partition and merge – each worker range-partitions its data and sends each range to the designated worker. Each receiving worker sorts its range locally (spilling if needed). The coordinator merges the sorted ranges via a k-way merge.

This distributes both the memory cost and the CPU cost of sorting. A 1TB ORDER BY with 8 workers requires roughly 125GB of spill per worker instead of 1TB on the coordinator.

Two-Phase Aggregation

Aggregation queries (GROUP BY) use a two-phase approach:

1. Partial aggregation – each worker computes partial aggregates on its local data. For SUM(amount) GROUP BY region, each worker produces a partial sum per region from its partition.
2. Final aggregation – partial results are shuffled by the grouping key to a set of finalizer workers (or the coordinator for small result sets). Each finalizer merges the partial aggregates into the final result.

This solves the q18 problem: TPC-H query 18 has a high-cardinality GROUP BY that produces millions of groups. On a single coordinator with 512MB, the GroupedHashAggregate exceeds memory. With two-phase aggregation, each worker handles a fraction of the groups, and memory pressure is distributed.

Distributed Joins

SQE supports four join strategies in distributed mode:

• Broadcast join – when one side of the join is small (below broadcast_threshold, default 10MB), it is broadcast to all workers. Each worker probes its local partition of the large side against the broadcast table. No shuffle required.
• Shuffle hash join – both sides are hash-partitioned on the join key and shuffled to matching workers. Each worker performs a local hash join on its partition.
• Pre-sorted merge join – when both sides are already sorted on the join key (detected via Iceberg sort-order metadata), workers perform a merge join without re-sorting. This avoids the sort cost entirely.
• Predicate transfer – before executing a join, the build side’s distinct join keys are collected and pushed as an IN-list filter to the probe side’s scan. This skips probe-side files that contain no matching keys, reducing I/O by 90%+ for selective joins. Based on the predicate transfer technique from Yang et al. (SIGMOD 2025).

[optimizer]
broadcast_threshold = "10MB"

Multi-Endpoint Flight SQL

In Phase A, all results flow through the coordinator’s single Flight SQL endpoint. Phase B adds multi-endpoint support: get_flight_info can return multiple FlightEndpoint entries, each pointing to a different worker. The client fetches results directly from workers, bypassing the coordinator for data transfer.

This eliminates the coordinator NIC bottleneck for large result sets. A query returning 4GB across 4 workers streams 1GB directly from each worker to the client, achieving 4x the effective bandwidth.

Stage Decomposition

The coordinator decomposes the physical plan into stages:

1. Scan stage – workers read Parquet files, apply predicates and projections.
2. Shuffle stage – workers hash-partition or range-partition output for the next stage.
3. Join/Aggregate stage – workers perform local joins or aggregations on shuffled data.
4. Final stage – coordinator (or a designated worker) performs final aggregation, sort, or limit.

Each stage boundary is a shuffle point. The coordinator tracks stage completion and triggers the next stage when all workers in the current stage have finished.

Memory Model

SQE uses a four-level watermark system to manage memory pressure:

The FairSpillPool divides the total memory_limit equally among all registered MemoryConsumer instances. When a consumer’s try_grow call would push the pool past the orange threshold, the pool asks other spillable consumers to spill first. If spilling frees enough memory, the allocation succeeds. If not, the allocation fails with ResourceExhausted.

Per-operator behavior:

• SortExec – spills sorted runs to disk, later merged via k-way merge.
• HashAggregateExec – spills partition groups to disk (when supported by DataFusion).
• HashJoinExec – not spillable upstream; SQE rewrites to SortMergeJoin when estimated size exceeds threshold.
• SortMergeJoinExec – both sides sort-and-spill independently, then merge with constant memory.

Configuration Reference

Benchmark Results

TPC-H at scale factor 1 (approximately 1GB of data) on a coordinator with 512MB memory and spill-to-disk enabled:

• 21 of 22 queries pass. All queries produce correct results within the memory budget.
• 1 failure: q18. TPC-H query 18 uses a high-cardinality GROUP BY with HAVING that produces millions of intermediate groups. DataFusion’s GroupedHashAggregate does not yet support spill-to-disk for hash aggregation, so the operator exceeds the 512MB limit. This is a known upstream limitation. With Phase B’s two-phase aggregation (distributing the groups across workers), q18 passes.

These results demonstrate that SQE can run analytical workloads on hardware that would be considered undersized for traditional query engines. The combination of spill-to-disk, late materialization, and scan planning keeps memory usage bounded regardless of data size.

Security and trust model

This page states SQE’s trust boundaries plainly: what is gated per user, what is not, and what a compromised component can reach. It describes the model as documented and shipping today, and marks the gaps. Where a control is designed but not yet built, it says so and links the limitation. For the SQL surface of access control, see GRANT and REVOKE. For the runtime controls (TLS, rate limiting, timeouts, error sanitization), see Security & Policy.

Identity: the user’s token is the credential

SQE has no service account. A client authenticates with a username and password (or a pre-minted JWT, or one of the other configured providers), and the coordinator exchanges that for an OIDC token. The token becomes the credential for the rest of the query. There is no second, broader identity that the engine falls back to for the catalog. See Authentication Flow.

In client-credentials mode (the lightweight test stack, where the catalog itself issues tokens) the username from the handshake is informational, used for session labelling and audit. The catalog requests still carry the service token. In OIDC password-grant mode, the token is the user’s own.

The metadata boundary: gated per user

Catalog operations carry the user’s bearer token. Listing namespaces, loading a table, committing a snapshot: all go to the catalog as Authorization: Bearer <user token>, and the catalog enforces what that user may see and do. SHOW TABLES returns only tables the user can access. A write commits as the user. The metadata path is gated per user.

The read data path: a known gap

Here the boundary is weaker than the metadata path, and it is a documented limitation, not a hidden one.

Writes use per-table credentials vended by the catalog. INSERT, MERGE, and DELETE go through the loaded table’s file IO, which carries the credentials the catalog returned for that table.

Reads do not. The coordinator reads data files with the static S3 key configured in the [storage] section. That key is the same for every user, and it is not scoped per table or per query. Per-user read credential vending (the catalog returns short-lived, table-scoped credentials and SQE reads with those) is designed but not yet built. See S3 Credential Vending and Limitations.

The consequence to hold in mind: a user who passes the catalog’s permission check for a table can have its data files read on their behalf with the static storage key. The catalog gates which tables a user can address; the read data path is not separately gated per user. Scope the [storage] key to the minimum the engine needs to function.

Policy enforcement: off by default

SQE parses a fine-grained access-control SQL surface: column masks (GRANT ... MASKED WITH), row filters (GRANT ... ROWS WHERE), SHOW EFFECTIVE GRANTS, and CHECK ACCESS. The enforcement model is plan rewriting: filters and masks are injected into the logical plan before DataFusion’s optimizer runs, so the optimizer cannot push a user predicate through a mask to probe raw values. The design follows the PostgreSQL row-level-security model, with no information leakage.

The enforcement is off by default: the default [policy] engine = "passthrough" returns plans unmodified. Two enforcers are shipped and wired, and you turn enforcement on by selecting one. ranger reads row-filter and column-mask policies from an Apache Ranger query service and feeds the plan rewriter; in-memory keeps grants in a hash map for dev and tests. The opa and cedar engines are defined in config but not yet wired (selecting them errors today). Because the Ranger backend reads the same query service-def that Apache Spark reads through its Kyuubi authorization plugin, one policy enforces byte-identically in SQE and in Spark. See Fine-grained access control for the how-to, Spark / Ranger Parity for the cross-engine result, and GRANT and REVOKE for the engine config.

The TVF object-store boundary

The file-format table-valued functions (read_parquet, read_csv, read_json, read_delta) read external files directly. They can address local filesystem paths and arbitrary object-store URLs. When inline credentials are omitted, they fall back to the configured [storage] credentials. Inline credentials are passed as SQL literals; SQE’s audit logger redacts values matching access_key, secret_key, and session_token patterns. See read_parquet TVF.

The boundary to reason about: a SQL-capable user can direct these functions at whatever the engine’s storage identity can reach. The trust boundary is the engine’s storage credentials and host filesystem access, not the user’s. Scope the engine’s storage credentials minimally and run it with least-privilege filesystem access, the same posture you would take for any service that reads paths supplied at query time.

What a compromised worker can reach

Workers are stateless. They hold no catalog state and no persistent credentials. The coordinator ships a worker a secured plan fragment plus the user’s bearer token for the query, and the worker executes against storage. A worker reads data files for the queries it is assigned. It does not hold the long-lived OIDC client secret, and it does not have its own standing identity to the catalog.

In distributed mode the coordinator and workers share a secret that authenticates worker registration and the credential push. The engine refuses to start when the coordinator URL or worker URLs are set with an empty worker secret. See Worker Secret.

A compromised worker can therefore reach the storage reachable with the credentials pushed to it for the queries it runs. The mitigation tracks the read-path gap above: today reads use the static [storage] key, so a compromised worker that observes that key sees the same broad storage reach the coordinator has. Per-user, per-table vended credentials would narrow that blast radius; they are not yet built for the read path.

Transport

Flight SQL connections can run with TLS, and optional mTLS adds client-certificate verification. TLS is off unless both a certificate and key are configured. See TLS in Configuration and Security & Policy. Run with TLS in any deployment where the network between client and coordinator, or coordinator and workers, is not already trusted.

Summary of boundaries

Research Papers

The streaming execution engine draws on decades of database systems research. This chapter lists the papers that most directly influenced SQE’s design, with notes on how each idea is implemented.

Papers and Their Influence

Detailed Notes

Graefe, “Volcano” (1994)

Volcano introduced the exchange operator as the universal mechanism for parallelism in query evaluation. An exchange operator sits between two plan tree segments and handles data redistribution – hash partitioning, round-robin, or broadcast – without the operators above or below knowing about parallelism. SQE’s DoExchange shuffle is a direct implementation of this model over Arrow Flight’s bidirectional streaming RPC. Each shuffle boundary in the stage decomposition corresponds to an exchange operator. The key insight from Volcano that SQE preserves: the operators themselves are single-threaded and unaware of distribution; all parallelism is encapsulated in the exchange.

Shapiro, “Hybrid Hash Join” (1986)

Shapiro’s hybrid hash join partitions the build side into buckets, keeping one bucket in memory and spilling the rest to disk. During the probe phase, the in-memory bucket is probed immediately; the spilled buckets are read back and probed sequentially. This is the standard approach for hash join spill in systems like PostgreSQL and Trino. DataFusion does not yet implement hash join spill upstream, so SQE cannot use this technique directly. Instead, SQE falls back to SortMergeJoin for large joins – both sides are sorted (spilling via external merge sort) and then merged with constant memory. The SortMergeJoin fallback is the safe path; when DataFusion adds hash join spill, SQE can adopt the hybrid approach from Shapiro for better performance on unsorted inputs.

Sethi et al., “Presto: SQL on Everything” (2019)

Presto’s architecture – a stateless coordinator that plans and schedules, stateless workers that execute, no shared storage between them – is the direct model for SQE’s coordinator/worker split. The paper describes Presto’s evolution from a no-spill engine (all intermediate data in memory) to one that supports spill-to-disk under memory pressure. SQE followed the same evolution: Phase A added coordinator spill (the “safe” path), and Phase B added distributed computation (pushing work to workers so the coordinator handles less data). The paper’s observation that “most queries fit in memory; spill is for the tail” matches SQE’s experience – 20 of 22 TPC-H queries run without spill on 512MB; only the largest sorts and aggregations trigger it.

Leis et al., “Morsel-Driven Parallelism” (2014)

The morsel-driven model assigns small, fixed-size chunks of work (morsels) to worker threads, enabling NUMA-aware scheduling without explicit thread pinning. DataFusion’s pull-based execution model, where each operator produces batches on demand, is conceptually similar. SQE’s batch sizing (default 8192 rows per RecordBatch) is informed by this paper’s finding that small, uniform work units lead to better load balancing and cache utilization. The paper also highlights the importance of avoiding global synchronization in the hot path – a principle SQE follows by using per-operator memory consumers and tokio::sync::watch channels for credential refresh rather than shared mutexes.

Pedreira et al., “Velox” (2022)

Velox introduces cooperative memory arbitration: operators register with a central arbitrator and respond to memory pressure by spilling or shrinking their buffers. This is more sophisticated than DataFusion’s FairSpillPool, which divides memory equally and triggers spill when any consumer exceeds its share. Velox’s arbitrator can make global decisions – asking a low-priority operator to spill so a high-priority one can proceed. SQE does not implement priority-based arbitration today, but the FairSpillPool’s watermark system (green/yellow/orange/red) provides a simpler version of the same concept. The Velox paper’s cooperative model is the planned future direction for SQE’s memory management, particularly for mixed workloads where interactive queries should preempt batch jobs.

Raasveldt & Muhleisen, “DuckDB” (2019)

DuckDB proves that a single-node engine with proper buffer management can process datasets far larger than available memory. Its out-of-core hash join and sort implementations use disk-backed buffers that transparently page data in and out. SQE’s Phase A is built on the same principle: spill-to-disk is not an error path, it is the normal execution path for large queries on small machines. The difference is that SQE operates over remote storage (S3) rather than local files, so the I/O pipeline (coalescing, footer cache, prefetch) is more critical. DuckDB’s benchmark results – 1TB queries on 16GB machines – provided the confidence that SQE’s 512MB target was achievable with the right memory management.

Pang et al., “Memory-Adaptive External Sorting” (1993)

This paper addresses the problem of external sorting when available memory fluctuates during execution (due to other concurrent operators). The key technique is dynamic run splitting: instead of committing to a fixed run size at the start of the sort, the algorithm adapts the run size based on currently available memory. SQE’s FairSpillPool implements a version of this: as other operators allocate and release memory, the pool available to a SortExec changes. When memory shrinks (another query starts), the sort produces smaller runs and spills more frequently. When memory grows (another query finishes), the sort can produce larger runs. The k-way merge at the end adapts to whatever set of runs was produced.

Abadi et al., “Materialization Strategies” (2007)

Abadi’s paper compares early materialization (read all columns, then filter) with late materialization (read predicate columns, filter, then read remaining columns for survivors) in column-oriented databases. Late materialization wins when predicates are selective because it avoids reading non-predicate columns for filtered-out rows. SQE implements late materialization in the Iceberg scan layer: the scan planner splits the column set into predicate columns and projection-only columns, reads the predicate columns first, applies the RowFilter, and then reads the projection columns only for rows that survived the filter. For a query like SELECT * FROM orders WHERE status = 'CLOSED' on a table where 5% of rows match, this reduces column-chunk reads by up to 19x (for a 20-column table where status is one column).

Yang et al., “Predicate Transfer” (2025)

Predicate transfer pushes join key values from the build side of a join to the probe side’s scan, filtering probe-side data before it enters the join operator. In the simplest form, the distinct join keys from the build side are collected into an IN-list and injected as a predicate on the probe side’s table scan. SQE implements this for distributed joins: after the build side is scanned and its distinct keys are known, the coordinator pushes the key set to probe-side workers as a scan predicate. Combined with Iceberg’s file-level min/max statistics, this can skip entire data files on the probe side. For selective joins (e.g., a dimension table join where only 100 of 10,000 distinct key values appear), predicate transfer skips 90%+ of probe-side files, dramatically reducing I/O. This is particularly effective for star-schema queries common in analytical workloads.

SQL Support

SQE inherits DataFusion’s broad SQL support and adds Iceberg-specific operations.

Query Language

SELECT & Expressions

-- Full ANSI SQL
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY total DESC
LIMIT 10;

-- CTEs
WITH monthly AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total
    FROM orders GROUP BY 1
)
SELECT month, total, LAG(total) OVER (ORDER BY month) AS prev_month
FROM monthly;

-- Subqueries, EXISTS, IN
SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 500);

Higher-order array functions

SQE parses SQL with the DuckDB dialect, so Trino-style lambda syntax (x -> expr) is accepted. The DuckDB dialect keeps the same identifier quoting as the default parser; it just adds lambda support. Two higher-order array functions are aliased onto DataFusion 54’s built-ins:

SELECT filter(ARRAY[1, 2, 3, 4], x -> x > 2);                  -- [3, 4]        (array_filter)
SELECT transform(ARRAY[1, 2, 3], x -> x * 10);                 -- [10, 20, 30]  (array_transform)
SELECT any_match(ARRAY[1, 2, 3], x -> x > 2);                  -- true          (array_any_match)
SELECT all_match(ARRAY[2, 4, 6], x -> x % 2 = 0);              -- true          (SQE UDF)
SELECT none_match(ARRAY[1, 3, 5], x -> x % 2 = 0);             -- true          (SQE UDF)
SELECT reduce(ARRAY[1, 2, 3, 4], 0, (s, x) -> s + x, s -> s);  -- 10            (SQE UDF)

All six Trino array higher-order functions are covered. See the array and map reference.

Window Functions

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - LAG(salary) OVER (ORDER BY hire_date) AS salary_diff
FROM employees;

Supported: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, CUME_DIST, PERCENT_RANK, with PARTITION BY, ORDER BY, and frame clauses (ROWS BETWEEN, RANGE BETWEEN).

Joins

-- All join types
SELECT * FROM a INNER JOIN b ON a.id = b.id;
SELECT * FROM a LEFT JOIN b ON a.id = b.id;
SELECT * FROM a RIGHT JOIN b ON a.id = b.id;
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id;
SELECT * FROM a CROSS JOIN b;

-- Anti and semi joins (via EXISTS/NOT EXISTS)
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);

Set Operations

SELECT id FROM a UNION ALL SELECT id FROM b;
SELECT id FROM a INTERSECT SELECT id FROM b;
SELECT id FROM a EXCEPT SELECT id FROM b;

DDL

-- Schemas
CREATE SCHEMA analytics;
DROP SCHEMA staging;

-- Tables (via CTAS)
CREATE TABLE analytics.summary AS
SELECT region, SUM(revenue) AS total FROM sales GROUP BY region;

-- CREATE OR REPLACE
CREATE OR REPLACE TABLE analytics.summary AS
SELECT region, SUM(revenue) AS total FROM sales GROUP BY region;

-- Views
CREATE VIEW active_customers AS
SELECT * FROM customers WHERE status = 'active';

DROP VIEW active_customers;

-- Drop
DROP TABLE analytics.summary;
DROP TABLE IF EXISTS analytics.summary;

DML

-- Insert from query
INSERT INTO target_table
SELECT * FROM source_table WHERE condition;

-- CTAS
CREATE TABLE new_table AS SELECT * FROM existing_table;

-- DELETE (Copy-on-Write by default)
DELETE FROM orders WHERE status = 'cancelled';
DELETE FROM orders WHERE customer_id IN (SELECT id FROM blacklist);

-- DELETE (Merge-on-Read; opt in via table property)
ALTER TABLE orders SET TBLPROPERTIES ('write.delete.mode' = 'merge-on-read');
DELETE FROM orders WHERE status = 'cancelled';  -- writes a position delete file

-- UPDATE (Copy-on-Write)
UPDATE orders SET status = 'shipped' WHERE tracking_id IS NOT NULL;
UPDATE orders SET amount = CASE WHEN amount > 1000 THEN amount * 0.9 ELSE amount END;

-- MERGE INTO (Copy-on-Write)
MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN UPDATE SET value = source.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (source.id, source.value);

All row-level write operations (DELETE, UPDATE, MERGE INTO) default to Copy-on-Write via the RisingWave iceberg-rust fork’s rewrite_files() transaction API. Affected data files are read, filtered/transformed, and rewritten as new files in a single atomic commit.

DELETE also supports Merge-on-Read when write.delete.mode = 'merge-on-read' is set on the table. SQE writes a position-delete file (or an equality-delete file when the table declares an identifier-field-id) and commits via FastAppendAction / RowDeltaAction. MoR avoids rewriting whole data files for small deletes against large tables.

Data Types

SQE accepts the standard ANSI SQL type set plus a few Iceberg-specific extensions:

CREATE TABLE events (
    id              BIGINT,
    payload         JSON,                       -- Aliases to Utf8 underneath
    occurred_at     TIMESTAMP(6),
    occurred_time   TIME(6),                    -- Time-of-day, microseconds
    occurred_at_tz  TIMESTAMP(6) WITH TIME ZONE,
    occurred_ns     TIMESTAMP_NS,               -- V3-only: nanosecond precision
    region_id       INTEGER,
    amount          DECIMAL(18, 2)
);

• JSON columns store as Utf8. CAST(json_col AS BIGINT|VARCHAR|DOUBLE) rides DataFusion’s built-in coercion. JSON-shaped extraction works through json_extract, json_extract_scalar, json_array_length, json_parse, json_get_str, json_get_int, json_get_float, json_get_bool.
• TIME / TIME(p) maps to Arrow Time64(Microsecond) since Iceberg’s time primitive is microsecond-only across V2 and V3. Precisions 0..=6 collapse to microsecond. TIME(p > 6) rejects with a clear NotImplemented; use TIMESTAMP(9) for sub-microsecond resolution.
• TIME WITH TIME ZONE rejects at CREATE TABLE: Arrow has no equivalent. Use TIMESTAMP WITH TIME ZONE instead.
• TIMESTAMP_NS (and TIMESTAMP_NS WITH TIME ZONE) is a V3-only nanosecond timestamp. SQE auto-upgrades the table to format-version 3 when one of these types appears in a CREATE.
• localtime() returns Time64. EXTRACT(HOUR|MINUTE|SECOND FROM time_col) works through the Trino-aliased hour() / minute() / second() UDFs. year() / month() / day() on a TIME column raise a clear plan error per Trino spec.

Metadata Queries

SHOW CATALOGS;
SHOW SCHEMAS;
SHOW TABLES;
SHOW TABLES IN schema_name;

-- information_schema
SELECT * FROM information_schema.tables;
SELECT * FROM information_schema.schemata;
SELECT * FROM information_schema.columns WHERE table_name = 'orders';

-- Query plan (logical + physical)
EXPLAIN SELECT * FROM orders WHERE amount > 100;

-- With actual execution metrics
EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 100;

-- With Iceberg file/row estimates (no execution)
EXPLAIN FULL SELECT * FROM orders WHERE amount > 100;

Feature Comparison

Query Plan Inspection (EXPLAIN)

SQE provides three variants of EXPLAIN for inspecting how queries are planned and executed.

EXPLAIN

Returns the logical and physical query plan without executing the query.

EXPLAIN SELECT * FROM orders WHERE amount > 100;

Output: Two rows, logical_plan and physical_plan, each containing a text representation of the plan tree. The plan shown is the policy-enforced plan: any row filters or column masks applied by the security layer are visible.

EXPLAIN ANALYZE

Executes the query and returns per-operator timing and row counts.

EXPLAIN ANALYZE
SELECT dept_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY dept_id;

Output columns: step, operation, output_rows, elapsed_ms

Rows are ordered leaf-to-root (execution order). output_rows and elapsed_ms are NULL for operators that do not expose DataFusion metrics.

EXPLAIN FULL

Returns the plan enriched with Iceberg table statistics, without executing the query.

EXPLAIN FULL SELECT * FROM large_table WHERE region = 'EU';

Output columns: step, operation, estimated_rows, estimated_bytes, files_scanned, files_total

For IcebergScanExec nodes, statistics come from the Iceberg snapshot summary (fast, no data file reads). estimated_rows reflects the total rows in the snapshot at plan time. files_scanned equals files_total because predicate-pushdown to file level is not yet implemented.

For other operators (Filter, Aggregate, Sort) estimated_rows comes from DataFusion’s cardinality analysis where available; file columns are NULL.

Notes

• All three variants apply policy enforcement. The plan reflects what will actually execute for the authenticated user.
• EXPLAIN FULL on non-Iceberg tables (e.g., information_schema) returns NULL for all statistics columns without error.

Custom SQL Extensions

SQE extends standard SQL with statements for security policy management. These are parsed by wrapping sqlparser-rs. We don’t fork the parser.

Status: Shipped. The parser handles the custom ROWS WHERE and MASKED WITH clauses and the policy engine enforces them via plan rewriting. Enforcement is off by default (policy.engine = passthrough, access_control.backend = none), so a default open-source deployment runs without it. See GRANT and REVOKE for the full SQL surface, backends, and known gaps.

Policy Statements

GRANT with Row Filter

GRANT SELECT ON schema.table TO ROLE role_name
  ROWS WHERE condition;

Example:

-- Analysts can only see European data
GRANT SELECT ON sales.orders TO ROLE eu_analyst
  ROWS WHERE region = 'EU';

-- Finance team sees only their cost center
GRANT SELECT ON hr.expenses TO ROLE finance
  ROWS WHERE cost_center = current_user_attr('cost_center');

GRANT with Column Mask

GRANT SELECT ON schema.table TO ROLE role_name
  MASKED WITH (column AS expression);

Example:

-- Support sees masked SSN (last 4 digits only)
GRANT SELECT ON customers TO ROLE support
  MASKED WITH (ssn AS '***-**-' || RIGHT(ssn, 4));

-- Partial email masking
GRANT SELECT ON users TO ROLE viewer
  MASKED WITH (email AS CONCAT(LEFT(email, 2), '***@', SPLIT_PART(email, '@', 2)));

REVOKE

REVOKE SELECT ON schema.table FROM ROLE role_name;

SHOW Statements

-- All grants on a table
SHOW GRANTS ON schema.table;

-- Effective grants for a user (combines all grants, resolves role inheritance)
SHOW EFFECTIVE GRANTS FOR USER "alice";

Parser Strategy

SQE wraps sqlparser-rs rather than forking it:

graph LR
    SQL["SQL Input"] --> SP["sqlparser-rs<br/>(standard parse)"]
    SP --> AST["Standard AST"]
    AST --> PP["Post-Parse Transform"]
    PP -->|Standard SQL| STD["Standard Statement"]
    PP -->|GRANT with ROWS WHERE| PS["PolicyStatement::GrantRowFilter"]
    PP -->|GRANT with MASKED WITH| PS2["PolicyStatement::GrantColumnMask"]
    PP -->|SHOW GRANTS| PS3["PolicyStatement::ShowGrants"]

The post-parse transform detects GRANT/REVOKE statements with the custom extensions and converts them to PolicyStatement AST nodes. Standard GRANT/REVOKE (without extensions) passes through unchanged.

Statement Classification

Every SQL statement is classified for routing, metrics, and audit:

#![allow(unused)]
fn main() {
pub enum StatementKind {
    Query,          // SELECT
    Ctas,           // CREATE TABLE AS SELECT
    Insert,         // INSERT INTO
    Merge,          // MERGE INTO
    Delete,         // DELETE FROM
    Drop,           // DROP TABLE/VIEW
    Rename,         // ALTER TABLE RENAME
    CreateView,     // CREATE VIEW
    DropView,       // DROP VIEW
    CreateSchema,   // CREATE SCHEMA
    DropSchema,     // DROP SCHEMA
    ShowCatalogs,   // SHOW CATALOGS
    ShowSchemas,    // SHOW SCHEMAS
    ShowTables,     // SHOW TABLES
    Policy,         // GRANT, REVOKE
    Utility,        // EXPLAIN, SET, etc.
}
}

Each kind maps to a stable lowercase label ("query", "ctas", "insert") used in Prometheus metrics and audit logs.

Iceberg Integration

SQE is built on the iceberg-rust library (vendored fork) and speaks the Iceberg REST Catalog protocol natively. Iceberg is the only table format SQE supports.

Iceberg version

• iceberg-rust: SQE-rebased fork of risingwavelabs/iceberg-rust dev_rebase_main_20260303 at commit c034b19105fa, vendored at vendor/iceberg-rust/. Provides RewriteFilesAction / OverwriteFilesAction (Copy-on-Write DELETE/UPDATE), PositionDeleteFileWriter (Merge-on-Read position deletes), and DeletionVectorWriter (Iceberg V3) on top of upstream v0.9.0.
• DataFusion: 54.0
• Arrow: 58
• Parquet: 58
• Iceberg table format: V2 and V3. V3 features verified end-to-end (TIMESTAMP_NS, column defaults, equality-delete UPDATE on identifier-fields, partition evolution).

The matrix score against icebergmatrix.org is 167/189 (88.4%), placing SQE fifth on the public scoreboard behind only Spark distributions (EMR, AWS Glue, OSS Spark, Dataproc). See getsqe.com/compare/iceberg for the per-cell breakdown and the side-by-side V2/V3 comparison against every other engine on the public scoreboard.

Architecture

graph TB
    subgraph SQE
        SC["SessionCatalog<br/>per-user token"]
        TP["IcebergTableProvider<br/>DataFusion TableProvider"]
        WR["Writer<br/>Parquet output"]
    end

    SC -->|REST API + bearer or SigV4| CAT["Catalog backend<br/>Polaris, Nessie, Glue REST,<br/>S3 Tables, Unity, HMS, JDBC, Hadoop"]
    CAT -->|table metadata| SC
    CAT -->|S3 credentials<br/>credential vending| SC

    TP -->|read Parquet| OS["Object storage<br/>AWS S3, GCS, ADLS Gen2, R2,<br/>Ceph, MinIO, rustfs"]
    WR -->|write Parquet| OS

    SC -->|commit| CAT

Supported catalogs

SQE keeps the catalog choice as a runtime configuration concern. Every catalog below ships compiled in by default; pick one in [catalog.backend]. See Catalog backends for the full per-backend recipe with TOML examples and verification queries. For where the data files live (S3, R2, GCS, ADLS Gen2, HTTPS, hf://), see Storage backends.

There are two ways into AWS-managed Iceberg. The REST path (type = "rest" with the Glue REST or S3 Tables REST endpoint as catalog_url) speaks the Iceberg REST protocol that AWS exposes on top of these services. The native SDK path (type = "glue" or type = "s3tables") goes directly through aws-sdk-glue or aws-sdk-s3tables without REST. Both work; the SDK path is currently more maintained upstream and avoids the SigV4 signing patches.

The five non-Hadoop backends share one dispatch path through the upstream iceberg-catalog-loader crate. SQE’s for_session_other_backend translates the typed CatalogBackend enum into a uniform (catalog_type, name, props) tuple and hands it to iceberg_catalog_loader::load(type). The loader picks the right CatalogBuilder (REST / Glue / S3 Tables / HMS / SQL) and returns an Arc<dyn iceberg::Catalog>. Hadoop is the lone outlier because it is filesystem-only; its dispatch stays in sqe-catalog/src/backends/hadoop.rs.

Two SQE-only patches sit on top of the vendored loader. The first feature-gates each backend so a slim build does not transitively pull every backend’s AWS SDK / Thrift / sqlx weight. The second adds Send + Sync to BoxedCatalogBuilder so the boxed builder can cross await points in async contexts. Both are documented in vendor/iceberg-rust/README.md and forward-compatible with upstream.

Live integration tests for HMS, Nessie, JDBC Postgres, AWS Glue, AWS S3 Tables, and Unity OSS live in sqe-catalog/tests/backends_integration.rs. Each is #[ignore] and runs against a docker-compose overlay or a real cloud account configured via .env. Glue and S3 Tables are also live-verified (2026-05-05) against AWS account 123456789012 in eu-example-1 and eu-example-2.

The AWS REST endpoints share the OSS Iceberg REST code path. Phase P added an aws-sigv4 cargo feature to the vendored iceberg-catalog-rest crate that swaps the OAuth/Bearer authenticator for an AWS SigV4 signer when rest.sigv4-enabled=true lands in the catalog properties (or in the server’s /v1/config defaults). The signer reads credentials from the standard AWS provider chain.

Catalog REST surface

For Polaris, Nessie, Unity OSS, AWS Glue, and AWS S3 Tables, SQE talks to the catalog via the Iceberg REST API. Key interactions:

Every request includes the user’s bearer token (Polaris, Unity, Nessie, anything OIDC) or is signed with AWS SigV4 (Glue, S3 Tables). The catalog enforces access control.

Credential vending

When SQE loads a table, the catalog returns the table metadata and scoped storage credentials for accessing the data files:

sequenceDiagram
    participant SQE
    participant Catalog
    participant Storage as S3 / Object store

    SQE->>Catalog: Load table (user token / SigV4)
    Catalog-->>SQE: Table metadata<br/>+ scoped storage credentials<br/>(STS / table-scoped key)
    Note over SQE: Credentials are per-user,<br/>per-table, time-limited
    SQE->>Storage: Read Parquet (scoped credentials)
    Storage-->>SQE: Data (allowed prefix only)

This means:

• No service account with broad storage access.
• Each user’s storage access is scoped to exactly the tables they are querying.
• Credentials are short-lived (STS or equivalent).

Read path

graph LR
    PLAN["LogicalPlan<br/>TableScan"] --> META["Load table metadata<br/>from catalog"]
    META --> PRUNE["Partition pruning<br/>(manifest filtering)"]
    PRUNE --> FILES["Data file list"]
    FILES --> READ["Read Parquet<br/>(columnar, predicate pushdown,<br/>row-group skipping, RowFilter)"]
    READ --> BATCH["Arrow RecordBatches"]

Read-side optimizations:

• Partition pruning: Iceberg manifest stats skip whole partitions that cannot match the query predicate.
• Column projection: only requested columns leave Parquet.
• Predicate pushdown: filters land at the row group level, the page-index level, and the Parquet RowFilter.
• Runtime filter pushdown: Phase P shipped a DynamicPredicate API that absorbs DataFusion 54 hash-join build-side runtime filters into the same pruning surface. SF10 TPC-H lineitem-heavy queries saw q06 -51%, q07 -31%, q14 -33%. Engineering log at Runtime filter pushdown.
• Bloom filter consultation: write.parquet.bloom-filter-columns lands bloom offsets in the file footer; DataFusion consults them automatically for literal equality predicates at scan time.
• 5-layer caching: REST catalog cache, table metadata cache, manifest cache, SessionContext cache, OAuth token cache. Warm queries hit sub-millisecond planning.

Write path

graph LR
    SQL["CTAS / INSERT / DELETE / UPDATE / MERGE"] --> EXEC["Execute SELECT"]
    EXEC --> BATCH["RecordBatches"]
    BATCH --> WRITE["Write Parquet<br/>(WriterProperties from table props)"]
    WRITE --> COMMIT["Commit to Iceberg<br/>(append / row-delta / rewrite)"]

Supported DML, both V2 and V3 verified:

• CREATE TABLE AS SELECT: Apache Iceberg V2 and V3, including TIMESTAMP_NS columns and DEFAULT literals.
• INSERT INTO: streaming, with proper schema validation against the catalog.
• DELETE FROM: Copy-on-Write via RewriteFilesAction, or Merge-on-Read via PositionDeleteFileWriter when write.delete.mode=merge-on-read.
• UPDATE: CoW or MoR (equality deletes when the table declares an identifier-field-id).
• MERGE INTO: full WHEN MATCHED / WHEN NOT MATCHED semantics, dispatching to CoW or MoR based on the table’s write.merge.mode (default copy-on-write).
• ALTER TABLE: ADD/DROP/RENAME COLUMN, SET/DROP NOT NULL, type promotion, ADD/DROP/REPLACE PARTITION FIELD (partition evolution), CREATE/DROP BRANCH/TAG, SET WRITE BRANCH.

DELETE reads write.delete.mode, UPDATE reads write.update.mode, and MERGE reads write.merge.mode. Each defaults to copy-on-write. CTAS and INSERT stream RecordBatches straight to Parquet at constant memory. Partitioned writes use an unbounded writer by default. Set fanout_max_open_writers or fanout_buffer_budget to opt into a bounded fanout writer that caps the number of open per-partition writers and flushes the least-recently-written one when a limit is hit.

The writer respects write.parquet.bloom-filter-columns and write.parquet.bloom-filter-fpp for any column the schema knows about. The footer-inspection test in sqe-catalog/src/parquet_writer_config.rs proves bloom offsets land in the resulting Parquet file.

Maintenance procedures

SQE runs table maintenance through CALL system.* procedures, each committing through the vendored iceberg-rust transaction actions:

• rewrite_data_files: bin-packs small files into larger ones, with target_file_size_bytes, min_input_files, and max_concurrent_file_group_rewrites options.
• expire_snapshots: drops snapshots older than a cutoff, honouring older_than and retain_last.
• remove_orphan_files: deletes data files no snapshot references.
• rewrite_manifests: compacts manifest files.
• register_table and drop_table (without purge): catalog-pointer operations for migration, disaster recovery, and the golden-dataset workflow.

Snapshot-pointer procedures (set_current_snapshot, rollback_to_snapshot) and drop_table(purge => true) are not yet implemented and return an explicit error.

V3 features verified

• TIMESTAMP_NS / TIMESTAMPTZ_NS: V3 nanosecond timestamps round-trip end-to-end.
• Column defaults: CREATE TABLE ... DEFAULT <literal> applies write_default; ALTER TABLE ADD COLUMN ... DEFAULT applies initial_default.
• Position deletes (V3): MoR DELETE on a V3 table writes position-delete files.
• Equality deletes (V3): UPDATE with a declared identifier-field-id commits a single RowDelta with new data file plus equality-delete row.
• Partition evolution (V3): ALTER TABLE ADD/DROP/REPLACE PARTITION FIELD evolves the spec on V3 tables, including with day(ts) on TIMESTAMP_NS columns.
• Time travel (V3): FOR SYSTEM_TIME AS OF and FOR VERSION AS OF work against V3 tables through the same snapshot walk as V2.
• Schema evolution (V3): ADD COLUMN, DROP COLUMN, RENAME COLUMN, SET DATA TYPE all work on V3.

V3 features still blocked upstream:

• Variant: pending iceberg-rust #2188.
• Geometry: pending DataFusion UDT #12644.
• Vector / Embedding: V3 spec not finalised.

The deferred list is tracked in docs/evidence/iceberg-matrix-state.json under caveats for each cell.

Write Path

SQE supports writing data to Iceberg tables through SQL. Writes go through the coordinator, which executes the SELECT portion, writes Parquet files to S3, and commits to the Iceberg catalog.

Supported Operations

CREATE TABLE AS SELECT (CTAS)

CREATE TABLE analytics.monthly_sales AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    region,
    SUM(amount) AS total
FROM raw.orders
GROUP BY 1, 2;

Flow:

1. Parse SQL, extract target table name and SELECT query
2. Execute SELECT to get Arrow RecordBatches
3. Convert Arrow schema to Iceberg schema
4. Create table in the Iceberg catalog
5. Stream RecordBatches to Parquet files at constant memory
6. Commit data files to Iceberg via AppendAction

Add PARTITIONED BY (year(ts), bucket(16, id), ...) to partition on write. The standard Iceberg transforms (year, month, day, hour, bucket, truncate, identity) are parsed and applied. Partitioned writes use an unbounded writer by default; set fanout_max_open_writers or fanout_buffer_budget to opt into a bounded fanout writer that caps open per-partition writers and flushes the least-recently-written one when a limit is hit.

CREATE OR REPLACE TABLE

CREATE OR REPLACE TABLE analytics.monthly_sales AS
SELECT ... ;

Drops the existing table (if it exists) and creates a new one. Useful for dbt table materializations.

INSERT INTO

INSERT INTO analytics.monthly_sales
SELECT
    DATE_TRUNC('month', order_date) AS month,
    region,
    SUM(amount) AS total
FROM raw.orders
WHERE order_date >= '2024-06-01'
GROUP BY 1, 2;

Flow:

1. Parse SQL, extract target table and SELECT query
2. Execute SELECT to get Arrow RecordBatches
3. Stream RecordBatches to Parquet files at constant memory
4. Commit data files to Iceberg via AppendAction (new snapshot)

Write Architecture

sequenceDiagram
    participant QH as Query Handler
    participant DF as DataFusion
    participant WH as Write Handler
    participant S3
    participant POL as Polaris

    QH->>DF: Execute SELECT query
    DF-->>QH: RecordBatches

    QH->>WH: handle_ctas(table_name, schema, batches)
    WH->>POL: Create table (schema)
    POL-->>WH: Table created

    WH->>S3: Write Parquet data files
    S3-->>WH: Written (paths + sizes)

    WH->>POL: Commit AppendAction<br/>(data file list → new snapshot)
    POL-->>WH: Committed
    WH-->>QH: Success

Row-Level Operations

DELETE, UPDATE, and MERGE each choose Copy-on-Write or Merge-on-Read per operation, read from a table property: write.delete.mode, write.update.mode, and write.merge.mode. Each defaults to copy-on-write.

Copy-on-Write reads the affected data files, filters or transforms the rows, and rewrites them as new files in a single atomic commit through RewriteFilesAction. Merge-on-Read leaves the data files in place and writes delete files instead: position deletes for DELETE, equality deletes for UPDATE (when the table declares an identifier-field-id), and a RowDeltaAction for MERGE. Copy-on-Write keeps reads fast; Merge-on-Read keeps writes cheap.

DELETE FROM

DELETE FROM sales.orders WHERE status = 'cancelled';

-- Cross-table subqueries in WHERE
DELETE FROM sales.orders
WHERE customer_id IN (SELECT id FROM blacklist);

-- DELETE without WHERE = truncate
DELETE FROM sales.orders;

Flow:

1. Scan table metadata to identify affected data files
2. Read each affected file, apply the WHERE filter
3. If all rows match: mark file for removal
4. If partial match: rewrite file without matching rows
5. Commit via RewriteFilesAction (remove old files, add rewritten files)

Under write.delete.mode=merge-on-read, DELETE instead writes position-delete files and commits a RowDeltaAction, leaving the data files untouched.

UPDATE

UPDATE sales.orders SET status = 'shipped' WHERE tracking_id IS NOT NULL;

-- CASE WHEN transformations
UPDATE sales.orders SET amount = CASE
    WHEN amount > 1000 THEN amount * 0.9
    ELSE amount
END;

Flow:

1. Scan table metadata to identify affected data files
2. Read each affected file, apply the WHERE filter
3. For matching rows: apply SET expressions
4. Rewrite file with modified rows
5. Commit via RewriteFilesAction

Under write.update.mode=merge-on-read, UPDATE writes a new data file plus an equality-delete file in one RowDeltaAction, provided the table declares an identifier-field-id.

MERGE INTO

MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN UPDATE SET value = source.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (source.id, source.value);

Flow:

1. Execute a full outer join of source and target via DataFusion
2. Classify each result row: matched (UPDATE/DELETE) or not matched (INSERT)
3. Rewrite affected target data files with modifications applied
4. Add new data files for INSERT rows
5. Commit via RewriteFilesAction (remove old files, add new + rewritten files)

Under write.merge.mode=merge-on-read, MERGE routes matched and unmatched rows through a RowDeltaAction (position/equality deletes plus new data files) instead of rewriting the target files.

Iceberg Dependency

Row-level writes depend on the SQE-rebased fork of risingwavelabs/iceberg-rust, vendored at vendor/iceberg-rust/. It provides RewriteFilesAction / OverwriteFilesAction for Copy-on-Write, PositionDeleteFileWriter and EqualityDeleteFileWriter with RowDeltaAction for Merge-on-Read, and DeletionVectorWriter for Iceberg V3, none of which are available upstream yet. SQE also carries its own vendor patches (feature-gated catalog backends, Send + Sync catalog builders, ADD COLUMN scan projection) and owns write-path components outside the vendor tree, such as the bounded fanout writer. See vendor/iceberg-rust/README.md for the patch inventory.

Memory Safety

Large writes used to be a way to run the coordinator out of memory. The write path now bounds its own memory in three ways.

The straight-line paths stream at constant memory. CTAS and INSERT write Parquet in a streaming loop rather than collecting the full result set first. Flight DoPut ingest streams the upload instead of buffering it. Row group by row group, the peak stays flat regardless of how many rows the SELECT produces.

Some row-level paths still have to buffer. Copy-on-Write MERGE reads the target rows, and UPDATE, DELETE, and Merge-on-Read decode each affected file before rewriting it. Those buffers register against the DataFusion memory pool (Layer A). When a buffer would exceed the pool, the write fails with a typed ResourceExhausted error instead of OOM-killing the process. The query dies, the coordinator lives, and the caller gets a clear reason.

Partitioned writes can open one Parquet writer per partition, and a high-cardinality partition key multiplies that into memory pressure. The bounded fanout writer caps the number of concurrently open per-partition writers. When a batch arrives for a new partition and the map is full, the least-recently-written writer closes and flushes first, then the new one opens. Cutover trades bounded memory for small-file debt, which CALL system.rewrite_data_files repairs. The bounded writer is opt-in: set fanout_max_open_writers or fanout_buffer_budget (see Configuration) to turn it on. Left unset, partitioned writes use the unbounded writer.

Two knobs stay off by default until validated against a live catalog. merge_target_streaming streams a Copy-on-Write MERGE target from the pinned data files through the merge join as governed operator memory, instead of buffering the whole target. write_buffer_tracking is a diagnostic escape hatch: it defaults on, and setting it false disables the Layer A reservations (never the streaming paths) if a deployment ever hits an accounting false positive.

Maintenance

Table maintenance runs through CALL system.* procedures: rewrite_data_files (bin-packs small files), expire_snapshots, remove_orphan_files, rewrite_manifests, plus register_table and drop_table. See Iceberg Integration for the full surface.

dbt Compatibility

The write path is designed to support dbt Core via a native dbt-sqe adapter:

Autonomous Compaction

Iceberg tables accumulate small files under normal write traffic: frequent appends, streaming ingest, and Merge-on-Read DELETE/UPDATE/MERGE statements that leave position and equality delete files behind. Left alone, small-file counts and delete debt both grow, and every scan pays for it: more files to open, more delete files to apply, worse pruning.

SQE compacts tables two ways. Run it on demand with CALL system.rewrite_data_files(...), the same procedure Trino and Spark expose under their own optimize/rewrite_data_files syntax. Or opt a table into the autonomous path: a background scheduler that watches opted-in tables, reports their compaction debt, and, once you trust it, compacts them on a cron schedule with no human in the loop.

This page is the orienting map. The full sizing knobs live in Configuration, the procedure arguments live in CALL procedures, and the distributed dispatch mechanics live in Distributed compaction.

What it does

A compaction rewrite reads a group of small data files, applies any Merge-on-Read deletes that cover them so deleted rows never reappear, and writes new, larger Parquet files in their place. The whole operation is one Iceberg commit: old files out, new files in, atomically. A strategy argument picks the layout, and one more argument targets delete-heavy files regardless of strategy:

• strategy => 'binpack' (default). Groups small files by partition and rolls them up to target_file_size_bytes (512 MiB by default). Cheapest option, no reordering.
• strategy => 'sort' with sort_order. Gathers a whole partition into one stream, sorts it by the given column list through a spillable DataFusion sort, and rolls the output at the target size. Sorting the partition as a single stream is what makes the result prunable: output files land with disjoint key ranges instead of each file spanning the full domain, so predicate pushdown skips more files at scan time.
• sort_order => 'zorder(col_a, col_b)'. Z-order clustering for queries that filter on different subsets of several columns at once. Iceberg’s sort-order metadata cannot express z-order, so SQE stamps none for this case, matching Spark’s behavior.
• delete_file_threshold => N. Rewrites any data file with at least N delete files applying to it, even if the file is already at or above the target size. The threshold is the lever for delete-heavy Merge-on-Read tables, where the file itself is fine but every scan pays to apply a stack of deletes against it.
• rewrite_all => true. Forces a rewrite of every data file, including files already at or above the target size and partitions below min_input_files. It applies all deletes and re-encodes at the target size. Use it to force a clean pass after a schema or partition-spec change, or to apply accumulated deletes across a whole table in one commit. Off by default; subsumed by strategy => 'sort', which already rewrites the whole partition.

CALL system.table_health('ns', 't') is the read-only companion: it reuses the same file-collection and bin-pack logic as a rewrite, but never writes anything, so a SELECT-only session can check compaction debt before deciding whether to run one. It reports live and small file counts, delete and delete-heavy file counts, eligible bin-pack groups, and estimated rewrite bytes. See CALL procedures for the full argument list and column reference for both procedures.

Autonomy: advisory before active

The [maintenance] config block gates the background path with a mode ladder, and every step up that ladder is a decision an operator makes explicitly, never a default:

• mode = "off" (the default). No maintenance principal is built, no scheduler task runs. Total absence, not a loop that declines to fire.
• mode = "advisory". The scheduler discovers opted-in tables and publishes the same health report CALL system.table_health returns. Nothing is rewritten.
• mode = "active". The scheduler commits real rewrites against tables that are both due and opted in, through the same handler CALL system.rewrite_data_files uses interactively.

Run advisory mode first. Let it report real compaction debt against your actual write pattern before opting a table into active mode. Active mode mutates data files and commits snapshots; treat the switch from advisory to active as a reviewed, per-table decision, not a rollout.

A table only ever gets touched by the active scheduler when three things line up: the global mode is advisory or active, the table owner has set sqe.maintenance.enabled = true via ALTER TABLE ... SET TBLPROPERTIES, and the maintenance principal holds a TABLE_WRITE_DATA grant on that table’s namespace in Polaris. Miss any one of the three and nothing happens to that table. Full detail on the gates, the cron schedule, and per-table overrides for the sizing knobs is in Configuration.

A dedicated, isolated principal

The background scheduler never runs as you, and it never runs as the service account behind interactive queries either. [maintenance.principal] configures a separate OAuth2 client-credentials identity, used solely by the maintenance path. It is not added to the interactive auth chain, so there is no code path by which a query session could authenticate as it, even by accident.

Give this principal the least privilege it needs: TABLE_READ_DATA for advisory analysis, TABLE_WRITE_DATA added only for tables opted into active mode, never CREATE or DROP or admin grants. Polaris enforces that boundary server-side, on top of SQE’s own opt-in and mode gates.

Distribution: coordinator-local or the worker fleet

[maintenance.distribution] mode decides whether an active-mode rewrite runs on the coordinator alone or fans its file groups out across the worker fleet:

• auto (default). Coordinator-local below min_workers healthy workers, fans out once the fleet reaches that floor.
• local. Always coordinator-local, even with a healthy fleet.
• require. Always fans out. Below min_workers it does not fall back quietly: a scheduled tick skips the job loudly, and a manual CALL ... distributed => 'require' errors outright.

In the distributed path, workers read data files, apply deletes, sort if requested, and write new Parquet directly against S3, using their own S3 credentials and no catalog token at all. Commit authority never leaves the coordinator: it validates every worker’s output, re-checks the row-count invariant across the whole job, and commits one atomic snapshot that swaps every old file for every new file at once. By default a job either commits everything or nothing. Opting into [maintenance.distribution] partial_progress trades that guarantee for incremental batch commits on very large tables, at the cost of a larger commit-conflict surface. See Configuration for the knob, and Distributed compaction for the full planning, dispatch, and commit flow, including the partial-progress commit model.

High availability

Running the scheduler on more than one coordinator needs a way to stop two of them compacting the same table in the same window. [maintenance.scheduler] lease picks the guard:

• none. No lease. Only safe for a single-coordinator deployment, and validation requires an explicit single_scheduler_acknowledged = true before it will accept this setting with the scheduler enabled.
• catalog (default). Before dispatching a rewrite, the scheduler claims a lease row in the state table. A coordinator that finds the lease already held skips its tick for that table; a crashed holder’s claim expires after lease_ttl_secs.

The lease is an efficiency mechanism, not the source of correctness. Correctness comes from Iceberg’s optimistic-concurrency commit: if a lease operation fails, or two coordinators somehow race to compact the same table, exactly one of them wins the commit and the other re-plans against the new snapshot and finds nothing left to do. The lease only saves the loser the cost of a redundant scan and rewrite. The alternative HA shape, running with the in-process scheduler disabled everywhere and driving timing from an external Kubernetes CronJob with concurrencyPolicy: Forbid, needs no lease at all, because there is only ever one caller in flight.

Audited and reversible

Every advisory-mode analysis and every active-mode commit emits an AuditKind::Maintenance audit event. Job history, per-table last-run state, and lease rows live in sqe_system.maintenance_log, a normal SQL table an operator creates once and queries like any other: filter it by status, table, or time range to see what the scheduler has actually done.

A compaction commit is an ordinary Iceberg snapshot. The files it superseded stay in place until expire_snapshots removes them, which means an autonomous compaction is reversible within the snapshot-retention window. Read the table’s snapshot history, find the snapshot before the compaction landed, and run:

CALL system.rollback_to_snapshot(table => 'analytics.events', snapshot_id => 8472810294831234567);

That single call points the table back at the prior snapshot and undoes the compaction, as long as that snapshot has not aged out.

Getting started: advisory to active

1. Check debt, no config changes required. CALL system.table_health(table => 'ns.t') works regardless of maintenance.mode, on any session with SELECT on the table. Use it to see whether a table is worth compacting at all.
2. Turn on advisory mode. Set [maintenance] mode = "advisory", add a [maintenance.principal] block, and set scheduler.enabled = true. mode = "active" comes later, in step 4. Let advisory mode run against your real tables long enough to see debt accumulate and validate the cron schedule.
3. Opt one table in. ALTER TABLE ns.t SET TBLPROPERTIES ('sqe.maintenance.enabled' = 'true'), and grant the maintenance principal TABLE_WRITE_DATA on that table’s namespace in Polaris.
4. Flip the global mode to active. Only opted-in tables with the write grant are ever touched; every other table keeps behaving exactly as it did under advisory.
5. Watch sqe_system.maintenance_log. Confirm jobs land as success, not skipped or failed, before opting in more tables.

Run manual CALL system.rewrite_data_files(...) calls at any point in this progression: the manual path and the autonomous path are the same handler, and neither depends on the other being configured.

Access control tutorial

A working walkthrough of both halves of SQE’s access control, in the order you would actually build them: first decide who may open a table at all, then decide which rows and columns they see inside it.

Every statement here runs against quickstart/polaris-ranger-keycloak. The same ground is covered as an executable transcript by scripts/access-control-demo.sh (32 steps, exits non-zero on any mismatch) and asserted on decoded Arrow values by make test-access-control (23 cases). If something in this page disagrees with those, they are right and this is stale.

The two gates

        GRANT / REVOKE / DENY                 Ranger query service
                 |                          (row filters, masks, tags)
                 v                                    |
  ranger "polaris" service                            v
                 |                            SQE plan rewriter
                 v                                    |
  Polaris embedded authorizer  --> table --> DataFusion --> rows
       "may you open it"                  "what do you see"

Gate one is Polaris. GRANT and REVOKE in SQE become policies on the Ranger polaris service, and Polaris’s embedded Ranger authorizer enforces them when SQE asks to load a table. This answers may this user open this object at all. SQE does no filtering here. A denial arrives as “table not found”, because Polaris hides rather than forbids.

Gate two is SQE. Row filters, column masks and column restriction are applied by rewriting the logical plan before DataFusion optimizes it, from policies on a Ranger query service. This answers which rows and columns may this user see.

They are independent, they use different Ranger services, and a query must pass both. Revoking the coarse SELECT denies the query at Polaris before any mask is ever computed, which is why the order in this tutorial matters: a mask you cannot observe is a mask you cannot debug.

Part 1: the Polaris gate

1.1 One grant, three policies

Start with the thing that trips up every first attempt, because the mechanism explains most of what follows. A table grant is not one policy. Reaching sales_wh.acdemo.orders needs three things to succeed, and only one of them is about the table:

• LIST_NAMESPACES, authorized at the catalog level with namespace-list. Polaris does not use Ranger’s SELF_OR_DESCENDANTS matching, so a namespace-scoped namespace-list will not do: listing is denied outright, not filtered.
• a per-namespace visibility probe (LOAD_NAMESPACE_METADATA) needing namespace-level namespace-properties-read. A 403 hides the namespace, deliberately, so ungranted namespace names do not leak.
• the table’s own access types, at the table level.

Either of the first two failing gives an empty schema list, and planning stops at “table not found” without ever attempting LOAD_TABLE. The table exists and Polaris would serve it; SQE never asks. Nothing in the log shows a 403, because there is no denial to report.

You write one statement. SQE writes all three policies:

GRANT SELECT ON sales_wh.acdemo.orders TO ROLE "analyst";

That is the same plan grant-profile.json v4 specifies, which is what the data-platform control plane generates its policies from. Matching it is deliberate: both write to the same Ranger service, and a SQL grant that produced different policies from the equivalent API call would make “who granted this” unanswerable.

Know what the catalog level costs. Its holder can enumerate every namespace NAME in the catalog, including namespaces unrelated to the table you granted. A namespace called pii_customer_health becomes visible even though not one of its rows is. That is a real widening and it happens on every table grant, so treat SHOW SCHEMAS as a leak surface and keep namespace names free of anything you would not put in a ticket title. If a catalog holds namespaces whose very existence is sensitive, separate catalogs are the boundary that works.

MANAGE and ALL are the exception. They bind at the catalog level already and carry catalog-content-manage, so there is nothing above them to add and the plan is a single policy.

Revoke touches the deepest level only, on purpose. REVOKE SELECT on the table removes the table policy and leaves the catalog and namespace policies alone. Those are shared with every other grant anyone holds in that catalog, so walking the whole plan backwards would strip discovery out from under unrelated grants: an outage dressed up as a narrow revoke. The cost is that traversal policies accumulate and nobody cleans them up. That is the right trade. An orphaned namespace-list is discovery on a catalog the grantee could already reach; over-revoking is an outage.

To take discovery back, do it explicitly and in this order:

REVOKE USAGE ON DATABASE sales_wh        FROM ROLE "analyst";
REVOKE USAGE ON SCHEMA   sales_wh.acdemo FROM ROLE "analyst";

Revoking discovery first is the tidier order, and it also avoids a known defect. A principal left holding catalog discovery while every namespace under it is invisible gets an empty schema list, and on a current-thread tokio runtime SQE’s catalog provider blocks in its sync-to-async bridge instead of reporting “table not found”. A deployed coordinator runs a multi-thread runtime and denies normally, so this is not something a served query hits; it shows up in tests and would affect an embedded single-threaded host. Recorded in docs/internal/research/2026-08-02-catalog-traversal-gate.md.

1.2 A grant is what enables a read

With the traversal in place, the grant is observable. Before:

-- as alice
SELECT id FROM sales_wh.acdemo.orders;
-- table 'sales_wh.acdemo.orders' not found

Grant it, wait for the Polaris plugin to poll (5 to 30 seconds; it is not instant), and the same statement returns rows:

-- as carol, an admin
GRANT SELECT ON sales_wh.acdemo.orders TO ROLE "analyst";

-- as alice, a member of analyst
SELECT id, region FROM sales_wh.acdemo.orders ORDER BY id;
--  id | region
-- ----+--------
--   1 | EU
--   2 | US
--   3 | EU

REVOKE puts it back:

REVOKE SELECT ON sales_wh.acdemo.orders FROM ROLE "analyst";

Revoking one privilege does not disturb another. Ranger permits a single policy per resource, so every grant on a table shares one item and their access types union. INSERT requires everything SELECT does, which means a literal REVOKE INSERT would strip the read access too. SQE labels each grant (chm:<GRANTEE_TYPE>:<name>:<PRIVILEGE>) and holds back the access types another labelled privilege still needs, so narrowing a user from read-write to read-only does what it says.

The chm prefix is not SQE’s own. The data-platform control plane writes to the same Ranger service and reads the same labels, so both tools have to agree on the format or each is blind to the other’s grants and cascades over them. A group grantee is labelled ROLE, because a Keycloak group is materialised as a Ranger role of the identical name.

Two properties worth internalising:

A role grant reaches only role members. dave is in no role, so the grant above does nothing for him. Role membership lives in Ranger, not in the token: Polaris ignores the token’s realm roles because they lack the PRINCIPAL_ROLE: prefix it expects.

Read does not imply write. SELECT and INSERT are separate privileges and map to disjoint access-type sets. An analyst holding SELECT gets a denial on INSERT until GRANT INSERT is issued too.

1.3 Privileges and the level each binds to

Two things follow from that right-hand column.

One privilege expands to many access types. The Polaris embedded authorizer does not honour service-def implied-grants, so SQE lists every access type the operation will check. INSERT is 22 of them, because committing an Iceberg snapshot fans out into many fine-grained Polaris operations.

The level is not advisory. Naming an object deeper than a privilege’s level used to silently widen the grant. GRANT ALL ON sales_wh.acdemo.orders dropped the namespace and table and wrote catalog-content-manage on sales_wh: one table named, success reported, the whole catalog conferred. SQE now refuses it and names the scope that would have been written:

Privilege 'ALL PRIVILEGES' binds to the catalog level, but the statement names
a namespace or table. The policy would apply to 'sales_wh' and everything under
it, which is wider than the object named. Re-issue the statement against
'sales_wh', or name a privilege that binds to the object you meant.

USAGE on a table and CREATE SCHEMA on a namespace widen through the same path and are refused the same way.

1.4 Wildcards: all and future

GRANT SELECT ON ALL TABLES IN SCHEMA sales_wh.acdemo TO ROLE "analyst";
GRANT SELECT ON FUTURE TABLES IN SCHEMA sales_wh.acdemo TO ROLE "analyst";

Both write the same policy, with table = "*", so both cover existing and future tables. Ranger has no future-only resource. Snowflake distinguishes the two; SQE cannot, and treats ON FUTURE as a superset rather than rejecting it. Use a table-specific grant when you mean one existing table.

Do not confuse either with GRANT ... ON SCHEMA, which stays a namespace resource and does not reach the tables inside it. Namespace USAGE is namespace-list plus namespace-properties-read and deliberately carries no table-data-read.

1.5 Views

A view has no resource level of its own. Its NAME goes in the table slot and the access types are the view-* set:

CREATE OR REPLACE VIEW sales_wh.acdemo.orders_eu AS
  SELECT id, region FROM sales_wh.acdemo.orders WHERE region = 'EU';

GRANT SELECT ON VIEW sales_wh.acdemo.orders_eu TO ROLE "analyst";

SHOW GRANTS ON sales_wh.acdemo.orders_eu;
-- view-properties-read | sales_wh.acdemo.orders_eu | ROLE | analyst | ALLOW
-- view-list            | sales_wh.acdemo.orders_eu | ROLE | analyst | ALLOW

Note what is absent: no table-data-read.

A view is not a privilege boundary. SQE expands the view and plans against its base tables, so the reader needs a grant on orders as well. This is the opposite of a Snowflake secure view, where the view owner’s privileges stand in for the reader’s. Never use a view to hand out indirect access to a table.

What a view does give you is masking and filtering that cannot be dodged, which is Part 2.

1.6 DENY

DENY SELECT ON sales_wh.acdemo.orders TO USER dave;

Deny beats allow in Ranger, so this overrides any grant dave holds directly or through a role. It is idempotent (re-issuing updates the same policy rather than stacking), reversible with REVOKE, and audited as a privilege change.

One caveat, deliberate: DENY goes through Ranger’s policy API, which authorizes the authenticated REST user rather than a named grantor. Unlike GRANT it is therefore not resource-scoped to the caller, and the admin_roles config gate is the only check. Ranger offers no grantor-scoped deny, so DENY stays admin-only even under the ranger-delegate setting below.

1.7 Who may grant

By default a session needs a role from [auth] admin_roles before it may issue GRANT or REVOKE at all, and then Ranger checks delegateAdmin on the resource. Both have to pass, which means WITH GRANT OPTION on its own does nothing for a user without an engine-wide admin role.

To let table owners manage their own tables, hand the decision to Ranger:

[access_control]
grant_authority = "ranger-delegate"

Then this works, as dave, holding no admin role:

-- as an admin, once: dave owns the table, bob can see the catalog and namespace
GRANT SELECT ON sales_wh.acdemo.orders TO USER dave WITH GRANT OPTION;
GRANT USAGE ON DATABASE sales_wh TO USER bob;
GRANT USAGE ON SCHEMA sales_wh.acdemo TO USER bob;

-- as dave, no admin role:
GRANT SELECT ON sales_wh.acdemo.orders TO USER bob;

The two USAGE statements are not decoration. A table grant writes three policies (catalog, namespace, table) because reaching a table needs discovery above it, and Ranger’s delegateAdmin does not cascade upward: dave owns the table and is refused 403 on the catalog. SQE skips a traversal level the grantee already holds, so once bob has discovery, dave’s grant only has to write the level he owns. A grantee with no discovery yet gets an error naming the level and these statements.

Before switching, read the Ranger policies. ranger-delegate widens who may grant to everyone holding delegateAdmin, and the quickstart’s wildcard discovery policy gives roles analyst and engineer exactly that for the discovery access types.

1.8 Introspection

SHOW GRANTS ON sales_wh.acdemo.orders;

Reads the policies back out of Ranger, one row per (access type, grantee).

CHECK ACCESS SELECT ON sales_wh.acdemo.orders FOR USER "alice";
--  allowed | reason
-- ---------+---------------------------
--  true    | Allowed via ROLE 'analyst'

CHECK ACCESS resolves the target user’s Ranger roles, including nested roles, and applies deny-overrides-allow. It is best-effort introspection, not the enforcement path: it does not account for tag policies, conditions, or wildcard resource matching beyond exact match and bare *. Polaris remains authoritative.

It does not resolve groups, because Ranger only learns a user’s groups when usersync runs. A grant reachable only through a group will not show up here.

Part 2: the SQE data gate

Everything in Part 2 is enforced by SQE, from a Ranger query service, and is invisible to gate one. A user must already hold SELECT for any of it to be observable.

Policies here are authored with SQE’s Databricks-inspired CREATE POLICY SQL. SQE translates the statement to Ranger, which remains the shared source of truth for SQE and Spark/Kyuubi. Ranger’s console and REST API remain available for external administration.

Resolved policies are cached. A mask tightened in the console is not honoured until the cached entry expires, up to [policy.ranger] cache-ttl-secs. Grants issued through SQE flush the cache on commit, so only console-authored changes have that window.

2.1 Column masks

A column mask scoped to a table and column:

CREATE OR REPLACE POLICY acdemo_mask_ssn
ON TABLE sales_wh.acdemo.orders
COLUMN MASK MASK_SHOW_LAST_4 TO ROLE engineer ON COLUMN ssn;

The database value is the namespace, not the catalog. bob (an engineer) then sees the masked value while alice (analyst only) sees the raw one, from the same statement against the same table. That contrast is the point: a mask is per-principal, not per-column.

The full Ranger built-in vocabulary is implemented:

Masks also block predicate pushdown on the raw value. WHERE ssn = '111-11-1111' evaluates against the masked value, never the underlying one, so a mask cannot be peeled off with a filter.

2.2 Row filters

The row-filter expression is ordinary SQL:

CREATE OR REPLACE POLICY acdemo_rowfilter_eu
ON TABLE sales_wh.acdemo.orders
ROW FILTER TO ROLE engineer USING (region = 'EU');

The filter is injected above the TableScan, before optimization, so the user’s own predicates can be pushed through it but not around it. Multiple applicable filters AND together.

Session functions are const-folded per session, which is how one policy serves many principals:

region = current_user() OR is_role_in_session('auditor')

current_user(), current_role() and is_role_in_session() are available.

One caveat. A row filter referencing a column the view does not project fails the query when read through that view:

Plan rewrite failed: Internal error: Failed to create policy filter:
Schema error: No field named region.

Fail-closed, so nothing leaks, but the message names neither the policy nor the view. The same filter with the same narrow projection in a direct query works. Until this is fixed, a row filter and a narrow view over the same table are mutually exclusive.

2.3 Column restriction

A mask SQE cannot build is not returned raw. The column is nullified in place and stays in the schema, so SELECT that_column still plans rather than erroring on an unknown field. This is the fail-closed path, and it is what you get from a CUSTOM mask with no expression, or a mask type carrying another component’s prefix such as trino:MASK_NULL.

2.4 Tags

Tags let one rule protect a column wherever it appears, instead of one policy per table. There are two halves, and they live in different places.

Association: which columns carry which tag. In SQE, on the Iceberg table property sqe.column-tags, written with SQL:

ALTER TABLE sales_wh.acdemo.orders SET TAGS (ssn = ('PII'), region = ('GEO'));
SHOW TAGS ON sales_wh.acdemo.orders;
ALTER TABLE sales_wh.acdemo.orders UNSET TAGS (region);

SET TAGS merges rather than replaces, so a previous tag on another column survives. Flushing the policy cache is part of the statement.

The rule: what the tag means. SQL writes a policy to Ranger’s linked tag service (SQE discovers the service and component-qualified vocabulary):

CREATE OR REPLACE POLICY acdemo_tag_pii
ON TAG PII
COLUMN MASK MASK_SHOW_LAST_4 TO ROLE engineer;

Two details that will cost you an afternoon each:

Mask types must be component-qualified. hive:MASK_SHOW_LAST_4, never the bare name. The tag service definition does not define bare names.

Tag row filters need a Ranger Admin property. Tag masks work out of the box. Tag row filters need this in ranger-admin-site.xml:

<property>
  <name>ranger.servicedef.autopropagate.rowfilterdef.to.tag</name>
  <value>true</value>
</property>

Ranger copies each component’s dataMaskDef into the tag service definition unconditionally, but copies its rowFilterDef only when that property is true, and it defaults to false. No Ranger upgrade changes this. Without it the POST is rejected with “tag policy can specify values for one of the following resource sets: does not have any resource hierarchies”, which names resource hierarchies rather than the missing capability.

A tag is not a protection

This is the one thing people get backwards.

A tag with no policy is not a protection, so there is nothing to fail closed about. A tag whose policy names a mask SQE cannot build IS a protection SQE cannot honour, so the column is restricted. Tagging a column does not protect it. The rule in Ranger is what protects it.

Spark parity

Masks are shared with Spark through the same query service. Associations are not: Spark reads tag associations from the Ranger or Atlas tag store, while SQE reads them from Iceberg table properties. One mask rule, two association sources.

One enforcement detail does not carry over, and it is pinned by scripts/access-control-parity-demo.sh: Kyuubi places its masking projection below its row-filter marker, so a row filter that reads a tag-masked column compares the mask value and matches nothing, where SQE matches on the stored value. No data leaks either way. Mask precedence used to be a second difference and is not any more, because policy.mask-precedence now defaults to tag.

2.5 Precedence

1. Restriction beats mask. A column SQE cannot safely return is nullified, whatever the mask says.
2. A tag mask beats a resource mask, by default. This matches the Ranger plugin order Spark/Kyuubi uses, so the same policy set renders the same value in both engines. Set policy.mask-precedence = "resource" for the most-specific-rule-wins reading instead.
3. Row filters AND together.
4. Row filters read stored values. A filter is evaluated before masks are applied, so masking a filtered column does not change which rows survive.
5. Deny beats allow, on gate one.

2.6 What happens when things break

Everything fails closed except the cache, which is deliberately fail-stale and bounded by its TTL.

Putting both gates together

An analyst who may read European orders, without ever seeing an SSN:

-- Gate one: may they open it
GRANT USAGE  ON DATABASE sales_wh        TO ROLE "analyst";
GRANT USAGE  ON SCHEMA   sales_wh.acdemo TO ROLE "analyst";
GRANT SELECT ON sales_wh.acdemo.orders   TO ROLE "analyst";

-- Gate two: what they see (author on the query service)
--   policyType 2, filterExpr "region = 'EU'",       roles ["analyst"]
--   policyType 1, column ssn, MASK_SHOW_LAST_4,     roles ["analyst"]

-- Verify gate one
CHECK ACCESS SELECT ON sales_wh.acdemo.orders FOR USER "alice";
-- true | Allowed via ROLE 'analyst'

-- Verify gate two by reading as alice
SELECT id, region, ssn FROM sales_wh.acdemo.orders ORDER BY id;
--  id | region | ssn
-- ----+--------+-------------
--   1 | EU     | xxx-xx-1111
--   3 | EU     | xxx-xx-3333

Two rows, not three, and no raw SSN. If you see three rows the filter has not landed yet; if you see the raw SSN the mask has not. Check the TTL before changing anything.

Where to go next

• Access control: support matrix for what is supported and what is proven, with the test names.
• Fine-grained access control for configuration keys and the mask vocabulary in reference form.
• Ranger access control for the catalog path, the identity model, and why role membership lives in Ranger.
• Fine-grained enforcement for the plan-rewrite internals and the precedence contract.
• Polaris + Ranger + Keycloak quickstart for a stack that runs all of it.

Fine-grained access control

SQE enforces row filters and column masks by rewriting the query’s logical plan before DataFusion optimizes it. Filters and masks are injected above the table scan, so the optimizer cannot push a user predicate through a mask to probe raw values. The model follows PostgreSQL row-level security: denied rows are invisible, masked columns return transformed values, and there is no information leakage.

The headline is where the policy comes from. SQE reads a Ranger service of servicedef type hive, named query in the quickstarts, the same instance Apache Spark reads through its Kyuubi authorization plugin. One policy, written once in the Ranger console, enforces byte-identically in SQE and in Spark: an SSN masked to xxx-xx-1111 reads the same no matter which engine ran the query. See Spark / Ranger Parity for the validated cross-engine result and its edges.

Enforcement is off by default

The default [policy] engine = "passthrough" returns plans unmodified. Turn enforcement on by selecting an engine:

• ranger reads row-filter and column-mask policies from that Ranger instance and feeds the plan rewriter. This is the production path, and the one shared with Spark/Kyuubi.
• in-memory keeps grants in a hash map, for development and tests.
• opa and cedar are defined in config but not yet wired (selecting them errors today).

Configure the Ranger backend

[policy]
engine = "ranger"
mask-precedence = "tag"        # which mask wins on a column covered by both (default: "tag")

[policy.ranger]
url = "http://ranger-admin:6080"
service-name = "query"         # the Ranger instance to read; shared with Spark/Kyuubi (default: "hive")
admin-user = "admin"
# Set the password via SQE_POLICY__RANGER__ADMIN_PASSWORD, not in the file.
admin-password = ""
cache-ttl-secs = 30            # resolved-policy cache TTL

mask-precedence settles the one case where a column is covered twice, by a resource policy naming it and by a tag policy matching its classification. tag is the default and matches the plugin order Hive and Spark/Kyuubi implement, so a rule authored once in Ranger renders the same value whichever engine reads it. resource restores the narrower most-specific-rule-wins reading. The column is masked either way; only which mask applies changes.

[policy.ranger] is distinct from [access_control.ranger]. The [policy] block points at the frontend-query service for SQE-side fine-grained enforcement (row filters and masks that SQE applies). The [access_control] block points at the polaris service for the coarse GRANT-to-catalog path where Polaris enforces. They can target the same Ranger Admin host and read different services. See GRANT and REVOKE for the two-axis model.

Column masks

SQE realizes the full Ranger hive-servicedef built-in mask set. Each Ranger dataMaskType maps to a mask SQE applies in the rewritten plan:

Character counting is by Unicode scalar, matching Hive, which is what makes the output byte-identical to Spark. Anything SQE cannot map, including a CUSTOM expression that fails to parse, restricts the column instead of leaking it. Masking is fail-closed.

Row filters

A Ranger row-filter policy attaches a boolean SQL expression to a table for a user or role. SQE parses it and injects it as a filter above the scan, so a user sees only the rows the expression admits. Row-filter expressions can reference session context.

Role-conditional masking

Row filters and CUSTOM masks can call session-context functions: current_user(), current_role(), and is_role_in_session(). SQE const-folds them per session before the plan is distributed, so a fragment running on a worker carries the resolved value rather than re-evaluating identity. That is how a single policy masks a column for an analyst but shows it to an auditor, the way Snowflake conditional masking does.

Tag-based masking

A mask can apply to every column carrying a tag rather than to a named column. The mask-per-tag rule lives in Ranger as a tag-service policy (returned in the Ranger download bundle’s tagPolicies block, shared with Spark). The tag-to-column association is authored in SQL and stored in the Iceberg sqe.column-tags table property:

ALTER TABLE sales.orders SET TAGS (ssn = ('PII'), amount = ('FINANCIAL'));
ALTER TABLE sales.orders UNSET TAGS (amount);
SHOW TAGS ON sales.orders;

The Snowflake form works too: ALTER TABLE ... MODIFY COLUMN ssn SET TAG PII = 'true'. SET TAGS merges, changing only the columns you name. Storing the association as a table property means it travels with the data through clone, rename, and replicate, and covers federated catalogs Polaris cannot gate. Tag parity with Spark stops at the association: Spark reads it from the Ranger or Atlas tag store, so full tag parity would need an Iceberg-to-Ranger tag sync, which is optional and not built.

Author the mask type under the hive: prefix (hive:MASK_SHOW_LAST_4). Ranger’s tag service definition never defines bare mask names: it aggregates the mask types of every component it can decorate, so the entries are hive:MASK_SHOW_LAST_4, trino:MASK_NULL, and so on. SQE accepts the bare and hive: forms and deliberately leaves another component’s prefix unmatched, which restricts the tagged column rather than applying another engine’s policy.

Tag row filters need one Ranger Admin property

A tag-service policy can carry a row filter as well as a mask, so every table with a column tagged PII is filtered by one rule. Ranger ships this switched off, and it is not a version limitation:

<property>
  <name>ranger.servicedef.autopropagate.rowfilterdef.to.tag</name>
  <value>true</value>
</property>

Ranger copies each component’s dataMaskDef into the tag service definition unconditionally, but copies its rowFilterDef only when that property is set in ranger-admin-site.xml. Without it, tag masks work and tag row filters cannot be authored at all: the POST is rejected with

tag policy can specify values for one of the following resource sets:
 does not have any resource hierarchies

which names resources rather than the missing capability, so it reads like a malformed policy. See Ranger tag storage for the source-level detail.

What happens when policy lookup fails

Every failure mode denies rather than falling back to unfiltered data:

Each of these is pinned by a test in crates/sqe-coordinator/tests/it/access_control_e2e.rs against a live Ranger, including one that stops the ranger-admin container mid-suite.

Admin-side edits are honored on a delay

cache-ttl-secs bounds an over-permissive window. SQE caches the resolved policy per user and table, so a mask or row filter authored directly in the Ranger console is not applied until that entry expires, up to cache-ttl-secs later. A query that ran before the edit keeps its old decision for the rest of the TTL.

Grants issued through SQE (GRANT, REVOKE) are not affected, since those flush the cache. Lower the TTL if prompt propagation of console-authored edits matters more than fetch load against Ranger Admin. The tag path re-reads the column-to-tag map on every call and has no such window.

Walkthrough

For a worked example that sets up both gates in order, with the SQL and the output at each step, see the access control tutorial. It covers the Polaris catalog gate and this fine-grained path separately, then together.

The in-engine SQL surface

Independent of Ranger, SQE parses a native grant surface (GRANT ... ROWS WHERE, GRANT ... MASKED WITH, SHOW EFFECTIVE GRANTS, CHECK ACCESS) that the in-memory engine enforces. See Security & Policy and GRANT and REVOKE.

How it fits the trust model

Fine-grained enforcement is one layer. Catalog metadata and the write path are gated per user through the caller’s bearer token; the read data path uses the engine’s storage credentials. See Security and trust model for the full boundary map, and Fine-grained Enforcement for the rewrite internals and the precedence contract.

Access control: what is supported, and what is proven

Two independent gates run on every query.

Polaris gates the catalog. GRANT and REVOKE in SQE become Apache Ranger policies on the polaris service, and Polaris’s embedded Ranger authorizer enforces them. This answers “may this user load this object at all”. SQE does no filtering on this axis.

SQE gates the data. Row filters, column masks, and column restriction are applied by rewriting the logical plan before DataFusion optimizes it, from policies read out of a Ranger query service. This answers “which rows and columns may this user see”.

A query must pass both. Revoking the coarse SELECT denies it at Polaris before any mask is computed.

Everything marked Proven below has an executable assertion in crates/sqe-coordinator/tests/it/access_control_e2e.rs, running against a live Polaris, Ranger 2.8 and Keycloak, asserting decoded Arrow values. Run it with make test-access-control. scripts/access-control-demo.sh walks the same ground as a readable SQL transcript.

Catalog gate (Polaris)

The polaris service-def resource hierarchy is root -> catalog -> {namespace -> table, principal, policy}.

One grant, three policies: the traversal is load-bearing

GRANT SELECT ON cat.ns.tbl TO USER alice writes THREE Ranger policies, not one, and the reason is on SQE’s side rather than Polaris’s.

Polaris will serve the table: a direct LOAD_TABLE with only the table-level grant returns 200. But SQE resolves a table through its catalog provider, which answers only for a namespace present in its cached namespace list, and building that list takes two calls that must both succeed:

1. LIST_NAMESPACES, authorized at the catalog level. A namespace-scoped namespace-list does not satisfy it, because Polaris does not use Ranger’s SELF_OR_DESCENDANTS matching. Listing is denied outright, never filtered.
2. A per-namespace visibility probe (LOAD_NAMESPACE_METADATA), needing namespace-level namespace-properties-read. On 403 the namespace is hidden, deliberately, so ungranted namespace names do not leak.

Either failure yields an empty schema list, and planning ends at table 'cat.ns.tbl' not found with LOAD_TABLE never attempted.

So one statement produces a three-level plan, written outermost first:

The shape matches grant-profile.json v5, which the data-platform control plane generates from. That is the point: both write to the same Ranger service, and a SQL grant producing different policies from the equivalent API call makes “who granted this” unanswerable. Pinned by a_table_grant_writes_v4s_three_level_plan and, live, by one_table_grant_writes_the_namespace_it_needs.

MANAGE and ALL bind at the catalog level already and carry catalog-content-manage, so their plan is a single policy.

The catalog level is a real widening, accepted rather than hidden. Any grantee who holds it can enumerate every namespace name in the catalog, so a name like pii_customer_health is visible even though its rows are not, and this now happens on every table grant. Separate catalogs are the boundary if namespace names are themselves sensitive. Verified on Polaris 1.7 with a clean database; recorded in docs/internal/research/2026-08-02-catalog-traversal-gate.md.

REVOKE touches the deepest level only. The catalog and namespace policies are shared with every other grant in that catalog, so walking the plan backwards would strip discovery from unrelated grants. Traversal policies therefore accumulate and are not cleaned up, which is the correct trade: an orphaned namespace-list is discovery on a catalog the grantee could already reach, whereas over-revoking is an outage. Clear it explicitly with REVOKE USAGE ON DATABASE before REVOKE USAGE ON SCHEMA (see the hang in the gap table below).

A grant must be scoped at the privilege’s own level

Each privilege binds to exactly one resource level, shown in the mapping table in Ranger access control. Naming an object deeper than that level is refused rather than widened:

GRANT ALL PRIVILEGES ON wh.sales.orders TO USER alice;

ALL binds to the catalog, so this used to drop the namespace and table and write catalog-content-manage on wh. One table was named, success was reported, and alice got every table in the catalog. SQE now errors and names the scope that would have been written. USAGE on a table and CREATE SCHEMA on a namespace widen through the same path and are refused the same way.

Views work, and are not a privilege boundary

A view has no resource level of its own. Its NAME goes in the table slot and the access types are the view-* set, which is what GRANT ... ON VIEW writes. Verified live: the resulting policy carries view-properties-read and view-list on the view coordinate and no table-data-read.

A view is not a security boundary. SQE expands the view and plans against its base tables, so the reader needs a grant there too. That is the opposite of a Snowflake secure view, where the view’s owner privileges stand in for the reader’s. Do not use a view to grant indirect access to a table.

What a view DOES give you is masking and filtering that cannot be dodged.

Column masks survive a view. There is no bypass. A view that projects a masked column returns the MASKED value, because the view expands to a TableScan of the base table and the plan rewriter runs on that scan. Verified with a user who is both an admin (so the view loads) and a member of the masked role: xxx-xx-1111 reading the base table, xxx-xx-1111 reading the view. Creating a view over a protected table is not a way around masking.

Row filters break on views when the filter references an unprojected column. This is a real defect, and it is view-specific. A row filter on region against a view declared as SELECT id, ssn FROM orders fails the whole query:

Plan rewrite failed: Internal error: Failed to create policy filter:
Schema error: No field named region.
Valid fields are ...orders.id, ...orders.ssn

The same filter on a DIRECT query with the same narrow projection (SELECT id, ssn FROM orders) works and returns the filtered rows, so this is not the general case: SQE injects the filter below the user projection and it resolves fine. Only the view path fails.

Behaviour is fail-closed (a hard error, no rows, nothing leaked) but the message is a DataFusion internal error that names neither the policy nor the view. This is the same class as Kyuubi’s #6889, which the quickstart bootstrap already cites as the reason no row-filter policy is seeded for the Spark cross-compare. Until it is fixed, a row filter and a narrow view over the same table are mutually exclusive.

Data gate (SQE plan rewriting)

Column masks

The full Ranger hive built-in vocabulary is implemented.

The hash case is asserted against a digest computed outside the engine (openssl dgst -sha256 -hmac), so the implementation is not checking itself. A plain SHA-256 of the same input is a different value, which is what proves the mask key reached the UDF.

Row filters, restriction, tags

Tag row filters need one Ranger Admin property

Tag masks work out of the box. Tag row filters need

<property>
  <name>ranger.servicedef.autopropagate.rowfilterdef.to.tag</name>
  <value>true</value>
</property>

in ranger-admin-site.xml. Ranger copies each component’s dataMaskDef into the tag service definition unconditionally but copies its rowFilterDef only when that property is true, and it defaults to false. No Ranger upgrade changes this. Without it the policy POST is rejected with “tag policy can specify values for one of the following resource sets: does not have any resource hierarchies”, which names resource hierarchies rather than the missing capability.

Also author tag mask types component-qualified (hive:MASK_SHOW_LAST_4). The tag service definition never defines bare names.

Failure behaviour

The last two rows are easy to conflate and they behave differently, so it is worth being explicit. A tag with no policy anywhere is not a protection, so there is nothing to fail closed about and the column reads normally. A tag whose policy names a mask SQE cannot build (a CUSTOM with no expression, or another component’s prefix such as trino:MASK_NULL) IS a protection SQE cannot honour, so the column is restricted. Tagging a column does not protect it by itself; the rule in Ranger is what protects it. | Unparseable row filter | becomes lit(false), deny all | Unit-tested | | Table not mappable to a policy key | deny all rows | Unit-tested |

One default is deliberately not fail-closed. The resolved-policy cache is fail-stale: a mask tightened in the Ranger console is not honored until the cached entry expires, up to [policy.ranger] cache-ttl-secs. Grants issued through SQE do not have this window, because GRANT, REVOKE and SET TAGS flush the cache on commit. The window is asserted at both edges by cache_ttl_bounds_policy_staleness.

Cross-engine parity (SQE and Spark)

Tag associations need one extra thing beyond a shared policy. They are authored into the Iceberg property sqe.column-tags, which only SQE reads, so with project-tags = true SQE ALSO writes the association into Ranger’s tag store where Kyuubi looks. If that write fails the Iceberg property is rolled back and the statement fails, because keeping it would mask the column in SQE while Spark returned it raw, and the statement would have reported success.

Tag mask types on the tag service must be component-qualified (hive:CUSTOM, not CUSTOM): Ranger’s tag servicedef aggregates each component’s mask vocabulary rather than defining bare names.

One policy in the shared frontend service, two engines, output compared directly. Per-engine checks are not enough: they pass while the engines disagree, which is the failure that matters. Both engines are pointed at the same service, and the suite is make test-access-control-spark.

Object-level parity is covered separately by spark_access_control_e2e: grants written through SQE’s GRANT statement, asserted through Spark, for read and write.

Known gaps

Where to go next

• Fine-grained access control for configuration and the mask vocabulary.
• Fine-grained enforcement for the rewrite internals and the precedence contract.
• Ranger access control for the catalog path and the identity model.
• Polaris + Ranger + Keycloak quickstart for a stack that runs all of it.

Access control: evaluation order, engine by engine

A reference for reviewers, auditors and data engineers who need to answer one question precisely: for this column, in this engine, who sees what, and which component decided?

Everything under “Measured” was observed on a live Apache Polaris 1.7, Apache Ranger 2.8, Keycloak 26.5 and Spark 3.5.9 with Kyuubi Authz 1.11.1, and has an executable assertion behind it. The comparison to Databricks and Snowflake at the end is from their product documentation, NOT measured here, and is marked as such. Treat the two kinds of claim differently.

Where the ACLs live

Five stores, and knowing which one answers a question is most of the work.

The last two hold the same information in two places. That is deliberate: the Iceberg property is the source of truth so tags travel with the table, and the Ranger tag store is a projection so foreign engines can read them. It is also the single most fragile part of the design, for reasons the gap table gives.

Order of evaluation: SQE

Numbered in the order a failure surfaces, which is not the order the documentation usually implies.

1. Namespace resolution (SQE-side, before any authorization of the table). SQE resolves a table through its catalog provider, which answers only for a namespace present in its cached namespace list. Building that list takes two calls, and BOTH must succeed:

• LIST_NAMESPACES, authorized at the catalog level via namespace-list. A namespace-scoped grant does not satisfy it, because Polaris does not use Ranger’s SELF_OR_DESCENDANTS matching. Listing is denied outright, never filtered.
• LOAD_NAMESPACE_METADATA per namespace, needing namespace-level namespace-properties-read. A 403 hides that namespace, deliberately, so ungranted names do not leak into SHOW SCHEMAS.

Either failure yields an empty schema list and the query ends at table 'cat.ns.tbl' not found, with LOAD_TABLE never attempted. Nothing in the log shows a 403, because there was no denial to report. For an auditor this is the most misleading state in the system: a permission problem that presents as a missing object.

2. Polaris authorizes LOAD_TABLE against the polaris service, using the caller’s bearer token. Needs table-properties-read and table-data-read. A denial is a 403 that SQE surfaces as “table not found”, matching the Polaris information-hiding model.

3. Polaris authorizes the write, at COMMIT. An INSERT is refused at ADD_TABLE_SNAPSHOT, not before the data is staged.

4. SQE’s plan rewriter applies the fine-grained tier, reading the query and tag services. Row filters inject as Filter nodes above the TableScan; column masks replace column references with masking expressions, before DataFusion optimizes, so the optimizer cannot push a user predicate through a mask onto raw values.

Because a single GRANT SELECT ON cat.ns.tbl has to satisfy step 1 as well as step 2, it writes three Ranger policies, outermost first:

That catalog-level policy is a real widening: any grantee holding it can enumerate every namespace name in the catalog. A name like pii_customer_health is visible even though its rows are not. Separate catalogs are the boundary when namespace names are themselves sensitive.

Order of evaluation: Spark

Different order, and the first step surprises people.

1. Kyuubi checks ITS OWN privilege first, before Polaris is consulted at all. Running in ACTIVE mode against the query service, it default-denies without a matching policyType-0 item:

org.apache.kyuubi.plugin.spark.authz.AccessControlException:
  Permission denied: user [bob] does not have [select] privilege on [ac/orders/id]

SQE ignores policyType-0 policies entirely. So the same grant that works in SQE fails in Spark, and the failure reads like a Polaris bug. The query service therefore carries one deliberate blanket allow for group public that makes Kyuubi defer, leaving object level to Polaris. It grants no data access, because Polaris still decides.

2. Polaris authorizes LOAD_TABLE, exactly as for SQE, provided Spark presented a per-user token. This is the step that is trivially skipped: with a service-account credential, Polaris authorizes the service account and the entire object tier is bypassed. Per-user identity requires

spark.sql.catalog.<c>.token=<the user's OIDC JWT>
spark.sql.catalog.<c>.token-refresh-enabled=false

The second line is load-bearing. Left at its default, Iceberg exchanges the external JWT against Polaris’s own token endpoint and the identity silently reverts to the service account.

A per-user token governs ONLY the catalog it is attached to. Any other catalog configured for the same warehouse in that session is a SEPARATE identity, and the caller chooses which one by naming it. Measured: with spark.sql.catalog.sales_wh.credential set to a service account, a user denied on a table through his own catalog reads the same table through that alias in the same session.

The session cannot defend itself. Overriding the alias’s token with the user’s JWT does not help, because Iceberg prefers credential when both are set (measured). The fix is a deployment one: remove the service-account catalog, do not shadow it.

The quickstart used to ship exactly that hazard and no longer does: no credential and no oauth2-server-uri, token-refresh-enabled pinned off, and the caller passes its own token per invocation. Two guards keep it that way, and both assert the property rather than inspecting the config file, because a config file is easy to regress:

• no_service_account_catalog_can_defeat_per_user_identity fails if naming another alias reads a table the caller was just denied.
• parity-test.sh fails if a spark-sql with no caller token can load the table at all.

A refusal without a credential is NOT the same refusal as a denied grant, and the guard asserts which one it got. With a credential the alias was a second IDENTITY. With none it has no identity, so the request never becomes an authorization question: Polaris rejects it before it can answer and Iceberg reports Unable to parse error response rather than a ForbiddenException. A test accepting either could not tell a revoked grant from a catalog nobody gave a token.

3. Kyuubi injects masks and row filters from the query and tag services.

Side by side

Precedence rules

Deny beats allow, in both engines, on both tiers. A Ranger deny item on a policy overrides an allow item on the same policy. Ranger keeps one policy per exact resource, so deny precedence is expressed by editing that policy rather than adding a second one.

A deny hits every member of the named role, including admins. Denying engineer denies anyone in engineer, and that includes an operator who holds an admin role as well. Verified the hard way: a deny on engineer locked the fixture admin out of the table and cascaded into nine unrelated test failures.

Resource masks and tag masks disagree between engines. SQE applies the resource mask; Kyuubi applies the tag mask, because stock RangerBasePlugin evaluates tag policies before resource policies. Neither leaks, since both mask. But the WEAKER of the two becomes effective for whoever picks that engine.

What to read to answer an audit question

For “who can read cat.ns.tbl”:

1. GET /service/plugins/policies/service/name/polaris, and look for policies whose resources match the table, its namespace, AND its catalog. Remember the three levels: a table-level allow with no catalog-level namespace-list is inert in SQE.
2. Check denyPolicyItems on those policies before policyItems. Deny wins.
3. Resolve Ranger ROLE membership, not OIDC claims. Polaris ignores the token’s realm roles (they lack the PRINCIPAL_ROLE: prefix), so role-based access works through Ranger role membership.

For “what does this user SEE in that table”:

4. GET /service/plugins/policies/service/name/query for policyType 1 and 2 items matching database (the last dotted namespace component), table, and column.
5. GET /service/plugins/policies/service/name/tag for tag rules, then find which columns carry those tags: SHOW TAGS ON cat.ns.tbl for SQE’s view, and GET /service/tags/download/query for the projection Spark reads. If those two disagree, the engines disagree.
6. Decide which engine the question is about, and apply the precedence row above.

For “was this refused, and by what”:

7. AccessControlException: Permission denied: user [...] means Kyuubi refused, before Polaris was consulted.
8. ForbiddenException: ... not authorized for op '...' means Polaris refused, and names the operation.
9. table not found from SQE may be a denial at step 1, with no 403 anywhere.

Gaps, and which way each one fails

Direction matters more than severity. A fail-closed gap is an outage; a fail-open gap is an incident.

How this differs from Databricks and Snowflake

From product documentation, not measured here. The architectural contrast is the part worth internalizing; treat specific feature claims as a starting point for your own check.

The structural difference

In Unity Catalog and in Snowflake, the engine is the policy authority. One system of record holds the grants, evaluates them, and enforces them. There is exactly one answer to “who can see this column”, and no possibility of two engines disagreeing, because there is only one engine.

SQE splits the roles three ways. Polaris is the object authority, Ranger is the policy store, and each engine enforces the fine-grained tier itself. That is what makes the same policy set govern SQE and Spark at once, which neither Databricks nor Snowflake offers for a foreign engine. Every divergence in the gap table above is the price of that property. If you do not need multiple engines on one policy set, you are paying for something you will not use.

Object level

Snowflake’s USAGE-on-the-path requirement is the closest analogue to SQE’s three-level expansion, and for the same underlying reason: reaching an object requires traversing to it. Snowflake makes the operator write it; SQE writes it for you and accepts the resulting namespace-name visibility.

Deny items are where SQE is genuinely ahead. Neither Databricks nor Snowflake offers a negative grant that overrides a positive one, so “everyone in analytics except contractors” is a role-modelling exercise there and a single policy item here.

Fine-grained

Snowflake’s tag-based masking is the direct analogue of SQE’s tag masks, and the comparison is instructive: Snowflake stores the tag association in its own metadata, so there is one place to look. SQE stores it in the Iceberg property so it travels with the table, then has to project it into Ranger for Spark. The sovereignty property and the consistency risk are the same design decision seen from two sides.

Views

Snowflake secure views hide the definition and evaluate with the view owner’s privileges, so a view is a genuine privilege boundary and the standard way to grant narrowed access. SQE views are not a privilege boundary. SQE expands the view and plans against the base tables, so the reader needs a grant there too. Do not use an SQE view to grant indirect access to a table.

What SQE views do give you is masking that cannot be dodged: a view projecting a masked column returns the masked value, because the rewriter runs on the base-table scan.

What to take from the comparison

If your governance model is single-engine, Unity Catalog and Snowflake give you one authority, one answer, and no projection to keep in step. That is a real advantage and this document is largely a catalogue of what it costs not to have it.

If you need Spark and SQE reading one policy set, or you need the policy store to be something you run yourself, the split model is the reason to accept the gap table above. Read it before deciding, not after.

See also

• Access control: support matrix for what is proven and by which test.
• Ranger access control for the write path and the two-tier design.
• Fine-grained access control for the mask vocabulary.

read_parquet TVF

read_parquet() is a table-valued function registered on every SQE SessionContext. It reads Parquet files from local disk or S3-compatible storage and returns them as a DataFusion table scan, making Parquet files directly queryable without first loading data into Iceberg.

Syntax

SELECT * FROM read_parquet(
  '<path>',
  [access_key => '<key>',]
  [secret_key => '<secret>',]
  [endpoint => '<url>',]
  [region => '<region>']
)

The first argument is the file path or glob pattern. All other arguments are named (keyword) parameters for S3 credentials. Named parameters are optional and fall back to the engine’s configured storage defaults when omitted.

Local files

Absolute paths and glob patterns both work:

-- Single file
SELECT * FROM read_parquet('/data/tpch/sf1/lineitem/part-0000.parquet');

-- All files in a directory
SELECT * FROM read_parquet('/data/tpch/sf1/lineitem/*.parquet');

-- Recursive glob
SELECT * FROM read_parquet('/data/tpch/sf1/lineitem/**/*.parquet');

The schema is inferred from the Parquet metadata of the first matched file. All matched files must share the same schema.

S3 with inline credentials

Pass credentials directly in the SQL statement. This is the primary mechanism used by sqe-bench load to inject credentials at load time without relying on environment variables or configuration files.

SELECT * FROM read_parquet(
  's3://bench-data/tpch/sf1/lineitem/*.parquet',
  access_key => 'AKIAIOSFODNN7EXAMPLE',
  secret_key => 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
  endpoint   => 'http://localhost:9000',
  region     => 'us-east-1'
);

All four named parameters are optional independently. Omit endpoint for AWS S3 (uses the default AWS endpoint for the given region). Omit region to default to us-east-1.

S3 with default credentials

When no inline credentials are provided, read_parquet() falls back to the storage configuration in sqe.toml:

-- Uses [storage] section from sqe.toml
SELECT * FROM read_parquet('s3://bench-data/tpch/sf1/lineitem/*.parquet');

This is convenient for internal workloads where the engine already has ambient S3 credentials configured.

Glob patterns

read_parquet() supports the same glob syntax as object_store:

For S3 paths, globbing is applied to the key prefix after the bucket name.

Using with CTAS for data loading

The primary use case for read_parquet() is ingesting external Parquet data into Iceberg tables via CTAS. This avoids an intermediate format conversion step. The Parquet files are read directly and written as Iceberg data files in one operation.

-- Load TPC-H lineitem from local disk
CREATE TABLE tpch_sf1.lineitem AS
SELECT * FROM read_parquet('/data/tpch/sf1/lineitem/*.parquet');

-- Load from S3 with inline credentials
CREATE TABLE tpch_sf1.lineitem AS
SELECT * FROM read_parquet(
  's3://bench-data/tpch/sf1/lineitem/*.parquet',
  access_key => 'AKIA...',
  secret_key => '...',
  endpoint   => 'http://localhost:9000',
  region     => 'us-east-1'
);

-- Transform during load
CREATE TABLE analytics.orders_summary AS
SELECT
  o_orderdate,
  o_orderstatus,
  COUNT(*) AS order_count,
  SUM(o_totalprice) AS total_revenue
FROM read_parquet('/data/tpch/sf1/orders/*.parquet')
GROUP BY o_orderdate, o_orderstatus;

Because read_parquet() returns a standard DataFusion table scan, it participates in the full optimizer pipeline: predicate pushdown, projection pruning, and partition pruning all apply.

Querying without loading

read_parquet() can also be used as a one-off query target, without creating an Iceberg table:

-- Inspect schema
DESCRIBE SELECT * FROM read_parquet('/data/tpch/sf1/orders/*.parquet') LIMIT 0;

-- Quick aggregation over raw Parquet
SELECT o_orderstatus, COUNT(*) AS cnt
FROM read_parquet('/data/tpch/sf1/orders/*.parquet')
GROUP BY o_orderstatus;

-- Join Parquet with an Iceberg table
SELECT p.p_name, l.l_quantity
FROM read_parquet('/data/tpch/sf1/lineitem/*.parquet') AS l
JOIN warehouse.tpch_sf1.part AS p ON l.l_partkey = p.p_partkey
LIMIT 20;

Implementation

read_parquet() is registered in sqe-catalog as a DataFusion TableFunction. On each invocation:

1. The path argument is parsed to detect s3:// vs local (/ or file://) paths.
2. For S3: an AmazonS3Builder is constructed from the inline named parameters, with fallback to the StorageConfig from sqe-core for any omitted fields.
3. For local paths: the built-in DataFusion local filesystem ObjectStore is used.
4. Glob patterns are expanded against the chosen ObjectStore.
5. A ListingTable is constructed over the matched files and returned as the table scan node.

The function is registered on every SessionContext at startup, so it is always available without any special configuration.

Limitations

• All matched Parquet files must share an identical Arrow schema. Schema evolution across files in the same glob is not supported.
• read_parquet() is read-only. It cannot be used as the target of an INSERT INTO.
• Credential parameters are passed as SQL literals. Avoid logging or displaying these queries in audit logs without redaction. SQE’s audit logger redacts named parameter values that match access_key, secret_key, and session_token patterns.
• Very large numbers of matched files (>10,000) may cause slow planning due to the object listing step.

File-format TVFs

The four TVFs read_parquet, read_csv, read_json, and read_delta query files directly without registering an external table. They share a uniform calling convention and a uniform path-resolution layer (local filesystem, S3, HTTPS, HuggingFace hf://).

This chapter covers read_csv, read_json, and read_delta. The dedicated read_parquet chapter covers Parquet specifically.

Common path forms

All four TVFs accept the same path shapes:

-- Local
SELECT * FROM read_csv('/data/sales.csv');

-- S3 (anywhere object_store understands)
SELECT * FROM read_csv('s3://bucket/key.csv',
    access_key => 'AKIA...', secret_key => '...',
    endpoint => 'http://localhost:9000', region => 'us-east-1');

-- HTTP / HTTPS (V10)
SELECT * FROM read_csv('https://raw.githubusercontent.com/.../data.csv');

-- HuggingFace (V10)
SELECT * FROM read_csv('hf://datasets/squad/plain_text/train.csv');

-- HuggingFace with revision (V12.1)
SELECT * FROM read_parquet('hf://datasets/foo/[email protected]/data.parquet');

-- HuggingFace auto-generated parquet view
SELECT * FROM read_parquet('hf://datasets/foo/bar@~parquet/default/train/0.parquet');

S3 credentials default to the engine’s [storage] block when not supplied inline. HTTPS and hf:// paths flow through V10’s LazyHttpObjectStoreRegistry, which constructs an HttpStore for the host on first request.

Quoted-string auto-detect

V8 introduced a shortcut. With the embedded CLI, the engine recognises a quoted string in a FROM clause as a file URL and dispatches to the right TVF based on extension:

SELECT * FROM '/data/sales.parquet';
SELECT * FROM 's3://bucket/orders.csv';
SELECT * FROM 'hf://datasets/foo/bar/data.csv';

Format dispatch happens by extension. .parquet -> read_parquet, .csv / .tsv / .psv / .ssv -> read_csv, .json / .jsonl / .ndjson -> read_json, .avro -> the Avro reader. Compressed extensions are recognised: .csv.gz, .tsv.zst, .json.bz2 all dispatch to the right reader with the right codec.

read_csv

SELECT * FROM read_csv(
    '<path>',
    [delimiter | delim | sep => '<byte>',]
    [has_header | header => '<bool>',]
    [quote => '<byte>',]
    [escape => '<byte>',]
    [comment => '<byte>',]
    [null_regex | nullstr => '<regex>',]
    [compression | compress => 'auto|none|gzip|bz2|xz|zstd',]
    [file_extension => '<.ext>']
);

Smart defaults:

• Delimiter detected from the path extension. .csv is ,, .tsv is tab, .psv is |, .ssv is ;. Compression suffixes are stripped first, so .tsv.gz still picks tab.
• Compression detected from the path extension. .gz, .bz2, .xz, .zst are recognised.
• has_header defaults to true (DataFusion default).

DuckDB-style aliases: sep, delim for delimiter; header for has_header; nullstr for null_regex; compress for compression.

-- All three are equivalent
SELECT * FROM read_csv('events.tsv');
SELECT * FROM read_csv('events.tsv', sep => '\t');
SELECT * FROM read_csv('events.tsv', delimiter => '\t', has_header => 'true');

-- Compressed, with explicit override
SELECT * FROM read_csv('events.tsv.zst', compression => 'auto');

-- Semicolon-separated file
SELECT * FROM read_csv('financial.ssv', sep => ';');

read_json

SELECT * FROM read_json(
    '<path>',
    [access_key | secret_key | endpoint | region | file_extension,]
    [format => 'auto|newline_delimited|array',]
    [compression | compress => 'auto|none|gzip|zip|zstd|bz2|xz']
);

Two execution paths back this TVF:

• Streaming path (NDJSON, any supported compression except zip): DataFusion’s built-in JSON listing table reads the file as a stream, the same way read_csv does. This is the default, and it handles arbitrarily large NDJSON files without buffering the whole object in memory.
• Buffer path (format => 'array', or any .zip source): the object is fetched whole, decompressed, and reshaped into NDJSON in memory before decoding. A top-level JSON array can’t be streamed line-by-line, and a zip archive isn’t a single compressed byte stream DataFusion’s codecs understand, so both cases route here. A size guard rejects inputs larger than the buffer cap with a clear error instead of risking OOM on a mislabeled file.

-- Plain NDJSON, local or remote
SELECT * FROM read_json('/var/log/events.jsonl');
SELECT * FROM read_json('hf://datasets/nyu-mll/glue/cola/train.jsonl');

-- gzip NDJSON (streaming; auto-detected from .gz)
SELECT * FROM read_json('s3://logs/2026-05-07/events.json.gz');

-- full JSON array (buffer path; format must be explicit)
SELECT * FROM read_json('/data/events.json', format => 'array');
SELECT * FROM read_json('/data/events.json.gz', format => 'array');

-- zip archive (buffer path; every JSON/NDJSON entry is concatenated,
-- directory entries are skipped, array and NDJSON entries can mix)
SELECT * FROM read_json('/data/export.json.zip');

read_delta

SELECT * FROM read_delta(
    '<path>',
    [access_key | secret_key | endpoint | region,]
    [version => '<u64>',]
    [timestamp => '<RFC3339>']
);

Read-only Delta Lake reader, V11. Wraps deltalake-core 0.32.1. Time travel via version (snapshot id) or timestamp (RFC3339); the two are mutually exclusive.

SELECT * FROM read_delta('/data/delta/sales');

SELECT * FROM read_delta('s3://bucket/delta/orders',
    access_key => 'AKIA...');

-- Time travel
SELECT * FROM read_delta('/data/delta/sales', version => '5');
SELECT * FROM read_delta('/data/delta/sales',
    timestamp => '2026-04-01T00:00:00Z');

Writes are not exposed. The Delta transaction pipeline is substantial; the read path covers the most common ad-hoc query case.

HuggingFace specifics

The hf:// path uses a slightly different shape than S3 or HTTPS because HuggingFace expects a revision in the URL.

Two revision spellings work:

1. Inline @<rev> (DuckDB-style):

   SELECT * FROM read_parquet('hf://datasets/foo/[email protected]/train.parquet');
   

2. Query parameter ?revision=<rev>:

   SELECT * FROM read_parquet('hf://datasets/foo/bar/train.parquet?revision=v1.0');
   

Default is main when neither is specified. Specifying both rejects with a clear error.

@~parquet is special. HuggingFace auto-generates a Parquet conversion of every dataset on the refs/convert/parquet branch. The TVF translates this:

-- Equivalent to https://huggingface.co/datasets/foo/bar/resolve/refs%2Fconvert%2Fparquet/data.parquet
SELECT * FROM read_parquet('hf://datasets/foo/bar@~parquet/default/train/0.parquet');

Glob expansion (**/*.parquet) is on the V12.2 roadmap; today the path must point to a specific file.

When to use which

• read_parquet: ad-hoc queries against Parquet on disk, S3, HTTPS, or hf://. Anything Iceberg-aware that does not need the catalog.
• read_csv: ETL ingestion, log analysis, dataset preview before deciding to load into Iceberg.
• read_json: NDJSON logs, HuggingFace train.jsonl style splits.
• read_delta: query a Delta Lake table without converting to Iceberg.

For tables with metadata that you want to write back to, register them in a catalog. The TVFs are reads only.

Implementation references

• sqe-catalog/src/read_parquet.rs
• sqe-catalog/src/read_csv.rs
• sqe-catalog/src/read_json.rs
• sqe-catalog/src/read_delta.rs
• sqe-catalog/src/file_tvf_common.rs: shared parsing + S3 / HTTPS / hf:// resolver
• sqe-catalog/src/lazy_object_store.rs: V10’s lazy HTTPS object-store registry

Observability

SQE provides full observability through Prometheus metrics, OpenTelemetry traces/logs, and structured audit logging.

Metrics (Prometheus)

Available at http://coordinator:9090/metrics in Prometheus text format.

Histogram buckets: 10ms, 25ms, 50ms, 100ms, 250ms, 500ms, 1s, 2.5s, 5s, 10s, 30s, 60s.

Statement types: query, ctas, insert, merge, delete, drop, create_view, create_schema, show_catalogs, show_schemas, show_tables, policy, utility.

Example Queries (PromQL)

# Query rate (queries per second)
rate(sqe_query_count_total[5m])

# Error rate
rate(sqe_query_count_total{status="error"}[5m])

# P99 query duration
histogram_quantile(0.99, rate(sqe_query_duration_seconds_bucket[5m]))

# Active sessions
sqe_active_sessions

Local observability stack

For a self-contained metrics view alongside the test stack, SQE ships a Docker Compose overlay using VictoriaMetrics (Prometheus-compatible, around 30 MB RAM) and Grafana:

docker compose -f docker-compose.test.yml -f docker-compose.observability.yml up -d
open http://localhost:13000    # Grafana, admin / admin

The overlay auto-scrapes the single-node coordinator (localhost:19090), the distributed coordinator (localhost:29090), and workers (localhost:29091-29094). A pre-built dashboard lives at deploy/observability/sqe-benchmark-dashboard.json and is auto-provisioned by the overlay. To import it manually, copy the JSON into your Grafana instance and point it at a Prometheus or VictoriaMetrics data source.

Health Endpoints

Available on port 9091 (metrics port + 1) for both coordinator and workers.

Kubernetes Probes

Cluster Status (Ballista/DataFusion-style)

GET /api/v1/status returns a JSON snapshot of the node and cluster:

{
  "status": "ACTIVE",
  "node": {
    "role": "coordinator",
    "version": "0.1.0",
    "datafusionVersion": "51",
    "uptimeSeconds": 3600
  },
  "workers": {
    "total": 2,
    "healthy": 2,
    "healthyUrls": ["http://worker-0:50052", "http://worker-1:50052"]
  }
}

For worker nodes, the workers field is null.

Trino-Compatible Info (port 8080)

When the Trino compat layer is enabled, standard Trino info endpoints are available on the Trino HTTP port:

These endpoints are compatible with Trino JDBC drivers, DBeaver, and other Trino-aware tools for auto-detecting node state.

OpenTelemetry

otlp_endpoint exports traces, metrics, and logs via OTLP/gRPC. A collector with only a traces pipeline should use traces_otlp_endpoint; Prometheus scraping and structured stdout logging then remain independent.

graph LR
    SQE["sqe-server"] -->|OTLP gRPC| COLL["OTel Collector"]
    COLL --> JAEGER["Jaeger<br/>(traces)"]
    COLL --> PROM["Prometheus<br/>(metrics)"]
    COLL --> LOKI["Loki<br/>(logs)"]

Configuration:

[metrics]
# Trace-only collector. Recommended when /metrics is scraped and stdout logs
# are collected separately.
traces_otlp_endpoint = "http://otel-collector:4317"
trace_sample_rate = 1.0

# Legacy all-signals endpoint. Leave empty for the trace-only setup above.
otlp_endpoint = ""

When the endpoint is empty (default), SQE falls back to structured JSON logs on stdout, no external dependency required.

Trace Spans

Key spans emitted:

• sqe.query: full query and result-stream lifecycle
• sqe.plan: SQL parsing and planning
• sqe.policy_rewrite: policy enforcement
• iceberg_scan: Iceberg planning, read, decode, and filter work
• dispatch_to_worker: coordinator fragment dispatch
• sqe.worker.scan: worker scan execution
• iceberg.rest.request: outbound Polaris or Iceberg REST catalog request

W3C trace and request correlation

The Trino HTTP and Flight SQL endpoints accept the standard W3C traceparent and tracestate headers. Flight clients send the same values as ASCII gRPC metadata. SQE extracts them before authentication or planning and uses the configured global W3C propagator on coordinator to worker Flight calls and outbound Iceberg REST catalog calls.

The optional x-request-id and x-session-id values are correlation metadata, not credentials. SQE accepts only 1 to 128 characters from A-Z, a-z, 0-9, ., _, :, and -. Invalid values are omitted. These headers never replace the W3C trace ID and are never used for authentication.

Correlation fields have distinct meanings:

Trino polling and cancellation are independent HTTP requests. Their spans use their own incoming remote parent and record the original query_id; SQE does not invent parent relationships between separate BFF requests. The durable sqe.query span remains active while the result stream is executing.

VictoriaLogs and VictoriaTraces investigation

Start in VictoriaLogs with the BFF request value, for example request_id:="req-01JABC". Open an SQE event and copy its trace_id. Search VictoriaLogs for that exact trace_id to see the coordinator, policy and auth, worker, and Polaris events. Open the same ID in VictoriaTraces to inspect the flight_sql.request or trino.* server span, sqe.query, Iceberg scan, dispatch_to_worker, sqe.worker.scan, and iceberg.rest.request chain. Use query_id to join later Trino polling or cancellation requests to the durable query lifecycle and to compare the trace with EXPLAIN FULL scan counters.

Audit Log

SQE writes a JSONL audit log capturing every query:

{
  "timestamp": "2025-03-15T10:30:00Z",
  "username": "alice",
  "session_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
  "query_text": "SELECT * FROM sales.orders WHERE region = 'EU'",
  "query_hash": "sha256:e3b0c44298fc1c149afb...",
  "statement_type": "query",
  "client_ip": "10.0.1.42",
  "duration_ms": 142,
  "rows_returned": 1583,
  "status": "ok"
}

The query_hash field is a SHA-256 hash of the SQL text, useful for correlating repeated queries without storing the full text. When audit logging is enabled, all fields are always present.

Configuration:

[metrics]
audit_log_path = "/var/log/sqe/audit.jsonl"

When the path is empty (default), audit logging is disabled (no-op).

Structured Logging

All SQE components use tracing with JSON output:

{
  "timestamp": "2025-03-15T10:30:00.142Z",
  "level": "INFO",
  "target": "sqe_coordinator::query_handler",
  "message": "Query executed",
  "trace_id": "0af7651916cd43dd8448eb211c80319c",
  "span_id": "00f067aa0ba902b7",
  "request_id": "req-01JABC",
  "session_id": "session-42",
  "query_id": "0190f4d5-ec1a-7b22-9f86-55a89dce7777",
  "user": "alice",
  "statement_type": "query",
  "duration_ms": 142,
  "rows": 1583
}

Log level controlled via RUST_LOG environment variable:

RUST_LOG=info             # Default
RUST_LOG=sqe=debug        # Debug SQE crates only
RUST_LOG=sqe=trace        # Everything

Kubernetes Integration

The Helm chart includes optional ServiceMonitor for Prometheus Operator:

serviceMonitor:
  enabled: true
  interval: 30s
  labels:
    release: prometheus

Trino Compatibility

SQE includes a Trino-compatible HTTP endpoint that allows existing Trino clients (JDBC drivers, CLI tools, DBeaver) and Trino-speaking BI tools (Metabase, Superset) to connect without modification.

Enabling

The Trino HTTP endpoint is enabled by default on port 8080. Set port to 0 to disable:

[coordinator]
trino_http_port = 8080    # 0 to disable

Endpoints

Authentication

The Trino endpoint supports two authentication methods:

Bearer Token

Pass an existing access token directly:

curl -H "Authorization: Bearer eyJhbG..." \
     -H "X-Trino-User: alice" \
     -d "SELECT 1" \
     http://localhost:8080/v1/statement

Basic Auth

Username and password are exchanged for a token via the configured OIDC/OAuth2 backend:

curl -u alice:password \
     -d "SELECT 1" \
     http://localhost:8080/v1/statement

Client Headers

SQE respects standard Trino client headers:

Result Pagination

Query results are paginated. The initial response includes a nextUri field. Follow nextUri links to retrieve subsequent pages:

{
  "id": "query-uuid",
  "stats": { "state": "FINISHED" },
  "columns": [{"name": "result", "type": "integer"}],
  "data": [[1]],
  "nextUri": "http://localhost:8080/v1/statement/query-uuid/1"
}

When nextUri is absent, all results have been consumed.

Async Submission

Submission is async, matching Trino’s own protocol. POST /v1/statement spawns the query on a background task with a bounded initial wait. If the query does not finish in that window, the first response carries state: QUEUED, no data, and a nextUri pointing at /v1/statement/queued/{id}/{token}:

{
  "id": "query-uuid",
  "stats": { "state": "QUEUED" },
  "nextUri": "http://localhost:8080/v1/statement/queued/query-uuid/0"
}

The client follows the queued links (state stays QUEUED or RUNNING) until the query finishes, at which point nextUri redirects to the results route at token 0 and the response starts carrying columns and data. Clients that only ever poll nextUri need no special handling: the queued and results routes chain transparently.

Using with the CLI

sqe-cli --protocol http --host localhost --port 8080 --user alice

Connecting External Tools

DBeaver

1. Create a new Trino connection
2. Host: localhost, Port: 8080
3. Authentication: Username/Password
4. Driver properties: no special settings needed

JDBC (Java)

String url = "jdbc:trino://localhost:8080";
Properties props = new Properties();
props.setProperty("user", "alice");
props.setProperty("password", "secret");
Connection conn = DriverManager.getConnection(url, props);

Metabase and Superset

BI tools that speak Trino connect through the same endpoint. Metabase uses the Trino JDBC driver, Superset uses the Trino SQLAlchemy dialect (trino://user@host:8080/catalog). Both drive a metadata handshake on connect (prepare a statement, enumerate catalogs and schemas, list tables, describe columns) before running a chart, and SQE now matches Trino’s exact response shape at each step:

• PREPARE and DEALLOCATE PREPARE are handled as session-control via the X-Trino-Added-Prepare / X-Trino-Deallocated-Prepare headers, so the JDBC connection test succeeds.
• SHOW TABLES returns a single Table column, SHOW SCHEMAS a Schema column, and SHOW CATALOGS a Catalog column, so schema sync reads the right values.
• SHOW CATALOGS and the system.jdbc.* / system.metadata.* tables enumerate every reachable catalog and skip the ones the caller is not authorized to list, and SHOW TABLES / SHOW SCHEMAS honor the session catalog (X-Trino-Catalog).
• DESCRIBE and SHOW COLUMNS resolve double-quoted identifiers ("catalog"."schema"."table"), so field discovery works.
• Types map to their Trino equivalents: timestamp(6) carries its precision in the type signature (so date bucketing over JDBC works), and computed unsigned-64 aggregates like count(*) map to bigint rather than decimal.

Limitations

• The Trino endpoint returns results as JSON (Trino wire format), not Arrow. For maximum performance, use Flight SQL.
• Transaction control (START TRANSACTION, COMMIT) is not supported. Queries execute in auto-commit mode.
• Type mapping covers common types; complex nested types may differ from native Trino behavior.
• Iceberg hidden columns ($path, $file_modified_time, $partition) are not exposed on table scans. They need a per-row, per-source-file column that is resolvable by name but excluded from SELECT *. DataFusion has no such metadata-column mechanism yet (tracked upstream at apache/datafusion#20135, not in any release), and adding the column to the scan schema would make every SELECT * return it. For file-level introspection use the table_files('ns', 't') table function, which lists file_path, record_count, and file_size_in_bytes per data file.
• Materialized views are not supported. CREATE MATERIALIZED VIEW returns a clear “not supported” error rather than creating a plain view. DROP MATERIALIZED VIEW IF EXISTS is treated as a no-op so client tooling that issues it on teardown can proceed.

Flight SQL vs Trino HTTP

Use Flight SQL for performance-sensitive workloads. Use Trino HTTP for compatibility with existing tools.

Benchmark Suite

SQE ships with sqe-bench, a Rust CLI tool that generates benchmark data, loads it into SQE via the read_parquet() TVF, and runs query suites to validate SQL correctness and measure query performance.

For the longitudinal view (every benchmark JSON in benchmarks/results/ plotted across time, per-suite, per-scale, per-query heatmaps), see getsqe.com/performance. Charts auto-regenerate from the committed JSONs via make benchmark-charts.

Available Benchmarks

Why these benchmarks? Each covers a different slice of SQL correctness:

• TPC-H and SSB validate the analytical core: joins, aggregates, GROUP BY, ORDER BY, date arithmetic. TPC-H is the standard first check for any SQL engine.
• TPC-DS is the hardest. Its 99 queries exercise correlated subqueries, CTEs, window functions, GROUPING SETS, and complex multi-table joins. Passing TPC-DS well means the engine handles real analytical workloads.
• TPC-C and TPC-E cover OLTP patterns: point lookups, small aggregates, indexed access by key ranges, plus write operations (DELETE, UPDATE) exercised via Copy-on-Write.
• TPC-BB exercises semi-structured data alongside the TPC-DS schema, useful for validating string functions and JSON handling.

Results (SF1, vs Trino 465)

The numbers below are the latest SF1 run, as of 2026-06-12, against Trino 465 on identical Iceberg tables and S3 storage. All 222 queries pass (222/222). SQE wins six of seven suites at SF1.

Run-to-run variance is real, so treat each figure as approximate. The rank order is stable across the last month of runs.

Where SQE trails

SSB is the one suite SQE loses at SF1: 8.3s against Trino’s 5.8s, a 0.70x result. SSB is a denormalized star schema built for fast star-join filtering. Trino ships build-side key sets (bloom filters) into its scans, which prunes the lineorder fact table before it is read. SQE’s equivalent, shipping build-side key sets to distributed workers, is in progress. The lineorder fact has a uniform foreign-key distribution that defeats row-group min/max pruning, so the runtime filter only helps at row level today.

TPC-DS has the most misses at SF1 (93/99). The six gaps are GROUPING SETS edge cases around grand-total row presence; they are the same six since March, not new regressions. TPC-E passes 11/11 but is the suite that historically needed the most work: it joins across 33 tables and uses IN-subquery patterns that DataFusion cannot always decorrelate, so deep-join queries dominate its run time.

The SF1 wins are decisive. At SF10 the picture narrows. On the level rig (Trino 481, totals across runs, single-node / distributed-2-worker / Trino range):

At SF10, TPC-H distributed (95.5s) lands inside Trino’s range, roughly par to ahead. TPC-DS distributed (338.3s) sits inside Trino’s range, close. SSB still trails at SF10, the same pattern as SF1. These are SF10 figures on a single rig, not the canonical SF1 results above.

How it is validated

Timing data is only as good as the result data behind it. Two layers of validation run before any number is trusted.

The first layer is differential validation against Trino. sqe-bench compare <suite> runs every query against SQE (Flight SQL) and Trino (HTTP) on the same Iceberg tables and diffs the result rows. A row-count or value mismatch fails the query. A query that returns zero rows on both engines is reported as vacuous, not a match: agreement on nothing validates nothing.

The second layer is an independent data oracle. DuckDB’s official dsdgen output loads side by side with the generated parquet, with per-table row counts and per-column null fractions checked, and all queries run against both datasets inside DuckDB. A query that returns rows on official data and none on ours is a generator-fidelity bug, found without either SQL engine in the loop.

The oracle earned its keep. It flagged 16 vacuous TPC-DS queries as generator gaps rather than engine bugs, and it settled the one genuine engine disagreement in SQE’s favor: TPC-DS q75 differs by two rows because Trino’s DECIMAL(17,2) division rounds two ratios up past a < 0.9 filter and drops them. DuckDB matches SQE exactly. The benchmark that looked like an SQE failure was a Trino rounding bug.

For the longitudinal view across every committed run, see the benchmark history on getsqe.com.

Generating Data

The generate command produces Parquet files on local disk or S3. Data is deterministic (seeded) so results are reproducible.

# Generate TPC-H at scale factor 1 (~1 GB, 8 tables)
cargo run -p sqe-bench -- generate tpch --scale 1 --output ./data

# Scale factor 10 (~10 GB)
cargo run -p sqe-bench -- generate tpch --scale 10 --output ./data

# Write directly to S3
cargo run -p sqe-bench -- generate tpch --scale 1 \
  --output s3://bench-data/ \
  --s3-access-key AKIA... \
  --s3-secret-key ... \
  --s3-endpoint http://localhost:9000 \
  --s3-region us-east-1

# Generate all benchmarks at once
./scripts/benchmark-generate-all.sh

Scale factors explained

The scale factor controls dataset size. Scale factor 1 produces roughly 1 GB for TPC-H and TPC-DS; SSB is ~600 MB at SF1.

Files are split at 128 MB for parallelism. Output is structured as:

./data/
└── tpch/
    └── sf1/
        ├── lineitem/
        │   ├── part-0000.parquet
        │   └── part-0001.parquet
        ├── orders/
        │   └── part-0000.parquet
        └── ... (8 tables total)

Direct-to-Iceberg Sink

generate --sink iceberg skips the staging Parquet step entirely. Instead of writing files to --output for a later load run, it connects straight to the Iceberg REST catalog, creates the tables, and commits data files with one fast_append per table. The sink works for every generator benchmark (TPC-H, TPC-DS, SSB, TPC-C, TPC-E, TPC-BB, ClickBench, and bank), not just the bank demo schema that introduced the sink.

cargo run -p sqe-bench -- generate tpch --scale 10 --sink iceberg \
  --catalog-uri http://localhost:8181/api/catalog \
  --warehouse bench --namespace tpch \
  --client-id ... --client-secret ...

The command creates the namespace if it does not exist, then generates and commits each table in turn. Data is written in parallel shards for TPC-H and bank; the other generators write one shard per table.

The sink buffers one full generation shard in memory before writing it, so peak memory scales with shard size: per-table for the serial generators, per rows / --threads for TPC-H and bank. At large scales, raise --threads to shrink each shard, or fall back to the parquet staging path (generate then load) if memory stays tight.

Two flags control repeated runs against the same namespace:

• --resume skips a table that already carries a sqe-bench.table.<name>=done property, so an interrupted or repeated generate run does not redo work or duplicate rows. The marker is set on the table itself, not derived from snapshot history, because some catalogs serve a trimmed snapshot list.
• --clean drops and recreates every table first, so the run is idempotent regardless of what state the namespace was in. --clean and --resume are mutually exclusive.

Without --resume or --clean, re-running generate against a table that already holds data commits another fast_append on top of it, duplicating every row. The done marker is only consulted when you pass --resume.

Catalog and storage settings match the load command’s --s3-* flags, plus the catalog’s OAuth2 client-credentials pair (--client-id/--client-secret) or a pre-acquired --bearer-token. See sqe-bench generate --help for the full flag list.

Loading Data

The load command connects to SQE and creates Iceberg tables using read_parquet() + CTAS. No intermediate format conversion is needed. Parquet files are read directly and written as Iceberg.

# Load TPC-H from local disk
cargo run -p sqe-bench -- load tpch \
  --scale 1 \
  --data ./data \
  --host localhost \
  --port 60051 \
  --username root \
  --password ""

# Load from S3
cargo run -p sqe-bench -- load tpch \
  --scale 1 \
  --data s3://bench-data/ \
  --s3-access-key AKIA... \
  --s3-secret-key ... \
  --s3-endpoint http://localhost:9000 \
  --s3-region us-east-1 \
  --host localhost \
  --port 60051 \
  --username root \
  --password ""

# Drop and recreate tables before loading
cargo run -p sqe-bench -- load tpch --scale 1 --data ./data --clean \
  --host localhost --port 60051 --username root --password ""

# Use the Trino HTTP protocol instead of Flight SQL
cargo run -p sqe-bench -- load tpch --scale 1 --data ./data \
  --protocol trino \
  --host localhost \
  --port 8080 \
  --username root \
  --password ""

The loader creates a namespace named <benchmark>_sf<N> (e.g., tpch_sf1) and sends one CTAS statement per table:

CREATE TABLE tpch_sf1.lineitem AS
SELECT * FROM read_parquet('/data/tpch/sf1/lineitem/*.parquet');

For S3 sources, inline credentials are injected:

CREATE TABLE tpch_sf1.lineitem AS
SELECT * FROM read_parquet(
  's3://bench-data/tpch/sf1/lineitem/*.parquet',
  access_key => 'AKIA...',
  secret_key => '...',
  endpoint => 'http://localhost:9000',
  region => 'us-east-1'
);

See read_parquet TVF for full syntax documentation.

Fast benchmark runs via attached golden tables

For the read-only suites (tpch, ssb, tpcds, clickbench), the load step dominates wall-clock time and adds nothing to the query measurement: every run rewrites the same parquet into fresh Iceberg tables before a single query runs. Publish those tables once into a persistent Polaris, then attach them read-only on every subsequent run instead of reloading.

Publish once:

BENCH_GOLDEN_POLARIS_URL=https://polaris.example.com/api/catalog \
BENCH_GOLDEN_WAREHOUSE=golden_warehouse \
BENCH_GOLDEN_TOKEN=<bearer> \
BENCH_S3_ENDPOINT=https://s3.example.com \
BENCH_GOLDEN_S3_ACCESS_KEY=... BENCH_GOLDEN_S3_SECRET_KEY=... \
BENCH_SCALE=1 ./scripts/benchmark-publish-iceberg.sh

Then run any read-only suite with BENCH_DATA_SOURCE=attach:

BENCH_GOLDEN_POLARIS_URL=https://polaris.example.com/api/catalog \
BENCH_GOLDEN_WAREHOUSE=golden_warehouse \
BENCH_GOLDEN_TOKEN=<bearer> \
BENCH_S3_ENDPOINT=https://s3.example.com \
BENCH_GOLDEN_S3_ACCESS_KEY=... BENCH_GOLDEN_S3_SECRET_KEY=... \
BENCH_DATA_SOURCE=attach BENCH_SCALE=1 ./scripts/benchmark-test.sh tpch

benchmark-test.sh attaches the golden catalog once (scripts/benchmark-attach-golden.sh) and queries each table as golden.<namespace>.<table> instead of generating and loading data. tpcc and tpce are write suites (their queries include DELETE/UPDATE via CoW), so they still generate and load normally even in attach mode; a shallow-clone path that would let them run against golden tables too, without mutating the shared copy, is planned but not yet built. bank and tpcbb also still generate and load normally in attach mode: bank is published to golden by benchmark-publish-iceberg.sh, but attach mode does not yet query it from there, and tpcbb’s own tables (web_clickstreams, product_reviews) are not published at all, since tpcbb shares tpcds’s namespace and only needs the two tables it adds. Wiring bank/tpcbb into attach mode is the deferred follow-up alongside the tpcc/tpce shallow-clone path. Attach is loud by design: a missing or unreachable golden catalog fails the run with the ATTACH error rather than silently falling back to a full load, which would mask the exact cost this path removes.

Unified harness (benchmark.sh + sqe-bench run)

For the read-only suites (tpch, ssb, tpcds, clickbench), the unified harness combines data attachment with query execution in one command:

BENCH_PROFILE=local BENCH_SCALE=1 scripts/benchmark.sh tpch ssb tpcds clickbench

The scripts/benchmark.sh orchestrates two steps. First, it attaches the golden Iceberg catalog (published once via benchmark-publish-iceberg.sh). Second, it runs the suites through sqe-bench run, which executes all queries against the attached tables and emits JSON reports to benchmarks/results/.

Configuration profiles live in benchmarks/profiles/<name>.toml. Profiles define the coordinator config, catalog credentials, and suite-specific settings. Credentials come from environment variables or AWS profiles, never stored in the TOML file itself. The profile loader redacts secrets on error and checks for committed keys at startup.

Each read-only suite attaches golden (zero load); write suites (tpcc, tpce) are a follow-up plan. The old benchmark-*.sh scripts remain: the migration to unified is a later commit after parity is confirmed.

Running Tests

The test command executes all queries in the benchmark suite against the loaded data and reports correctness and timing.

# Run all TPC-H queries (Flight SQL, default)
cargo run -p sqe-bench -- test tpch \
  --scale 1 \
  --host localhost \
  --port 60051 \
  --username root \
  --password ""

# Run a single query
cargo run -p sqe-bench -- test tpch --scale 1 --query q03 \
  --host localhost --port 60051 --username root --password ""

# Use Trino HTTP protocol
cargo run -p sqe-bench -- test tpch --scale 1 \
  --protocol trino \
  --host localhost \
  --port 8080 \
  --username root \
  --password ""

# Run all benchmarks end-to-end
./scripts/benchmark-test.sh tpch
./scripts/benchmark-test.sh tpcds
./scripts/benchmark-test.sh ssb

Query result statuses

DIFF is not treated as a failure in CI. It is a signal for investigation. Decimal precision differences are expected when comparing float-heavy aggregates across different engines.

Queries can declare their requirements in a header comment:

-- name: Revenue by nation
-- requires: delete, merge
-- timeout: 30s
SELECT ...

Any query with -- requires: will be SKIPped if SQE does not support that feature, rather than FAILing the suite.

Understanding Results

Terminal output

TPC-H SF1 - Flight SQL (localhost:50051)
─────────────────────────────────────────
q01  PASS   1.23s   6001215 rows
q02  PASS   0.45s       460 rows
q03  PASS   0.89s     11620 rows
...
q17  DIFF   2.10s         1 rows  (decimal precision)
q22  PASS   0.33s         7 rows

Results: 20/22 PASS, 1 DIFF, 1 SKIP
Total time: 28.4s
Report: benchmarks/results/tpch-sf1-flight-2026-03-24T14:30:00.json

JSON report format

Reports are written to benchmarks/results/<benchmark>-sf<N>-<protocol>-<timestamp>.json:

{
  "benchmark": "tpch",
  "scale_factor": 1,
  "protocol": "flight",
  "host": "localhost:50051",
  "timestamp": "2026-03-24T14:30:00Z",
  "summary": {
    "total": 22,
    "pass": 20,
    "fail": 0,
    "diff": 1,
    "skip": 1,
    "error": 0,
    "total_duration_ms": 28400
  },
  "queries": [
    {
      "id": "q01",
      "status": "pass",
      "duration_ms": 1230,
      "rows": 6001215
    },
    {
      "id": "q17",
      "status": "diff",
      "duration_ms": 2100,
      "rows": 1,
      "diff_detail": "decimal precision mismatch: expected 1.0000, got 0.9999"
    }
  ]
}

JSON reports are machine-readable and suitable for tracking regressions over time in CI.

Historical Performance Tracking

Benchmark JSON results are committed to benchmarks/results/ for historical comparison. This enables tracking performance regressions and improvements across releases. The committed JSONs feed the per-suite, per-scale, per-query timeline on getsqe.com/performance; refer there for the longitudinal view of how each suite moved across the optimization work.

Comparing against Trino

The benchmark harness can run the same suite against a real Trino on the same data, so you can compare SQE and Trino directly. The Results section above is the output of exactly this run at SF1. There are two modes:

• Correctness parity: --compare-trino diffs SQE’s results against Trino’s row-for-row. This is how SQL correctness is validated at scale, not just timing. A row-count or value mismatch fails the query. A query that returns zero rows on both engines is reported as vacuous, not a match, because agreement on nothing validates nothing. Small decimal differences on float-heavy aggregates are flagged for investigation rather than treated as failures.
• Timing: the same run records per-query wall-clock for both engines, so a head-to-head speed comparison falls out of the parity run.

Run a comparison yourself and see the captured numbers in the benchmark quickstart: Benchmarks: TPC-H / TPC-DS / SSB, or in the repo under benchmarks/.

CI/CD Integration

All three scripts support automated use without a TTY:

# Generate data once (idempotent - skip if files exist)
./scripts/benchmark-generate-all.sh

# Load all benchmarks
./scripts/benchmark-load.sh

# Run and report
./scripts/benchmark-test.sh tpch
./scripts/benchmark-test.sh tpcds
./scripts/benchmark-test.sh ssb

# Exit code is 0 if all queries are PASS or SKIP
# Exit code is 1 if any query is FAIL or ERROR

A typical CI pipeline runs TPC-H at SF1 as a smoke test on every PR, and the full suite (TPC-H + TPC-DS + SSB) nightly.

Query Files

Query SQL files are stored under benchmarks/queries/<benchmark>/:

benchmarks/
├── queries/
│   ├── tpch/     q01.sql ... q22.sql
│   ├── tpcds/    q01.sql ... q99.sql
│   ├── ssb/      q1.1.sql ... q4.3.sql
│   ├── tpcc/     order_status.sql, stock_level.sql, ...
│   ├── tpce/     trade_lookup.sql, customer_position.sql, ...
│   └── tpcbb/    q01.sql ... q10.sql
├── expected/
│   ├── tpch/sf1/    q01.csv ... q22.csv
│   └── ...
└── schemas/
    ├── tpch.sql
    ├── tpcds.sql
    └── ...

Expected results under benchmarks/expected/ are CSV files containing the correct output at the given scale factor. They are committed to the repository and used for regression checking.

Adding New Benchmarks

Implement the BenchmarkGenerator trait in sqe-bench/src/generate/:

#![allow(unused)]
fn main() {
pub trait BenchmarkGenerator {
    fn name(&self) -> &str;
    fn tables(&self) -> Vec<TableDef>;
    fn generate_table(
        &self,
        table: &str,
        scale: f64,
        output: &dyn ObjectStore,
        prefix: &str,
    ) -> Result<GenerateStats>;
}

pub struct TableDef {
    pub name: String,
    pub schema: Arc<Schema>,             // Arrow schema
    pub row_count_fn: fn(f64) -> usize,  // scale factor to row count
}
}

Steps to add a benchmark:

1. Create sqe-bench/src/generate/<name>.rs implementing BenchmarkGenerator.
2. Add SQL query files under benchmarks/queries/<name>/.
3. Add expected result CSVs under benchmarks/expected/<name>/sf1/.
4. Register the generator in sqe-bench/src/generate/mod.rs.
5. Add the benchmark name to the CLI subcommand list in sqe-bench/src/cli.rs.
6. Add a schema DDL file under benchmarks/schemas/<name>.sql for reference.

Supported catalog backends

SQE supports multiple Iceberg catalog backends and wire-protocol client adapters. The same binary works with every option below; choose by setting the [catalog.backend] block in your TOML. Catalog weight is opt-in through cargo features: a default REST-only build pulls no AWS SDK, no Thrift, no sqlx. Add glue, s3tables, hms, or sql as needed; rest and hadoop are always available.

For per-backend TOML configuration, credential setup, and troubleshooting, see Catalog backends.

Polaris (and any Iceberg REST catalog)

Apache Polaris exposes the Iceberg REST catalog specification. SQE uses this as its default backend. Any Iceberg REST-compatible service (Polaris, Lakeformation REST, custom) works with the same config block.

[catalog]
polaris_url = "http://localhost:18181/api/catalog"
warehouse   = "test_warehouse"

See the quickstart: Polaris + Keycloak (client credentials).

AWS Glue Data Catalog

Native AWS SDK integration against the regional Glue Data Catalog. Credentials come from the standard provider chain (AWS_PROFILE, instance profile, SSO).

[catalog.backend]
type      = "glue"
region    = "eu-example-1"
warehouse = "s3://my-bucket/warehouse"

See the quickstart: AWS Glue Data Catalog.

AWS S3 Tables

Managed Iceberg via the federated Glue Iceberg REST endpoint with AWS SigV4 authentication on every request. Backed by the vendored iceberg-catalog-rest crate with the aws-sigv4 feature.

[catalog.backend]
type             = "s3tables"
table_bucket_arn = "arn:aws:s3tables:eu-example-1:ACCOUNT:bucket/NAME"

See the quickstart: AWS S3 Tables (managed Iceberg).

Unity Catalog OSS

Unity Catalog OSS exposes an Iceberg REST adapter at /api/2.1/unity-catalog/iceberg/. The OSS image is read-only on create/drop; use for query workloads.

docker compose -f docker-compose.unity.yml up -d
set -a; source .env; set +a
cargo test -p sqe-catalog --test backends_integration -- --ignored unity_catalog::

See the quickstart: Unity Catalog OSS (Iceberg REST, read-only).

Hive Metastore

Thrift metastore protocol. Requires the hms cargo feature.

docker compose -f docker-compose.hms.yml up -d
set -a; source .env; set +a
cargo test -p sqe-catalog --features hms --test backends_integration -- --ignored hms::

Covered by the suite (hms::live_hms_namespace_round_trip, a create / list / drop round-trip against the Thrift metastore).

Project Nessie

Git-like Iceberg REST catalog with branch/tag semantics.

docker compose -f docker-compose.nessie.yml up -d
set -a; source .env; set +a
cargo test -p sqe-catalog --test backends_integration -- --ignored nessie::

See the quickstart: Project Nessie (Iceberg REST catalog).

Hadoop (filesystem warehouse, no catalog service)

No metadata service. SQE walks the warehouse prefix for metadata.json files. See Embedded mode for the catalog-free embedded flow.

[catalog.backend]
type      = "hadoop"
warehouse = "s3://my-bucket/warehouse"

Quack (DuckDB wire protocol)

Quack is not an Iceberg catalog; it is a client/server wire protocol that lets DuckDB clients (and other Quack-compatible tools) issue SQL to SQE and receive Arrow-serialised results. It sits alongside the Iceberg catalog backends, not in competition with them.

See Quack.

How catalog backends are tested

• sqe-catalog/tests/backends_integration.rs: live round-trips per backend (create / list / drop namespace, or read smoke), gated on #[ignore] plus the .env warehouse variables.
• sqe-catalog/tests/mount_*_test.rs: mount-time validation per backend (rejects bad secrets, requires a warehouse, and so on).

Embedded mode

SQE can run the full query engine in-process, with no server and no network. sqe-cli in embedded mode starts DataFusion, the Iceberg reader, and the same SQL planner locally, inside the CLI process. This is the fastest path for querying a warehouse from a laptop, a CI job, or a script.

Four warehouse modes:

• In-memory (--memory): a transient DataFusion catalog. Nothing is persisted. Good for ad-hoc SQL and testing SQL functions.
• Filesystem warehouse (--warehouse PATH): an Iceberg warehouse on local disk or object storage with no catalog service. SQE walks the path for metadata.json files and treats the prefix as the catalog. The “Iceberg without a catalog” case.
• Persistent SQLite catalog (--catalog-backend sqlite): a durable single-node catalog backed by a local SQLite file. Survives restarts.
• Cloud catalogs embedded: Glue and S3 Tables can be attached directly, with no coordinator, using the standard AWS credential chain.

See the quickstarts:

• Embedded: query local and remote files
• Embedded: persistent local catalog (SQLite)
• Embedded: attach multiple catalogs

In-memory

sqe-cli --embedded --memory -e "SELECT 1 AS one"

+-----+
| one |
+-----+
| 1   |
+-----+

Filesystem warehouse (no catalog service)

Point at a directory; SQE reads the Iceberg metadata directly. No Polaris, no Glue, no metastore.

sqe-cli --embedded --warehouse /data/warehouse \
    -e "SELECT COUNT(*) FROM sales.orders"

This is the catalog-free Hadoop mode. Writes need atomic rename, which object stores do not all provide, so this mode is read-oriented; for writes use a real catalog. The same backend powers the [catalog.backend] type = "hadoop" server config.

Cloud catalogs embedded

The embedded engine can attach a Glue or S3 Tables catalog directly, with no coordinator. Pass --catalog-backend plus the cloud warehouse; credentials come from the standard AWS provider chain (AWS_PROFILE, instance profile, SSO). These catalogs attach read-only (query, not write); use the server for writes. Requires the aws cargo feature, which is off by default to keep the AWS SDK out of standard builds: cargo install --path crates/sqe-cli --features aws.

# AWS Glue Data Catalog (warehouse is an s3:// prefix)
AWS_PROFILE=analytics sqe-cli --embedded \
    --catalog-backend glue \
    --catalog-warehouse s3://my-bucket/warehouse --region eu-example-1 \
    -e "SELECT * FROM glue.analytics.events LIMIT 10"

# AWS S3 Tables (warehouse is the table-bucket ARN)
AWS_PROFILE=analytics sqe-cli --embedded \
    --catalog-backend s3tables \
    --catalog-warehouse arn:aws:s3tables:eu-example-1:ACCOUNT:bucket/NAME \
    --region eu-example-1 \
    -e "SHOW SCHEMAS"

The catalog mounts under the backend name by default (glue. / s3tables.); override with --catalog-name.

Writing data

The embedded engine ships the same Iceberg write path as the cluster: DDL, CTAS, INSERT, UPDATE, DELETE, MERGE INTO, all against the local SQLite-backed catalog. A laptop session can build real Iceberg tables, not just read them.

CREATE SCHEMA iceberg.sales;

CREATE TABLE iceberg.sales.orders (
    id     BIGINT,
    region VARCHAR,
    ts     TIMESTAMP,
    total  DECIMAL(18,2)
);

-- Land external data straight into Iceberg. CTAS streams, so a file
-- larger than memory still loads without OOM.
CREATE TABLE iceberg.sales.orders_2026 AS
SELECT id, region, ts, total
FROM read_parquet('s3://bucket/2026/*.parquet')
WHERE total > 0;

INSERT INTO iceberg.sales.orders VALUES (1, 'eu', NOW(), 99.95);

UPDATE iceberg.sales.orders SET total = total * 1.21 WHERE region = 'eu';

DELETE FROM iceberg.sales.orders WHERE id < 100;

MERGE INTO iceberg.sales.orders t
USING iceberg.sales.orders_2026 s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET total = s.total
WHEN NOT MATCHED THEN INSERT (id, region, ts, total)
                    VALUES (s.id, s.region, s.ts, s.total);

Default DML mode is Copy-on-Write. Opt a table into Merge-on-Read with the standard Iceberg properties:

ALTER TABLE iceberg.sales.orders
SET TBLPROPERTIES ('write.delete.mode' = 'merge-on-read');

Branching, tagging, and time travel work from the embedded prompt too; see DML for SET WRITE_BRANCH and FOR VERSION AS OF, and DDL for CREATE BRANCH / CREATE TAG. Exporting results to plain files goes through COPY ... TO; see Using the CLI.

Tables in the warehouse are valid Iceberg. Point a cluster deployment (or any other Iceberg reader) at the same path later and they come along unchanged.

Cookbook

Common embedded patterns in one place. Details on each TVF live in read_parquet and the file-format TVFs; path and credential forms in Storage backends.

Inspect an unknown file:

SELECT * FROM '/tmp/unknown.parquet' LIMIT 10;

Pull a public dataset into a local Iceberg table:

CREATE TABLE iceberg.demo.titanic AS
SELECT * FROM read_csv(
    'https://raw.githubusercontent.com/datasets/titanic/main/data/titanic.csv'
);

Materialize a HuggingFace dataset locally:

CREATE TABLE iceberg.demo.squad AS
SELECT * FROM read_parquet(
    'hf://datasets/squad/plain_text/train-00000-of-00001.parquet'
);

Join an Iceberg table against a raw file without loading it first:

SELECT i.region, sum(d.amount)
FROM iceberg.sales.orders i
JOIN read_parquet('/exports/transactions.parquet') d ON i.id = d.order_id
GROUP BY i.region;

Read a semicolon-separated European CSV:

SELECT * FROM read_csv('data/financial.ssv', sep => ';');

One-shot query from a shell script, machine-readable output:

sqe-cli --embedded \
    -e "SELECT count(*) FROM read_parquet('s3://bucket/sales/*.parquet')" \
    --format csv

Run a SQL script, abort on the first error:

sqe-cli --embedded --file daily-report.sql --stop-on-error

Differences from cluster mode

The embedded prompt speaks the same SQL surface as the coordinator’s Flight SQL endpoint: same parser, same planner, same Trino-compat function set, same Iceberg V2/V3 readers and writers. What changes is the deployment shape, and a few things fall away with it:

• No auth. No OIDC, no bearer tokens, no per-user identity, no policy enforcement. The process runs as the Unix user.
• Local catalogs plus read-only Glue / S3 Tables. Shared REST catalogs (Polaris, Nessie, Unity) and writes to cloud catalogs need the cluster path.
• Single-node execution. No worker fan-out, no distributed shuffle. The query memory pool is capped by --memory-limit (default 1GB).
• Single writer. The SQLite catalog is a single-process catalog. Two embedded sessions writing the same warehouse at once will conflict.
• No observability endpoints. Prometheus metrics, OpenTelemetry, and the audit log live in the server.

How it is tested

• crates/sqe-cli/tests/cli_smoke.rs: binary-level flag parsing, exit codes, mutually-exclusive flag rejection, and the --embedded --memory happy path.
• The catalog spec parser (NAME=PATH) is validated for empty names, missing separators, and dotted names.

Notes

• --memory and --warehouse are mutually exclusive.
• Local-path TVFs (read_csv and friends) work in embedded mode; the embedded engine enables allow_local_paths so a laptop user can read local files.
• Embedded mode authenticates the OS user against the configured catalog’s credential source, not OIDC; there is no server to pass tokens through.

Flight SQL

Arrow Flight SQL is SQE’s primary wire protocol. It is columnar end to end: results come back as Arrow record batches over gRPC, with no row-by-row serialization tax. Every official client (the sqe-cli, ADBC drivers, the dbt-sqe adapter) speaks it. This page covers both topologies against Apache Polaris.

Single server

One coordinator parses, plans, and executes. Good for development, small deployments, and the default Docker image.

Prerequisites

docker compose -f docker-compose.test.yml up -d
./scripts/bootstrap-test.sh

This brings up Polaris (http://localhost:18181) and an S3-compatible store, then creates the test_warehouse warehouse and the default / test_ns namespaces.

Configuration

The coordinator reads one TOML file. The Flight SQL listener is on 50051:

[auth]
token_endpoint = "http://localhost:18181/api/catalog/v1/oauth/tokens"
client_id      = "root"

[catalog]
catalog_url = "http://localhost:18181/api/catalog"
warehouse   = "test_warehouse"

[storage]
s3_endpoint   = "http://localhost:19000"
s3_region     = "us-east-1"
s3_path_style = true

Run

# Start the coordinator (Flight SQL on 50051).
./target/release/sqe-server --config sqe.toml

# Connect with the CLI over Flight.
./target/release/sqe-cli --protocol flight --host localhost --port 50051 \
    -u root -e "SELECT 1 AS one"

Expected output

 one
-----
 1
(1 row)

The in-process equivalent is exercised by integration_test.rs: test_authentication (OIDC client-credentials against Polaris), test_simple_select, and the file-format TVF tests all run the full Flight SQL query path against this stack.

How it is tested

• crates/sqe-coordinator/tests/integration_test.rs (run with ./scripts/integration-test.sh): authentication, SELECT, CTAS round-trip, information_schema, and read_parquet/read_csv/read_json.

Distributed (coordinator + workers)

The coordinator parses and plans, then ships secured plan fragments to stateless workers over Arrow Flight. Workers hold no catalog state; they receive the plan and the user’s bearer token and execute.

Prerequisites

docker compose -f docker-compose.test.yml -f docker-compose.distributed.yml up --build -d
./scripts/bootstrap-test.sh

This adds a coordinator (Flight SQL on 60051) and two workers (internal 50052, exposed as 60061 / 60062) on the shared Polaris and storage.

Run

scripts/test.sh scenario distributed

The scenario builds sqe-cli, runs SQL over Flight on 60051, and verifies worker dispatch through system.runtime.tasks (proving fragments actually reach the workers rather than silently falling back to local execution).

Expected output

The scenario runs a sequence of SQL statements over Flight on 60051 and prints a pass line per check, ending with the worker-dispatch verification. The single-node Flight path (auth, CTAS, SELECT against live Polaris) was re-run this round through the TVF integration tests (3/3, 23.6s); the distributed harness is covered by the suite and the committed benchmark baselines (TPC-H SF1 distributed 22/22 in 12.0s, 3.1x over single node).

How it is tested

• crates/sqe-coordinator/tests/integration_test.rs::test_distributed_select (ignored by default; needs workers listening on :50052).
• quickstart/distributed (run via scripts/test.sh scenario distributed): full coordinator + worker harness.
• scripts/concurrent-test.sh: N parallel Flight clients, cache behaviour.

Notes

• Auth is bearer-token passthrough: the CLI authenticates the user via OIDC, and the token rides to Polaris and S3. There is no service account.
• The internal Flight port is 50051; compose maps it to 60051 to avoid colliding with a local coordinator.
• Workers are stateless. Scale by adding worker replicas; the coordinator load-balances fragments across registered workers.

Trino HTTP compatibility

SQE speaks enough of the Trino HTTP protocol that Trino clients, JDBC drivers, and BI tools can point at it unchanged. The coordinator exposes the /v1/statement endpoint with nextUri pagination, /v1/info, and /v1/info/state. This is a compatibility surface, not a re-implementation of Trino; it covers the query-submission path that clients actually use.

Single server

Prerequisites

docker compose -f docker-compose.test.yml up -d
./scripts/bootstrap-test.sh

Configuration

The Trino HTTP listener runs alongside Flight SQL. The default port is 8080 (the test/distributed compose files map it to 28080):

[coordinator]
trino_http_port = 8080    # 0 to disable

Run

# Submit a query. Basic auth carries the user; the password is the OIDC secret
# (empty for the local root client).
curl -s -u root: \
  -H "X-Trino-User: root" \
  -d "SELECT 1 AS one" \
  http://localhost:28080/v1/statement

Trino clients follow the nextUri field until results are exhausted. A JDBC client connects with the Trino driver against http://localhost:28080.

Expected output

The first response carries a nextUri; following it returns the data:

{
  "columns": [{"name": "one", "type": "bigint"}],
  "data": [[1]],
  "stats": {"state": "FINISHED"}
}

How it is tested

• crates/sqe-coordinator/tests/integration_test.rs::test_trino_http_query: server startup, Basic auth, /v1/statement POST, pagination.
• test_trino_type_mapping, test_trino_batches_to_json: Arrow to Trino JSON.
• scripts/trino-parity-test.sh and scripts/trino-compat-test.sh: run the same SQL against SQE and a real Trino and diff the results.

Distributed

The Trino HTTP endpoint lives on the coordinator. Distribution across workers is identical to the Flight path: the coordinator plans, workers execute. The client sees a single Trino-compatible endpoint regardless of worker count.

Prerequisites

docker compose -f docker-compose.test.yml -f docker-compose.distributed.yml up --build -d
./scripts/bootstrap-test.sh

Run

scripts/test.sh scenario distributed

The scenario exercises the Trino HTTP endpoint on 28080 alongside the Flight path and confirms worker dispatch.

Expected output

The distributed scenario’s Trino check submits a query to the HTTP endpoint on the coordinator (28080) and follows nextUri to completion, alongside the Flight path on the same cluster. This is covered by the suite; the docker-dependent re-run was not repeated this round (see the validation matrix note on local Docker capacity).

Trino SQL parity

SQE adds a Trino-compatibility function layer (date/time helpers like year(), month(), day_of_week(), JSON casts, and more) so dbt models and Trino SQL run with fewer rewrites. The current parity surface is tracked in Trino Compatibility. The parity scripts above are the regression guard.

Notes

• Authentication needs Basic auth (-u user:password) to populate the session, not just the X-Trino-User header. For the local root client the password is empty (-u root:).
• The Trino HTTP endpoint is enabled by default on [coordinator] trino_http_port = 8080; set the port to 0 to disable it. Flight SQL is the recommended protocol for SQE-native clients.

SQL Reference

A function-by-function and statement-by-statement reference for everything SQE can parse and execute. Every entry lists where the implementation lives so you can jump from “what does this do” to “where do I read the code”.

The reference focuses on what ships in the running engine, not the SQL standard in the abstract. If a function is not listed here, it is not registered in our SessionContext.

How to read these tables

Every page uses the same column shape. The first three columns describe the function in SQE; the four right columns describe how the same idea looks in other engines.

Origins

Every function in SQE has exactly one origin. Eight values appear:

The two registration entry points are crates/sqe-coordinator/src/session_context.rs (cluster mode) and crates/sqe-cli/src/embedded.rs (single-binary mode). Both register the same UDFs / UDTFs in the same order, so a function works the same way in both personas.

Pages

Scalar functions

• Conditional and null-handling: if, iff, case, coalesce, nullif, greatest, least, nvl, nvl2, typeof, try.
• String: concat, substring, trim, lower, upper, regex, normalisation, split, format, hash digests.
• Math: trig, rounding, logs, exponents, sign, modular, base conversion.
• Date and time: timestamp construction, extraction, formatting, parsing, arithmetic, time-zone handling.
• Array, map, struct: the 40+ functions from datafusion-functions-nested plus Trino aggregate constructors (map_agg, histogram).
• JSON: two layered surfaces: Trino-named (json_extract, json_parse) and the datafusion-functions-json json_get_* family.
• Encoding, hashing, URL: base64, hex, md5, sha224..512, url_extract_*, url_encode, url_decode.

Aggregate and window

• Aggregate functions: count, sum, avg, statistical, regression, array_agg, string_agg / listagg, histogram, map_agg, approximation.
• Window functions: row_number, rank, lag, lead, first_value, frame syntax (ROWS BETWEEN, RANGE BETWEEN, GROUPS BETWEEN).

Table-valued functions

• Table-valued functions: file format (read_parquet, read_csv, read_json, read_delta), Iceberg metadata (table_snapshots, table_history, table_files, table_partitions, table_manifests, table_refs), generators (generate_series, unnest).

Statements

• DDL: CREATE, ALTER, DROP for tables, schemas, views; partition evolution; branches and tags; column defaults.
• DML: SELECT, INSERT, UPDATE, DELETE, MERGE, COPY TO, TRUNCATE, time travel (FOR VERSION AS OF, FOR SYSTEM_TIME AS OF, FOR INCREMENTAL BETWEEN), SET WRITE_BRANCH.
• CALL procedures: system.rewrite_data_files, expire_snapshots, remove_orphan_files, rewrite_manifests, suggest_bloom_filter_columns.
• GRANT and REVOKE: SQE-specific security extensions. GRANT MASKED WITH, GRANT ROWS WHERE, SHOW GRANTS, SHOW EFFECTIVE GRANTS, CHECK ACCESS.
• SHOW and EXPLAIN: metadata queries and plan inspection. SHOW CATALOGS, SHOW STATS, EXPLAIN FULL.
• Operators: arithmetic, string, comparison, null tests, casting (::), set membership.

Embedded CLI

• Dot-commands: .help, .tables, .schema, .describe, .summarize, .timer, .read, .format. Embedded CLI only.

What is intentionally not in SQE

Some functions appear in the dialect comparison columns as missing. The reasoning:

• PIVOT, UNPIVOT, QUALIFY, ASOF JOIN, FROM-first syntax: DataFusion’s parser does not accept them. Tracked upstream.
• Lambda expressions, list comprehensions: DataFusion has no AST node for closures.
• Oracle / Snowflake DECODE: name collides with DataFusion’s binary decode(input, encoding) helper. CASE WHEN covers the use case.
• IIF (T-SQL): covered by if (Trino) and iff (Snowflake), both registered.
• postgres_table_scanner, mysql_table_scanner, sqlite_scanner: out of scope. SQE is Iceberg-first; if you need a non-Iceberg engine, query it where it lives.
• spatial, vss, fts, excel: niche. Use a tool built for the job (PostGIS, a vector DB, an FTS engine).

The full DuckDB-comparison audit lives at getsqe.com/compare/duckdb. The Trino-comparison audit lives at getsqe.com/compare/trino.

Conditional and null-handling

Functions for choosing between values, replacing nulls, and inspecting types. Most are scalar UDFs; CASE WHEN is a SQL expression handled by the planner.

Function table

Patterns

Replace NULL with a default

SELECT coalesce(comment, 'no comment') FROM orders;
SELECT nvl(comment, 'no comment') FROM orders;          -- two-arg shorthand

Treat empty strings as NULL

SELECT coalesce(nullif(name, ''), 'unknown') FROM users;

nullif(name, '') returns NULL when name is the empty string, then coalesce substitutes the default.

Branch on a boolean

SELECT
    iff(amount > 1000, 'large', 'small') AS bucket,    -- Snowflake
    if(amount > 1000, 'large', 'small') AS bucket_t    -- Trino
FROM orders;

Both calls produce the same result. Use whichever matches your team’s existing dbt models. dbt-snowflake projects ported to SQE keep iff() working unmodified.

Complex branching: prefer CASE

SELECT
    CASE
        WHEN amount < 100 THEN 'small'
        WHEN amount < 1000 THEN 'medium'
        ELSE 'large'
    END AS bucket
FROM orders;

Reach for CASE when there are more than two branches or the condition is not a single boolean expression.

Take the safer cast

SELECT try(CAST(payload AS BIGINT)) AS amount FROM events;

try() swallows the conversion error and returns NULL for rows that fail. Without it, one bad row aborts the query.

Type promotion

coalesce, greatest, least, if, iff all coerce arguments to a common supertype. The rules follow SQL standard widening: integer + decimal -> decimal; integer + double -> double; date + timestamp -> timestamp. If the arguments have no common supertype the planner returns an error before execution.

case is stricter: every branch must produce the same type, or the planner adds explicit casts when it can. Mixed types without an obvious supertype fail at plan time.

NULL handling cheat sheet

Why no IIF (T-SQL)

T-SQL’s IIF(cond, then, else) is the same shape as iff. SQE registers iff (Snowflake) and if (Trino), both pointing at the same implementation, so a T-SQL IIF rename is the only change needed. We deliberately did not register a third name to keep the function table tight.

Why no Oracle / Snowflake DECODE

Snowflake’s DECODE(expr, search1, result1, ..., default) is a multi-way conditional with NULL = NULL match semantics. Two reasons it is not in SQE:

1. The name collides with DataFusion’s built-in decode(input, encoding), which decodes base64 / hex strings to binary. Registering a Snowflake-style DECODE under the same name would shadow the encoding helper and break any existing callsite.
2. CASE WHEN expr IS NOT DISTINCT FROM s1 THEN r1 ... END covers the same ground in standard SQL. (IS NOT DISTINCT FROM treats NULL = NULL as true.)

The audit row lives in the feature comparison so the conflict is visible.

String functions

DataFusion contributes ~35 string functions plus a unicode submodule and a regex submodule. SQE adds Trino-name aliases and a few extras.

Concatenation

Length and offsets

Slicing

Trimming

Case and padding

Predicates

Regular expressions

Hashing

Distance / phonetic

Encoding

See Encoding, hashing, URL for from_base64, to_base64, from_hex, to_hex, encode, decode.

Unicode normalization

Examples

Cleanse user input

SELECT
    initcap(trim(lower(name))) AS name_clean,
    regexp_replace(email, '\\s+', '') AS email_clean,
    coalesce(nullif(trim(comment), ''), 'no comment') AS comment_clean
FROM users;

Extract domain from URL

SELECT
    url,
    regexp_extract(url, 'https?://([^/]+)', 1) AS host_via_regex,
    url_extract_host(url) AS host_via_helper
FROM access_logs;

url_extract_host is dramatically faster than the regex version. Prefer it whenever the input is well-formed URLs.

Tokenise a sentence

SELECT id, token
FROM articles, UNNEST(split(body, ' ')) AS t(token)
WHERE length(token) > 3;

Mask sensitive data

SELECT
    user_id,
    sha256(email) AS email_hash,
    concat('***', right(phone, 4)) AS phone_masked
FROM users;

(For declarative masking via grants, see GRANT and REVOKE.)

What is NOT supported

• SOUNDEX_DIFFERENCE (T-SQL). use levenshtein(soundex(a), soundex(b)).
• PARSE_URL family with named parts. Use the url_extract_* family in Encoding, hashing, URL.
• STRING_SPLIT_TO_ARRAY. use split(s, delim).

Math functions

DataFusion contributes ~30 math functions. SQE adds a small set of Trino-named extras (e(), mod(), truncate(), sign()) plus base conversion and IEEE specials (infinity, nan).

Sign and rounding

Powers, logs, roots

Trigonometry

Modular and bit / base

SELECT to_base(255, 16);     -- 'ff'
SELECT from_base('ff', 16);  -- 255
SELECT to_base(8, 2);        -- '1000'

Random

IEEE specials

Statistical helpers

For aggregates (stddev, variance, corr, covar_*, regr_*), see Aggregate functions. The math page covers scalars only.

Examples

Bucketing and binning

SELECT
    floor(amount / 100) * 100 AS bucket,
    count(*)
FROM orders
GROUP BY 1
ORDER BY 1;

Geometric mean via logs

SELECT exp(avg(ln(price))) AS geo_mean FROM products WHERE price > 0;

DataFusion has no built-in geo_mean; the log identity is the standard workaround.

Distance from a reference point (Pythagorean)

SELECT
    name,
    sqrt(pow(x - 100, 2) + pow(y - 200, 2)) AS distance
FROM points
ORDER BY distance
LIMIT 10;

Hex and binary representations

SELECT
    n,
    to_base(n, 16) AS hex,
    to_base(n, 2)  AS bin,
    to_base(n, 8)  AS oct
FROM generate_series(0, 255) AS t(n);

Numeric type promotion

pow, log, exp always return Double regardless of input. abs, floor, ceil, round preserve the input type. +, -, * follow SQL standard widening: integer + decimal -> decimal; integer + double -> double; decimal + decimal -> decimal with combined precision.

/ between two integers in DataFusion returns Double, not integer. For integer division use floor(a / b) or div(a, b).

Decimal precision

DECIMAL(p, s) arithmetic widens precision per SQL standard. Two DECIMAL(18, 2) values multiplied produce DECIMAL(36, 4). Going beyond DECIMAL(38, ...) overflows; CAST or use Double.

Date and time

The largest single category in SQE. Two layers stack:

1. DataFusion native functions: date_part, date_trunc, date_bin, extract, now, the to_timestamp_* family, make_date. Powerful but uses DataFusion-specific names.
2. Trino aliases registered by sqe-trino-functions: year(), month(), day(), date_add(), date_diff(), format_datetime(). These cover every Trino date function so dbt-trino models work unmodified.

Snowflake and Spark also map well via the Trino layer.

Construction and current value

Extraction (year, month, day, …)

These return integer parts. SQE registers Trino names; DataFusion’s extract and date_part are also available for SQL standard syntax.