Analytics Data Engineer

Analytics Data Engineer

When to Use

• Structure a dbt project (layers, naming, materializations)
• Build staging → intermediate → mart pipelines in the warehouse
• Implement incremental, snapshot, or CDC-driven models
• Add tests (unique, not null, relationships, custom SQL) and freshness checks
• Document models and expose lineage for BI and stakeholders
• Define marts that map to metrics and dashboards
• Set up CI for analytics SQL (compile, test, slim CI)
• Debug metric mismatches between mart and dashboard

When NOT to Use

• Enterprise mesh, governance program, platform selection → data-architect
• Partition/cluster tuning without dbt context → data-warehouse-engineer
• Chart choice, executive dashboards, stakeholder storytelling → bi-analyst
• Feature engineering, training, experiments → data-scientist
• Data org roadmap and steward operations → data-manager
• Analytics eng hiring, squad roadmap, launch governance → analytics-data-engineering-manager-product
• Generic app CI/CD without analytics patterns → devops

Related skills

Core Workflows

1. Project layout and conventions

Layering, naming (stg_, int_, fct_, dim_), materialization defaults, env targets.

See `references/dbt_project_structure.md`.

2. Modeling for analytics

Facts, dimensions, wide marts, grain, degenerate dimensions, bridge tables.

See `references/analytics_modeling.md`.

3. Incremental and CDC

Merge strategies, full-refresh exceptions, late-arriving facts.

See `references/incremental_cdc.md`.

4. Quality and contracts

Tests, severity, source freshness, optional contracts with downstream.

See `references/testing_quality.md`.

5. Docs, lineage, exposures

Model descriptions, column docs, exposures to BI tools.

See `references/docs_lineage_exposures.md`.

6. Metrics alignment

Grain, definitions, ownership with bi-analyst and business-analyst.

See `references/metrics_alignment.md`.

Output standards

• Every mart documents grain and primary key in YAML
• Tests on keys and critical business rules before merge
• PR includes: models changed, test plan, backfill impact, downstream exposures
• No breaking grain change without migration note to BI

When to load references

• dbt layout → references/dbt_project_structure.md
• Modeling → references/analytics_modeling.md
• Incremental → references/incremental_cdc.md
• Tests → references/testing_quality.md
• Docs/CI → references/docs_lineage_exposures.md
• Metrics → references/metrics_alignment.md

Analytics Modeling

Grain first

Every mart answers: one row = what?

Document in model YAML:

description: One row per order line per day (order_date)

Changing grain is a breaking change for BI.

Patterns

Coordinate star-schema depth with data-warehouse-engineer for platform-wide conformed dimensions.

Facts

• Declarative measures; same grain
• Handle late-arriving dimensions in intermediate layer
• Surrogate keys stable across reloads

Dimensions

• SCD2 via snapshots or valid_from/valid_to in intermediate
• Role-playing dates as separate keys in fact (order_date_key, ship_date_key)

Intermediate layer role

• Complex joins and business rules here
• Keeps marts thin and testable
• Not exposed in catalog to general analysts

Performance hints

• Pre-aggregate heavy metrics in int_ or mart variants (fct_orders_daily)
• Align cluster/partition keys with filter columns — see data-warehouse-engineer

dbt Project Structure

Layering

Rule: BI tools only query marts (and approved exposures), not staging.

Naming

• {entity} singular for dimensions, events plural for facts where clear
• Suffix role: _daily, _hourly for snapshots of grain
• Avoid environment names in model names

Materializations (defaults)

Override in dbt_project.yml by folder.

Packages and macros

• Centralize surrogate keys, timezone, currency in macros
• Pin package versions in packages.yml

Environments

Anti-patterns

• Business logic in staging
• Circular dependencies between intermediates
• select * in production models

Documentation, Lineage, and Exposures

Model documentation

Minimum per mart in schema.yml:

• description — business purpose
• Column description for keys and measures
• meta for owner, PII tier, refresh cadence

Lineage

• dbt docs generate DAG; publish to catalog (dbt Cloud, OpenMetadata, etc.)
• Tag PII columns for governance alignment with data-architect

Exposures

Declare downstream dashboards in exposures.yml:

exposures:
  - name: executive_revenue_dashboard
    type: dashboard
    depends_on:
      - ref('fct_revenue')

Use for impact analysis when changing fct_revenue.

CI documentation

• Fail PR if undocumented public mart columns (custom test or linter)
• Auto-deploy docs site on main merge

README for analysts

Short analyst-facing note:

• Approved tables and grains
• Known limitations and refresh time
• Contact for metric definition changes

Handoff to BI

Incremental and CDC

When to incremental

Strategies

Watermarks

• Use updated_at with lookback buffer (e.g. 3 days) for late updates
• Document lookback in model description
• Full-refresh job on schedule for small tables only

CDC sources

• Fivetran/Airbyte/DMS — trust _fivetran_synced or equivalent
• Deletions: soft-delete flag in staging; filter in mart

Backfill playbook

1. Pause downstream dashboards or warn BI 2. Run full-refresh in maintenance window 3. Validate row counts and key uniqueness tests 4. Reconcile sample metrics with bi-analyst

Full refresh guardrails

• Require PR label and approver for prod full-refresh on large models
• Use --select model+ carefully in CI

Metrics Alignment

Definition of done for a metric

Document in metric catalog or mart YAML meta.

Reconciliation workflow

When dashboard ≠ mart:

1. Confirm same grain and filters 2. Confirm same time zone and date field 3. Compare SQL from BI tool vs mart query 4. Check incremental watermark and late data 5. Log root cause (definition change vs bug)

Roles

Changing a metric

1. Propose definition change with before/after SQL 2. Impact exposures and dashboards 3. Version or communicate breaking change 4. Update tests to match new rule

Semantic layer note

If Looker/MetricFlow/semantic layer exists:

• Prefer single definition in semantic layer OR mart, not both diverging
• Analytics engineer owns mart; BI may own explores — align in writing

PII and aggregation

• Minimum necessary dimensions in marts
• Aggregates for broad self-serve; row-level restricted roles

Testing and Quality

Test pyramid (analytics)

Severity

Custom tests

Use for rules that are not single-column:

• Revenue = sum(line_amount)
• No orders with ship before order date
• Referential integrity across systems

Contracts (optional)

• Define column types and constraints where warehouse supports
• Coordinate breaking contract changes with BI

CI minimum bar

On every PR:

• dbt parse / dbt compile
• dbt run --select state:modified+ (or slim CI)
• dbt test --select state:modified+

Production monitoring

• Freshness SLA per critical source
• Test failures route to on-call per data-system-ops-lead runbook

Anti-patterns

• Tests only on primary key
• No tests on business-critical measures
• Disabling tests to green CI