6 ms·

Why Semantic Layers Matter (and how to build one with DuckDB)

152 points by secondrow 1y ago

whitten 1y ago
I think Common Logic ( https://en.m.wikipedia.org/wiki/Common_Logic https://en.m.wikipedia.org/wiki/Common_Logic - ISO/IEC 24707:2007) would be a good addition to any effort trying to add a semantic layer to any database. This is a good write up that doesn’t require DuckDB as it isn’t specific to a particular database.
cryptonector 1y ago
Is a "semantic layer" nothing more than a fancy name for a SQL VIEW in a NoSQL?
- aszen 1y ago
  No, it's more than that. Semantic Layer is about decomposing views into dimensions and aggregates, then letting downstream apps/users compose their own views on top without having to redefine/re-calculate business level metrics. This makes data analyis more flexible than sql views which are hardcoded on particular groupings.
- CharlesW 1y ago
  It's a lot more. A SQL VIEW is just a saved query, where a semantic layer defines the shared meaning of the data, and helps enforce consistent metrics, joins, and logic across tools. You'd be surprised at how many ways "active customer" can be represented as SQL.
  - porridgeraisin 1y ago
    Doesn't a view do that? create view active_cx as select * from customer join audit_events using(...) join ... where -- active condition -- use active_cx wherever select ... from orders join active_cx using(...) where ts > start_of_month() group by active_cx.id
    - cryptonector 1y ago
      It sounds like "semantic layer" == views/queries created automatically and on the fly.
- Frotag 1y ago
  Kind of annoying the article writes "What is [a semantic layer] anyway?" twice but never defines it directly.
  - articsputnik 1y ago
    OP here - I wrote extensively about, that's why I linked to existing article rather than explaining once more, and focusing on the why and how to build one. See also comment above: https://news.ycombinator.com/reply?id=44960004&goto=item%3Fid%3D44953575%2344960004 https://news.ycombinator.com/reply?id=44960004&goto=item%3Fi...
    - cryptonector 1y ago
      I looked for such a link in TFA, and it wasn't obvious.
mritchie712 1y ago
We built a transformation library[0] (think a simpler, more performant dbt) for duckdb and I'd really like to create a semantic layer as an extension for it at some point. Limiting support to only duckdb would make some really useful features trivial to implement. e.g. duckdb has a `json_serialize_sql` function that would handle a lot of the tedious parts of building a semantic layer. 0 - https://github.com/definite-app/crabwalk https://github.com/definite-app/crabwalk
- datadrivenangel 1y ago
  How do you compare your library to SQLMesh?
  - mritchie712 1y ago
    if you're looking for something like SQLMesh, then I'd stay away from crabwalk. it's purely meant to run SQL transformations in DuckDB in a reliable way with data lineage.
LargoLasskhyfv 1y ago
OT, but I really like the design of their site.
sschnei8 1y ago
I love a semantic layer as much as the next guy... Pivoting a decent sized BI shop toward using one instead of splashing the same SQL all over the place is *tough*. It's one of those: "the analyst could have been building important report for director and you want them to create re-usable logic??? we'll do that later, get report done now. Just copy/paste that SQL over here" This is how you end up with the the 1000 model, "the numbers don't match up", hot mess situations that gain momentum and are hard to slow down.
- halfcat 1y ago
  The flip side is, you often don’t know what needs to be reusable until you’ve had some iterations. Wrong abstractions can be way worse, and also gain their own momentum.
  - dietr1ch 1y ago
    The problem is that often these quick or maybe not reusable are written in such a haste that there's no breadcrumbs left to do the right thing whenever you are done getting that urgent thing out (most likely never because "everything is urgent" :( )
    - sschnei8 1y ago
      Exactly. “Everything is urgent” is the great burden of our time lol
- mritchie712 1y ago
  this is why I liked Looker. The only way to build reports was from the semantic layer which was easy to use and built into the BI. we took the same approach when we started https://www.definite.app/ https://www.definite.app/.
  - secondrow 1y ago
    This is why I liked BusinessObjects
kovezd 1y ago
Nothing to do with linear, meaningful projections on embedding spaces, and everything to do with efficient maintenance of legacy data reporting systems.
aszen 1y ago
I like the idea of a semantic Layer but don't think defining it in yaml is the right way to go about it. Semantic Layer needs proper language and tooling support which Malloy provides.
- kermatt 1y ago
  Anything but a markup language / JSON.
- 12ian34 1y ago
  are you using Malloy in prod? Do you know of anyone? I remember the Malloy hype during Data Council in 2023. Haven't heard about it since.
  - apwell23 1y ago
    i think the team moved on from google ( which was the main benefactor)
  - aszen 1y ago
    Not yet, but it's evolving nicely, the original team moved to meta and they are building interesting open source stuff
- articsputnik 1y ago
  Yeah, we still have them, MDX before and now DAX :) I curate some more on here in case of interest: https://www.ssp.sh/brain/data-modeling-languages https://www.ssp.sh/brain/data-modeling-languages.
  - efromvt 1y ago
    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 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.
