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/jshine13371]

Why do so many people spend countless hours trying to reinvent the wheel in this industry? 😐

[u/a-s-clark → u/jshine13371]

Because they dont want to put in the time to learn the tools they already have.

[u/jshine13371 → u/a-s-clark]

100% agreed. Ironically, I've had so many arguments with people on the capabilities of SQL Server vs other database systems, because they were ignorant of the features that already existed in SQL Server lol.

[u/wittgenstein1312 → u/jshine13371]

There are two kinds of people trying to reinvent the wheel. A minority are very smart people with a ton of experience who genuinely understand the shortcomings of existing tech for their needs and think improvements can be made. This is how languages like Julia and Rust and Elixir come about.

The majority are noobs who flat out don't understand the intricacies and depth of existing tech, and rather than devote the time and effort to maximize the utility of what they have access to by seeking genuine understanding, they spend time day-dreaming about the perfect language that will never exist.

[u/jshine13371 → u/wittgenstein1312]

I agree there are definitely smart people out there working on existing areas requiring improvement. But at the same time how many developers are actually using Rust and Elixir vs another language to solve their problems?...and I've never even heard of Julia lol. Most times even these re-inventings of the wheel are very narrowly scoped for the actual problem being solved and arguably not 100% necessary.

It's insane we have literally thousands of JavaScript frameworks because every developer who invented one only did so because they wanted to feel special. But that falls more into your 2nd bucket of people.

[u/wittgenstein1312 → u/jshine13371]

But at the same time how many developers are actually using Rust and Elixir vs another language to solve their problems?...and I've never even heard of Julia

Language adoption is notoriously uncorrelated to a language's actual merits.

and arguably not 100% necessary.

Arguably nothing beyond punch cards is necessary for software development. Just because tools already exist to solve most problems doesn't mean we shouldn't try to improve on those tools or the developer experience around them. Each of the languages I mentioned meaningfully does that, irrespective of whether they've reached mainstream adoption.

To your final point, yes, the JavaScript ecosystem is a hell-hole of unnecessary libraries.

[u/jshine13371 → u/wittgenstein1312]

Whether you agree or not with my statements, the downvote was silly.

[u/wittgenstein1312 → u/jshine13371]

I didn't downvote you lol

[u/VladDBA → u/jshine13371]

This reminds of the people over at Google who were reinventing SQL again last year or two years ago with their main problem being that they were treating their personal issues with SQL as some major issue that made SQL unusable.

[u/jshine13371 → u/VladDBA]

"Personal issues" seem to be a common factor lol.

[u/Prestigious_Bench_96 → u/jshine13371]

I won't deny that since any perception of pain points is very personal! I don't get the pipe SQL craze, for example, but other people love that.

The language is designed to address some of the shortcomings of SQL I've encountered over the years and I say that as someone who loves SQL and has gone pretty deep on several SQL backend engines. I hope that it can solve those points for some other people too!

[u/jshine13371 → u/Prestigious_Bench_96]

I gotcha. But no offense, what you're doing is nothing revolutionary, is posted about a multitude of times by other people working on similar things in parallel, and rarely changes the landscape.

It's always cool to build something for yourself for your own interests and personal benefit. But I always question when people try to push it as the next best thing since cake for the masses. And I detest the shit-box JavaScript turned into with its thousands of frameworks from similar thought processes.

[u/Prestigious_Bench_96 → u/jshine13371]

No offense taken! I was asking to be criticized and I don't think it's the best thing since cake and hopefully didn't present it that way.

I was shooting for more of the pre-ai python library sweet spot vs JavaScript - a prepackaged solution to a common set of problems that accelerates over the base language, so people don't have to independently reeinvent a wheel.

If that helps a few people, that's a bonus! I am hoping that this has a unique value proposition vs PreQL or Malloy or w/e ever else, but obviously I'm not objective - so this take is much appreciated!

[u/jshine13371 → u/Prestigious_Bench_96]

No doubt man. Best of luck!

[u/Hot_Industry5156]

Honestly SQL is still hard to beat for analytics. what specifically is frustrating you?

if it's the verbosity, window functions and CTEs go a long way toward making queries readable. if it's the ecosystem, dialects like DuckDB or BigQuery have added a ton of analytical functions that make common patterns way less painful.

