Get to data reporting faster with Fivetran dbt packages

Add powerful transformations, connect more data sources and drive flexible reporting.
May 25, 2020

With Fivetran, you can load all of your data into your warehouse with a few clicks. And you don't have to worry about data pipeline maintenance thanks to automated connectors that keep up with changes in source APIs and schemas. Now, with Fivetran dbt packages, we're making it even easier for you to start building reports on that data. Packages will:

  • Take care of all the basic table standardization and join the tables needed for the data models so you can spend your time modeling your unique business logic.
  • Staging models are created to standardize column names and filter out records that have been soft-deleted.
  • Intermediate models perform all necessary joining of the tables and data aggregations.
  • Add data tests to catch if something changes in your source data that will break downstream reporting.
  • All primary keys have built-in tests for uniqueness and non-null values.
  • Freshness tests will make sure your source data is not stale.
  • Start building visualizations immediately, or use dbt to structure the underlying data to better reflect your unique business logic as well as to combine your sources together.
  • Final output models pair nicely with your favorite BI or visualization tool. Simply link the new tables to your BI tool and generate dashboards unique to your company.

ELT for the modern data stack

Analytics environments are moving from legacy data integration approaches with an ETL architecture to ELT.

ELT consists of extracting (E) data from sources, loading (L) into a target destination, and transforming (T) within that target, such as a cloud data warehouse. The new ELT architecture offers significant performance, flexibility, and cost advantages: shorter data loading times, transformations written in SQL instead of scripting languages, no dedicated ETL tier required, and so on.

Our core strengths are the “E” and the “L.” For extraction, Fivetran offers zero-maintenance data pipelines with adaptive connectors that automatically handle changes in source APIs and schemas. For loading, Fivetran delivers data organized into expertly prebuilt schemas to your target destination.

While Fivetran also handles transformations, we want to ensure customers can enjoy best-in-breed capabilities for every step of extract, load and transform. dbt (data build tool) from Fishtown Analytics is the most popular open-source project for SQL-based transformations. Therefore, Fivetran is offering advanced transformations through a series of dbt packages to enable the most powerful, flexible, and sophisticated transformations possible for Fivetran customers.

The output of these dbt packages are clear, comprehensible tables that make reporting, visualizations, and dashboards much easier.

What's required

These challenges require solutions in a number of areas.

First, a great solution should include easy, flexible reporting of the application’s data with your data visualization tool of choice. Second, you should be able to easily combine the source application’s data with other data. Finally, you should be able to achieve both items above without worrying about data pipeline engineering and changes in source APIs.

The prerequisites to get there include:

  • Access rights to your source application: In order to pull data from your application, you’ll need appropriate access rights to the data set.
  • Fivetran:
  • Account, or free 14-day trial, which includes access to Connectors for your sources
  • Sync required tables, listed on the dbt package’s README  (for example, here for the NetSuite package)
  • Fivetran dbt package for your source application
  • dbt: dbt Core or dbt Cloud
  • Data visualization tool of choice

How Fivetran helps

Fivetran ingests (“extract”) and loads data to achieve automated data integration. Fivetran offers an easy way to extract this data from the source without having to read through the instruction manual, allocate developer resources, or do any coding at all.

For example, the setup guide for the Fivetran NetSuite Connector is only one page that covers how to pull required fields such as role ID and assign the correct permissions to that role.

Additionally, Fivetran generates an easily comprehensible and expertly pre-built target schema to load data (the “L” in ELT) that can be used much more easily for reporting than the application’s operational tables.

How dbt helps

dbt is the “T” in the ELT process that allows analysts to transform data in-warehouses using SQL. dbt encapsulates software delivery best practices, including CI/CD and testing, to efficiently develop trusted data sets.

Fivetran dbt packages

The Fivetran dbt packages apply industry best practices to complex data models. The packages take care of some table standardization and join the tables needed for the data models so you can spend your time modeling your unique business logic.

Fivetran’s dbt packages use the target schemas generated by Fivetran automated connectors as a starting point. Then, the dbt package generates additional tables for more sophisticated reporting goals.  Packages are compatible with all cloud warehouses Snowflake, Amazon Redshift and Google BigQuery and query logic can be easily edited and reused across warehouses. You can schedule the package in dbt Cloud to keep your data fresh for the business stakeholders.

This table summarizes the different tables created by Fivetran:

Tables Generated
Fivetran Automated Connector Tables for normalized data
Fivetran dbt Packages Tables Generated
dbt Source Package* Tables of lightly cleansed data ; acts as a data dictionary (This package is automatically called from the Transform Package so there is no need to install it manually. It is not dependent on other dbt packages.)
dbt Package (Transform)** Tables for transformed data to attain aggregated data (Dependent upon Source Package which installs automatically.)

*in dbt hub, these will be labeled as <data_source>_source. For example, there are two GitHub packages in dbthub by Fivetran. “Github_source” would be the Source Package**in dbt hub, these will be labeled as with the data source name. For example, there are two GitHub packages in dbthub by Fivetran. “github” would be the “Transform”  Package

Results

Learn more about each dbt package, including the transformation logic and table outputs:

Next steps

Free 14 Day Trial – Experience data integration in minutes, not days!

CREATE YOUR ACCOUNT TODAY

Commencer gratuitement

Rejoignez les milliers d’entreprises qui utilisent Fivetran pour centraliser et transformer leur data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Product
Product

Get to data reporting faster with Fivetran dbt packages

Get to data reporting faster with Fivetran dbt packages

