Data Analytics Engineering

Data Analytics Engineering

Scope

• Define metrics, grains, and dimensional models.
• Build transformation layers and semantic models.
• Implement data quality tests and observability.
• Document datasets, lineage, and ownership.
• Align analytics outputs with BI and product needs.

Ask For Inputs

• Business metrics and decision use cases.
• Source systems, data freshness, and latency needs.
• Existing warehouse, tooling, and orchestration.
• Expected data volumes and change cadence.
• Governance requirements and access controls.

Workflow

1. Define metric dictionary and grains. 2. Design staging, intermediate, and mart layers. 3. Model dimensions and facts with clear keys. 4. Build semantic layer and metric definitions. 5. Add tests for freshness, nulls, ranges, and duplicates. 6. Document lineage, owners, and SLAs. 7. Plan rollout, backfills, and validation checks.

Outputs

• Metric dictionary and semantic model.
• Data model with schema and grain definitions.
• Transformation plan and dbt or SQLMesh structure.
• Data quality test suite and alerting plan.
• Documentation and ownership map.

Quality Checks

• Keep metric definitions stable and versioned.
• Treat metrics as APIs: document changes, deprecate safely, and backfill deliberately.
• Define data contracts for core tables (schema, freshness, keys) to control downstream breakage.
• Avoid mixed grains in a single model.
• Ensure tests cover critical joins and aggregates.
• Validate against source of truth and historical baselines.

Templates

• assets/metric-dictionary.md for metric definitions and owners.
• assets/semantic-layer-spec.md for entities, measures, and dimensions.
• assets/data-quality-test-plan.md for test coverage planning.

Resources

• references/modeling-patterns.md for modeling guidance and data quality patterns.
• references/tool-comparison-2026.md for dbt vs SQLMesh vs Coalesce decision matrix.
• references/semantic-layer-patterns.md for semantic layer implementation (Cube, dbt Semantic Layer, AtScale, warehouse-native).
• references/data-quality-testing.md for data quality test strategies, dbt tests, Great Expectations, and alert design.
• references/metric-governance.md for metric lifecycle management, ownership models, deprecation policies, and metric debt prevention.
• data/sources.json for curated vendor docs and trend-tracking sources (use as a WebSearch seed list).

Related Skills

• Use data-lake-platform for platform architecture.
• Use data-sql-optimization for query tuning.
• Use ai-ml-data-science for modeling and experiments.

---

Trend Awareness Protocol

IMPORTANT: When users ask recommendation questions about analytics engineering, data modeling, or BI, you MUST use WebSearch to check current trends before answering. If WebSearch is unavailable, use data/sources.json + web browsing and state what you verified vs assumed.

Trigger Conditions

• "What's the best tool for [analytics engineering/data modeling/BI]?"
• "What should I use for [transformation/semantic layer/metrics]?"
• "What's the latest in analytics engineering?"
• "Current best practices for [dbt/metrics layers/data quality]?"
• "Is [tool/approach] still relevant in 2026?"
• "[dbt] vs [SQLMesh] vs [other]?"
• "Best BI tool for [use case]?"
• "SQLMesh acquisition" or "Fivetran transformation"
• "Agentic analytics" or "AI data workflows"
• "Metric debt" or "metric governance"

Required Searches

1. Search: "analytics engineering best practices 2026" 2. Search: "[dbt/SQLMesh/semantic layer] vs alternatives 2026" 3. Search: "analytics engineering trends January 2026" 4. Search: "[specific tool] new releases 2026" 5. Search: "agentic analytics AI data 2026" (for AI-related queries)

What to Report

After searching, provide:

• Current landscape: What analytics tools/patterns are popular NOW
• Emerging trends: New tools, patterns, or standards gaining traction
• Deprecated/declining: Tools/approaches losing relevance or support
• Recommendation: Based on fresh data, not just static knowledge

Example Topics (verify with fresh search)

• Transformation tools (dbt, SQLMesh, Coalesce)
• Semantic layers (dbt Semantic Layer, Cube, AtScale, warehouse-native)
• Metrics stores and headless BI
• Data quality tools (dbt tests, Elementary, dbt-expectations/Metaplane)
• BI platforms (Metabase, Superset, Lightdash, Hex)
• Data modeling patterns (dimensional, wide tables, activity schema)
• Analytics engineering workflows and CI/CD
• Agentic AI workflows for analytics
• Data mesh and domain-owned data products

Fact-Checking

