When is owning a Rego-to-SQL residual translation layer worth it over hand-writing the compliance query?
answer
- one source of truth versus two artifacts
- duplication cost scales, translator cost is fixed
- who maintains it in a year
- drift is quiet and asymmetric
- load it as data first if it fits
basics
~20 sIt pays when the same rules must be reported over several data stores and also enforced elsewhere, so one policy stays the single source of truth. For one stable rule over one store, a hand-written query is cheaper and easier to defend.
solid answer
~50 sThe honest framing is that pushdown buys you one source of truth and charges you a translator to maintain. Hand-writing the SQL means two artifacts that must agree forever — and the day they drift, the report says the estate is clean while the gate is still blocking changes, and nobody can tell an auditor which one expressed the control. Compiling the residual removes that drift by construction, but you now own translation code, dialect coverage, a policy for untranslatable clauses, and the job of debugging a machine-generated `WHERE` clause at the worst possible moment. I look at three things: how many rules and how many stores, whether the same policy is also enforced on changes, and who is going to maintain the translator in a year. One rule, one store, one team — write the query and keep them side by side in review. Dozens of rules across several stores that also gate changes — build the translator, and treat it as infrastructure with its own tests.
go deeper
Understand the core tension: writing the rule twice risks the two copies disagreeing, and deriving the query from the policy avoids that but adds code someone has to maintain.
Be able to name the concrete costs on each side — drift and fixture tests versus dialect coverage, untranslatable clauses and debugging generated queries — rather than declaring one approach better.
An interviewer expects you to reach for the cheap option first: load the inventory as data if it fits, and pin a hand-written query with shared fixtures before contemplating a translator.
Own the decision as rules times stores against a fixed engineering cost, name who maintains the result, and state what you would show an auditor to prove the report and the rule are the same rule.
## The decision, stated plainly You can answer "which resources are out of policy" two ways. Write the query by hand in the store's language, or compile the Rego rule with the resource set unknown and translate the residual into that query. Both produce a list. They differ in what you own afterwards. ## What hand-writing costs A hand-written query is fast to produce, readable by anyone who knows SQL, and reviewable on its own. Its cost is **duplication**: the rule now exists twice, in two languages, maintained by people who may not both be in the room when one changes. Drift is not hypothetical; it is the default outcome over a year. The failure is quiet and asymmetric — the report says the estate is clean while the enforcement path is still blocking changes, or the reverse — and when an auditor asks which artifact expressed the control, the true answer is "neither, exactly". Drift can be managed. You can pin the two together with tests that run the same fixtures through the policy and the query and assert the same verdicts. That is a real mitigation and it is much cheaper than a translator. It is also the thing teams promise to do and stop doing. ## What the translator costs Compiling the residual removes drift by construction: there is one rule, and the query is derived from it. The bill arrives as code you now own. - **Coverage.** Rego expresses things SQL does not, and every store dialect differs. You will meet a residual with a regex, a cross-field arithmetic relation, or a lookup into another document, and you need a defined behaviour for it — over-fetch and finish the evaluation in OPA is the sound one, but it is more machinery. - **Correctness.** A translator that is subtly wrong is worse than a hand-written query that is obviously wrong, because nobody reads its output. Polarity, the empty-result and always-true cases, and disjunction handling all need tests of their own. - **Debuggability.** When the report looks wrong at 6pm, someone is reading generated SQL and trying to map it back to a rule body. That person should not be the only one who understands the translator. - **Ownership.** This is infrastructure with a bus factor. If it was a side project of one enthusiastic engineer, it becomes a liability the moment they change teams. ## The three questions that decide it **How many rules and how many stores?** One rule over one inventory does not justify a translation layer. Thirty rules over an inventory, a cloud config store and a ticketing system does — the duplication cost scales with rules times stores, the translator cost is roughly fixed. **Is the same policy also enforced somewhere else?** If the rule only ever produces reports, duplication is contained. If the same rule also decides changes, then "the report and the gate apply the same rule" is a property you will be asked to demonstrate, and deriving one from the other is the strongest way to demonstrate it. **Who maintains it in a year?** If the answer is a platform team with tests and a rotation, build it. If the answer is a name rather than a team, write the query and pin it with fixtures. ## The middle path most teams should take first Before building anything, check whether you can skip the problem: if the inventory fits comfortably in memory, load it as `data` and evaluate the rule normally. No translator, no duplication, one rule. It stops working at some estate size, but it buys you the time to learn which rules actually matter, and the migration to pushdown later is a change of plumbing rather than of policy. ## What you tell the auditor either way Whichever path you pick, the deliverable is the same: a list that is traceable to a specific policy revision, produced by a mechanism you can describe. With a translator, that trace is the compile request and the generated query. With a hand-written query, it is the query plus the fixture tests proving it agrees with the policy. What you must not present is a list whose provenance is somebody's saved query with no link back to the rule at all.
- If you keep a hand-written query, how do you stop it drifting from the policy?Pin them with shared fixtures: a set of resource records run through both the Rego rule and the query, asserting identical verdicts, in the same pipeline that gates policy changes. It is far cheaper than a translator and catches the common drift. Its weakness is that it only covers the cases someone thought to write down.
- What would make you abandon a translator you had already built?Two signals. If most residuals stop translating cleanly — because the policies grew regex, cross-field or cross-document conditions — you are over-fetching so often that the pushdown is not earning anything. And if nobody but the original author can debug it, the correctness risk has outgrown the drift risk it was built to remove.
- How do you justify the translator's cost to a lead who sees only a working report today?Frame it as rules times stores, not as one report. The duplication cost grows with every rule and every store you add and is paid in silent wrong answers; the translator is a fixed cost paid once, in code with tests. If the estate is genuinely one rule and one store, the honest answer is that you cannot justify it and should not build it.
saying these in an interview costs you the question
- Builds the translator before proving the duplication hurts
- Ignores that the inventory might simply fit in memory
- Treats generated SQL as inherently trustworthy
- Assumes fixture tests will keep two artifacts aligned forever
- Presents a saved query as evidence with no link to the policy