QA and PROD without a second database
Ask a small engineering team how they separate test from production data work and you usually get one of three answers.
"We have a staging database." Sometimes true, and good, though it's often months out of date — so passing there proves less than people think.
"We comment out the production bit before running." Honest, and terrifying.
"We have report.sql and report_prod.sql." The most common, and the one that quietly rots: the two files drift, and nobody
can say which is correct.
None of these are laziness. They're what you get when environments are a convention rather than a mechanism. Here's the mechanism we chose instead.
The model: one query, values per environment¶
A data step holds one piece of SQL. Anything that differs between environments becomes a placeholder:
SELECT customer_id, SUM(amount) AS revenue
FROM {{ schema }}.orders
WHERE created_at >= NOW() - INTERVAL '{{ window_days }} days'
GROUP BY 1;
Each {{ placeholder }} gets a row in a table with a column per environment. The query is one object in every environment; only the values move.
That's the whole idea. What follows is the set of decisions that make it hold up — each of which costs something.
QA and PROD are pinned, not optional¶
Every placeholder must have a QA row and a PROD row. The save flow enforces it: placeholders are detected in your SQL, the empty pairs created, and you're prompted before you can commit.
The cost is friction at exactly the wrong moment: you're trying to save a query and the tool wants to talk about production. We kept it, because the alternative is a step that runs fine in QA and fails in production on a value nobody remembered to set — and that failure lands at 3am, not at save time.
You can add more environments (STAGING, DEV, whatever you run) from the Variables tab. QA and PROD are the two that can't be absent.
Substitution happens on the server¶
The obvious implementation is to substitute values in the browser and send finished SQL. We do the opposite: the editor sends the raw SQL plus the environment, and the server pulls the values and substitutes before the query reaches your warehouse.
Any other design makes the environment selector a lie. If the browser has already chosen the values, the dropdown next to Run is describing something that already happened. Substituting server-side keeps the selection real: one saved step, two sets of inputs.
It also means the browser never holds your production values.
Running defaults to QA¶
The control next to Run starts on QA every time. Not the last thing you used, not the environment of the deployment you were just looking at.
Small decision, specific target: the half-finished query you run just to see if it parses. The dangerous case isn't deliberately running against production, it's running against production by momentum. Defaulting to QA makes the dangerous thing a choice — and when you make it, the control turns amber so you can see what you picked.
Deployments pin a version¶
Editing a step creates a new version. A deployment points at a specific one.
So opening a step and improving the SQL can't change what production does tonight. Promotion is separate and deliberate: you move the deployment to the new version when you're ready.
The cost is one moment of confusion the first time: I fixed it, why is the old one still running? That beats the alternative, where every edit is a silent deploy.
What this doesn't give you¶
Worth being straight about the limits.
It isn't a second database. If your QA values point at the same warehouse as PROD, you're separating inputs, not blast radius. Pointing QA at a separate schema or database is still on you.
It isn't a test suite. Environment separation stops you running the wrong thing. It doesn't tell you whether the results are right: no assertion runs, nothing goes red when a row count halves. Assertions that gate a deployment are coming. Today this is the safety half of testing, not the correctness half.
It won't stop a determined mistake. Choose PROD, run something destructive, and it does exactly what you asked. The design makes that a decision rather than an accident, which is about as far as a tool should go.
The shape of the argument¶
Every one of these trades convenience for a smaller class of 3am failures. Enforced pairs cost friction at save time. Server-side substitution costs a round trip. QA-by-default costs a click when you genuinely wanted PROD. Pinned versions cost a promotion step.
We think that's the right trade for a team where one engineer owns the data work and there's no platform team to catch mistakes. If you'd have called any of them differently, the reasoning above is the part worth arguing with.
🧩 Keywords¶
environments, qa vs prod, sql variables, data pipeline safety, versioning, deployment