Overview

What You'll Learn

What You'll Build

What You'll Need

Setup

Create Target Database and Schema

CREATE DATABASE IF NOT EXISTS analytics;
CREATE SCHEMA IF NOT EXISTS analytics.demo;

Scaffold the Project Files in One Step

mkdir -p dbt-ossie-demo/macros \
         dbt-ossie-demo/models/staging \
         dbt-ossie-demo/models/marts
cd dbt-ossie-demo

cat << 'EOF' > requirements.txt
# Minimal set for dbt -> Ossie -> Snowflake semantic views (tested Oct. 16, 2026).
# dbt-core writes target/osi_document.json during parse; dbt-snowflake is the adapter
# that parse requires for a snowflake profile, and it builds the tables and runs the publish macro.
# No Apache Ossie package or converter is needed on this path.
dbt-core==1.12.6
dbt-snowflake==1.12.5
EOF

cat << 'EOF' > dbt_project.yml
name: dbt_ossie_demo
version: "1.0.0"
config-version: 2

profile: dbt_ossie_demo

model-paths: ["models"]
target-path: "target"
clean-targets: ["target", "dbt_packages"]

models:
  dbt_ossie_demo:
    staging:
      +materialized: view
    marts:
      +materialized: table
EOF

cat << 'EOF' > profiles.yml
dbt_ossie_demo:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      # Browser SSO by default. Set SNOWFLAKE_AUTHENTICATOR to another value
      # (for example oauth_authorization_code) if your account requires it.
      authenticator: "{{ env_var('SNOWFLAKE_AUTHENTICATOR', 'externalbrowser') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE') }}"
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"
      database: "{{ env_var('SNOWFLAKE_DATABASE', 'ANALYTICS') }}"
      schema: "{{ env_var('SNOWFLAKE_SCHEMA', 'DEMO') }}"
      threads: 8
      client_session_keep_alive: false
EOF

cat << 'EOF' > models/staging/_sources.yml
version: 2

sources:
  - name: tpch
    database: SNOWFLAKE_SAMPLE_DATA
    schema: TPCH_SF1
    tables:
      - name: orders
      - name: lineitem
      - name: customer
      - name: part
      - name: nation
EOF

cat << 'EOF' > models/staging/stg_orders.sql
-- Order headers, trimmed to a recent slice so the demo stays small.
select
    o_orderkey      as order_key,
    o_custkey       as customer_key,
    o_orderdate     as order_date,
    o_orderstatus   as order_status,
    o_totalprice    as order_total_price
from {{ source('tpch', 'orders') }}
where o_orderdate >= date '1998-01-01'
EOF

cat << 'EOF' > models/staging/stg_lineitem.sql
-- Line items are the grain of the fact table: one row per item per order.
select
    l_orderkey      as order_key,
    l_linenumber    as line_number,
    l_partkey       as part_key,
    l_quantity      as quantity,
    l_extendedprice as extended_price,
    l_discount      as discount
from {{ source('tpch', 'lineitem') }}
EOF

cat << 'EOF' > models/staging/stg_customers.sql
select
    c.c_custkey     as customer_key,
    c.c_name        as customer_name,
    c.c_mktsegment  as market_segment,
    n.n_name        as nation
from {{ source('tpch', 'customer') }} c
left join {{ source('tpch', 'nation') }} n
    on c.c_nationkey = n.n_nationkey
EOF

cat << 'EOF' > models/staging/stg_products.sql
select
    p_partkey   as part_key,
    p_name      as product_name,
    p_brand     as brand,
    p_mfgr      as manufacturer,
    p_type      as product_type
from {{ source('tpch', 'part') }}
EOF

cat << 'EOF' > models/marts/fct_order_items.sql
-- Fact table at order-item grain. Surrogate key is order_key + line_number,
-- which MetricFlow needs as a single primary entity column.
select
    o.order_key || '-' || l.line_number             as order_item_key,
    o.order_key,
    o.customer_key,
    l.part_key,
    o.order_date,
    o.order_status,
    l.quantity,
    l.extended_price,
    l.discount,
    l.extended_price * (1 - l.discount)             as net_revenue,
    l.extended_price * l.discount                   as discount_amount
