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.

• 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

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

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

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

# 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

Navigation

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.