SyncSage Documentation | Syncsage Docs

dbt (data build tool) is a transformation tool that enables analytics engineers to transform data in their warehouses by writing SQL queries. This guide will help you understand the basics of dbt and how to use it with SyncSage.

What is dbt?

dbt is a command-line tool that enables data analysts and engineers to transform data in their warehouses by writing SQL queries. dbt handles turning these SQL queries into tables and views in your data warehouse.

With dbt, you can:

Write SQL queries to transform your data
Test your data to ensure quality
Document your data models
Manage dependencies between models
Version control your transformations

Getting Started with dbt in SyncSage

SyncSage integrates seamlessly with dbt, allowing you to leverage its powerful transformation capabilities. Here's how to get started:

To set up a dbt project in SyncSage, follow these steps:

Navigate to the Transformations section in SyncSage
Click on "New dbt Project"
Configure your project settings
Connect to your data warehouse

Here's an example of a dbt project structure:

my_dbt_project/
├── dbt_project.yml          # Project configuration
├── models/                  # Contains your SQL models
│   ├── staging/             # Staging models
│   │   ├── schema.yml       # Schema definitions
│   │   ├── source_1.sql     # Staging model for source 1
│   │   └── source_2.sql     # Staging model for source 2
│   ├── intermediate/        # Intermediate models
│   └── marts/               # Business-defined data marts
│       ├── finance/         # Finance models
│       └── marketing/       # Marketing models
├── tests/                   # Custom tests
├── macros/                  # Reusable SQL snippets
└── analysis/                # Ad-hoc analyses

dbt models are written in SQL and saved as .sql files. Here's an example of a simple dbt model:

models/staging/stg_customers.sql

-- models/staging/stg_customers.sql
SELECT
  id as customer_id,
  first_name,
  last_name,
  email,
  date_created,
  CASE
    WHEN status = 'active' THEN true
    ELSE false
  END as is_active
FROM
  {{ source('raw_data', 'customers') }}

This model selects data from a source table, renames columns, and transforms the status field into a boolean.

dbt provides two key functions for managing dependencies between models:

source(): References raw data in your warehouse
ref(): References other dbt models

Example of using source():

models/staging/schema.yml

version: 2

sources:
  - name: raw_data
    database: raw_db
    schema: public
    tables:
      - name: customers
      - name: orders

Example of using ref():

models/marts/customer_orders.sql

-- models/marts/customer_orders.sql
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email,
  COUNT(o.order_id) as order_count,
  SUM(o.amount) as total_amount
FROM
  {{ ref('stg_customers') }} c
LEFT JOIN
  {{ ref('stg_orders') }} o ON c.customer_id = o.customer_id
GROUP BY
  1, 2, 3, 4

Testing Your Models

dbt allows you to test your models to ensure data quality. You can define tests in YAML files:

models/staging/schema.yml

version: 2

models:
  - name: stg_customers
    description: Staged customer data
    columns:
      - name: customer_id
        description: Primary key
        tests:
          - unique
          - not_null
      - name: email
        description: Customer email address
        tests:
          - unique
          - not_null

Documenting Your Models

Documentation is crucial for maintaining a data warehouse. dbt makes it easy to document your models:

models/marts/schema.yml

version: 2

models:
  - name: customer_orders
    description: Summary of customer orders
    columns:
      - name: customer_id
        description: Primary key
      - name: first_name
        description: Customer first name
      - name: last_name
        description: Customer last name
      - name: email
        description: Customer email address
      - name: order_count
        description: Total number of orders placed by the customer
      - name: total_amount
        description: Total amount spent by the customer

Running dbt in SyncSage

SyncSage provides a seamless interface for running dbt commands:

Navigate to your dbt project in SyncSage
Click on "Run" to execute your models
View the logs and results in the SyncSage interface

You can also schedule dbt runs to execute automatically at specified intervals.

Next Steps

Now that you understand the basics of dbt, you might want to explore: