[Feature] dbt should know about Attributes (foundation for Metrics/Dimensions)

[aaronsteers]

Describe the Feature

Following from #4071, I called out in my comment that I believe metrics would be better established on top of "attributes" instead of building directly on "columns" and "models" abstractions in dbt.

As a foundation for deeper metadata understandings within dbt and to unify the documentation effort for existing dbt projects, we should first establish some type of ontological definition of what columns "mean", as they relate to an analytical framing.

Proposal

1. We add top-level configurability for "attributes" within dbt core.
   1. An attribute maps to one or more columns in one or more tables (models), and it defines the nature of the column in an ontological manner.
   2. An attribute may have an "attribute type", such as dimension_key, dimension_property, fact, etc.
   3. An attribute definition can define how a table interacts with the time dimension, which is needed for certain metric-related applications. (For instance, if a table has an attribute as_of_date marked as the primary temporal attribute for the table, this will inform how metrics calculations can be performed - and at what grain they are possible.)
   4. An attribute may have a text description, which will appear in the docs on any tables (models) where it is referenced.
   5. Attributes may take part in an attribute hierarchy (day->month->year, city->state->country, etc.)
   6. Users may use dbt docs (or other tools) to find references to tables. (I want to see all the tables that contain the "sales_revenue" attribute.)
2. We provide easy ways to auto-map attributes to columns. For instance, for an attribute called sales_revenue, we could set an auto_map_by_name: true property to find and map all references of the column sales_revenue, or we could explicitly map to models and column references.

Benefits

