Streamlining Data Integration: Connecting Two Different Databases with Fivetran and DBT

Integration process

Today, we dive into the world of data transformation with DBT and Fivetran. DBT restricts data transformation between discrete databases, while Fivetran opens new possibilities by enabling seamless data transformation across different database environments. Let’s unlock the power of efficient data handling together!

To establish a connection between DBT, Fivetran, and Snowflake, you can follow the below steps.

STEP 1: Fivetran Integration

a. Log in to Fivetran account through the dashboard.

URL: Dashboard | Fivetran

b. If you haven’t established a Fivetran account yet, you can initiate the process by creating a new account.

URL: Sign up | Fivetran

STEP 2: Configure Source

Configure Fivetran connectors to access data from the desired sources.

The steps to create a source connector are as follows:

a. Select source connector and initiate the setup by clicking.

Datasource

b. This action will navigate to the source connector page. Provide all the required information.

c. Click on “Save & Test.”

d. The system will verify the connection details you provided. Upon completion, it will display “All connection tests passed.”

Setup for Source

STEP 3: Configure Destination

Establish a connection between Fivetran, Here I have chosen Snowflake as a destination data warehouse.

Steps for Connecting Destination Connector:

a. Select destination connector and initiate the setup process by clicking.

b. This will navigate to the destination connector page. Provide all the required information.

c. Select “Save & Test.”

d. The system will verify the provided connection details, and upon completion, it will indicate “All connection tests passed.

Setup for Destination

STEP 4: Initial Data Synchronization

To get historical data from the connected sources to target data warehouse, execute an initial data synchronization.

a. Navigate to connectors tab and select the source connector (snowflake_db).

Source Connector

b. Next, on the subsequent page, select the “Sync Now” icon located in the right corner.

Sync Source data

c. Upon clicking the sync now icon, the synchronization process will start.

The “Sync Now” functionality in Fivetran enables you to manually initiate the synchronization of data from source systems to destination data warehouse.

a. Real-time Data Updates: Enable real-time data sync for analytics, ensuring up-to-the-minute reporting.

b. Improved Operational Efficiency: Enhance operational efficiency with timely data, enabling agile decision-making and maintaining a competitive edge in dynamic environments.

STEP 5: Add Transformation (DBT)

a. Navigate to Transformations: In the Fivetran dashboard, go to the “Transformations” section.

Transformation Page

b. Establish Transformation: Initiate the creation of a new transformation job.

c. Select the transformation method, here transformation method with dbt is used.

Add Transformation

d. Copy the public key.

Public key Generation

e. Go to GitHub account →add deploy keys in repository.

Add deploy key

f. After completing the configuration, you will be directed to the transformation page, where models will synchronize with Fivetran.

Syncing models

STEP 6: Establish a DBT Project

a. Create model in dbt

Reference link: https://medium.com/@kalayarasi.rajendran/schedule-dbt-models-with-apache-airflow-using-docker-container-a4a821a575cf

b. In crafting this blog, I’ve developed a dbt model named “student_details.”

Code for target table

c. GitHub link: KalayarasiR/fivetran (github.com)

STEP 7: GitHub Integration

a. Initialize Git Repository

b. Navigate to dbt project directory and initialize a Git repository

c. Link local Git repository to the GitHub repository

git init
  git add README.md
  git commit -m "first commit"
  git branch -M main
  git remote add origin git@github.com:KalayarasiR/poc.git
  git push -u origin main

STEP 8: Initiate first transformation with Fivetran and dbt

a. In the source, I’ve established “student_details” as the data source, and Fivetran will seamlessly transfer this data to the target database as a staging area through its synchronization process.

CREATE TABLE POC_SOURCE.STUDENT.STUDENT_DETAILS (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATE,
    Gender VARCHAR(10),
    Address VARCHAR(255),
    City VARCHAR(50),
    State VARCHAR(50),
    Country VARCHAR(50),
    Email VARCHAR(100),
    Load_dts TIMESTAMP_NTZ(9)
);

