pytrilogy

Continued on my urban tree mapping kick from the last few months - it's continued to be a good way to stress test other projects of mine [3]. I've given it a new shiny landing page [1] so I can work on SEO a bit, I'll need to do a facelift on the data explorer to bring it in line stylistically. I'm pivoting from adding more sources to data quality - as I've tried to vet some of the city sources I'm finding fun things (Cambridge, MA, USA merges in trees from Harvard, and some are straight duplicates with slight offsets, lots of places have trees in the middle of the road, many locations are address level instead of tree level). Now trying to leverage aerial photography to drive some QA, which has been a fun area to explore! [1]: https://arborary.world/ [2]: https://greenmtnboy.github.io/tree_reporting [3]: https://github.com/trilogy-data/

I finished wiring up the backend data merge so user(s) (aka me, really) can record trees as they walk and have those merged into the general city data on my urban tree map app[1]. There's some very cool trees in the neighborhood I'm excited to have recorded. Which leads me to trying to get the processing off github actions (it's all duckdb pipelines), which has led me to improve the orchestration implementation of my 'better sql' [2] to optimize the asset based refresh flow. I'm now wiring that up in a general cloud service to give me the data orchestration platform I've always wanted. Building a multi-tenant cloud experience has been interesting! [1]: https://greenmtnboy.github.io/sf_tree_reporting [2]: https://github.com/trilogy-data/pytrilogy

SQL-first analytic IDE; similar to Redash/Metabase. Aims to solve reuse/composability at the code layer with modified syntax, Trilogy, that includes a semantic layer directly in the SQL-like language. Status: experiment; feedback and contributions welcome! Built to solve 3 problems I have with SQL as my primary iterative analysis language: 1. Adjusting queries/analysis takes a lot of boilerplate. Solve with queries that operate on the semantic layer, not tables. Also eliminates the need for CTEs. 2. Sources of truth change all the time. I hate updating reports to reference new tables. Also solved by the semantic layer, since data bindings can be updated without changing dashboards or queries. 3. Getting from SQL to visuals is too much work in many tools; make it as streamlined as possible. Surprise - solve with the semantic layer; add in

MDX really did it well, at the cost of being impenetrable to the average user - the drilldown flow you could get is still hard to beat. Shameless plug for the list, though - I work on https://github.com/trilogy-data/pytrilogy - semantic layer directly embedded in otherwise (mostly) SQL syntax. I'll do an equivalent example on the taxi dataset when I have some time.

I'm unconvinced the syntax makes a genuine difference. But always interesting to see what folks come up with! Some notable others: PreQL/Trilogy - https://github.com/trilogy-data/pytrilogy https://news.ycombinator.com/item?id=40728938 Malloy -https://www.malloydata.dev/ https://github.com/malloydata/malloy https://news.ycombinator.com/item?id=30053860 PRQL - https://prql-lang.org/ https://github.com/PRQL/prql https://news.ycombinator.com/item?id=36866861

Still iterating on Trilogy, a SQL variant with an embedded semantic layer that removes the need for joins and better typing/functions. Spent some time last month improving array handling + error messages and UX and adding an MCP server option; Claude does pretty well already but there's some syntax/error tweaks to make it simpler for it and humans. Then pivoting back into scheduling + materialization optimizations (identify common aggregates across several scripts and automatically build the common datasets for reuse). Link: https://github.com/trilogy-data/pytrilogy

Continuing to plug away at Trilogy, a better SQL for data consumption and analytics. Getting closer to core feature completeness, at which point can pivot to focus on integrated visualizations + pre-processing/ETL. Most recently have been focused on better geographic visualizations in the public studio for people to experiment - getting decent automatic lat/long, want to have easy path visualizations (start/end, etc). More AI-accelerated options as well, especially around model authoring. Repo: https://github.com/trilogy-data/pytrilogy Studio: https://trilogydata.dev/studio-core/