• Use web search/web fetch to verify current external facts, versions, pricing, deadlines, regulations, or platform behavior before final answers.
• Prefer primary sources; report source links and dates for volatile information.
• If web access is unavailable, state the limitation and mark guidance as unverified.

Data Quality Test Plan

Tests

Monitoring

• Freshness SLA:
• Alerts:
• Runbook:

Metric Dictionary

Semantic Layer Spec

Entities

• Entity:
• Primary key:

Measures

Dimensions

Data Quality Testing for Analytics Pipelines

Purpose: Operational reference for implementing automated data quality testing across analytics pipelines — dbt tests, Great Expectations, anomaly detection, and coverage metrics. Freshness anchor: Q1 2026.

---

Decision Tree: Choosing a Testing Approach

START: What is your transformation layer?
│
├─ dbt
│   │
│   ├─ Need basic schema validation?
│   │   └─ YES → dbt built-in tests (not_null, unique, accepted_values, relationships)
│   │
│   ├─ Need statistical or complex tests?
│   │   └─ YES → dbt-expectations package
│   │
│   ├─ Need anomaly detection over time?
│   │   └─ YES → Elementary anomaly monitors
│   │
│   └─ Need cross-database or source validation?
│       └─ YES → dbt + Great Expectations via external tests
│
├─ Spark / Python pipelines
│   └─ Great Expectations or Soda Core
│
└─ Warehouse-native (no orchestrator)
    └─ Scheduled SQL checks + alerting (Monte Carlo, Datafold)

---

Quick Reference: Testing Tool Comparison (2026)

---

dbt Built-In Tests

Schema Tests (YAML-configured)

# models/staging/stg_orders.yml
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'completed', 'cancelled', 'refunded']
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: amount
        tests:
          - not_null
      - name: created_at
        tests:
          - not_null

Custom Data Tests (SQL)

-- tests/assert_revenue_not_negative.sql
-- Fails if any completed order has negative revenue
SELECT
  order_id,
  amount
FROM {{ ref('fct_orders') }}
WHERE status = 'completed'
  AND amount < 0

-- tests/assert_daily_order_volume.sql
-- Fails if any day has zero orders (data gap detection)
WITH date_spine AS (
  SELECT date_day
  FROM {{ ref('dim_dates') }}
  WHERE date_day >= '2025-01-01'
    AND date_day < CURRENT_DATE
),
daily_orders AS (
  SELECT
    DATE_TRUNC('day', created_at) AS order_date,
    COUNT(*) AS order_count
  FROM {{ ref('fct_orders') }}
  GROUP BY 1
)
SELECT d.date_day
FROM date_spine d
LEFT JOIN daily_orders o ON d.date_day = o.order_date
WHERE o.order_count IS NULL
  AND EXTRACT(DOW FROM d.date_day) NOT IN (0, 6) -- exclude weekends if applicable

Test Severity Configuration

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique:
              severity: error  # blocks pipeline
          - not_null:
              severity: error
      - name: discount_code
        tests:
          - not_null:
              severity: warn  # logs warning, pipeline continues

---

dbt-expectations Package

Installation

# packages.yml
packages:
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.12.0"]

Common Expectations

models:
  - name: fct_orders
    tests:
      # Row count within expected range
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1000
          max_value: 1000000

      # No duplicate compound keys
      - dbt_expectations.expect_compound_columns_to_be_unique:
          column_list: ["order_id", "line_item_id"]

    columns:
      - name: email
        tests:
          # Regex pattern match
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$"

      - name: amount
        tests:
          # Value range check
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000
              mostly: 0.99  # 99% of values must pass

          # Distribution check
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 20
              max_value: 200

      - name: created_at
        tests:
          # Freshness check
          - dbt_expectations.expect_row_values_to_have_recent_data:
              datepart: day
              interval: 1

---

Elementary Anomaly Detection

Installation and Setup

# packages.yml
packages:
  - package: elementary-data/elementary
    version: [">=0.14.0", "<0.16.0"]

Anomaly Monitor Configuration

models:
  - name: fct_orders
    tests:
      # Volume anomaly — detect unusual row count changes
      - elementary.volume_anomaly:
          timestamp_column: created_at
          time_bucket:
            period: day
            count: 1
          training_period:
            period: day
            count: 30
          sensitivity: 3  # standard deviations

      # Freshness anomaly — detect late-arriving data
      - elementary.freshness_anomaly:
          timestamp_column: created_at
          sensitivity: 3

    columns:
      - name: amount
        tests:
          # Column-level anomaly
          - elementary.column_anomalies:
              timestamp_column: created_at
              column_anomalies:
                - zero_count
                - null_count
                - average
                - standard_deviation

      - name: status
        tests:
          # Distribution shift detection
          - elementary.all_columns_anomalies:
              timestamp_column: created_at

