Data Analytics Engineering

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)

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

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

textCopy code
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?

textCopy code
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

CI/CD Quality Gate Checklist

dbt projects (PR checks):

bashCopy code
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):

bashCopy code
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

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

bashCopy code
# 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

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.