Problem Statement
The workflow aims to help a Data Team Lead transform raw source table schemas and business requirements into well‑structured semantic models for dbt projects. It ensures that metrics have a single source of truth, models follow medallion or Kimball principles, and documentation and testing standards are consistently applied. The output includes a semantic model diagram, dbt project structure, metric definitions, and documentation guidelines.
Steps
Ingest Project Inputs
INGESTCollect and parse provided source table schemas, business metric definitions, consumer needs, and naming conventions. Normalize the inputs into a consistent structure for downstream processing.
Analyze Sources and Identify Entities
EXECUTEEvaluate source schemas to determine candidate entities, fact tables, and dimensions. Assess grain, keys, and relationships required for a semantic model following medallion or Kimball principles.
Generate Semantic Model Draft
EXECUTEProduce a structured text-based semantic model diagram outlining facts, dimensions, relationships, and lineage. Ensure naming conventions and architecture principles are applied.
Draft Metric Specifications
EXECUTECreate standardized metric definitions with formulas, grain, filters, and source-of-truth models. Ensure alignment with business rules and plan for metric evolution.
Construct dbt Project Skeleton
EXECUTEProduce a dbt folder and file structure including staging, intermediate, marts, and tests. Apply materialization strategies based on usage patterns and performance needs.
Validate Architecture and Documentation Standards
EXECUTEEvaluate whether outputs adhere to naming conventions, lineage requirements, test coverage expectations, and engineering standards. Flag inconsistencies or risks.
Prompt Configurations
Semantic Model and dbt Structure Generation
You are an analytics engineering lead. Using the provided inputs (source table schemas, business metric definitions, consumer needs, and naming conventions), generate the following: 1. A semantic model diagram in text format describing entities, facts, dimensions, keys, and relationships. 2. A dbt project structure skeleton including staging, intermediate, and marts layers. 3. Metric specifications with calculations, grain, filters, and source models. 4. A documentation outline including lineage, tests, and standards. Requirements: - Follow medallion/kimball design principles. - Apply consistent naming conventions. - Ensure metrics have a single source of truth. - Include materialization recommendations based on usage. - Do not invent source fields; use only provided inputs. - Tone: structured, engineering-focused, standards-driven.
Example Inputs/Outputs
Source schemas: orders(order_id, customer_id, order_date, total_amount), customers(customer_id, name, region). Metric definitions: revenue = sum(total_amount). Consumers: finance dashboard, sales forecasting model. Naming conventions: snake_case, dim_ for dimensions, fct_ for facts.
Semantic Model Diagram: - fct_orders (order_id PK, customer_id FK, order_date, total_amount) - dim_customers (customer_id PK, name, region) - Relationship: fct_orders.customer_id -> dim_customers.customer_id dbt Project Structure: - staging/stg_orders.sql - staging/stg_customers.sql - intermediate/int_orders_enriched.sql - marts/fct_orders.sql - marts/dim_customers.sql - tests/schema.yml Metric Specification: - revenue: sum(total_amount) at order level, sourced from fct_orders. Documentation Outline: - Overview - Source → Staging → Marts lineage - Metric definitions - Test strategy (uniqueness, referential integrity, accepted values) - Naming conventions