Introduction

The Fivetran dbt package for NetSuite generates balance sheets and income statements, along with related tables that tie transactions with reporting and accounting periods, so that using Fivetran and dbt, you can:  

• Set up zero-maintenance data pipelines for NetSuite in minutes
• Easily create reports and dashboards of NetSuite data in the data visualization tool of your choice.
• Combine your NetSuite data with other data to get a full view of your business

In addition, this approach helps you more flexibly structure your reports for important finance activities, such as aggregating historical data and converting currencies. You can then enrich this NetSuite data with related sales and account data to get a full view of your business finances.

Background

The industry is moving from a legacy ETL architecture for building data pipelines 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. Fivetran’s core strengths are the “E” and the “L.” For extraction, Fivetran ensures zero-maintenance data pipelines with adaptive connectors that handle changes in source APIs and schemas. For loading, Fivetran delivers expertly prebuilt schemas for 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 transformations. Fivetran’s dbt package for NetSuite is the first of a planned series of packages to enable the most powerful, flexible, and sophisticated transformations possible for Fivetran customers.

The Challenge With NetSuite Reporting

You likely know that NetSuite is a great ERP, but its reporting challenges include:

1. Limited internal reporting tools: Reporting is constrained by what the UI offers
2. Siloed reporting: Keeping data solely within NetSuite means not being able to join data from other sources to paint the complete picture.
3. Brittle data pipelines: NetSuite has two commonly used APIs available. One is based on the SOAP protocol and the other is a JDBC API. Our experience with their SOAP API is that there are inconsistencies between objects shown in the UI, documentation, and the actual data from the API. The data set that comes through the UI is also incomplete, with many tables, such as transactions and transaction-related tables, missing. The other option is to go through the JDBC driver, which comes with a dense, obscure 129-page instruction guide.

What’s Required

These challenges require solutions in a number of areas. First, a great solution should include easy, flexible reporting of NetSuite data with your data visualization tool of choice. Second, you should be able to easily combine NetSuite 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:

• NetSuite: In order to pull data from the NetSuite JDBC driver, you’ll need access to the SuiteAnalytics Connect license, which allows access to the full data set as shown in the UI.
• Fivetran:
• Account, or free 14-day trial, which includes access to the NetSuite Connector
• Sync required tables, listed here
• Fivetran dbt package for NetSuite
• 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 NetSuite’s JDBC driver without having to read through the instruction manual, allocate developer resources, or do any coding at all. The setup guide is only one page that covers how to pull required fields such as role ID and assign the correct permissions to that role. Any changes in the NetSuite API or schema are automatically handled.

How dbt Helps

dbt is the “T” in the ELT process that allows analysts to transform data in-warehouses using SQL. The Fivetran dbt package for Netsuite applies industry best practices to one of the most complex data models in Netsuite–the income statement. This package takes care of all data cleaning and joins the 17 tables needed for this data model so you can spend your time modeling your unique business logic.

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.

Using Fivetran dbt for NetSuite:

Depending on your dbt deployment choice, there are two ways to make use of Fivetran's new package.

The following videos walk you through using Fivetran with dbt for NetSuite and these steps:

1. Fivetran: Configure Fivetran for the relevant NetSuite tables
2. dbt: Instructions for using the dbt package, whether with dbt Core or dbt Cloud

For dbt Core users, we offer:

And for dbt Cloud folks, use the following:

Results

Here are sample table outputs from the dbt package as well as visualizations and dashboards that come “out of the box” with the package:New tables created in the warehouse by the dbt package:

You can also show the data flow from sources to destination. Examples follow.

Transactions (click to zoom in for detail)

Income statement (click to zoom in for detail)

Balance sheet  (click to zoom in for detail)

All three final models together  (click to zoom in for detail)

Data visualizations  (click to zoom in for detail)

Next Steps

• Get the Fivetran dbt package for NetSuite
• Contact beta@fivetran.com 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
• Join Fivetran for dbt Office Hours on Tuesday, 10am PT, May 12
• Learn about Fivetran’s journey to build our NetSuite Connector

Get the Fivetran dbt package for NetSuite

Read blog