A dbt macro that generates a Snowflake Semantic View from a dbt Semantic Model

README.md

dbt Macro: Semantic Model to Snowflake Semantic View

A dbt macro that generates a Snowflake Semantic View from a dbt semantic model.

Installation

1. Copy generate_semantic_view.sql into your macros/ directory.
2. Add Snowflake-Labs/dbt_semantic_view to your packages.yml and run dbt deps.

packages.yml:

...
  - package: Snowflake-Labs/dbt_semantic_view
    version: 1.0.3
...

Usage Notes

To use: create a dbt model and give it the name you want the semantic view in snowflake to have. Our team stores them in semantic_views/.

marts/example/semantic_views/example.sql:

{{ config(materialized='semantic_view') }}

-- depends_on: {{ ref('...') }}
-- depends_on: {{ ref('...') }}
-- depends_on: {{ ref('...') }}
-- depends_on: {{ ref('...') }}

{{ generate_semantic_view() }}

Dependency resolution is a bit funky at this point - add a depends_on comment listing all included dbt models.

Improvement points

• The macro generates a semantic view of all semantic models - i.e., it works for a single semantic view per project. It should be trivial to parameterize the macro to allow defining models to include, which - when passing parameters using the ref() statement - would have the added benefit of eliminating the need for the depends_on comments.
• Not all metrics have been tested.

If you make an improved version, or have an interest in teaming up and turning this into a dbt package, please post in the comments here!

generate_semantic_view.sql

{% macro generate_semantic_view() %}
-- noqa: disable=layout.indent
{# TODO add parameter to include/exclude models as needed. #}

{%- set semantic_models = [] -%}
{% if graph.semantic_models %}
    {%- for sm in graph.semantic_models.values() -%}
        {%- do semantic_models.append(sm) -%}
    {%- endfor -%}
{%- endif -%}

{%- set entity_primary_keys = {} -%}
{%- set sm_aliases = {} -%}

{%- for sm in semantic_models -%}
    {%- do entity_primary_keys.update({sm.name: get_primary_entity_id(sm)}) -%}
    {%- do sm_aliases.update({sm.name: sm.node_relation.alias }) -%}
{%- endfor %}

tables (
{% for sm in semantic_models %}
    {{ sm.node_relation.alias | upper }} as {{ ref(sm.node_relation.alias) }} 
    {% if entity_primary_keys[sm.name] -%}
        primary key ({{ entity_primary_keys[sm.name] | upper }})
    {%- endif %}
    {%- if sm.description %} comment={{ dbt.string_literal(sm.description) }}{% endif -%}
    {%- if not loop.last %},{% endif -%}
{%- endfor %}
)

{%- set relationships = [] -%}
{%- for sm in semantic_models -%}
    {%- for entity in sm.entities -%}
        {%- if entity.type == "foreign" -%}
            {%- do relationships.append({'sm': sm, 'entity': entity}) -%}
        {% endif %}
    {%- endfor -%}
{%- endfor %}

relationships(
{%- for rel in relationships %}
    {{ rel.sm.name | upper }}_{{ rel.entity.name | upper }} 
as {{ sm_aliases[rel.sm.name] }}({{ rel.entity.expr or rel.entity.name ~ "_ID" }}) 
references {{ sm_aliases[rel.entity.name] }}({{ entity_primary_keys[rel.entity.name]  | upper }})
    {%- if not loop.last %},{% endif %}
{%- endfor %}
)

{%- set dimensions = [] -%}
{%- for sm in semantic_models -%}
    {%- for dim in sm.dimensions -%}
        {%- do dimensions.append({'table': sm_aliases[sm.name], 'dimension': dim}) -%}
    {%- endfor -%}
{%- endfor %}

dimensions (
{%- for dimension in dimensions %}
    {{ dimension.table | upper }}.{{ dimension.dimension.expr or dimension.dimension.name | upper }} 
as {{ dimension.table | upper }}.{{ dimension.dimension.name | upper }}
    {%- if dimension.dimension.description %}
comment={{ dbt.string_literal(dimension.dimension.description) }}
    {% endif -%}
    {%- if not loop.last %},{% endif %}
{%- endfor %}
)

{%- set metrics = [] -%}
{%- for sm in semantic_models -%}
    {%- for measure in sm.measures -%}
        {%- do metrics.append({'table': sm_aliases[sm.name], 'measure': measure}) -%}
    {%- endfor -%}
{%- endfor %}

metrics (
{%- for metric in metrics %}
    {{ metric.table }}.{{ metric.measure.name | upper }} 
  as {% if metric.measure.agg == "sum" -%}
  SUM({{ metric.table }}.{{ metric.measure.expr }})
  {%- elif metric.measure.agg == "max" -%}
  MAX({{ metric.table }}.{{ metric.measure.expr }})
  {%- elif metric.measure.agg == "min" -%}
  MIN({{ metric.table }}.{{ metric.measure.expr }})
  {%- elif metric.measure.agg == "average" -%}
  AVG({{ metric.table }}.{{ metric.measure.expr }})
  {%- elif metric.measure.agg == "median" -%}
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY {{ metric.table }}.{{ metric.measure.expr }})
  {%- elif metric.measure.agg == "count_distinct" -%}
  COUNT(DISTINCT {{ metric.table }}.{{ metric.measure.expr }})
  {%- elif metric.measure.agg == "percentile" -%}
  PERCENTILE_CONT({{ metric.measure.percentile or 0.5 }}) WITHIN GROUP (ORDER BY {{ metric.table }}.{{ metric.measure.expr }})
  {%- elif metric.measure.agg == "sum_boolean" -%}
  SUM(CASE WHEN {{ metric.table }}.{{ metric.measure.expr }} THEN 1 ELSE 0 END)
  {%- else -%}
        {{ metric.table }}.{{ metric.measure.expr }}
    {%- endif -%}
    {% if metric.measure.description %}
  comment={{ dbt.string_literal(metric.measure.description) }}
    {% endif %}
    {%- if not loop.last %},{% endif %}
{%- endfor %}
)

{% if model.description %}
comment={{ dbt.string_literal(model.description) }}
{% endif %}

copy grants
{% endmacro %}