Project Strucuture

A dbt™ project is the foundation of how you organize, develop, and maintain your data transformations. Think of it as a collection of files that define how your raw data should be transformed into analytics-ready datasets.

Core Components

Every dbt™ project consists of several key components:

1. Project Files

• Models – SQL files that define your transformations.

• Tests – YAML files that check data quality.

• Documentation – YAML files describing your project.

• Macros – Reusable code snippets for automation and logic.

2. Configuration Files

• dbt_project.yml – Primary project configuration.

• sources.yml – Defines raw data sources.

• properties.yml – Documents models, tests, and metadata.

• packages.yml – Manages external package dependencies.

3. Project Layout

A standard dbt™ project structure looks like this:

dbt_project/
├── models/          # SQL transformations
├── tests/           # Data quality tests
├── macros/          # Reusable code
├── seeds/           # Seed data (CSV files)
├── snapshots/       # Capturing historical changes
├── dbt_project.yml  # Project configuration
├── sources.yml      # Source definitions
├── packages.yml     # External dependencies
├── properties.yml   # Schema definitions & metadata

---

Project Configuration (dbt_project.yml)

The dbt_project.yml file is the heart of your dbt™ project. It:

• Identifies your dbt™ project

• Sets project-wide settings

• Defines how models should be materialized

• Controls project behavior

Basic Structure

# Project identification
name: "paradime_training"
version: "1.0.0"
config-version: 2

# Connection profile
profile: "paradime-training"

# Paths for different dbt components
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

# Define how models should be materialized
models:                   
  paradime_training:        
    materialized: view    # Default materialization
    staging:
      materialized: view  # Staging models as views
    marts:
      materialized: table # Mart models as tables

# Cleaning up project-generated artifacts
clean-targets:
  - "target"
  - "dbt_packages"

This file defines how dbt interacts with your project, where it looks for files, and how models are materialized.

---

Source Definitions (sources.yml)

Sources represent raw data warehouse tables. Defining them in YAML allows dbt to:

• Track dependencies between raw data and models.

• Monitor data freshness.

• Document the origin of raw data.

Example: Defining Sources

version: 2
sources:
  - name: raw_data
    description: "Raw data from our production database"
    database: raw_db
    schema: public
    tables:
      - name: customers
        description: "Customer information from CRM"
        columns:
          - name: customer_id
            description: "Primary key for customers"
      - name: orders
        description: "Order transactions"

---

Schema and Metadata Definitions (properties.yml)

The properties.yml file (also known as schema.yml) is where you document models and define tests at the column level.

Example: Model Documentation and Testing

version: 2
models:
  - name: customers
    description: "Cleaned customer data with deduplicated records"
    columns:
      - name: customer_id
        description: "Primary key for customers"
        tests:
          - unique
          - not_null
      - name: email
        description: "Customer email address"
        tests:
          - unique

This ensures data integrity and provides clear documentation.

---

Package Management (packages.yml)

Packages in dbt™ function like libraries in programming languages, allowing teams to extend dbt’s functionality.

Why Use Packages?

• Pre-built Transformations – Utilize pre-tested transformations instead of writing SQL from scratch.

• Code Reusability – Share logic across different dbt projects.

• Community Best Practices – Leverage solutions built and maintained by the dbt™ community.

Example: Defining Packages

packages:
  - package: dbt-labs/dbt_utils     # Utility functions for common SQL operations
    version: 0.9.2
  - package: calogica/dbt_date      # Advanced date handling functions
    version: 0.5.0
  - package: fivetran/google_ads    # Pre-built models for transforming Google Ads data
    version: 1.0.0
  - package: fivetran/facebook_ads  # Transform Facebook Ads data
    version: 1.2.1
  - package: fivetran/salesforce    # Standardized Salesforce transformations
    version: 2.0.0

These packages simplify integrating third-party data sources like Google Ads, Facebook Ads, and Salesforce.

---

Project Organization: Layered Approach

To keep dbt™ projects maintainable, transformations are structured in layers:

Layer	Purpose	Example
Staging	Cleans and renames raw data	stg_orders.sql
Intermediate	Combines multiple staging models	int_order_details.sql
Marts	Business-ready tables for analytics	dim_customers.sql, fact_sales.sql

Benefits of Layered Organization

• Clear Data Lineage – Easily trace transformations.

• Modular Transformations – Modify one layer without affecting others.

• Efficient Troubleshooting – Isolate issues quickly.

• Scalability – Teams can work on different layers independently.

---

How Everything Works Together

A dbt™ project follows a structured workflow:

• Sources define where raw data comes from.

• Models transform that data into usable tables and views.

• Tests ensure data integrity before analytics teams use it.

• Materialization determines how transformed data is stored.

• Version Control tracks all changes for collaboration.

By structuring your dbt™ project effectively, you can build scalable, maintainable, and high-performance data pipelines.