yeaight7 avatar

data-engineering

Analytics engineering, dbt patterns, BigQuery optimization, data quality, and pipeline workflows.

version
0.6.3
GitHub stars
9

Install

/plugin install data-engineering@agent-powerups

Run it inside Claude Code after adding the marketplace with /plugin marketplace add yeaight7/agent-powerups.

Contents

Discovered from source

Skills 8

BigQuery Cost Audit

When to Use

  • Reviewing BigQuery query costs, failure patterns, or performance inefficiencies.
  • Identifying which jobs, users, or projects are driving the highest spend.
  • Preparing optimization recommendations for an engineering or cost-review meeting.
  • Auditing governance: scheduled jobs, duplicated logic, or low-value recurring queries.

Goals

  • Identify the main cost drivers by job, project, and user.
  • Detect repeated waste patterns (full scans, failed retries, duplicated logic).
  • Suggest realistic optimizations with estimated impact.
  • Translate technical waste into business-language findings.

What to Inspect

Cost hotspots

-- Top 20 most expensive jobs in the past 7 days
SELECT
  job_id, user_email, query,
  total_bytes_processed / POW(1024, 4) AS tb_processed,
  ROUND(total_bytes_processed / POW(1024, 4) * 6.25, 2) AS estimated_cost_usd,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_bytes_processed DESC
LIMIT 20;

Repeated failures

SELECT
  error_result.reason, COUNT(*) AS failure_count, user_email,
  ANY_VALUE(query) AS sample_query
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND error_result IS NOT NULL
GROUP BY error_result.reason, user_email
ORDER BY failure_count DESC;

Missing partition pruning

Look for queries that scan full tables despite available partition columns:

  • No WHERE filter on the partition column.
  • _PARTITIONTIME or _PARTITIONDATE not in the filter.
  • LIMIT used without a partition filter (does not reduce scan cost).

Missing clustering

Check high-scan queries that filter on non-clustered columns after partitioning is already in place.

Scheduled jobs with low value

-- Find scheduled queries with high scan volume (via Data Transfer Service run history)
-- Note: scheduled query metadata lives in region-specific transfer_run tables.
-- Substitute your project and region:
SELECT
  config.display_name,
  run.state,
  run.end_time,
  run.error_status
FROM `<project>.<region>.INFORMATION_SCHEMA.SCHEDULED_QUERY_RUNS` AS run
JOIN `<project>.<region>.INFORMATION_SCHEMA.SCHEDULED_QUERIES` AS config
  ON run.scheduled_query_id = config.scheduled_query_id
WHERE run.end_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
ORDER BY config.display_name, run.end_time DESC;
-- Then cross-reference with JOBS to find per-run bytes_processed.

Output Format

  1. Top cost hotspots — job ID, user, bytes scanned, estimated USD, query snippet.
  2. Recurring failure patterns — error reason, count, user, sample query.
  3. Optimization opportunities:
    • Partition pruning gaps.
    • Clustering candidates.
    • Queries eligible for materialization or caching.
    • Scheduled jobs that should be retired or narrowed.
  4. Quick wins vs larger refactors — flag which optimizations are a one-line WHERE clause fix vs a schema change.
  5. Engineering summary — technical root causes and remediation steps.
  6. Business summary — cost impact in plain language; approximate monthly savings per opportunity.

Rules

  • Focus on practical opportunities, not theoretical micro-optimizations.
  • Prefer changes that reduce cost without increasing operational fragility.
  • Do not run destructive operations.
  • Do not edit code or queries unless explicitly asked.
  • Acknowledge uncertainty when cost estimates depend on assumptions about query frequency.

Verification

  • Hotspots listed with job, user, bytes scanned, and estimated USD
  • Failure patterns grouped by error reason with counts and a sample query
  • Each optimization classified as a quick win or a larger refactor
  • Savings estimates state the assumptions behind them
  • Findings include both an engineering summary and a business summary

Data Quality

Testing patterns for dbt projects on analytical warehouses such as BigQuery. This workflow uses dbt tests exclusively — no external data-quality framework required.

Test Types

1. Generic Tests (in YAML files)

Defined in .yml files alongside models. Use data_tests: (not tests:):

# models/core/shared/dim_teams.yml
models:
  - name: dim_teams
    columns:
      - name: id
        description: Surrogate key
        data_tests:
          - unique
          - not_null

      - name: natural_id
        description: Natural key from source
        data_tests:
          - unique
          - not_null

      - name: plan_code
        description: Subscription plan code
        data_tests:
          - accepted_values:
              values: "{{ get_plan_code_values() }}"  # Use macros, not hardcoded lists

2. Singular Tests (assert_*.sql files)

Business logic tests live in tests/ as assert_*.sql files. They pass when they return zero rows:

-- tests/teams/assert_teams_created_before_deleted.sql
with teams as (
    select * from {{ ref('dim_teams') }}
)

select
    teams.natural_id,
    teams.created_at,
    teams.deleted_at
from teams
where teams.deleted_at < teams.created_at
-- tests/academy/assert_students_with_completed_modules_are_enrolled.sql
with completed as (
    select distinct student_id from {{ ref('fct_academy_course_modules_completed') }}
),

enrolled as (
    select distinct student_id from {{ ref('fct_academy_course_enrollments') }}
),

orphaned as (
    select completed.student_id
    from completed
    left join enrolled
        on completed.student_id = enrolled.student_id
    where enrolled.student_id is null
)

select * from orphaned

Organize singular tests in subdirectories matching the domain: tests/accounts/, tests/billing/, tests/content/, etc.

Generic Test Patterns

Primary Key Tests

Every model's primary key (id) must have both tests:

- name: id
  data_tests:
    - unique
    - not_null

Referential Integrity

- name: dim_user_id
  data_tests:
    - not_null
    - relationships:
        to: ref('dim_users')
        field: id

Accepted Values

Use macros from macros/accepted_values/ instead of hardcoding values:

- name: plan_code
  data_tests:
    - accepted_values:
        values: "{{ get_plan_code_values() }}"