---

Great Expectations Integration

Use When

• Python/Spark pipelines (non-dbt)
• Need profiling-driven test generation
• Cross-database validation required
• Complex statistical tests beyond dbt capabilities

Checkpoint Configuration

# great_expectations/checkpoints/orders_checkpoint.yml
name: orders_checkpoint
config_version: 1.0
class_name: Checkpoint
run_name_template: "orders_validation_%Y%m%d"
validations:
  - batch_request:
      datasource_name: warehouse
      data_asset_name: fct_orders
    expectation_suite_name: orders_suite
    action_list:
      - name: store_validation_result
        action:
          class_name: StoreValidationResultAction
      - name: send_slack_notification
        action:
          class_name: SlackNotificationAction
          slack_webhook: ${SLACK_WEBHOOK}
          notify_on: failure

Common Expectations

suite = context.add_expectation_suite("orders_suite")
suite.add_expectation(gx.expectations.ExpectTableRowCountToBeBetween(min_value=1000))
suite.add_expectation(gx.expectations.ExpectColumnValuesToNotBeNull(column="order_id"))
suite.add_expectation(gx.expectations.ExpectColumnValuesToBeUnique(column="order_id"))
suite.add_expectation(gx.expectations.ExpectColumnValuesToBeBetween(
    column="amount", min_value=0, max_value=100000, mostly=0.99))
suite.add_expectation(gx.expectations.ExpectColumnValuesToBeInSet(
    column="status", value_set=["pending", "completed", "cancelled", "refunded"]))

---

Freshness Monitoring

dbt Source Freshness

# models/staging/sources.yml
sources:
  - name: raw_orders
    database: raw
    schema: ecommerce
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 12, period: hour}
    loaded_at_field: _loaded_at
    tables:
      - name: orders
        freshness:
          warn_after: {count: 2, period: hour}
          error_after: {count: 4, period: hour}
      - name: order_items
      - name: customers
        freshness:
          warn_after: {count: 24, period: hour}
          error_after: {count: 48, period: hour}

# Run freshness check
dbt source freshness --select source:raw_orders

---

Test Coverage Metrics

Coverage Targets

CI/CD Integration Checklist

• [ ] Run dbt test on every PR that modifies models
• [ ] Fail CI if test coverage drops below threshold
• [ ] Run dbt source freshness in scheduled pipeline
• [ ] Post test results to Slack/Teams on failure
• [ ] Store test history for trend analysis
• [ ] Run anomaly monitors on schedule (not just on PR)
• [ ] Include data diff (Datafold) for mart-layer changes

---

Test-Driven Analytics Development

Workflow

1. Write failing test (expected behavior of new model) 2. Build model to pass the test 3. Add edge case tests 4. Validate against stakeholder expectations 5. Deploy with full test suite

Example: TDD for Revenue Metric

• Step 1: Write tests FIRST — not_null, row_count > 0, values_between min:0, recent_data
• Step 2: Build model to pass the tests
• Step 3: Add edge case tests (zero revenue days, refunds exceeding orders)
• Step 4: Validate against stakeholder expectations
• Step 5: Deploy with full test suite

---

Anti-Patterns

---

Cross-References

• semantic-layer-patterns.md — Semantic models depend on tested upstream models
• metric-governance.md — Metric validation tests and certification process
• data-quality-patterns.md — Broader data quality framework for lake platforms
• monitoring-alerting-patterns.md — Alerting infrastructure for test failures

---

Last updated: 2026-02-10 | Next review: 2026-05-10

Metric Governance

Purpose: Operational framework for managing the full lifecycle of business metrics — definition standards, ownership, versioning, certification, deprecation, and conflict resolution. Freshness anchor: Q1 2026.

---

Decision Tree: Is This Metric Ready for Production?

START: New metric request received
│
├─ Does a canonical metric already exist for this concept?
│   │
│   ├─ YES → Can the existing metric be reused or filtered?
│   │   │
│   │   ├─ YES → Use existing metric with dimension filter
│   │   │        (do NOT create a duplicate)
│   │   │
│   │   └─ NO → Document why existing metric is insufficient
│   │            → Propose modification or new derived metric
│   │            → Route through metric review process
│   │
│   └─ NO → Continue to definition phase ↓
│
├─ Has the metric been defined with all required fields?
│   │
│   ├─ NO → Complete metric definition template (see below)
│   │
│   └─ YES → Has the metric been validated against known values?
│       │
│       ├─ NO → Run validation tests against existing reports
│       │
│       └─ YES → Has the metric been reviewed by domain owner?
│           │
│           ├─ NO → Submit for peer review
│           │
│           └─ YES → CERTIFY and publish to metric catalog

