Data Analytics Engineering

Code-defined marts, metrics as APIs, contracts on critical interfaces, semantic layers only where they improve reuse or AI/BI consumption, and metadata systems that expose owners, lineage, quality, and governance to both humans and agents.

Primary sources: data/sources.json. Refresh time-sensitive claims against official docs before giving definitive recommendations.

When to Use

• Choose or improve an analytics engineering stack (dbt, SQLMesh, Coalesce)
• Define marts, grains, dimensions, facts, wide tables, or activity schemas
• Design or migrate a semantic layer (dbt Semantic Layer, Lightdash, Cube, warehouse-native)
• Add data contracts, metric governance, ownership, catalogs, and lineage
• Build data quality checks, freshness monitoring, anomaly detection, and release gates
• Prepare BI-ready models for dashboards, notebooks, APIs, or AI/NLQ analytics

When NOT to Use

• Lakehouse or ingestion architecture -> data-lake-platform
• Product/event instrumentation, attribution, or identity resolution -> marketing-product-analytics
• OLTP tuning, indexes, locks, or transactional database operations -> data-sql-optimization
• Metabase API automation -> data-metabase
• ML feature engineering, experiments, or model evaluation -> ai-ml-data-science

Triage Checklist

Run through these before any recommendation:

• What are the canonical business metrics and who owns each one?
• Serving requirements: dashboards, notebooks, APIs, embedded analytics, or AI/NLQ?
• Transformation baseline: dbt, SQLMesh, visual tooling, or warehouse SQL only?
• Which datasets are contract-worthy (downstream consumers depend on schema, freshness, semantics)?
• Semantic layer needed, or are well-governed marts sufficient today?
• Which metadata systems already cover catalog, lineage, ownership, access, and quality?

Stack Lookups

Release stage, adapter coverage, licence, and corporate ownership of these tools change between releases and deals. Do not state them from memory; look them up for the decision they feed. Other files in this skill point here instead of restating tool status.

Before you recommend...	Look up	Primary source
A dbt engine change (dbt Core or Fusion, local or platform)	Release stage and per-adapter availability for the user's warehouse and deployment mode	dbt release notes and the dbt upgrade guide for the target version
dbt Semantic Layer / MetricFlow	Current semantic YAML spec, release, and BI integrations	dbt Semantic Layer docs; MetricFlow release history on PyPI
SQLMesh	Release, licence, and governance body	SQLMesh repository and its foundation project page
Vendor risk as a reason to pick one tool	Current owner, licence, and governance body of each candidate	Vendor press pages; each project's LICENSE file and foundation page

• Engine upgrades: move production only on adapters the release notes list as generally available for that deployment mode. On any other adapter, run the new engine in CI next to the current one and keep production where it is.
• Vendor risk: analytics tooling consolidates, so two tools that look independent may share an owner. Check before you use "vendor independence" to separate them. When they share an owner, compare what survives an ownership change: the licence of the code you run, whether a neutral foundation governs the project, and how portable your model code is (plain SQL versus vendor-specific templating and config).

Default Workflow

1. Lock the metric contract first — define KPI names, business logic, grain, owner, and dimensions in assets/metric-dictionary.md
2. Choose one transformation baseline — standardize on dbt or SQLMesh before debating semantic-layer tooling (references/tool-comparison.md)
3. Model for consumption — build staging -> intermediate -> marts layers, pick final shape (star, wide, or activity schema) with references/modeling-patterns.md
4. Add contracts on critical interfaces — enforce schema, ownership, freshness, and quality expectations (references/contracts-catalogs-lineage.md)
5. Choose semantic serving only where it pays off — use references/semantic-layer-patterns.md to decide between dbt-native, Lightdash, Cube, or warehouse-native
6. Add release-safe quality controls — static tests, freshness, audits, anomaly monitoring (references/data-quality-testing.md and references/release-and-ci-patterns.md)
7. Publish discoverability and governance — catalog assets, lineage, owners, and change notices (references/metric-governance.md and assets/ownership-catalog-worksheet.md)

Decision: Choose Transformation Baseline

What does your team care about most?
  Plan-based deployment, environment isolation, backfill control
    -> SQLMesh
  Broadest ecosystem, contracts, semantic layer, dbt-native CI
    -> dbt (dbt Core or the dbt platform)
  Visual metadata-driven development, enterprise onboarding speed
    -> Coalesce
  Already on dbt and want faster compile + typed SQL
    -> Evaluate dbt Fusion, only on adapters that are GA for your deployment mode (see Stack Lookups)

Decision: Add a Semantic Layer?

Are the same business metrics reimplemented in 3+ places?
  NO -> Governed marts only; revisit when the answer flips to YES
  YES ->
    Most consumers are dbt-native?
      YES -> dbt Semantic Layer (MetricFlow) or Lightdash
    Need embedded analytics or product-facing APIs?
      YES -> Cube
    Single warehouse platform?
      Snowflake -> Snowflake Semantic Views
      Databricks -> Unity Catalog Metric Views
    Consumers need a business-friendly metric catalog as much as a query layer?
      YES -> Lightdash (or semantic layer + OpenMetadata/DataHub catalog)

