Semantic Layer Implementation: Building and Managing Metrics with dbt Metrics and Cube.js

Introduction

Your finance team is querying revenue. Your product team is querying revenue. Your executives are seeing revenue in three different dashboards — and none of the numbers match. The finance team filters out refunds. The product team includes trial conversions. The BI tool applies its own session-level aggregation before anyone even touches the data. Everyone is right by their own logic, and that's exactly the problem.

This is the classic "metric sprawl" failure mode, and it's not a data quality problem — it's an architecture problem. When metric definitions live inside individual SQL files, BI tool calculated fields, or analyst notebooks, you have as many definitions of "revenue" as you have people who care about revenue. The semantic layer pattern exists to solve this: a single, governed place where business metrics are defined once, tested, and served consistently to every consumer — whether that's a BI tool, a data application, or an API call from a product engineer.

By the end of this lesson, you'll have built a production-grade semantic layer using both dbt Metrics (the definition-time layer) and Cube.js (the serving layer). You'll understand when to use each, how they complement each other, and how to avoid the architectural traps that cause teams to abandon their semantic layers six months after implementing them.

What you'll learn:

• How to define metrics in dbt using the MetricFlow specification, including measures, dimensions, and grain
• How Cube.js serves metrics as a queryable API, including caching strategies and pre-aggregation design
• How to connect dbt-defined models to Cube.js schemas for a unified semantic layer
• Advanced patterns for handling slowly changing dimensions, multi-currency metrics, and composite business logic
• How to test, version, and govern your metric definitions so they stay trustworthy over time

---

Prerequisites

This lesson assumes you're comfortable with:

