analytics-engineering

When this skill is activated, always start your first response with the 🧢 emoji.

Analytics Engineering

A disciplined framework for building trustworthy, well-tested data transformation pipelines using dbt and modern analytics engineering practices. This skill covers dbt model layering, semantic layer design, metrics definitions, dimensional modeling, and self-serve analytics patterns. It is opinionated about dbt Core/Cloud but the modeling principles apply to any SQL-based transformation tool. The goal is to help you build a data warehouse that analysts can trust and navigate without engineering support.

When to use this skill

Trigger this skill when the user:

• Sets up a new dbt project or restructures an existing one
• Designs the model layer hierarchy (staging, intermediate, marts)
• Writes or reviews dbt models using ref(), source(), or macros
• Defines metrics in YAML (dbt Metrics, MetricFlow, or Cube)
• Builds a semantic layer for self-serve analytics
• Implements slowly changing dimensions (SCD Type 1, 2, 3)
• Writes dbt tests (generic, singular, or custom) and data contracts
• Configures sources, exposures, or freshness checks
• Asks about dimensional modeling (star schema, snowflake schema, OBT)

Do NOT trigger this skill for:

• Data pipeline orchestration (Airflow, Dagster) unrelated to dbt models
• Raw data ingestion or ELT tool configuration (Fivetran, Airbyte connectors)

Key principles

1. Layer your models deliberately - Use a three-layer architecture: staging

(1:1 with source tables, rename and cast only), intermediate (business logic joins and filters), and marts (wide, denormalized tables ready for analysts). Every model lives in exactly one layer. No skipping layers.

1. One source of truth per grain - Each mart model must have a clearly defined

grain (one row = one what?). Document it in the YAML schema. If two mart models have the same grain, one of them should not exist.

1. Test everything that matters, nothing that doesn't - Test primary keys with

unique and not_null. Test foreign keys with relationships. Test business rules with custom singular tests. Do not write tests that duplicate what the warehouse already enforces.

1. Metrics are code, not queries - Define metrics in version-controlled YAML,

not in BI tool calculated fields. This ensures a single definition that every consumer (dashboard, ad-hoc query, API) shares. Disagreements about numbers end when metric definitions are in the repo.

1. Build for self-serve, not for tickets - Every mart should be understandable

by a non-engineer. Use clear column names (no abbreviations), add descriptions to every column in the YAML schema, and expose models as documented datasets in the BI tool. If analysts file tickets asking what a column means, the model is incomplete.

Core concepts

Model layer architecture

| Layer | Prefix | Purpose | Example | |---|---|---|---| | Staging | stg_ | 1:1 with source, rename + cast + basic cleaning | stg_stripe__payments | | Intermediate | int_ | Business logic, joins across staging models | int_orders__pivoted_payments | | Marts | fct_ / dim_ | Analyst-facing, denormalized, documented | fct_orders, dim_customers |

Staging models should be views (no materialization cost). Intermediate models are tables or ephemeral depending on reuse. Marts are always tables (or incremental).

Dimensional modeling

Fact tables (fct_) contain measurable events at a specific grain - orders, payments, page views. They hold foreign keys to dimension tables and numeric measures.

Dimension tables (dim_) contain descriptive attributes - customers, products, dates. They provide the "who, what, where, when" context for facts.

One Big Table (OBT) is a pre-joined wide table combining facts and dimensions. Use OBT for BI tools that perform poorly with joins. It trades storage for query simplicity.

The semantic layer

A semantic layer sits between the data warehouse and consumers (BI tools, notebooks, APIs). It defines metrics, dimensions, and entities in a declarative format so that every consumer gets the same answers. dbt's MetricFlow, Cube, and Looker's LookML are implementations of this pattern. The semantic layer eliminates "which number is right?" debates by making metric logic authoritative and centralized.

Incremental models

For large fact tables, use dbt incremental models to process only new/changed rows instead of rebuilding the entire table. The is_incremental() macro gates the WHERE clause to filter for rows since the last run. Always define a unique_key to handle late-arriving or updated records via merge behavior.

Common tasks

Set up dbt project structure

my_project/
  dbt_project.yml
  models/
    staging/
      stripe/
        _stripe__models.yml    # source + model definitions
        _stripe__sources.yml   # source freshness config
        stg_stripe__payments.sql
        stg_stripe__customers.sql
      shopify/
        _shopify__models.yml
        _shopify__sources.yml
        stg_shopify__orders.sql
    intermediate/
      int_orders__pivoted_payments.sql
    marts/
      finance/
        _finance__models.yml
        fct_orders.sql
        dim_customers.sql
      marketing/
        _marketing__models.yml
        fct_ad_spend.sql
  tests/
    singular/
      assert_order_total_positive.sql
  macros/
    cents_to_dollars.sql

Use underscores for filenames, double underscores to separate source system from

entity (e.g. stg_stripe__payments). Group staging models by source system, marts

by business domain.

Write a staging model

Staging models rename, cast, and apply minimal cleaning. No joins, no business logic.

-- models/staging/stripe/stg_stripe__payments.sql
with source as (
    select * from {{ source('stripe', 'payments') }}
),

renamed as (
    select
        id as payment_id,
        order_id,
        cast(amount as integer) as amount_cents,
        cast(created as timestamp) as created_at,
        status,
        lower(currency) as currency
    from source
)

select * from renamed

Build a mart fact table

-- models/marts/finance/fct_orders.sql
{{
    config(
        materialized='incremental',
        unique_key='order_id',
        on_schema_change='sync_all_columns'
    )
}}

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

payments as (
    select * from {{ ref('int_orders__pivoted_payments') }}
),

final as (
    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        orders.status,
        payments.total_amount_cents,
        payments.payment_method,
        payments.total_amount_cents / 100.0 as total_amount_dollars
    from orders
    left join payments on orders.order_id = payments.order_id
    {% if is_incremental() %}
    where orders.updated_at > (select max(updated_at) from {{ this }})
    {% endif %}
)

select * from final

Define metrics in YAML (MetricFlow)

# models/marts/finance/_finance__models.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
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: status
        type: categorical
    measures:
      - name: order_count
        agg: count
        expr: order_id
      - name: total_revenue_cents
        agg: sum
        expr: total_amount_cents
      - name: average_order_value_cents
        agg: average
        expr: total_amount_cents

metrics:
  - name: revenue
    type: derived
    label: "Total Revenue"
    description: "Sum of all order payments in dollars"
    type_params:
      expr: total_revenue_cents / 100
      metrics:
        - name