Source Data

Entity Source Data

The source data definition sets where the data for an entity comes from. Source data is associated with a single entity. The source data definition specifies:

The origin of the data (e.g., a physical object or an SQL query).
The columns to be included as attributes of an entity, along with their designated names.
The metadata associated with those attributes, derived from the source.

Columns

Columns from the source data are added as attributes in the entity.

Attribute Mapping and Renaming

As part of the source data definition, you can optionally specify a name for the created attribute. If no name is provided, the column name is used as the attribute’s default name.

A common pattern for light data cleaning transformations involves adding a column as a hidden attribute and then creating a calculated attribute with the same name that applies the transformation.For example, if the city_name column in the source data contains surrounding whitespace, you can normalize the column by adding it as a hidden attribute and creating a calculated attribute to remove the whitespace:

Add the city_name column as _city_name_raw attribute, set as hidden.
Create a calculated attribute called city_name with the expression TRIM(entity._city_name_raw)

Structure and Type Synchronization

When source data is created, its column structure and data types are initially imported from the source. If the structure or data types change over time, you can re-synchronize them by using the “synchronize from source” option available in the UI or API.

Metadata Synchronization

Metadata can be synchronized from the source data to the entity and its attributes:

Table and columns comments are imported as descriptions for the entity and its attributes
Table and columns tags are imported as labels for the entity and its attributes

In addition, primary keys and foreign keys metadata is leveraged to suggest entity keys and relationships.

Data Mapping

Source data can be a database object (such as a table, view, or custom SQL query).

Database Objects

Source objects can include any Snowflake object containing data, such as TABLE, VIEW, DYNAMIC TABLE, INTERACTIVE TABLE, MATERIALIZED VIEW, and more. All types of tables are supported, including regular tables, Iceberg tables, and external tables. Note that the object path can be relative to the environment.

If its source path is relative (only table name), it will refer to the object in the database and schema of the current environment.
If its source path is absolute (db.schema.table) it always refers to that absolute path.

Custom SQL

Source data can be based on any SQL query. Note that the SQL query runs as-is on the data warehouse. The SQL query runs within the current environment.

If a FROM statement of the SQL query is relative (only table name), it will refer to the table on the database and schema of the current environment.
If a FROM statement of the SQL query is absolute (db.schema.table) it always refers to that absolute path.

The SQL query may use parameters to adjust its execution. Parameters can be set within a domain to create different data sources.

Custom SQL may result in lower performance due to the following reasons:

Filters may not be pushed down into the query, leading to larger table scans.
Column selection may not be pushed down, causing unnecessary data movement.

If the source data is a large dataset that is frequently filtered for performance reasons, it’s recommended to encapsulate it in a table. If the source data contains many columns, consider encapsulating it in a view.In particular, it’s always better to reference a table directly rather than using a SELECT * FROM table custom SQL source.

Semi-Structured and JSON Data

Source data, whether it’s a query, table, or view, can include semi-structured columns (such as JSON, arrays, or maps). A Honeydew attribute can be of a semi-structured type and used directly as a structured object. However, most BI tools cannot process semi-structured data and require simple, structured columns. There are two approaches to normalize semi-structured data for BI:

Use the source as-is with semi-structured attributes and create calculated attributes to unpack specific fields..
Unpack all fields with a custom SQL query source.

For a JSON column with different fields, a structured custom SQL query can look like this:

-- the table JSON_TABLE has a column named json_data with different nested values
SELECT
    -- unpack a field by key
    json_data:"id"::string AS id,
    -- unpack a sub-field in a properties by key
    json_data:"address":"city"::string AS address_city,
    -- unpack a sub-field in an array by key
    json_data:"visits"[0]:"time"::timestamp AS first_visit_time,
FROM DATABASE.SCHEMA.JSON_TABLE;

Note that when unpacking semi-structured data, it is recommended to explicitly cast the columns to the appropriate types.

In most cases, it is recommended to avoid using LATERAL FLATTEN join to flatten a JSON object.When using LATERAL FLATTEN, each key in the JSON object becomes a separate row with a key and a value, which can alter the granularity of the entity. If this behavior is desired, use the SEQ column, which provides a unique sequence number, as part of the entity key to maintain consistency and avoid issues with key duplication.

YAML Schema

Each entity is based on a source table defined in a separate text file stored in Git. The schema for source table is:

type: dataset
entity: <entity name>
name: <source table name>
display_name: <display name>
sql: |
  <sql statement>
owner: <owner>
description: |-
  <description>
labels: [label1, label2, ...]
tags:
  - key: <key>
    value: <value>
    source: <snowflake/databricks/bigquery/honeydew/other>
  - ...
folder: <folder>
hidden: <True/False/Yes/No>
dataset_type: <table/sql/entity>
attributes:
  - column: <src column name>
    name: <attribute name>
    display_name: <display name>
    datatype: <bool / date / float / number / string / time / timestamp>
    timegrain: <the time grain for time attributes - hour/day/month/...>
    description: <description>
    labels: [label1, label2, ...]
    tags:
      - key: <key>
        value: <value>
        source: <snowflake/databricks/bigquery/honeydew/other>
      - ...
    folder: <folder>
    hidden: <True/False/Yes/No>
    metadata:
      - name: <metadata section name>
        metadata:
          - name: <metadata field name>
            value: <metadata field value>
          - ...
      - ...
  - ...

Fields:

name: Name of source table
display_name, owner, description, labels, tags, folder, hidden: Metadata
dataset_type: Choose if a table, a SQL query or a virtual entity
sql: The name of table (when type is a table) / the base entity (virtual table) / SQL query (custom sql)
attributes: List of attributes to bring from the source, with optional renaming. Each attribute supports a metadata field for tool-specific metadata (see examples for AI, Tableau, Power BI, Lightdash and ThoughtSpot)

Quick Start

Semantic Ecosystem

Modeling Reference

Context Layer

Management

Integrations

Security

Release Notes

Entity Source Data

Columns

Attribute Mapping and Renaming

Structure and Type Synchronization

Metadata Synchronization

Data Mapping

Database Objects

Custom SQL

Semi-Structured and JSON Data

YAML Schema

​Entity Source Data

​Columns

​Attribute Mapping and Renaming

​Structure and Type Synchronization

​Metadata Synchronization

​Data Mapping

​Database Objects

​Custom SQL

​Semi-Structured and JSON Data

​YAML Schema

Entity Source Data

Columns

Attribute Mapping and Renaming

Structure and Type Synchronization

Metadata Synchronization

Data Mapping

Database Objects

Custom SQL

Semi-Structured and JSON Data

YAML Schema