Generate dbt Models with Best Practices

dbt Model Generator develops data transformation models with an emphasis on readability, maintainability, documentation, and performance. It organizes transformations into staging, intermediate, mart, and snapshot models so that raw-data cleanup, complex business logic, analytics datasets, and historical dimension tracking have distinct roles.

Naming guidance uses source and entity identifiers for staging models, entity and description for intermediate models, and business area and entity for marts. Double underscores separate these parts. This connects model names to the layer and business purpose of the transformation.

Configuration examples define materializations and schemas at the project level and override settings within individual models. They cover views, tables, incremental materialization, unique keys, schema synchronization, partitioning, and clustering. A finance subdirectory example gives incremental models their own configuration.

Use this skill when creating a dbt transformation pipeline or improving an existing model's structure and update behavior. It supports both straightforward staging models and marts that join customers with order-derived metrics.

Incremental strategy selection depends on the data: append for immutable events, merge for mutable dimensions, delete+insert for partition rebuilds, and insert_overwrite for date-partitioned datasets. The examples also filter incremental input using the latest stored update timestamp and limit merge updates to selected columns.

Optimization guidance includes early filtering in staging, readable CTEs, dynamic filtering through variables, and database-specific clustering and partitioning. These choices are tied to the model and dataset rather than presented as a single materialization suitable for every transformation.

Inputs include raw source tables, existing model references, business rules, identifiers, update timestamps, and materialization requirements. Outputs include SQL models, project or model configuration, schema documentation, and column-level tests.

The staging example standardizes names and casts dates and timestamps. The mart example aggregates completed orders into total orders, lifetime value, and first and last order dates, then derives value segments and joins those metrics with customer information.

Schema examples document the customer identifier, lifetime value, and customer segment. Tests include uniqueness, non-null requirements, accepted values, and a non-negative accepted range. This places the expected meaning of the resulting columns alongside their validation rules.

The skill uses dbt source declarations for raw data and model references for transformation dependencies. Examples also include execution hooks and a reporting-role grant.

select * from {{ ref('staging_model') }}

Macro examples use dbt_utils for surrogate keys, pivoting, and retrieving column values, alongside a custom phone-number standardization macro. Together, these patterns support reusable transformations while keeping model lineage explicit.