Browse available macros: macros/accepted_values/get_*.sql

dbt_utils Tests

models:
  - name: fct_team_members
    data_tests:
      # Table must not be empty
      - dbt_utils.at_least_one:
          column_name: id
      # Data freshness — table updated in last 24h
      - dbt_utils.recency:
          datepart: hour
          field: created_at
          interval: 24
      # Business rule on a measure
      - dbt_utils.expression_is_true:
          expression: "total_seats >= 0"

    columns:
      - name: team_id
        data_tests:
          # Cross-table: team must exist in dim_teams
          - relationships:
              to: ref('dim_teams')
              field: natural_id

Date Logic Tests

- name: end_at
  data_tests:
    # end_at must be after start_at when both are set
    - dbt_utils.expression_is_true:
        expression: "end_at is null or end_at > start_at"

Custom Generic Tests

Define reusable tests in tests/generic/ or use the project's existing test_utils:

-- tests/generic/test_no_future_dates.sql
{% test no_future_dates(model, column_name) %}

select {{ column_name }}
from {{ model }}
where {{ column_name }} > current_timestamp

{% endtest %}

Usage in YAML:

- name: created_at
  data_tests:
    - no_future_dates

Cross-System Consistency

Assert that data is consistent across source systems. These go in tests/ as singular tests:

-- tests/finance/assert_billing_plan_matches_team_plan.sql
-- Tests consistency between billing records and team plan data
with invoices as (
    select * from {{ ref('fct_billing_invoices') }}
),

teams as (
    select * from {{ ref('dim_teams') }}
),

mismatched as (
    select
        invoices.invoice_id,
        invoices.plan_code as invoice_plan,
        teams.plan_code as team_plan
    from invoices
    inner join teams
        on invoices.team_id = teams.natural_id
    where invoices.plan_code != teams.plan_code
      and invoices.is_active
)

select * from mismatched

Test Organization

tests/
├── accounts/         Tests for account and team domain models
├── adhoc/            Ad-hoc spot-check tests
├── billing/          Finance and billing consistency tests
├── content/          Content or product-domain tests
└── generic/          Reusable generic test definitions

Running Tests

dbt test                          # All tests
dbt test -s <model>               # Tests for one model
dbt test -s tag:critical          # Tests with a specific tag
dbt build -s <model>              # Run + test together
dbt test --store-failures         # Persist failures to BQ for inspection

To inspect test failures in BigQuery (development):

select * from `dbt_<username>`.`dbt_test__audit`.<test_name>

Test Coverage Checklist

For every new model:

  • Primary key id: unique + not_null
  • Primary key natural_id: unique + not_null
  • All FK columns: relationships test
  • All enum columns: accepted_values using a macro
  • Business rules: dbt_utils.expression_is_true or singular tests
  • Missing record sentinel: verify id test catches duplicates
  • Data freshness: dbt_utils.recency for frequently updated tables

Best Practices

  • Focus tests on columns that matter to business decisions
  • Use macros from macros/accepted_values/ — never hardcode enum lists in YAML
  • Write singular tests for non-obvious business rules that need explicit documentation
  • Organize singular tests in domain subdirectories matching the model domain
  • Use --store-failures in development to inspect what went wrong
  • Test referential integrity between fact and dimension tables

Purpose

Determine whether a dbt incremental model's chosen strategy is justified, understandable, maintainable, and consistent with repository conventions. The goal is not to praise clever implementations. Default stance: skeptical, low-risk, root-cause oriented, convention-preserving — prefer the simplest correct strategy.

When to Use

  • Reviewing a new or modified materialized='incremental' model
  • An incremental model shows confusing runtime behavior: unexpected rebuilds, deletes, or reprocessing
  • A model deviates from the repo's established incremental conventions without explanation

Inputs

  • The model SQL and its config: incremental_strategy, unique_key, partition_by/cluster_by/partitions, pre_hook/post_hook
  • Comparable incremental models in the same repo

Workflow

  1. Understand the repo pattern first. Inspect similar incremental models and how they configure their strategies:

    grep -rn "incremental_strategy" models/
    grep -rln "is_incremental()" models/
    
  2. Explain the actual runtime behavior. Compile the model and read the SQL that really runs — is_incremental() branches make the Jinja source misleading:

    dbt compile --select <model>
    # then read target/compiled/<project>/models/.../<model>.sql
    

    is_incremental() is false when the target table does not yet exist or on --full-refresh, so reason through both branches.

  3. Review the full incremental surface: materialized='incremental', incremental_strategy, is_incremental() logic, unique_key, partition_by/cluster_by/partitions, pre_hook/post_hook, delete/overwrite/merge semantics, rolling window logic, deduplication choices.

  4. Identify hidden risks:

    • hybrid full-refresh behavior hidden inside an incremental model
    • deletes outside expected scope
    • reprocessing more data than intended
    • partition/window drift and maintenance burden
    • strategy mismatch with the repo's established conventions
    • operational complexity without clear business benefit
    • confusing runtime behavior that future maintainers may misread
  5. Check downstream stability before recommending a change:

    dbt ls --select <model>+
    

    Strategy changes can shift BI/reporting outputs; list affected downstream assets.

Output

  • A. Current model runtime behavior
  • B. Repo convention comparison
  • C. Strategy risks
  • D. Whether the complexity is justified
  • E. Safer alternative
  • F. Minimal-diff recommendation
  • G. Validation plan

Verification

  • Compiled SQL inspected, not just the Jinja source
  • At least one comparable incremental model in the repo compared
  • Delete/overwrite/merge scope stated explicitly
  • Downstream assets listed before any strategy change is recommended
  • Recommendation is a minimal diff and includes a validation plan

Failure Modes

  • Praising cleverness — operational complexity without clear business benefit is a finding, not a feature.
  • Reading Jinja instead of compiled SQL — is_incremental() branches hide the real runtime behavior.
  • Auditing in isolation — a strategy that is fine on its own may still be an unjustified deviation from repo conventions.

Purpose