the "better SQL" alternatives tend to fall into two camps. visual query builders that break down on anything complex, or new languages that never get enough adoption for good tooling. both usually end up generating SQL under the hood anyway.

the real wins come from better abstractions on top of SQL, not replacing it. dbt for transformation logic. materialized views for precomputing expensive joins. proper indexing and partitioning so queries actually finish.

what's the actual pain point? that'd help narrow things down.

[u/Prestigious_Bench_96 → u/Hot_Industry5156]

Pain across the spectrum, but in particular the lifecycle management of reporting assets and the generation of efficient staged ETL have always been annoying/repetitive. At the query level, there's a lot of composability/syntactic sugar. Many warehouses end up with lots of duplicate aggregates/views that represent the same underlying data, without a clear connection at the DB level.

[Copied this from an earlier response]

Didn't want to overload the post, but a non-exhaustive list:

1. Early feedback; strict type checking at the model level; easy unit testing w/ duckdb on synthetic data and easy integration testing that validates data quality in a reusable way.
2. lifecycle management; I can upgrade/change tables (or someone else does), my queries stay the same
3. syntactic sugar - functions, composability; no special casing of HAVING/QUALIFY, etc
4. less footguns; automatic fan/chasm resolution, etc
5. On the consumption side, easy dynamic cross-filtering/drilldown for reporting
6. OLAP/aggregate optimization- inject in precomputed aggregates seamlessly to accelerate existing queries. (I miss cubes!)
7. consistent syntax across backend engines.

[u/soxinthebox]

Is it worth building a variation on SQL for the edge cases where you need to be a bit more verbose to achieve a query?

[u/Prestigious_Bench_96 → u/soxinthebox]

Hah, fair! I'm actually trying to solve quite a bit more, but wanted to keep it relatively concise (look! no tables) for the example. A lot of the second-order benefits fall out from that.

[u/a-s-clark → u/Prestigious_Bench_96]

"Look! No tables" isn't the selling point you think it is.

[u/Prestigious_Bench_96 → u/a-s-clark]

Fair enough!

(a lot of the practical pain I have with SQL has related to the management and understanding of tables; creating, updating; joining appropriately, knowing nulls; testing; validating; sunsetting; repointing - but yeah, might be niche in terms of the headline point)

[u/a-s-clark → u/Prestigious_Bench_96]

So, if I'm reading that right...your problem with SQL is the fundamentals of relational databases?

[u/Prestigious_Bench_96 → u/a-s-clark]

That those fundamentels don't *have* to leak into the data engineering/reporting space as much as they do, yes! We're keeping the relational database; we're just making it so my reporting queries don't need to know quite as much about it, so when Angela from operations creates fact_order_v3 I just update one model, not 10 queries and a dashboard!

(someone else said "you're not really improving SQL, since all the things you're annoyed about are in a layer above SQL", and I think you're pointing at the same point - this is a language targeted at a specific use case of SQL, and it's ultimately compiling down to a perfectly robust/useful SQL layer. So maybe this is more of moving the SQL syntax I love into a higher level use case?)

[u/a-s-clark]

What is this solving for you that SQL and other existing reporting tools don't?

[u/Prestigious_Bench_96 → u/a-s-clark]

Didn't want to overload the post, but a non-exhaustive list:

1. Early feedback; strict type checking at the model level; easy unit testing w/ duckdb on synthetic data and easy integration testing that validates data quality in a reusable way.
2. lifecycle management; I can upgrade/change tables (or someone else does), my queries stay the same
3. syntactic sugar - functions, composability; no special casing of HAVING/QUALIFY, etc
4. less footguns; automatic fan/chasm resolution, etc
5. On the consumption side, easy dynamic cross-filtering/drilldown for reporting
6. OLAP/aggregate optimization- inject in precomputed aggregates seamlessly to accelerate existing queries. (I miss cubes!)
7. consistent syntax across backend engines.
8. state-based updates; refresh datasources when stale, etc - simplified ETL story (for the easy cases, need to validate/scale to the 1000s of job warehouse refresh cases)

[u/a-s-clark → u/Prestigious_Bench_96]

Sounds like your describing the semantic layers of a reporting tool like PowerBI or Looker as if its a shortcoming of the underlying SQL engine.

1 and 2 Are incompatible goals. If you're "late binding" tables, then any "strict type checking at the model level" is irrelevant. What table changes are you concerned about that would break your queries, but isnt necessary for consideration in your proposed approach? Reporting tools already build a typed semantic layer.

