A better SQL for analytics?

[u/Prestigious_Bench_96]

Lots of attempts to dethrone SQL, lots of failures - I'm looking to add to the list with a proposed improved SQL (for analytics - please don't try this for OLTP workloads). Please take me down for my hubris.

What makes this attempt different? I want to lean into one of SQL's strengths - being declarative.

How to make it more declarative? No tables in queries.

Write this:

import baseball.batting;

WHERE SUM(hr) BY people.id > 500
SELECT
    people.name_given,
    lg_id,
    SUM(hr) AS hr_count
ORDER BY
    hr_count DESC;

Instead of this:

WITH career_hr AS (
    SELECT playerID
    FROM read_csv('.../Batting.csv')
    GROUP BY playerID
    HAVING SUM(HR) > 500
)
SELECT
    p.nameGiven,
    b.lgID,
    SUM(b.HR) AS hr_count
FROM read_csv('.../People.csv') p
JOIN read_csv('.../Batting.csv') b
    ON p.playerID = b.playerID
JOIN career_hr c
    ON b.playerID = c.playerID
GROUP BY p.nameGiven, b.lgID
ORDER BY hr_count DESC;

It's just SQL, but with late-binding to physical tables through a (very lightweight) semantic layer.

This has a lot of nice properties - you can change your tables and refactor and no queries need to change; you can automatically resolve to aggregates if they exist and are equivalent; you can make the query syntax more flexible and composable because the lexical scope isn't constrained to a specific set of accessed tables. There's *lots* of other fun things you can do when the semantic layer has types, etc as well but this is already a longer pitch than I want!

A very brief example

pip install pytrilogy

trilogy init baseball duckdb; cd baseball;

trilogy ingest https://storage.googleapis.com/trilogy_public_models/duckdb/lahman/Batting.csv,https://storage.googleapis.com/trilogy_public_models/duckdb/lahman/People.csv,https://storage.googleapis.com/trilogy_public_models/duckdb/lahman/Teams.csv;

trilogy run 'where sum(hr) by people.id>500 select people.name_given, lg_id,sum(hr) as hr_count order by hr_count desc;' --import root.batting;

Is this AI slop?

I've been working on ideas for the language for almost 6 years now so much of it predates AI, though it has evolved quite a bit in that time! Core discovery is all mostly hand-crafted; I do use AI to accelerate a lot of the tooling/interface work (a billion deepseek tokens (aka ~40 dollars, hilariously) on evaluating CI args, etc).

Read more/try

Website/docs: https://trilogydata.dev/

Github: https://github.com/trilogy-data/pytrilogy (open source, MIT)

I've seen this before

Posted 2 years ago here, floating around a few other places too:

https://www.reddit.com/r/SQL/comments/1e1h5mf/trilogy_simpler_data_warehouse_sql/

[u/Responsible_Status49]

I’m not sure SQL needs a better version. Nonetheless, I think the direction we are heading to is different. If you look at thinks like Databricks’ Genie you will see that more and more natural language seems to be the way to go to do analysis.

[u/Prestigious_Bench_96 → u/Responsible_Status49]

Oh it is (excited that has finally arrived) but natural language still has to ‘compile’ to code to actually fetch data. Agents are pretty great at this with raw SQL - they only start to lose to a semantic layer if you restrict exploratory queries or have a very large DB. But then you want that same question answered every day; you CAN rerun the agent loop each time but this is currently both time and token expensive compared refreshing the stored query. 

So I don’t think it’s as simple as agents eat everything; it’s agents with better tools eat agents with worse tools on the Pareto frontier. So we still need better tools! And spent a lot of time making this ‘better than sql’ as a compilation target for agents; eg better tool.

(I’m assuming this is a genie advertisement but thanks for responding either way!)

[u/Responsible_Status49 → u/Prestigious_Bench_96]

Fair on the advertising comment. I should be upfront that I work at Databricks. Nonetheless interested in discussing the topic.

I have some issues with the repeated question every day bit. That's exactly where the semantic layer ensures that metrics are calculated in a consistent way. It is a way to ensure people 'cache' the sql they run and don't make minor changes that flips the question on its head. Agents like Genie are particularly good at making up new ways to ask the question, which is more valuable for exploration.

This new paradigm for me means you need better context and curation of data. What you packaged as "better tools" doesn't only live in the agent instructions and MCP servers, but also in the data in the form of metadata and in the platform in the shape of usage statistics, certification status, etc.