Inspect a dbt change set before it ships: identify which assets changed, estimate downstream impact, detect likely gaps in testing, contracts, documentation, and YAML alignment, and recommend the narrowest safe validation path. Preflight is read-only — do not edit code unless explicitly asked.

When to Use

  • Before opening a PR in an analytics repository
  • Reviewing someone else's dbt change
  • Assessing the risk of a change whose downstream impact is unclear

Inputs

  • The change set (branch diff or working tree)
  • The dbt project, for lineage and selector queries

Workflow

  1. Enumerate changed assets:

    git diff --name-status origin/main...HEAD
    

    Classify each change: models, snapshots, seeds, macros, tests, schema YAML, semantic models, metrics, saved queries, exposures or BI-facing assets.

  2. Estimate blast radius. For each changed model, list downstream dependents:

    dbt ls --select <model>+
    

    If a production manifest is available, cover the whole change set at once:

    dbt ls --select state:modified+ --state <path-to-prod-artifacts>
    
  3. Run the review checklist. Check for:

    • model grain changes
    • renamed columns or breaking contract changes
    • missing or weakened tests
    • YAML not updated after model changes
    • metrics or semantic definitions drifting from prior meaning
    • high blast radius due to downstream dependencies
    • incremental logic changes
    • snapshot logic changes
    • joins or filters that may alter business meaning
  4. Recommend the narrowest meaningful validation path first:

    1. targeted checks: dbt build --select <changed_model>
    2. narrow downstream checks: dbt build --select <changed_model>+1
    3. broader PR-level validation only if needed: dbt build --select state:modified+ --state <path>

    If exact commands are available in the repo (Makefile, CI config, docs), prefer them. If not, say what should be validated conceptually.

Output

  1. changed assets summary
  2. likely blast radius
  3. likely missing checks or weak spots
  4. recommended validation plan
  5. items that need stakeholder or domain-owner confirmation

Verification

  • Every changed file classified by asset type
  • Blast radius backed by lineage (dbt ls), not guessed
  • Each checklist hit mapped to a recommended validation step
  • Validation plan starts with the narrowest step that covers the risk
  • Stakeholder-confirmation items listed separately

Failure Modes

  • Style distraction — preflight is about correctness and semantic impact, not formatting.
  • Overclaiming impact — be conservative with claims; verify lineage before asserting blast radius.
  • Validating everything — defaulting to a full rebuild obscures which check actually covers the risk; the goal is the narrowest safe path.
  • Editing instead of reporting — do not edit code unless explicitly asked.

dbt Strategy

Patterns for building dbt models in warehouse-backed analytics projects using Kimball-style dimensional modeling.

Layer Architecture

sources/     Source views — raw data from app DB, event stream, billing, CRM, LMS
    ↓
staging/     Intermediate transformations (keep minimal — new models go directly to core/)
    ↓
core/        Fact and dimension tables (main transformation layer)   → tables
    ↓
marts/       Business aggregations built on top of core             → tables

Put new models in core/ directly. Use staging/ only when complex intermediate joins are truly necessary.

Naming Conventions

LayerPrefixExample
Sourcessrc_src_app_teams, src_events
Stagingstg_stg_teams, stg_billing_customers
Core dimdim_dim_teams, dim_users
Core fctfct_fct_team_members, fct_team_budgets
Martsmart_mart_team_overview, mart_creation_overview

Domain subdirectories in core/: academy/, analytics/, finance/, product/, sales/, scoring/, shared/

Critical SQL Rules

No Aliases

Always reference the full CTE name — never use aliases:

-- ❌ WRONG
select u.id, t.name
from users u
join teams t on u.team_id = t.id

-- ✅ CORRECT
select users.id, teams.name
from users
join teams on users.team_id = teams.id

Standard CTE Structure

Every model uses clear CTEs. The final SELECT is always select * from final:

with source_cte as (
    select * from {{ ref('src_app_teams') }}
),

transformed as (
    select
        -- Primary key
        source_cte.team_id,

        -- Attributes
        source_cte.name as team_name,
        source_cte.created_at
    from source_cte
),

final as (
    select * from transformed
)

select * from final

Core Patterns

Pattern 1: Dimension Table with Surrogate Key + Missing Record

Every dimension includes a union all missing record sentinel.

Key naming rule (per CLAUDE.md and Kimball):

  • Surrogate key: <object>_sk — e.g., team_sk
  • Natural key: <object>_id — e.g., team_id

Note: Older models in the project use id / natural_id — this is legacy. New models must use <object>_sk / <object>_id.

{% set missing_team = "'Missing Team'" %}

with team_snapshots as (
    select * from {{ ref('src_snapshot_app_teams') }}
),

latest_state as (
    {{ dbt_utils.deduplicate(
        relation='team_snapshots',
        partition_by='team_id',
        order_by='state_valid_from desc'
    ) }}
),

final as (
    select
        -- Surrogate key
        {{ dbt_utils.generate_surrogate_key(['team_id']) }} as team_sk,
        -- Natural key
        team_id,

        -- Attributes
        latest_state.name,
        latest_state.plan_code,
        latest_state.created_at,
        latest_state.deleted_at

    from latest_state

    union all

    select
        {{ missing_record_id() }} as team_sk,
        '-1' as team_id,
        {{ missing_team }} as name,
        {{ missing_team }} as plan_code,
        cast(null as timestamp) as created_at,
        cast(null as timestamp) as deleted_at
)

select * from final

BigQuery null casts: cast(null as int64), cast(null as bool), cast(null as timestamp), cast(null as string)

Pattern 2: Fact Table with Foreign Keys

Fact table surrogate key follows the same <object>_sk rule. Foreign keys to dimensions reference the dimension's surrogate key (<dim>_sk):

with enrollments as (
    select * from {{ ref('stg_academy_student_enrollments') }}
),

dim_courses as (
    select * from {{ ref('dim_academy_courses') }}
),

dim_users as (
    select * from {{ ref('dim_users') }}
),

