I’m still figuring out how to convey the value of an expressive semantic layer, how differentiated Strata and 0Sql is, and why it’s important to focus the value of a semantic layer around the non technical user. Then I woke up this morning it suddenly hit me. I can convert from Strata to MetricFlow and Cube and vice versa to get fine grained differences.
For this experiment we are using the TPCDS example which is live at our demo site. It’s a fairly complex model that has several fact domains represented: Catalog Sales, Store Sales, Web Sales, Catalog Returns, Call Center, Inventory and more. It also implements our semantic routing setup by allocating a portion data to duckdb while the historical stays on Athena.
You can access the model here. This is the 0Sql version which is the same query engine behind Strata. However, it’s much easier to test and play around with little work.
Clone and follow along.
Here is my one shotted prompt from scratch (I came up with this after a few trial and errors):
Please convert my ~/Projects/tpcds-athena-demo project from 0sql.io semantic model
to a MetricFlow semantic model. Do not create any new tables or alter the current
physical design in anyway. Work hard to find workarounds when a direct feature is not
available. Avoid running live queries against athena. You can compare results
by looking at sql query results. Once done create a comprehensive summary of difference.
Be straight to the point and not overly wordy.Why did I use 0sql vs Strata here? Its must easier to play around with when you only care about sql generation. It’s more apples to apples since we are comparing pure semantic layer vs all the other features that Strata brings.
The first summary stated that MetricFlow supported ratios but Strata/0Sql didn’t! Absolute baloney slop from Fable. I had it reread the docs and run a complex query:
me > wait zsql supports rations,cumaliztives, and temporal transformations..
Derived as well (called calculations). feel free to review docs
claude > You're right to push back. Let me check the 0sql docs and the CLI before
restating that section.Right to pushback, hahahahah. Right I am.
Okay now we got something but I wanted it to run an actual complex query before committing to a final verdict:
me > can you get each tool to answer this question: "Month, Item Product Name,
Inventory on Hand, date > 6m ".. Use the spec form on 0sql and the accurate connonical
form on metricflow. Then show me the sql queries.See the two different semantic layer SQL generation compared in beautiful detail here.
On aesthetics of written character alone, 0Sql/Strata wins by a landslide. Is beauty truth?
me > now run a faire evaluation on which is better semantic layer be fair!
claude >
Verdict first: on what each engine can do with a model, 0sql is the stronger semantic
layer. On ecosystem, maturity and what sits around the model, MetricFlow is.
Which one is "better" depends on whether you are buying a planner or buying into dbt.Read the complete verdict here.
I think this is overall a fair verdict. However, I didn’t ask it this most important question: which of these two semantic layers would benefit non technical business users and agents the most? I leave that up to you, dear reader, to decide.