---

Metric Definition Template

Required Fields

Optional Fields

---

Naming Standards

Convention Rules

Dimension Naming

---

Ownership Assignment

RACI Matrix for Metrics

• R = Responsible, A = Accountable, C = Consulted, I = Informed

Ownership Transfer Checklist

• [ ] New owner acknowledges responsibility
• [ ] Transfer documented in metric catalog
• [ ] Alert/monitor subscriptions updated
• [ ] Stakeholder notification sent
• [ ] Knowledge transfer session completed
• [ ] Access permissions updated

---

Versioning Protocol

Semantic Versioning for Metrics

Version Change Workflow

1. Propose: PR with metric definition changes + migration notes 2. Validate: Run comparison tests (old vs new values) 3. Review: Domain owner approves 4. Document: Changelog entry with before/after values 5. Deploy: Update semantic layer 6. Communicate: Notify consumers via standard channel 7. Deprecate old: Follow deprecation workflow if major version

Changelog Format

## monthly_recurring_revenue v3.0.0 (2026-02-10)

### Breaking Change
- Excluded trial subscriptions from MRR calculation
- Previous: included all subscriptions with status IN ('active', 'trial')
- New: includes only subscriptions with status = 'active'

### Impact
- MRR will decrease by approximately 5-8%
- Affected dashboards: Executive Summary, Board Deck, Investor Report
- Migration: filter `is_trial = false` achieves equivalent result on v2.x

### Validation
- Compared v2.x vs v3.0.0 for last 12 months
- Diff report: [link to validation]

---

Certification Process

Certification Levels

Certification Checklist

• [ ] Business definition reviewed by stakeholder
• [ ] Implementation matches business definition exactly
• [ ] Tests cover: not_null, range, freshness, anomaly detection
• [ ] Values validated against independent source (finance system, CRM)
• [ ] Owner assigned and acknowledged
• [ ] SLO defined (freshness, accuracy tolerance)
• [ ] Listed in metric catalog with all required fields
• [ ] At least one dashboard uses this metric correctly
• [ ] No conflicting definitions exist in the catalog
• [ ] Quarterly review scheduled

Decertification Triggers

• Test failures unresolved for >48 hours
• Owner departed without transfer
• Source model deprecated
• Values diverge from source of truth by >2%
• No consumer usage for >90 days

---

Deprecation Workflow

Timeline

Deprecation Notice Template

# In metric definition
deprecated: true
deprecated_at: "2026-02-10"
deprecated_reason: "Replaced by net_revenue_v2 which excludes test accounts"
replacement_metric: net_revenue_v2
migration_guide: |
  Replace `net_revenue` with `net_revenue_v2`.
  If you need the old behavior (including test accounts),
  use `net_revenue_v2` with filter `include_test_accounts = true`.
sunset_date: "2026-05-10"

---

Conflict Resolution

Common Conflicts

Conflict Resolution Process

1. Identify: Document both definitions with exact SQL/logic 2. Compare: Run both calculations side-by-side for 90-day window 3. Adjudicate: Domain owner or data council decides canonical version 4. Resolve: Losing definition becomes either deprecated or renamed variant 5. Communicate: Announce resolution with rationale to all consumers

---

Metric Catalog Requirements

Minimum Viable Catalog

Catalog Tools (2026)

---

Stakeholder Communication Patterns

Communication Matrix

---

Anti-Patterns

---

Cross-References

• semantic-layer-patterns.md — Technical implementation of governed metric definitions
• data-quality-testing.md — Test suites that validate metric correctness
• data-mesh-patterns.md — Domain ownership model for metric governance
• permissions-collections.md — Access control for metric consumption

---

Last updated: 2026-02-10 | Next review: 2026-05-10

Analytics Modeling Patterns

Core Principles

• Define grain before writing transformations
• Prefer star schema for BI and reporting
• Separate staging, intermediate, and marts
• Keep metric definitions stable and versioned
• Validate joins with row count checks

Layer Architecture

Three-Layer Pattern

Staging Layer Rules

• One model per source table
• Column renaming only (no joins)
• Type casting and null handling
• No business logic

Intermediate Layer Rules

