π Integrating Fivetran with dbt (Data Build Tool)
Fivetran automates data ingestion, while dbt enables data transformation on top of your destination (e.g., Snowflake). Together, they form a modern ELT pipeline.
π§ Overviewβ
Fivetran extracts data from sources and loads it into a destination warehouse.
dbt transforms that raw data into cleaned, modeled data for analytics, reporting, or machine learning.
π οΈ Prerequisitesβ
- A working Fivetran pipeline (data loaded to warehouse like Snowflake, BigQuery, etc.)
- A dbt project set up locally or in dbt Cloud
- Destination schema where Fivetran loads data
- Access to the warehouse (Snowflake, BigQuery, Redshift, etc.)
βοΈ Steps to Integrateβ
β Step 1: Confirm Fivetran Destination Detailsβ
- Note the:
- Warehouse account name
- Database & Schema
- Role used
- Tables created by Fivetran
β Step 2: Initialize a dbt Projectβ
If you havenβt already, create a dbt project:
dbt init my_project
Inside profiles.yml, add your connection details:
my_profile:
target: dev
outputs:
dev:
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
role: "{{ env_var('SNOWFLAKE_ROLE') }}"
database: "{{ env_var('SNOWFLAKE_DATABASE') }}"
warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"
schema: "{{ env_var('SNOWFLAKE_SCHEMA') }}"
threads: 4
β Step 3: Model Fivetran Data in dbtβ
- In your dbt
models/directory, create models that select and clean data from Fivetran-managed tables.
-- models/stg_customers.sql
with raw as (
select * from {{ source('fivetran_schema', 'customers') }}
)
select
id,
first_name,
last_name,
created_at::timestamp as created_at,
updated_at::timestamp as updated_at
from raw
- Configure sources in
models/schema.yml:
version: 2
sources:
- name: fivetran_schema
tables:
- name: customers
β Step 4: Run dbtβ
Run transformations with:
dbt run
For testing and documentation:
dbt test
dbt docs generate
dbt docs serve
π Optional: Automate with dbt Cloud + Fivetranβ
-
Link your dbt Cloud account to Fivetran
- Go to Fivetran β Connector Settings β Transformations
- Click Enable dbt Cloud Integration
- Provide API Key and Account info
-
Trigger dbt jobs after Fivetran sync
- Define sync-triggered jobs in dbt Cloud
- Fivetran will automatically trigger dbt after a successful sync
π― Benefitsβ
| Feature | Description |
|---|---|
| β Seamless ELT | Load with Fivetran, transform with dbt |
| β±οΈ Auto-Orchestration | Fivetran triggers dbt after sync |
| π Reusable Models | dbt lets you build modular, tested models |
| π Secure Access | OAuth/Secrets-based authentication |
π Additional Resourcesβ
- Fivetran + dbt Integration Guide
- dbt Documentation
- dbt Cloud Setup
- Fivetran Transformations Overview
π‘ Use Fivetran for ingestion and dbt for transformation to build clean, scalable pipelines with zero manual effort!