An Entity in Honeydew is defined as “a collection of stuff that shares the same granularity”.
For example in TPC-H, we can make an entity called
orders - all the data we have per orders.
orders entity is based on a TABLE in Snowflake, that has a unique key column in it (
orderkey), and one row per order. All properties of a single order are in the columns.
When modeling a SQL data warehouse, entities like customers may be stored in tables called “Dimensions”, and events on them like payments in tables called “Facts”. If you start from a well-architected data warehouse then import each existing table in it (dimension or fact) as an entity in Honeydew. But don’t worry if you don’t! We are here to make modeling business entities easy.
The most important thing in an Entity is its ”Granularity Key”. This is what defines what is a unique instance of the Entity - for example in
orders entity, the key is
orderkey defining a unique order.
Granularity Key can be a combination of a few attributes that are unique together. This is called composite or compound key. For example, in TPC-H
lineitems entity, the key is a combination of
An entity can be key-less, but there is significant limitations to key-less entities, the most important is that it only be the higher granularity of every entity related to it.
Honeydew engine assumes entity keys are unique and non-null. Unexpected results may be otherwise.
It is good practice to test the key column(s) for uniqueness (automatic testing within Honeydew coming soon)
Entities may include metadata such as their owner, business description, labels. See metadata section for more details.
Every entity is backed by a text file in git that defines it, and keeps history of every change. See schema for more details on the underlying representation.
Entities don’t have to be based on a physical table in the data warehouse. Following entity types are possible:
- A physical table or a view
- A custom SQL query that defines the data for the entity
- A virtual entity, that is based on a calculation
The definition of the source table (regardless of its type) sets columns that become attributes of the entity.
When defining entities in the UI, the entity granularity key must come from its source table. If the entity key is based on a calculated attribute, then create a key-less entity first, make the calculated attribute, and then use it as its key.
Source tables based on a data warehouse path (
database.schema.table) can be of any kind supported by Snowflake (TABLE, VIEW, DYNAMIC TABLE, MATERIALIZED VIEW, etc.).
Source tables based on a custom SQL query, will run that query for any access to the entity data.
Custom SQL may result in lower performance:
- Filters might not be pushed down into it, resulting in larger table scans
- Column selection might not be pushed down into it, resulting in unnecessary data movement.
If the source data is a large dataset that is commonly filtered for performance reasons, consider to encapsulate it in a table. In the source data has many columns, consider to encapsulate it in a view.
In particular, always prefer pointing to a table over using
SELECT * FROM table custom SQL source table.
Following configuration is supported:
- Choice of columns from the source table to add to the entity as attributes
- Names of columns: may rename columns before making them into attributes
Use renaming to remove unnecessary prefixes from columns names
An Entity is defined by its granularity key and source table.
However, sometimes that granularity comes from attributes that exist in the semantic model.
There are few reasons to make a virtual entity:
- Nested or Denormalized data tables that include few levels of granularity together.
- Build metrics on a level of granularity that is not an entity key.
- Build 1:many relationships to a level of granularity that is not an entity key.
For a virtual entity, must define:
- Source entity (it can be virtual as well)
- Granularity key that comes from the source entity
- Attributes that come from the source entity that are at the virtual entity granularity
For example an event table might include a
user_id column and a
user_name, that is per
user_id and is duplicated in the event table.
If you have an events entity (on the event table), can build from it a users entity with
user_id as key, and
user_name as an attribute.
Virtual entities have a single granularity key - multi-attribute granularity is not supported.
However, it is easy to make one:
- Build in the source entity a calculated attribute
virtual_keythat is a concatenation of the multiple attributes
- Build a virtual entity that has
virtual_keyas a key, and all the multiple attributes as attributes from the source
Example - virtual entity to change metric granularity
For example, in TPC-H, we may want to look at
orders.orderdate as a Granularity Key of a ""Date” entity so we can do daily metrics:
- Make a virtual entity based on
orderdateas its key
- Make a calculated attribute in it that calculates daily revenue (defined as
- Make a metric in it that calculated average daily revenue (defined as
- Use that metric to look at average daily revenue by