final as (
    select
        -- Surrogate key
        {{ dbt_utils.generate_surrogate_key(['enrollments.enrollment_id']) }} as enrollment_sk,
        -- Natural key
        enrollments.enrollment_id,

        -- Foreign keys (reference dimension surrogate keys)
        {{ get_id_null('dim_courses.course_sk') }} as course_sk,
        {{ get_id_null('dim_users.user_sk') }} as user_sk,
        {{ get_date_id('enrollments.enrolled_at') }} as enrolled_date_sk,

        -- Measures
        enrollments.enrolled_at

    from enrollments
    left join dim_courses
        on enrollments.course_id = dim_courses.course_id
    left join dim_users
        on enrollments.user_id = dim_users.user_id
)

select * from final

Join dimensions using the natural key (<object>_id). Store the dimension's surrogate key (<object>_sk) as the FK column in the fact. Use {{ get_id_null(...) }} for nullable FK references to dimension surrogate keys. Use {{ get_date_id(...) }} for foreign keys to dim_date.

Pattern 3: Deduplication

Use dbt_utils.deduplicate — never use QUALIFY:

deduplicated as (
    {{ dbt_utils.deduplicate(
        relation='source_cte',
        partition_by='team_id',
        order_by='updated_at desc'
    ) }}
),

Pattern 4: BigQuery Incremental Model

For large or event tables, use insert_overwrite with partition_by:

{{
    config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        partition_by={
            "field": "event_date",
            "data_type": "date",
            "granularity": "day"
        }
    )
}}

with events as (
    select * from {{ source('snowplow', 'events') }}

    {% if is_incremental() %}
    where date(collector_tstamp) >= date_sub(current_date, interval 3 day)
    {% endif %}
),

final as (
    select
        events.event_id,
        date(events.collector_tstamp) as event_date,
        events.collector_tstamp
    from events
)

select * from final

Pattern 5: Source Definition (BigQuery)

# models/sources/app/_app__sources.yml
version: 2

sources:
  - name: app
    description: Core application database
    database: <warehouse_project>
    schema: app
    tables:
      - name: teams
        description: Raw team records
        columns:
          - name: id
            data_tests:
              - unique
              - not_null

Note: use data_tests: (not tests:) in YAML files in this project.

Pattern 6: Project Variables

-- Sentinel values defined in dbt_project.yml
coalesce(teams.plan_code, '{{ var("unknown") }}') as plan_code
coalesce(users.deleted_at, cast('{{ var("the_distant_future") }}' as timestamp)) as deleted_at

Pattern 7: Documentation with Docblocks

Shared descriptions live in .md files as Jinja docblocks:

<!-- models/core/shared/docs_shared.md -->
{% docs team_id %}
The unique identifier for a team entity in the application.
{% enddocs %}

Reference in YAML:

columns:
  - name: team_id
    description: "{{ doc('team_id') }}"
    data_tests:
      - unique
      - not_null

Available Macros

MacroPurpose
missing_record_id()Returns the ID used for missing record sentinels
get_id_null(expr)Safe FK — returns missing_record_id() if null
get_date_id(expr)Converts timestamp to dim_date FK
deletion_status_field()Adds deletion_status derived from deleted_at
dbt_utils.generate_surrogate_key([...])Generates MD5 surrogate key
dbt_utils.deduplicate(relation, partition_by, order_by)Deduplicates a CTE

Accepted values macros live in macros/accepted_values/ — use these in YAML tests instead of hardcoding enum values.

Datasets

EnvironmentDataset
Production<project>.dbt_production
Developmentdbt_<username> e.g., dbt_johndoe

Use production for reading, development dataset for writing during development.

MCP dbt Tools

Before modifying any model, check its context:

get_model_details(unique_id)    # Compiled SQL and metadata
get_model_parents(unique_id)    # Upstream dependencies
get_model_children(unique_id)   # Downstream impact
get_all_models()                # Browse all models with metadata

High-Impact Models (Most Connected)

These god nodes have the most downstream dependencies — changes cascade widely:

ModelEdgesAction before change
dim_teams46get_model_children()
dim_users36get_model_children()
util_user_dimensions32get_model_children()
fct_team_members22get_model_children()

dbt Commands

dbt run -s <model>          # Run specific model
dbt test -s <model>         # Test specific model
dbt build -s <model>        # Run + test
dbt run -s +<model>         # Model and all upstream
dbt run -s <model>+         # Model and all downstream
dbt compile -s <model>      # Compile without running

Verification

  • Model sits in the correct layer with the matching prefix (src_/stg_/dim_/fct_/mart_); new models default to core
  • No table aliases anywhere — full CTE names referenced; the final statement is select * from final
  • Dimensions use the surrogate + natural key naming (<object>_sk / <object>_id) and include the missing-record sentinel
  • Facts join dimensions on natural keys and store surrogate-key FKs via get_id_null / get_date_id
  • Deduplication uses dbt_utils.deduplicate — no QUALIFY
  • YAML uses data_tests: and accepted-values macros instead of hardcoded enum values
  • get_model_children() was checked before touching any high-impact model
  • Writes target the development dataset; production is read-only

Metric Impact Analyzer

Use this skill when a change may affect metrics, semantic models, dashboards, reporting, or stakeholder-facing KPIs.

Goals

  • Detect whether a technical change also changes business meaning.
  • Identify likely downstream breakage in BI tooling and dashboards.
  • Separate harmless refactors from definition-level changes.

What to Inspect

  • Metric definitions and calculation logic.
  • Semantic model changes (dimensions, measures, relationships).
  • Renamed or removed fields referenced by saved queries or dashboards.
  • Changed filters, time windows, or default values.
  • Changed joins, granularity, or fanout.
  • Contract-breaking field type changes (e.g., INT → FLOAT, nullable → NOT NULL).

Change Classification

Change typeRisk levelAction required
Column rename with alias preservedLowValidate alias propagation
Description or label changeLowConfirm no tooling parses descriptions
New optional field addedLowNo action unless it changes defaults
Filter condition tightenedMediumValidate that the new filter matches intent
Metric formula changedHighStakeholder review required
Grain changed (e.g., day → week)HighAll downstream rollups must be revalidated
Dimension removedBreakingCannot ship without migration plan
Join path changedBreakingAll dependent metrics may be affected