• Complex joins and window functions
• Deduplication logic
• Business calculations
• Can reference other intermediate models

Marts Layer Rules

• Star schema design (facts + dimensions)
• Wide tables for specific use cases
• Aggregations and final metrics
• Optimized for BI tool consumption

Dimensional Modeling

Fact Tables

-- fct_orders: grain = one row per order line item
SELECT
    order_line_id,           -- degenerate dimension
    order_id,                -- degenerate dimension
    customer_key,            -- FK to dim_customers
    product_key,             -- FK to dim_products
    order_date_key,          -- FK to dim_dates
    quantity,                -- measure
    unit_price,              -- measure
    discount_amount,         -- measure
    net_revenue              -- calculated measure
FROM {{ ref('int_orders_enriched') }}

Dimension Tables

-- dim_customers: SCD Type 2 for tracking changes
SELECT
    customer_key,            -- surrogate key
    customer_id,             -- natural key
    customer_name,
    segment,
    region,
    valid_from,
    valid_to,
    is_current
FROM {{ ref('int_customers_history') }}

Slowly Changing Dimensions

Metric Definition Patterns

MetricFlow Syntax (dbt Semantic Layer)

semantic_models:
  - name: orders
    defaults:
      agg_time_dimension: order_date
    entities:
      - name: order_id
        type: primary
      - name: customer_id
        type: foreign
    measures:
      - name: order_total
        agg: sum
        expr: amount
      - name: order_count
        agg: count
        expr: order_id
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: order_status
        type: categorical

metrics:
  - name: revenue
    type: simple
    type_params:
      measure: order_total
  - name: average_order_value
    type: derived
    type_params:
      expr: revenue / order_count

Metric Governance Rules

• One canonical definition per metric
• Version metrics with semantic versioning
• Document calculation logic inline
• Include grain and time spine requirements
• Specify valid dimensions for each metric

Data Quality Testing

Test Coverage Matrix

dbt-expectations Examples

# Updated: dbt-expectations now maintained by Metaplane
# Install: pip install dbt-expectations (Metaplane fork)

models:
  - name: fct_orders
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1000
          max_value: 10000000
      - dbt_expectations.expect_column_values_to_be_between:
          column_name: order_total
          min_value: 0
          max_value: 1000000
      - dbt_expectations.expect_column_values_to_match_regex:
          column_name: order_id
          regex: "^ORD-[0-9]{8}$"

Elementary Anomaly Detection

# Elementary provides ML-based anomaly detection
models:
  - name: fct_orders
    meta:
      elementary:
        timestamp_column: created_at
    tests:
      - elementary.volume_anomalies:
          timestamp_column: created_at
          where: "order_status = 'completed'"
      - elementary.freshness_anomalies:
          timestamp_column: updated_at

State Management Patterns

dbt (Stateless)

• Relies on incremental flags and manifests
• Add-ons like dbt_artifacts for change tracking
• Full refresh on schema changes
• CI validation with state comparison

SQLMesh (Stateful)

• Built-in state tracking
• Virtual dev environments
• Only changed tables rebuilt
• Terraform-like state management
• Can reduce rebuild work by tracking change impact (validate in your environment)

When to Choose Each

Anti-Patterns to Avoid

Mixed Grains

-- BAD: mixing order-level and line-item-level
SELECT
    order_id,
    line_item_id,        -- line-item grain
    order_total,         -- order grain (will duplicate)
    line_item_amount
FROM orders
JOIN line_items USING (order_id)

Metric Drift

• Multiple definitions of "revenue" across teams
• Undocumented filters in metric calculations
• No versioning on metric changes
• Solution: Implement semantic layer with single source of truth

Missing Lineage

• No documentation of upstream dependencies
• Unclear data freshness expectations
• No ownership assignment
• Solution: Use OpenLineage + DataHub for metadata

Related Resources

• Tool Comparison 2026 - dbt vs SQLMesh vs Coalesce
• Data Quality Test Plan - Test coverage template
• Metric Dictionary - Metric definition template

Semantic Layer Patterns

Purpose: Operational guide for choosing, implementing, and governing semantic layers that translate raw warehouse tables into governed, reusable metric definitions. Freshness anchor: Q1 2026.

---

Decision Tree: Choosing a Semantic Layer Approach