Decision: Classify a Metric Change Before Shipping

Change class	Examples	Required release path
Additive	New metric, dimension, or optional field	Validate grain and source coverage; publish owner and definition before exposing it
Corrective	Bug fix that changes historical values	Dual-run old/new logic over a representative window; quantify affected periods and consumers; issue a change notice
Breaking	Rename, removal, grain change, or semantic redefinition	Version the metric or field; keep a compatibility window; migrate named consumers; define rollback

For corrective and breaking changes, reconciliation must compare totals and segment-level results at the intended grain. A matching grand total can hide offsetting errors, fan-out, or a changed population. Do not cut over until the owner accepts the measured delta, downstream consumers are enumerated, and the old definition remains recoverable for the agreed window.

Quick Reference

Task	Resource	When to Load
Choose dbt vs SQLMesh vs Coalesce	references/tool-comparison.md	New stack selection or migration
Pick star vs wide vs activity schema	references/modeling-patterns.md	Designing marts and semantic boundaries
Decide whether to add a semantic layer	references/semantic-layer-patterns.md	Metrics reuse, NLQ, API, or BI serving
Add contracts, ownership, lineage, catalog	references/contracts-catalogs-lineage.md	Shared marts and governed datasets
Add tests, audits, anomaly checks, CI gates	references/data-quality-testing.md	Prevent regressions and stale data
Define metric lifecycle and deprecation	references/metric-governance.md	Executive metrics and shared KPI programs
Plan rollout, dual-run, backfills	references/release-and-ci-patterns.md	Safe deployment and migration
PII separation, vault pattern, pseudonymisation	references/pii-vault-and-pseudonymisation.md	LLM/AI-facing query surfaces or GDPR scope
Draft metric definitions	assets/metric-dictionary.md	New KPIs or metric refactors
Draft semantic layer design	assets/semantic-layer-spec.md	Serving layer design review
Draft quality coverage	assets/data-quality-test-plan.md	Model-by-model test planning
Start a SQLMesh project or model	assets/transformation/sqlmesh/template-sqlmesh-project.md, assets/transformation/sqlmesh/template-sqlmesh-model.md	SQLMesh setup or model definition; see all SQLMesh templates below
Communicate metric changes	assets/metric-change-notice.md	Breaking or non-breaking metric updates
Document owners and catalog fields	assets/ownership-catalog-worksheet.md	Governance and discoverability setup
Migrate to a semantic layer	assets/semantic-layer-migration-checklist.md	Ad-hoc SQL to governed metrics
Handle data quality incidents	assets/data-quality-incident-runbook.md	Failures, stale data, or contract breaks

CI/CD Quality Gate Checklist

dbt projects (PR checks):

dbt deps
dbt parse
# prod-artifacts/ holds the production manifest.json (or set DBT_ENGINE_STATE)
dbt build --select state:modified+ --defer --state ./prod-artifacts

• No contracted model failures
• Freshness checks pass for critical sources
• Comparison queries run for executive KPI changes
• Schema tests pass on all mart models
• Anomaly monitoring shows no new alerts post-deploy

SQLMesh projects (PR/preview checks):

sqlmesh plan --no-prompts dev
sqlmesh test
sqlmesh audit   # SQLMesh has no state: selector; audit specific models with --model <name> (repeatable)

• Plan diff reviewed before apply
• Unit tests pass locally (no warehouse compute consumed)
• Audits pass on changed models
• Forward-only or backfill scope confirmed before deploy

Operating Principles

1. Metrics are APIs — stable names, clear owners, versioned changes, explicit deprecation windows; do not change KPI semantics silently.
2. One model, one grain — a mart must have one unambiguous grain; create a separate model for a different grain instead of mixing.
3. Contracts on shared interfaces — required for executive marts, handoff tables, and models used by many teams; do not contract every transient staging model.
4. Semantic layers are optional — add when multiple consumers need governed reuse, NLQ/AI access, or product-grade metric APIs; skip when well-governed marts are enough.
5. Metadata serves humans and agents — require descriptions, owners, lineage, quality status, and access boundaries on high-value assets.

Common Anti-Patterns

Anti-Pattern	Root Cause	Fix
KPI logic in dashboards or notebooks	No governed mart	Define in mart or semantic model first
Multiple grains in one mart	Dashboard convenience	Create separate models per grain
Contracts on every staging model	Misapplied governance	Contract only shared, high-stakes interfaces
Semantic layer before marts are stable	Premature abstraction	Stabilize marts before defining entities/measures
Same 360 table for every request	No modeling discipline	One model, one grain, one purpose
Allowing AI/NLQ access to undocumented marts	Missing metadata	Require grain, owner, freshness contract before AI access

Known Traps