from {{ ref('stg_orders') }} o
join {{ ref('stg_lineitem') }} l
    on o.order_key = l.order_key
EOF

cat << 'EOF' > models/marts/dim_customers.sql
select
    customer_key,
    customer_name,
    market_segment,
    nation
from {{ ref('stg_customers') }}
EOF

cat << 'EOF' > models/marts/dim_products.sql
select
    part_key,
    product_name,
    brand,
    manufacturer,
    product_type
from {{ ref('stg_products') }}
EOF

cat << 'EOF' > models/marts/metricflow_time_spine.sql
-- MetricFlow requires a daily time spine to resolve time-based and
-- cumulative metrics. Generated with Snowflake's GENERATOR.
with days as (
    select
        dateadd(day, seq4(), date '1990-01-01') as date_day
    from table(generator(rowcount => 20000))
)
select date_day
from days
where date_day < date '2045-01-01'
EOF

cat << 'EOF' > models/marts/_models.yml
version: 2

models:
  - name: metricflow_time_spine
    description: Daily time spine required by the dbt semantic layer.
    time_spine:
      standard_granularity_column: date_day
    columns:
      - name: date_day
        granularity: day
EOF

cat << 'EOF' > models/marts/_semantic_models.yml
semantic_models:
  - name: order_items
    model: ref('fct_order_items')
    defaults:
      agg_time_dimension: order_date
    entities:
      - name: order_item_key
        type: primary
      - name: customer_key
        type: foreign
      - name: part_key
        type: foreign
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
    measures:
      - name: net_revenue
        agg: sum
      - name: line_count
        agg: count
        expr: order_item_key
      - name: distinct_parts
        agg: count_distinct
        expr: part_key

  - name: customers
    model: ref('dim_customers')
    entities:
      - name: customer_key
        type: primary
    dimensions:
      - name: market_segment
        type: categorical

  - name: products
    model: ref('dim_products')
    entities:
      - name: part_key
        type: primary
    dimensions:
      - name: brand
        type: categorical

metrics:
  - name: total_revenue
    type: simple
    type_params:
      measure: net_revenue
  - name: item_count
    type: simple
    type_params:
      measure: line_count
  - name: revenue_per_item
    type: ratio
    type_params:
      numerator: total_revenue
      denominator: item_count
  - name: products_sold
    type: simple
    type_params:
      measure: distinct_parts
EOF