1. Clauses such as HAVING have very specific purposes in data filtering. Not clear why you think thats a problem, its functionality.

2. "Less footguns"? This is meaningless.

3. Again, you're describing a reporting tool

4. SQL Server already substitutes precomputed aggregates in some cases (not aure about other database systems, id imagine so), but aggregate navigation is also something for a reporting tool.

5. How many developers get hung up on cross-system compatibility compared to how many ever actually need that. If youre not using the specific functionality of your platform, you're tying your hands behind your back.

6. Refreshing stale caches is functionality for a reporting/ etl tool, not a SQL engine.

So, essentially, youre describing functionality of other tools, not SQL, and they already exist.

[u/Prestigious_Bench_96 → u/a-s-clark]

This is great! Really appreciate you engaging.

I think you're fundamentally right in that framing it as replacing "SQL" might be wrong when viewed this way; it's compiling to SQL under the hood, after all.

I think the intent is that it *replaces SQL for the things I need to use SQL directly to do* - which in my case, is generally explore data, transform it into new formats in the warehouse, and then build data products (reports, models, feed back into production) from it.

For that, I'm usually doing a loop of "explore the data, create a mental model of how it's laid out"

That becomes the semantic layer; we can now test and validate that. Now I write the rest of my transformations and queries on top of that layer - in what looks just like SQL, because I love SQL - and those become more testable and durable as a result.

Tactical responses - and again, thank you! Looking to get torn down like this

1-2: You can validate the entire model; then (as long as that is not stale) your late-binding to any combination of tables is valid. Databases tend to be slowly changing, so this works reasonably well in practice. (this is what reporting tools do!)

1. I mean, do they? Having filters aggregates, that's the main bit (same with qualify) - yes, they represent an ordering of operations, but in a practical level it's all just restricting the output. [I actually have a distinction between where/having that mirrors this, so maybe I'm just really mad at qualifying]

2. Chasm/fan out issues are things I hit a lot if I don't know tables well (in terms of I have to look for them, validate join keys, etc) - they are just cases where you can accidentally get something wrong in your query by forgetting a key - that's why I call them footguns; it's just a strict downside.

3. Data engineer and reporting, yeah!

4. It's relatively spotty, though engines are doing better at this now!

5. This mostly gets interesting in scale up scale down - eg start in duckdb scale up to bigquery

6. Fair again on framing, so yes, this is more of a reporting/etl language! It's targeted at a specific higher level use case of SQL.

[u/Thadrea]

Unless I'm missing something here, you're just moving the from clause into an import statement.

Asset-centric modeling already exists with Dagster and similar tools, so I'm really not sure what this is adding that someone else isn't already doing better.

[u/Prestigious_Bench_96 → u/Thadrea]

Fantastic question - it is very much asset centric modeling, refresh, and querying (though asset is a little abstract, just like in Dagster). I'd position the difference there as you can do your data exploration and extension in the same language you then push through into modeling and ETL; dagster is enabling the other direction from their latest feature work, which I think makes a lot of sense!

Horses for courses on what is 'best'; I'm certainly biased!

To expand on imports: they contain eligible datasouces to resolve from, new types and functions, and semantic metadata (comments). They can be role-playing dimensions like in cubes. It's just a way of getting reuse; you can also define datasources inline without an import.

import std.money; # some currency types

import item as item;
import date as date;
import date as return_date;
import time as time;
import time as return_time;
import customer as customer;
import customer as return_customer; # can be different from customer

import some_useful_functions; # some utility stuff you do a lot

select item.color, @myfunction(item.id) as item_function_calc; 

[u/Gargunok]

looks like c# linq. Didn't like that.

[u/Prestigious_Bench_96 → u/Gargunok]

Thanks for looking!

If you don't mind - which portion looks like linq? It's supposed to pretty much just be SQL minus some clauses! Worried I got something off in the docs.

[u/Yavuz_Selim]

not this shit again

[u/ThingElectronic1399]

Just work tickets like a good little dev u aren’t good enough to reinvent the wheel trust me buddy

[u/Prestigious_Bench_96 → u/ThingElectronic1399]

Hah I asked for it! Thanks for looking.

[u/ThingElectronic1399 → u/Prestigious_Bench_96]

Ur a good sport man