The design thinking behind GSQL

When Grant and I started building Graphene, the first big design question we faced was how a coding agent should actually talk to data. There are two opposing answers in the community right now.

One side argues that semantic layers are key. Agents shouldn’t write raw SQL; they should call well-defined metrics through a constrained API. The other side argues that semantic layers are overly constraining to agents, and that SQL coupled with markdown is all you need.

Both camps make pretty good points. So which do you choose?

What semantic layers get right

Semantic layers provide three primary benefits to agents:

Metrics you invoke. A metric like EBITDA (earnings before interest, taxes, depreciation, and amortisation) is probably complex and built from other metrics, each with its own definition. Documenting all of that in markdown means the agent has to assemble the full SQL from prose every time, and it will get the definition subtly wrong in different ways across different queries. Semantic layers solve this by exposing metrics as first-class query components that you invoke by reference, like a function call.

Fan-out protection. SQL silently allows you to aggregate across a join that duplicates rows. If you sum orders.amount after joining order_items (many items per order), you get a very large, very wrong number. Semantic layers such as LookML rewrite such queries to deduplicate data. The resulting SQL is hard to read, but the user gets the correct answer, and an entire class of silent bugs goes away.

Metadata. If you intend to display a number to a human, you will want to format it nicely. A DECIMAL that represents money should have a currency symbol and two decimal places. An INT that represents a year should not have a thousands separator. Semantic layers attach metadata about the semantic meaning of data so that there can be sensible defaults in the presentation layer without needing agent intervention.

What SQL with markdown gets right

On the other hand, simply using SQL and markdown has its benefits:

Agent fluency. SQL is orders of magnitude better represented in pretraining data than the query APIs of semantic layers. This means the agent has far more familiarity with SQL, and has seen many more analytics solutions in SQL than it has in any other query API.

A higher ceiling. Real data work involves CTEs, subqueries, window functions, set operations, and a wide library of functions. Semantic layer APIs don’t offer these capabilities, so agents are limited in what they can do.

GSQL: why not both?

As we dug deeper, our realization was that these concepts aren’t actually mutually exclusive. So why not both? Can’t we just add the semantic layer features into a query API that looks and feels like regular SQL?

And that’s exactly what GSQL is. It gives you the benefits of both camps.

Here’s a simple two-table model.

table orders (

  -- Base columns

  id BIGINT
  user_id BIGINT
  created_at DATETIME
  status STRING -- One of 'Processing', 'Shipped', 'Complete', 'Cancelled', 'Returned'
  amount FLOAT -- Amount paid by customer #currency=USD
  cost FLOAT -- Cost of materials #currency=USD

  -- Join relationships

  join one users on user_id = users.id

  -- Dimensions

  revenue_recognized: status in ('Processing', 'Shipped', 'Complete')
  
  -- Measures
  
  revenue: sum(case when revenue_recognized then amount else 0 end) #currency=USD
  cogs: sum(case when revenue_recognized then cost else 0 end) #currency=USD
  profit: revenue - cogs #currency=USD
  profit_margin: profit / revenue #ratio
)

table users (
  id BIGINT
  name VARCHAR
  email VARCHAR
  age INTEGER
  country_code VARCHAR

  join many orders on id = orders.user_id
)

You declare your metrics and your join graph once, in the model. Then you query in regular SQL, with the model’s vocabulary available everywhere. For example, this query returns the top 10 users by profit generated:

-- Agent writes this
select
  users.name, -- The dot operator traverses the modeled join
  profit -- Graphene will expand this into its full aggregate expression
from orders -- A join statement here is not needed
group by 1
order by 2 desc
limit 10

Graphene translates this into database-specific SQL and runs the query against your connected database:

-- Graphene runs this
select
  users.name,
  sum(case when status in ('Processing', 'Shipped', 'Complete') then amount else 0 end) - sum(case when status in ('Processing', 'Shipped', 'Complete') then cost else 0 end) as profit
from orders
left outer join users on orders.user_id = users.id
group by 1
order by 2 desc
limit 10

A few things to notice. The metric profit is referenced as a single token, with no opportunity for definition drift. The user-to-orders join is resolved automatically from the model—no join clause needed. The agent didn’t have to learn a new language to leverage these new concepts.

Underneath the surface, the parser knows that profit is an aggregate, and with its knowledge of the join graph it can prevent silent fan-outs. It carries the formatting metadata each measure declares, and can even infer it in some cases (eg. extract(year from dt) automatically attaches timeGrain=year metadata). And because GSQL is SQL, the agent still has the full ceiling: CTEs, subqueries, window functions, and over 200 functions are available the moment they’re needed.

---

We’ve spent a lot of time inside agent-driven data work, and what’s become clear is that accuracy without sacrificing flexibility is essence of the engineering problem. GSQL is the foundation we landed on. Everything else we’re building in Graphene leverages it.

If you want to try it, the open source repo is on GitHub. We’d love to hear what you think.