Our analytics engineering team at Fivetran builds and maintains a robust library of open-source dbt™ data models (also known as dbt packages) that anyone can access. In fact, over 1,000 new projects are created each month using these data models.

As the team has written before, these data models help customers of all sizes turn raw connector data into the tables they need to solve their myriad of business use cases. Whether you’re trying to analyze deal opportunities from Salesforce, create a general ledger report with QuickBooks data or analyze return on ad spend across your multiple advertising platforms — we have a data model for you.

But, we often get asked the question, “How exactly do the data models work?”

So, for the first time, we’re taking a step behind the curtain and showing you how our data models extend our connector entity relationship diagrams (ERDs) to create those analytics-ready tables.

Fivetran entity relationship diagrams (ERDs)

First, a quick overview of our ERDs. For our SaaS connectors, we create detailed diagrams that illustrate how the various data points from each data source relate. You can find these in the connector documentation. Here, for example, is QuickBooks.

If you are building out reporting, it’s important to understand these relationships and how you can link together key metrics and dimensions like revenue, invoice and expenses. Without these diagrams, you’ll likely waste critical time trying to engineer joins and calculations in data to varying degrees of success.

Fivetran data models

These ERDs show the state of the raw data as we load it from the SaaS connector. But, that doesn’t mean the data is ready to be analyzed. You have the raw tables you need for revenue and expenses, but do you know what your overall profit is? Or how about your losses? To perform that analysis, you need to transform your data. You have to join together tables across the ERD and execute calculations. 

This is typically a manual job done by data analysts or analytics engineers using SQL or data transformations tooling. To make this process easier, and to expedite the time to insight, our analytics engineering team works with customers across all industries and company sizes to create and standardize some of the most common use cases in our data models. 

These data models perform the majority of the foundational work that occupies an analysts’ time. The models identify the relevant entities in the ERD and perform various transformations. What they ultimately produce are the output tables our customers say are most needed for their analytical workflows.

Fivetran data models extending the ERD

Let’s dive into the QuickBooks example in a bit more detail. Suppose you want to generate a comprehensive general ledger encompassing all your transactions within QuickBooks. This  manual process between the analytics and finance team would require configuring data into the appropriate structure for the ledger. Fortunately, our QuickBooks data model offers an output feature that facilitates this process. 

A general ledger serves as a central repository for recording and organizing all financial transactions of a business. It consists of various accounts, such as assets, liabilities, equity, revenue and expenses. Each transaction is meticulously documented in the appropriate account, including key details like the date, description and amount.

To derive the general ledger from Fivetran's QuickBooks schema, our data model initially identifies the necessary tables to build the model. In the case of the QuickBooks general ledger, we focus on tables that contain transaction data and associated transaction details.

The relevant tables are as follows:

• BILL
• BILL_PAYMENT
• CREDIT_CARD_PAYMENT
• CREDIT_MEMO
• DEPOSIT
• INVOICE
• JOURNAL_ENTRY
• PAYMENT
• PURCHASE
• REFUND_RECEIPT
• SALES_RECEIPT
• TRANSFER
• VENDOR_CREDIT

Next, we create an intermediate model for each type of transaction, which defines a debit and credit record for each individual transaction. Additionally, we incorporate additional line items to enrich the models with further transaction details. These models are referred to as "double entry" models. For instance, the double entry model for bill transactions is named INT_QUICKBOOKS__BILL_DOUBLE_ENTRY. By associating each transaction record, the General Ledger gains a comprehensive understanding of the transaction. The additional line item joins populate the models with more detailed information about each transaction.

Finally, our general ledger consolidates data from each double entry model by performing a union operation. We incorporate additional joins to include relevant account details and incorporate calculations to provide the running balance for each account.

And, we are always maintaining and updating our data models based on customer feedback. For example, we have seen customers utilize more transaction type tables (such as credit_card_payment) to generate their general ledgers. Our team will handle adding these into the data models to ensure your ledger always includes all transaction types and rework is not needed on your end.

[CTA_MODULE]

Accelerating your time to insight

With our data models, we’ve automated this previously manual and inefficient process  . We also help you accelerate your time to insight by providing orchestration methods for these data models like our new Quickstart data models - which supports QuickBooks and 14 other data models - and dbt Core* integration. 

You can spend less time now on building and maintaining your ELT pipelines and more time on the data-driven insights your company needs to run their business. Just ask Carl-Johan Wehtje, who works in BI & Analytics at Snowflake:

We use Google, Facebook and LinkedIn to run digital ads, and we ingest this data with Fivetran. Fivetran’s data models automatically clean up our raw data sources and output them into one model. We immediately get a clean and normalized reporting set, enabling us to focus on more complex calculations and joining work. It means we can expose this data to our end users faster, so that they can start drawing insights from it.

The best part? All of this is absolutely free. Free data models and orchestration have no impact on Fivetran consumption - and often help teams lower their data warehouse computational costs. Take our customer Learnerbly for example, who generates faster reporting with our Fivetran data models and dbt Core integration. 

Download a data model and set up your first Transformation. Not a Fivetran customer? Start a 14 day free trial of Fivetran!

*dbt Core is a trademark of dbt Labs, Inc. All rights therein are reserved to dbt Labs, Inc. Fivetran Transformations is not a product or service of or endorsed by dbt Labs, Inc.

[CTA_MODULE]