Insurance Claims Pipeline with dbt

Introduction

30 Second Summary

Every time you file an insurance claim after a car accident, a data pipeline processes your information through layers of cleaning, validation, and analysis. The speed and accuracy of that pipeline determines how quickly you get paid.

In this project, you will build a production-style insurance claims pipeline on Databricks. You will transform raw claims data through a Medallion architecture and produce analytical outputs in a Tableau dashboard.

What You'll Build

You will build an end-to-end data engineering pipeline on Databricks using PySpark, dbt, Delta Lake, and Tableau.

By the end of this project, you'll have:

• A Databricks workspace with Delta Lake tables organized in a Medallion architecture (Bronze, Silver, and Gold layers).
• A complete dbt project with SQL transformation models, a star schema data mart, automated data quality tests, and CI/CD via GitHub Actions.
• An interactive Tableau dashboard published to a shareable public URL.
• A production-ready pipeline with incremental loading and scheduled orchestration (Secret Mission).

Are there any prerequisites?

You should be comfortable with Python, SQL, Git, and the command line. You'll also need VS Code, Tableau Desktop Public Edition, and a Kaggle account set up before starting.

Set Up Databricks and Local Environment

Before you build the insurance claims pipeline, you need two things in place. A cloud workspace where your data will live, and a local project that connects to it and runs transformations.

In this step, you will sign up for Databricks Free Edition, install dbt with the Databricks adapter, and verify the two can talk to each other.

In this step, get ready to:

• Sign up for Databricks Free Edition and collect your connection credentials.
• Install dbt-databricks and scaffold the dbt project.
• Verify the Databricks connection and initialize a GitHub repository.

Sign up for Databricks Free Edition

Databricks Free Edition is a perpetually free tier of the Databricks platform. It gives you a full workspace with notebooks, a SQL warehouse, and Unity Catalog for organizing your data.

• Open the Databricks Free Edition signup page in your browser.
• Click **Sign up for Free Edition**.Click Sign up for Free Edition.
• Fill out the registration form with your name, email, and a password.
• Check your email for a verification link, then click it to confirm your account.
• Databricks will provision your workspace. This may take a minute or two.

Once your workspace loads, you need to collect three pieces of information that dbt will use to connect: the host URL, the SQL warehouse HTTP path, and a Personal Access Token (PAT).

Collect your Databricks connection details

Your workspace URL is the first value you need. It tells dbt which Databricks instance to connect to.

• Look at your browser's address bar. Your host URL looks something like dbc-a1b2345c-d6e7.cloud.databricks.com.
• Copy everything after https:// and before any trailing slash. Save this value in a temporary text file.

Next, you need the HTTP path for your SQL warehouse. Databricks automatically creates a Starter Warehouse for you.

• In the left sidebar, click SQL Warehouses (under the SQL section).
• Click the name Starter Warehouse to open its details.
• Click the Connection Details tab.
• Copy the HTTP path value and save it in your text file alongside the host URL.

What is a SQL warehouse?

A SQL warehouse is the compute engine that runs your queries in Databricks. Think of it as a managed server that starts up when you need it and shuts down when you don't. The HTTP path is the unique address that points to your specific warehouse instance.

The last piece is a Personal Access Token (PAT). This is how dbt authenticates with your Databricks workspace from your local machine.

• Click your username in the top-right corner of the workspace.
• Select Settings.
• Click Developer in the left panel.
• Next to Access tokens, click Manage.
• Click Generate new token.
• Enter dbt access in the comment field.
• Leave the lifetime at the default value, then click Generate.
• Copy the displayed token and save it in your text file. You will not be able to see this token again after closing the dialog.
• Click Done.

Pro tip: keep your credentials handy

You should now have three values saved: the host URL, the HTTP path, and the PAT. You will use all three in the next substep when configuring dbt. Do not share your PAT with anyone or commit it to version control.

Set up the local dbt project

dbt runs on your local machine and sends SQL transformations to Databricks for execution. You need a Python virtual environment with the dbt-databricks adapter installed.

• Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
• Type VS Code and press Enter to open it.
• Open the integrated terminal by pressing Ctrl+` (backtick key, same shortcut on both macOS and Windows).

• Move to your Desktop by running this command:

cd ~/Desktop

• Create and activate a Python virtual environment by running these commands:

macOS

python3 -m venv insurance-venv
source insurance-venv/bin/activate

Windows

python -m venv insurance-venv
.\insurance-venv\Scripts\activate

Your terminal prompt should now show (insurance-venv) at the beginning, confirming the virtual environment is active.

Not seeing (insurance-venv) in your prompt?

On Windows, you may need to allow script execution first. Run Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned in PowerShell, then try the activate command again.

On macOS, make sure you have Python 3 installed. Run python3 --version to check.

Help me troubleshoot my Python virtual environment

• Install the dbt-databricks adapter by running:

pip install dbt-databricks==1.11.6

• You should see Successfully installed followed by a list of packages including dbt-databricks-1.11.6 in the output.

What did you just install?

The dbt-databricks package includes dbt Core plus the Databricks-specific adapter. It uses pure Python APIs to connect to your SQL warehouse. No ODBC driver required.

Seeing an installation error?

Make sure your virtual environment is active (you should see (insurance-venv) in your prompt). If you see permission errors, do not use sudo. Deactivate and recreate the virtual environment instead.

Help me fix my dbt-databricks installation

Now you will scaffold the dbt project. The dbt init command creates the project folder and walks you through an interactive setup wizard.

• Initialize your dbt project by running:

dbt init insurance_claims_pipeline

dbt will ask you a series of questions. Enter the following values at each prompt:

• Which database would you like to use? Enter the number next to databricks.
• host: Paste the workspace host URL you saved earlier (without https://).
• http_path: Paste the SQL warehouse HTTP path you saved earlier.
• Authentication type: Select token (use access token).
• token: Paste the Personal Access Token you generated.
• catalog: Enter insurance.
• schema: Enter default.
• threads: Enter 1.

What do these prompts mean?

• The host is the address of your Databricks workspace.
• The http_path tells dbt which SQL warehouse to send queries to.
• The catalog is set to insurance because that is the Unity Catalog you will create in the next step. dbt does not validate it at this stage.
• The schema is a default fallback. Your dbt models will override it with specific schemas like silver and gold later.

• Confirm the project was created by running:

ls insurance_claims_pipeline

You should see folders like models, macros, and a dbt_project.yml file in the output.

Verify the connection and create a GitHub repository

dbt has a built-in command that tests every part of the connection: the host, the HTTP path, the token, and the project configuration.

• Move into your new project directory by running:

cd insurance_claims_pipeline

• Test the connection by running:

dbt debug

You should see a series of checks ending with All checks passed! in green text. This confirms dbt can reach your Databricks workspace and authenticate successfully.

dbt debug showing a connection error?

• Check that your host URL does not include https://. It should be just the domain, like dbc-a1b2345c-d6e7.cloud.databricks.com.
• Verify that your PAT was copied correctly. Go back to Databricks and generate a new token if needed.
• Make sure the Starter Warehouse is running. Go to SQL Warehouses in the Databricks sidebar and click the start icon if the warehouse is stopped.
• Your connection details are stored in ~/.dbt/profiles.yml. Open that file to double-check the values.

Help me debug my dbt Databricks connection

With the connection verified, it is time to put your project under version control with Git.

• Open github.com in your browser.
• Click the + icon in the top-right corner.
• Click New repository.
• Enter insurance-claims-pipeline in the Repository name field.
• Keep Public selected. Leave all checkboxes unchecked (no README, no .gitignore).
• Click Create repository.

GitHub will show a quick setup page with a repository URL. Copy that URL, then go back to your VS Code terminal.

• Initialize Git and push your project by running these commands:

git init
git add .
git commit -m "Initial dbt project setup"
git branch -M main
git remote add origin [[YOUR_REPO_URL="https://github.com/your-username/insurance-claims-pipeline.git"]]
git push -u origin main

What do these commands do?

• git init creates a new Git repository in the current folder.
• git add . stages all the files dbt created.
• git remote add origin links your local repository to the GitHub remote.
• git push uploads your code to GitHub.

• Refresh your GitHub repository page in the browser. You should see your dbt project files including dbt_project.yml and the models folder.

Git push failing with authentication errors?

GitHub no longer accepts passwords for Git operations. You need either a GitHub Personal Access Token or SSH key configured. If prompted for credentials, use your GitHub username and a GitHub PAT (not your Databricks PAT) as the password.

Help me push my project to GitHub

Your local environment is connected to Databricks and your project is tracked in GitHub. Next up, you will ingest raw insurance claims data into a Delta Lake table to build the Bronze layer of your pipeline.

Ingest Raw Data into Delta Lake (Bronze Layer)

Your Databricks workspace and local dbt environment are connected and ready to go. Now it is time to bring data into the picture.

Every production data pipeline starts with raw data ingestion. In the Medallion architecture, this first stop is called the Bronze layer. The Bronze layer stores data exactly as it arrived from the source, with no cleaning or transformations applied. Your job in this step is to get the raw insurance claims CSV into a Delta Lake table so the rest of the pipeline has a reliable starting point.

In this step, get ready to:

• Download the auto insurance claims dataset from Kaggle and upload it to a Unity Catalog volume.
• Write a PySpark notebook that reads the CSV, adds metadata columns, and writes it to a Delta table.
• Verify the Bronze layer table loaded correctly using SQL queries.

Download the dataset and create the Bronze schema

Before you can ingest anything, you need two things: the raw data file and a place to put it in Databricks. You will download a real-world insurance claims dataset from Kaggle, then set up the Unity Catalog structure to store it.

• Go to kaggle.com and log in to your account.
• Search for auto-insurance-claims-data by buntyshah.
• Click the Download button in the top right corner of the dataset page.
• Extract the downloaded .zip file on your computer. You should see a CSV file inside.

What if I see multiple CSV files?

The exact file name depends on the dataset version. Look for the file that contains insurance claims data. You will rename it to insurance_claims.csv in a later step when uploading it to Databricks.

Now create the catalog, schema, and volume structure in your Databricks workspace.

• In your Databricks workspace, click SQL Editor in the left sidebar.
• Create the catalog, schema, and volume by running these SQL statements one at a time:

CREATE CATALOG IF NOT EXISTS insurance;

CREATE SCHEMA IF NOT EXISTS insurance.bronze;

CREATE VOLUME IF NOT EXISTS insurance.bronze.landing;

What do these SQL commands do?

• CREATE CATALOG creates the top-level container called insurance. Think of it like a database server.
• CREATE SCHEMA creates bronze inside that catalog. This is where your raw data tables live.
• CREATE VOLUME creates a managed storage location called landing where you can upload files. Files in a volume are accessible at the path /Volumes/insurance/bronze/landing/.

• Verify all three were created by checking that each statement returned OK with no errors.

Now upload the CSV file to the volume you just created.

• In the left sidebar, click New, then click Add or upload data.
• Click Upload files to a volume.
• Under Destination volume, navigate to insurance > bronze > landing.
• Under Files, click browse and select the CSV file you extracted from the Kaggle download.
• If your CSV file is not already named insurance_claims.csv, rename it to insurance_claims.csv on your computer before uploading.
• Click Upload and wait for the upload to complete.

Upload failing or volume not showing?

• Make sure you ran all three SQL statements (catalog, schema, volume) successfully before trying to upload.
• If the volume does not appear in the destination picker, try refreshing the page and navigating again.
• Databricks Free Edition uses managed volumes only. Do not try to specify an external storage location.

Why can't I find my volume in Databricks?

Write the ingestion notebook

With the CSV file uploaded to your volume, you can now write PySpark code to read it, enrich it with metadata, and write it to a Delta table. You will do this in a Databricks notebook.

• In the left sidebar, click New, then click Notebook.
• Name the notebook 01_bronze_ingestion.
• Make sure the default language is set to Python.
• Paste the following code into the first cell of the notebook:

from pyspark.sql import SparkSession
from pyspark.sql.functions import current_timestamp, lit

# Read CSV from Unity Catalog volume
df = spark.read.option("header", "true") \
    .option("inferSchema", "true") \
    .csv("/Volumes/insurance/bronze/landing/insurance_claims.csv")

# Add metadata columns
df_with_metadata = df \
    .withColumn("_ingested_at", current_timestamp()) \
    .withColumn("_source_file", lit("insurance_claims.csv"))

# Write to Delta table
df_with_metadata.write \
    .format("delta") \
    .mode("overwrite") \
    .saveAsTable("insurance.bronze.raw_claims")

print(f"Loaded {df_with_metadata.count()} rows into insurance.bronze.raw_claims")

What does this code do?

• spark.read.csv(...) reads the CSV file from the volume path. The header option tells Spark the first row contains column names. The inferSchema option tells Spark to automatically detect data types instead of treating everything as strings.
• withColumn("_ingested_at", current_timestamp()) adds a timestamp recording exactly when this data was loaded. This is essential for tracking data freshness and supporting incremental loads later.
• withColumn("_source_file", lit("insurance_claims.csv")) records which file the data came from. Together, these two metadata columns create a data lineage trail.
• write.format("delta").saveAsTable(...) writes the DataFrame as a Delta table in the insurance.bronze schema. Delta Lake adds ACID transactions, time travel, and schema enforcement on top of your data.

• Run the cell by clicking the play button or pressing Shift+Enter.

The cell takes a few seconds to run. When it finishes, you should see a print statement like Loaded 1000 rows into insurance.bronze.raw_claims.

Why does the row count vary?

If you downloaded the original dataset by buntyshah, you should see approximately 1,000 rows. If you downloaded the updated version by thebumpkin, you may see around 9,134 rows. Either version works for this project.

Seeing a FileNotFoundException?

• Double check that your CSV file is named exactly insurance_claims.csv in the volume. The path in the code must match the file name exactly.
• Verify the file was uploaded to insurance > bronze > landing, not to a different volume or schema.
• Check the file path is /Volumes/insurance/bronze/landing/insurance_claims.csv with all lowercase letters. Databricks normalizes volume paths to lowercase.

Help me debug my Bronze ingestion notebook error.

Awesome, I've got everything!

Great. Make sure you have saved your notebook in Databricks.

I'd like to double check the full code

Here is the complete notebook code for your 01_bronze_ingestion notebook:

from pyspark.sql import SparkSession
from pyspark.sql.functions import current_timestamp, lit

# Read CSV from Unity Catalog volume
df = spark.read.option("header", "true") \
    .option("inferSchema", "true") \
    .csv("/Volumes/insurance/bronze/landing/insurance_claims.csv")

# Add metadata columns
df_with_metadata = df \
    .withColumn("_ingested_at", current_timestamp()) \
    .withColumn("_source_file", lit("insurance_claims.csv"))

# Write to Delta table
df_with_metadata.write \
    .format("delta") \
    .mode("overwrite") \
    .saveAsTable("insurance.bronze.raw_claims")

print(f"Loaded {df_with_metadata.count()} rows into insurance.bronze.raw_claims")

Verify the data loaded correctly

The notebook reported a row count, but you should independently verify the data using the SQL editor. This confirms the Delta table is queryable and the data looks correct.

• Click SQL Editor in the left sidebar.
• Run the following query to check the total row count:

SELECT count(*) FROM insurance.bronze.raw_claims;

• Confirm the count matches what your notebook printed (approximately 1,000 rows for the buntyshah dataset).
• Run this query to inspect a sample of the data:

SELECT * FROM insurance.bronze.raw_claims LIMIT 5;

You should see columns like policy_number, age, incident_date, total_claim_amount, and fraud_reported, along with your two metadata columns _ingested_at and _source_file.

Why add metadata columns?

The _ingested_at and _source_file columns are not part of the original dataset. You added them in your notebook to create a data lineage trail. In production pipelines, these columns help answer questions like "when was this data loaded?" and "where did it come from?" They also become critical when you add incremental loading later.

Query returning zero rows?

• Go back to your notebook and make sure the cell completed without errors. Look for the Loaded X rows print output at the bottom of the cell.
• Verify you are querying insurance.bronze.raw_claims (not a different schema or catalog).
• Make sure your SQL warehouse is running. Check the top of the SQL editor for the warehouse status indicator.

My Bronze table query is returning unexpected results.

Your Bronze layer is loaded and verified. Next up, you will use dbt to clean and standardize this raw data into the Silver layer.

Build Staging Models (Silver Layer)

Your Bronze layer is loaded with raw insurance claims data sitting in a Delta Lake table.

Raw data is messy. Column names are inconsistent, data types are wrong, and categorical values have trailing whitespace. The Silver layer in the Medallion architecture is where you clean and standardize all of this using dbt models. In this step, you will create a staging model that transforms the raw claims into analysis-ready data, then add schema tests to validate data quality.

In this step, get ready to:

• Create a dbt source definition pointing to the Bronze layer Delta table.
• Build a staging model that cleans, casts, and standardizes every column.
• Add schema tests to validate data quality and run the full staging build.

Create the source definition and staging model

Before dbt can query your Bronze table, it needs a source definition. This is a YAML file that tells dbt which database, schema, and table to read from.

• In VS Code, create a new folder called staging inside the models folder of your insurance_claims_pipeline project.
• If you see a models/example/ folder left over from dbt init, delete it. You will not need the sample models.
• Create a new file called _sources.yml inside models/staging/ and paste the following code:

version: 2

sources:
  - name: bronze
    database: insurance
    schema: bronze
    tables:
      - name: raw_claims
        description: "Raw auto insurance claims data ingested from Kaggle CSV"
        columns:
          - name: policy_number
            description: "Unique policy identifier"
          - name: fraud_reported
            description: "Whether fraud was reported (Y/N)"

What does this file do?

• database: insurance and schema: bronze tell dbt to look in the insurance.bronze namespace of your Unity Catalog.
• The raw_claims table entry maps to the Delta table you created in the Bronze ingestion notebook.
• Column descriptions are optional but help with auto-generated documentation later.

• Save _sources.yml. You will not see a change yet. The staging model you create next reads from this source.

Now it is time to build the actual transformation logic. The staging model reads every column from the Bronze table, renames them to snake_case, casts data types, and standardizes categorical values.

• Create a new file called stg_claims.sql inside models/staging/ and paste the following code:

-- Pull all columns from the Bronze source
with source as (
    select * from {{ source('bronze', 'raw_claims') }}
),

-- Clean, cast, and standardize every column
cleaned as (
    select
        -- Policy identifiers
        cast(policy_number as string) as policy_number,
        cast(months_as_customer as int) as months_as_customer,
        cast(age as int) as customer_age,

        -- Policy details
        upper(trim(policy_state)) as policy_state,
        trim(policy_csl) as policy_csl,
        cast(policy_deductable as int) as policy_deductible,
        cast(policy_annual_premium as double) as policy_annual_premium,
        cast(umbrella_limit as int) as umbrella_limit,

        -- Customer demographics
        trim(insured_sex) as insured_sex,
        trim(insured_education_level) as insured_education_level,
        trim(insured_occupation) as insured_occupation,
        trim(insured_relationship) as insured_relationship,
        cast(capital_gains as int) as capital_gains,
        cast(capital_loss as int) as capital_loss,

        -- Incident details
        cast(incident_date as date) as incident_date,
        trim(incident_type) as incident_type,
        trim(collision_type) as collision_type,
        trim(incident_severity) as incident_severity,
        trim(authorities_contacted) as authorities_contacted,
        upper(trim(incident_state)) as incident_state,
        trim(incident_city) as incident_city,
        cast(number_of_vehicles_involved as int) as number_of_vehicles_involved,
        trim(property_damage) as property_damage,
        cast(bodily_injuries as int) as bodily_injuries,
        cast(witnesses as int) as witnesses,
        trim(police_report_available) as police_report_available,

        -- Claim amounts
        cast(total_claim_amount as double) as total_claim_amount,
        cast(injury_claim as double) as injury_claim,
        cast(property_claim as double) as property_claim,
        cast(vehicle_claim as double) as vehicle_claim,

        -- Vehicle info
        trim(auto_make) as auto_make,
        trim(auto_model) as auto_model,
        cast(auto_year as int) as auto_year,

        -- Convert Y/N fraud flag to boolean
        case
            when upper(trim(fraud_reported)) = 'Y' then true
            else false
        end as is_fraud,

        -- Preserve Bronze metadata
        _ingested_at,
        _source_file
    from source
)

select * from cleaned

What does this model do?

This model applies four types of cleaning to the raw data:

• Type casting: Converts strings to proper types like int, double, and date. The raw CSV imports everything as strings.
• Renaming: Renames age to customer_age and fixes the misspelled policy_deductable to policy_deductible.
• Trimming and uppercasing: Removes whitespace from categorical values and uppercases state codes for consistency.
• Boolean conversion: Converts the fraud_reported column from Y/N strings to a proper true/false boolean called is_fraud.

• Save stg_claims.sql.

You will not see output yet. You will run this model in the final substep of this step.

Why read from a source instead of hardcoding the table name?

The {{ source('bronze', 'raw_claims') }} Jinja function resolves to insurance.bronze.raw_claims using the values in your _sources.yml. If your catalog or schema name ever changes, you update it in one place instead of every model.

Pro tip: column name gotchas

The Kaggle dataset has a few quirks. The column for deductible is misspelled as policy_deductable in the raw CSV. The staging model reads the raw name and aliases it to the correct spelling. If you see a column-not-found error later, double-check that the raw column names in your Bronze table match what the model expects.

Awesome, I've got everything!

Make sure you have saved both _sources.yml and stg_claims.sql in your models/staging/ folder.

I'd like to double check the full code

Here is the complete stg_claims.sql file for comparison:

with source as (
    select * from {{ source('bronze', 'raw_claims') }}
),

cleaned as (
    select
        cast(policy_number as string) as policy_number,
        cast(months_as_customer as int) as months_as_customer,
        cast(age as int) as customer_age,
        upper(trim(policy_state)) as policy_state,
        trim(policy_csl) as policy_csl,
        cast(policy_deductable as int) as policy_deductible,
        cast(policy_annual_premium as double) as policy_annual_premium,
        cast(umbrella_limit as int) as umbrella_limit,
        trim(insured_sex) as insured_sex,
        trim(insured_education_level) as insured_education_level,
        trim(insured_occupation) as insured_occupation,
        trim(insured_relationship) as insured_relationship,
        cast(capital_gains as int) as capital_gains,
        cast(capital_loss as int) as capital_loss,
        cast(incident_date as date) as incident_date,
        trim(incident_type) as incident_type,
        trim(collision_type) as collision_type,
        trim(incident_severity) as incident_severity,
        trim(authorities_contacted) as authorities_contacted,
        upper(trim(incident_state)) as incident_state,
        trim(incident_city) as incident_city,
        cast(number_of_vehicles_involved as int) as number_of_vehicles_involved,
        trim(property_damage) as property_damage,
        cast(bodily_injuries as int) as bodily_injuries,
        cast(witnesses as int) as witnesses,
        trim(police_report_available) as police_report_available,
        cast(total_claim_amount as double) as total_claim_amount,
        cast(injury_claim as double) as injury_claim,
        cast(property_claim as double) as property_claim,
        cast(vehicle_claim as double) as vehicle_claim,
        trim(auto_make) as auto_make,
        trim(auto_model) as auto_model,
        cast(auto_year as int) as auto_year,
        case
            when upper(trim(fraud_reported)) = 'Y' then true
            else false
        end as is_fraud,
        _ingested_at,
        _source_file
    from source
)

select * from cleaned

Add schema tests for data quality

Staging models clean your data, but how do you know the data actually meets your expectations? dbt schema tests let you define rules that your data must pass every time the model runs.

• Create a new file called _stg_schema.yml inside models/staging/ and paste the following code:

version: 2

models:
  - name: stg_claims
    description: "Cleaned and standardized insurance claims from Bronze layer"
    columns:
      - name: policy_number
        description: "Unique policy identifier"
        tests:
          - not_null
          - unique
      - name: incident_date
        description: "Date of the incident"
        tests:
          - not_null
      - name: total_claim_amount
        description: "Total claim amount in USD"
        tests:
          - not_null
      - name: incident_severity
        description: "Severity classification of the incident"
        tests:
          - accepted_values:
              values: ['Minor Damage', 'Major Damage', 'Total Loss', 'Trivial Damage']
      - name: incident_type
        description: "Type of incident"
        tests:
          - accepted_values:
              values: ['Single Vehicle Collision', 'Vehicle Theft', 'Multi-vehicle Collision', 'Parked Car']
      - name: is_fraud
        description: "Whether fraud was reported (true/false)"
        tests:
          - not_null

What do these tests check?

• not_null ensures critical columns like policy_number, incident_date, and total_claim_amount never contain null values.
• unique on policy_number guarantees there are no duplicate policies. This is important because the Gold layer will use policy number as a key.
• accepted_values on incident_severity and incident_type catches any unexpected category values that could break downstream analysis.

• Save _stg_schema.yml.

You will not see output yet. The next substep runs the model and tests together.

Run the staging build and verify in Databricks

The dbt build command compiles your SQL model, runs it against Databricks, and then executes every schema test. If all tests pass, your Silver layer is ready.

• In your VS Code terminal, make sure you are in the insurance_claims_pipeline project directory.
• Run the staging models and tests by running this command:

dbt build --select staging

What does this command do?

• dbt build runs models first, then executes their associated tests.
• --select staging limits the run to only models in the models/staging/ folder. This keeps things focused while you are iterating.

You should see output showing the model was created and all tests passed. Look for green PASS lines for each test.

Seeing test failures?

• If the accepted_values test fails, the raw data may contain category values not listed in your schema file. Run SELECT DISTINCT incident_severity FROM insurance.bronze.raw_claims in the Databricks SQL editor to see the actual values, then update the list in _stg_schema.yml.
• If the unique test on policy_number fails, you may have loaded the Bronze table more than once. Re-run the Bronze ingestion notebook with mode("overwrite") to replace the data.
• If you see a connection error, run dbt debug to verify your Databricks credentials are still valid.

Help me debug my dbt build failures for the staging model.

• Once all tests pass, verify the Silver layer table in Databricks. Open the Databricks SQL editor and run this query:

SELECT count(*) as row_count FROM insurance.silver.stg_claims

You should see roughly 1,000 rows (the exact count depends on your dataset version).

• Run this query to inspect a few cleaned rows and confirm the transformations look correct:

SELECT policy_number, customer_age, policy_deductible, incident_severity, is_fraud
FROM insurance.silver.stg_claims
LIMIT 5

You should see customer_age as an integer (not a string), policy_deductible with the corrected spelling, and is_fraud as true or false instead of Y or N.

Your Silver layer is built and validated. The raw data now has clean types, consistent naming, and tested quality guarantees. Next up, you will build the Gold layer with a star schema data mart that turns these cleaned claims into analytical tables.

Build the Gold Layer Data Mart

Your Silver layer staging model cleaned and standardized the raw claims data. Now it's time to shape that data for analytics by building the Gold layer.

In this step, you'll build a star schema data mart. Star schemas separate data into dimension tables (descriptive attributes like customer details) and fact tables (measurable events like claims). This structure makes analytical queries fast and intuitive.

In this step, get ready to:

• Build three dimension tables for customers, vehicles, and policies.
• Create a fact table and two analytical mart tables for claims analysis and fraud detection.
• Run and verify all Gold layer models in Databricks.

Create the dimension models

Dimension tables hold the descriptive attributes that give context to your facts. You'll create three dimensions: one for customer demographics, one for vehicle details, and one for policy information.

Each dimension needs a surrogate key. This is a hashed identifier that dbt generates from one or more source columns. You'll use the dbt_utils package for this, so let's install it first.

• Create a file called packages.yml in your project root (the same level as dbt_project.yml) and paste the following:

packages:
  - package: dbt-labs/dbt_utils
    version: ">=1.3.0"

What is dbt_utils?

dbt_utils is an open-source package from dbt Labs that provides commonly needed macros. You'll use its generate_surrogate_key() macro to create MD5 hash keys for your dimension and fact tables.

• Save packages.yml.
• Install the package by running this command from your project directory:

dbt deps

You should see output confirming that dbt-labs/dbt_utils was installed successfully.

Package install failed?

Make sure you are running the command from your dbt project folder (the one containing dbt_project.yml). Also check that your packages.yml file is valid YAML with no indentation errors.

Help me troubleshoot dbt deps failing to install dbt_utils

Next, you need to tell dbt how to materialize your mart models. The Gold layer models should be written as tables in a gold schema.

• Open dbt_project.yml and find the models: section.
• Find this block (which you added in the previous step):

models:
  insurance_claims_pipeline:
    staging:
      +schema: silver
      +materialized: view

• Add the marts: configuration below the staging: block so the full section looks like this:

models:
  insurance_claims_pipeline:
    staging:
      +schema: silver
      +materialized: view
    marts:
      +schema: gold
      +materialized: table

What does this configuration do?

This tells dbt to create all models in the models/marts/ directory as physical tables (not views) in the insurance.gold schema. Tables are better for Gold layer analytics because queries read pre-computed results instead of re-running transformations every time.

• Save dbt_project.yml.

Now you're ready to create the dimension models. Start with the customer dimension.

• Create a new folder called marts inside your models/ directory.
• Create the file models/marts/dim_customer.sql and paste the following code:

with customers as (
    select distinct
        -- Generate a surrogate key from policy_number
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as customer_key,
        policy_number,
        customer_age,
        insured_sex,
        insured_education_level,
        insured_occupation,
        insured_relationship,
        months_as_customer,
        capital_gains,
        capital_loss
    from {{ ref('stg_claims') }}
)

select * from customers

What does this model do?

• generate_surrogate_key(['policy_number']) creates an MD5 hash from the policy number. This becomes the customer_key that the fact table will use to join back to this dimension.
• select distinct ensures each customer appears only once, even if they have multiple claims.
• ref('stg_claims') references your Silver layer staging model, creating a dependency chain that dbt tracks automatically.

• Save dim_customer.sql.

You won't see a change yet. The next two dimension models follow the same pattern.

• Create models/marts/dim_vehicle.sql and paste the following code:

with vehicles as (
    select distinct
        -- Surrogate key for vehicle dimension
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as vehicle_key,
        policy_number,
        auto_make,
        auto_model,
        auto_year
    from {{ ref('stg_claims') }}
)

select * from vehicles

What does this model do?

This dimension extracts the vehicle-specific attributes (make, model, year) for each policy. The surrogate key uses the same policy_number base as the customer dimension. This ensures the fact table can join to each dimension using consistent keys.

• Save dim_vehicle.sql.

You won't see a change yet. One more dimension to go.

• Create models/marts/dim_policy.sql and paste the following code:

with policies as (
    select distinct
        -- Surrogate key for policy dimension
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as policy_key,
        policy_number,
        policy_state,
        policy_csl,
        policy_deductible,
        policy_annual_premium,
        umbrella_limit
    from {{ ref('stg_claims') }}
)

select * from policies

What does this model do?

This dimension captures the policy-level attributes: state, CSL (Combined Single Limits), deductible, annual premium, and umbrella limit. These fields let analysts slice claims data by policy characteristics without cluttering the fact table.

• Save dim_policy.sql.
• Verify all three dimension models compile and run by executing this command:

dbt run --select dim_customer dim_vehicle dim_policy

You should see output ending with Completed successfully and PASS=3. This confirms all three dimension tables were created in the insurance.gold schema.

Seeing an error about dbt_utils?

If you see Compilation Error: 'dbt_utils' is undefined, the package was not installed. Run dbt deps and then try again.

If you see a connection error, run dbt debug to verify your Databricks connection is still active. Free Edition SQL warehouses can shut down after inactivity.

Help me debug my dbt dimension models failing to run on Databricks

Create the fact table and claims summary

Fact tables sit at the center of a star schema. They hold the measurable events (claims, in your case) and foreign keys that point to each dimension. You'll also create a summary mart that pre-aggregates claims data for faster dashboard queries.

• Create models/marts/fact_claims.sql and paste the following code:

with claims as (
    select
        -- Claim surrogate key using policy_number + incident_date
        {{ dbt_utils.generate_surrogate_key(['policy_number', 'incident_date']) }} as claim_key,
        -- Foreign keys to dimension tables
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as customer_key,
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as vehicle_key,
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as policy_key,
        policy_number,
        incident_date,
        incident_type,
        collision_type,
        incident_severity,
        incident_state,
        incident_city,
        authorities_contacted,
        number_of_vehicles_involved,
        property_damage,
        bodily_injuries,
        witnesses,
        police_report_available,
        -- Measure columns
        total_claim_amount,
        injury_claim,
        property_claim,
        vehicle_claim,
        is_fraud
    from {{ ref('stg_claims') }}
)

select * from claims

What does this model do?

• The claim_key is generated from both policy_number and incident_date. This uniquely identifies each claim even if a single policy has multiple incidents.
• customer_key, vehicle_key, and policy_key all hash from policy_number alone. This matches the keys in your dimension tables, enabling joins.
• The measure columns (total_claim_amount, injury_claim, property_claim, vehicle_claim) are the numeric values analysts will aggregate in queries and dashboards.

• Save fact_claims.sql.

You won't see a change yet. The next model builds on this fact table.

The claims summary mart pre-aggregates claims data by geography and severity. This saves your Tableau dashboard from running expensive aggregations on every refresh.

• Create models/marts/mart_claims_summary.sql and paste the following code:

with claims as (
    select * from {{ ref('fact_claims') }}
),

summary as (
    select
        incident_state,
        incident_severity,
        -- Claim volume metrics
        count(*) as total_claims,
        round(avg(total_claim_amount), 2) as avg_claim_amount,
        round(sum(total_claim_amount), 2) as total_claim_value,
        -- Fraud metrics
        sum(case when is_fraud then 1 else 0 end) as fraud_count,
        round(
            sum(case when is_fraud then 1 else 0 end) * 100.0 / count(*),
            2
        ) as fraud_rate_pct,
        -- Injury and witness averages
        round(avg(bodily_injuries), 2) as avg_bodily_injuries,
        round(avg(witnesses), 2) as avg_witnesses
    from claims
    group by incident_state, incident_severity
)

select * from summary

What does this model do?

• This model references fact_claims using ref(), which tells dbt to build the fact table first.
• It groups claims by incident_state and incident_severity, then calculates average claim amounts, total claim value, fraud counts, and fraud rates for each group.
• The fraud_rate_pct column divides fraud count by total claims and multiplies by 100. This gives you the fraud percentage per state and severity combination.

• Save mart_claims_summary.sql.
• Verify the fact table and summary mart by running:

dbt run --select fact_claims mart_claims_summary

You should see Completed successfully with PASS=2. dbt builds fact_claims first (because the summary references it), then builds the summary mart.

Seeing a compilation or runtime error?

If you see Compilation Error: node 'stg_claims' not found, make sure you ran your staging model in Step 3 first. Run dbt run --select stg_claims to rebuild it.

If you see a SQL syntax error, double check that you copied the Jinja curly braces exactly. Every {{ }} block needs double curly braces, not single.

Help me debug my dbt fact table or claims summary model

Build the fraud indicators model

The last Gold layer model calculates fraud risk signals for every claim. Insurance companies use composite scoring like this to flag suspicious claims for investigation.

• Create models/marts/mart_fraud_indicators.sql and paste the following code:

with claims as (
    select * from {{ ref('fact_claims') }}
),

percentiles as (
    -- Calculate the 90th percentile threshold for claim amounts
    select
        percentile_approx(total_claim_amount, 0.90) as p90_claim_amount
    from claims
),

fraud_signals as (
    select
        c.claim_key,
        c.policy_number,
        c.incident_date,
        c.incident_type,
        c.incident_severity,
        c.incident_state,
        c.total_claim_amount,
        c.is_fraud,
        -- Individual fraud risk flags
        case when c.total_claim_amount > p.p90_claim_amount then 1 else 0 end as high_claim_flag,
        case when c.witnesses = 0 then 1 else 0 end as zero_witnesses_flag,
        case when c.police_report_available = 'NO' then 1 else 0 end as no_police_report_flag,
        case when c.property_damage = 'NO' then 1 else 0 end as no_property_damage_flag,
        -- Composite risk score (sum of all flags, 0-4)
        (
            case when c.total_claim_amount > p.p90_claim_amount then 1 else 0 end
            + case when c.witnesses = 0 then 1 else 0 end
            + case when c.police_report_available = 'NO' then 1 else 0 end
            + case when c.property_damage = 'NO' then 1 else 0 end
        ) as risk_score
    from claims c
    cross join percentiles p
)

select * from fraud_signals

What does this model do?

• The percentiles CTE calculates the 90th percentile of claim amounts using Databricks' percentile_approx() function. Any claim above this threshold gets flagged as unusually high.
• Four binary flags capture different fraud risk signals: high claim amount, zero witnesses, no police report, and no property damage.
• The risk_score column sums all four flags. A claim scoring 4 out of 4 is the most suspicious. A claim scoring 0 shows none of the common fraud patterns.
• The cross join attaches the single percentile value to every row so each claim can be compared against the threshold.

• Save mart_fraud_indicators.sql.
• Build all Gold layer models together by running:

dbt build --select marts

You should see Completed successfully with PASS=6. All six Gold layer models (three dimensions, the fact table, and two analytical marts) are now materialized as Delta Lake tables in your insurance.gold schema.

• Open the Databricks SQL editor and verify the fraud indicators table by running this query:

SELECT risk_score, count(*) as claim_count
FROM insurance.gold.mart_fraud_indicators
GROUP BY risk_score
ORDER BY risk_score DESC

You should see a breakdown of claims by risk score, from 0 to 4. Claims with higher scores have more fraud risk signals present.

Seeing zero rows or an error?

If the query returns no results, check that dbt build --select marts finished with PASS=6 and no errors. If any model failed, dbt skips downstream models that depend on it.

If you see Table or view not found, make sure you are querying insurance.gold.mart_fraud_indicators (not insurance.silver or insurance.bronze).

Help me debug why my Gold layer mart tables are empty or missing in Databricks

Awesome, I've got everything!

Great. Double check you've saved all six files in your models/marts/ directory.

I'd like to double check the full code

Here is the complete code for each Gold layer model file.

with customers as (
    select distinct
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as customer_key,
        policy_number,
        customer_age,
        insured_sex,
        insured_education_level,
        insured_occupation,
        insured_relationship,
        months_as_customer,
        capital_gains,
        capital_loss
    from {{ ref('stg_claims') }}
)

select * from customers

with vehicles as (
    select distinct
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as vehicle_key,
        policy_number,
        auto_make,
        auto_model,
        auto_year
    from {{ ref('stg_claims') }}
)

select * from vehicles

with policies as (
    select distinct
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as policy_key,
        policy_number,
        policy_state,
        policy_csl,
        policy_deductible,
        policy_annual_premium,
        umbrella_limit
    from {{ ref('stg_claims') }}
)

select * from policies

with claims as (
    select
        {{ dbt_utils.generate_surrogate_key(['policy_number', 'incident_date']) }} as claim_key,
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as customer_key,
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as vehicle_key,
        {{ dbt_utils.generate_surrogate_key(['policy_number']) }} as policy_key,
        policy_number,
        incident_date,
        incident_type,
        collision_type,
        incident_severity,
        incident_state,
        incident_city,
        authorities_contacted,
        number_of_vehicles_involved,
        property_damage,
        bodily_injuries,
        witnesses,
        police_report_available,
        total_claim_amount,
        injury_claim,
        property_claim,
        vehicle_claim,
        is_fraud
    from {{ ref('stg_claims') }}
)

select * from claims

with claims as (
    select * from {{ ref('fact_claims') }}
),

summary as (
    select
        incident_state,
        incident_severity,
        count(*) as total_claims,
        round(avg(total_claim_amount), 2) as avg_claim_amount,
        round(sum(total_claim_amount), 2) as total_claim_value,
        sum(case when is_fraud then 1 else 0 end) as fraud_count,
        round(
            sum(case when is_fraud then 1 else 0 end) * 100.0 / count(*),
            2
        ) as fraud_rate_pct,
        round(avg(bodily_injuries), 2) as avg_bodily_injuries,
        round(avg(witnesses), 2) as avg_witnesses
    from claims
    group by incident_state, incident_severity
)

select * from summary

with claims as (
    select * from {{ ref('fact_claims') }}
),

percentiles as (
    select
        percentile_approx(total_claim_amount, 0.90) as p90_claim_amount
    from claims
),

fraud_signals as (
    select
        c.claim_key,
        c.policy_number,
        c.incident_date,
        c.incident_type,
        c.incident_severity,
        c.incident_state,
        c.total_claim_amount,
        c.is_fraud,
        case when c.total_claim_amount > p.p90_claim_amount then 1 else 0 end as high_claim_flag,
        case when c.witnesses = 0 then 1 else 0 end as zero_witnesses_flag,
        case when c.police_report_available = 'NO' then 1 else 0 end as no_police_report_flag,
        case when c.property_damage = 'NO' then 1 else 0 end as no_property_damage_flag,
        (
            case when c.total_claim_amount > p.p90_claim_amount then 1 else 0 end
            + case when c.witnesses = 0 then 1 else 0 end
            + case when c.police_report_available = 'NO' then 1 else 0 end
            + case when c.property_damage = 'NO' then 1 else 0 end
        ) as risk_score
    from claims c
    cross join percentiles p
)

select * from fraud_signals

Your Gold layer data mart is complete with three dimensions, a fact table, a claims summary, and a fraud risk scorer. Next up, you'll add automated data quality tests and generate full documentation with a data lineage graph.

Add Data Quality Tests and Documentation

In the last step, you built a star schema data mart with dimension tables, a fact table, and analytical marts. Your Gold layer is ready for analysis.

Building models is only half the story. In production, you need automated tests to catch data quality issues every time the pipeline runs. dbt's testing framework lets you define these rules as YAML configuration right alongside your models.

In this step, get ready to:

• Define schema tests for all mart layer models.
• Install the dbt-utils package and run the full pipeline with tests.
• Generate interactive documentation and explore the data lineage graph.

Define schema tests for the mart models

dbt tests are assertions you declare in YAML schema files. Each test checks a specific rule about a column. If the rule is violated, dbt flags it as a failure.

You'll start with the dimension tables. Every dimension needs its surrogate key validated as both unique and non-null.

• Create a new file called _mart_schema.yml inside your models/marts/ folder.
• Add the dimension model test definitions by copying this code into the file:

version: 2

models:
  - name: dim_customer
    description: "Customer dimension table"
    columns:
      - name: customer_key
        description: "Surrogate key for customer"
        tests:
          - not_null
          - unique
      - name: policy_number
        tests:
          - not_null

  - name: dim_vehicle
    description: "Vehicle dimension table"
    columns:
      - name: vehicle_key
        description: "Surrogate key for vehicle"
        tests:
          - not_null
          - unique

  - name: dim_policy
    description: "Policy dimension table"
    columns:
      - name: policy_key
        description: "Surrogate key for policy"
        tests:
          - not_null
          - unique

What do these tests do?

• not_null checks that a column has no missing values. A null surrogate key would mean a dimension record with no identity.
• unique checks that no two rows share the same value. Duplicate surrogate keys would corrupt your joins downstream.
• Each dimension (dim_customer, dim_vehicle, dim_policy) follows the same pattern: validate the surrogate key, then validate any critical business columns.

You won't see a change yet. The tests run when you execute dbt build later in this step.

Now you'll add the fact table and mart model tests. The fact table introduces a new test type: relationships. This test validates referential integrity between your fact table and dimension tables.

• In _mart_schema.yml, add the following below the dim_policy model definition (still inside the models: list):

  - name: fact_claims
    description: "Fact table containing claim measures and dimension keys"
    columns:
      - name: claim_key
        description: "Surrogate key for each claim"
        tests:
          - not_null
          - unique
      - name: customer_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_key
      - name: vehicle_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_vehicle')
              field: vehicle_key
      - name: policy_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_policy')
              field: policy_key
      - name: total_claim_amount
        tests:
          - not_null

  - name: mart_claims_summary
    description: "Aggregated claims summary by state and severity"
    columns:
      - name: incident_state
        tests:
          - not_null
      - name: total_claims
        tests:
          - not_null

  - name: mart_fraud_indicators
    description: "Fraud risk scoring for each claim"
    columns:
      - name: claim_key
        tests:
          - not_null
      - name: risk_score
        description: "Composite fraud risk score (0-4)"
        tests:
          - not_null

What do the relationship tests do?

The relationships test checks that every value in the fact table's foreign key column exists in the referenced dimension table. For example, the test on customer_key confirms that every claim links to a real customer record in dim_customer.

If a key appears in fact_claims but not in the dimension table, the test fails. This catches orphaned records that would silently disappear from your dashboard when you join tables.

• Save _mart_schema.yml.

You won't see a change yet. The next substep runs these tests.

Awesome, I've got everything!

Double check you've saved _mart_schema.yml before moving on.

I'd like to double check the full code

Here is the complete _mart_schema.yml file. Make sure your file matches this exactly.

version: 2

models:
  - name: dim_customer
    description: "Customer dimension table"
    columns:
      - name: customer_key
        description: "Surrogate key for customer"
        tests:
          - not_null
          - unique
      - name: policy_number
        tests:
          - not_null

  - name: dim_vehicle
    description: "Vehicle dimension table"
    columns:
      - name: vehicle_key
        description: "Surrogate key for vehicle"
        tests:
          - not_null
          - unique

  - name: dim_policy
    description: "Policy dimension table"
    columns:
      - name: policy_key
        description: "Surrogate key for policy"
        tests:
          - not_null
          - unique

  - name: fact_claims
    description: "Fact table containing claim measures and dimension keys"
    columns:
      - name: claim_key
        description: "Surrogate key for each claim"
        tests:
          - not_null
          - unique
      - name: customer_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_key
      - name: vehicle_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_vehicle')
              field: vehicle_key
      - name: policy_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_policy')
              field: policy_key
      - name: total_claim_amount
        tests:
          - not_null

  - name: mart_claims_summary
    description: "Aggregated claims summary by state and severity"
    columns:
      - name: incident_state
        tests:
          - not_null
      - name: total_claims
        tests:
          - not_null

  - name: mart_fraud_indicators
    description: "Fraud risk scoring for each claim"
    columns:
      - name: claim_key
        tests:
          - not_null
      - name: risk_score
        description: "Composite fraud risk score (0-4)"
        tests:
          - not_null

Install dbt packages and run the full pipeline

Your mart models use the dbt_utils.generate_surrogate_key() macro to create surrogate keys. This macro comes from the dbt-utils package, which needs to be declared in a packages.yml file at the root of your dbt project.

• Create a file called packages.yml in the root of your insurance_claims_pipeline project folder (the same level as dbt_project.yml).
• Add the following package declaration:

packages:
  - package: dbt-labs/dbt_utils
    version: ">=1.3.0"

What does this file do?

packages.yml tells dbt which third-party macro libraries to install. The dbt_utils package provides helper macros like generate_surrogate_key() that your dimension and fact models depend on.

• Save packages.yml.
• Install the package by running this command from your project directory:

dbt deps

• You should see output confirming the package was installed, including a line like Installing dbt-labs/dbt_utils.

Seeing a package resolution error?

• Make sure packages.yml is in the project root, not inside the models/ folder.
• Check that the file name is exactly packages.yml (not package.yml or packages.yaml).
• Verify you are running the command from your insurance_claims_pipeline project directory, not a parent folder.

Why is dbt deps failing to install my package?

With the package installed, you can now run the full pipeline. This builds every model across Staging and Marts, then runs every test you defined in both _stg_schema.yml and _mart_schema.yml.

• Execute the full pipeline by running:

dbt build

• You should see dbt run each model (staging, dimensions, facts, marts) and then run each test. Every line should show a green PASS status.

What should I see?

The output lists each model and test in execution order. Models show OK when they build successfully. Tests show PASS when they find no violations.

At the bottom, dbt prints a summary like Completed successfully. Done. PASS=X WARN=0 ERROR=0 SKIP=0 TOTAL=X. If you see any ERROR entries, read the failure message to identify which test failed and which column caused the issue.

Seeing test failures?

• A failing unique test means duplicate values exist. Check your staging model's SELECT DISTINCT logic and the column you are deduplicating on.
• A failing relationships test means a foreign key in fact_claims has no matching record in the dimension table. Verify the surrogate key macro inputs match between the fact and dimension models.
• A failing not_null test means null values exist. Check your staging model's CAST statements for the affected column.

Help me debug my failing dbt tests.

Generate and explore the documentation site

dbt can auto-generate a documentation website from your models, descriptions, and tests. It also builds a data lineage graph that visualizes how data flows from source to final output.

• Generate the documentation catalog by running:

dbt docs generate

• You should see output confirming the catalog was generated, ending with Catalog written.
• Launch the documentation site in your browser by running:

dbt docs serve

• Your browser opens to the dbt documentation site, usually at http://localhost:8080.

The docs site shows every model, source, column, and test in your project. Each model page includes the SQL definition, column descriptions, and which tests apply.

• Click the blue graph icon in the bottom-right corner of the docs site to open the lineage graph.
• Explore the lineage graph. You should see the full data flow from the raw_claims Bronze source, through stg_claims in the Silver layer, branching into the dimension tables (dim_customer, dim_vehicle, dim_policy), into fact_claims, and finally into the analytical marts.

Pro tip: save a screenshot now

This lineage graph is one of the most impressive visuals for your GitHub README. Take a screenshot of the full graph showing all nodes and connections. You will use this in the final step when you publish your project.

Don't see the lineage graph?

• Make sure you ran dbt docs generate before dbt docs serve. The generate step creates the catalog file that the site reads.
• Look for the small blue icon in the bottom-right corner of the page. It may not be visible on every page of the docs site.
• If the graph is empty, check that your models built successfully by re-running dbt build first.

Why is my dbt docs lineage graph not showing any models?

• Press Ctrl+C in your terminal to stop the docs server when you are done exploring.

Your pipeline now has automated data quality guardrails and full documentation. Next up, you'll set up CI/CD with GitHub Actions so these tests run automatically on every pull request.

Set Up CI/CD with GitHub Actions

Your dbt project now has a full suite of data quality tests and auto-generated documentation with lineage.

The next challenge is making sure those tests run automatically every time someone proposes a code change. In this step, you'll set up a CI/CD pipeline using GitHub Actions so that every pull request triggers a dbt build against your Databricks workspace.

In this step, get ready to:

• Create a GitHub Actions workflow that runs dbt build on every pull request.
• Configure GitHub secrets for secure Databricks authentication.
• Test the pipeline by opening a pull request and verifying it passes.

Create the GitHub Actions workflow file

GitHub Actions lets you define automated workflows as YAML files inside your repository. When certain events happen (like opening a pull request), GitHub spins up a runner and executes the steps you define.

• Create the workflow directory inside your project by running this command:

mkdir -p .github/workflows

• Confirm the directory was created by running:

ls .github/workflows

You should see the workflows directory listed without any errors. It will be empty for now.

• Create a new file called dbt_ci.yml inside the .github/workflows/ directory.
• Paste the following code into dbt_ci.yml:

name: dbt CI

on:
  pull_request:
    branches:
      - main

jobs:
  dbt-build:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'

      - name: Install dbt-databricks
        run: |
          python -m pip install --upgrade pip
          pip install dbt-databricks==1.11.6

      - name: Run dbt build
        env:
          DBT_DATABRICKS_HOST: ${{ secrets.DBT_DATABRICKS_HOST }}
          DBT_DATABRICKS_TOKEN: ${{ secrets.DBT_DATABRICKS_TOKEN }}
          DBT_DATABRICKS_HTTP_PATH: ${{ secrets.DBT_DATABRICKS_HTTP_PATH }}
        run: |
          dbt deps
          dbt build --profiles-dir .

What does this workflow do?

• on: pull_request triggers the workflow whenever someone opens a PR targeting the main branch.
• The first two steps check out your code and install Python 3.11 on a fresh Ubuntu runner.
• The Install step pins dbt-databricks==1.11.6 so every CI run uses the exact same version.
• The Run step injects your Databricks credentials from GitHub secrets as environment variables, then runs dbt deps and dbt build. The --profiles-dir . flag tells dbt to use the CI profiles.yml in the repo root instead of your local ~/.dbt/ directory.

• Save dbt_ci.yml.

You won't see a change yet. The workflow only runs when you open a pull request on GitHub, which you'll do after configuring authentication.

Awesome, I've got everything!

Great. Double check you've saved dbt_ci.yml.

I'd like to double check the full code

name: dbt CI

on:
  pull_request:
    branches:
      - main

jobs:
  dbt-build:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'

      - name: Install dbt-databricks
        run: |
          python -m pip install --upgrade pip
          pip install dbt-databricks==1.11.6

      - name: Run dbt build
        env:
          DBT_DATABRICKS_HOST: ${{ secrets.DBT_DATABRICKS_HOST }}
          DBT_DATABRICKS_TOKEN: ${{ secrets.DBT_DATABRICKS_TOKEN }}
          DBT_DATABRICKS_HTTP_PATH: ${{ secrets.DBT_DATABRICKS_HTTP_PATH }}
        run: |
          dbt deps
          dbt build --profiles-dir .

Configure CI authentication and push to GitHub

The workflow references three secrets and a profiles.yml file that doesn't exist yet. Your local profiles.yml at ~/.dbt/ contains your actual PAT token, so you never want to commit that.

Instead, you'll create a separate CI-only profiles.yml in the repo root that reads credentials from environment variables at runtime.

• Create a new file called profiles.yml in the root of your insurance-claims-pipeline project folder (the same level as dbt_project.yml).
• Paste the following code into profiles.yml:

insurance_claims_pipeline:
  target: ci
  outputs:
    ci:
      type: databricks
      catalog: insurance
      schema: ci_{{ env_var('GITHUB_RUN_ID', 'local') }}
      host: "{{ env_var('DBT_DATABRICKS_HOST') }}"
      http_path: "{{ env_var('DBT_DATABRICKS_HTTP_PATH') }}"
      token: "{{ env_var('DBT_DATABRICKS_TOKEN') }}"
      threads: 1

How is this different from your local profiles.yml?

• Your local ~/.dbt/profiles.yml has your actual PAT token hardcoded. This CI version uses dbt's env_var() function to read credentials from environment variables that GitHub Actions injects at runtime.
• The schema: ci_{{ env_var('GITHUB_RUN_ID', 'local') }} line creates a unique schema for each CI run (like ci_12345678). This prevents CI runs from overwriting your development tables.

• Save profiles.yml.

Awesome, I've got everything!

Great. Double check you've saved profiles.yml in the project root.

I'd like to double check the full code

insurance_claims_pipeline:
  target: ci
  outputs:
    ci:
      type: databricks
      catalog: insurance
      schema: ci_{{ env_var('GITHUB_RUN_ID', 'local') }}
      host: "{{ env_var('DBT_DATABRICKS_HOST') }}"
      http_path: "{{ env_var('DBT_DATABRICKS_HTTP_PATH') }}"
      token: "{{ env_var('DBT_DATABRICKS_TOKEN') }}"
      threads: 1

Now push everything to GitHub so the workflow file and CI profile are in the repository.

• Stage, commit, and push your changes by running these commands:

git add .
git commit -m "Add CI/CD workflow and CI profiles"
git push origin main

Push rejected or authentication error?

• If you see a "rejected" error, pull first with git pull origin main --rebase and then push again.
• If you see an authentication error, make sure you have a GitHub Personal Access Token or SSH key configured for your repository.
• Help me fix my git push error.

Next, you need to add the three secrets that the workflow references. Without these, GitHub Actions cannot authenticate with your Databricks workspace.

• Open your insurance-claims-pipeline repository on GitHub.
• Click Settings in the top navigation bar of your repository.
• In the left sidebar, click Secrets and variables, then click Actions.
• Click New repository secret.
• Enter DBT_DATABRICKS_HOST as the name. For the value, paste your Databricks workspace URL without the https:// prefix (for example, dbc-abc123-def4.cloud.databricks.com).
• Click Add secret.
• Repeat the process to add a second secret. Enter DBT_DATABRICKS_TOKEN as the name and paste your Databricks Personal Access Token as the value.
• Add a third secret. Enter DBT_DATABRICKS_HTTP_PATH as the name and paste your SQL warehouse HTTP path as the value.

Where do I find my SQL warehouse HTTP path?

In your Databricks workspace, click SQL Warehouses in the left sidebar. Click on your warehouse name, then open the Connection Details tab. The HTTP path is listed there. This is the same value you used when running dbt init in Step 1.

You should now see all three secrets listed on the Actions secrets page.

Don't see the Settings tab?

The Settings tab only appears if you are the owner of the repository. Make sure you are viewing the repo under your own GitHub account, not a fork or someone else's repo.

Help me find where to add secrets to my GitHub repository.

Test the CI pipeline with a pull request

The workflow triggers on pull requests, not direct pushes to main. To test it, you need to create a branch, make a change, and open a PR.

• Create a new feature branch by running this command:

git checkout -b add-schema-description

• Open models/marts/_mart_schema.yml in VS Code.
• Find this line under the dim_vehicle model:

  - name: dim_vehicle
    description: "Vehicle dimension table"

• Change the description to add more detail, so the line looks like this:

  - name: dim_vehicle
    description: "Vehicle dimension table with make, model, and year"

• Save _mart_schema.yml.
• Commit and push your branch by running these commands:

git add .
git commit -m "Add vehicle dimension description"
git push origin add-schema-description

• Open your repository on GitHub in your browser.
• You should see a banner that says your branch had recent pushes. Click Compare & pull request.
• Leave the title and description as defaults.
• Click Create pull request.

GitHub Actions picks up the PR automatically and starts running your dbt CI workflow. You can watch it in real time.

• Scroll down on the PR page to the checks section. Click Details next to the dbt-build check to watch the workflow logs.
• Wait for all steps to complete. This typically takes 2-5 minutes.

When the workflow finishes, you should see a green checkmark next to the dbt-build check on the PR page. This confirms that all your dbt models built successfully and all tests passed.

Workflow showing a red X?

• Click Details to open the workflow logs. The failing step is highlighted in red.
• If the error mentions authentication or connection issues, double check that your three secrets (DBT_DATABRICKS_HOST, DBT_DATABRICKS_TOKEN, DBT_DATABRICKS_HTTP_PATH) are spelled exactly as shown and contain the correct values.
• If the error mentions a profile not found, make sure your profiles.yml is in the repo root (the same level as dbt_project.yml) and that the profile name matches insurance_claims_pipeline in both files.
• If tests fail but models build, check the test output in the logs. The CI run uses a fresh ci_ schema, so all models are rebuilt from scratch.
• Help me debug my failing GitHub Actions dbt workflow.

Your CI/CD pipeline is live. Every future pull request to main will automatically build all models and run every test, catching problems before they reach production. Next up, you'll build an interactive Tableau dashboard to visualize your pipeline's Gold layer outputs.

Build and Publish the Tableau Dashboard

Your CI/CD pipeline is running on GitHub Actions, validating every pull request with a full dbt build. The last piece of your portfolio project is a visualization layer that makes your data accessible to non-technical stakeholders.

In this step, you'll export your Gold layer tables from Databricks and build an interactive dashboard in Tableau Public. The published dashboard gives recruiters a live, clickable link instead of just a screenshot.

In this step, get ready to:

• Export Gold layer tables from Databricks as CSV files.
• Build three visualizations in Tableau Desktop Public Edition.
• Publish the dashboard to Tableau Public and link it in your GitHub README.

Export your Gold layer tables from Databricks

Tableau Public cannot connect live to a Databricks database. You need to export your Gold layer data as CSV files first, then import those files into Tableau.

Why export to CSV?

Tableau Public is a free tool, and one of its limitations is that it only supports flat file data sources like CSV, Excel, and Google Sheets. Live database connections require Tableau Desktop (paid). For portfolio projects, CSV export is the standard workflow.

You'll run three queries in the Databricks SQL editor and download each result set as a CSV file.

• In your Databricks workspace, open the SQL Editor from the left sidebar.
• Export the claims summary data by running this query:

SELECT * FROM insurance.gold.mart_claims_summary

• Click the download icon (downward arrow) in the results panel.
• Select Download as CSV and save the file as mart_claims_summary.csv.
• Clear the editor and export the fraud indicators by running this query:

SELECT * FROM insurance.gold.mart_fraud_indicators

• Download this result as CSV and save it as mart_fraud_indicators.csv.

The third export is the most important one. Your fact_claims table contains surrogate keys, not the actual dimension attributes. You need a join query that flattens everything into one table for Tableau.

• Export the full denormalized claims data by running this query:

-- Join fact table with all dimensions for a flat export
SELECT
    f.policy_number,
    f.incident_date,
    f.incident_type,
    f.incident_severity,
    f.incident_state,
    f.incident_city,
    f.total_claim_amount,
    f.injury_claim,
    f.property_claim,
    f.vehicle_claim,
    f.is_fraud,
    f.witnesses,
    f.bodily_injuries,
    f.police_report_available,
    c.customer_age,
    c.insured_sex,
    c.insured_education_level,
    c.insured_occupation,
    v.auto_make,
    v.auto_model,
    v.auto_year,
    p.policy_state,
    p.policy_deductible,
    p.policy_annual_premium,
    p.umbrella_limit
FROM insurance.gold.fact_claims f
LEFT JOIN insurance.gold.dim_customer c ON f.customer_key = c.customer_key
LEFT JOIN insurance.gold.dim_vehicle v ON f.vehicle_key = v.vehicle_key
LEFT JOIN insurance.gold.dim_policy p ON f.policy_key = p.policy_key

What does this query do?

• It selects every claim from fact_claims and attaches the matching customer demographics, vehicle details, and policy information.
• The LEFT JOIN ensures every claim row is included, even if a dimension key doesn't match.
• The result is a single flat table with all the columns Tableau needs for the dashboard.

• Download this result as CSV and save it as claims_detail.csv.
• Confirm you now have three CSV files saved locally: mart_claims_summary.csv, mart_fraud_indicators.csv, and claims_detail.csv.

Query returning no results?

• Make sure your SQL warehouse is running. Check the SQL Warehouses page in the left sidebar and start the warehouse if it is stopped.
• If the table name is not found, your dbt schema may have a prefix. Run SHOW SCHEMAS IN insurance to see the exact schema name and adjust your query accordingly.
• Verify that you ran dbt build --select marts successfully in Step 4. The Gold layer tables must exist before you can query them.

Why can't I query my Gold layer tables in Databricks?

Build visualizations in Tableau Desktop Public Edition

With your three CSV files ready, you'll build three visualizations that tell the story of your insurance claims data. Each visualization highlights a different angle: geography, fraud patterns, and high-level KPIs.

• Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
• Type Tableau Public and press Enter to open it.
• On the start page, under Connect, click Text file.
• Navigate to where you saved your CSV files and open mart_claims_summary.csv.

Tableau loads the CSV into the data source pane. You can preview the data at the bottom of the screen to confirm it looks correct.

Pro tip: add all three CSVs

You can add the other two CSV files (mart_fraud_indicators.csv and claims_detail.csv) by dragging them into the data source pane next to the first file. Each will appear as a separate data connection that you can switch between when building individual worksheets.

Now build the first of three visualizations: a geographic heatmap of claims by state.

• Click Sheet 1 at the bottom of the screen to open a new worksheet.
• Make sure your data source is set to mart_claims_summary in the Data pane on the left.
• Drag Incident State from the Data pane onto the canvas.

Tableau automatically generates a map because it recognizes state abbreviations as geographic data.

• Drag Avg Claim Amount onto the Color shelf on the Marks card.
• Drag Total Claims onto the Label shelf on the Marks card so each state shows its claim count.
• Double-click the sheet tab at the bottom and rename it to Claims by State.

You should see a filled map of the United States where darker colors represent higher average claim amounts.

Seeing dots instead of a filled map?

If Tableau shows dots instead of filled regions, change the mark type. Click the dropdown on the Marks card (it may say Automatic) and select Map or Filled Map. If Tableau doesn't recognize the state field as geographic, right-click Incident State, select Geographic Role, then choose State/Province.

How do I fix my Tableau map that isn't showing filled regions for state data?

Next, build the second visualization: a bar chart showing fraud rates by incident severity.

• Click the New Worksheet icon (the tab with a plus sign) at the bottom of the screen.
• Make sure your data source is still set to mart_claims_summary.
• Drag Incident Severity to Columns.
• Drag Fraud Rate Pct to Rows.
• If Tableau aggregates the fraud rate as SUM, right-click Fraud Rate Pct on the Rows shelf and change the measure to Average.
• Drag Incident State onto the Color shelf on the Marks card to break each severity bar down by state.
• Rename this worksheet to Fraud by Severity.

You should see a bar chart with four bars (Minor Damage, Major Damage, Total Loss, Trivial Damage), each broken down by state color.

For the third visualization, you'll build a summary KPI view using the denormalized claims detail data.

• Click the New Worksheet icon at the bottom.
• Switch your data source to claims_detail in the Data pane on the left.
• Drag Auto Make to Rows.
• Drag Total Claim Amount to Columns. Tableau creates a horizontal bar chart of claim volume by vehicle make.
• Sort the bars in descending order by clicking the sort descending icon in the toolbar.
• Rename this worksheet to Claims by Vehicle Make.

Now combine all three worksheets into a single dashboard.

• Click the New Dashboard icon at the bottom of the screen (the tab with a grid and plus sign).
• In the left panel under Sheets, drag Claims by State onto the top half of the dashboard canvas.
• Drag Fraud by Severity onto the bottom-left area.
• Drag Claims by Vehicle Make onto the bottom-right area.
• Double-click the dashboard title at the top and change it to Insurance Claims Analysis.

Your dashboard now shows a geographic heatmap, a fraud rate breakdown, and a ranked bar chart of vehicle makes. All three views are interactive. Clicking a state on the map filters the other charts.

Pro tip: add filter actions

To make the map filter the other sheets when you click a state, go to Dashboard in the top menu, then Actions, then Add Action, and choose Filter. Set the source sheet to Claims by State and target all other sheets. This creates a cross-filtering experience that recruiters love to see in portfolio dashboards.

Dashboard layout looks cramped?

Under Size in the left panel of the Dashboard tab, change the fixed size to Automatic so it adapts to the browser window. You can also rearrange sheets by dragging their edges on the canvas.

How do I improve the layout of my Tableau dashboard?

Publish to Tableau Public and update your README

Your dashboard is complete locally. Publishing it to Tableau Public creates a shareable URL that anyone can visit without installing Tableau.

• In the top menu bar, click Server.
• Click Tableau Public.
• Click Save to Tableau Public.

What happens when you publish?

Tableau Public will prompt you to sign in with your Tableau Public account. If you don't have one, you can create a free account at that point. Tableau then creates a data extract and uploads both the extract and the dashboard to your public profile.

• Sign in with your Tableau Public credentials when prompted.
• Enter Insurance Claims Analysis as the workbook name.
• Click Save.

Tableau opens your published dashboard in a browser. The URL in your browser's address bar is your permanent, shareable dashboard link.

• Copy the URL from your browser's address bar.

What makes this URL special?

This URL is publicly accessible. Anyone with the link can interact with your dashboard, filter the data, and hover over charts to see details. This is what you'll put in your GitHub README so recruiters can explore your work without installing anything.

The final step is adding the dashboard link and a screenshot to your project's GitHub README.

• In VS Code, open the README.md file in your project root.
• Add a section for the Tableau dashboard by pasting the following:

## Dashboard

[View the interactive Tableau dashboard](your-tableau-public-url-here)

![Dashboard Screenshot](docs/dashboard-screenshot.png)

## Data Lineage

![dbt Lineage Graph](docs/dbt-lineage.png)

What does this README section do?

• The first link points to your live Tableau Public dashboard.
• The image references expect you to save screenshots in a docs/ folder in your repo.
• The dbt lineage graph screenshot comes from the documentation site you generated in Step 5.

• Replace your-tableau-public-url-here with the Tableau Public URL you copied.
• Create a docs folder in your project root by running:

mkdir docs

• Take a screenshot of your Tableau dashboard and save it as dashboard-screenshot.png inside the docs/ folder.
• Take a screenshot of your dbt lineage graph (from Step 5) and save it as dbt-lineage.png inside the docs/ folder.
• Save README.md.
• Commit and push all changes to GitHub by running:

git add .
git commit -m "Add Tableau dashboard and README screenshots"
git push

• Open your GitHub repository in a browser and verify that the README displays the dashboard link and both screenshots.

Screenshots not showing in GitHub?

• Make sure the image file names in your markdown match the actual file names exactly, including capitalization.
• Confirm the screenshots are inside the docs/ folder, not the project root.
• Check that you committed and pushed the image files. Run git status to see if any files are unstaged.

Why aren't my README images showing up on GitHub?

Secret mission

Add Incremental Loading and Databricks Workflow Orchestration

Right now your pipeline reprocesses every single row every time it runs. In production, insurance companies ingest thousands of new claims daily. Can you make your pipeline smart enough to process only new data, and schedule it to run automatically?

Before you go

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project uses Databricks Free Edition, Tableau Public, and GitHub Actions free tier, so there are no ongoing costs. However, Databricks Free Edition has fair usage quotas that could be consumed by idle resources.

Resources you used:

• Databricks Free Edition workspace with Unity Catalog (insurance catalog, bronze/silver/gold schemas).
• Databricks Personal Access Token (PAT).
• Delta tables: raw_claims, stg_claims, dimension tables, fact table, and mart tables.
• Databricks notebook: 01_bronze_ingestion.
• Local dbt project (insurance_claims_pipeline) and Python virtual environment.
• GitHub repository (insurance-claims-pipeline) with GitHub Actions secrets.
• Tableau Public dashboard and exported CSV files.
• Downloaded Kaggle dataset (local CSV).

Keep everything running

No action needed. Choose this if you're still actively building on this pipeline or plan to extend it with additional data sources, models, or dashboard pages.

• Your Databricks Free Edition workspace stays available with all tables, notebooks, and catalog objects intact.
• Your GitHub repository and Actions workflows remain active.
• Your Tableau Public dashboard stays published at its shareable URL.
• Keep your PAT secure. Do not share it or commit it to version control.

Pause - I'll come back to this later

Free up local resources while keeping your cloud-hosted work available for later.

• Deactivate your Python virtual environment by running deactivate in your terminal.
• Your Databricks workspace, Delta tables, and notebooks persist automatically. No action needed there.
• Your Tableau Public dashboard stays published. You can update it any time by re-publishing from Tableau Desktop Public Edition.
• Your GitHub repository and Actions secrets remain available. No action needed.
• Delete the exported Gold layer CSV files from your local machine if you need to free up disk space.

Delete - I don't want to use this again

Remove all project resources and start fresh.

• In Databricks, open the SQL editor and run DROP CATALOG insurance CASCADE to remove the catalog and all schemas, tables, and volumes within it.
• Delete the 01_bronze_ingestion notebook from your Databricks workspace.
• Revoke your Personal Access Token in your Databricks user menu under Settings > Developer > Access Tokens.
• Delete your GitHub repository by going to Settings > General > scroll to Danger Zone > Delete this repository. This also removes your GitHub Actions secrets.
• Delete the Tableau Public dashboard by logging into your Tableau Public profile, selecting the workbook, and clicking Delete.
• Delete the local project folder (insurance_claims_pipeline), the Python virtual environment, the downloaded Kaggle CSV, and the exported Gold layer CSV files from your machine.

Mission accomplished

That's a Wrap!

Congratulations! You just built a complete, production-style data engineering pipeline using the exact stack that insurance companies rely on. This project covers Databricks, Spark, Delta Lake, dbt, star schema modeling, data quality testing, CI/CD, and Tableau visualization. That is a recruiter-ready portfolio piece.

Here's what you accomplished:

• Built a full Medallion architecture pipeline on Databricks, ingesting raw insurance claims into Delta Lake with PySpark and transforming them through Bronze, Silver, and Gold layers using dbt models.
• Designed a star schema data mart with fact and dimension tables, implemented automated data quality tests, and generated full documentation with data lineage.
• Set up CI/CD with GitHub Actions to validate dbt models on every pull request and published an interactive Tableau Public dashboard with a shareable URL backed by your Gold layer outputs.
• Added incremental loading to your dbt models and scheduled the full pipeline with Databricks Workflows for production-ready orchestration.

Ready to quiz yourself?