Documentation/Connectors/Warehouse Connectors

Warehouse Connectors

Connect Snowflake, BigQuery, Databricks, ClickHouse, Redshift, or Microsoft Fabric as a live, read-only data source — no copy job, no sync schedule. Query it through the normal CRUD API or run ad-hoc analytical SQL through the Query console.

Overview

A warehouse connector is a connector whose source is an analytical SQL system — Snowflake, BigQuery, Databricks, ClickHouse, Redshift, or Microsoft Fabric — instead of an operational database. It behaves differently from a regular database connector in one important way: there is no sync worker and no local copy. Every read against a warehouse-backed schema is executed live against the warehouse at request time, and the typed result rows are returned straight to your app.

Once a table is bound, your app reads it exactly like any other schema — the same POST /api/v1/crud/{domain}/query your app already uses for synced data works unchanged. The connector, the live binding, and the source SQL are invisible to that call; the only visible difference is that the response carries access_mode: "warehouse" and writes are rejected. For a worked, end-to-end walkthrough of standing a source up, see Connect Your Database.

•No copy — nothing from the warehouse is stored in Supero; every read is a real query against your warehouse
•Read-only by design — every table on a warehouse connector is bound live_read; there is no sync mode and no write-through for these sources
•Typed results — each dialect driver coerces warehouse cell values (dates, decimals, timestamps, JSON) into real typed fields, not raw strings
•A Query console primitive (run_query) for ad-hoc, caller-composed analytical SQL against an attested read-only warehouse role
•Cost guards on every read — row caps, byte caps, and statement/drain timeouts, so a runaway query fails loudly instead of running up your warehouse bill

A warehouse connector serving reporting: the app queries Supero, Supero plans and executes SQL against the warehouse, and typed rows come back with no copy stored — No copy job in the middle. The read is the query.

ℹ️ Six supported warehouse sources

Snowflake, BigQuery, Databricks, ClickHouse, and Redshift share one HTTP-based warehouse driver. Microsoft Fabric is modeled as a warehouse too, but it speaks T-SQL over TDS and is routed through the SQL Server reader rather than the HTTP driver — see the Fabric notes below.

⚠️ Four of the six are not in the console picker today

Only Snowflake and Databricks appear as warehouse tiles in the admin console right now. ClickHouse, BigQuery, Redshift, and Fabric are not offered there while they finish testing — they are fully accepted over the API and over MCP, and an existing connector on one of them keeps working and keeps its type visible on its own detail page. Snowflake and Databricks are also the two that have been verified end to end against real accounts; the other four have been exercised in isolation rather than against a live warehouse, so run a proof-of-concept before committing to one.

The Live-Read, No-Copy Model

A regular ("sync") connector runs a worker that copies records from the source into Supero on a schedule, webhook, or manual trigger, and your app reads the local copy. A warehouse connector skips that entirely: the table is bound with mode "live_read", and every read is planned into a real SQL statement, sent to the warehouse over its native or HTTP API, and mapped back into typed rows before your app ever sees them.

This means data is always current — there is no staleness window between a sync run and the next one — but it also means read latency depends on your warehouse's own query performance, and repeated reads mean repeated warehouse compute. Design your schema and query patterns with that in mind, the same way you would for any live analytical source.

Querying a warehouse-backed schema

No special API — once the table is bound (see Configuring below), query it the same way you query any schema:

bash

curl -X POST https://api.supero.dev/api/v1/crud/acme-corp/query \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "type": "order_facts",
    "filters": {"region": "us-east"},
    "sort": [{"field": "order_date", "order": "desc"}],
    "limit": 25
  }'

json

{
  "success": true,
  "results": [
    {
      "uuid": "a1b2c3d4-...",
      "name": "ORD-88421",
      "fq_name": ["acme-corp", "order_facts", "ORD-88421"],
      "region": "us-east",
      "order_date": "2026-06-30T00:00:00Z",
      "amount": 412.50
    }
  ],
  "access_mode": "warehouse"
}

Every row still carries the standard identity envelope (uuid, name, fq_name, parent_type) so refs, get-by-uuid, and RBAC work exactly the way they do for a synced schema — Supero registers a stable identity for each source row the first time it is read. The response is stamped access_mode: "warehouse" so your app or dashboard can detect a read-only schema without a separate connector lookup. That stamp is also the only reliable proof that a read really reached your warehouse — see Live Data, Read-Only & Security.

Aggregates

Beyond list and query, a warehouse-backed schema supports the same aggregate primitive as any other schema, with four operations: stats, count_by, distinct, and time_series. An aggregate is planned into a single SQL statement and executed at the warehouse, so a count_by over a large fact table does not drag rows back to Supero to count them — which is usually the right way to build a dashboard tile against a warehouse.