• dbt Core or dbt Cloud at an intermediate level (you've written models, tests, and macros)
• SQL and data warehousing concepts (aggregations, window functions, grain)
• Basic JavaScript or YAML configuration
• Familiarity with at least one data warehouse (Snowflake, BigQuery, or Redshift)
• Docker for running Cube.js locally

You don't need prior experience with semantic layers or MetricFlow, but you should understand why inconsistent metrics are a problem worth solving architecturally.

---

What the Semantic Layer Actually Is (and Isn't)

Before writing a single line of YAML, let's be precise about what we're building. The term "semantic layer" gets thrown around loosely — sometimes it means a BI tool's calculated fields, sometimes it means a data catalog, sometimes it means a physical materialization of pre-aggregated data.

For our purposes, a semantic layer has three specific responsibilities:

1. Definition: Metrics and dimensions are defined declaratively in a single place, in a way that can be version-controlled, reviewed, and tested. This is what dbt Metrics handles.

2. Serving: Consumers query metrics through a consistent API, and the layer translates those queries into efficient SQL against your warehouse. This is Cube.js's domain.

3. Governance: The layer enforces access controls, caches appropriately, and provides enough observability that you know when something breaks. Both tools contribute here.

What the semantic layer is not is a replacement for your transformation layer. You still need dbt models cleaning and joining raw data. The semantic layer sits on top of those clean models and answers the question: "Given these clean tables, what does 'monthly active users' mean, and how should I compute it for any combination of time grain, dimension, and filter?"

The critical architectural insight is that the semantic layer is a translation layer, not a storage layer. It doesn't (usually) hold your data. It holds the knowledge of how to query your data correctly.

---

Part 1: dbt Metrics with MetricFlow

Understanding MetricFlow's Mental Model

dbt's semantic layer is powered by MetricFlow, which dbt acquired and integrated. MetricFlow thinks about metrics differently than traditional BI tools. Instead of defining a metric as a SQL expression, you define it in terms of measures (the thing you're aggregating), entities (the join keys), and dimensions (the axes along which you slice).

This separation matters because it lets MetricFlow generate correct SQL for any combination of dimensions and time grains without you having to write every permutation. If you define revenue as a sum of order_amount and you have dimensions for country, product_category, and customer_segment, MetricFlow can correctly handle revenue by country, revenue by product and month, and revenue by customer_segment and quarter — all from a single metric definition.

Configuring Your dbt Project for MetricFlow

First, ensure your dbt_project.yml has the right configuration:

# dbt_project.yml
name: 'analytics'
version: '1.0.0'
config-version: 2

profile: 'analytics'

model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
analysis-paths: ["analyses"]
macro-paths: ["macros"]

semantic-model-paths: ["models/semantic"]
metric-paths: ["models/metrics"]

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

Install the MetricFlow CLI and dbt-core packages:

# packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]

pip install dbt-core dbt-snowflake "dbt-metricflow[snowflake]"

Building Your Foundation Models

Your semantic layer is only as good as the models underneath it. For this lesson, we'll work with a realistic e-commerce dataset. Here's the mart model that will power our metrics:

-- models/marts/fct_orders.sql
{{
  config(
    materialized='table',
    cluster_by=['ordered_at']
  )
}}

with orders as (
    select * from {{ ref('stg_orders') }}
),

order_items as (
    select * from {{ ref('stg_order_items') }}
),

customers as (
    select * from {{ ref('dim_customers') }}
),

products as (
    select * from {{ ref('dim_products') }}
),

order_totals as (
    select
        order_id,
        sum(item_revenue) as gross_revenue,
        sum(item_cost) as total_cost,
        sum(case when is_refunded then item_revenue else 0 end) as refund_amount,
        sum(item_revenue) - sum(case when is_refunded then item_revenue else 0 end) as net_revenue,
        count(distinct product_id) as distinct_products_ordered,
        count(line_item_id) as total_line_items
    from order_items
    group by 1
),

final as (
    select
        -- Primary key
        orders.order_id,

        -- Foreign keys (entities for MetricFlow)
        orders.customer_id,
        orders.store_id,

        -- Dimensions
        orders.status as order_status,
        orders.channel as acquisition_channel,
        customers.country_code,
        customers.customer_segment,
        customers.is_new_customer,

        -- Time dimensions
        orders.ordered_at,
        date_trunc('day', orders.ordered_at) as ordered_date,
        date_trunc('week', orders.ordered_at) as ordered_week,
        date_trunc('month', orders.ordered_at) as ordered_month,

        -- Measures
        order_totals.gross_revenue,
        order_totals.net_revenue,
        order_totals.refund_amount,
        order_totals.total_cost,
        order_totals.gross_revenue - order_totals.total_cost as gross_profit,
        order_totals.distinct_products_ordered,
        order_totals.total_line_items,

        -- Flags
        case when orders.status = 'completed' then 1 else 0 end as is_completed_order

    from orders
    left join order_totals using (order_id)
    left join customers using (customer_id)
)

select * from final

Defining a Semantic Model

Now we create the semantic model — the layer that tells MetricFlow how to interpret this table:

# models/semantic/sem_orders.yml
semantic_models:
  - name: orders
    description: >
      Order-grain fact table. Each row represents one order. Use this model
      for revenue, order count, and gross profit metrics. For customer-level
      metrics (LTV, cohort analysis), join through the customer entity.
    
    model: ref('fct_orders')
    
    # Entities are your join keys — they connect semantic models together
    entities:
      - name: order
        type: primary
        expr: order_id
      
      - name: customer
        type: foreign
        expr: customer_id
      
      - name: store
        type: foreign
        expr: store_id
    
    # Dimensions are the attributes you slice by
    dimensions:
      - name: order_status
        type: categorical
        expr: order_status
        description: Current status of the order (pending, completed, refunded, cancelled)
      
      - name: acquisition_channel
        type: categorical
        expr: acquisition_channel
        description: The channel through which the customer placed this order
      
      - name: country_code
        type: categorical
        expr: country_code
        description: Two-letter ISO country code of the customer
      
      - name: customer_segment
        type: categorical
        expr: customer_segment
        description: "Marketing segment: enterprise, smb, consumer"
      
      - name: is_new_customer
        type: categorical
        expr: is_new_customer
        description: True if this was the customer's first order ever
      
      # Time dimensions are special — MetricFlow uses them for grain
      - name: ordered_at
        type: time
        type_params:
          time_granularity: day
        expr: ordered_at
    
    # Measures are the things you aggregate
    measures:
      - name: gross_revenue
        agg: sum
        expr: gross_revenue
        description: Total revenue before refunds
        create_metric: false  # We'll define the metric explicitly below
      
      - name: net_revenue
        agg: sum
        expr: net_revenue
        description: Revenue after subtracting refunds
        create_metric: false
      
      - name: refund_amount
        agg: sum
        expr: refund_amount
      
      - name: gross_profit
        agg: sum
        expr: gross_profit
      
      - name: order_count
        agg: count_distinct
        expr: order_id
        description: Number of distinct orders
      
      - name: completed_order_count
        agg: sum
        expr: is_completed_order
        description: Number of orders with completed status

Defining Metrics

With the semantic model in place, metrics become declarations of intent rather than SQL expressions:

# models/metrics/revenue_metrics.yml
metrics:
  # Simple metric
  - name: gross_revenue
    label: "Gross Revenue"
    description: >
      Total order revenue before accounting for refunds. Use this metric
      when you want to understand top-line demand. For profitability analysis,
      use net_revenue or gross_profit instead.
    type: simple
    type_params:
      measure: gross_revenue
    filter: |
      {{ Dimension('order__order_status') }} != 'cancelled'
  
  # Another simple metric with a filter
  - name: net_revenue
    label: "Net Revenue"
    description: >
      Revenue after subtracting refunded amounts. This is the primary revenue
      metric for financial reporting.
    type: simple
    type_params:
      measure: net_revenue
    filter: |
      {{ Dimension('order__order_status') }} != 'cancelled'
  
  # Ratio metric — this is where MetricFlow shines
  - name: refund_rate
    label: "Refund Rate"
    description: >
      Percentage of gross revenue that was refunded. Calculated as
      refund_amount / gross_revenue. High values indicate fulfillment
      or product quality issues.
    type: ratio
    type_params:
      numerator: refund_amount
      denominator: gross_revenue
  
  # Derived metric — computed from other metrics
  - name: gross_margin
    label: "Gross Margin %"
    description: >
      Gross profit as a percentage of gross revenue.
      Formula: (gross_revenue - total_cost) / gross_revenue
    type: derived
    type_params:
      expr: gross_profit / gross_revenue
      metrics:
        - name: gross_profit
        - name: gross_revenue
  
  # Cumulative metric — running totals
  - name: cumulative_revenue_mtd
    label: "Revenue (Month to Date)"
    description: Running total of net revenue, reset at the start of each month.
    type: cumulative
    type_params:
      measure: net_revenue
      window: 1 month
      grain_to_date: month
  
  # Order count
  - name: orders
    label: "Orders"
    description: Count of distinct orders placed, excluding cancellations.
    type: simple
    type_params:
      measure: order_count
    filter: |
      {{ Dimension('order__order_status') }} != 'cancelled'
  
  # Average order value — a derived metric
  - name: average_order_value
    label: "Average Order Value"
    description: >
      Net revenue divided by order count. A key e-commerce health metric.
      Increasing AOV typically indicates successful upsell/cross-sell or
      product mix shift toward higher-value items.
    type: derived
    type_params:
      expr: net_revenue / orders
      metrics:
        - name: net_revenue
        - name: orders

Handling the Multi-Currency Problem

One of the nastiest edge cases in metric definition is multi-currency data. If your orders table contains amounts in multiple currencies, a naive sum(order_amount) is meaningless. Here's how to handle this at the semantic layer:

-- models/marts/fct_orders_usd.sql
-- This model normalizes all amounts to USD using daily exchange rates
-- The semantic layer should only see normalized amounts

with orders as (
    select * from {{ ref('fct_orders') }}
),

exchange_rates as (
    select * from {{ ref('dim_exchange_rates') }}
    where target_currency = 'USD'
),

normalized as (
    select
        orders.*,
        
        -- Normalized amounts in USD
        orders.gross_revenue * coalesce(exchange_rates.rate, 1.0) as gross_revenue_usd,
        orders.net_revenue * exchange_rates.rate as net_revenue_usd,
        orders.gross_profit * exchange_rates.rate as gross_profit_usd,
        
        orders.currency_code
    
    from orders
    left join exchange_rates 
        on orders.currency_code = exchange_rates.source_currency
        and date_trunc('day', orders.ordered_at) = exchange_rates.rate_date
)

select * from normalized

Warning: Never normalize currencies in the semantic layer itself. Do it in your dbt transformation models. The semantic layer should receive clean, normalized data. Putting currency conversion logic in metric definitions makes those definitions brittle and hard to test.

Testing Your Metrics

MetricFlow provides a CLI for testing metric definitions before they reach production:

# Validate your semantic layer configuration
mf validate-configs

# Query a metric to test it
mf query --metrics gross_revenue --group-by metric_time__month

# Query with dimension filters
mf query \
  --metrics gross_revenue,net_revenue,refund_rate \
  --group-by metric_time__month,order__country_code \
  --where "order__customer_segment = 'enterprise'" \
  --start-time 2024-01-01 \
  --end-time 2024-12-31

# Explain the SQL that will be generated (critical for debugging)
mf query \
  --metrics average_order_value \
  --group-by metric_time__week \
  --explain

The --explain flag is invaluable. It shows you the exact SQL MetricFlow generates, which lets you verify correctness and optimize your underlying models for the query patterns MetricFlow will actually run.

-- Example output from --explain for average_order_value
-- MetricFlow generates a subquery structure to handle the ratio correctly

with metric_subquery as (
    select
        date_trunc('week', ordered_at) as metric_time__week,
        sum(net_revenue) as net_revenue,
        count(distinct order_id) as orders
    from analytics.marts.fct_orders
    where order_status != 'cancelled'
    group by 1
)
select
    metric_time__week,
    safe_divide(net_revenue, orders) as average_order_value
from metric_subquery
order by 1

Versioning Metrics

One underappreciated feature of the dbt semantic layer is that metric definitions live in version control alongside your models. This means you can use standard Git workflows for governance:

# In your metric definition, add explicit versioning context
- name: net_revenue
  label: "Net Revenue"
  description: >
    [v2 - Updated 2024-03-15] Revenue after subtracting refunds, 
    excluding cancelled orders. 
    
    BREAKING CHANGE from v1: Previously included cancelled orders in 
    denominator. This was incorrect per Finance team definition. 
    All historical dashboards should be updated.
    
    Owner: @data-team/finance-analytics
    Stakeholders: CFO, VP Finance, Revenue Operations
  type: simple
  type_params:
    measure: net_revenue
  filter: |
    {{ Dimension('order__order_status') }} not in ('cancelled', 'fraud')

Tip: Treat your metric definitions like APIs. Any change to filter logic, measure selection, or aggregation type is a breaking change for downstream consumers. Use your PR description to communicate impact, and consider maintaining old metric definitions under a versioned name (net_revenue_v1) during transition periods.

---

Part 2: Cube.js as the Serving Layer

Why You Need a Serving Layer

dbt Metrics (MetricFlow) is excellent at defining metrics, but it has limitations as a serving layer for interactive applications:

1. It generates SQL that runs against your warehouse — fine for scheduled reports, expensive for interactive dashboards with many concurrent users
2. It doesn't provide an HTTP API that BI tools and data apps can query directly
3. It has no built-in caching or pre-aggregation
4. It doesn't handle row-level security for multi-tenant use cases

Cube.js fills these gaps. It sits between your warehouse and your consumers, providing a REST/GraphQL/SQL API, an intelligent caching layer, and pre-aggregation capabilities that can make interactive analytics fast even over billions of rows.

Architecture Overview

dbt Transformations → Clean Warehouse Tables
                              ↓
                         Cube.js Schema
                         (reads from warehouse)
                              ↓
              ┌───────────────┴───────────────┐
              ↓                               ↓
         REST API                      Pre-aggregation
    (for BI tools,                    (materialized in
     data apps)                        warehouse)
              ↓                               ↓
    Superset / Metabase             Fast interactive
    Custom dashboards                  queries

Setting Up Cube.js

npx cubejs-cli create analytics-semantic-layer -d snowflake
cd analytics-semantic-layer

Configure your warehouse connection in .env:

# .env
CUBEJS_DB_TYPE=snowflake
CUBEJS_DB_HOST=yourorg.snowflakecomputing.com
CUBEJS_DB_USER=CUBE_SVC_USER
CUBEJS_DB_PASS=your_secure_password
CUBEJS_DB_NAME=ANALYTICS
CUBEJS_DB_SCHEMA=MARTS
CUBEJS_DB_WAREHOUSE=COMPUTE_WH
CUBEJS_DB_ROLE=CUBE_READER

CUBEJS_API_SECRET=your_long_random_secret_here
CUBEJS_DEV_MODE=true

# Use Redis for production caching
CUBEJS_CACHE_AND_QUEUE_DRIVER=redis
REDIS_URL=redis://localhost:6379

Writing Cube.js Schemas

Cube.js uses JavaScript (or YAML) to define cubes — the equivalent of semantic models. We'll use JavaScript for its flexibility:

// schema/Orders.js
const { defineJoinedSchema } = require('./helpers/schemaHelpers');

cube('Orders', {
  // Point at the dbt-generated mart table
  sql: `SELECT * FROM ${COMPILE_CONTEXT.securityContext?.schema || 'MARTS'}.FCT_ORDERS`,
  
  // Alternative: reference a specific dbt model output
  // sql: `SELECT * FROM ANALYTICS.MARTS.FCT_ORDERS`,

  title: 'Orders',
  description: 'Order-grain fact table. Each row = one order.',

  // Joins connect this cube to dimension cubes
  joins: {
    Customers: {
      sql: `${Orders}.customer_id = ${Customers}.customer_id`,
      relationship: 'many_to_one'
    },
    Stores: {
      sql: `${Orders}.store_id = ${Stores}.store_id`,
      relationship: 'many_to_one'
    }
  },

  // Measures are the aggregatable values
  measures: {
    count: {
      type: 'count',
      title: 'Order Count',
      description: 'Number of distinct orders'
    },

    grossRevenue: {
      sql: 'gross_revenue',
      type: 'sum',
      title: 'Gross Revenue',
      description: 'Total revenue before refunds',
      format: 'currency',
      filters: [
        { sql: `${CUBE}.order_status != 'cancelled'` }
      ]
    },

    netRevenue: {
      sql: 'net_revenue',
      type: 'sum',
      title: 'Net Revenue',
      description: 'Revenue after refunds, excluding cancellations',
      format: 'currency',
      filters: [
        { sql: `${CUBE}.order_status != 'cancelled'` }
      ]
    },

    refundAmount: {
      sql: 'refund_amount',
      type: 'sum',
      title: 'Refund Amount',
      format: 'currency'
    },

    grossProfit: {
      sql: 'gross_profit',
      type: 'sum',
      title: 'Gross Profit',
      format: 'currency'
    },

    // Calculated measures — defined once, available everywhere
    refundRate: {
      sql: `${refundAmount} / NULLIF(${grossRevenue}, 0)`,
      type: 'number',
      title: 'Refund Rate',
      description: 'Refund amount as % of gross revenue',
      format: 'percent'
    },

    averageOrderValue: {
      sql: `${netRevenue} / NULLIF(${count}, 0)`,
      type: 'number',
      title: 'Average Order Value',
      format: 'currency'
    },

    grossMarginPct: {
      sql: `${grossProfit} / NULLIF(${grossRevenue}, 0)`,
      type: 'number',
      title: 'Gross Margin %',
      format: 'percent'
    },

    // Running total — useful for MTD/QTD views
    cumulativeNetRevenue: {
      sql: 'net_revenue',
      type: 'runningTotal',
      title: 'Cumulative Net Revenue (Running Total)'
    }
  },

  // Dimensions are filterable/groupable attributes
  dimensions: {
    orderId: {
      sql: 'order_id',
      type: 'number',
      primaryKey: true,
      shown: false  // Don't expose raw IDs in BI tools
    },

    orderStatus: {
      sql: 'order_status',
      type: 'string',
      title: 'Order Status'
    },

    acquisitionChannel: {
      sql: 'acquisition_channel',
      type: 'string',
      title: 'Acquisition Channel'
    },

    countryCode: {
      sql: 'country_code',
      type: 'string',
      title: 'Country'
    },

    customerSegment: {
      sql: 'customer_segment',
      type: 'string',
      title: 'Customer Segment'
    },

    isNewCustomer: {
      sql: 'is_new_customer',
      type: 'boolean',
      title: 'New Customer?'
    },

    // Time dimensions with hierarchies
    orderedAt: {
      sql: 'ordered_at',
      type: 'time',
      title: 'Order Date'
    }
  },

  // Segments are named filter presets — extremely useful for business logic
  segments: {
    completed: {
      sql: `${CUBE}.order_status = 'completed'`,
      title: 'Completed Orders Only'
    },

    newCustomers: {
      sql: `${CUBE}.is_new_customer = true`,
      title: 'New Customer Orders'
    },

    highValue: {
      sql: `${CUBE}.net_revenue > 500`,
      title: 'High Value Orders (>$500)'
    },

    enterprise: {
      sql: `${CUBE}.customer_segment = 'enterprise'`,
      title: 'Enterprise Segment'
    }
  },

  // Pre-aggregations are the performance optimization layer
  preAggregations: {
    // Daily rollup — covers most dashboard queries
    dailyRevenue: {
      measures: [
        Orders.grossRevenue,
        Orders.netRevenue,
        Orders.refundAmount,
        Orders.grossProfit,
        Orders.count
      ],
      dimensions: [
        Orders.orderStatus,
        Orders.acquisitionChannel,
        Orders.countryCode,
        Orders.customerSegment
      ],
      timeDimension: Orders.orderedAt,
      granularity: 'day',
      // Refresh once per hour during business hours
      refreshKey: {
        every: '1 hour'
      },
      // Partition by month for efficient range queries
      partitionGranularity: 'month',
      // Build in parallel for faster refresh
      buildRangeStart: {
        sql: `SELECT DATE_TRUNC('month', DATEADD(month, -13, CURRENT_DATE()))`
      },
      buildRangeEnd: {
        sql: `SELECT CURRENT_TIMESTAMP()`
      }
    },

    // Monthly rollup — for trend charts
    monthlyRevenue: {
      measures: [
        Orders.grossRevenue,
        Orders.netRevenue,
        Orders.grossProfit,
        Orders.count
      ],
      dimensions: [
        Orders.countryCode,
        Orders.customerSegment,
        Orders.acquisitionChannel
      ],
      timeDimension: Orders.orderedAt,
      granularity: 'month',
      refreshKey: {
        every: '24 hours'
      }
    }
  }
});

Handling Slowly Changing Dimensions

A common gotcha is querying historical data against dimensions that change over time. If a customer's customer_segment changes from smb to enterprise, do you want to reclassify their historical orders? Usually no — you want the segment at the time of the order.

Handle this in your dbt models, not in Cube.js:

-- models/marts/fct_orders.sql
-- Join to customer snapshot at time of order, not current state

with orders as (select * from {{ ref('stg_orders') }}),

-- Use dbt_utils.star to pull from the SCD type 2 customer dimension
-- at the point in time of each order
customer_snapshots as (
    select * from {{ ref('dim_customers_scd') }}
),

orders_with_historical_customer as (
    select
        orders.*,
        cs.customer_segment as customer_segment_at_order_time,
        cs.customer_tier as customer_tier_at_order_time,
        cs.country_code
    from orders
    left join customer_snapshots cs
        on orders.customer_id = cs.customer_id
        and orders.ordered_at >= cs.dbt_valid_from
        and (orders.ordered_at < cs.dbt_valid_to or cs.dbt_valid_to is null)
)

select * from orders_with_historical_customer

Warning: If you join on current customer attributes in Cube.js at query time, your historical revenue-by-segment numbers will change whenever a customer changes segments. This will cause your data to "rewrite history" silently and make year-over-year comparisons meaningless. Always snapshot dimension attributes at the fact grain in your dbt models.

Implementing Row-Level Security

Cube.js handles multi-tenant data access through its security context — a JWT payload that gets injected into your SQL:

// schema/Orders.js — with row-level security
cube('Orders', {
  sql: `
    SELECT * FROM MARTS.FCT_ORDERS
    WHERE 1=1
    ${COMPILE_CONTEXT.securityContext?.storeId 
      ? `AND store_id = ${COMPILE_CONTEXT.securityContext.storeId}` 
      : ''}
    ${COMPILE_CONTEXT.securityContext?.allowedCountries?.length > 0
      ? `AND country_code IN (${COMPILE_CONTEXT.securityContext.allowedCountries
          .map(c => `'${c}'`).join(', ')})`
      : ''}
  `,
  
  // ... rest of schema
});

// cube.js — JWT validation and context extraction
module.exports = {
  checkSqlAuth: async (query, password) => {
    // Validate the JWT and extract claims
    const payload = jwt.verify(password, process.env.CUBEJS_API_SECRET);
    return {
      password,
      securityContext: {
        userId: payload.sub,
        storeId: payload.store_id,
        allowedCountries: payload.allowed_countries || [],
        role: payload.role
      }
    };
  },

  queryRewrite: (query, { securityContext }) => {
    // Enforce that non-admin users can only see their store's data
    if (securityContext.role !== 'admin' && securityContext.storeId) {
      query.filters = [
        ...(query.filters || []),
        {
          member: 'Orders.storeId',
          operator: 'equals',
          values: [String(securityContext.storeId)]
        }
      ];
    }
    return query;
  }
};

Pre-aggregation Design: The Performance Architecture

Pre-aggregations are Cube.js's most powerful feature and the most commonly misused. Here's how to think about them architecturally.

Cube.js materializes pre-aggregations in your warehouse (or an external rollup store). When a query comes in, it checks if any pre-aggregation covers the query's measures, dimensions, and time grain. If yes, it queries the pre-aggregation instead of the raw table — potentially 100x faster.

The design challenge is that you can't pre-aggregate everything. You need to understand your query patterns:

preAggregations: {
  // Pattern 1: High-cardinality dimensions go in separate pre-aggs
  // Don't combine 10 dimensions in one pre-agg — the cardinality explodes
  
  // Good: One pre-agg for geographic analysis
  geoRevenue: {
    measures: [Orders.grossRevenue, Orders.netRevenue, Orders.count],
    dimensions: [Orders.countryCode],
    timeDimension: Orders.orderedAt,
    granularity: 'day',
    partitionGranularity: 'month'
  },

  // Good: Separate pre-agg for segment analysis  
  segmentRevenue: {
    measures: [Orders.grossRevenue, Orders.netRevenue, Orders.count, Orders.averageOrderValue],
    dimensions: [Orders.customerSegment, Orders.acquisitionChannel],
    timeDimension: Orders.orderedAt,
    granularity: 'day',
    partitionGranularity: 'month'
  },

  // Bad (anti-pattern): One giant pre-agg with all dimensions
  // This creates enormous materialization with low hit rate
  // everythingPreagg: {
  //   measures: [...all measures...],
  //   dimensions: [...all 15 dimensions...],
  //   ...
  // }
}

Refresh strategy matters as much as the pre-agg design itself:

// For near-real-time data (streaming or frequent loads)
refreshKey: {
  sql: `SELECT MAX(updated_at) FROM MARTS.FCT_ORDERS`
  // Cube polls this query; when the result changes, it rebuilds
}

// For batch-loaded data with known schedule
refreshKey: {
  every: '6 hours',  // Simpler, lower overhead
  incremental: true  // Only rebuild new partitions
}

// For very large historical datasets — only build recent data
buildRangeStart: {
  sql: `SELECT DATEADD(month, -13, CURRENT_DATE())`
},
buildRangeEnd: {
  sql: `SELECT CURRENT_DATE()`
}

---

Part 3: Integrating dbt Metrics and Cube.js

The Integration Pattern

The cleanest integration pattern is: dbt defines the clean tables, Cube.js defines the metric semantics. You do not need to run both MetricFlow and Cube.js — pick one serving layer. If your primary consumers are BI tools and you need an API, use Cube.js. If your consumers are primarily dbt Cloud users and you want metrics in dbt Cloud's native semantic layer, use MetricFlow.

If you're using both, synchronize the definitions through a code generation step:

# scripts/sync_metrics_to_cube.py
# Reads dbt metric YAML files and generates Cube.js schema snippets
# This keeps definitions in sync without manual duplication

import yaml
import json
from pathlib import Path

def load_dbt_metrics(metrics_dir: str) -> list:
    """Load all metric definitions from dbt YAML files."""
    metrics = []
    for yaml_file in Path(metrics_dir).glob('**/*.yml'):
        with open(yaml_file) as f:
            content = yaml.safe_load(f)
            if content and 'metrics' in content:
                metrics.extend(content['metrics'])
    return metrics

def generate_cube_measure(metric: dict) -> dict:
    """Convert a dbt metric definition to a Cube.js measure."""
    measure = {
        'title': metric.get('label', metric['name']),
        'description': metric.get('description', ''),
    }
    
    metric_type = metric['type']
    
    if metric_type == 'simple':
        measure['type'] = 'sum'
        measure['sql'] = metric['type_params']['measure']
    elif metric_type == 'ratio':
        num = metric['type_params']['numerator']
        den = metric['type_params']['denominator']
        measure['type'] = 'number'
        measure['sql'] = f'${{CUBE}}.{num} / NULLIF(${{CUBE}}.{den}, 0)'
    elif metric_type == 'derived':
        measure['type'] = 'number'
        measure['sql'] = metric['type_params']['expr']
    
    return measure

if __name__ == '__main__':
    metrics = load_dbt_metrics('models/metrics')
    cube_measures = {m['name']: generate_cube_measure(m) for m in metrics}
    
    print("// Auto-generated from dbt metric definitions")
    print("// DO NOT EDIT MANUALLY — run scripts/sync_metrics_to_cube.py to regenerate")
    print(json.dumps({'measures': cube_measures}, indent=2))

Testing the Full Stack

End-to-end testing across both layers requires a structured approach:

# Step 1: Test dbt model freshness and data quality
dbt test --select marts.fct_orders

# Step 2: Validate semantic model definitions
mf validate-configs

# Step 3: Spot-check critical metrics against known values
mf query --metrics net_revenue --group-by metric_time__month \
  --start-time 2024-01-01 --end-time 2024-01-31

# Step 4: Compare MetricFlow output against Cube.js output
# (if running both) — numbers should match exactly
curl "http://localhost:4000/cubejs-api/v1/load" \
  -H "Authorization: Bearer ${CUBE_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{
    "query": {
      "measures": ["Orders.netRevenue"],
      "timeDimensions": [{
        "dimension": "Orders.orderedAt",
        "granularity": "month",
        "dateRange": ["2024-01-01", "2024-01-31"]
      }]
    }
  }'