1. Over time, best practices will emerge related to column namings to reduce the work needed for attribute mapping, which in turn improves the readability of the project overall. (For instance, this discourages "sales_region" on one table to have an integer ID and "sales_region" on another table to contain the string with the name of the region.)
2. We can build metrics ([Feature] dbt should know about metrics #4071) more easily on top of attributes. For example, "customers per region" can be more easily defined once "customer_id", "number of customers", "region_id", and "number of regions" are already known and clearly defined attributes.

Sample Code

Adapted from my comment on the related metrics topic.

Note
Update (2022-12-05): Updated with entities, per:

• [CT-1599] [Feature] dbt should know more semantic information #6379

entities:
- name: customer
  attributes:
  - name: customer_id
    label: Customer ID
    attribute_type: dimension_key
    column_names:
    - customer_id
    models:
    # as much or as little constraint to get ontologically the right column mappings
    - marts.*
    - !marts.the_weird_one
  - name: customer_count
    label: Customer Count
    description: A pre-aggregated count of customers.
    attribute_type: entity_count
    column_names:
    - customer_id
    models:
    # everywhere we see this column in the `marts` folder, map it to this attribute
    - marts.*
  - name: country
    label: Country Name
    attribute_type: dimension_key
    models:
    # everywhere we see this column in the `marts` folder, map it to this attribute
    - marts.*
    column_names:
    - country
    - country_name
- name: orders
  attributes:
  - name: sales_revenue
    label: Sales Revenue
    attribute_type: amount
    column_names:
    - sales_revenue
    models:
    - marts.*

Describe alternatives you've considered

Metrics: The alternatives for metrics is to create mappings directly over all tables and columns. The greater the number of tables of different aggregation levels, and/or projections for query optimization, the larger the redundancy of those metrics mappings will be.

Documentation: This is actually stemming from another inquiry I ran into a couple years ago: how to document all columns in all models, without having to put the same text description on every single instance. (And then, how to keep them up to date as you want to update how "sales revenue" is calculated on all of them.) As far as I'm aware, there isn't yet a good solution for this documentation problem, and so my general guidance has been "don't worry about descriptions on columns" - because it's just too much work and no single-source-of-truth to keep them in sync across tables.

Adding attributes would hopefully change that, since the many places the column exists, it will always carry (or link to) the same text description for users of the project.

Who will this benefit?

This would benefit teams who want column-level descriptions for themselves and their users.

This benefits users, because they can better documentation on columns, and better understand equivalency (or lack thereof) of similarly named column across a project.

Are you interested in contributing this feature?

Sure!

Anything else?

Prior art

Inspired by OLAP platforms and BI layers which support ROLAP capabilities:

• MicroStrategy (called "facts" there)
• Mondrian ("attributes" / "dimensions")
• SSAS ("attributes" / "properties" / "dimensions")

[tnightengale]

At my current company I have a script that generates yaml for a table which has been created via dbt run and adds in any existing descriptions that match the column name from the latest local manifest.json. The ethos is: if a column is called the same thing between models then it means the same thing.

I feel like if we want to solve consistent documentation, we should perhaps consider a new builtin CLI command? Something that could generate docs with existing descriptions, as well as point out inconsistent definitions, and perhaps edit the yml directly with any updates.

I'm leaning that way because I think attributes as they are proposed above are sort of trying to be too many things: are we solving metric definitions or consistent docs? Let's be explicit and narrow in on the problem we want to solve 😄

[david-kubecka]

I would like to offer another PoV from a GoodData company (I'm an employee there) that has implemented its own ROLAP engine (historically inspired by MicroStrategy). Same as the issue author, we (and our customers) see great value of building metrics on top of an abstraction over the physical DB (instead of directly over DB columns). We would like to offer our longterm experience in this area to help shape the universal semantic layer design. Let me briefly outline our approach.

What you outline here is basically what we in GoodData call a Logical Data Model (LDM). The LDM consists of datasets (similar to the outputs of dbt models, or simply DB tables/views) which in turn consist of attributes (dimension_key in your proposal) and facts (an umbrella term of numeric aggregatable values - like amount or entity_count). So what you call attributes here we would proably label as LDM objects (if there ever is a need to create an umbreall term for that).

Once an LDM is set up the end users can then define their metrics using facts and attributes via a custom language called MAQL. A typical simple metric could look like

SUM(Price) WHERE Product = "Shoe"

Such a metric could then be used in many different reports, in each of them broken down by any attributes "compatible" with the Price fact, e.g. Product, Customer, Campaign. (For more info about LDM you can read the intro docs).

I hope that our approach could be of some inspiration in your proposed design which I generally like quite a lot, especially the overall direction of your thinking. I may be biased, though :-) Couple of questions:

• I know that's this is just a very early design but do you have an idea about possible values of attribute_type? Also, in your example you have dimension_key and dimension_property. What use case do you see behind distinguishing between those with respect to metric layer?
• You propose the ability to define a hierarchy of attributes. How would that work with respect to models? Must the hierarchy be always within single model or could it cross the model boundaries?
• Regarding hierarchies again: What use case do you see in them? In GoodData we indeed utilize this concept quite heavily e.g. for reusable ratio metrics which can be broken down exactly in the hiearchy corresponding to the denominator attribute. I've not seen this much elsewhere, though, so I'm interested in your ideas.

Anyway, thanks for a great kick off!

[jtcohen6]

Big thank you @aaronsteers for the clear and thorough write-up, and @tnightengale @david-kubecka for the thoughtful replies!

I'm way overdue for responding to this one, in part because it sent me down a flurry of mental paths, and it's taken me a while to gather my thoughts together into anything serviceable. I believe the move into defining metrics in dbt-core necessitates a move from defining how models are created to defining how models ought to be queried. This integrates a lot of different thoughts that are still half-baked—so read the following as a mix of inspiration and guiding principle, without expectation of any concrete vision or immediate action.

Semantically meaningful column properties ought to be inherited, and not re-typed every single time. That inheritance can run in two ways:

• Via configuration, with consistent project-level baselines, plus clear override where appropriate (!marts.the_weird_one in your example). This would work similarly to how functional model configuration takes place in dbt today: very hierarchical, very top-down, very YAML. To Teghan's point, there's a degree of enforced consistency that we want here. When two columns have the same name, they should represent the same thing (data type, concept, ...). I would be happy about dbt-core or companion tooling enforcing that expectation, in the same way we can enforce expectations around test + documentation coverage. Making it possible to configure these things, DRY-ly, in all the places users expect, makes it more reasonable, in turn, to expect them to be applied everywhere.
• Via lineage: Today, models implicitly inherit all column values/types/etc from upstream models—but that lineage is not known to dbt, or stated anywhere explicitly. This is a big ask from folks who find themselves typing the same descriptions, tests, and meta properties over and over again (Doc (and potentially, Test) Inheritance #2995). I don't believe we're going to be able to do the good version of this without first building capabilities for inferring column-level lineage (Column level lineage #4458), and extract as much useful information as possible out of the SQL that folks are already writing.

The separation/combination I described above is something I want to pursue, and it's something we can do with existing constructs—models and columns—plus a few new capabilities. So, do we need another construct here? What does a notion of an attribute get us?

I've become increasingly convinced that there's real value in defining column "types." The closest thing to column "types" in dbt today is a mix of descriptions (achieved with reusable docs blocks) and generic tests (bear with me). Those tests imply a certain set of commonly understood properties—uniqueness, never null, only certain values, never negative, never overlapping—and those implied properties are, to human eyes, semantically meaningful attributes.

So, imagine a revenue "type", which defines:

• database column type (int)
• tags, meta properties
• standard set of metrics, appropriate to this column type (e.g. total_revenue), including behavior around timestamps
• set of tests/expectations — never null, never more than a too-big number
• standard set of grants, policy tags, column masking behavior, ...?
• standard description — could it be templated and take arguments?

Then we work toward a synthesized approach whereby:

• The sales_revenue column is an instance of the revenue type. In the first model where sales_revenue is calculated, we can override or customize each of those type-inherited properties as appropriate / specific to sales
• Every model with sales_revenue in it derives its properties from upstream in the DAG (left-right), via lineage; and/or the consistency of those properties is enforced across the DAG (up-down), via metadata
• Via metadata, we can surface a centralized glossary (Add the ability to define a terms section or glossary #2860) that combines types, all the columns of that type, and all the models in which each column appears
• (To put the pie all the way into the sky) dbt could run "static type checks," before runtime, based on metadata/summary queries against source tables and analyzing model SQL

Lots of questions:

• Should we try to make some types available out of the box, to aid in standardizing? Or trust that the standardization can happen via popular packages?
• Should "types" be able to inherit from each other? What if we want both revenue and sales_revenue to be types, with the latter as a child type of the former?
• Should "types" be domain-specific only? Or should they also include functionally meaningful designations, that are less domain-specific—think primary_key + foreign_key—that would give dbt enhanced capabilities around materialization + downstream querying? This is what you propose in the (optional) dimension_key
• Is this meaningfully different from the original proposal? Or am I proposing a half-baked middle way, a compromise between strong centralization of all important column definitions and the free-for-all that we see today, a center which does not hold?

Ok! This is just the beginning. I'm going to turn this into a discussion, in the hope that more folks join in :)

[pbower]

Hi, I love this reply.
Also - a big fan of the metrics types and metadata tagging. Could this promote evolution into data interoperability, and self-generating API’s from dbt should organisation’s wish to benchmark or data share for allowable use cases? As, would the metadata, conditions etc imply compatibility? (Eg. NPS for the most basic example). Could this then help promote the nature of data quality expectations and conditions (existing dbt concepts) , and support more organic development in areas like Health without relying on government or industry bodies to mandate code /schema standards (which never works - especially when companies then buy off the shelf crappy systems that are ‘compliant’)? Is this then another pathway that promotes growth and extension in the dbt product over time?

[jankatins]

An argument to at least allow defining derived/computed metrics based on base metrics:

I come from mara-schema which itself is based on the XML schema used to configure metrics in the mondrian OLAP engine. A best practice was to base metrics on plain columns and derived metrics from these base metrics. So a rate was METRIC a / METRIC B instead of count(col_a) / count(col_b). I found that very intuitive because it means

a) you do not need to copy the column definition from the base metric to the derived metric
b) the base metric has to be defined as well and so is available for doing analytics on as well
c) In the metrics catalog, you can link to the definition of the base metric instead of copying everything to the derived metric

