
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-powerupsRun it inside Claude Code after adding the marketplace with /plugin marketplace add yeaight7/agent-powerups.
Contents
Discovered from sourceSkills 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
WHEREfilter on the partition column. _PARTITIONTIMEor_PARTITIONDATEnot in the filter.LIMITused 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
- Top cost hotspots — job ID, user, bytes scanned, estimated USD, query snippet.
- Recurring failure patterns — error reason, count, user, sample query.
- Optimization opportunities:
- Partition pruning gaps.
- Clustering candidates.
- Queries eligible for materialization or caching.
- Scheduled jobs that should be retired or narrowed.
- Quick wins vs larger refactors — flag which optimizations are a one-line WHERE clause fix vs a schema change.
- Engineering summary — technical root causes and remediation steps.
- 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:
relationshipstest - All enum columns:
accepted_valuesusing a macro - Business rules:
dbt_utils.expression_is_trueor singular tests - Missing record sentinel: verify
idtest catches duplicates - Data freshness:
dbt_utils.recencyfor 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-failuresin 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
-
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/ -
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>.sqlis_incremental()is false when the target table does not yet exist or on--full-refresh, so reason through both branches. -
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. -
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
-
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
-
Enumerate changed assets:
git diff --name-status origin/main...HEADClassify each change: models, snapshots, seeds, macros, tests, schema YAML, semantic models, metrics, saved queries, exposures or BI-facing assets.
-
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> -
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
-
Recommend the narrowest meaningful validation path first:
- targeted checks:
dbt build --select <changed_model> - narrow downstream checks:
dbt build --select <changed_model>+1 - 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.
- targeted checks:
Output
- changed assets summary
- likely blast radius
- likely missing checks or weak spots
- recommended validation plan
- 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
| Layer | Prefix | Example |
|---|---|---|
| Sources | src_ | src_app_teams, src_events |
| Staging | stg_ | stg_teams, stg_billing_customers |
| Core dim | dim_ | dim_teams, dim_users |
| Core fct | fct_ | fct_team_members, fct_team_budgets |
| Marts | mart_ | 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
| Macro | Purpose |
|---|---|
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
| Environment | Dataset |
|---|---|
| Production | <project>.dbt_production |
| Development | dbt_<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:
| Model | Edges | Action before change |
|---|---|---|
dim_teams | 46 | get_model_children() |
dim_users | 36 | get_model_children() |
util_user_dimensions | 32 | get_model_children() |
fct_team_members | 22 | get_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 type | Risk level | Action required |
|---|---|---|
| Column rename with alias preserved | Low | Validate alias propagation |
| Description or label change | Low | Confirm no tooling parses descriptions |
| New optional field added | Low | No action unless it changes defaults |
| Filter condition tightened | Medium | Validate that the new filter matches intent |
| Metric formula changed | High | Stakeholder review required |
| Grain changed (e.g., day → week) | High | All downstream rollups must be revalidated |
| Dimension removed | Breaking | Cannot ship without migration plan |
| Join path changed | Breaking | All dependent metrics may be affected |
Analysis Steps
- Summarize what changed technically (field names, types, logic, joins).
- Classify each change using the table above.
- Infer what may have changed semantically — ask: "will this metric return a different number for the same time period?"
- Identify who or what downstream may break: dashboards, saved queries, alerts, exports, downstream dbt models, BI tool semantic layers.
- Flag whether stakeholder sign-off is required before the change is promoted.
Output Format
- Technical change summary — what exactly changed, at the field/model level.
- Business meaning impact — does this change what the number means to a business user?
- Likely reporting or dashboard breakage — specific assets at risk.
- Required validation — what must be confirmed before shipping.
- 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
-
Enumerate what changed:
git diff origin/main...HEAD -- '*.yml' '*.yaml' dbt ls --resource-type metric dbt ls --resource-type semantic_model -
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).
-
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?
-
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" . -
Verify entity mapping. Ensure
entities(primary/foreign keys) match the granularity of the underlying semantic model. -
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 parsepasses 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
exprwithout 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
-
Establish the intended grain before and after the query or change.
-
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
-
Ask the review questions:
- What is the intended grain before and after this query?
- Could this query duplicate or drop rows?
- Has the business definition changed even if the SQL still runs?
- Are there edge cases around nulls, late-arriving data, or time windows?
- What result could look plausible while still being wrong?
-
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
- summary of business-logic risks
- likely semantic changes
- highest-risk joins, filters, or aggregations
- concrete validation checks to run
- 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
- Analyze Static SQL: You cannot run the query to see the bytes billed. You must statically analyze the SQL syntax and dbt
config()blocks. - Focus on Bytes Billed: In BigQuery, cost is driven by data scanned, not compute time. Focus entirely on reducing data scanned.
Execution Steps
- Read the provided SQL or dbt model file.
- Look for the target table's schema (if available in the repo) to identify partitioned columns.
- Evaluate the
WHEREclauses. Are partition keys used? If not, flag a full table scan. - Evaluate the
SELECTclauses. Are there unnecessary columns? - For dbt models, check the
configblock. Is this a table, view, or incremental model? If it's a massive daily table rebuild, recommend an incremental strategy. - 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/orstaging/for raw/lightly cleaned data.core/ormarts/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 finalfor debugging convenience.
4. Data Quality Tests
Add tests to every YAML file:
- Ensure primary keys have
uniqueandnot_nulltests. - Verify foreign keys with
relationshipstests. - Use
accepted_valuesfor enum columns. - Write singular tests for complex business logic.
5. Documentation
- Document every column in
.ymlfiles. - 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
.sqlfile designs. - Incremental configurations if applicable.
3. YAML Tests and Documentation
.ymltest definitions.- Business rule test logic.
4. Operations Guide
- Required
dbt runcommands. - Expected row counts or validation steps.
- Known edge cases.
Analytics Feature Development
CRITICAL BEHAVIORAL RULES
- Execute steps in order. Do NOT skip ahead or merge steps.
- 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. - Stop at checkpoints. When reaching a
PHASE CHECKPOINT, stop and wait for explicit user approval. - Halt on failure. If any step fails, stop immediately and ask how to proceed.
- 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
statusis"in_progress": Display current step and ask to resume or start fresh. - If
statusis"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
- 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.
- Review YML Only: Focus your analysis on the
.ymlfiles defining metrics, semantic models, and dimensions.
Execution Steps
- Read the changed semantic layer files.
- Identify modifications to
metrics:. If a calculation (expr) or aggregation type changed, flag it as a breaking change. - Identify modifications to
semantic_models:. Ensure primary keys (entities) and dimensions are correctly mapped to the underlying SQL model. - Check for removed metrics or dimensions. Flag these as critical breaking changes for downstream consumers.
- 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
- 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_byandcluster_byare configured. - Select * Check: Flag any
SELECT *on large tables. Recommend explicit column selection to reduce bytes billed. - Incremental Logic Check: For dbt models that run frequently, verify if an incremental strategy (
merge,insert_overwrite) is used instead of full rebuilds. - Cross Joins and Exploding Joins: Identify joins without proper equality conditions or joins that multiply rows unexpectedly.
- 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
- No aliases ever — always use full CTE names in joins and selects
- Standard CTE structure — end with
select * from final - Deduplication — use
dbt_utils.deduplicate, neverQUALIFY - Missing records — every dimension has a
union allmissing record sentinel - Surrogate key —
{{ dbt_utils.generate_surrogate_key([...]) }} as <object>_sk(e.g.,team_sk,user_sk) - Natural key —
<source_field> as <object>_id(e.g.,team_id,user_id) - YAML tests — use
data_tests:nottests: - Legacy note — older models use
id/natural_id; new models must use<object>_sk/<object>_id
Key Macros
| Macro | Use |
|---|---|
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)
| Model | Dependencies | Risk |
|---|---|---|
dim_teams | 46 | High |
dim_users | 36 | High |
util_user_dimensions | 32 | High |
fct_team_members | 22 | Medium-High |
Always run get_model_children() before modifying these.
Capabilities
Model Development
- Design and implement
dim_*andfct_*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_*.sqlsingular 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
.mddocumentation 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
- Define the Business Question: What question does this model answer?
- Identify Grain: State explicitly what one row represents.
- Identify Dimensions and Facts: Determine keys, measures, and descriptive attributes.
- Plan Dependencies: Map the DAG.
- Design the CTE Structure: Outline each CTE.
- Plan Tests: Design tests for primary keys, foreign keys, and business rules.
Output Format
For every model design, produce:
- Model type: fact, dimension, mart, or staging
- File path
- Grain: One row = one [X]
- Columns table: Column name | Type | Description | FK target
- DAG plan: upstream dependencies, downstream consumers
- CTE outline: Name and purpose of each CTE
- 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/orstaging/for raw data views.core/ormarts/for fact (fct_*) and dimension (dim_*) tables.
Critical SQL Rules
- No aliases — always use full CTE names in joins and selects unless instructed otherwise by the project's conventions.
- Standard CTE structure — end with
select * from final. - Surrogate keys — use
dbt_utils.generate_surrogate_key([...]). - YAML tests — ensure
data_tests:(ortests:, 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_*andfct_*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
- 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.
- Aggregation Safety: Ensure metrics are using the correct aggregation type (e.g.,
sumvscount_distinct). Flag dangerous aggregations like summing a semi-additive metric. - Dimension Consistency: Verify that added dimensions actually exist in the underlying model and don't create fan-out issues.
- Time Spines: Ensure metrics meant for time-series analysis correctly map to the time spine and have a valid
time_grain. - 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- debugging-diagnosticsv0.6.3
- dev-vitalsv0.6.3
- documentation-systemsv0.6.3
- github-opsv0.6.3
- machine-learning-opsv0.6.3
- mcp-developmentv0.6.3
- memory-optimizationv0.6.3
- quality-gatesv0.6.3
- security-guardrailsv0.6.3
- skill-authoringv0.6.3
- software-engineeringv0.6.3
- spec-driven-developmentv0.6.3
- spec-quality-gatesv0.6.3
- tool-integrationsv0.6.3
- agent-evaluation-labv0.6.3
- agentic-systemsv0.6.3
- codebase-intelligencev0.6.3
- codebase-maintenancev0.6.3
- connected-appsv0.6.3
- context-efficiencyv0.6.3