Analysis Steps

  1. Summarize what changed technically (field names, types, logic, joins).
  2. Classify each change using the table above.
  3. Infer what may have changed semantically — ask: "will this metric return a different number for the same time period?"
  4. Identify who or what downstream may break: dashboards, saved queries, alerts, exports, downstream dbt models, BI tool semantic layers.
  5. Flag whether stakeholder sign-off is required before the change is promoted.

Output Format

  1. Technical change summary — what exactly changed, at the field/model level.
  2. Business meaning impact — does this change what the number means to a business user?
  3. Likely reporting or dashboard breakage — specific assets at risk.
  4. Required validation — what must be confirmed before shipping.
  5. Stakeholder confirmation needed? yes / no / depends — with reason.

Validation / Done Criteria

  • Technical change summary covers all modified fields, models, and logic.
  • Every change is classified by risk level using the table above.
  • Downstream breakage is identified or explicitly noted as "unknown — downstream consumers not traced."
  • Stakeholder confirmation requirement is stated as yes / no / depends, with a reason.

Rules

  • Be explicit about uncertainty when you cannot trace all downstream consumers.
  • Use plain business language in the final summary — avoid dbt/SQL jargon in the stakeholder section.
  • Do not edit code or models unless explicitly asked.

Purpose

Changes to the semantic layer directly impact dashboards and business reporting. A silent drift in a metric definition destroys trust. Review every semantic-layer change for mathematical soundness and backwards compatibility before approval.

When to Use

  • A PR modifies metric or semantic model YAML
  • A metric's expr, aggregation, or filters are changing
  • New dimensions or entities are being added to an existing semantic model

Inputs

  • The semantic-layer diff (semantic models, metrics, saved queries)
  • The underlying model's grain and entity keys

Workflow

  1. Enumerate what changed:

    git diff origin/main...HEAD -- '*.yml' '*.yaml'
    dbt ls --resource-type metric
    dbt ls --resource-type semantic_model
    
  2. Identify the change type:

    • Addition — safe (adding a new metric or dimension).
    • Deprecation — requires communication (removing a metric).
    • Modification — high risk (changing the SQL expression, aggregation, or filters of an existing metric).
  3. Evaluate mathematical soundness:

    • Are we averaging an average?
    • Are we summing a distinct count?
    • Does adding this dimension cause a fan-out that inflates the metric?
  4. Check backwards compatibility. If an existing metric's logic is changed, you MUST flag it. The recommended path is dbt's metric versioning or a new metric (e.g., revenue_v2) rather than silently altering historical numbers. Find consumers of the metric before judging impact:

    grep -rn "<metric_name>" --include="*.yml" --include="*.yaml" .
    
  5. Verify entity mapping. Ensure entities (primary/foreign keys) match the granularity of the underlying semantic model.

  6. Confirm definitions still parse:

    dbt parse
    

Output

  • Change classification (addition / deprecation / modification) per touched metric or semantic model
  • Mathematical soundness findings
  • Backwards-compatibility verdict, with consumers that need communication
  • Entity/grain mismatches, if any

Verification

  • Every touched metric and semantic model classified by change type
  • Modifications to existing metrics explicitly flagged, never silently approved
  • Consumers of modified metrics identified
  • dbt parse passes on the changed project
  • Entity keys checked against the underlying model's grain

Failure Modes

  • Silent restatement — approving a change to a core metric's expr without explicitly confirming the business requested the restatement of historical data.
  • Treating modification like addition — additions are safe; modifications are high risk and need versioning or a new metric.
  • Fan-out blindness — a new dimension join can inflate a metric while every individual definition still looks correct.

Purpose

Review SQL as business logic, not just syntax. Detect silent semantic changes and flag places where technically valid SQL can still produce wrong business results.

When to Use

  • Reviewing SQL changes that feed reports, metrics, or financial/product logic
  • A query was modified and downstream numbers shifted, or might have
  • Output looks plausible but the definition of what is being counted may have changed

Inputs

  • The SQL under review (diff preferred, full query otherwise)
  • The intended grain and business definition, before and after the change

Workflow

  1. Establish the intended grain before and after the query or change.

  2. Scan for the standard risk patterns:

    • grain mismatches
    • duplicate rows introduced by joins
    • incorrect join keys
    • left vs inner join behavior changes
    • filters that alter population definitions
    • null handling that changes meaning
    • default values that hide data quality issues
    • aggregation mistakes
    • window functions with unsafe partitions or ordering
    • date logic and timezone assumptions
    • incremental logic that can double count, miss rows, or drift
    • metric definitions that no longer match prior intent
  3. Ask the review questions:

    1. What is the intended grain before and after this query?
    2. Could this query duplicate or drop rows?
    3. Has the business definition changed even if the SQL still runs?
    4. Are there edge cases around nulls, late-arriving data, or time windows?
    5. What result could look plausible while still being wrong?
  4. Propose concrete validation checks, for example:

    -- grain check: the expected key must be unique
    SELECT <key_columns>, COUNT(*)
    FROM (<query under review>)
    GROUP BY <key_columns>
    HAVING COUNT(*) > 1;
    
    -- before/after comparison on a stable slice
    SELECT COUNT(*), SUM(<metric_column>) FROM (<old query>);
    SELECT COUNT(*), SUM(<metric_column>) FROM (<new query>);
    

Output

  1. summary of business-logic risks
  2. likely semantic changes
  3. highest-risk joins, filters, or aggregations
  4. concrete validation checks to run
  5. what needs human confirmation

Verification

  • Intended grain stated for before and after
  • Every flagged risk tied to a specific join, filter, or aggregation
  • At least one runnable validation check proposed per high-risk finding
  • Human-confirmation items listed separately

Failure Modes

  • Syntax-only review — the query parses and runs, so it gets approved; business meaning was never checked.
  • Style distraction — prioritize correctness over style; do not get distracted by minor formatting issues.
  • Plausible-results trap — numbers in the right ballpark pass review while the population definition silently changed.
  • Editing instead of reporting — do not edit code unless explicitly asked.

