dbt Model Architecture Design

What this prompt does

This prompt makes the AI design a layered dbt project architecture for an analytics domain. You provide [business_domain], the [raw_schema] the data lands in, [source_count], and the [warehouse_type], and the model lays out sources, staging, intermediate, and mart models following dbt best practices. Sources are declared in schema.yml with freshness checks (warn_after: [freshness_warn], error_after: [freshness_error]) and loaded_at_field configured.

The structure works because the layering keeps each model's job small and its tests meaningful. Staging models (stg_) do only renaming, casting, and light cleaning as views; intermediate models (int_) hold cross-source business logic — joins, dedup, SCD2 — materialized per [intermediate_materialization]; marts (fct_/dim_) follow [dimensional_model] with documented grain. It adds tests for [business_rules], environment-specific schemas with [prod_schema] and [tag_categories] for selective runs, and a macro for [reusable_logic]. Because each layer has one responsibility, runs stay selective and tests stay honest.

When to use it

• You're starting a new dbt project and want a clean staging/intermediate/marts structure rather than a flat pile of models.
• You need source freshness checks and tests wired in from day one.
• Your business logic spans multiple source systems and you want it isolated in intermediate models.
• You're modeling facts and dimensions and want documented grain on each mart.
• You want dev and prod to use different schemas and tags to run subsets of the project.
• You have logic (currency conversion, date lookups) repeated across models and want it pinned in one macro.

Example output

Expect a project blueprint: a folder layout, a sample sources schema.yml with freshness config, example stg_, int_, and fct_/dim_ model SQL skeletons with their materializations, a tests section covering unique/not_null/accepted_values plus custom singular tests for your business rules, a dbt_project.yml with environment schemas and tags, and a reusable macro for [reusable_logic].

Pro tips

• Keep staging models thin — one per source table, views only. Pushing joins or business logic into stg_ models is the most common way the layering erodes.
• Choose [intermediate_materialization] per model weight: ephemeral for light transforms, table for heavy joins, as the default suggests.
• Write the [business_rules] tests as real assertions (MRR never negative, end_date >= start_date) so a violation fails the run instead of silently passing.
• Set [freshness_warn] and [freshness_error] from your actual load cadence; thresholds tighter than reality will page you on normal delays.
• Define the [reusable_logic] macro early — currency or date logic copy-pasted across models is exactly where projects quietly drift out of sync.
• Use [tag_categories] deliberately so selective runs (daily, finance) actually map to how you schedule, not just decoration.
• Materialize staging as views unless a downstream model reads it repeatedly; turning a stg_ model into a table prematurely adds build time for no benefit.
• Document the grain on every mart so two analysts querying fct_ and dim_ models share the same definition of what one row represents.