The main focus of this package is to provide users insights into their QuickBooks data that can be used for financial statement reporting and deeper analysis. The package achieves this by:
- Creating a comprehensive general ledger that can be used to create financial statements with additional flexibility.
- Providing historical general ledger month beginning balances, ending balances, and net change for each account.
- Enhancing Accounts Payable and Accounts Receivables data by providing past and present aging of bills and invoices.
- Pairing all expense and sales transactions in one table with accompanying data to provide enhanced analysis.
Please be aware that the dbt_quickbooks and dbt_quickbooks_source packages were developed with single currency company data. As such, the package models will not reflect accurate totals if your QuickBooks account has Multi-Currency enabled.
This package contains transformation models designed to work simultaneously with our QuickBooks source package. A dependency on the source package is declared in this package’s
packages.yml file, so it will automatically download when you run
dbt deps. The primary outputs of this package are described below. Intermediate models are used to create these output models.
|quickbooks__general_ledger||Table containing a comprehensive list of all transactions with offsetting debit and credit entries to accounts.|
|quickbooks__general_ledger_by_period||Table containing the beginning balance, ending balance, and net change of the dollar amount for each month since the first transaction. This table can be used to generate a balance sheet and income statement for your business.|
|quickbooks__profit_and_loss||Table containing all revenue and expense account classes by calendar year and month enriched with account type, class, and parent information.|
|quickbooks__balance_sheet||Table containing all asset, liability, and equity account classes by calendar year and month enriched with account type, class, and parent information.|
|quickbooks__ap_ar_enhanced||Table providing the amount, amount paid, due date, and days overdue of all bills and invoices your company has received and paid along with customer, vendor, department, and address information for each invoice or bill.|
|quickbooks__expenses_sales_enhanced||Table providing enhanced customer, vendor, and account details for each expense and sale transaction.|
Include in your
packages: - package: fivetran/quickbooks version: [">=0.5.0", "<0.6.0"]
By default, this package looks for your QuickBooks data in the
quickbooks schema of your target database.
If this is not where your QuickBooks data is, add the below configuration to your
... config-version: 2 vars: quickbooks_database: your_database_name quickbooks_schema: your_schema_name
Changing the Build Schemalink
By default this package will build the QuickBooks staging models within a schema titled (<target_schema> +
_quickbooks_staging) and QuickBooks final models within a schema titled (<target_schema> +
_quickbooks) in your target database. If this is not where you would like your modeled QuickBooks data to be written to, add the following configuration to your
... models: quickbooks: +schema: my_new_schema_name # leave blank for just the target_schema quickbooks_source: +schema: my_new_schema_name # leave blank for just the target_schema
This package takes into consideration that not every QuickBooks account utilizes the same transactional tables, and allows you to disable the corresponding functionality. By default, most variables’ values are assumed to be
true (with exception of purchase orders). Add variables for only the tables you want to disable or enable respectively:
... vars: using_address: false #disable if you don't have addresses in QuickBooks using_bill: false #disable if you don't have bills or bill payments in Quickbooks using_credit_memo: false #disable if you don't have credit memos in Quickbooks using_department: false #disable if you don't have departments in Quickbooks using_deposit: false #disable if you don't have deposits in Quickbooks using_estimate: false #disable if you don't have estimates in Quickbooks using_invoice: false #disable if you don't have invoices in Quickbooks using_invoice_bundle: false #disable if you don't have invoice bundles in Quickbooks using_journal_entry: false #disable if you don't have journal entries in Quickbooks using_payment: false #disable if you don't have payments in Quickbooks using_refund_receipt: false #disable if you don't have refund receipts in Quickbooks using_transfer: false #disable if you don't have transfers in Quickbooks using_vendor_credit: false #disable if you don't have vendor credits in Quickbooks using_sales_receipt: false #disable if you don't have sales receipts in QuickBooks using_purchase_order: true #enable if you want to include purchase orders in your staging models
After running the models within this package, you may want to compare the baseline financial statement totals from the data provided against what you expect. You can make use of the analysis functionality of dbt and run pre-written SQL to test these values. The SQL files within the analysis folder contain SQL queries you may compile to generate balance sheet and income statement values. You can then tie these generated values to your expected ones and confirm the values provided in this package are accurate.
Don’t see a model or specific metric you would have liked to be included? Notice any bugs when installing
and running the package? If so, we highly encourage and welcome contributions to this package!
Please create issues or open PRs against
main. Check out this post on the best workflow for contributing to a package.
This package has been tested on BigQuery, Snowflake, Redshift, and Postgres.
- Provide feedback on our existing dbt packages or what you’d like to see next
- Have questions or feedback, or need help? Book a time during our office hours here or shoot us an email at email@example.com.
- Find all of Fivetran’s pre-built dbt packages in our dbt hub
- Learn how to orchestrate your models with Fivetran Transformations for dbt Core™
- Learn more about Fivetran overall in our docs
- Check out Fivetran’s blog
- Learn more about dbt in the dbt docs
- Check out Discourse for commonly asked questions and answers
- Join the chat on Slack for live discussions and support
- Find dbt events near you
- Check out the dbt blog for the latest news on dbt’s development and best practices