Build a metric consistency test that runs in CI:

# tests/test_metric_consistency.py
import requests
import subprocess
import json
import pytest

CUBE_API = "http://localhost:4000/cubejs-api/v1"
CUBE_TOKEN = "your_dev_token"

def query_cube(measures, filters=None, time_range=None):
    query = {"measures": measures}
    if time_range:
        query["timeDimensions"] = [{
            "dimension": "Orders.orderedAt",
            "granularity": "month",
            "dateRange": time_range
        }]
    response = requests.post(
        f"{CUBE_API}/load",
        headers={"Authorization": f"Bearer {CUBE_TOKEN}"},
        json={"query": query}
    )
    return response.json()["data"]

def test_revenue_metrics_relationship():
    """Net revenue should always be <= gross revenue."""
    result = query_cube(
        ["Orders.netRevenue", "Orders.grossRevenue"],
        time_range=["2024-01-01", "2024-12-31"]
    )
    for row in result:
        assert float(row["Orders.netRevenue"]) <= float(row["Orders.grossRevenue"]), \
            f"Net revenue ({row['Orders.netRevenue']}) exceeds gross revenue ({row['Orders.grossRevenue']}) for {row.get('Orders.orderedAt.month')}"

def test_refund_rate_bounds():
    """Refund rate should be between 0 and 1."""
    result = query_cube(
        ["Orders.refundRate"],
        time_range=["2024-01-01", "2024-12-31"]
    )
    for row in result:
        rate = float(row["Orders.refundRate"])
        assert 0 <= rate <= 1, f"Refund rate {rate} is out of bounds"