btbuildem 1y ago
Impressive! An entire article about semantic layers, artfully avoids ever defining what a semantic layer is. Let me take a swipe at it: a semantic layer helps express queries and their results in terms the end-consumers will care about / prefer to reason in, instead of whatever extremely correct and efficient atrocities the database nerds came up with. Did I get that right?
- anon84873628 1y ago
  Sounds good to me! Semantic layers help expose a more user-friendly view of the data, so it is easier to ask business questions and get accurate results. More technically, it brings modularity and reusability to SQL. Things like joins, aggregate functions, and dimensional expressions are encapsulated as new fields/objects. Typically this logic is rendered at query time rather than pre-computed and materialized. The advantage of that is more flexible iteration and composability. In essence they are highly glorified SQL templating engines.
- seedless-sensat 1y ago
  Came to make the same comment. I got through like 12 paragraphs, and it still hadn't explained what a Semantic Layer is, so I gave up
- articsputnik 1y ago
  From the article: > There's a lot of information out there, including from myself about the history and rise [2022], comparing it to an MVC-like approach, or explaining its capabilities. That's why in this article I focus on the why and showcase how to use it in a practical example in the next chapter. [1] https://www.ssp.sh/blog/rise-of-semantic-layer-metrics/ https://www.ssp.sh/blog/rise-of-semantic-layer-metrics/ [2] https://cube.dev/blog/exploring-the-semantic-layer-through-the-lens-of-mvc https://cube.dev/blog/exploring-the-semantic-layer-through-t... [3] https://cube.dev/blog/universal-semantic-layer-capabilities-integrations-and-enterprise-benefits https://cube.dev/blog/universal-semantic-layer-capabilities-... My one line definition that I use atm: > A semantic layer acts as an intermediary, translating complex data into understandable user business concepts. It bridges the gap between raw data in databases (such as sales data with various attributes) and actionable insights (such as revenue per store or popular brands). This layer helps business users access and interpret data using familiar terms without needing deep technical knowledge. https://www.ssp.sh/brain/semantic-layer#semantic-layer-definition https://www.ssp.sh/brain/semantic-layer#semantic-layer-defin... Edit: I'm the OP.
Demiurge 1y ago
Yeah, I think it's great that there are ARD formats and you can access bytes via low level s3 like protocol. This enables interesting tools like DuckDB which can abstract away some stuff, and be fastish and "serverless". However, clearly there is also some kind of marketing hype train and jargon built around it, and it seems like a concerted movement to displace some other "boring" and "uncool" products and technologies. I actually think it's great to displace proprietary services with open formats and protocols. I hope it takes out "data lakes" and co, but I'd love to keep MVC and not invent completely new terms, APIs and ORMs, for things that have been working fine, for a long time.
mousematrix 1y ago
hey all, another perspective that I have been thinking about is if semantic layers are like ORM for but BI dashboards. Actually, they I think its more than BI dashboards since a similar idea applies to Features. Features in ML land are nothing but a Measure + Entity metadata + TTL. So, really its about higher-order semantics and as we move up the stack, we need richer expression to describe our world. Feature stores explored here: https://www.xorq.dev/blog/featurestore-to-featurehouse https://www.xorq.dev/blog/featurestore-to-featurehouse I think my key takeaway building this is that we need better expression systems and Ibis is a great foundation to build yours..maybe you want to build a language for some other domain etc. PS: I am one of the authors of bsl and co-founder of Xorq. I am one of the authors of bsl and founder of xorq.
- secondrow 1y ago
  I’m familiar with Xorq. One of features of the Xorq library that I find interesting is that it catalogs data processing (compute) expressions as it compiles, along with call lineage. Makes reuse easier for SQL and non-SQL processing.
  - secondrow 1y ago
    FYI - https://github.com/xorq-labs/xorq https://github.com/xorq-labs/xorq
cool_dude85 1y ago
>defined once in a single source of truth As one of the consumers of a "semantic layer" for many years now, I am firmly convinced that a "single source of truth" must either be useless or a lie. Ok, the DBA has produced some joins that I can count up to decide how many "customers" we have. We immediately have the issue that a "customer count" from the semantic layer cannot always be the meaningful or relevant figure. In my experience, outside of the exllicit context it was written it, it cannot be the correct figure. So, I have my single source of truth customer count, but my revenue per customer needs to to use a different count that's slightly off. Another analyst needs to produce customer calls to our call center and that uses a slightly different definition. And so on, until the semantic layer is just a special database for pre-defined executive KPI dashboards and no more.

