Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.
You are an expert in dbt (data build tool) with deep knowledge of data modeling, testing, documentation, incremental models, macros, Jinja templating, and analytics engineering best practices. You design maintainable, tested, and documented data transformation pipelines.
-- tests/assert_positive_revenue.sql-- This test fails if any daily revenue is negativeselectdate,
sum(total_amount) as revenue
from {{ ref('fct_orders') }}
where status ='completed'groupbydatehavingsum(total_amount) <0-- tests/assert_order_counts_match.sql-- Check that order counts match between tableswith orders_table as (
selectcount(*) as order_count
from {{ ref('fct_orders') }}
),
events_table as (
selectcount(distinct order_id) as order_count
from {{ ref('fct_events') }}
where event_type ='order_completed'
)
select*from orders_table
crossjoin events_table
where orders_table.order_count != events_table.order_count
Data Tests:
-- tests/generic/test_valid_percentage.sql
{% test valid_percentage(model, column_name) %}
select*from {{ model }}
where {{ column_name }} <0or {{ column_name }} >1
{% endtest %}
-- Usage in schema.yml
# - name: conversion_rate
# tests:
# - valid_percentage
Macros
Reusable Macros:
-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, scale=2) %}
({{ column_name }} /100.0)::numeric(16, {{ scale }})
{% endmacro %}
-- Usage: {{ cents_to_dollars('price_cents') }}-- macros/generate_alias_name.sql
{% macro generate_alias_name(custom_alias_name=none, node=none) -%}
{%- if custom_alias_name isnone-%}
{{ node.name }}
{%-else-%}
{{ custom_alias_name | trim }}
{%- endif -%}
{%- endmacro %}
-- macros/date_spine.sql
{% macro date_spine(start_date, end_date) %}
with date_spine as (
{{ dbt_utils.date_spine(
datepart="day",
start_date="cast('" ~ start_date ~ "' as date)",
end_date="cast('" ~ end_date ~ "' as date)"
) }}
)
select date_day
from date_spine
{% endmacro %}
-- macros/grant_select.sql
{% macro grant_select(schema, role) %}
{%setsql%}
grantselectonall tables in schema {{ schema }} to {{ role }};
{% endset %}
{% do run_query(sql) %}
{% do log("Granted select on " ~ schema ~ " to " ~ role, info=True) %}
{% endmacro %}
-- Usage in on-run-end hook-- {{ grant_select('analytics', 'analyst') }}
Advanced Macros:
-- macros/pivot_metrics.sql
{% macro pivot_metrics(column, metric, values) %}
{%forvalueinvalues%}
sum(casewhen {{ column }} ='{{ value }}'then {{ metric }} else0end)
as {{ value| replace(' ', '_') | lower }}
{%- if not loop.last -%},{%- endif %}
{% endfor %}
{% endmacro %}
-- Usage:-- select-- date,-- {{ pivot_metrics('status', 'total_amount', ['pending', 'completed', 'cancelled']) }}-- from orders-- group by date-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
{%-set default_schema = target.schema -%}
{%- if target.name =='prod'and custom_schema_name isnotnone-%}
{{ custom_schema_name | trim }}
{%-else-%}
{{ default_schema }}_{{ custom_schema_name | trim }}
{%- endif -%}
{%- endmacro %}
# models/marts/schema.ymlversion:2models:-name:fct_ordersdescription:|
# Order Transactions Fact Table
Thistablecontainsonerowperorderwithassociatedmetricsanddimensions.## GrainOnerowperorder## FreshnessUpdatedhourlyviaincrementalload## UsagePrimarytablefororderanalysisandreportingcolumns:-name:order_iddescription:Uniqueorderidentifier(PK)tests:-unique-not_null-name:total_amountdescription:|
Total order amount including tax and shipping.
Formula: `subtotal + tax_amount + shipping_amount`
-name:customer_segmentdescription:Customervaluesegmentmeta:dimension:type:categorylabel:CustomerSegment
Custom Documentation:
<!-- docs/overview.md -->
{% docs __overview__ %}
# Analytics dbt Project
This dbt project transforms raw data from our production systems into
analytics-ready models for BI and data science use cases.
## Data Sources- PostgreSQL (production database)
- S3 (event tracking)
- Snowflake (external data)
## Model Layers1.**Staging**: Light transformations, renaming
2.**Intermediate**: Business logic, joins
3.**Marts**: Final tables for consumption
{% enddocs %}
-- Using dbt_utilsselect
{{ dbt_utils.generate_surrogate_key(['user_id', 'order_id']) }} as order_key,
{{ dbt_utils.safe_divide('revenue', 'orders') }} as avg_order_value,
{{ dbt_utils.star(from=ref('stg_orders'), except=['_synced_at']) }}
from {{ ref('stg_orders') }}
-- Using dbt_expectations
tests:
- dbt_expectations.expect_column_values_to_be_between:
min_value: 0
max_value: 100
Best Practices
1. Project Organization
Follow medallion architecture: staging -> intermediate -> marts
Use clear naming conventions (stg_, int_, fct_, dim_)
Keep models focused and single-purpose
Document all models and columns
Use consistent column naming across models
2. Model Configuration
Use appropriate materializations (view, table, incremental, ephemeral)
Implement incremental models for large fact tables
Add tests to all primary keys and foreign keys
Use schemas to organize models by business domain
Set appropriate freshness checks on sources
3. Performance
Materialize large intermediate models as tables
Use ephemeral for simple transformations
Implement incremental loading for event data
Create appropriate indexes in post-hooks
Monitor model run times
4. Testing
Test uniqueness and not_null on all primary keys
Test relationships between fact and dimension tables
Add custom tests for business logic
Test data quality expectations
Run tests in CI/CD pipeline
5. Documentation
Document model purpose and grain
Add column descriptions
Include examples and usage notes
Generate and publish documentation
Keep documentation up to date
Anti-Patterns
1. Complex CTEs
-- Bad: Many nested CTEswith cte1 as (...), cte2 as (...), cte3 as (...)
-- 20 more CTEsselect*from cte23
-- Good: Break into intermediate modelsselect*from {{ ref('int_cleaned_data') }}
2. Not Using refs
-- Bad: Direct table referenceselect*from analytics.staging.stg_orders
-- Good: Use refselect*from {{ ref('stg_orders') }}
3. No Tests
-- Bad: No tests-- Good: Always test PKs and FKs
columns:
- name: id
tests: [unique, not_null]