def test_aov_consistency():
    """AOV should equal net_revenue / order_count."""
    result = query_cube(
        ["Orders.averageOrderValue", "Orders.netRevenue", "Orders.count"],
        time_range=["2024-01-01", "2024-03-31"]
    )
    for row in result:
        computed_aov = float(row["Orders.netRevenue"]) / float(row["Orders.count"])
        reported_aov = float(row["Orders.averageOrderValue"])
        assert abs(computed_aov - reported_aov) < 0.01, \
            f"AOV inconsistency: computed={computed_aov}, reported={reported_aov}"

---

Hands-On Exercise

Build a semantic layer for a SaaS subscription business. The scenario: you're a data engineer at a B2B SaaS company with 50,000 customers across three subscription tiers (starter, professional, enterprise). You need to implement metrics for monthly recurring revenue (MRR), churn rate, and net revenue retention (NRR).

Setup: Create a new dbt project or use an existing one. You'll need the following source tables (create seed files if you don't have real data):

• subscriptions: one row per subscription-month, with subscription_id, customer_id, mrr_amount, subscription_status, tier, started_at, churned_at
• customers: customer_id, company_name, industry, employee_count_band, contract_start_date

Step 1: Build the foundation mart

Create fct_subscription_months.sql — a model with one row per (subscription, month) combination that captures beginning MRR, ending MRR, new MRR, churned MRR, and expansion MRR for each month.

Step 2: Define the semantic model

Write sem_subscriptions.yml with:

• Entities: subscription (primary), customer (foreign)
• Dimensions: subscription_status, tier, industry, employee_count_band
• Time dimension: month_start_date (grain: month)
• Measures: beginning_mrr, ending_mrr, new_mrr, churned_mrr, expansion_mrr

Step 3: Define the metrics

Create mrr_metrics.yml with these metrics:

• mrr (simple: sum of ending_mrr)
• new_mrr (simple: sum of new_mrr, filter to new subscriptions)
• churned_mrr (simple: sum of churned_mrr, filter to churned subscriptions)
• net_mrr_change (derived: new_mrr + expansion_mrr - churned_mrr)
• logo_churn_rate (ratio: churned subscription count / beginning subscription count)
• net_revenue_retention (derived: ending_mrr / beginning_mrr, for existing customers only)

Step 4: Build the Cube.js schema

Create Subscriptions.js with:

• All measures from step 3
• Pre-aggregations for monthly tier analysis and monthly industry analysis
• A segment for churned subscriptions

Step 5: Write consistency tests

Verify that: (a) beginning MRR + new MRR + expansion MRR - churned MRR = ending MRR for each month, (b) NRR is between 0% and 200% for any given month, (c) logo churn rate is between 0% and 100%.