• Slowly changing dimensions leaking into KPI joins and silently changing historical numbers.
• Metric refactors that change semantics without a notice, owner sign-off, or deprecation window.
• Identity stitching, attribution, and semantic metrics coexisting without explicit precedence rules.
• Assuming a semantic layer removes the need for release discipline, data tests, and change communication.
• Fan-out duplication: joining a fact to a dimension with a hidden one-to-many relationship (e.g. multiple addresses per customer, multiple attribution touches per order) silently multiplies additive measures. Check row counts before and after every join added to a mart, not just at the end.
• Non-additive measures in semantic layers: ratios, distinct counts, and percentiles do not roll up by simple summation across dimensions. A semantic layer that lets consumers slice a pre-computed ratio by a new dimension will produce a plausible but wrong number unless the measure is defined to recompute from its base components at query time.
• SCD Type 2 joins without effective-dating: joining a fact table to a dimension's current row (instead of the row valid at the fact's event time) rewrites history every time a dimension attribute changes — a common source of "the numbers changed even though nothing happened this month."
• Backfills without idempotency: a backfill or reprocessing job that appends instead of replacing (or lacks a natural dedup key) creates silent double-counting that structural uniqueness tests may not catch if the test only runs on the latest partition.
• Timezone/DST drift in freshness SLAs: freshness windows defined in wall-clock local time break twice a year and near midnight UTC boundaries; define freshness thresholds in UTC and treat calendar-day grain as a modeling decision, not an accident of the source system's timestamp.
• Simpson's paradox in aggregated KPIs: an org-wide metric can move in the opposite direction of every underlying segment when segment mix shifts; before alerting on a KPI's overall trend, check whether segment-level trends actually agree with it.

Scripts

Script	Purpose
scripts/analytics_linter.py	Validate, lint, and health-score a metric dictionary JSON file

# Validate required fields, duplicate names, and undefined data sources
python scripts/analytics_linter.py validate --input data/valid-metric-dictionary.json

# Lint metric quality: missing owners, undocumented dimensions, naming, SLAs
python scripts/analytics_linter.py lint --input data/valid-metric-dictionary.json

# Generate a Markdown metric dictionary health report
python scripts/analytics_linter.py report \
  --input data/sample-metric-dictionary.json \
  --output metric-health-report.md

Data

File	Description
data/sources.json	Curated reference sources for this skill
data/valid-metric-dictionary.json	Production-valid 15-metric dictionary for smoke tests and quickstart examples
data/sample-metric-dictionary.json	Realistic 15-metric dictionary with intentional gaps for linting demos

Navigation

File	Load When
references/tool-comparison.md	Choosing or comparing dbt, SQLMesh, Coalesce, or semantic-layer tools
references/modeling-patterns.md	Designing mart layers, grain, star/wide/activity schemas
references/semantic-layer-patterns.md	Deciding on and implementing a semantic serving layer
references/contracts-catalogs-lineage.md	Adding data contracts, catalog metadata, and lineage on shared assets
references/data-quality-testing.md	Building test suites, freshness checks, and anomaly monitoring
references/metric-governance.md	Governing, versioning, and deprecating shared KPIs
references/release-and-ci-patterns.md	CI/CD pipelines, dual-run validation, backfills, safe cutovers
references/pii-vault-and-pseudonymisation.md	Separating PII from analytical facts for LLM/AI or GDPR-scoped surfaces
references/transformation-patterns.md	Designing dbt or SQLMesh transformation layer (moved from data-lake-platform)
references/lake-data-quality-patterns.md	Adding GX/Soda quality contracts on lake-native tables (moved from data-lake-platform)

Out of scope: causal inference, network science, information theory, and theory-of-constraints analysis. For DAG blast radius, use dbt ls -s model+ or len(nx.descendants(G, node)) on the lineage graph. For pipeline-lag bottlenecks, use foundations-theory-of-constraints. For causal attribution, mutual-information feature selection, or network analysis on analytics data, use foundations-causal-inference, foundations-information-theory, or foundations-network-science.

Templates

• dbt: project template
• SQLMesh setup and modeling: project, model, incremental, DAG
• SQLMesh verification and operations: testing, production, security, layering and access
• assets/metric-dictionary.md
• assets/semantic-layer-spec.md
• assets/data-quality-test-plan.md
• assets/metric-change-notice.md
• assets/ownership-catalog-worksheet.md
• assets/semantic-layer-migration-checklist.md
• assets/data-quality-incident-runbook.md

Related Skills

• data-lake-platform — ingestion, table formats, orchestration, data mesh
• data-sql-optimization — transactional SQL performance and operational tuning
• marketing-product-analytics — event instrumentation and acquisition measurement
• data-metabase — Metabase automation and dashboard scripting
• ai-ml-data-science — experimentation and modeling workflows

Current-Source Policy

• Prefer trust_tier: primary entries in data/sources.json for vendor capabilities, syntax, pricing, limits, and release-sensitive recommendations.
• For recommendation questions, refresh against current official docs and recent release notes.
• Separate verified facts from judgment calls; label strategic opinions explicitly.
• If web access is unavailable, state that the recommendation is partially unverified.

Learnings Loop

When prior decisions or pitfalls are relevant, consult learnings.consolidated.md if present; use learnings.md only for needed history or as the available fallback. Otherwise skip both.

After applying it, if you encountered a pattern worth remembering, a mistake worth preventing, or a domain fact that surprised you, append one dated bullet to learnings.md via agents-skills-feedback-loop/scripts/append_learning.py. Do not modify SKILL.md itself.