{
  "markdown": "# dbt for Snowflake Demonstration Project\n\n## 📚 Documentation Navigation\n\n| Document                                           | Description                                                              |\n| -------------------------------------------------- | ------------------------------------------------------------------------ |\n| **[README.md](README.md)**                         | 👈 **You are here** - Project overview, architecture, and feature matrix |\n| **[DBT_SETUP_GUIDE.md](DBT_SETUP_GUIDE.md)**       | Complete installation and setup instructions for all platforms           |\n| **[DBT_BEST_PRACTICES.md](DBT_BEST_PRACTICES.md)** | Implementation guide for dbt modeling best practices                     |\n\n---\n\n## 🚀 Quick Start\n\n**New to dbt?** Check out our comprehensive **[dbt Setup Guide](DBT_SETUP_GUIDE.md)** for detailed installation instructions across all platforms.\n\n**Ready to explore?** This project demonstrates dbt best practices integrated with medallion architecture for comprehensive training.\n\n### Prerequisites\n\n- dbt 2.0 (Fusion) — install: `curl -fsSL https://public.cdn.getdbt.com/fs/install/install.sh | sh -s -- --update`\n- SNOWFLAKE_SAMPLE_DATA (available by default in all Snowflake accounts)\n- [Snowflake Finance & Economics](https://app.snowflake.com/marketplace/data-products/search?search=Finance%20%26%20Economics) (free in Snowflake Data Marketplace)\n\n### Quick Setup\n\n```bash\n# Install dependencies\ndbt deps\n\n# Build the project\ndbt build --full-refresh\n```\n\nFor detailed setup instructions, see **[DBT_SETUP_GUIDE.md](DBT_SETUP_GUIDE.md)**.\n\n### 📋 Quick Command Reference\n\n```bash\n# Essential commands\ndbt deps                       # Install packages\ndbt build                      # Run all models and tests\ndbt build --full-refresh       # Full reload of incremental models\n\n# Selection commands\ndbt build --select modelname   # Run specific model\ndbt build --select +modelname  # Run model and parents\ndbt build --select modelname+  # Run model and children\n\n# Documentation\ndbt docs generate              # Generate documentation\ndbt docs serve                 # Serve documentation locally\n```\n\nSee **[DBT_SETUP_GUIDE.md](DBT_SETUP_GUIDE.md)** for complete command reference and troubleshooting.\n\n## 🔍 Quick Feature Finder\n\nLooking for a specific dbt feature? Jump directly to examples:\n\n**🏗️ Materializations:** [Ephemeral](#materializations) • [Incremental](#materializations) • [Dynamic Tables](#materializations) • [Python Models](#materializations)\n**🧪 Testing:** [dbt_constraints](#-testing-framework) • [Generic Tests](#-testing-framework) • [Singular Tests](#-testing-framework) • [Contracts](#-testing-framework)\n**📊 Advanced:** [Snapshots](#-advanced-dbt-features) • [Exposures](#-advanced-dbt-features) • [Seeds](#-advanced-dbt-features) • [Analyses](#-advanced-dbt-features)\n**🔧 Jinja:** [Advanced Templating](#-jinja--macros) • [Custom Macros](#-jinja--macros) • [Variables](#-jinja--macros) • [Loops](#-jinja--macros)\n**⚙️ Snowflake:** [Streams](#snowflake-specific-features) • [Sequences](#snowflake-specific-features) • [Secure Views](#snowflake-specific-features) • [Warehouses](#snowflake-specific-features)\n**📈 Business:** [Executive Dashboards](#-business-intelligence-integration) • [Customer Analytics](#-business-intelligence-integration) • [TPC-H Benchmarks](#-business-intelligence-integration)\n\n---\n\n## Project Architecture\n\nThis project demonstrates **dbt Best Practices integrated with Medallion Architecture** for comprehensive training:\n\n### 🏆 Integrated Architecture: Best Practices + Medallion\n\n- **Bronze Layer** (`models/bronze/`) - **Staging models** + raw data patterns\n- **Silver Layer** (`models/silver/`) - **Intermediate models** + transformation examples\n- **Gold Layer** (`models/gold/`) - **Marts models** + advanced analytics\n\n### 📚 Complexity-Based Learning Path\n\nEach layer contains three complexity levels with integrated best practices:\n\n#### 🥉 **Bronze Layer: Staging + Raw Patterns**\n\n- **Crawl** (`bronze/crawl/`) - Simple staging models with basic column renaming\n  - `stg_tpc_h__nations`, `stg_tpc_h__regions`, `stg_tpc_h__customers`, `stg_tpc_h__orders`\n- **Walk** (`bronze/walk/`) - Complex staging with composite keys and external sources\n  - `stg_tpc_h__lineitem`, `stg_economic_essentials__fx_rates`, `stg_customers_with_tests`\n- **Run** (`bronze/run/`) - Advanced staging with streams and incremental loading\n  - `customer_cdc_stream`, `stg_orders_incremental`\n\n#### 🥈 **Silver Layer: Intermediate + Transformations**\n\n- **Crawl** (`silver/crawl/`) - Simple data cleaning and standardization\n  - `clean_nations`\n- **Walk** (`silver/walk/`) - Business logic and aggregations\n  - `int_customers__with_orders`, `customer_segments`, `lookup_exchange_rates`\n- **Run** (`silver/run/`) - Complex transformations with advanced features\n  - `int_fx_rates__daily`, `order_facts_dynamic`, `customer_clustering`, `async_bulk_operations`\n\n#### 🥇 **Gold Layer: Marts + Analytics**\n\n- **Crawl** (`gold/crawl/`) - Simple aggregated views and summaries\n  - `dim_current_year_orders`, `dim_current_year_open_orders`, `nation_summary`\n- **Walk** (`gold/walk/`) - Complete business dimensions and analytics\n  - `dim_customers`, `dim_orders`, `customer_insights`, `dim_calendar_day`, `dim_customer_changes`\n- **Run** (`gold/run/`) - High-performance fact tables and advanced analytics\n  - `fct_order_lines`, `executive_dashboard`, `fact_order_line_full_reload`, `fact_order_line_pivot`\n\n#### 🔧 **Other Layer: Utility + Reference Models**\n\n- **Utility Models** (`other/`) - System monitoring and reference implementations\n  - `dbt_query_history`, `dynamic_warehouse_assignment`\n- **Benchmarks** (`other/tpc_h_benchmarks/`) - Industry-standard performance queries\n  - `Q1_FACT_PRICING_SUMMARY_REPORT_QUERY`, `Q2_MINIMUM_COST_SUPPLIER_QUERY`, `Q3_SHIPPING_PRIORITY_QUERY`, `Q4_ORDER_PRIORITY_CHECKING_QUERY`\n\n### 🎯 Best Practices Demonstrated\n\n#### ✅ **Integrated Best Practices by Layer**\n\n**🥉 Bronze (Staging) Best Practices:**\n\n- **One-to-one source relationships**: Each source has exactly one staging model\n- **Standardized naming**: `stg_{source_name}__{table_name}` convention\n- **View materialization**: All staging models as views for optimal performance\n- **Feature organization**: `basic_staging/` (crawl) vs `complex_staging/` (walk)\n- **Complexity assessment**: Most staging is crawl-level (basic column renaming)\n- **Comprehensive testing**: Primary keys, foreign keys, data quality checks\n\n**🥈 Silver (Intermediate) Best Practices:**\n\n- **Business logic isolation**: Complex transformations separated from staging\n- **Feature organization**: `business_logic/` vs `advanced_transformations/`\n- **Ephemeral materialization**: For reusable components (`business_logic/`)\n- **Table materialization**: For complex logic that needs persistence (`advanced_transformations/`)\n- **No direct source references**: Only references staging models\n\n**🥇 Gold (Marts) Best Practices:**\n\n- **Business-ready data**: Final data products for consumption\n- **Feature organization**: `dimensions/` vs `facts/` by data modeling pattern\n- **Proper dependencies**: References staging, intermediate, and other marts only\n- **Clear naming**: `dim_` for dimensions, `fct_` for facts\n- **Incremental materialization**: Large fact tables for scalability\n- **Comprehensive business testing**: Relationship validation, business rule checks\n\n**Key Rules Implemented:**\n\n- ✅ **No Direct Joins to Source**: All models reference staging layer, not sources directly\n- ✅ **Proper Staging Layer**: One-to-one relationship between sources and staging models\n- ✅ **No Source Fanout**: Each source referenced by exactly one staging model\n- ✅ **Proper Model Dependencies**: Clear lineage from staging → intermediate → marts\n- ✅ **Standardized Naming**: Consistent `stg_`, `int_`, `dim_`, `fct_` prefixes\n- ✅ **No Hard-coded References**: All references use `ref()` and `source()` functions\n\n## 🎯 Comprehensive dbt Features Demonstrated\n\n### **🏗️ Materializations** {#materializations}\n\n| Feature                      | Model                         | Location                                                                                                       | Layer     | Description                                          |\n| ---------------------------- | ----------------------------- | -------------------------------------------------------------------------------------------------------------- | --------- | ---------------------------------------------------- |\n| **Ephemeral (Staging)**      | `stg_tpc_h__customers`        | [`bronze/crawl/`](models/bronze/crawl/stg_tpc_h__customers.sql)                                                | 🥉 Bronze | One-to-one source relationship, CTE compilation      |\n| **Ephemeral (Staging)**      | `stg_tpc_h__lineitem`         | [`bronze/walk/`](models/bronze/walk/stg_tpc_h__lineitem.sql)                                                   | 🥉 Bronze | Complex staging with composite keys                  |\n| **Ephemeral (Intermediate)** | `int_customers__with_orders`  | [`silver/walk/`](models/silver/walk/int_customers__with_orders.sql)                                            | 🥈 Silver | Reusable business logic, aggregations                |\n| **Table (Intermediate)**     | `int_fx_rates__daily`         | [`silver/run/`](models/silver/run/int_fx_rates__daily.sql)                                                     | 🥈 Silver | Complex transformations with window functions        |\n| **Table (Dimensions)**       | `dim_customers`               | [`gold/walk/`](models/gold/walk/dim_customers.sql)                                                             | 🥇 Gold   | Business-ready customer dimension                    |\n| **Table (Dimensions)**       | `dim_orders`                  | [`gold/walk/`](models/gold/walk/dim_orders.sql)                                                                | 🥇 Gold   | Business-ready orders dimension                      |\n| **Incremental (Facts)**      | `fct_order_lines`             | [`gold/run/`](models/gold/run/fct_order_lines.sql)                                                             | 🥇 Gold   | High-performance fact table with incremental loading |\n| **Incremental (Advanced)**   | `stg_orders_incremental`      | [`bronze/run/`](models/bronze/run/stg_orders_incremental.sql)                                                  | 🥉 Bronze | Advanced incremental with complex Jinja logic        |\n| **Incremental (Macros)**     | `dim_customers_macro_example` | [`gold/run/incremental_with_macros/`](models/gold/run/incremental_with_macros/dim_customers_macro_example.sql) | 🥇 Gold   | SCD Type 2 with custom macros                        |\n| **Dynamic Table**            | `order_facts_dynamic`         | [`silver/run/`](models/silver/run/order_facts_dynamic.sql)                                                     | 🥈 Silver | Real-time analytics with dynamic tables              |\n| **Python Model (ML)**        | `customer_clustering`         | [`silver/run/`](models/silver/run/customer_clustering.py)                                                      | 🥈 Silver | Machine learning with scikit-learn                   |\n| **Python Model (Async)**     | `async_bulk_operations`       | [`silver/run/`](models/silver/run/async_bulk_operations.py)                                                    | 🥈 Silver | Parallel processing and bulk operations              |\n| **View (Legacy)**            | `dim_calendar_day`            | [`gold/walk/`](models/gold/walk/dim_calendar_day.sql)                                                          | 🥇 Gold   | Calendar dimension with ghost keys                   |\n\n### **🧪 Testing Framework**\n\n| Feature                  | Model/Location                       | Complexity                                                                 | Description                                          |\n| ------------------------ | ------------------------------------ | -------------------------------------------------------------------------- | ---------------------------------------------------- | ----------------------------------------- |\n| **dbt_constraints (PK)** | All dimension models                 | All                                                                        | Primary key constraints with database enforcement    |\n| **dbt_constraints (FK)** | All fact models                      | All                                                                        | Foreign key relationships with referential integrity |\n| **dbt_constraints (UK)** | Various models                       | All                                                                        | Unique key constraints for business rules            |\n| **Generic Tests**        | `test_positive_values`               | [`tests/generic/`](tests/generic/test_positive_values.sql)                 | Walk                                                 | Custom reusable test for positive values  |\n| **Singular Tests**       | `test_customer_balance_distribution` | [`tests/singular/`](tests/singular/test_customer_balance_distribution.sql) | Run                                                  | Specific business rule validation         |\n| **Advanced Testing**     | `fct_order_lines`                    | [`gold/_models.yml`](models/gold/_models.yml)                              | Run                                                  | Complex test combinations and expressions |\n| **Contract Enforcement** | `order_facts_dynamic`                | [`silver/_models.yml`](models/silver/_models.yml)                          | Run                                                  | Column contracts and data types           |\n| **dbt_utils Tests**      | Various models                       | All                                                                        | Unique combinations, accepted values, ranges         |\n\n### **📊 Advanced dbt Features**\n\n| Feature                    | Model/Location                   | Complexity                                                                              | Description |\n| -------------------------- | -------------------------------- | --------------------------------------------------------------------------------------- | ----------- | -------------------------------------------- |\n| **Snapshots (SCD Type 2)** | `DIM_CUSTOMERS_SCD`              | [`snapshots/30_presentation/`](snapshots/30_presentation/DIM_CUSTOMERS_SCD.sql)         | Run         | Historical data tracking with check strategy |\n| **Snapshots (Streams)**    | `DIM_CUSTOMERS_FROM_STREAM`      | [`snapshots/30_presentation/`](snapshots/30_presentation/DIM_CUSTOMERS_FROM_STREAM.sql) | Run         | Change data capture with Snowflake streams   |\n| **Exposures**              | `executive_dashboard_exposure`   | [`exposures/`](exposures/executive_dashboard_exposure.yml)                              | Walk        | BI tool integration and lineage              |\n| **Analyses**               | `customer_cohort_analysis`       | [`analyses/`](analyses/customer_cohort_analysis.sql)                                    | Run         | Exploratory data analysis                    |\n| **Seeds**                  | `dynamic_warehouses`             | [`seeds/`](seeds/dynamic_warehouses.csv)                                                | Walk        | Reference data management                    |\n| **Operations**             | `medallion_architecture_helpers` | [`macros/`](macros/medallion_architecture_helpers.sql)                                  | Run         | Custom dbt operations                        |\n\n### **🔧 Jinja & Macros**\n\n| Feature             | Model/Location              | Complexity                                                    | Description |\n| ------------------- | --------------------------- | ------------------------------------------------------------- | ----------- | ------------------------------------------ |\n| **Advanced Jinja**  | `order_facts_dynamic`       | [`silver/run/`](models/silver/run/order_facts_dynamic.sql)    | Run         | Complex loops, conditionals, and variables |\n| **Custom Macros**   | `snowflake_integration_key` | [`macros/`](macros/snowflake_integration_key.sql)             | Run         | Surrogate key generation                   |\n| **Custom Macros**   | `insert_ghost_key`          | [`macros/`](macros/insert_ghost_key.sql)                      | Run         | Ghost key insertion for dimensions         |\n| **Custom Macros**   | `get_scd_sql`               | [`macros/`](macros/get_scd_sql.sql)                           | Run         | SCD Type 2 SQL generation                  |\n| **Jinja Variables** | `raw_orders_incremental`    | [`bronze/run/`](models/bronze/run/raw_orders_incremental.sql) | Run         | Dynamic SQL with environment variables     |\n| **Jinja Loops**     | `fact_order_line_pivot`     | [`gold/run/`](models/gold/run/fact_order_line_pivot.sql)      | Run         | Dynamic column generation                  |\n\n### **⚙️ Snowflake-Specific Features** {#snowflake-specific-features}\n\n| Feature              | Model/Location           | Complexity                                                                             | Description                          |\n| -------------------- | ------------------------ | -------------------------------------------------------------------------------------- | ------------------------------------ | ------------------------------------- |\n| **Dynamic Tables**   | `order_facts_dynamic`    | [`silver/run/`](models/silver/run/order_facts_dynamic.sql)                             | Run                                  | Real-time materialized views with lag |\n| **Streams**          | `customer_cdc_stream`    | [`bronze/run/`](models/bronze/run/customer_cdc_stream.sql)                             | Run                                  | Change data capture streams           |\n| **Sequences**        | Various dimension models | Run                                                                                    | Auto-incrementing surrogate keys     |\n| **Secure Views**     | `DIM_CUSTOMERS_SHARE`    | [`gold/walk/sensitive_data/`](models/gold/walk/sensitive_data/DIM_CUSTOMERS_SHARE.sql) | Walk                                 | Data sharing with row-level security  |\n| **Transient Tables** | Various models           | All                                                                                    | Optimized storage for temporary data |\n| **Query Tags**       | All models               | All                                                                                    | Query identification and monitoring  |\n| **Warehouses**       | Various models           | All                                                                                    | Dynamic warehouse assignment         |\n\n### **🔄 Development Workflow**\n\n| Feature               | Location                | Complexity | Description                                                             |\n| --------------------- | ----------------------- | ---------- | ----------------------------------------------------------------------- |\n| **Project Structure** | `dbt_project.yml`       | All        | Medallion architecture configuration                                    |\n| **Variables**         | `dbt_project.yml`       | Walk       | Global configuration management                                         |\n| **Environments**      | `profiles.yml.sample`   | Walk       | Multi-environment setup                                                 |\n| **Packages**          | `packages.yml`          | All        | External package management (dbt_constraints, dbt_utils, dbt_artifacts) |\n| **Documentation**     | `DBT_SETUP_GUIDE.md`    | All        | Complete installation and configuration guide                           |\n| **Best Practices**    | `DBT_BEST_PRACTICES.md` | All        | Implementation guide for dbt modeling standards                         |\n| **Hooks (Pre/Post)**  | Various models          | Run        | Custom SQL execution before/after model runs                            |\n| **Grants**            | `dbt_project.yml`       | All        | Automated permission management                                         |\n\n### **📈 Business Intelligence Integration**\n\n| Feature                    | Model/Location            | Complexity                                                                                          | Description |\n| -------------------------- | ------------------------- | --------------------------------------------------------------------------------------------------- | ----------- | ----------------------------------------- |\n| **Executive Dashboard**    | `executive_dashboard`     | [`gold/run/`](models/gold/run/executive_dashboard.sql)                                              | Run         | KPI aggregations for leadership reporting |\n| **Customer Insights**      | `customer_insights`       | [`gold/walk/`](models/gold/walk/customer_insights.sql)                                              | Walk        | Customer analytics and segmentation       |\n| **TPC-H Performance**      | `Q1_FACT_PRICING_SUMMARY` | [`other/tpc_h_benchmarks/`](models/other/tpc_h_benchmarks/Q1_FACT_PRICING_SUMMARY_REPORT_QUERY.sql) | Run         | Industry-standard performance benchmarks  |\n| **Query History Analysis** | `dbt_query_history`       | [`other/`](models/other/dbt_query_history.sql)                                                      | Run         | dbt execution monitoring and optimization |\n\n### **🎓 Training & Learning Path**\n\n| Complexity                 | Focus                                       | Example Models                                | Key Concepts                                       |\n| -------------------------- | ------------------------------------------- | --------------------------------------------- | -------------------------------------------------- |\n| **🥉 Crawl (Beginner)**    | Basic staging, simple transformations       | `stg_tpc_h__customers`, `raw_nations`         | Source relationships, basic SQL, ephemeral models  |\n| **🥈 Walk (Intermediate)** | Business logic, intermediate models         | `int_customers__with_orders`, `dim_customers` | Aggregations, joins, business rules, testing       |\n| **🥇 Run (Advanced)**      | Complex analytics, performance optimization | `fct_order_lines`, `customer_clustering`      | Incremental loading, Python models, advanced Jinja |\n\n## Resources\n\n- Learn more about dbt [in the docs](https://docs.getdbt.com/docs/introduction)\n- Free [on-demand training](https://courses.getdbt.com/)\n- [Additional Packages](https://hub.getdbt.com/)\n- Create PK, UK, and FK in Snowflake using [dbt Constraints](https://github.com/Snowflake-Labs/dbt_constraints)\n- Snowflake Guide - [Accelerating Data Teams with dbt Core & Snowflake](https://quickstarts.snowflake.com/guide/data_teams_with_dbt_core/index.html)\n- Snowflake Guide - [Accelerating Data Teams with dbt Cloud & Snowflake](https://quickstarts.snowflake.com/guide/data_teams_with_dbt_cloud/index.html)\n- Snowflake Guide - [Data Engineering with Apache Airflow, Snowflake & dbt](https://quickstarts.snowflake.com/guide/data_engineering_with_apache_airflow/index.html)\n",
  "bytes": 23421,
  "sha": "2f61ae84d6b180d07ef1e8b2df6073fcf9fc0e88288daa5e6381a3c9fc5be3d4",
  "repo_slug": "sfc-gh-dflippo/snowflake-dbt-demo",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/skl_sfc_gh_dflippo_snowflake_dbt_demo_claude_93ae0de6/readme"
}