---

Common Mistakes & Troubleshooting

Mistake 1: Defining Metrics Too Early in the Stack

Symptom: Your semantic layer is full of CASE WHEN logic, COALESCE chains, and business rule implementations.

Problem: Business logic belongs in dbt models, not metric definitions. Metric definitions should reference already-clean columns.

Fix: If you're writing complex SQL inside a metric filter or measure expression, stop and move that logic into a dbt model. Semantic layer definitions should be boring: SUM(net_revenue), not SUM(CASE WHEN is_refunded = false AND status NOT IN ('cancelled', 'fraud', 'test') AND amount > 0 THEN usd_amount * exchange_rate ELSE 0 END).

Mistake 2: Pre-aggregation Cardinality Explosion

Symptom: Your Cube.js pre-aggregations take 4 hours to build and don't seem to be used.

Problem: You've included too many high-cardinality dimensions in a single pre-aggregation. If you have 1,000 days × 50 countries × 10 segments × 8 channels × 5 tiers, that's 20 million rows — for a single pre-agg that probably won't match most queries anyway.

Fix: Split into purpose-specific pre-aggregations. Run CUBEJS_LOG_LEVEL=info and watch the logs to see which pre-agg (if any) is being hit for your common queries.

# Enable pre-agg debugging
CUBEJS_LOG_LEVEL=info npm run dev