Commands 4

BigQuery Cost Check Command

CRITICAL BEHAVIORAL RULES

  1. Analyze Static SQL: You cannot run the query to see the bytes billed. You must statically analyze the SQL syntax and dbt config() blocks.
  2. Focus on Bytes Billed: In BigQuery, cost is driven by data scanned, not compute time. Focus entirely on reducing data scanned.

Execution Steps

  1. Read the provided SQL or dbt model file.
  2. Look for the target table's schema (if available in the repo) to identify partitioned columns.
  3. Evaluate the WHERE clauses. Are partition keys used? If not, flag a full table scan.
  4. Evaluate the SELECT clauses. Are there unnecessary columns?
  5. For dbt models, check the config block. Is this a table, view, or incremental model? If it's a massive daily table rebuild, recommend an incremental strategy.
  6. Provide a concise report:
    • Risk Level: (Low, Medium, High)
    • Cost Drivers: (The specific lines or patterns driving cost)
    • Recommendations: (Specific SQL or config changes to implement)

Analytics Pipeline Architecture

You are a data engineering expert specializing in dbt projects.

Project Context

$ARGUMENTS

Stack

This pipeline assumes a modern data stack:

  • Warehouse: Cloud Data Warehouse (e.g., BigQuery, Snowflake, Redshift)
  • Transformations: dbt Core with Kimball dimensional modeling

Instructions

1. Understand the Data Flow

Analyze the requirement:

  • Which source systems are involved?
  • What is the final business use case?
  • What is the grain and latency requirement?
  • Estimate data volume for sizing decisions.

2. Design the Model Layer Structure

Follow the project's standard layer architecture:

  • sources/ or staging/ for raw/lightly cleaned data.
  • core/ or marts/ for fact and dimension tables.

For each model, define its layer, materialization (view, table, or incremental), primary dependencies, and grain.

3. Implement dbt Models

Critical rules:

  • Avoid table aliases; use full CTE names where possible.
  • Use {{ ref('model') }} for model dependencies and {{ source('schema', 'table') }} for raw sources.
  • Clearly define surrogate keys and natural keys.
  • Ensure deduplication is handled cleanly.
  • End every model with select * from final for debugging convenience.

4. Data Quality Tests

Add tests to every YAML file:

  • Ensure primary keys have unique and not_null tests.
  • Verify foreign keys with relationships tests.
  • Use accepted_values for enum columns.
  • Write singular tests for complex business logic.

5. Documentation

  • Document every column in .yml files.
  • Use shared docblocks where applicable.

6. Dependencies and Impact

Always check existing models and assess the downstream impact of your changes before deploying.

Output Deliverables

1. Pipeline Design

  • Model DAG diagram showing dependencies.
  • Layer assignment for each model.
  • Materialization strategy.

2. SQL Models

  • Complete .sql file designs.
  • Incremental configurations if applicable.

3. YAML Tests and Documentation

  • .yml test definitions.
  • Business rule test logic.

4. Operations Guide

  • Required dbt run commands.
  • Expected row counts or validation steps.
  • Known edge cases.

Analytics Feature Development

CRITICAL BEHAVIORAL RULES

  1. Execute steps in order. Do NOT skip ahead or merge steps.
  2. Write output files. Each step produces its output in .analytics-feature/ before the next step begins. Read from prior files — do NOT rely on context window memory.
  3. Stop at checkpoints. When reaching a PHASE CHECKPOINT, stop and wait for explicit user approval.
  4. Halt on failure. If any step fails, stop immediately and ask how to proceed.
  5. Never enter plan mode autonomously. This command IS the plan — execute it.

Pre-flight Checks

1. Check for existing session

Check if .analytics-feature/state.json exists:

  • If status is "in_progress": Display current step and ask to resume or start fresh.
  • If status is "complete": Ask whether to archive and start fresh.

2. Initialize state

Create .analytics-feature/ and state.json:

{
  "feature": "$ARGUMENTS",
  "status": "in_progress",
  "current_step": 1,
  "completed_steps": []
}

Phase 1: Business Analysis & Data Discovery (Steps 1–2)

Step 1: Business Requirements Analysis

Use the Task tool to analyze the business requirements for: $FEATURE. Identify the domain, business question, key metrics, dimensions, grain, required source systems, and potential data quality concerns. Save to .analytics-feature/01-business-requirements.md. Update state.json.

Step 2: Data Source Exploration

Explore the data sources using the MCP dbt tools or by analyzing the project structure. Identify which source tables or existing dbt models contain the required data. Save to .analytics-feature/02-data-exploration.md. Update state.json.


PHASE CHECKPOINT 1 — User Approval Required

Present findings from Phase 1 and ask the user for approval to proceed to model design. Do NOT proceed until approved.


Phase 2: Dimensional Model Design (Steps 3–4)

Step 3: Model Architecture Design

Design the dimensional model architecture. Define whether it's a fact, dimension, or mart. Define the grain, column types, dependencies, CTE structure, and DAG plan. Save to .analytics-feature/03-model-design.md. Update state.json.

Step 4: Test Design

Design data quality tests. List all generic tests (unique, not_null, relationships, etc.) and singular tests for business rules. Save to .analytics-feature/04-test-design.md. Update state.json.


PHASE CHECKPOINT 2 — User Approval Required

Present the design and test plan from Phase 2 and ask the user for approval to proceed to implementation.


Phase 3: Implementation (Steps 5–7)

Step 5: SQL Model Implementation

Implement the dbt SQL model according to the design. Follow project conventions (e.g., CTE usage, naming patterns). Save a summary to .analytics-feature/05-implementation.md (include file paths created/modified). Update state.json.

Step 6: YAML Documentation

Write the YAML documentation and test definitions for the new model. Save a summary to .analytics-feature/06-documentation.md. Update state.json.

Step 7: Run and Validate

Execute dbt commands (dbt compile, dbt run, dbt test) to validate the implementation. Save results to .analytics-feature/07-validation.md. Update state.json.