START: Do you need governed, reusable metrics across multiple consumers?
│
├─ NO → Ad-hoc SQL with documented conventions is sufficient
│
├─ YES → Are you already using dbt as your transformation layer?
│   │
│   ├─ YES → Is your team >5 analysts querying independently?
│   │   │
│   │   ├─ YES → dbt Semantic Layer + MetricFlow
│   │   │        (native integration, single source of truth)
│   │   │
│   │   └─ NO → dbt metrics with exposures
│   │            (lighter lift, still governed)
│   │
│   └─ NO → Do you need sub-second query latency for dashboards?
│       │
│       ├─ YES → Cube (pre-aggregation + caching layer)
│       │
│       └─ NO → Do you have a modern warehouse with native semantics?
│           │
│           ├─ YES (BigQuery, Databricks) → Warehouse-native semantic layer
│           │
│           └─ NO → Cube or LookML depending on existing stack

---

Quick Reference: Semantic Layer Tools (2026)

---

Core Concepts

Metric Definition Components

• Measures: Aggregations applied to columns (SUM, COUNT, AVG, COUNT_DISTINCT)
• Dimensions: Attributes for grouping and filtering (categorical, time-based)
• Entities: Join keys that connect semantic models (user_id, order_id)
• Filters: Default or required constraints applied to metrics
• Grain: The lowest level of detail a metric supports

Metric Types

---

Operational Patterns

Pattern 1: MetricFlow in dbt (dbt Semantic Layer)

• Use when: Already on dbt Cloud, team queries metrics via downstream tools
• Implementation:

# models/semantic/sem_orders.yml
semantic_models:
  - name: orders
    defaults:
      agg_time_dimension: order_date
    model: ref('fct_orders')
    entities:
      - name: order_id
        type: primary
      - name: customer_id
        type: foreign
    measures:
      - name: order_total
        agg: sum
        expr: amount
      - name: order_count
        agg: count
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: order_status
        type: categorical

# models/semantic/metrics.yml
metrics:
  - name: revenue
    type: simple
    type_params:
      measure: order_total
    filter: |
      {{ Dimension('order__order_status') }} = 'completed'

  - name: average_order_value
    type: derived
    type_params:
      expr: revenue / order_count
      metrics:
        - name: revenue
        - name: order_count

Pattern 2: Cube Pre-Aggregation Layer

• Use when: Need sub-second dashboards, multiple data sources, or headless BI
• Implementation:

// schema/Orders.js
cube('Orders', {
  sql: `SELECT * FROM public.fct_orders`,

  preAggregations: {
    ordersByDay: {
      measures: [Orders.totalRevenue, Orders.count],
      dimensions: [Orders.status],
      timeDimension: Orders.createdAt,
      granularity: 'day',
      refreshKey: {
        every: '1 hour'
      }
    }
  },

  measures: {
    count: { type: 'count' },
    totalRevenue: {
      sql: 'amount',
      type: 'sum',
      format: 'currency'
    },
    averageOrderValue: {
      sql: `${totalRevenue} / NULLIF(${count}, 0)`,
      type: 'number',
      format: 'currency'
    }
  },

  dimensions: {
    status: { sql: 'status', type: 'string' },
    createdAt: { sql: 'created_at', type: 'time' }
  }
});

Pattern 3: Warehouse-Native Semantics (BigQuery)

• Use when: Fully on BigQuery, want zero additional infrastructure
• Implementation:

-- Create authorized view as semantic interface
CREATE OR REPLACE VIEW `project.analytics.v_revenue_metrics` AS
SELECT
  DATE_TRUNC(order_date, DAY) AS metric_date,
  region,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS revenue,
  COUNT(DISTINCT order_id) AS order_count,
  SAFE_DIVIDE(
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END),
    COUNT(DISTINCT order_id)
  ) AS average_order_value
FROM `project.warehouse.fct_orders`
GROUP BY 1, 2;

-- Materialize for performance
CREATE MATERIALIZED VIEW `project.analytics.mv_revenue_daily`
AS SELECT * FROM `project.analytics.v_revenue_metrics`;

Pattern 4: Headless BI via API

• Use when: Metrics consumed by applications, notebooks, Slack bots — not just dashboards
• Components:
• Semantic layer as query engine (Cube, MetricFlow)
• REST or GraphQL API for programmatic access
• SDK for application embedding
• Cache layer for repeated queries

# Cube REST API query example
curl -X POST http://localhost:4000/cubejs-api/v1/load \
  -H "Authorization: Bearer ${CUBE_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{
    "measures": ["Orders.totalRevenue"],
    "dimensions": ["Orders.status"],
    "timeDimensions": [{
      "dimension": "Orders.createdAt",
      "granularity": "month",
      "dateRange": ["2026-01-01", "2026-03-31"]
    }]
  }'

