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.
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.
- 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: