Get to Data Reporting Faster With Fivetran dbt Packages
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 StackAnalytics 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 RequiredThese 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.
- dbt: dbt Core or dbt Cloud
- Data visualization tool of choice
How Fivetran HelpsFivetran 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 Helpsdbt 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 PackagesThe 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:
|Fivetran Automated Connector||Tables for normalized data|
|Fivetran dbt Packages|
|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
*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
ResultsLearn more about each dbt package, including the transformation logic and table outputs:
- Fivetran dbt package for NetSuite
- Fivetran dbt package for Mailchimp
- Fivetran dbt package for Salesforce
- Fivetran dbt package for GitHub
- Fivetran dbt package for Marketo
- Coming soon: Fivetran dbt packages for Zendesk and more
- Get the Fivetran dbt packages at dbthub.
- Con tact firstname.lastname@example.org today if you have questions or would like to request additional dbt packages. Feel free to post questions on the #modeling channel of getdbt Slack.
- Watch the Fivetran dbt packages on the YouTube playlist
- Watch the recorded webinar: How Ritual's Ecommerce Data Stack Powers Their Customer Retention Program
- Review our recent participation in dbt Office Hours