How to Build Fact and Dimension Tables in dbt

📘 Introduction

In dbt, the Gold layer is where cleaned data becomes useful for analytics. This is where you usually create business-ready models such as dimension tables and fact tables.

In this tutorial, you will learn how to build fact and dimension tables in dbt with a simple university example. We will use the Medallion Architecture: Bronze contains source definitions in `models/01_bronze/sources.yml` for `schema: 01_bronze` for raw tables, Silver contains cleaned dbt models, and Gold contains business-ready models in `03_gold` dim and fact tables.

We will create two dimension tables, dim_course and dim_student, and one fact table, fact_enrollment. Each Gold model gets its own YAML file directly next to the model.

💡 What are fact and dimension tables?

A dimension table describes a business entity. In our example, a course and a student are dimensions because they describe things we want to analyze.

• dim_course describes courses.
• dim_student describes students.

A fact table describes an event or measurement. In our example, an enrollment is a fact because it records that a student enrolled in a course.

• fact_enrollment contains one row per enrollment event.
• It connects to dim_course and dim_student through keys.
• It can contain measures such as enrollment_count.

🧭 What are we implementing?

We will start with cleaned Silver models and create Gold models from them:

Bronze sources.yml -> Silver cleaned dbt models -> Gold dim and fact tables

The key idea is to keep the grain clear:

• dim_course: one row per course
• dim_student: one row per student
• fact_enrollment: one row per enrollment event

✅ Prerequisites

☑️ A working dbt project
☑️ Raw course, student, and enrollment data already loaded into your data warehouse or local database
☑️ Raw source tables already defined in models/01_bronze/sources.yml
☑️ Silver models named course_cleaned, course_mapping_cleaned, student_cleaned, and enrollment_cleaned already exist
☑️ Each Silver model already has its own YAML file
☑️ Basic SQL knowledge
☑️ A data warehouse or local database adapter already configured for dbt

🗂️ Current project structure

Before creating the Gold layer, the relevant model structure looks like this:

models/
├── 01_bronze/
│   └── sources.yml
├── 02_silver/
│   ├── course_cleaned.sql
│   ├── course_cleaned.yml
│   ├── course_mapping_cleaned.sql
│   ├── course_mapping_cleaned.yml
│   ├── student_cleaned.sql
│   ├── student_cleaned.yml
│   ├── enrollment_cleaned.sql
│   └── enrollment_cleaned.yml
└── 03_gold/

🎓 Want to go deeper with dbt?

If you want to understand Bronze, Silver, and Gold layers in more detail, the Academy and the dbt book go deeper into hands-on Medallion Architecture projects with practical examples.

If you’d like to dive deeper into dbt (data build tool), our book Building Modern Data Pipelines with dbt: From Raw Data to Gold Standard with the Medallion Architecture provides a hands-on guide to designing modern data pipelines. It covers dbt’s core concepts and best practices, including building Bronze, Silver, and Gold layers with the Medallion Architecture. It also serves as a hands-on study guide for the dbt Analytics Engineering Certification.

View on Amazon