152 points by secondrow 1y ago

I think Common Logic ( https://en.m.wikipedia.org/wiki/Common_Logic https://en.m.wikipedia.org/wiki/Common_Logic - ISO/IEC 24707:2007) would be a good addition to any effort trying to add a semantic layer to any database. This is a good write up that doesn’t require DuckDB as it isn’t specific to a particular database.

cryptonector 1y ago

Is a "semantic layer" nothing more than a fancy name for a SQL VIEW in a NoSQL?

aszen 1y ago
No, it's more than that. Semantic Layer is about decomposing views into dimensions and aggregates, then letting downstream apps/users compose their own views on top without having to redefine/re-calculate business level metrics. This makes data analyis more flexible than sql views which are hardcoded on particular groupings.
CharlesW 1y ago
It's a lot more. A SQL VIEW is just a saved query, where a semantic layer defines the shared meaning of the data, and helps enforce consistent metrics, joins, and logic across tools. You'd be surprised at how many ways "active customer" can be represented as SQL.
- porridgeraisin 1y ago
  Doesn't a view do that? create view active_cx as select * from customer join audit_events using(...) join ... where -- active condition -- use active_cx wherever select ... from orders join active_cx using(...) where ts > start_of_month() group by active_cx.id
  - cryptonector 1y ago
    It sounds like "semantic layer" == views/queries created automatically and on the fly.
Frotag 1y ago
Kind of annoying the article writes "What is [a semantic layer] anyway?" twice but never defines it directly.
- articsputnik 1y ago
  OP here - I wrote extensively about, that's why I linked to existing article rather than explaining once more, and focusing on the why and how to build one. See also comment above: https://news.ycombinator.com/reply?id=44960004&goto=item%3Fid%3D44953575%2344960004 https://news.ycombinator.com/reply?id=44960004&goto=item%3Fi...
  - cryptonector 1y ago
    I looked for such a link in TFA, and it wasn't obvious.

mritchie712 1y ago

We built a transformation library[0] (think a simpler, more performant dbt) for duckdb and I'd really like to create a semantic layer as an extension for it at some point. Limiting support to only duckdb would make some really useful features trivial to implement. e.g. duckdb has a `json_serialize_sql` function that would handle a lot of the tedious parts of building a semantic layer. 0 - https://github.com/definite-app/crabwalk https://github.com/definite-app/crabwalk

datadrivenangel 1y ago
How do you compare your library to SQLMesh?
- mritchie712 1y ago
  if you're looking for something like SQLMesh, then I'd stay away from crabwalk. it's purely meant to run SQL transformations in DuckDB in a reliable way with data lineage.

LargoLasskhyfv 1y ago

OT, but I really like the design of their site.

sschnei8 1y ago

I love a semantic layer as much as the next guy... Pivoting a decent sized BI shop toward using one instead of splashing the same SQL all over the place is *tough*. It's one of those: "the analyst could have been building important report for director and you want them to create re-usable logic??? we'll do that later, get report done now. Just copy/paste that SQL over here" This is how you end up with the the 1000 model, "the numbers don't match up", hot mess situations that gain momentum and are hard to slow down.

halfcat 1y ago
The flip side is, you often don’t know what needs to be reusable until you’ve had some iterations. Wrong abstractions can be way worse, and also gain their own momentum.
- dietr1ch 1y ago
  The problem is that often these quick or maybe not reusable are written in such a haste that there's no breadcrumbs left to do the right thing whenever you are done getting that urgent thing out (most likely never because "everything is urgent" :( )
  - sschnei8 1y ago
    Exactly. “Everything is urgent” is the great burden of our time lol
mritchie712 1y ago
this is why I liked Looker. The only way to build reports was from the semantic layer which was easy to use and built into the BI. we took the same approach when we started https://www.definite.app/ https://www.definite.app/.
- secondrow 1y ago
  This is why I liked BusinessObjects

kovezd 1y ago

Nothing to do with linear, meaningful projections on embedding spaces, and everything to do with efficient maintenance of legacy data reporting systems.

aszen 1y ago

I like the idea of a semantic Layer but don't think defining it in yaml is the right way to go about it. Semantic Layer needs proper language and tooling support which Malloy provides.

kermatt 1y ago
Anything but a markup language / JSON.
12ian34 1y ago
are you using Malloy in prod? Do you know of anyone? I remember the Malloy hype during Data Council in 2023. Haven't heard about it since.
- apwell23 1y ago
  i think the team moved on from google ( which was the main benefactor)
- aszen 1y ago
  Not yet, but it's evolving nicely, the original team moved to meta and they are building interesting open source stuff
articsputnik 1y ago
Yeah, we still have them, MDX before and now DAX :) I curate some more on here in case of interest: https://www.ssp.sh/brain/data-modeling-languages https://www.ssp.sh/brain/data-modeling-languages.
- efromvt 1y ago
  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 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.