---

Caching Strategies

Cache Invalidation Checklist

• [ ] Define refresh schedule aligned with data loading cadence
• [ ] Set TTL shorter than data freshness SLO
• [ ] Implement cache warming for critical dashboards
• [ ] Monitor cache hit ratio (target >80% for production)
• [ ] Add manual invalidation endpoint for emergency refreshes
• [ ] Log cache misses for capacity planning

---

Migration: Ad-Hoc SQL to Governed Metrics

Phase 1: Inventory (Week 1-2)

• [ ] Audit existing dashboards for metric definitions
• [ ] Catalog all SQL containing business logic (CASE, SUM, filters)
• [ ] Identify conflicting definitions for same metric name
• [ ] Map metric consumers (dashboards, reports, notebooks, apps)

Phase 2: Define (Week 3-4)

• [ ] Establish naming convention (see metric-governance.md)
• [ ] Write semantic model YAML for top 10 metrics
• [ ] Validate metric outputs match existing dashboard values
• [ ] Get stakeholder sign-off on canonical definitions

Phase 3: Implement (Week 5-8)

• [ ] Deploy semantic layer infrastructure
• [ ] Migrate top 10 metrics to semantic definitions
• [ ] Connect one downstream consumer as pilot
• [ ] Set up monitoring for query latency and correctness

Phase 4: Govern (Week 9+)

• [ ] Deprecate ad-hoc SQL versions with migration guide
• [ ] Enforce semantic layer for new metric requests
• [ ] Add CI checks for metric definition changes
• [ ] Publish metric catalog to stakeholders

---

Anti-Patterns

---

Cross-References

• metric-governance.md — Naming, versioning, and deprecation protocols for metrics
• data-quality-testing.md — Testing strategies for the models underlying semantic layers
• data-mesh-patterns.md — Domain ownership model that feeds semantic layer boundaries
• monitoring-alerting-patterns.md — Alerting on semantic layer query latency and errors

---

Last updated: 2026-02-10 | Next review: 2026-05-10

Analytics Engineering Tools Comparison (2026)

Transformation Tools

Quick Decision Matrix

dbt (data build tool)

Strengths:

• Industry standard with massive ecosystem
• dbt Cloud provides IDE, scheduling, lineage
• Rich package ecosystem (dbt-utils, dbt-expectations)
• Strong community and documentation

Weaknesses:

• Stateless architecture requires workarounds
• Jinja templating errors caught at runtime only
• Full refresh on schema changes
• Performance at scale can be challenging

2026 Updates:

• Semantic Layer with MetricFlow now production-ready
• AI/LLM integrations via vendor tooling or query APIs (verify current options)
• Enhanced CI/CD with state comparison

SQLMesh

Strengths:

• Built-in state tracking (only changed tables rebuild)
• Virtual dev environments
• SQLGlot parser catches errors at compile time
• Column-level lineage automatic
• Plan-based deploys and rollbacks (validate feature set per release)

Weaknesses:

• Smaller ecosystem than dbt
• Less mature tooling around it

2026 Updates:

• Active development; verify current ownership/roadmap
• Improving integrations and enterprise adoption

Coalesce

Strengths:

• Visual, column-aware workspace
• Metadata-driven development
• Full column and object-level lineage
• Impact analysis before changes
• Faster onboarding for new engineers

Weaknesses:

• Higher cost than open-source alternatives
• Smaller community
• Less flexibility for complex custom logic

2026 Updates:

• AI-readiness features
• Enhanced governance controls
• Multi-cloud support (Snowflake, Databricks, BigQuery, Fabric)

When to Choose Each

Choose dbt when:
├── You have existing dbt investment
├── You need dbt Cloud features (hosted IDE, RBAC)
├── Your team knows SQL + Jinja
└── You want largest ecosystem and community

Choose SQLMesh when:
├── Performance is critical (large transformations)
├── You're using Fivetran for ingestion
├── You want stateful development experience
└── You need compile-time validation

Choose Coalesce when:
├── Your team prefers visual development
├── You need enterprise governance features
├── Onboarding speed is important
└── You have budget for premium tooling

Semantic Layer Tools

Quick Decision Matrix

dbt Semantic Layer (MetricFlow)

Architecture: Push-down to warehouse

How it works:

• Define metrics in YAML alongside dbt models
• MetricFlow rewrites queries for optimization
• Executes on your warehouse (Snowflake, BigQuery, etc.)
• No separate caching layer

Best for:

