Give Power Users a SQL Editor: The Escape Hatch BI Needs

A SQL editor inside your BI tool is the escape hatch that keeps power users from exporting data and going rogue. No semantic layer, however well-built, answers every ad hoc question, so the moment a curated dropdown cannot express the question, your strongest analysts need a way to write raw SQL without leaving the tool. Give them one, and the truth stays in one place.

This is the case for treating the SQL editor as a first-class part of the BI experience, not a hidden developer feature. It is the difference between a power user who stays inside your governed environment and one who quietly builds a parallel reporting stack in a notebook nobody else can see.

The question the dropdowns cannot answer

Every governed BI model is a curated surface. You define metrics, dimensions, and relations so that the common questions become a few clicks: revenue by month, signups by channel, churn by cohort. That curation is the entire point, and for the majority of questions it works beautifully.

Then someone asks the question you did not anticipate. What is the median time between a customer's first and third purchase, segmented by the acquisition campaign, but only for accounts that upgraded within 30 days? It is a perfectly reasonable business question. It is also not in any dropdown, and it may never be, because it is a one-time investigation, not a recurring report.

This is the structural limit of curated analytics. A semantic layer optimizes for the questions you know about. Real analysis is mostly about the questions you did not. When the curated surface runs out, the analyst has two options: ask an engineer to extend the model and wait, or find a way to write the query themselves right now. The second option always wins, because the business does not wait.

Why power users quietly bypass BI tools

When a BI tool has no real SQL escape hatch, power users do not stop asking hard questions. They route around the tool. The pattern is familiar to anyone who has run a data team: an analyst exports a CSV, pulls it into a notebook or a standalone SQL client connected straight to the warehouse, and answers the question there. The dashboard tool becomes a viewer for executives while the actual analysis happens somewhere else entirely.

This is how a single source of truth quietly fragments. The number that ends up in the board deck was computed in a notebook against a hand-written query that nobody reviewed, using a revenue definition that may or may not match the governed metric. Two analysts answer the same question two ways. Now you are reconciling spreadsheets instead of trusting your BI layer, which is exactly the failure mode that governed self-serve analytics was supposed to prevent.

The deeper problem is that bypassing is invisible. There is no log entry that says "a power user gave up on the BI tool today." You only discover the parallel stack when two reports disagree in a meeting. By then the drift is already months old, and the trust cost is paid.

The escape hatch: raw SQL in the tool

The fix is not to anticipate every question. That is impossible. The fix is to make the escape hatch live inside the tool, so that when the curated surface runs out, the next step keeps the analyst in the same governed environment, on the same connection, with the same access controls.

A first-class SQL editor does three things that a CSV export plus a separate client cannot:

1. It uses the same governed connection. The analyst writes against the warehouse you already vetted, with the permissions you already granted, instead of a personal connection string copied into a notebook.
2. It keeps the work discoverable. A query written in the tool can be saved, shared, and reviewed. A query in someone's local notebook is invisible to everyone else.
3. It stays next to the definitions. When the editor sits beside your governed metrics, the analyst can see how revenue is defined and reuse that logic rather than reinventing it inconsistently.

The goal is not to replace the semantic layer with raw SQL. It is to make raw SQL the natural overflow channel of the same system, so the curated layer and the ad hoc query are two views of one source of truth rather than two competing ones.

Keeping ad-hoc SQL and governed metrics in one place

The interesting design question is how ad hoc SQL and governed metrics coexist without one undermining the other. If the SQL editor is a totally separate world, you are back to two sources of truth: the curated metrics for some questions, and raw queries for others, with no relationship between them.

The better model treats the curated definitions and the ad hoc query as points on a single spectrum. An analyst exploring in raw SQL should be able to reference the governed metrics and dimensions they already trust, rather than rewriting the revenue calculation from scratch every time. A query that starts as SELECT ... FROM orders can lean on the same definitions that power the dashboards, so the ad hoc answer agrees with the governed one by construction.

This is where a code-defined semantic layer earns its keep. Because the metrics, dimensions, and relations are real objects in the project rather than buried in a GUI, the SQL editor can sit beside them as one more way to interrogate the same model. The escape hatch does not bypass governance. It inherits it.

models:
  - name: orders
    sql: SELECT * FROM raw.orders
    metrics:
      - name: total_revenue
        expression: "SUM(amount)"
        description: "Revenue across all orders, governed definition"
    dimensions:
      - name: order_month
        expression: "DATE_TRUNC('month', created_at)"
        description: "Month an order was placed"

With that definition in the project, the dropdown question and the raw-SQL question both resolve total_revenue the same way. The power user gets freedom; the organization keeps consistency.

From a one-off query to a saved Insight

The last piece of a good escape hatch is the on-ramp back. Most one-off queries stay one-off, and that is fine. But every so often the throwaway investigation turns out to matter, and the question becomes a recurring one that other people will ask next week.

When that happens, the ad hoc query should be promotable into a reusable artifact rather than recreated by hand in the curated layer. The exploratory SQL that proved the insight should be able to graduate into an Insight, a defined visualization backed by the same model, with client-side filters, splits, and sorts available to everyone who opens it.

insights:
  - name: revenue-by-month
    description: "Monthly revenue, promoted from an ad hoc investigation"
    model: ${ref(orders)}
    props:
      type: bar
      x: ?{ ${ref(orders).order_month} }
      y: ?{ ${ref(orders).total_revenue} }
    interactions:
      - sort: ?{ ${ref(orders).order_month} ASC }

This is the loop that keeps a BI tool healthy. Curated metrics answer the known questions. The SQL editor answers the unknown ones. And the best of those unknowns get promoted into curated artifacts, so the surface grows from real demand instead of guesswork. The escape hatch is not just a safety valve; it is the discovery mechanism that tells you what to govern next.

The SQL editor in Visivo's Explorer

We built this thinking directly into Visivo's redesigned Explorer. The new Explorer ships a SQL editor as a first-class surface, so a power user who hits the edge of the curated model can drop into raw SQL against the same governed source without leaving the tool or exporting a file. It sits next to the project's models, metrics, and dimensions, which means the query you write by hand and the metrics you defined in YAML draw on the same definitions.

The point is not that raw SQL is better than the semantic layer. It is that the two belong together. A power user who can reach raw SQL when they need it, and promote a good query into a shared Insight when it matters, has no reason to fragment the truth into a private notebook. That is the whole job of the escape hatch: to keep your most capable analysts inside the governed system precisely when they are doing their most demanding work. The full breakdown of what shipped lives in the v1.0.80 release recap.

If you want to see how raw SQL, governed metrics, and shareable Insights fit together, the fastest path is to get started with a local project and browse the examples.

Previously in Visivo

This continues our ongoing series on building BI that respects how analysts actually work. In the previous post we looked at pivot tables in the modern data stack and why fast, reshapeable summarization still matters. Here we picked up the thread for the power user who needs to go beyond any pre-built view and write SQL directly.