Migrating dbt Metrics to Snowflake: Why Rolling Windows Break and How to Rebuild Them
The article details the migration of dbt metric definitions to Snowflake semantic views via Apache Ossie, revealing that rolling window metrics degrade to simple sums and cross-table filters cause import errors, requiring manual reconstruction and query-granularity validation to ensure numerical parity.
Snowflake recently announced a public preview path to import metric definitions from dbt via Apache Ossie, an open specification that unifies semantic models across tools. The official guide demonstrates the end‑to‑end flow: dbt exports metric definitions in Ossie format, Snowflake imports them to create semantic views, and downstream queries invoke those views instead of hand‑written SQL. The example uses three source tables — orders, order_items, and customers — staged into a fact table fct_orders. A query that filters to a customer segment, groups by brand, and computes total revenue plus a revenue‑per‑row ratio returns results identical to a manual three‑table join.
Cross‑table filter references break on import
When a metric definition embeds a filter that traverses a relationship — for instance, a "category revenue" metric that restricts order_items to rows whose product.category equals a specific value — the export step flattens the join path into a plain column name. Snowflake’s importer cannot resolve that column, so the import fails with an error. The guide notes that the working brand‑grouped query only proves the joins used in that query are sound; any metric that carries its own cross‑table filter must be inspected individually and, if necessary, rebuilt inside the Snowflake semantic view.
Rolling window metrics lose their window definition
The article illustrates the problem with a concrete revenue dashboard. Three orders exist: 100 CNY on 5 Nov, 30 CNY on 6 Nov, and 70 CNY on 12 Nov. The interval total (5‑12 Nov) correctly sums to 200 CNY. A 7‑day rolling revenue for 12 Nov should cover 6‑12 Nov, yielding 30 + 70 = 100 CNY. However, the current dbt‑to‑Ossie export converts a rolling metric into a simple SUM(amount). Snowflake’s native semantic view supports window functions, but the Ossie import does not yet ingest window specifications. If the exported formula is grouped by date, each day shows only that day’s revenue — 12 Nov shows 70 CNY, missing the 6 Nov order.
Rebuilding the window in the target semantic view restores the correct logic, but the article warns about a subtle filter interaction: even if the chart starts rendering on 12 Nov, the first point’s calculation must still see 6 Nov data. Applying a date filter that excludes pre‑12 Nov rows before the window computation will again drop the 30 CNY order. By 13 Nov the window shifts to 7‑13 Nov; the 6 Nov order slides out and, with no new order that day, rolling revenue falls to 70 CNY. The same 30 CNY order must appear on 12 Nov and disappear on 13 Nov — a concrete regression check.
Validation at query granularity and regression‑safe deployment
For migration acceptance, the author recommends running the exact dashboard queries against both systems on a fixed dataset. Compare interval totals once, then compare daily trend rows one by one. If the dashboard slices by brand, include brand in the comparison key. If the original chart shows rolling values on zero‑revenue days, verify the target returns rows for those days too. For ratio metrics, compare numerator and denominator separately — both can be off while the ratio coincidentally matches.
Because Snowflake’s deployment scripts replace same‑named views on re‑run, any manual fixes applied after import would be overwritten. The team should maintain the supplemental definitions (window clauses, rebuilt cross‑table filters) alongside the deployment scripts, rebuild the complete view in a validation environment, and execute the fixed comparison queries before promoting to production. This ensures that every future release — whether a dashboard change, a new natural‑language query via Snowflake Intelligence, or a view republishing — continues to return 100 CNY for 12 Nov rolling revenue under the same orders and definitions.
References: Snowflake Engineering Blog – Building Semantic Views from dbt via Apache Ossie; Snowflake Hands‑on Guide – Import Example, Cross‑table Filters, Rolling Windows & Re‑deployment; Snowflake Technical Docs – Defining Window Metrics in Native Semantic Views; dbt Official Docs – Cumulative Metrics and Time Windows.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
