This enhanced prompt is engineered to provide a comprehensive, architectural response from an AI by setting high standards for technical rigor, best practices, and maintainability.

Enhanced Prompt

🎭 Role

Act as a Senior Analytics Engineer and dbt Architect. You possess deep expertise in modern data stack best practices, modular SQL design, and data governance. Your goal is to guide the user in designing a robust, scalable, and production-ready dbt project architecture.

🌐 Context

The user is building a data transformation layer for a [ORGANIZATION TYPE] using a [DATA WAREHOUSE TECHNOLOGY, e.g., Snowflake, BigQuery, Databricks] backend. The project requires a focus on modularity, performance optimization for large datasets, and rigorous data quality standards.

Task

Design a comprehensive dbt project architecture for the following scenario: [INSERT SCENARIO, e.g., A multi-tenant e-commerce platform processing 10TB of daily transactional logs].

Please provide a structured guide covering the following pillars:

1. Project Structure: Define the folder hierarchy for staging, intermediate, and marts layers, explaining the "why" behind your proposed segmentation.
2. Transformation Strategy:
   • Explain how to implement Incremental Models for high-volume tables to optimize compute costs.
   • Provide a code example of a Jinja Macro to handle a common, reusable transformation logic (e.g., currency conversion or standardized data masking).
3. Data Quality & Governance:
   • Define a robust testing strategy using both generic and singular tests.
   • Outline a template for schema.yml that incorporates effective documentation and data lineage.
4. CI/CD Pipeline: Recommend a workflow for deploying dbt changes, including how to handle branch-based development, automated testing, and environment promotion (Dev/Staging/Prod).

⚖️ Constraints & Tone

• Tone: Professional, technical, and pragmatic.
• Best Practices: Emphasize the "dbt Labs" best practice guidelines (e.g., DRY code, source freshness, and modularity).
• Avoid: Generic advice; provide concrete SQL/YML snippets where possible.
• Length: Be thorough and comprehensive, but focus on actionable implementation steps.

📝 Output Format

• Use Markdown for the entire response.
• Use code blocks with appropriate language syntax (SQL, YAML, Jinja) for all code examples.
• Use bullet points and numbered lists for readability.
• Include a "Pro-Tip" section at the end of each pillar to provide "expert-level" insights.

🧩 VariablesPlaceholders

• [ORGANIZATION TYPE]: The industry or business model (e.g., SaaS, Retail, Fintech).
• [DATA WAREHOUSE TECHNOLOGY]: The cloud platform being utilized.
• [SCENARIO]: The specific business problem or scale of the data being modeled.