Data Analytics Engineering Skill

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 Status (July 2026)

| Tool | Status | Key 2026 Fact | |------|--------|---------------| | dbt Core | v2.0 in alpha; open source, Apache 2.0, built on Fusion foundations | Upgrade guide | | dbt Fusion | GA on Snowflake (dbt platform); preview on BigQuery/Redshift; private preview on Databricks; no GA date confirmed yet | New dbt-platform projects default to Fusion; local/CLI Fusion still preview across adapters | | dbt + SDF | SDF Labs acquired Jan 2025; Rust SQL compiler is now the Fusion engine | Enables column-level lineage and typed SQL | | MetricFlow | Open sourced Apache 2.0 (Oct 2025, v0.209+); latest v0.211 (May 12, 2026) | Anchors the Open Semantic Interchange (OSI) v1.0 spec (Jan 2026) with Snowflake, Databricks, Salesforce, ThoughtSpot, Atlan, Alation, Denodo | | SQLMesh | Contributed to Linux Foundation by Fivetran (announced March 25, 2026, KubeCon EU); Apache 2.0 | Fivetran acquired SQLMesh's creator, Tobiko Data, in Sept 2025; founding LF members include Benzinga, CloudKitchens, Harness, Infinite Lambda, Jump AI, Minerva |

Verify GA/preview status per adapter before recommending a Fusion cutover — it changes monthly; treat the table above as directional, not a substitute for the Fusion availability page.

Default Workflow

Lock the metric contract first — define KPI names, business logic, grain, owner, and dimensions in assets/metric-dictionary.md
Choose one transformation baseline — standardize on dbt or SQLMesh before debating semantic-layer tooling (references/tool-comparison.md)
Model for consumption — build staging -> intermediate -> marts layers, pick final shape (star, wide, or activity schema) with references/modeling-patterns.md
Add contracts on critical interfaces — enforce schema, ownership, freshness, and quality expectations (references/contracts-catalogs-lineage.md)
Choose semantic serving only where it pays off — use references/semantic-layer-patterns.md to decide between dbt-native, Lightdash, Cube, or warehouse-native
Add release-safe quality controls — static tests, freshness, audits, anomaly monitoring (references/data-quality-testing.md and references/release-and-ci-patterns.md)
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 (now Linux Foundation / Apache 2.0)
  Broadest ecosystem, contracts, semantic layer, dbt-native CI
    -> dbt (Core v2 alpha or dbt platform with Fusion)
  Visual metadata-driven development, enterprise onboarding speed
    -> Coalesce
  Already on dbt and want faster compile + typed SQL
    -> Upgrade to dbt Fusion (GA on Snowflake; preview elsewhere)

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)

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 | | 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
dbt build --select state:modified+

[ ] 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 --models state:modified+

[ ] 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

Metrics are APIs — stable names, clear owners, versioned changes, explicit deprecation windows; do not change KPI semantics silently.
One model, one grain — a mart must have one unambiguous grain; create a separate model for a different grain instead of mixing.
Contracts on shared interfaces — required for executive marts, handoff tables, and models used by many teams; do not contract every transient staging model.
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.
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/causal-inference-applied.md | DAG-driven feature selection, DML, observational ATE estimation | | references/information-theory-applied.md | MI feature selection, KL drift detection, MDL clustering | | references/theory-of-constraints-applied.md | Pipeline lag isolation, capacity reallocation, approval-queue debug | | references/network-science-applied.md | Centrality, PageRank, community detection applied to lineage graphs |

Templates

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.

Fact-Checking

Use web search/web fetch to verify current external facts, versions, pricing, deadlines, or platform behavior before final answers.
Prefer primary sources; report source links and dates for volatile information.

Learnings Loop

Before applying this skill on a non-trivial task, read learnings.consolidated.md in this directory (and learnings.md if present).

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.

Agent Skills: Data Analytics Engineering

Install this agent skill to your local

Skill Files