May 25, 2020
May 25, 2020
Get to data reporting faster with Fivetran dbt packages
SUJETS
No items found.
PARTAGER
Add powerful transformations, connect more data sources and drive flexible reporting.

With Fivetran, you can load all of your data into your warehouse with a few clicks. And you don't have to worry about data pipeline maintenance thanks to automated connectors that keep up with changes in source APIs and schemas. Now, with Fivetran dbt packages, we're making it even easier for you to start building reports on that data. Packages will:

  • Take care of all the basic table standardization and join the tables needed for the data models so you can spend your time modeling your unique business logic.
  • Staging models are created to standardize column names and filter out records that have been soft-deleted.
  • Intermediate models perform all necessary joining of the tables and data aggregations.
  • Add data tests to catch if something changes in your source data that will break downstream reporting.
  • All primary keys have built-in tests for uniqueness and non-null values.
  • Freshness tests will make sure your source data is not stale.
  • Start building visualizations immediately, or use dbt to structure the underlying data to better reflect your unique business logic as well as to combine your sources together.
  • Final output models pair nicely with your favorite BI or visualization tool. Simply link the new tables to your BI tool and generate dashboards unique to your company.

ELT for the modern data stack

Analytics environments are moving from legacy data integration approaches with an ETL architecture to ELT.

ELT consists of extracting (E) data from sources, loading (L) into a target destination, and transforming (T) within that target, such as a cloud data warehouse. The new ELT architecture offers significant performance, flexibility, and cost advantages: shorter data loading times, transformations written in SQL instead of scripting languages, no dedicated ETL tier required, and so on.

Our core strengths are the “E” and the “L.” For extraction, Fivetran offers zero-maintenance data pipelines with adaptive connectors that automatically handle changes in source APIs and schemas. For loading, Fivetran delivers data organized into expertly prebuilt schemas to your target destination.

While Fivetran also handles transformations, we want to ensure customers can enjoy best-in-breed capabilities for every step of extract, load and transform. dbt (data build tool) from Fishtown Analytics is the most popular open-source project for SQL-based transformations. Therefore, Fivetran is offering advanced transformations through a series of dbt packages to enable the most powerful, flexible, and sophisticated transformations possible for Fivetran customers.

The output of these dbt packages are clear, comprehensible tables that make reporting, visualizations, and dashboards much easier.

What's required

These challenges require solutions in a number of areas.

First, a great solution should include easy, flexible reporting of the application’s data with your data visualization tool of choice. Second, you should be able to easily combine the source application’s data with other data. Finally, you should be able to achieve both items above without worrying about data pipeline engineering and changes in source APIs.

The prerequisites to get there include:

  • Access rights to your source application: In order to pull data from your application, you’ll need appropriate access rights to the data set.
  • Fivetran:
  • Account, or free 14-day trial, which includes access to Connectors for your sources
  • Sync required tables, listed on the dbt package’s README  (for example, here for the NetSuite package)
  • Fivetran dbt package for your source application
  • dbt: dbt Core or dbt Cloud
  • Data visualization tool of choice

How Fivetran helps

Fivetran ingests (“extract”) and loads data to achieve automated data integration. Fivetran offers an easy way to extract this data from the source without having to read through the instruction manual, allocate developer resources, or do any coding at all.

For example, the setup guide for the Fivetran NetSuite Connector is only one page that covers how to pull required fields such as role ID and assign the correct permissions to that role.

Additionally, Fivetran generates an easily comprehensible and expertly pre-built target schema to load data (the “L” in ELT) that can be used much more easily for reporting than the application’s operational tables.

How dbt helps

dbt is the “T” in the ELT process that allows analysts to transform data in-warehouses using SQL. dbt encapsulates software delivery best practices, including CI/CD and testing, to efficiently develop trusted data sets.

Fivetran dbt packages

The Fivetran dbt packages apply industry best practices to complex data models. The packages take care of some table standardization and join the tables needed for the data models so you can spend your time modeling your unique business logic.

Fivetran’s dbt packages use the target schemas generated by Fivetran automated connectors as a starting point. Then, the dbt package generates additional tables for more sophisticated reporting goals.  Packages are compatible with all cloud warehouses Snowflake, Amazon Redshift and Google BigQuery and query logic can be easily edited and reused across warehouses. You can schedule the package in dbt Cloud to keep your data fresh for the business stakeholders.

This table summarizes the different tables created by Fivetran:

Tables Generated
Fivetran Automated Connector Tables for normalized data
Fivetran dbt Packages Tables Generated
dbt Source Package* Tables of lightly cleansed data ; acts as a data dictionary (This package is automatically called from the Transform Package so there is no need to install it manually. It is not dependent on other dbt packages.)
dbt Package (Transform)** Tables for transformed data to attain aggregated data (Dependent upon Source Package which installs automatically.)

*in dbt hub, these will be labeled as <data_source>_source. For example, there are two GitHub packages in dbthub by Fivetran. “Github_source” would be the Source Package**in dbt hub, these will be labeled as with the data source name. For example, there are two GitHub packages in dbthub by Fivetran. “github” would be the “Transform”  Package

Results

Learn more about each dbt package, including the transformation logic and table outputs:

Next steps

Free 14 Day Trial – Experience data integration in minutes, not days!

CREATE YOUR ACCOUNT TODAY
Topics
No items found.
Share

Articles associés

No items found.
No items found.
No items found.

Commencer gratuitement

Rejoignez les milliers d’entreprises qui utilisent Fivetran pour centraliser et transformer leur data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.