PR #138 introduces the following updates:
- Added
nulliflogic to account for "divide by zero" errors inint_quickbooks__deposit_double_entryandint_quickbooks__deposit_transactionsfor whentotal_amountvalues from thedepositsource table are zero.
- Added integrity test
deposit_ledger_amounts_matchwithin integration tests to compare deposit amounts betweenint_quickbooks__deposit_double_entryand deposittransaction_typevalues inquickbooks__general_ledger. - Modified seed files in
integration_teststo reproduce issue and confirm fixes.
New major feature alert! Multicurrency is here!
- We have introduced multicurrency support to the following models by providing these new fields that convert transaction amounts by their exchange rates. (PR #134)
- IMPORTANT: We do not yet have proper
converted_amountvalues for credit card payments and transfers. Currently it is being brought in as the equivalent ofamount, so you might see slight discrepancies if need these values converted as well. Please open an issue with us to help work with us to support this feature. - We have kept the existing cash value fields that provides amounts and balances to ensure full coverage to customers regardless of their currency setup. (PR #134)
- The new multicurrency fields that fulfill the same function as the respective existing fields is below:
| Model | New Multicurrency Fields | Respective Single Currency Fields |
|---|---|---|
| quickbooks__general_ledger | adjusted_converted_amount, running_converted_balance |
adjusted_amount, running_balance |
| quickbooks__general_ledger_by_period | period_net_converted_change, period_beginning_converted_balance, period_ending_converted_balance |
period_net_change, period_beginning_balance, period_ending_balance |
| quickbooks__profit_and_loss | converted_amount |
amount |
| quickbooks__balance_sheet | converted_amount |
amount |
| quickbooks__cash_flow_statement | cash_converted_ending_period, cash_converted_beginning_period, cash_converted_net_period |
cash_ending_period, cash_beginning_period, cash_net_period |
| quickbooks__ap_ar_enhanced | total_converted_amount, estimate_total_converted_amount, total_current_converted_payment |
total_amount, estimate_total_amount, total_current_payment |
| quickbooks__expenses_sales_enhanced | total_converted_amount, converted_amount |
total_amount, amount |
- Introduced
*_converted_*type fields in our intermediate models to convert amounts where exchange rates exist for those transactions. If there is no exchange rate, these*_converted_*fields will default back to the already existing fields created for single currency, and all downstream calculations should match the single currency amount, balance and cash values. (PR #134) - For double-entry models that applied a cross-join to either AP/AR accounts, we are now mapping those accounts based on the
currency_idvalue in theaccountssource table for those transactions. (PR #134) - In the
analysisfolder, added theconverted_balanceto thequickbooks__balance_sheetandending_converted_balanceto thequickbooks__income_statementmodels. (PR #134)
- Adjusted logic for discount sales receipt lines in
int_quickbooks__sales_receipt_double_entrymodel to bring in these values properly as negative adjusted amounts in thequickbooks__general_ledger. (PR #130) - Applied filter in
int_quickbooks__invoice_double_entryto filter out 'Accounts Receivable' accounts that are inactive. (PR #134)
- Added consistency and integrity tests within integration tests for all end models. (PR #130) & (PR 134)
- Appended
using_credit_card_payment_txncheck inget_enabled_unioned_modelsmacro tofalseto match consistency of how the variable is defined throughout our Quickbooks models by default.
- Updated README to reflect the new multicurrency support. (PR #134)
- Added yml documentation with the new multicurrency fields and descriptions. (PR #134)
PR #125 includes the following updates:
- The
period_first_dayandperiod_last_dayfields were mistakenly left out in the quickbooks__profit_and_loss model although they were intended to be introduced as new fields in the v0.13.0 release. We have added these fields into the model.
PR #124 includes the following updates:
- Updates the int_quickbooks__invoice_join and downstream quickbooks__ap_ar_enhanced models to include and require the
using_paymentsconfig. Previously, these models would fail if thepaymentor thepayment_linesource tables did not exist. - Corrects the misspelled
customer_vendor_webistefield tocustomer_vendor_websiteinquickbooks__ap_ar_enhanced.
- Updates the logic for the
amountfield in int_quickbooks__invoice_double_entry to useinvoice.total_amountonly on the condition when a bundle is associated with the invoice andinvoice.total_amountis 0, otherwiseinvoice_lines.amountis used.- This avoids double counting when aggregating invoice_line items and accounts for the edge cases where a bundle_id is involved.
- Updates the quickbooks__profit_and_loss and quickbooks__balance_sheet models to include both
period_first_dayandperiod_last_dayin addition tocalendar_date. This allows users to have greater flexibility in choosing which date to aggregate records upon.- Please note
calendar_dateis slated to be deprecated, and the fieldsperiod_first_dayandperiod_last_dayare both offered as replacements, depending on how your company performs their financial reporting.
- Please note
PR #123 includes the following updates:
- Added
source_relationto joins within the following models as it was previously missed:int_quickbooks__invoice_joinint_quickbooks__bill_joinint_quickbooks__refund_receipt_double_entryint_quickbooks__sales_receipt_double_entryquickbooks__balance_sheetanalysis model.
PR #119 includes the following updates:
- Included a default start and end date in the
int_quickbooks__general_ledger_date_spinelogic when generating the date spine. These default start and end dates will ensure the model still succeeds when no transactions are yet available.- The default start date will be one month in the past
- The default end date will be the current date
- Updated the maintainer PR template to resemble the most up to date format.
- Removed the check docs GitHub Action as it is no longer necessary.
PR #114 includes the following updates:
- Updated model
int_quickbooks__invoice_double_entryto account for thesales_item_account_idfield from theinvoice_linessource when determining theaccount_idassociated with an invoice.
PR #109 includes the following updates:
- Adjusted the joins within the below intermediate double entry models to be
left joinas opposed to aninner join. This update was necessary as there was the possibility of the respective account cte joins to return no records. If this was the case, the logic could erroneously remove transactions from the record.
PR #103 includes the following updates:
- Update seeds and configs in the integration tests folder to match what was updated upstream in the (source package PR #51) to correct timestamp fields that should be date fields (
due_date,transaction_date). Previously, some fields were getting interpreted as timestamps while some were interpreted as dates, leading to errors on downstream joins. In the upstream staging models,due_dateandtransaction_dateare now explicitly cast asdatetypes.
This will be a breaking change to those whose source tables still use the old timestamp formats, so please update your package version accordingly.
This PR includes the following updates:
- Updated intermediate double entry models that have
account_typeandaccount_sub_typefilters with configurable variables, since the type names used in the filter can be adjusted internally by QuickBooks customers. (PR #98) - Includes
items.asset_account_idas the second field of the coalesce for thepayed_to_account_idfield within theint_quickbooks__bill_payment_double_entrymodel to ensure all account_id types are taken into consideration when mapping the transaction to the proper account. (PR #100)
- The intermediate models where these variables were introduced in the models below:
| Updated model | New variables to filter on |
|---|---|
| int_quickbooks__bill_payment_double_entry | quickbooks__accounts_payable_reference |
| int_quickbooks__credit_memo_double_entry | quickbooks__accounts_receivable_reference |
| int_quickbooks__deposit_double_entry | quickbooks__undeposited_funds_reference |
| int_quickbooks__invoice_double_entry | quickbooks__sales_of_product_income_reference, quickbooks__accounts_receivable_reference |
| int_quickbooks__payment_double_entry | quickbooks__accounts_receivable_reference |
- Updated README with additional steps for configuration. This is an optional step since most customers will rely on the default account type/subtype values available.
PR #95 includes the following updates:
- Included
source_relationin all joins and window functions for models outputtingsource_relation. This is to prevent duplicate records in end models when using the unioning functionality. These updates were in the intermediate models, which flowed to downstream end models:quickbooks__general_ledgerquickbooks__expenses_sales_enhanced
- In end model
quickbooks__general_ledger, addedsource_relationas part of the generated surrogate keyunique_idto prevent duplicateunique_ids when using the unioning functionality.
- Added description for column
source_relationto the documentation.
- Updated test from a combination of columns to uniqueness of
unique_idinquickbooks__general_ledger. - Updated partitioning in certain models to include
source_relation. - Updated analysis
quickbooks__balance_sheetwith updated join strategy.
- Databricks compatibility! (#92)
PR #93 includes the following updates:
- Adjusted the purchase amount totals within the
int_quickbooks__purchase_transactionsmodel to factor in credits when calculating purchase amounts.
- Added logic to the
int_quickbooks__invoice_double_entrymodel to account for invoice discounts as they should be treated as contra revenue accounts that behavior differently from normal sale item detail invoice line items. (#85) - Updated the
cash_beginning_periodandcash_net_periodvalues to coalesce to 0 in thequickbooks__cash_flow_statementin order to ensure every row has a value, especially the first row in the sequence since it will always be null. (#88)
- Added
department_idto thequickbooks__general_ledgerand the upstream tables required for that change. (#63)- Please note that this field was not added to the downstream
quickbooks__general_ledger_by_period,quickbooks__balance_sheet,quickbooks__profit_and_loss, orquickbooks__cash_flow_statementmodels as this would require the grain of these models to be adjusted for thedepartment_id. This would likely cause more confusion in the initial output. As such, the field was omitted in the aggregate models to ensure consistency of these models. If you wish this to be included, please open a Feature Request to let us know!
- Please note that this field was not added to the downstream
- Included documentation within the DECISIONLOG centered around the behavior of how invoice discounts are handled within the
int_quickbooks__invoice_double_entrymodel. (#85)
- Leveraged the new
detail_typefield to ensure better accuracy when identifying invoice lines that should be accounted for in the general ledger calculations. (#85) - Incorporated the new
fivetran_utils.drop_schemas_automationmacro into the end of each Buildkite integration test job. (#87) - Updated the pull request templates. (#87)
- See the source package CHANGELOG for updates made to the staging layer in
dbt_quickbooks_source v0.8.0.
- Adding partitions by
class_idin appropriate models to ensure correct account amount aggregations inquickbooks__general_ledger,quickbooks__general_ledger_by_period,quickbooks__balance_sheet, andquickbooks__profit_and_lossmodels. (#77) - Modifying join in
int_quickbooks__general_ledger_balancesto account for nullclass_idvalues and bring in the correct non-zero balances. (#77)
- Replacing
account_namewithaccount_idas input for thegenerate_surrogate_keyfunction to fixunique_iduniqueness issues in thequickbooks__general_ledgermodel. A full refresh is recommended for accurate and consistent surrogate keys. (#73)
- Added
transaction_sourcetogenerate_surrogate_keyfunction to fixunique_iduniqueness issues in thequickbooks__general_ledgermodel. A full refresh is recommended for accurate and consistent surrogate keys, for more information please refer to dbt-utils release notes regardinggenerate_surrogate_key. (#62)
- Created the
quickbooks__cash_flow_statementmodel so customers can more easily produce their own cash flow statements. Default categorizations are created inint_quickbooks__cash_flow_classifications, where each account line is assigned acash_flow_type, with main types beingCash or Cash Equivalents,Operating,Investing, andFinancing. Theordinalvalue is also created based on thecash_flow_typefor ordering purposes. All values created are based on cash flow best practices. (#69) - For the
quickbooks__cash_flow_statement, customers can create and configure their owncash_flow_typeandordinalfor ordering purposes. See the README for details and use the seedcash_flow_statement_type_ordinal_examplefile for guidance). (#69) - Added
account_ordinalvalue toquickbooks__general_ledger_by_period,quickbooks__balance_sheetandquickbooks__profit_and_lossto allow customers to order their financial reports based on the account field values. The ordinals can be further configured by the customer. See the README for details and use the seedfinancial_statement_ordinal_examplefile for guidance). (#65) (#66) - Added
class_idtoquickbooks__general_ledger,quickbooks_general_ledger_by_period, andquickbooks__balance_sheet; add in class values for all intermediate models necessary to pass into final models. (#58). - Added
source_relationfield to all Quickbooks models to allow customers, if they have multiple Quickbooks connectors, to union them inside the package. (#62). - Added tests to all final models, particularly to test uniqueness across a combination of columns, including
source_relation. (#62) - Modified
int_quickbooks__retained_earningsintermediate model to accurately reflectaccount_namefield, from "Net Income / Retained Earnings Adjustment" to "Net Income Adjustment". (#66) - Updated README to follow latest package standards. (#71)
- Added
quickbooks_[source_table_name]_identifiervariables so it's easier to refer to source tables with different names. (#71)
PR #51 includes the following breaking changes:
- Dispatch update for dbt-utils to dbt-core cross-db macros migration. Specifically
{{ dbt_utils.<macro> }}have been updated to{{ dbt.<macro> }}for the below macros:any_valuebool_orcast_bool_to_textconcatdate_truncdateadddatediffescape_single_quotesexcepthashintersectlast_daylengthlistaggpositionreplacerightsafe_castsplit_partstring_literaltype_biginttype_floattype_inttype_numerictype_stringtype_timestamparray_appendarray_concatarray_construct
- For
current_timestampandcurrent_timestamp_in_utcmacros, the dispatch AND the macro names have been updated to the below, respectively:dbt.current_timestamp_backcompatdbt.current_timestamp_in_utc_backcompat
- Dependencies on
fivetran/fivetran_utilshave been upgraded, previously[">=0.3.0", "<0.4.0"]now[">=0.4.0", "<0.5.0"].
- Addition of the
credit_card_payment_txn(enabled/disabled using theusing_credit_card_payment_txnvariable) source as well as the accompanying staging and intermediate models. This source includes all credit card payment transactions and will be used in downstream General Ledger generation to ensure accurate reporting of all transaction types. (#61)Note: the
credit_card_payment_txnsource and models are disabled by default. In order to enable them, you will want to set theusing_credit_card_payment_txnvariable totruein your dbt_project.yml.
- @mikerenderco (#50, #47)
- @mel-restori (#54, #47)
- The
int_quickbooks__bill_payment_double_entry,int_quickbooks__credit_memo_double_entry,int_quickbooks__deposit_double_entry, andint_quickbooks__payment_double_entrymodels perform a cross join on thestg_quickbooks__accountsmodel for the respective debit/credit account. However, if this cross join includes more than one record, it will result in duplicates. An additional filter to remove sub accounts has been added to ensure the output of the models do not have duplicates. (#49)
- A GitHub workflow has been added to ensure the dbt docs are regenerated before each merge to the
mainrelease branch. (#49)
- Within the
v0.5.1release, thetransaction_idfield was erroneously removed from thequickbooks__general_ledgermodel. This field has since been added back. (#46)
- Updated the
dbt-utils.surrogate_key()macro to take the argument as a single list rather than a series of strings. This is to be in line with the proper use of the macro and ensure it is not impacted when the series of string argument is deprecated. (#46)
- Created indices for
double_entry_transactionsmodels. Used row_number functions forpayment,bill_paymentandtransfermodels. (#41) - Removed transaction index on final
quickbooks__general_ledgermodel, replaced by the newer indices in the sub-ledgers. (#41) - Adjusted the
bundle_income_accountscte within theint_quickbooks__invoice_double_entrymodels to coalesce the parent and sub account id. This correctly removes any duplicate records caused from this cte in a downstream join. (#42)
- It was discovered that IDs from the source tables can sometimes be strings. The previous build of the package interpreted all IDs as integers. To ensure the package operates as intended, the package has been updated to cast all IDs to the string datatype. If you were leveraging the end models in downstream analysis, this change could break your join conditions. Be sure to be aware of any join conditions you may have downstream before upgrading your QuickBooks package. (#36)[#36]
🎉 dbt v1.0.0 Compatibility 🎉
- Adjusts the
require-dbt-versionto now be within the range [">=1.0.0", "<2.0.0"]. Additionally, the package has been updated for dbt v1.0.0 compatibility. If you are using a dbt version <1.0.0, you will need to upgrade in order to leverage the latest version of the package.- For help upgrading your package, I recommend reviewing this GitHub repo's Release Notes on what changes have been implemented since your last upgrade.
- For help upgrading your dbt project to dbt v1.0.0, I recommend reviewing dbt-labs upgrading to 1.0.0 docs for more details on what changes must be made.
- Upgrades the package dependency to refer to the latest
dbt_quickbooks_source. Additionally, the latestdbt_quickbooks_sourcepackage has a dependency on the latestdbt_fivetran_utils. Further, the latestdbt_fivetran_utilspackage also has a dependency ondbt_utils[">=0.8.0", "<0.9.0"].- Please note, if you are installing a version of
dbt_utilsin yourpackages.ymlthat is not in the range above then you will encounter a package dependency error.
- Please note, if you are installing a version of
Refer to the relevant release notes on the Github repository for specific details for the previous releases. Thank you!