INSERT INTO POC_SOURCE.STUDENT.STUDENT_DETAILS (StudentID, FirstName, LastName, DateOfBirth, Gender, Address, City, State, Country, Email, Load_dts)
VALUES
    (1, 'John', 'Doe', '1995-03-15', 'Male', '123 Main St', 'Anytown', 'CA', 'USA', 'john.doe@email.com', CURRENT_TIMESTAMP),
    (2, 'Jane', 'Smith', '1998-08-22', 'Female', '456 Oak St', 'Sometown', 'NY', 'USA', 'jane.smith@email.com', CURRENT_TIMESTAMP),
    (3, 'Bob', 'Johnson', '1990-12-05', 'Male', '789 Pine St', 'Othertown', 'TX', 'USA', 'bob.johnson@email.com', CURRENT_TIMESTAMP),
    (4, 'Alice', 'Williams', '1993-06-10', 'Female', '101 Elm St', 'Newtown', 'FL', 'USA', 'alice.williams@email.com', CURRENT_TIMESTAMP),
    (5, 'Charlie', 'Brown', '1997-02-28', 'Male', '202 Birch St', 'Hometown', 'OH', 'USA', 'charlie.brown@email.com', CURRENT_TIMESTAMP),
    (6, 'Eva', 'Miller', '1994-09-17', 'Female', '303 Maple St', 'Smalltown', 'IL', 'USA', 'eva.miller@email.com', CURRENT_TIMESTAMP),
    (7, 'David', 'Taylor', '1999-11-03', 'Male', '404 Cedar St', 'Bigtown', 'GA', 'USA', 'david.taylor@email.com', CURRENT_TIMESTAMP),
    (8, 'Grace', 'Moore', '1991-07-12', 'Female', '505 Redwood St', 'Greattown', 'WA', 'USA', 'grace.moore@email.com', CURRENT_TIMESTAMP),
    (9, 'Frank', 'Davis', '1996-04-20', 'Male', '606 Spruce St', 'Littletown', 'NC', 'USA', 'frank.davis@email.com', CURRENT_TIMESTAMP),
    (10, 'Helen', 'Anderson', '1992-01-08', 'Female', '707 Fir St', 'Wonderland', 'MI', 'USA', 'helen.anderson@email.com', CURRENT_TIMESTAMP);

b. Fivetran will generate a staging environment within the target Snowflake account.

Source DB and Target DB

c. Add Deployment.yml in project

jobs:
  - name: Update_Version
    schedule: '*/30 * * * *' 
    steps:
     - name: version
       command: dbt --version

  - name: Run_Job
    targetName: dev
    schedule: 1 * * * * 
    steps:
      - name: run models 
        command: dbt run 
      - name: test models
        command: dbt test 

“After adding the deployment.yml file to the repository, it will be reflected on the transformation page.”

d. The job is configured to run hourly, starting at the first minute, and can also be manually triggered when needed.

By selecting “Run Now,” it will initiate the specified jobs outlined in the deployment.yml file.

Run Status

e. Fivetran has now produced a target table in the destination.

Target table data

Advantages of Integrating DBT with Fivetran :

1. Effortless Data Transformation: Simplify the process of transforming data from source to destination.
2. Flexible Sync Scheduling: Enable syncing processes on a customizable schedule — daily, weekly, or monthly.
3. Convenient DBT Run Scheduling: Schedule DBT runs at convenience for seamless transformations.
4. Versatile Source and Destination Connections: Connect diverse data sources for both the origin and destination with ease.

Conclusion:
This powerful integration trio ensures smooth data transformation and synchronization from source to destination, standing as an efficient solution for real-time updates.

About Us:
Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well-known for superior delivery.

Website: https://bi3technologies.com/

Follow us on,
LinkedIn: https://www.linkedin.com/company/bi3technologies
Instagram: https://www.instagram.com/bi3technologies/
Twitter: https://twitter.com/Bi3Technologies
Personal LinkedIn: https://www.linkedin.com/in/kalayarasi-r-a39962213