Documentation/Under the hood/Querying a warehouse you do not pay for: five guardrails we had to build
Querying a warehouse you do not pay for: five guardrails we had to build
Guardrails for reading a customer's warehouse: a fail-closed byte cap, a timeout pushed to the source, an injected LIMIT, mandatory type mapping, and a self-service kill switch.
- connectors
- warehouses
- bigquery
- snowflake
- postgres
- cost-control
Overview
Every query you send to a customer's warehouse spends their money. Design like it.
---
When an application reads from its own database, a bad query costs you a slow page and an annoyed colleague.
When it reads live from a customer's Snowflake, BigQuery or Postgres, a bad query costs *them*. Their bytes scanned, the unit BigQuery bills in. Their credits, the unit Snowflake bills in. Their CPU, and eventually their vacuuming. And they did not write the query. We did, on their behalf, in response to somebody loading a dashboard.
That asymmetry changes what a sensible default looks like.
Five of ours follow, with the reasoning behind each. The reasoning transfers even if you never use any of our software, because the underlying problem belongs to anyone who has ever held a credential to somebody else's database.
1. A byte cap that fails closed, on by default
BigQuery bills by bytes scanned and will happily scan a very large table if you ask it to. It also accepts a
maximumBytesBilled parameter on a job: exceed it and the job **fails**. It does not run, and it does not invoice.That parameter defaults to unset. Unset means unlimited.
Ours defaults to 100 GiB and is set on every job we submit.
A customer can raise it in the connector's configuration, and can set it to zero to remove it entirely, which some do.
The direction of the default is the whole point. An unset cap fails **open** into a bill. A set cap fails **closed** into an error message, and an error message is a thing a human can read and act on, whereas a bill arrives four weeks later attached to somebody else's name.
A note on what we do not claim: BigQuery also offers a dry-run mode that returns an estimate without running the query. We do not use it. If you need a pre-flight estimate and not a hard cap, that is a gap, and the cap is not a substitute for it.
2. The timeout belongs at the source, not at your client
A client-side timeout stops *you* waiting.
It does not stop the warehouse working.
Cancel an HTTP request to Snowflake and the query keeps running until it finishes or the server gives up. You have stopped paying attention. You have not stopped paying.
So the statement timeout goes to the source, as a parameter of the query, defaulted to 60 seconds. Snowflake and ClickHouse honour that as a real statement timeout and kill their own work.
The others are weaker and you should know which is which. BigQuery's parameter bounds how long we wait for a response, not how long the job runs. Databricks is explicitly told to carry on past the wait. Redshift takes no statement timeout from us at all. So this guardrail is real on two engines out of five, and on the other three the honest description is that we stop waiting. Per-driver details like that are what make a single abstraction across five warehouse engines tedious to build, and are also exactly why it is worth having built once instead of five times, in five connectors that each discovered the problem separately. Five, not the six warehouses we connect to: Microsoft Fabric speaks SQL Server on the wire and is read through the T-SQL path rather than an HTTP driver, so none of the knobs in this section reach it.
3. The Postgres version of the same problem, and it is worse
Operational Postgres is a different case from a warehouse, and the situation there was genuinely bad before we fixed it.
There was no query timeout anywhere on that path. A connect timeout bounds the handshake and nothing else. Our own layer gives up after 120 seconds, at which point it stops reading the socket, and **Postgres does not notice a client that stopped reading.** The query carries on.
Two consequences, and the second is the one that matters.
An abandoned read keeps burning the customer's CPU for as long as the query would have taken anyway.
Worse: a read at a deep
OFFSET holds a transaction snapshot open the entire time. Postgres reclaims superseded row versions in the background, and it cannot reclaim anything a still-open snapshot might need to see, so those tables stop being vacuumed and start to bloat.That degrades a database we do not operate, cannot see, and will not be the first to notice is unwell.
The fix sends
statement_timeout and idle_in_transaction_session_timeout in the connection's startup packet instead of a SET afterwards. Same effect, zero extra round trips, and no window between connecting and being bounded.4. The kill switch belongs to the customer
The startup packet also carries an
application_name.If I were the customer here and not the vendor, this is the guardrail I would want most. Every connection we open identifies itself, so a DBA can see exactly which sessions are ours in
pg_stat_activity and terminate them with pg_terminate_backend filtered by that name, without touching anything else and without opening a support ticket.With one caveat we would rather state than have found. Some poolers reject unknown startup parameters, and when that happens the connection is retried without them — which drops
application_name and both timeouts together. That is the deployment shape where a vendor connection is *most* likely to be pooled, so the lever is least reliable exactly where you would reach for it. Check for our application_name in pg_stat_activity before you rely on it being there — though the honest reading of that instruction is that it is the wrong way round. The whole argument for the lever is that it should not depend on our cooperation, and then we hand you a manual step and make its absence your problem. We know when we retried a connection without the startup parameters. Reading current_setting('application_name') back after connecting and saying so loudly is the version of this that does not need a checklist, and it is on us to do rather than on you to remember.It is a revocation lever that does not require us to be awake, cooperative, or reachable. The customer does not have to ask. That is what makes it worth more than any promise in a contract about how quickly we would respond to a request to stop. A promise is a thing you have to trust. A
pg_terminate_backend is a thing you can run.Any vendor connecting to a customer's database should set this, and the reason most do not is that it is only useful on the day the vendor is the problem.
5. Type mapping is not a nicety
Warehouse REST responses come back as positional arrays of **stringified** cells. Every value, whatever its declared type, arrives as text.
So the driver must coerce each cell back to its real type, keyed by the alias in the
SELECT. That sounds like tidying-up, and it is load-bearing for a reason that is not obvious.Incremental sync tracks a watermark, usually a modified-time column, and asks for rows newer than it. If a timestamp flows through as the *string*
"2026-02-14T06:30:00Z" instead of a datetime, the watermark comparison stops meaning what it says. The sync does not error. It re-reads everything, or it reads nothing, and it reports success either way.The same class of bug, one layer up, silently empties page two of a paginated read: Four ways a list endpoint lies to you has that one.
What we refuse to do, on purpose
An arbitrary aggregation pipeline against a live warehouse is not translated into SQL. It returns
501.We could have translated the easy 80% of it and shipped it. That would mean the hard 20% either produced silently wrong numbers or produced a confusing error deep in someone else's query planner, and a dashboard that shows a wrong total is worse than a dashboard that shows an error. Only one of them gets reported.
Every read also gets a
LIMIT injected, capped at 10,000 rows, plus a deterministic tiebreak in the ORDER BY so that paging is stable. T-SQL has no LIMIT and needs OFFSET/FETCH, and requires an ORDER BY to use it, which the injected tiebreak happens to provide. A limit of zero is special-cased, because FETCH NEXT 0 ROWS ONLY is a syntax error, not an empty result.And warehouses are read-only. There is no sync-into-our-storage path for them and no write-back. That is a smaller feature set than we could plausibly claim, and it is the shape analytics engines actually have.
The failure we caused by failing loudly
Incremental sync needs a cursor column. An earlier version validated that column strictly and **failed the entire run** if it was missing. That seems right by everything written above.
It was a production outage.
A customer selected several tables, only some of which carried the column. The run aborted for all of them. That included the tables which had already synced correctly and had nothing whatsoever wrong with them.
The fix degrades **per table** instead: the tables with the column sync incrementally, the ones without fall back to a full read, and the degradation is reported durably against the run. A log line in a pod that gets deleted is not a report. An un-checkable configuration should become a visible per-table degradation, not an outage.
Fail closed on spending someone else's money.
Do not fail closed on an entire multi-table sync because one table is shaped differently than you expected. The first is a guardrail around an irreversible cost that lands on a person who never asked for it; the second is a guardrail around a configuration mismatch that costs nothing and is trivially recoverable, and applying the same reflex to both is how a safety mechanism becomes the outage.
Knowing which of the two you are looking at is most of the job.
(Disclosure: I work on Supero. We hold credentials to customer-owned databases and warehouses, so the defaults above are ours to get right. The reasoning is not proprietary, and the
application_name recommendation applies to every vendor you have ever given a database credential to.)On this page