How to Build and Test the Gold Layer Using dbt and GitHub

The Gold layer transforms data from a technical format into a reliable resource. The Bronze layer ingests raw data, while the Silver layer standardizes it. In the Gold layer, data gets transformed into business models that define key terms such as "revenue," "active customer," and "churn."

 

The Gold layer is built using dbt and is version-controlled through GitHub. It is validated through automated testing and serves as a contract between the data team and the business.

 

This guide explains how to build the Gold layer using dbt, connect it to GitHub, and implement automated testing.

Let's start by creating the dbt Project.

• To start building the Gold layer, create a new dbt project in your workspace.
• Click "Create New Item," then select "dbt Preview."
• A dialog box will prompt you for a project name.
• Enter a descriptive name related to your business or dataset, like gold_layer_dbt.
• Once you've entered the name, click Create to initialize the project.

Why GitHub Integration Matters

Connecting dbt to GitHub enables secure tracking and reviewing of changes. By creating a GitHub Personal Access Token, dbt can communicate with your repository. This integration makes your transformation logic part of a collaborative workflow, enabling change reviews, history tracking, and rollbacks. 

 

• To generate one, go to github.com, log in to your account, then navigate to Settings.
• Scroll and click Developer Settings, then select Personal Access Tokens and choose Fine-Grained Tokens.
• Click the Generate New Token button.
• In the token details form, name it dbt-gold-layer-access, set a 90-day expiration, and select Only Selected Repositories for your dbt repo.
• For permissions, set Contents to Read & Write and Metadata to Read. Click "Generate Token." GitHub displays your token only once, so copy it immediately and store it securely, as you won't be able to view it again.

In your dbt project screen,

1. click the "Connect to GitHub Project" button. In the dialog box, select "GitHub Source Control" and paste your repository URL into the "GitHub Repository URL" field.
2. Set the authentication method to "GitHub Personal Access Token" and enter your token. Then, click "Next."
3. Choose your branch name (either "main" or "master") and click "Next" again.
4. Then, go to "Data Warehouse Settings," select "gold" from the Schema dropdown, and click "Connect." Your dbt artifact is now seamlessly integrated with your GitHub repository, enhancing your workflow and collaboration.

 

Here is the complete file:  

 

•  dbt_project.yml — the main configuration file

•  models/ — folder where your SQL transformation models live

•  models/gold/ — sub-folder for Gold layer models

•  schema.yml — defines tests and documentation for your models

Running dbt and Populating the Gold Schema

With the infrastructure in place, the transformation pipeline can now run. Executing dbt compiles SQL models, applies transformations to Silver data, and writes curated outputs into the Gold schema.

If issues arise, logs help identify schema mismatches or SQL errors, reinforcing a feedback loop that improves reliability over time.

Designing the Gold Business Models

Two foundational models demonstrate how the Gold layer delivers business value.

The first model aggregates course engagement metrics, calculating total enrollments, total completions, and completion rates. These metrics transform raw activity data into insights about learning effectiveness and user engagement.

 

The second model focuses on quiz performance. By calculating average scores, highest scores, and pass rates, it provides a clear view into assessment quality and learner success. Together, these models illustrate how the Gold layer converts operational data into meaningful KPIs.

 

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

WITH quiz_clean AS (
    SELECT
        id,
        name,
        course,
        TRY_CAST(grade AS FLOAT)        AS grade
    FROM silver.[silver.quiz]
),

attempts_clean AS (
    SELECT
        quiz,
        userid,
        TRY_CAST(sumgrades AS FLOAT)    AS sumgrades
    FROM silver.[silver.quiz_attempts]
)

SELECT
    q.id                                                        AS quiz_id,
    q.name                                                      AS quiz_name,
    q.course                                                    AS course_id,
    q.grade                                                     AS max_grade,
    COUNT(DISTINCT a.userid)                                    AS total_attempts,
    ROUND(AVG(a.sumgrades), 2)                                  AS avg_score,
    MAX(a.sumgrades)                                            AS highest_score,
    ROUND(
        100.0 * SUM(
            CASE 
                WHEN a.sumgrades >= q.grade * 0.5
                THEN 1 ELSE 0
            END
        ) / NULLIF(CAST(COUNT(*) AS FLOAT), 0),
        1
    )                                                           AS pass_rate_pct
FROM quiz_clean q
LEFT JOIN attempts_clean a
    ON q.id = a.quiz
GROUP BY
    q.id, q.name, q.course, q.grade

 

Before Gold models are published, dbt automatically runs tests that validate data quality. These checks ensure key fields are not null, eliminate duplicates, enforce allowed values, and maintain table relationships.

 

By the end of this process, the Gold schema becomes the authoritative source for analytics. The journey from project creation to GitHub integration, version control, transformation execution, and automated testing creates a fully productionized analytics layer.

Dashboards become consistent, KPIs become trusted, and teams gain confidence in the numbers they use every day.

The Gold layer is not just another step in the pipeline — it is the moment data becomes business intelligence.

 

Here is the step-by-step guide. Watch here.