cat << 'EOF' > macros/publish_semantic_view.sql
{#-
  Publish dbt's Ossie document (target/osi_document.json) as a Snowflake semantic view
  named view_name. Run after `dbt parse` or `dbt build`:
    dbt run-operation publish_semantic_view --args '{view_name: sv_orders}'
-#}
{% macro publish_semantic_view(view_name, doc_path='target/osi_document.json') %}
{% if execute %}
  {%- set schema = target.database ~ '.' ~ target.schema -%}
  {%- set stage = schema ~ '.ossie_publish' -%}
  {% do run_query("create temporary stage if not exists " ~ stage ~ " file_format = (type = json)") %}
  {% do run_query("put 'file://" ~ doc_path ~ "' @" ~ stage ~ " auto_compress = false overwrite = true") %}
  {% set publish %}
    execute immediate $$
    declare
      doc varchar;
    begin
      select to_json(object_insert($1, 'semantic_model',
               array_construct(object_insert($1:semantic_model[0], 'name', '{{ view_name }}', true)), true))
        into :doc
        from @{{ stage }}/osi_document.json;
      call system$create_semantic_view_from_ossie_yaml('{{ schema }}', :doc);
      return 'published {{ schema }}.{{ view_name }}';
    end;
    $$
  {% endset %}
  {% set result = run_query(publish) %}
  {{ log(result.columns[0].values()[0], info=true) }}
{% endif %}
{% endmacro %}
EOF

Configure Python and Environment Variables

# Minimal set for dbt -> Ossie -> Snowflake semantic views (tested Oct. 16, 2026).
# dbt-core writes target/osi_document.json during parse; dbt-snowflake is the adapter
# that parse requires for a snowflake profile, and it builds the tables and runs the publish macro.
# No Apache Ossie package or converter is needed on this path.
dbt-core==1.12.6
dbt-snowflake==1.12.5

name: dbt_ossie_demo
version: "1.0.0"
config-version: 2

profile: dbt_ossie_demo

model-paths: ["models"]
target-path: "target"
clean-targets: ["target", "dbt_packages"]

models:
  dbt_ossie_demo:
    staging:
      +materialized: view
    marts:
      +materialized: table

dbt_ossie_demo:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      # Browser SSO by default. Set SNOWFLAKE_AUTHENTICATOR to another value
      # (for example oauth_authorization_code) if your account requires it.
      authenticator: "{{ env_var('SNOWFLAKE_AUTHENTICATOR', 'externalbrowser') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE') }}"
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"
      database: "{{ env_var('SNOWFLAKE_DATABASE', 'ANALYTICS') }}"
      schema: "{{ env_var('SNOWFLAKE_SCHEMA', 'DEMO') }}"
      threads: 8
      client_session_keep_alive: false

uv venv --python 3.12 .venv
uv pip install --python .venv -r requirements.txt
source .venv/bin/activate

export SNOWFLAKE_ACCOUNT=<org-account>
export SNOWFLAKE_USER=<user>
export SNOWFLAKE_ROLE=<role>
export SNOWFLAKE_WAREHOUSE=<warehouse>
export SNOWFLAKE_DATABASE=ANALYTICS      # default
export SNOWFLAKE_SCHEMA=DEMO             # default
# export SNOWFLAKE_AUTHENTICATOR=...     # defaults to externalbrowser

Review Staging Models

Inspect the TPC-H Source

version: 2

sources:
  - name: tpch
    database: SNOWFLAKE_SAMPLE_DATA
    schema: TPCH_SF1
    tables:
      - name: orders
      - name: lineitem
      - name: customer
      - name: part
      - name: nation

Inspect the Staging Views

-- Order headers, trimmed to a recent slice so the demo stays small.
select
    o_orderkey      as order_key,
    o_custkey       as customer_key,
    o_orderdate     as order_date,
    o_orderstatus   as order_status,
    o_totalprice    as order_total_price
from {{ source('tpch', 'orders') }}
where o_orderdate >= date '1998-01-01'

-- Line items are the grain of the fact table: one row per item per order.
select
    l_orderkey      as order_key,
    l_linenumber    as line_number,
    l_partkey       as part_key,
    l_quantity      as quantity,
    l_extendedprice as extended_price,
    l_discount      as discount
from {{ source('tpch', 'lineitem') }}

select
    c.c_custkey     as customer_key,
    c.c_name        as customer_name,
    c.c_mktsegment  as market_segment,
    n.n_name        as nation
from {{ source('tpch', 'customer') }} c
left join {{ source('tpch', 'nation') }} n
    on c.c_nationkey = n.n_nationkey

select
    p_partkey   as part_key,
    p_name      as product_name,
    p_brand     as brand,
    p_mfgr      as manufacturer,
    p_type      as product_type
from {{ source('tpch', 'part') }}

Review Star Schema

Inspect Fact and Dimension Models

-- Fact table at order-item grain. Surrogate key is order_key + line_number,
-- which MetricFlow needs as a single primary entity column.
select
    o.order_key || '-' || l.line_number             as order_item_key,
    o.order_key,
    o.customer_key,
    l.part_key,
    o.order_date,
    o.order_status,
    l.quantity,
    l.extended_price,
    l.discount,
    l.extended_price * (1 - l.discount)             as net_revenue,
    l.extended_price * l.discount                   as discount_amount
from {{ ref('stg_orders') }} o
join {{ ref('stg_lineitem') }} l
    on o.order_key = l.order_key

select
    customer_key,
    customer_name,
    market_segment,
    nation
from {{ ref('stg_customers') }}

select
    part_key,
    product_name,
    brand,
    manufacturer,
    product_type
from {{ ref('stg_products') }}

Inspect the Time Spine

-- MetricFlow requires a daily time spine to resolve time-based and
-- cumulative metrics. Generated with Snowflake's GENERATOR.
with days as (
    select
        dateadd(day, seq4(), date '1990-01-01') as date_day
    from table(generator(rowcount => 20000))
)
select date_day
from days
where date_day < date '2045-01-01'

version: 2

models:
  - name: metricflow_time_spine
    description: Daily time spine required by the dbt semantic layer.
    time_spine:
      standard_granularity_column: date_day
    columns:
      - name: date_day
        granularity: day

Build Semantic Layer

Inspect Semantic Models and Metrics

semantic_models:
  - name: order_items
    model: ref('fct_order_items')
    defaults:
      agg_time_dimension: order_date
    entities:
      - name: order_item_key
        type: primary
      - name: customer_key
        type: foreign
      - name: part_key
        type: foreign
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
    measures:
      - name: net_revenue
        agg: sum
      - name: line_count
        agg: count
        expr: order_item_key
      - name: distinct_parts
        agg: count_distinct
        expr: part_key

  - name: customers
    model: ref('dim_customers')
    entities:
      - name: customer_key
        type: primary
    dimensions:
      - name: market_segment
        type: categorical

  - name: products
    model: ref('dim_products')
    entities:
      - name: part_key
        type: primary
    dimensions:
      - name: brand
        type: categorical

metrics:
  - name: total_revenue
    type: simple
    type_params:
      measure: net_revenue
  - name: item_count
    type: simple
    type_params:
      measure: line_count
  - name: revenue_per_item
    type: ratio
    type_params:
      numerator: total_revenue
      denominator: item_count
  - name: products_sold
    type: simple
    type_params:
      measure: distinct_parts

Run dbt Build

dbt build --profiles-dir .

Publish Semantic View

Inspect the Publish Macro

{#-
  Publish dbt's Ossie document (target/osi_document.json) as a Snowflake semantic view
  named view_name. Run after `dbt parse` or `dbt build`:
    dbt run-operation publish_semantic_view --args '{view_name: sv_orders}'
-#}
{% macro publish_semantic_view(view_name, doc_path='target/osi_document.json') %}
{% if execute %}
  {%- set schema = target.database ~ '.' ~ target.schema -%}
  {%- set stage = schema ~ '.ossie_publish' -%}
  {% do run_query("create temporary stage if not exists " ~ stage ~ " file_format = (type = json)") %}
  {% do run_query("put 'file://" ~ doc_path ~ "' @" ~ stage ~ " auto_compress = false overwrite = true") %}
  {% set publish %}
    execute immediate $$
    declare
      doc varchar;
    begin
      select to_json(object_insert($1, 'semantic_model',
               array_construct(object_insert($1:semantic_model[0], 'name', '{{ view_name }}', true)), true))
        into :doc
        from @{{ stage }}/osi_document.json;
      call system$create_semantic_view_from_ossie_yaml('{{ schema }}', :doc);
      return 'published {{ schema }}.{{ view_name }}';
    end;
    $$
  {% endset %}
  {% set result = run_query(publish) %}
  {{ log(result.columns[0].values()[0], info=true) }}
{% endif %}
{% endmacro %}

Run the Publish Operation

dbt run-operation publish_semantic_view --profiles-dir . --args '{view_name: sv_orders}'

Query Semantic View

Run a Semantic View Query

SELECT brand,
       AGG(total_revenue)    AS total_revenue,
       AGG(revenue_per_item) AS revenue_per_item,
       AGG(products_sold)    AS products_sold
  FROM analytics.demo.sv_orders
 WHERE market_segment = 'BUILDING'
 GROUP BY brand
 ORDER BY total_revenue DESC
 LIMIT 5;

Modeling Guidelines Worth Knowing

Clean Up

DROP SEMANTIC VIEW IF EXISTS analytics.demo.sv_orders;
DROP SCHEMA IF EXISTS analytics.demo;

Conclusion And Resources

What You Learned

Related Resources