metabase also uses such a model (base metrics + derived metrics based on it), so syncing such a model to metabase worked very well.

[aaronsteers]

So a rate was METRIC a / METRIC B instead of count(col_a) / count(col_b). I found that very intuitive because it means

a) you do not need to copy the column definition from the base metric to the derived metric
b) the base metric has to be defined as well and so is available for doing analytics on as well
c) In the metrics catalog, you can link to the definition of the base metric instead of copying everything to the derived metric

Very well said! Yes, this exactly. ☝️

Your example of a "rate" metric is perfect also, since rates are non additive in nature, and would require an expressive representation besides some single column name. Defining as METRIC a / METRIC b means that the expression is portable to any additional tables that contain those base metrics - but also to any layer of GROUP BY aggregation which can be calculated for the original table.

[aaronsteers]

I had not heard of mara-schema, and found this in their docs to be pretty interesting+relevant:

https://github.com/mara/mara-schema#user-content-defining-entities-attributes-metrics--data-sets

Attributes represent facts about an entity. They correspond to the non-numerical columns in a fact or dimension table:

order_item_entity.add_attribute(
    name='Order item ID',
    description='The ID of the order item in the backend',
    column_name='order_item_id',
    type=Type.ID,
    high_cardinality=True)

They come with a speaking name (as shown in reporting front-ends), a description and a column_name in the underlying database table.

[callum-mcdata]

@aaronsteers @jankatins do you feel that this design pattern is reflected in the implementation of expression metrics ? I know this is tangentially related to the addition of attributes into dbt-core but want to check whether a degree of this functionality has been addressed.

[abdel-87]

Hi,

How about the Cube JS approach? See a snippet below, where you define measures in a very intuitive/SQL like way.
It is also a well adapted open source project with 12k+ stars. https://cube.dev/docs/schema/getting-started

cube(Users, {
measures: {
count: {
sql: id,
type: count,
},

payingCount: {
  sql: `id`,
  type: `count`,
  filters: [{ sql: `${CUBE}.paying = 'true'` }],
},

payingPercentage: {
  sql: `100.0 * ${payingCount} / ${count}`,
  type: `number`,
  format: `percent`,
},

},
});

By using and stickkng to the SQL like approach, it will be very intuitive and easy to adapt by dbt community, and well readable, such as the mara-schema example. Attributes really sound like something very abstract.

Also, you will be able to use a Metric serving layer such as CubeJS to read the metric definitions from dbt and serve all your BI/Reporting tools from it

[pbower]

I agree with above in that it feels like attributes seems similar to columns, and we would then also be spending extra time duplicating things (which this is trying to avoid). However, could it work as an optional YAML file in the project config /similar with ‘attribute: [colalias1,colalias2,colalias3]’ style to have as a way to shortcut and propagate the documentation and meta tags?
So, handy to have, without being cumbersome or adding unnecessary complexity for the casual user?