v0.15.0 dbt_netsuite

For Netsuite2, PR #144 includes the following updates:

Breaking Changes (Full refresh required after upgrading)

• Corrected account_number field logic for the netsuite2__balance_sheet model to match the native Balance Sheet report within Netsuite:
  • Income statement accounts should use the account number of the system-generated retained earnings account.
  • Cumulative Translation Adjustment (CTA) accounts should use the account number of the system-generated CTA account.
  • We modified the logic to ensure the account number is the retained earnings number for income statement accounts in the balance sheet, and CTA rather than null.
  • Since this will change the account_number, a --full-refresh after upgrading will be required.

New Fields

• Added commonly used fields to each end model. They are listed in the below table.
• Also added foreign keys to each end model to make it easier for customers to join back to source tables for better insights.

Models	New Fields
netsuite2__transaction_details	New fields: is_reversal, reversal_transaction_id, reversal_date, is_reversal_defer, is_eliminate, exchange_rate, department_full_name, subsidiary_full_name, subsidiary_currency_symbol, transaction_line_amount, account_display_name New keys: customer_id, vendor_id, class_id, location_id, department_id, currency_id, parent_account_id, vendor_category_id (if netsuite2__using_vendor_categories is enabled)
netsuite2__balance_sheet	New fields: account_display_name, subsidiary_full_name, is_account_intercompany, is_account_leftside
netsuite2__income_statement	New fields: account_display_name New keys: class_id, location_id, department_id

IMPORTANT: All of the affected models have pass-through functionality. If you have already been using passthrough column variables to include the newly added fields (without aliases), you MUST remove the fields from your passthrough variable configuration in order to avoid duplicate column errors.

Feature Updates

• You can now leverage passthrough columns in netsuite2__transaction_details to bring in additional fields from the locations and subsidiaries source tables.
• To add additional columns to this model, do so by adding our pass-through column variables locations_pass_through_columns and subsidiaries_pass_through_columns to your dbt_project.yml file:

vars:
    locations_pass_through_columns: 
        - name: "location_custom_field"
    subsidiaries_pass_through_columns: 
        - name: "sub_field"
          alias: "subsidiary_field"

• For more details on how to passthrough columns, please consult our README section.

Under the Hood

• Additional consistency tests added for each Netsuite2 end model in order to be used during integration test validations.
• Updated yml documentation with new fields.

Contributors