⚠️ Writes are rejected

A create (POST), update (PUT), or delete (DELETE) against a warehouse-backed schema fails — there is no local store to write into, and warehouse connectors never write through to the source. If you need write access to the same system, use a regular database connector against it instead.

Read-Only by Design

Every table on a warehouse connector must be bound live_read. There is no warehouse "sync" (a whole-warehouse copy into Supero) and no live_readwrite (write-through) mode for an analytical source — reverse-ETL back into a warehouse is not available today.

target.tables[].mode you set	What happens
(unset)	Auto-promoted to live_read — the only valid mode for a warehouse table.
live_read	Accepted as-is.
sync	Rejected — connector create/update fails with a 400 naming the offending table.
live_readwrite	Rejected — same 400, since a warehouse source cannot be written through.

This check runs on connector create and on any update that touches target.tables, so you cannot silently drift a warehouse table into an unsupported mode later.

Per-Warehouse Config and Auth

Every warehouse is configured the same way as a database connector — plugin: "database" with a dialect-specific config object — but the fields and auth method differ per source. All five HTTP-driven warehouses share one dialect table:

db_type	Auth	Wire protocol	Key config fields
snowflake	Key-pair JWT (recommended), or OAuth client credentials	Snowflake SQL API (HTTP, async submit → poll)	account, host, warehouse, database, schema, role, username, private_key
bigquery	Service-account JSON (OAuth2)	BigQuery Jobs API (HTTP, async submit → poll)	project_id, dataset, location, service_account_json, maximum_bytes_billed
databricks	Personal access token, or OAuth-M2M (service principal)	SQL Statement Execution API (HTTP, async submit → poll)	host, warehouse_id, catalog, schema, token, client_id, client_secret
clickhouse	HTTP Basic auth	ClickHouse HTTP interface (synchronous — submit returns the final result)	host, port, secure, database, username, password
redshift	AWS IAM credentials (SigV4)	Redshift Data API (HTTP, async ExecuteStatement → DescribeStatement → GetStatementResult)	region, deployment (provisioned/serverless), cluster_identifier or workgroup_name, database, db_user or secret_arn

Secrets — passwords, private keys, service-account JSON, access keys — are referenced via the connector's secret_refs and resolved server-side at query time. A GET on a connector never echoes them back.

Snowflake example

json

{
  "plugin": "database",
  "config": {
    "db_type": "snowflake",
    "account": "ORG-ACCOUNT",
    "warehouse": "COMPUTE_WH",
    "database": "ANALYTICS",
    "schema": "PUBLIC",
    "role": "READER",
    "username": "SVC_SUPERO",
    "private_key": "-----BEGIN PRIVATE KEY-----...",
    "query_type": "table",
    "table_names": ["CUSTOMERS"]
  }
}

BigQuery example

json

{
  "plugin": "database",
  "config": {
    "db_type": "bigquery",
    "project_id": "my-gcp-project",
    "dataset": "analytics",
    "location": "US",
    "service_account_json": { "...": "..." },
    "query_type": "table",
    "table_names": ["orders"]
  }
}

Databricks example

json

{
  "plugin": "database",
  "config": {
    "db_type": "databricks",
    "host": "dbc-xxxxxxxx.cloud.databricks.com",
    "warehouse_id": "abc123def456",
    "catalog": "main",
    "schema": "sales",
    "token": "dapi...",
    "query_type": "table",
    "table_names": ["orders"]
  }
}

ClickHouse example

json

{
  "plugin": "database",
  "config": {
    "db_type": "clickhouse",
    "host": "myhost.clickhouse.cloud",
    "port": 8443,
    "secure": true,
    "database": "analytics",
    "username": "default",
    "password": "...",
    "query_type": "table",
    "table_names": ["events"]
  }
}

Redshift example (provisioned cluster)

json

{
  "plugin": "database",
  "config": {
    "db_type": "redshift",
    "region": "us-east-1",
    "deployment": "provisioned",
    "cluster_identifier": "analytics-cluster",
    "database": "analytics",
    "db_user": "reader",
    "query_type": "table",
    "table_names": ["orders"]
  }
}

For a serverless Redshift deployment, set deployment: "serverless" and workgroup_name instead of cluster_identifier, and provide secret_arn for the Data API to resolve credentials.

Microsoft Fabric

