# 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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.datasuite.vn/data-platform/index/development/dbt-modeling-guide.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.