> For the complete documentation index, see [llms.txt](https://docs.datasuite.vn/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.datasuite.vn/data-platform/index/development/dbt-modeling-guide.md).

# DBT Modeling Guide

This guide covers creating dimensional models using DBT, including fact tables, dimension tables, and star schema design for the AdventureWorks dataset.

## Dimensional Model for Sale Processes

### Overview

| Fact Table | Dim Date | Dim Territory | Dim Product |
| ---------- | -------- | ------------- | ----------- |
| fact\_sale | x        | x             | x           |

### Star Schema Design

```plantuml
@startuml
!theme plain

entity fact_sale {
  * sale_key : bigint <<PK>>
  --
  * date_key : int <<FK>>
  * territory_key : int <<FK>>
  * product_key : int <<FK>>
  --
  order_qty : decimal
  unit_price : decimal
  line_total : decimal
  sub_total : decimal
  tax_amt : decimal
  total_due : decimal
  sales_order_id : int
}

entity dim_date {
  * date_key : int <<PK>>
  --
  date : date
  year : int
  quarter : int
  month : int
  day : int
  day_of_week : int
  week_of_year : int
  is_weekend : boolean
  fiscal_year : int
  fiscal_quarter : int
}

entity dim_territory {
  * territory_key : int <<PK>>
  --
  territory_id : int
  territory_name : varchar(50)
  country_region_code : varchar(3)
  territory_group : varchar(50)
  region_name : varchar(50)
}

entity dim_product {
  * product_key : int <<PK>>
  --
  product_id : int
  product_name : varchar(50)
  product_number : varchar(25)
  product_category : varchar(50)
  product_subcategory : varchar(50)
  standard_cost : decimal
  list_price : decimal
  is_current : boolean
}

dim_date ||--o{ fact_sale
dim_territory ||--o{ fact_sale
dim_product ||--o{ fact_sale

@enduml
```

## Building Dimensional Models

### Fact Table Creation

Create `models/marts/core/fact_sale.sql`:

```sql
{{ config(
    materialized='incremental',
    unique_key='sale_key',
    on_schema_change='fail'
) }}

WITH sales_with_keys AS (
    SELECT 
        {{ dbt_utils.surrogate_key(['soh.salesorderid', 'sod.salesorderdetailid']) }} as sale_key,
        {{ get_date_key('soh.orderdate') }} as date_key,
        {{ get_territory_key('soh.territoryid') }} as territory_key,
        {{ get_product_key('sod.productid') }} as product_key,
        
        -- Measures
        sod.orderqty,
        sod.unitprice, 
        sod.linetotal,
        soh.subtotal,
        soh.taxamt,
        soh.totaldue,
        
        soh.salesorderid,
        soh.orderdate
    FROM {{ ref('stg_sales_order_header') }} soh
    INNER JOIN {{ ref('stg_sales_order_detail') }} sod
        ON soh.salesorderid = sod.salesorderid
)

SELECT * FROM sales_with_keys
{% if is_incremental() %}
    WHERE orderdate > (SELECT MAX(orderdate) FROM {{ this }})
{% endif %}
```

### Dimension Tables

**Date Dimension** (`models/marts/core/dim_date.sql`):

```sql
{{ config(materialized='table') }}

SELECT 
    {{ dbt_utils.surrogate_key(['date']) }} as date_key,
    date,
    EXTRACT(year FROM date) as year,
    EXTRACT(quarter FROM date) as quarter,
    EXTRACT(month FROM date) as month,
    EXTRACT(day FROM date) as day,
    EXTRACT(dow FROM date) as day_of_week,
    EXTRACT(week FROM date) as week_of_year,
    CASE WHEN EXTRACT(dow FROM date) IN (0,6) THEN TRUE ELSE FALSE END as is_weekend,
    -- Add fiscal year logic as needed
    EXTRACT(year FROM date) as fiscal_year,
    EXTRACT(quarter FROM date) as fiscal_quarter
FROM {{ ref('date_spine') }}
```

**Product Dimension** (`models/marts/core/dim_product.sql`):

```sql
{{ config(materialized='table') }}

SELECT 
    {{ dbt_utils.surrogate_key(['productid']) }} as product_key,
    productid,
    productname,
    productnumber,
    pc.name as product_category,
    psc.name as product_subcategory,
    standardcost,
    listprice,
    productline,
    size,
    weight,
    color,
    TRUE as is_current,
    current_timestamp() as effective_from,
    NULL as effective_to
FROM {{ ref('stg_products') }} p
LEFT JOIN {{ ref('stg_product_categories') }} pc 
    ON p.productcategoryid = pc.productcategoryid
LEFT JOIN {{ ref('stg_product_subcategories') }} psc
    ON p.productsubcategoryid = psc.productsubcategoryid
```

## Testing and Documentation

### Data Quality Tests

Create `models/marts/schema.yml`:

```yaml
version: 2

models:
  - name: fact_sale
    description: "Central sales transaction fact table"
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - date_key
            - territory_key
            - product_key
            - salesorderid
    columns:
      - name: sale_key
        tests:
          - unique
          - not_null
      - name: total_due
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 1000000

  - name: dim_date
    columns:
      - name: date_key
        tests:
          - unique
          - not_null
```

## Macros and Utilities

Create helper macros in `macros/` directory:

**`macros/get_date_key.sql`:**

```sql
{% macro get_date_key(date_column) %}
    toInt32(toYYYYMMDD({{ date_column }}))
{% endmacro %}
```

## Next Steps

Continue to [Testing & Validation](/data-platform/index/development/testing-validation.md) to ensure data quality.