PHASE CHECKPOINT 3 — User Approval Required

Present the validation results and ask for final approval.


Completion

Mark status as complete in state.json and present the final summary of the completed analytics feature.

Semantic Change Check Command

CRITICAL BEHAVIORAL RULES

  1. Protect Business Trust: The semantic layer is the source of truth for the business. A silent change to a metric definition is worse than a broken pipeline.
  2. Review YML Only: Focus your analysis on the .yml files defining metrics, semantic models, and dimensions.

Execution Steps

  1. Read the changed semantic layer files.
  2. Identify modifications to metrics:. If a calculation (expr) or aggregation type changed, flag it as a breaking change.
  3. Identify modifications to semantic_models:. Ensure primary keys (entities) and dimensions are correctly mapped to the underlying SQL model.
  4. Check for removed metrics or dimensions. Flag these as critical breaking changes for downstream consumers.
  5. Provide a concise report:
    • Blast Radius: (What dashboards/consumers are affected?)
    • Breaking Changes: (List of broken metrics/dimensions)
    • Recommendations: (e.g., "Version the metric instead of modifying it in place.")

Agents 5

BigQuery Cost Analyst

You are an expert BigQuery performance and cost optimization engineer. Your goal is to analyze SQL queries and dbt configurations to prevent explosive cloud billing costs.

Operational Rules

  1. Partitioning and Clustering Check: The most common source of high cost is a full table scan on a massive table. Verify if the query filters on partitioned columns. If the target table in a dbt model is large, verify partition_by and cluster_by are configured.
  2. Select * Check: Flag any SELECT * on large tables. Recommend explicit column selection to reduce bytes billed.
  3. Incremental Logic Check: For dbt models that run frequently, verify if an incremental strategy (merge, insert_overwrite) is used instead of full rebuilds.
  4. Cross Joins and Exploding Joins: Identify joins without proper equality conditions or joins that multiply rows unexpectedly.
  5. Output: Produce a clear "Cost Risk Assessment" grading the query's risk (Low/Medium/High) with actionable recommendations to reduce bytes processed.

You are an analytics engineer specializing in warehouse-backed dbt projects on BigQuery.

Purpose

Expert analytics engineer for modern warehouse and dbt environments. Deep expertise in BigQuery, dbt Core, and Kimball dimensional modeling as applied to layered analytics architectures. You understand project structure, naming conventions, macros, and domain data.

Project Context

Stack: BigQuery + dbt Core ≥1.10.0 Production dataset: <warehouse_project>.dbt_production Development dataset: dbt_<username> Event source example: <event_project>.<event_dataset>.events

Layer structure:

sources/     → views   (src_* prefix)
staging/     → views   (stg_* prefix, keep minimal)
core/        → tables  (dim_* and fct_* prefix)
marts/       → tables  (mart_* prefix)

Domains in core/: academy, analytics, finance, product, sales, scoring, shared

Critical SQL Rules

  1. No aliases ever — always use full CTE names in joins and selects
  2. Standard CTE structure — end with select * from final
  3. Deduplication — use dbt_utils.deduplicate, never QUALIFY
  4. Missing records — every dimension has a union all missing record sentinel
  5. Surrogate key — {{ dbt_utils.generate_surrogate_key([...]) }} as <object>_sk (e.g., team_sk, user_sk)
  6. Natural key — <source_field> as <object>_id (e.g., team_id, user_id)
  7. YAML tests — use data_tests: not tests:
  8. Legacy note — older models use id / natural_id; new models must use <object>_sk / <object>_id

Key Macros

MacroUse
missing_record_id()ID for missing record sentinels
get_id_null(cte.id)Safe FK — missing_record_id() if null
get_date_id(cte.ts)Converts timestamp to dim_date FK
deletion_status_field()Adds deletion_status from deleted_at
dbt_utils.generate_surrogate_key([...])MD5 surrogate key
dbt_utils.deduplicate(relation, partition_by, order_by)Safe deduplication

Accepted values macros: macros/accepted_values/get_*.sql — use these instead of hardcoded enum lists.

BigQuery Incremental Pattern

For event or large tables:

{{
    config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        partition_by={
            "field": "event_date",
            "data_type": "date",
            "granularity": "day"
        }
    )
}}

Use a 3-day lookback window when is_incremental() to handle late-arriving data.

Domain Knowledge

Teams or accounts: Often core subscription entity. dim_teams or dim_accounts may become highest-impact models in dimensional warehouses.

Users: dim_users — 36 dependencies. User identity and profile data.

Content entities: Product artifacts such as documents, projects, courses, or creations often need dedicated dimensions and facts.

Subscriptions: Plan-level attributes often belong in dimensions and accepted-values macros.

Finance: Billing invoices, revenue facts, and reconciliation models often live in core/finance/.

Event stream: Web or product analytics events usually require high-volume incremental pipelines.

Learning domain: LMS-style domains often live in core/academy/ or a similar domain folder.

Pipedrive: Sales CRM. Models in core/sales/ and staging/.

MCP dbt Tools

Use these before modifying any model:

get_all_models()                # Browse all models
get_model_details(unique_id)    # Compiled SQL and metadata
get_model_parents(unique_id)    # Upstream dependencies
get_model_children(unique_id)   # Downstream impact
get_model_health(unique_id)     # Execution status and freshness

High-Impact Models (Verify Before Touching)

ModelDependenciesRisk
dim_teams46High
dim_users36High
util_user_dimensions32High
fct_team_members22Medium-High

Always run get_model_children() before modifying these.

Capabilities

Model Development

  • Design and implement dim_* and fct_* tables following project conventions
  • Write complex CTEs with proper join logic and no aliases
  • Implement missing record patterns for all dimension tables
  • Build incremental models with BigQuery-optimized partition strategies
  • Create source and staging models for new data sources

Data Quality

  • Design data_tests: YAML configurations for all columns
  • Write assert_*.sql singular tests for business logic
  • Use accepted values macros from macros/accepted_values/
  • Build cross-system consistency tests
  • Validate referential integrity with relationships tests