# Look for lines like:
# "Using pre-aggregation: dailyRevenue"
# "Pre-aggregation not found, querying raw table"

Mistake 3: Metric Filter Inconsistency Between Tools

Symptom: MetricFlow shows gross_revenue of $1.2M for January, but Cube.js shows $1.3M.

Problem: The filter for excluded order statuses is defined differently in each tool. MetricFlow filters status != 'cancelled', while Cube.js filters status NOT IN ('cancelled', 'test').

Fix: Use the code generation script from Part 3 to keep definitions synchronized. If maintaining both is necessary, add a reconciliation test to your CI pipeline that compares outputs.

Mistake 4: Time Zone Handling

Symptom: Revenue metrics are slightly off at month boundaries. Different BI tools show different totals for "January revenue."

Problem: Your warehouse stores timestamps in UTC, but your users are in different time zones. An order placed at 11 PM Pacific on January 31st is February 1st UTC. If Cube.js converts to user local time and your manual queries don't, you'll see discrepancies.

Fix: Be explicit about time zones everywhere. In Cube.js:

orderedAt: {
  sql: `CONVERT_TIMEZONE('UTC', 'America/New_York', ordered_at)`,
  type: 'time',
  title: 'Order Date (ET)'
}

And document this decision in your metric definitions so users understand which time zone applies.