Fabric (a Warehouse or Lakehouse SQL-endpoint item) is configured with db_type: "fabric" — host, port (1433), database, item_type, and Entra ID service-principal credentials (client_id, client_secret, tenant_id). Entra ID service-principal auth is the only supported method; there is no SQL authentication path. Put the SQL connection string from the Fabric portal in host and the item name in database. Fabric speaks T-SQL over TDS, not the HTTP warehouse protocol the other five use, so it is planned through the SQL Server dialect internally. Table live-read works the same way as the other warehouses, but Fabric does not support the Query console (run_query) described below.

⚠️ Fabric live tables need a real primary key

The five HTTP-driven warehouses are exempt from the usual live-table identity requirement, because the read planner supplies its own deterministic ordering for them. Fabric is not exempt: it normalises to the SQL Server dialect, which keeps the strict primary-key requirement. A Fabric live table with no primary key is refused at save time.

Snowflake account identifier and JWT lifetime

Two Snowflake details account for most first-connection failures. The account identifier must be the uppercase organisation-account form with no region or cloud suffix appended — a lowercase or suffixed identifier authenticates as a different account and fails. And key-pair auth mints a JWT with a 59-minute life that is re-minted at 50 minutes, so a clock more than a few minutes out of sync on your side will produce intermittent authentication failures that look like a credential problem.

Configuring a Warehouse Connector

The end-to-end flow is: create the connector with its source config, optionally test connectivity, discover the tables you want, then set each table's live binding.

•Create — POST /api/v1/connectors with source ({plugin: "database", config: {...}} from the tables above), an initial target, and a trigger (at minimum {"type": "manual"}).
•Test connectivity (optional) — POST /api/v1/connectors/test-config runs the same driver used at query time against your config before you commit to it, so a passing test predicts a working connector rather than just confirming the payload parsed. It also returns the warehouse’s table list under sample_record.tables, scoped exactly the way discovery will scope it.
•Discover — POST /api/v1/connectors/{id}/discover samples the source and returns the columns for each table, which you need to build the live binding.
•Bind each table — set mode: "live_read" and a live block (field_map, columns, primary_key, tenant_binding) on the matching entry in target.tables[]; PUT the connector to save it. PUT is the only update verb on a connector — PATCH is not routed and returns 405.
•Tenant scoping — for a multi-tenant warehouse table, set tenant_binding to {"mode": "fixed", "tenant": "<tenant-name>"} (the whole connector serves one tenant) or {"mode": "column", "tenant_field": "...", "tenant_value_map": {...}, "on_unmapped_tenant": "deny"} (a column identifies the tenant per row). An unmapped tenant is denied by default — it never falls through unfiltered.

ℹ️ The create response comes back with null source, target, and trigger

A successful create returns the connector with source, target, and trigger as null. That is the response shape, not a sign that your configuration was dropped — but it does mean you cannot read the created source straight out of the create response. GET the connector back before you build the binding against it.

⚠️ Warehouse discovery samples at most 50 tables per run

A discovery run that is not given an explicit stream list will inspect up to 50 tables and report in its run stats that it capped, rather than silently truncating. On a wide warehouse, name the tables you want in the streams array. Sampled discovery is separately capped at 100 named streams per run; beyond that, send infer_from: "schema".

Warehouse discovery has two consequences worth knowing when you build a binding by hand: every discovered column comes back marked nullable, because warehouse result metadata carries no NOT NULL flag, and primary_key always comes back empty, because there is no primary-key introspection for these sources. Set primary_key yourself if you want get-by-uuid on an entity table.

json

{
  "name": "wh-clickhouse-events",
  "display_name": "ClickHouse live events",
  "enabled": true,
  "project_uuid": "8f14e45f-2d3a-4b1c-9e6a-1234567890ab",
  "source": {
    "plugin": "database",
    "config": {
      "db_type": "clickhouse",
      "host": "myhost.clickhouse.cloud",
      "port": 8443,
      "secure": true,
      "database": "analytics",
      "username": "default",
      "password": "...",
      "query_type": "table",
      "table_names": ["events"]
    }
  },
  "target": {
    "namespace": "wh",
    "tables": [{
      "source": "events",
      "schema": "event_fact",
      "operation": "upsert",
      "mode": "live_read",
      "stream_class": "event",
      "enabled": true,
      "live": {
        "field_map": {"id": "id", "region": "region", "amount": "amount"},
        "columns": [
          {"name": "id", "sql_type": "UUID"},
          {"name": "region", "sql_type": "String"},
          {"name": "amount", "sql_type": "Decimal(12,2)"}
        ],
        "primary_key": ["id"],
        "tenant_binding": {"mode": "fixed", "tenant": "northwind"},
        "hidden_fields": []
      }
    }]
  },
  "trigger": {"type": "manual", "allow_manual": true}
}

