dbt Project Scalability AI Prompt

Scale this dbt project to support a growing team and larger data volumes.

Current project size: {{model_count}} models, {{team_size}} engineers
Pain points: {{pain_points}} (slow CI, difficult navigation, merge conflicts, inconsistent standards)
Warehouse: {{warehouse}}

1. Multi-project architecture (dbt mesh):
   Split the monorepo into multiple smaller dbt projects connected via cross-project refs:
   - Platform project: shared staging and dimension models consumed by all
   - Domain projects: finance, marketing, product — each with their own team and release cycle
   - Cross-project ref: {{ ref('platform', 'dim_customers') }}
   Benefits: independent deployments, clear ownership, faster CI (smaller projects)

2. Model governance with groups and contracts:

   Groups (assign ownership):
   groups:
     - name: finance
       owner:
         name: Finance Analytics Team
         email: finance-analytics@company.com

   Model contract (enforce public model schema):
   models:
     - name: fct_revenue
       group: finance
       access: public  # can be referenced from other projects
       config:
         contract:
           enforced: true  # CI fails if schema drifts
       columns:
         - name: revenue_usd
           data_type: numeric

3. Slim CI for large projects:
   - Use dbt state artifacts to run only modified models and their dependents
   - Target: CI time < 10 minutes regardless of project size
   - Store production manifest.json in S3; download in CI as the comparison state

4. Model access levels:
   - private: only accessible within the same group/project
   - protected: accessible from the same project
   - public: can be referenced cross-project
   Enforce: downstream teams can only depend on public models

5. Style guide enforcement:
   - sqlfluff: SQL linter with dbt dialect support
     sqlfluff lint models/ --dialect snowflake
   - pre-commit hooks: run sqlfmt, sqlfluff, and yamllint before every commit
   - Standardized model config template in .dbt/config.yml

Return: dbt mesh architecture design, contract enforcement setup, slim CI configuration, access level policy, and style guide tooling.