Mistake 5: Neglecting Metric Documentation

Symptom: Three months after launching your semantic layer, you get a Slack message: "The refund rate metric went from 2% to 8% overnight. Is something broken?"

Problem: Nobody knows what the metric's normal range is, what the filter conditions are, or what would cause a legitimate spike vs. a data quality issue.

Fix: Every metric definition should include: business definition, formula, filter conditions, expected range, common causes of unusual values, and the owning team. Yes, this is more YAML than you want to write. Yes, it will save you hours of incident investigation.

Mistake 6: Treating the Semantic Layer as a Shortcut for Bad Models

Symptom: You have 50 metrics in your semantic layer, but your underlying models aren't tested, aren't documented, and have known data quality issues.

Problem: The semantic layer makes it easy to query metrics, but it doesn't fix bad data. If fct_orders has 5% duplicate order IDs due to a pipeline bug, every metric built on it is wrong — and now it's consistently wrong everywhere.

Fix: The semantic layer should be downstream of rigorously tested, well-understood models. Get your model tests, schema tests, and data freshness checks solid before building the semantic layer on top.

---

Summary & Next Steps

You've built a production-grade semantic layer using both dbt Metrics (MetricFlow) and Cube.js. Let's consolidate what you've learned:

On definition (dbt Metrics):

• Metrics are defined in terms of measures, dimensions, and entities — not SQL expressions
• MetricFlow generates correct SQL for any combination of dimensions and time grains from a single definition
• Metric definitions should live on top of clean, tested dbt models — not replace them
• Version and document every metric as if it's a public API, because for your organization, it is

On serving (Cube.js):

• Cube.js provides the REST/GraphQL API, caching, and pre-aggregation that make interactive analytics feasible
• Pre-aggregation design is about matching your actual query patterns — not materializing everything
• Row-level security lives in the security context and queryRewrite, not in your data models
• Time zone handling must be explicit and consistent

On integration:

• Pick one serving layer per use case — don't run MetricFlow and Cube.js simultaneously unless you have a clear reason and a synchronization mechanism
• Test metric consistency across tools and alert when numbers diverge
• Business logic belongs in dbt transformations, not in semantic layer definitions

Next steps to deepen your understanding:

1. Explore dbt Cloud's native semantic layer — if you're on dbt Cloud, the MetricFlow-powered semantic layer integrates directly with connected BI tools without Cube.js
2. Study Cube Store — Cube.js's native OLAP engine for pre-aggregation storage, which avoids writing back to your primary warehouse
3. Implement metric-driven alerting — use your Cube.js API as the data source for anomaly detection (revenue drops 30%? automated Slack alert before anyone opens a dashboard)
4. Explore LookML vs Cube.js — if you're a Looker shop, understand how LookML's semantic layer compares architecturally and when you'd choose one over the other
5. Governance at scale — learn how to implement metric approval workflows, deprecation processes, and audit trails as your semantic layer grows beyond 20 metrics and multiple teams

The semantic layer you've built here is a living artifact. Its value compounds over time as more consumers rely on it and as you add more metrics — but only if you maintain it with the same rigor you'd apply to a customer-facing API. Treat it that way from day one.