• @jmongerlyra (PR #136)
• @fastbarreto (PR #124)

Full Changelog: v0.14.0...v0.15.0

v0.14.0 dbt_netsuite

For Netsuite2, PR #138 and PR #132 include the following updates:

Breaking Changes (Full refresh required after upgrading)

• Partitioned models have had the partition_by logic adjusted to include a granularity of a month. This change should only impact BigQuery warehouses and was applied to avoid the common too many partitions error users have experienced due to over-partitioning by day. Therefore, adjusting the partition to a monthly granularity will increase the partition windows and allow for more performant querying.
• This change was applied to the following models:
  • int_netsuite2__tran_with_converted_amounts
  • netsuite2__balance_sheet
  • netsuite2__income_statement
  • netsuite2__transaction_details

Upstream Netsuite Source Breaking Changes (Full refresh required after upgrading)

• Casted specific timestamp fields across all staging models as dates where the Netsuite UI does not perform timezone conversion. Keeping these fields as type timestamp causes issues in reporting tools that perform automatic timezone conversion.
• Adds additional commonly used fields within the stg_netsuite2__* models.

IMPORTANT: Nearly all of the affected models have pass-through functionality. If you have already been using passthrough column variables to include the newly added fields (without aliases), you MUST remove the fields from your passthrough variable configuration in order to avoid duplicate column errors.

• Please refer to the v0.11.0 dbt_netsuite_source release for more details regarding the upstream changes to view the fields that were added and impacted.

Bug Fixes

• Updates logic in netsuite2__transaction_details to select the appropriate customer and vendor values based on the whether the transaction type is a customer invoice or credit, or a vendor bill or credit.
  • Customer fields impacted: company_name, customer_city, customer_state, customer_zipcode, customer_country, customer_date_first_order, customer_external_id.
  • Vendor fields impacted: vendor_category_name, vendor_name, vendor_create_date.

Feature Updates

• New fields customer_alt_name and vendor_alt_name were introduced into netsuite2__transaction_details, after being added into the stg_netsuite2__customers and stg_netsuite2__vendors models in the most recent release of dbt_netsuite_source.
• We added the employee model in the v0.11.0 release of dbt_netsuite_source, which will materialize stg_netsuite2__employees from the source package by default.
  • Since this model is only used by a subset of customers, we've introduced the variable netsuite2__using_employees to allow users who don't utilize the employee table in Netsuite2 the ability to disable that functionality within your dbt_project.yml. This value is set to true by default. Instructions are available in the README for how to disable this variable.

Under the Hood

• Consistency tests added for each Netsuite2 end model in order to be used during integration test validations.

Contributors

• @jmongerlyra (PR #131)

Full Changelog: v0.13.0...v0.14.0

v0.13.0 dbt_netsuite

For Netsuite2, PR #116 includes the following updates:

🚨 Breaking Changes 🚨

⚠️ Since the following changes are breaking, a --full-refresh after upgrading will be required.

• Performance improvements:

  • Snowflake, Postgres, and Redshift destinations:
    • Added an incremental strategy for the following models:
      • int_netsuite2__tran_with_converted_amounts
      • netsuite2__balance_sheet
      • netsuite2__income_statement
      • netsuite2__transaction_details
  • Bigquery and Databricks destinations:
    • Due to the variation in pricing and runtime priorities for customers, by default we chose to materialize these models as tables instead of incremental materialization for Bigquery and Databricks. For more information on this decision, see the Incremental Strategy section of the DECISIONLOG.
    • To enable incremental materialization for these destinations, see the Incremental Materialization section of the README for instructions.

• To reduce storage, updated the default materialization of the upstream staging models to views. (See the dbt_netsuite_source CHANGELOG for more details.)

🎉 Features

• Added a default 3-day look-back to incremental models to accommodate late arriving records, based on the _fivetran_synced_date of transaction records. The number of days can be changed by setting the var lookback_window in your dbt_project.yml. See the Lookback Window section of the README for more details.
• Added macro netsuite_lookback to streamline the lookback calculation.

Under the Hood:

• Added integration testing pipeline for Databricks SQL Warehouse.
• Included auto-releaser GitHub Actions workflow to automate future releases.

For Netsuite2, PR #114 includes the following updates:

Features

• Added the following columns to model netsuite2__transaction_details:

  • department_id
  • entity_id
  • is_closed
  • is_main_line
  • is_tax_line
  • item_id
  • transaction_number

• ❗Note: If you have already added any of these fields as passthrough columns to the transactions_pass_through_columns, transaction_lines_pass_through_columns, accounts_pass_through_columns, or departments_pass_through_columns vars, you will need to remove or alias these fields from the var to avoid duplicate column errors.

• Removed the unnecessary reference to entities in the netsuit2__transaction_details model.

📝 Documentation Update 📝

• Updated DECISIONLOG with our reasoning for why we don't bring in future-facing transactions and leave the converted_amount in transaction details empty. (#115)

Contributors:

• @FrankTub (#114)

Full Changelog: v0.12.0...v0.13.0

v0.12.0 dbt_netsuite

🎁 Official release for Netsuite2! 🎁

PR #98 is the official supported release of dbt_netsuite v0.12.0-b1.

📈 New Visualization Support (BigQuery & Snowflake users) 📊

• Our team has created the Netsuite Streamlit app to help you visualize the end reports created in this package! See instructions here on how to fork our Streamlit repo and configure your own reports.

PR #95 (built upon #90) introduces the following updates.

🚨 Breaking Changes 🚨

• Multi-book functionality is now disabled by default. To enable it, set the variable netsuite2__multibook_accounting_enabled to true in your dbt_project.yml.
  • ❗Note: The default behavior was updated due to addition of accounting_book fields. Depending on your Netsuite setup, adding this field can significantly increase the row count of the end models.
  • See additional details in the multi-book section below.

🎉 Features 🎉

Model updates

• For more accurate categorization of accounts, accounts having the following special_account_type_id are now categorized as:

special_account_type_id	account_type_name	account_type_id
retearnings	Retained Earnings	retained_earnings
cta-e	Cumulative Translation Adjustment	cumulative_translation_adjustment
cumultransadj	Cumulative Translation Adjustment	cumulative_translation_adjustment

• The below fields have been added for all configurations.
  • If you are leveraging a *_pass_through_columns variable to include accounting_period_id or subsidiary_id, you may need to remove them to avoid a duplicate column error.

model	new cols
netsuite2__transaction_details	accounting_period_id subsidiary_id transaction_details_id
netsuite2__income_statement	income_statement_id
netsuite2__balance_sheet	balance_sheet_id subsidiary_name subsidiary_id

• balance_sheet_id, income_statement_id, and transaction_details_id are surrogate keys created for each end model. These keys are now tested for uniqueness and replaces the previous combination-of-columns tests for these models.

• For detailed descriptions on the added columns, refer to our dbt docs for this package.

Multi-book

• Expanded accounting_book information that is included. As mentioned above, this feature is now disabled by default. To enable it, set the below variable to true in your dbt_project.yml.
  • ❗Notes:
    • If you choose to enable this feature, this will add rows for transactions for your non-primary accounting_book_ids, and any of your downstream use cases may need to be adjusted.
    • The surrogate keys mentioned above are dynamically generated depending on your enabled/disabled features, so adding these rows should not cause test failures.
    • If you are leveraging a *_pass_through_columns variable to include the below columns, you may need to remove them to avoid a duplicate column error.

vars:
    netsuite2__multibook_accounting_enabled: true # False by default.

• The resulting fields added by enabling this feature are:

model	new cols
netsuite2__transaction_details	accounting_book_id accounting_book_name
netsuite2__income_statement	accounting_book_id accounting_book_name
netsuite2__balance_sheet	accounting_book_id accounting_book_name

To_subsidiary

• Added the option to include to_subsidiary information in all end models. This feature is disabled by default, so to enable it, set the below variable to true in your dbt_project.yml. You will also need to be using exchange rates, which is enabled by default.
  • ❗Notes:
    • If you choose to enable this feature, this will add rows for transactions where to_subsidiary is not a top-level subsidiary. Your downstream use cases may need to be adjusted.
    • The surrogate keys mentioned above are dynamically generated depending on your enabled/disabled features, so adding these rows should not cause test failures.
    • If you are leveraging a *_pass_through_columns variable to include the below columns, you may need to remove them to avoid a duplicate column error.

vars:
    netsuite2__using_to_subsidiary: true # False by default.

• The resulting fields added by enabling this feature are:

model	new cols
netsuite2__transaction_details	to_subsidiary_id to_subsidiary_name to_subsidiary_currency_symbol
netsuite2__income_statement	to_subsidiary_id to_subsidiary_name to_subsidiary_currency_symbol
netsuite2__balance_sheet	to_subsidiary_id to_subsidiary_name to_subsidiary_currency_symbol

🚘 Under the hood 🚘

• Removed previously deprecated, empty model int_netsuite2__consolidated_exchange_rates.

Contributors:

• @jmongerlyra (#90)
• @rwang-lyra (#90)

Full Changelog: v0.11.0...v0.12.0

v0.12.0-b1 dbt_netsuite

📈 New Visualization Support (BigQuery & Snowflake users) 📊

• Our team has created the Netsuite Streamlit app to help you visualize the end reports created in this package! See instructions here on how to fork our Streamlit repo and configure your own reports.

Beta Release Notes for Netsuite2

PR #95 (built upon #90) introduces the following updates. These changes are released in beta format to encourage community feedback and insights before the final release.

🚨 Breaking Changes 🚨

• Multi-book functionality is now disabled by default. To enable it, set the variable netsuite2__multibook_accounting_enabled to true in your dbt_project.yml.
  • ❗Note: The default behavior was updated due to addition of accounting_book fields. Depending on your Netsuite setup, adding this field can significantly increase the row count of the end models.
  • See additional details in the multi-book section below.

🎉 Features 🎉

Model updates

• For more accurate categorization of accounts, accounts having the following special_account_type_id are now categorized as:

special_account_type_id	account_type_name	account_type_id
retearnings	Retained Earnings	retained_earnings
cta-e	Cumulative Translation Adjustment	cumulative_translation_adjustment
cumultransadj	Cumulative Translation Adjustment	cumulative_translation_adjustment

• The below fields have been added for all configurations.
  • If you are leveraging a *_pass_through_columns variable to include accounting_period_id or subsidiary_id, you may need to remove them to avoid a duplicate column error.

model	new cols
netsuite2__transaction_details	accounting_period_id subsidiary_id transaction_details_id
netsuite2__income_statement	income_statement_id
netsuite2__balance_sheet	balance_sheet_id

• balance_sheet_id, income_statement_id, and transaction_details_id are surrogate keys created for each end model. These keys are now tested for uniqueness and replaces the previous combination-of-columns tests for these models.

• For detailed descriptions on the added columns, refer to our dbt docs for this package.

multi-book

• Expanded accounting_book information that is included. As mentioned above, this feature is now disabled by default. To enable it, set the below variable to true in your dbt_project.yml.
  • ❗Notes:
    • If you choose to enable this feature, this will add rows for transactions for your non-primary accounting_book_ids, and any of your downstream use cases may need to be adjusted.
    • The surrogate keys mentioned above are dynamically generated depending on your enabled/disabled features, so adding these rows should not cause test failures.
    • If you are leveraging a *_pass_through_columns variable to include the below columns, you may need to remove them to avoid a duplicate column error.

vars:
    netsuite2__multibook_accounting_enabled: true # False by default.

• The resulting fields added by enabling this feature are:

model	new cols
netsuite2__transaction_details	accounting_book_id accounting_book_name
netsuite2__income_statement	accounting_book_id accounting_book_name
netsuite2__balance_sheet	accounting_book_id accounting_book_name

to_subsidiary

• Added the option to include to_subsidiary information in all end models. This feature is disabled by default, so to enable it, set the below variable to true in your dbt_project.yml. You will also need to be using exchange rates, which is enabled by default.
  • ❗Notes:
    • If you choose to enable this feature, this will add rows for transactions where to_subsidiary is not a top-level subsidiary. Your downstream use cases may need to be adjusted.
    • The surrogate keys mentioned above are dynamically generated depending on your enabled/disabled features, so adding these rows should not cause test failures.
    • If you are leveraging a *_pass_through_columns variable to include the below columns, you may need to remove them to avoid a duplicate column error.

vars:
    netsuite2__using_to_subsidiary: true # False by default.

• The resulting fields added by enabling this feature are:

model	new cols
netsuite2__transaction_details	to_subsidiary_id to_subsidiary_name to_subsidiary_currency_symbol
netsuite2__income_statement	to_subsidiary_id to_subsidiary_name to_subsidiary_currency_symbol
netsuite2__balance_sheet	to_subsidiary_id to_subsidiary_name to_subsidiary_currency_symbol subsidiary_name

🚘 Under the hood 🚘

• Removed previously deprecated, empty model int_netsuite2__consolidated_exchange_rates.

Contributors:

• @jmongerlyra (#90)
• @rwang-lyra (#90)

Full Changelog: v0.11.0...v0.12.0-b1

v0.11.0 dbt_netsuite

PR #92 includes the following updates:

🚨 Breaking Changes 🚨:

• This release includes a breaking change in the upstream dbt_netsuite_source dependency. Please refer to the respective dbt_netsuite_source v0.8.0 release notes for more information.

🐛 Bug Fixes 🐛:

• Adjusted our translation rate logic to calculate converted_amount in netsuite__balance_sheet and netsuite2__balance_sheet.
  • The logic is adjusted so we examine the general_rate_type rather than account_category, as is intended by Netsuite definitions.
  • Historical and average rates now convert amounts into the converted_amount_using_transaction_accounting_period. Otherwise, it looks at converted_amount_using_reporting_month.
  • The is_leftside logic is added to make sure debit values are properly assigned as negative converted values if false and positive if true.
• Modified the Cumulative Translation Adjustment calculation within the netsuite2__balance_sheet model to be built upon referencing that the general_rate_type is either historical or average as opposed to checking that the account_category is equity.
  • This update more accurately reflects the behavior of how the Cumulative Translation Adjustment should be calculated. The equity check was not as robust and had an opportunity to generate an incorrect value.

Contributors:

• @jmongerlyra (#75)
• @rwang-lyra (#75)

Full Changelog: v0.10.0...v0.11.0

v0.10.0 dbt_netsuite

PR #84 includes the following updates:

🚨 Breaking Changes 🚨

• For Netsuite2: updated the following models to reference account_type_id instead of type_name:
  • int_netsuite2__tran_with_converted_amounts
  • netsuite2__balance_sheet
  • netsuite2__income_statement
  • netsuite2__transaction_details
• The above change was implemented for Netsuite2 because type_name was previously utilized to categorize records, which was causing issues for users that customized the type_name values. Utilizing the unique identifier account_type_id instead produces more accurate results in the final models. Below is a list of the account type name and type id pairings used (list also found here):

Type Name	Type ID
Accounts Receivable	AcctRec
Accounts Payable	AcctPay
Bank	Bank
Cost of Goods Sold	COGS
Credit Card	CredCard
Deferred Expense	DeferExpense
Deferred Revenue	DeferRevenue
Equity	Equity
Expense	Expense
Fixed Asset	FixedAsset
Income	Income
Long Term Liability	LongTermLiab
Non Posting	NonPosting
Other Asset	OthAsset
Other Current Asset	OthCurrAsset
Other Current Liability	OthCurrLiab
Other Expense	OthExpense
Other Income	OthIncome
Statistical	Stat
Unbilled Receivable	UnbilledRec

• We also added the following account_type_id values for use in model netsuite2__balance_sheet and its downstream models:

account_type_name	account_type_id
Net Income	net_income
Retained Earnings	retained_earnings

Under the Hood

• Removed accepted_values test from column account_type_names in model netsuite2__transaction_details since logic is now based on account_type_id instead, and type names can be changed by the user.
• Updated documents with descriptions for account_type_id

Full Changelog: v0.9.0...v0.10.0

v0.9.0 dbt_netsuite

PR #74 includes the following updates:

🚨 Breaking Changes 🚨

• Removed the int_netsuite2__consolidated_exchange_rates model
  • Originally the accounting_book_id field was brought into the int_netsuite2__acctxperiod_exchange_rate_map model via int_netsuite2__consolidated_exchange_rates, but this was resulting in duplicate records downstream in the netsuite2__transaction_details model due to the way it was being joined. Now we have brought in accounting_book_id (accountingbook) via the stg_netsuite2__consolidated_exchange_rates model, so we do not have a need for int_netsuite2__consolidated_exchange_rates

Test Updates

• Added account_id to the unique combination test for netsuite2__balance_sheet

Full Changelog: v0.8.1...v0.9.0

v0.8.1 dbt_netsuite

PR #73 applies the following changes:

🎉 Feature Updates 🎉

• Introduces variable netsuite2__using_exchange_rate to allow users who don't utilize exchange rates in Netsuite2 the ability to disable that functionality, and return only the unconverted amount as the final converted amount.
• This variable will also disable upstream models utilizing exchange rates, since they only flow into the intermediate model that converts amounts into their default subsidiary currency.
• IMPORTANT: The netsuite2__using_exchange_rate variable has also been implemented in the dbt_netsuite_source package, so be sure to set it globally by inserting this code into your dbt_project.yml:

vars:
  netsuite2__using_exchange_rate: false

• Updated documentation in netsuite2.yml to provide context on how disabling exchange rates impacts specific models.

🔧Under the Hood 🔩

• Incorporated the new fivetran_utils.drop_schemas_automation macro into the end of each Buildkite integration test job.
• Updated the pull request templates.

Full Changelog: v0.8.0...v0.8.1

v0.8.0 dbt_netsuite

PR #66 applies the following changes:

🚨 Breaking Changes 🚨 (Netsuite.com Endpoint Only)

• Adds transaction_id and transaction_line_id to the Netsuite (1) income statement and balance sheet models. These fields were already present in the Netsuite2 versions of these models. These columns are included in newly added Primary Key tests on the Netsuite (1) income statement and balance sheet models.

Note: Ensure that neither of these columns are included in your balance_sheet_transaction_detail_columns or income_statement_transaction_detail_columns variables.

Under the Hood

• Aligns join types in Netsuite end models with the joins in Netsuite2 end models.
• Adds new account type <> account category mappings in the int_netsuite__transactions_with_converted_amounts/int_netsuite2__tran_with_converted_amounts model.
  • Prepaid Expense account types are treated as Deferred Expense accounts.
  • Non Posting and Statistical account types will be placed in a new Other category.
• Adds an accepted_values test on the transaction detail end models that will raise a warning if unexpected account types are encountered.
• Adds a DECISIONLOG.
• Updates README to include the netsuite2__using_jobs variable.
• Adds uniqueness and not-null tests to the Netsuite (1) income statement and balance sheet models. These tests were already present in the Netsuite2 models.