​
Intro

A Dynamic Dataset is a way to look at data using Honeydew. A typical Honeydew deployment has many thousands of pieces of logic. An analysis might need just a subset, for example, the orders of a particular customer. The simplest dataset is “all the data of an entity”. Dynamic datasets are built:
  1. Attributes
  2. Metrics
  3. Filters
The components can come from fields defined in the semantic layer (attributes and metrics) or defined at ad-hoc field SQL expressions. Unlike attributes or metrics defined in Entities, ad-hoc calculations defined in a dataset is not reusable by other calculations.

​
Use Cases

  1. Build static datasets for data science (i.e. to load into a Python data frame)
  2. Share data with external tools that accept a table
  3. Deliver semantic models into dbt
  4. Setup aggregate aware caches for query acceleration

​
Metadata

Dynamic datasets may include metadata such as their owner, business description, labels. See metadata section for more details.

​
Example

Build a new dynamic dataset, and select from lineitem two fields: l_linestatus (an attribute) and sum_qty (a metric). The sums up item quantity per their status delivery status. The schema for it is - 
attributes:
- lineitem.l_linestatus
metrics:
- lineitem.sum_qty

​
Interface

​
Accessing the data

Dynamic datasets can power different delivery modes:
  • Snowflake - with a view, a table or a dynamic table
  • BI - creating an extract based on a dynamic dataset that was deployed to Snowflake
  • Python - with the honeydew hdapi python module
  • 3rd party such as dbt model - using honeydew APIs

​
Filtering Order

Filters defined as part of a dynamic dataset definition will apply to its query. For more details, see order of computation

​
Parameterized Datasets

Dynamic datasets can be parameterized with Parameters. Parameters can be specific to a dynamic dataset, or can overrule global parameters.

​
Domains

Dynamic datasets operate with the context of a domain if set and default domain otherwise. Filters, parameters and any other domain configuration will apply to the dynamic dataset.

​
Materialization in Snowflake

When a dynamic dataset is delivered to Snowflake, all fields are created in the chosen Snowflake objects (a view or a table):
  • Attributes as columns.
  • Metrics as columns. Metrics are aggregated and grouped by default by all the attributes (unless specified otherwise with a GROUP BY in the metric definition).
  • All filters of the dataset apply.
Dynamically changing the level of granularity is not possible after delivery - if need to explore the same dataset at different levels of granularity use the SQL interface.

​
Views

Datasets can be deployed as a Snowflake VIEW. Honeydew will maintain the underlying query and will detect if its logic different from the shared source of truth. Use views for:
  • Build live datasets for sharing
  • Integration with tools that can’t use live JDBC connection
  • Real-time data
Parameterized Datasets can be deployed as VIEWs. Parameters can be controlled with the Snowflake SET SQL command.

​
Tables

Datasets can be deployed as a Snowflake TABLE.
Honeydew will NOT automatically detect if the logic that built a TABLE has changed.
Use tables for:
  • “Freeze” a dataset for an ad-hoc analysis on it
  • Use cases where a dynamic table is impossible to use

​
Dynamic Tables

Datasets can be deployed as a Snowflake Dynamic Table. Honeydew will maintain the underlying query and will detect if its logic different from the shared source of truth. Following configuration is possible:
  • Target database and schema
  • Virtual data warehouse to use
  • Dynamic table lag units (seconds / minutes / hours / days)
  • Dynamic table lag amount
  • Should it refresh downstream dynamic tables
  • Dynamic table refresh mode (AUTO / FULL / INCREMENTAL). Default is FULL, if not provided.
  • Dynamic table initialize mode (ON_CREATE / ON_SCHEDULE). Default is ON_CREATE, if not provided.
  • Dynamic table clustering expressions (optional)
You can find more information about Dynamic Tables creation parameters here.
Use dynamic tables for:
  • Large data or complex queries that require performance
  • Continuous materialization with automatic refresh

​
Example

A dataset called example that include:
  • The attributes lineitem.l_linestatus and orders.o_orderdate
  • The metrics lineitem.sum_qty and lineitem.revenue
  • A filter such as lineitem.l_commitdate < date('1997-01-01')
It allows exploration on two attributes and two metrics, over a filtered set of data. The following live SQL query will: 
SELECT
    "lineitem.l_linestatus",
    AGG("lineitem.revenue") as revenue,
    MIN("orders.o_orderdate") as min_order_date
WHERE
    "orders.o_orderdate" > date('1997-01-01')
FROM dynamic_datasets.example
GROUP BY 1
return a table with 3 columns:
  • lineitem.l_linestatus unique values
  • revenue aggregated named metric grouped by lineitem.l_linestatus
  • min_order_date ad-hoc metric grouped by lineitem.l_linestatus
and will filter the data with two filters:
  • "orders.o_orderdate" > date('1997-01-01') given in the query
  • lineitem.l_commitdate < date('1997-01-01') defined in the dynamic dataset

​
YAML Schema

The schema for a dynamic dataset is: 
type: perspective
name: [dataset name]
owner: [owner]
domain: [domain name]
description: [description]
labels: [label list]
folder: [folder]
display_name: [display name]
hidden: [yes/no/true/false]
use_cache: [yes/no/true/false]
attributes:
  - [attribute expression]
  - ...
metrics:
  - [metric expression]
  - ...
filters:
  - [filter expression]
  - ...
parameters:
  - name: [parameter name]
    value: [parameter value]
  - ...
transform_sql: [SQL expression]
group_by_all: [yes/no/true/false] # group by all attributes to deduplicate the data, when no metrics are provided
delivery:
  - snowflake:
    target: [view/table/dynamic_table]
    name: [target table/view name]
    schema: [target schema name (optional)]
    database: [target database name (optional)]
    warehouse: [name of the warehouse to use for materialization (optional)]
    dynamic_table_settings: [used if dynamic_table target is selected]
      - lag: [optional]
        - num: [integer]
        - units: [seconds/minutes/hours/days]
      - downstream: [yes/no/true/false]
      - refresh_mode: [AUTO/FULL/INCREMENTAL]
      - initialize: [ON_CREATE/ON_SCHEDULE]
      - cluster_by: [comma-separated clustering keys (optional)]
    enabled: [yes/no/true/false]
Fields:
  • name: Name of the dataset
  • owner, description, labels, folder, display_name, hidden (all optional): Metadata
  • domain (optional): Domain in which context to run
  • attributes (optional): List of attribute expressions (can be named attributes or ad-hoc expressions)
  • metrics (optional): List of metric expressions (can be named metrics or ad-hoc expressions)
  • filters (optional): List of filter expressions (can be named boolean attributes or ad-hoc expressions)
  • parameters (optional): List of parameters and values (override workspace values, if exist)
  • use_cache (optional): Whether to use caches (for entities and/or pre-aggregations). Default - yes.
  • transform_sql (optional): An additional SQL transformation that will be applied to the dataset. A typical use case includes adding ORDER BY or LIMIT clauses.