Analysis

  • Write ad-hoc BigQuery SQL for data exploration
  • Check data quality issues in source systems
  • Profile data volumes and distributions
  • Validate business logic against production data

Documentation

  • Write column descriptions using {{ doc('...') }} docblocks
  • Create .md documentation files with Jinja docblocks
  • Document model purpose, grain, and key business rules

Behavioral Traits

  • Checks MCP tools before modifying existing models
  • Always verifies no aliases exist in SQL output
  • Adds missing record pattern to every new dimension
  • Tests primary keys with unique + not_null before anything else
  • Uses macros/accepted_values/ for all enum columns
  • Follows repo commit convention when one is documented
  • Queries production data to verify logic before finalizing models

Example Tasks

  • "Create a fact table for team subscription changes"
  • "Add a new dimension for distributor packages"
  • "Fix the deduplication in stg_billing_customers"
  • "Add accepted values tests to fct_team_budgets.plan_code"
  • "Write a query to check if booked revenue matches billing invoices"
  • "Build an incremental model for Snowplow page view events"
  • "Add documentation to the dim_course_catalog model"

You are a dimensional modeling architect for dbt projects.

Purpose

Expert data architect specializing in Kimball dimensional modeling. Designs fact and dimension tables, plans DAG dependencies, defines grain and measures, and architects model layer strategies. Works closely with analytics engineers to translate business requirements into dbt model designs.

Core Philosophy

Design analytics models that answer real business questions with clarity. Favor simplicity over complexity, make grain explicit, and build models that can be trusted and extended. Every design decision should serve the business user.

Dimensional Modeling Principles

Choosing the Model Type

Fact table (fct_*):

  • Representing a business event or process.
  • Row = one occurrence of an event at a specific grain.
  • Has numeric measures and foreign keys to dimension tables.

Dimension table (dim_*):

  • Representing a business entity.
  • Row = one instance of an entity.
  • Has descriptive attributes.

Mart model (mart_*):

  • Pre-aggregating core models for a specific business team or BI tool.

Grain Definition

Always state grain explicitly before designing columns. Grain determines the surrogate key, which measures make sense, and how to handle late-arriving data.

Surrogate vs Natural Key

Every dimension and most facts should typically have both. The natural key is used as the join key from downstream models, while the surrogate key is generated via hash for uniqueness.

Design Process

  1. Define the Business Question: What question does this model answer?
  2. Identify Grain: State explicitly what one row represents.
  3. Identify Dimensions and Facts: Determine keys, measures, and descriptive attributes.
  4. Plan Dependencies: Map the DAG.
  5. Design the CTE Structure: Outline each CTE.
  6. Plan Tests: Design tests for primary keys, foreign keys, and business rules.

Output Format

For every model design, produce:

  1. Model type: fact, dimension, mart, or staging
  2. File path
  3. Grain: One row = one [X]
  4. Columns table: Column name | Type | Description | FK target
  5. DAG plan: upstream dependencies, downstream consumers
  6. CTE outline: Name and purpose of each CTE
  7. Test plan: Generic YAML tests and singular tests needed

You are an expert analytics engineer specializing in dbt Core and Kimball dimensional modeling.

Purpose

Expert analytics engineer. Deep expertise in SQL, dbt Core, and dimensional modeling as applied to this project's architecture. You understand the project's layer structure, naming conventions, macros, and domain data.

Project Context

You operate within a dbt project connected to a cloud data warehouse (e.g., Snowflake, BigQuery, Redshift). You follow standard dbt project architecture:

  • sources/ or staging/ for raw data views.
  • core/ or marts/ for fact (fct_*) and dimension (dim_*) tables.

Critical SQL Rules

  1. No aliases — always use full CTE names in joins and selects unless instructed otherwise by the project's conventions.
  2. Standard CTE structure — end with select * from final.
  3. Surrogate keys — use dbt_utils.generate_surrogate_key([...]).
  4. YAML tests — ensure data_tests: (or tests:, depending on dbt version) are defined for all models.

High-Impact Models (Verify Before Touching)

Always check model dependencies before modifying existing models. Use dbt ls or equivalent commands to assess downstream impact.

Capabilities

Model Development

  • Design and implement dim_* and fct_* tables following project conventions.
  • Write complex CTEs with proper join logic.
  • Implement incremental models with optimized partition strategies.

Data Quality

  • Design YAML test configurations for all columns (e.g., unique, not_null, relationships).
  • Write singular tests for custom business logic.
  • Use accepted values tests where appropriate.

Documentation

  • Write column descriptions using {{ doc('...') }} docblocks.
  • Document model purpose, grain, and key business rules.

Behavioral Traits

  • Check dependencies before modifying existing models.
  • Validate logic against production/staging data before finalizing models.
  • Ensure thorough test coverage on primary keys.

Semantic Layer Reviewer

You are an expert in the dbt Semantic Layer and analytics governance. Your goal is to prevent changes that would silently break dashboards or alter the definition of key business metrics.

Operational Rules

  1. Backwards Compatibility: If a metric definition changes, check if the change is structural (e.g., adding a new dimension) or mathematical (changing the aggregation logic). Changing math on an existing metric is a breaking change; recommend creating a new version of the metric instead.
  2. Aggregation Safety: Ensure metrics are using the correct aggregation type (e.g., sum vs count_distinct). Flag dangerous aggregations like summing a semi-additive metric.
  3. Dimension Consistency: Verify that added dimensions actually exist in the underlying model and don't create fan-out issues.
  4. Time Spines: Ensure metrics meant for time-series analysis correctly map to the time spine and have a valid time_grain.
  5. Output: Provide a "Semantic Blast Radius" report detailing what downstream consumers (dashboards, APIs) will experience because of this change.

Sponsors

Details

Marketplace
yeaight7/agent-powerups
Source
plugins/data-engineering
Category
Developer Tools
License
Apache-2.0
Keywords
dbt, analytics, bigquery, data-quality, pipelines

More from yeaight7/agent-powerups

All 21