field_map is always {schema_field: source_column} — the direction the read planner expects. Any discovered column you don't explicitly rename gets an identity mapping automatically at discovery-confirm, so a table with no renames still works with an empty field_mapping.

Entity vs. event tables

stream_class distinguishes bounded dimension tables from time-series/fact tables:

stream_class	Meaning	get-by-uuid
entity	A real, non-temporal primary key — customers, devices. Full list/query/get-by-uuid/refs support.	Supported.
event	The primary key contains a temporal column, or there is no stable key — readings, logs. Addressed by dimension + time range instead of a single identity.	Returns 501 (address by query instead).

The Query Console (run_query)

Beyond the schema-shaped list/query/aggregate reads above, warehouse connectors expose one more primitive: run_query, an opaque, caller-composed analytical SELECT. It's the endpoint behind a SQL query console — the caller writes real SQL (including SQL generated by an AI assistant) and gets back raw columns, with no identity envelope and no automatic tenant filtering pushed into the query.

Because the platform cannot safely rewrite caller-composed SQL to add tenant scoping, run_query is gated tightly: it only works for a connector whose warehouse role is attested read-only and whose tenant binding is fixed (not column-based, since opaque SQL can't have a per-row tenant filter injected into it).

Requirement	How you satisfy it
Warehouse dialect	db_type must be snowflake, databricks, clickhouse, bigquery, or redshift. Fabric is not eligible (it doesn't use the warehouse SQL driver).
Attested read-only role	Set read_only_attested: true in the connector's source config. This is your attestation that the warehouse role/user you configured has no write, DDL, or table-creation rights — Supero's SQL guard is defense-in-depth, not a substitute for a least-privilege role.
Fixed tenant binding	Every table's tenant_binding must be {"mode": "fixed", ...}. A connector using column-based tenant scoping is rejected with 409 — it needs per-row filtering that opaque SQL can't carry.

ℹ️ Where it appears in the console

On a warehouse connector’s detail page, a fifth tab named Query appears alongside Overview, Schemas, Run History, and Settings — but only once read_only_attested is true on the connector and the dialect is eligible. Because Fabric is ineligible and four of the six warehouse types are not in the console picker today, the Query tab is reachable in practice on Snowflake and Databricks connectors created through the console.

bash

curl -X POST https://api.supero.dev/api/v1/connectors/CONNECTOR_ID/run_query \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT region, COUNT(*) AS orders, SUM(amount) AS total FROM order_facts WHERE order_date >= ? GROUP BY region ORDER BY total DESC",
    "params": ["2026-01-01"],
    "row_limit": 200
  }'

json

{
  "success": true,
  "data": {
    "rows": [
      {"region": "us-east", "orders": 1042, "total": 88213.50},
      {"region": "us-west", "orders": 811, "total": 71005.10}
    ],
    "columns": ["region", "orders", "total"],
    "result_count": 2,
    "stats": {},
    "tenant": null
  }
}

sql uses each warehouse's own native placeholder syntax (Snowflake/Databricks/Redshift positional or named markers, ClickHouse's typed {pN:Type} form, BigQuery's @pN) with params supplied positionally — never string-interpolate a value into the SQL text yourself.

The read-only SQL guard

Before your SQL ever reaches the warehouse, it is validated as a single read-only statement. This runs in addition to the read-only-role requirement above — defense-in-depth, not a replacement for it.

•Must be exactly one statement, and must start with SELECT or WITH (a leading "(" before SELECT is tolerated). A trailing single ";" is allowed and stripped.
•No embedded statement separators — a second ";" anywhere in the query is rejected.
•No write/DDL/control keywords anywhere in the statement (outside of string literals and comments): insert, update, delete, merge, upsert, drop, alter, create, truncate, rename, grant, revoke, call, exec, execute, copy, attach, detach, vacuum, kill, into, unload, lock, unlock.
•A query longer than 20,000 characters is rejected.

A rejected query returns 400 with a machine-readable code — empty_query, query_too_long, multiple_statements, not_a_select, or forbidden_keyword — plus a human-readable message naming the offending keyword.

Cost Controls

Warehouse queries are billed by the warehouse itself, so every live read — structured queries and run_query alike — runs under caps designed to fail a runaway query loudly rather than let it scan an unbounded amount of data.

Control	Default	Behavior when hit
Row cap	10,000 rows	A structured live-read query stops at the cap and marks source_meta.scan as "partial" — count is best-effort and never fails the read. run_query instead cancels the in-flight statement and fails the call with 413, since an opaque query has no safe way to signal "more available."
Byte cap	64 MiB buffered	The in-flight statement is cancelled and the call fails with 413 once Supero has buffered this much result data, regardless of row count.
Query deadline	90 seconds	The overall budget for one warehouse statement — submit, poll, and drain combined. The clock starts when the statement is submitted, not when the first row arrives. It is deliberately held under the platform’s own 120-second proxy timeout so the connector’s deadline fires first and you get a structured 504 or 413 rather than an opaque gateway timeout. run_query may set its own.
Statement timeout	60 seconds	Pushed to the warehouse itself as its own query timeout — the primary cost lever, since it stops the warehouse's compute, not just Supero's wait.
Result cache	60 s TTL, 32 MiB total, 4 MiB per entry	Repeated identical warehouse reads inside the window are served from memory instead of re-billing your warehouse — so a dashboard on a refresh timer with several viewers collapses onto one query. It is process memory only, never written to disk. The trade-off is that a warehouse number can be up to a minute stale.
Poll interval	1 second	How often an asynchronous statement is polled while it runs. It bounds how quickly a fast query returns, not how much it costs.

Databricks has two extra bounds of its own: a hybrid synchronous wait window of 30 seconds before the poll loop takes over, and a 25 MiB cap on inline result delivery. Neither is usually something you tune; they matter when you are diagnosing why a large Databricks result behaves differently from the same query on Snowflake.

These are result-side (drain-time) guards — a first line of defense is always your own SQL: add a LIMIT, filter on partitioned/clustered columns, and scope date ranges narrowly, especially for warehouses like Redshift that don't expose a native per-query byte cap of their own.

BigQuery bytes-billed cap

BigQuery additionally enforces maximumBytesBilled — a fail-closed cap on how many bytes a query is allowed to bill for, defaulting on so an unbounded SELECT * can't produce a surprise scan even if you didn't configure one. Override it per connector with the maximum_bytes_billed config field (set it to 0 to explicitly remove the cap).

ClickHouse result constraints

If your ClickHouse role has its own read-only constraint on max_result_rows or max_result_bytes, Supero's own caps are dropped automatically on a constraint violation and the query is retried once without them — your source-side constraint remains the effective limit rather than the read failing outright.

ℹ️ Query console usage counts toward your plan

run_query and other warehouse reads consume Power usage the same way other Supero reads do. Current plan allowances and any add-on packs are on the Billing screen in your dashboard at app.supero.dev/billing.

Failure Semantics

A source outage or connectivity problem — timeout, connection refused, a 5xx from the warehouse's API — is surfaced as an explicit error naming the connector rather than an empty result set. This matters because "no rows" and "the warehouse is unreachable" mean very different things to your app, and silently returning the former for the latter would look like a real, empty answer.

🚨 One case where an empty result does not mean an empty warehouse

Once the live binding for a schema has been resolved, a failure is always an error. Before that, in a narrow window, a read can be served against Supero’s empty local store instead — returning 200 with zero rows and no access_mode stamp. Assert on the access_mode stamp, not on the row count: a genuine warehouse read returns access_mode "warehouse". See Live Data, Read-Only & Security for the full description.

Situation	What you get
Warehouse unreachable / timed out / 5xx	An error naming the connector (502/503) — never results: [].
Auth rejected by the warehouse	A non-retryable authentication error — check credentials, account/user identifier casing, and (for Snowflake/Redshift) clock skew.
Unmapped field in a filter/sort/projection	400 — the field isn't in the table's discovered column set.
Unmapped tenant (column tenant_binding, on_unmapped_tenant: "deny")	403 — fails closed rather than returning unfiltered rows.
A hidden field used as a query target	400 — hidden_fields are rejected as query targets.
run_query cost guard tripped (row/byte cap, drain deadline)	413 — the in-flight statement is cancelled.
run_query SQL fails the read-only guard	400 with a machine-readable code (see Query Console above).

Next Steps

•Connectors Overview — the supported-sources matrix, the console walkthrough, and the full control-plane API
•Live Data, Read-Only & Security — how read-only, tenant isolation, and credential handling are enforced on the live path
•Warehouse Reporting — a worked example of building reporting on top of a warehouse connector
•Access Policies — hidden_fields, record scope, and how they apply to a live table
•Schema Design — fields, references, and extends for the schemas your live tables bind to

**Further reading.** The cost and timeout defaults behind these connectors, and why the kill switch belongs to you: Querying a warehouse you do not pay for.

← PreviousDatabase Connectors Next →File, HTTP API, Webhook & Public Catalog

On this page