• Teams already using dbt
• Multi-cloud environments (vendor independence)
• Teams wanting metric definitions close to transformation code

Notes:

• If you need product-grade APIs and caching, compare against Cube or warehouse-native options
• For AI/LLM analytics, validate how your semantic layer integrates with your chosen LLM/BI tooling

Cube

Architecture: Caching layer with pre-aggregations

How it works:

• Define semantic model in JavaScript/YAML
• Cube generates and caches pre-aggregations
• Sub-second queries (50-500ms)
• REST/GraphQL/Postgres-compatible APIs

Best for:

• Embedded analytics in products
• High-concurrency workloads
• API-first development
• Multi-tenant applications

Key features:

• Headless BI leader
• Strong security model for multi-tenancy
• Developer-friendly APIs

AtScale

Architecture: Enterprise semantic layer

How it works:

• Full MDX support for Excel/Power BI
• Enterprise-scale governance
• Connects to multiple data sources

Best for:

• Large enterprises with Excel-heavy culture
• Organizations needing MDX compatibility
• Complex governance requirements

Emerging: Warehouse-Native Semantic Layers

Snowflake Semantic Views:

• Native to Snowflake
• SQL-based definitions
• Integrated with Snowflake governance

Databricks Metric Views:

• Unity Catalog integration
• Native to Databricks lakehouse
• SQL-based

When to choose warehouse-native:

• Single-cloud commitment
• Want simplest possible architecture
• Native governance is priority

Data Quality Tools

Quick Decision Matrix

dbt Tests (Native)

Built-in tests:

• unique - No duplicate values
• not_null - No null values
• accepted_values - Value in allowed list
• relationships - Foreign key validity

Use for: Basic data validation in every project

dbt-expectations

Note: As of December 2024, actively maintained by Metaplane (fork)

Key tests:

• expect_table_row_count_to_be_between
• expect_column_values_to_be_between
• expect_column_values_to_match_regex
• expect_compound_columns_to_be_unique

Use for: Complex assertions without custom SQL

Elementary

Key features:

• ML-based anomaly detection
• Volume anomalies
• Freshness anomalies
• Schema change detection
• Visual dashboard for test results

Use for: Proactive monitoring beyond static thresholds

Great Expectations

Key features:

• Python-native validation
• Rich expectation library
• Data docs generation
• CI/CD integration

Use for: Non-dbt pipelines, Python-heavy teams

2026 Trend: AI Increases Blast Radius

Recommendation: Layer multiple tools: 1. dbt tests for baseline validation 2. dbt-expectations for complex rules 3. Elementary for anomaly detection

Data Catalog and Lineage

Quick Decision Matrix

OpenLineage

What it is: Open standard for data lineage

Use for:

• Standardizing lineage across tools
• Integration with Airflow, Spark, dbt
• Building custom lineage solutions

DataHub

What it is: Self-hosted metadata catalog

Key features:

• Dataset discovery
• Ownership management
• Lineage visualization
• Glossary and tags

Use for: Organizations wanting open-source catalog

Interoperability Watchlist

Some vendors and open standards aim to improve portability between semantic layers and metric stores. Verify current adoption and compatibility before betting on portability.

Watch for:

• Metric definition portability
• Cross-tool semantic model sharing
• Vendor-neutral metric stores

Recommendation Framework

For New Projects (2026)

Small team, budget-conscious:
├── Transformation: dbt Core
├── Semantic: dbt Semantic Layer (if dbt Cloud) or Cube
├── Quality: dbt tests + Elementary
└── Catalog: DataHub (self-hosted)

Mid-size team, performance-focused:
├── Transformation: SQLMesh (especially if Fivetran user)
├── Semantic: dbt Semantic Layer or Cube
├── Quality: dbt tests + dbt-expectations + Elementary
└── Catalog: DataHub or Atlan

Enterprise, governance-focused:
├── Transformation: Coalesce or dbt Cloud Enterprise
├── Semantic: AtScale or dbt Semantic Layer
├── Quality: Full stack (dbt + Elementary + Monte Carlo)
└── Catalog: Atlan or Collibra

Migration Paths

dbt Core → SQLMesh:

• SQLMesh supports dbt project import
• Gradual migration possible
• Keep dbt for Cloud features, SQLMesh for execution

dbt Core → Coalesce:

• Full project migration required
• Good for teams wanting visual development
• Higher cost, lower complexity

Related Resources

• Modeling Patterns - Data modeling best practices
• Semantic Layer Spec - Semantic model template
• Data Quality Test Plan - Test coverage planning