btbuildem 1y ago

Impressive! An entire article about semantic layers, artfully avoids ever defining what a semantic layer is. Let me take a swipe at it: a semantic layer helps express queries and their results in terms the end-consumers will care about / prefer to reason in, instead of whatever extremely correct and efficient atrocities the database nerds came up with. Did I get that right?

anon84873628 1y ago
Sounds good to me! Semantic layers help expose a more user-friendly view of the data, so it is easier to ask business questions and get accurate results. More technically, it brings modularity and reusability to SQL. Things like joins, aggregate functions, and dimensional expressions are encapsulated as new fields/objects. Typically this logic is rendered at query time rather than pre-computed and materialized. The advantage of that is more flexible iteration and composability. In essence they are highly glorified SQL templating engines.
seedless-sensat 1y ago
Came to make the same comment. I got through like 12 paragraphs, and it still hadn't explained what a Semantic Layer is, so I gave up
articsputnik 1y ago
From the article: > There's a lot of information out there, including from myself about the history and rise [2022], comparing it to an MVC-like approach, or explaining its capabilities. That's why in this article I focus on the why and showcase how to use it in a practical example in the next chapter. [1] https://www.ssp.sh/blog/rise-of-semantic-layer-metrics/ https://www.ssp.sh/blog/rise-of-semantic-layer-metrics/ [2] https://cube.dev/blog/exploring-the-semantic-layer-through-the-lens-of-mvc https://cube.dev/blog/exploring-the-semantic-layer-through-t... [3] https://cube.dev/blog/universal-semantic-layer-capabilities-integrations-and-enterprise-benefits https://cube.dev/blog/universal-semantic-layer-capabilities-... My one line definition that I use atm: > A semantic layer acts as an intermediary, translating complex data into understandable user business concepts. It bridges the gap between raw data in databases (such as sales data with various attributes) and actionable insights (such as revenue per store or popular brands). This layer helps business users access and interpret data using familiar terms without needing deep technical knowledge. https://www.ssp.sh/brain/semantic-layer#semantic-layer-definition https://www.ssp.sh/brain/semantic-layer#semantic-layer-defin... Edit: I'm the OP.

Demiurge 1y ago

Yeah, I think it's great that there are ARD formats and you can access bytes via low level s3 like protocol. This enables interesting tools like DuckDB which can abstract away some stuff, and be fastish and "serverless". However, clearly there is also some kind of marketing hype train and jargon built around it, and it seems like a concerted movement to displace some other "boring" and "uncool" products and technologies. I actually think it's great to displace proprietary services with open formats and protocols. I hope it takes out "data lakes" and co, but I'd love to keep MVC and not invent completely new terms, APIs and ORMs, for things that have been working fine, for a long time.

mousematrix 1y ago

hey all, another perspective that I have been thinking about is if semantic layers are like ORM for but BI dashboards. Actually, they I think its more than BI dashboards since a similar idea applies to Features. Features in ML land are nothing but a Measure + Entity metadata + TTL. So, really its about higher-order semantics and as we move up the stack, we need richer expression to describe our world. Feature stores explored here: https://www.xorq.dev/blog/featurestore-to-featurehouse https://www.xorq.dev/blog/featurestore-to-featurehouse I think my key takeaway building this is that we need better expression systems and Ibis is a great foundation to build yours..maybe you want to build a language for some other domain etc. PS: I am one of the authors of bsl and co-founder of Xorq. I am one of the authors of bsl and founder of xorq.

secondrow 1y ago
I’m familiar with Xorq. One of features of the Xorq library that I find interesting is that it catalogs data processing (compute) expressions as it compiles, along with call lineage. Makes reuse easier for SQL and non-SQL processing.
- secondrow 1y ago
  FYI - https://github.com/xorq-labs/xorq https://github.com/xorq-labs/xorq

cool_dude85 1y ago

>defined once in a single source of truth As one of the consumers of a "semantic layer" for many years now, I am firmly convinced that a "single source of truth" must either be useless or a lie. Ok, the DBA has produced some joins that I can count up to decide how many "customers" we have. We immediately have the issue that a "customer count" from the semantic layer cannot always be the meaningful or relevant figure. In my experience, outside of the exllicit context it was written it, it cannot be the correct figure. So, I have my single source of truth customer count, but my revenue per customer needs to to use a different count that's slightly off. Another analyst needs to produce customer calls to our call center and that uses a slightly different definition. And so on, until the semantic layer is just a special database for pre-defined executive KPI dashboards and no more.