> ## Documentation Index
> Fetch the complete documentation index at: https://handbook.pragunbhutani.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Dimension Tables
So far in our data modeling journey, we have:
1. Defined the schema of the source tables.
2. Created a staging layer to clean and rename the columns.
3. Created snapshots of the staging tables to track changes over time.
We shall now begin creating our dimension tables. **Dimensions** are descriptive attributes that provide context to facts.
They are typically used for filtering and grouping data. Examples of dimensions include time, location, and product.
When you track the changes to a dimension over time, it is known as a **Slowly Changing Dimension**.
Our dimension tables follow the following naming convention:
* `dim_` for simple dimensions.
* `dim__history` for slowly changing dimensions.
You can find all the code for creating these dimension tables in the `models/core/dimensions/` directory
of the [example-dbt-project](https://github.com/pragunbhutani/example-dbt-project/tree/main/models/core/dimensions).
## Creating Dimension Tables
### dim\_users\_history
#### Specification
We would like our `dim_users_history` table to have the following columns.
Note that our `user_id` is no longer a primary key in this table.
This is because since we can now have multiple rows for each user, each row representing a different version of the user's history,
the `user_id` is no longer unique.
Instead, we have a `dbt_scd_id` column that acts as the primary key.
We have also added a column called `is_current` that indicates whether a row is the current version of the user.
| Column Name | Type | Description |
| --------------- | --------- | ----------------------------------------------- |
| dim\_users\_key | PK | Primary Key |
| user\_id | FK | Foreign Key referencing user\_id in users table |
| user\_name | varchar | Name of the user |
| user\_email | varchar | Email address of the user |
| latitude | float | Latitude of the user's location |
| longitude | float | Longitude of the user's location |
| created\_at | timestamp | Time when the user was created |
| updated\_at | timestamp | Time when the user was last updated |
| valid\_from | timestamp | Time when the record became valid |
| valid\_to | timestamp | Time when the record ceased to be valid |
| is\_current | boolean | Indicates whether the record is current |
#### Model
The following dbt model creates a slowly changing dimension table for the `users` table.
```sql theme={null}
with
--
source as (
select * from {{ ref('snapshot_stg_users') }}
),
--
renamed as (
select
dbt_scd_id as dim_users_key,
user_id,
user_name,
user_email,
latitude,
longitude,
created_at,
updated_at,
dbt_valid_from as valid_from,
dbt_valid_to as valid_to,
(valid_to is null) as is_current
from
source
)
--
select * from renamed
```
### dim\_merchants\_history
#### Specification
We would like our `dim_merchants_history` table to have the following columns:
| Column Name | Type | Description |
| ------------------- | --------- | ------------------------------------------------------- |
| dim\_merchants\_key | PK | Primary Key |
| merchant\_id | FK | Foreign Key referencing merchant\_id in merchants table |
| merchant\_name | varchar | Name of the merchant |
| latitude | float | Latitude of the merchant's location |
| longitude | float | Longitude of the merchant's location |
| created\_at | timestamp | Time when the merchant was created |
| updated\_at | timestamp | Time when the merchant was last updated |
| valid\_from | timestamp | Time when the record became valid |
| valid\_to | timestamp | Time when the record ceased to be valid |
| is\_current | boolean | Indicates whether the record is current |
#### Model
The following dbt model creates a slowly changing dimension table for the `merchants` table.
```sql theme={null}
with
--
source as (
select * from {{ ref('snapshot_stg_merchants') }}
),
--
renamed as (
select
dbt_scd_id as dim_merchants_key,
merchant_id,
merchant_name,
latitude,
longitude,
created_at,
updated_at,
dbt_valid_from as valid_from,
dbt_valid_to as valid_to,
(valid_to is null) as is_current
from
source
)
--
select * from renamed
```
### dim\_items\_history
#### Specification
We would like our `dim_items_history` table to have the following columns:
| Column Name | Type | Description |
| ---------------- | --------- | ----------------------------------------------- |
| dim\_items\_key | PK | Primary Key |
| item\_id | FK | Foreign Key referencing item\_id in items table |
| item\_name | varchar | Name of the item |
| item\_category | varchar | Category of the item |
| base\_price\_usd | float | Base price of the item in USD |
| created\_at | timestamp | Time when the item was created |
| updated\_at | timestamp | Time when the item was last updated |
| valid\_from | timestamp | Time when the record became valid |
| valid\_to | timestamp | Time when the record ceased to be valid |
| is\_current | boolean | Indicates whether the record is current |
#### Model
The following dbt model creates a slowly changing dimension table for the `items` table.
```sql theme={null}
with
--
source as (
select * from {{ ref('snapshot_stg_items') }}
),
--
renamed as (
select
dbt_scd_id as dim_items_key,
item_id,
item_name,
item_category,
base_price_usd,
created_at,
updated_at,
dbt_valid_from as valid_from,
dbt_valid_to as valid_to,
(valid_to is null) as is_current
from
source
)
--
select * from renamed
```
### dim\_promotions\_history
#### Specification
We would like our `dim_promotions_history` table to have the following columns:
| Column Name | Type | Description |
| ------------------------------- | --------- | --------------------------------------------------------- |
| dim\_promotions\_key | PK | Primary Key |
| promotion\_id | FK | Foreign Key referencing promotion\_id in promotions table |
| item\_id | FK | Foreign Key referencing item\_id in items table |
| discount\_percentage\_as\_float | float | Percentage discount applied to the item |
| created\_at | timestamp | Time when the promotion was created |
| updated\_at | timestamp | Time when the promotion was last updated |
| valid\_from | timestamp | Time when the record became valid |
| valid\_to | timestamp | Time when the record ceased to be valid |
| is\_current | boolean | Indicates whether the record is current |
The following dbt model creates a slowly changing dimension table for the `promotions` table.
```sql theme={null}
with
--
source as (
select * from {{ ref('snapshot_stg_promotions') }}
),
--
renamed as (
select
dbt_scd_id as dim_promotions_key,
promotion_id,
item_id,
discount_percentage,
created_at,
updated_at,
dbt_valid_from as valid_from,
dbt_valid_to as valid_to,
(valid_to is null) as is_current
from
source
)
--
select * from renamed
```
## Next Steps
We have now created our dimension tables. In the next section, we shall create our fact tables.