Use this skill when building dbt models, designing semantic layers, defining metrics, creating self-serve analytics, or structuring a data warehouse for analyst consumption. Triggers on dbt project setup, model layering (staging, intermediate, marts), ref() and source() usage, YAML schema definitions, metrics definitions, semantic layer configuration, dimensional modeling, slowly changing dimensions, data testing, and any task requiring analytics engineering best practices.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
The command stays on one line. Scroll horizontally to inspect it before copying.
Prefer a local copy? Download the files currently available to SkillsMP.
File Explorer
6 files
Showing SKILL.md
SKILL.md
Source instructions · Read-only preview
name
analytics-engineering
version
0.1.0
description
Use this skill when building dbt models, designing semantic layers, defining metrics, creating self-serve analytics, or structuring a data warehouse for analyst consumption. Triggers on dbt project setup, model layering (staging, intermediate, marts), ref() and source() usage, YAML schema definitions, metrics definitions, semantic layer configuration, dimensional modeling, slowly changing dimensions, data testing, and any task requiring analytics engineering best practices.
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
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.
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.
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.
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.
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.
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.sqlwith source as (
select*from {{ source('stripe', 'payments') }}
),
renamed as (
select
id as payment_id,
order_id,
cast(amount asinteger) as amount_cents,
cast(created astimestamp) 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') }}
),
finalas (
select
orders.order_id,
orders.customer_id,
orders.order_date,
orders.status,
payments.total_amount_cents,
payments.payment_method,
payments.total_amount_cents /100.0as total_amount_dollars
from orders
leftjoin payments on orders.order_id = payments.order_id
{% if is_incremental() %}
where orders.updated_at > (selectmax(updated_at) from {{ this }})
{% endif %}
)
select*fromfinal
Define metrics in YAML (MetricFlow)
# models/marts/finance/_finance__models.ymlsemantic_models:-name:ordersdefaults:agg_time_dimension:order_datemodel:ref('fct_orders')entities:-name:order_idtype:primary-name:customer_idtype:foreigndimensions:-name:order_datetype:timetype_params:time_granularity:day-name:statustype:categoricalmeasures:-name:order_countagg:countexpr:order_id-name:total_revenue_centsagg:sumexpr:total_amount_cents-name:average_order_value_centsagg:averageexpr:total_amount_centsmetrics:-name:revenuetype:derivedlabel:"Total Revenue"description:"Sum of all order payments in dollars"type_params:expr:total_revenue_cents/100metrics:-name:total_revenue_cents-name:order_counttype:simplelabel:"Order Count"type_params:measure:order_count
Write dbt tests and data contracts
# models/marts/finance/_finance__models.ymlmodels:-name:fct_ordersdescription:"One row per order. Grain: order_id."config:contract:enforced:truecolumns:-name:order_iddata_type:varchardescription:"Primary key - unique order identifier"tests:-unique-not_null-name:customer_iddescription:"FK to dim_customers"tests:-not_null-relationships:to:ref('dim_customers')field:customer_id-name:total_amount_centsdata_type:integerdescription:"Total order value in cents"tests:-not_null-dbt_utils.accepted_range:min_value:0
-- tests/singular/assert_order_total_positive.sql-- Returns rows that violate the rule (should return 0 rows to pass)select order_id, total_amount_cents
from {{ ref('fct_orders') }}
where total_amount_cents <0
WHERE status != 'test' in 12 models; when the value changes, half get missed
Create a macro or a staging-layer filter applied once at the source boundary
No incremental strategy for large tables
Full table rebuilds take hours and spike warehouse costs
Use incremental models with a reliable updated_at or event timestamp
Gotchas
Incremental models with a broken unique_key silently duplicate rows - If the unique_key doesn't match how the source system generates IDs (e.g., composite keys, NULL-able columns), the merge strategy falls back to appending and your fact table will have duplicate rows. Always test with a unique dbt test on the mart's primary key after the first incremental run.
ref() creates a compile-time dependency but not a runtime guarantee - dbt's ref() ensures build order, but if a staging model's source table is empty or missing, the downstream mart builds with zero rows and no error. Add not_null and row count tests to staging models so silent empty builds surface in CI.
Metrics defined in both the semantic layer and the BI tool diverge - If analysts can also create calculated fields in Looker/Tableau/Power BI, they will. Within months there will be two definitions of "revenue" and no one knows which is correct. Enforce a semantic-layer-first policy and audit BI tool custom fields quarterly.
SELECT * in staging models breaks on upstream schema changes - When a source table adds or removes a column, SELECT * staging models silently change shape, potentially breaking downstream marts. Explicitly list every column in staging models so schema changes cause a compile error rather than silent breakage.
Hardcoded dates in incremental WHERE clauses don't survive full refreshes - An incremental model that filters with where created_at > '2024-01-01' will drop historical data on a --full-refresh. Use {{ this }} to reference the current max timestamp, and document what happens on a forced full refresh.
References
For detailed patterns and implementation guidance, load the relevant file from
references/:
references/dbt-patterns.md - Advanced dbt patterns including macros, packages,
hooks, custom materializations, and CI/CD integration
references/semantic-layer.md - Deep dive into MetricFlow configuration, Cube
setup, dimension/measure types, and BI tool integration
references/self-serve-analytics.md - Patterns for building analyst-friendly
data platforms, documentation strategies, and data catalog integration
Only load a references file if the current task requires it - they are long and will
consume context.
Companion check
On first activation of this skill in a conversation: check which companion skills are installed by running ls ~/.claude/skills/ ~/.agent/skills/ ~/.agents/skills/ .claude/skills/ .agent/skills/ .agents/skills/ 2>/dev/null. Compare the results against the recommended_skills field in this file's frontmatter. For any that are missing, mention them once and offer to install:
