dbt NetSuite

https://fivetran.github.io/dbt_netsuite/
Admin
dbt NetSuite is a data transformation package that provides pre-built dbt models and macros for...
Tokens:32,632
Snippets:106
Trust Score:8.3
Update:9 months ago
Show doc for...
Tokens:
Raw
### Create Netsuite Accounts Staging Model

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This dbt model stages Netsuite account data. It uses a Jinja template to enable the model based on variable configurations and applies Fivetran utilities for schema mapping and source relation handling. It also includes logic for pass-through columns.

```sql
SELECT
    source_relation,
    _fivetran_synced,
    id AS account_id,
    externalid AS account_external_id,
    parent AS parent_id,
    acctnumber AS account_number,
    accttype AS account_type_id,
    sspecacct AS special_account_type_id,
    fullname AS name,
    accountsearchdisplaynamecopy AS display_name,
    description AS account_description,
    deferralacct AS deferral_account_id,
    cashflowrate AS cash_flow_rate_type,
    generalrate AS general_rate_type,
    currency AS currency_id,
    class AS class_id,
    department AS department_id,
    location AS location_id,
    CASE WHEN includechildren = 'T' THEN true ELSE false END AS is_including_child_subs,
    CASE WHEN isinactive = 'T' THEN true ELSE false END AS is_inactive,
    CASE WHEN issummary = 'T' THEN true ELSE false END AS is_summary,
    CASE WHEN eliminate = 'T' THEN true ELSE false END AS is_eliminate,
    _fivetran_deleted
    -- The below macro adds the fields defined within your accounts_pass_through_columns variable into the staging model
    {{ netsuite.fill_pass_through_columns(var('accounts_pass_through_columns', [])) }}
FROM
    fields
```

--------------------------------

### Create Netsuite Accounts Staging Model

Source: https://fivetran.github.io/dbt_netsuite/index

This dbt model creates a staging table for Netsuite accounts. It uses `fivetran_utils.fill_staging_columns` to ensure all necessary columns are present and applies source relation mapping. It also includes a macro to add custom pass-through columns.

```sql
SELECT {{ fivetran_utils.fill_staging_columns( source_columns=adapter.get_columns_in_relation(ref('stg_netsuite2__accounts_tmp')), staging_columns=get_netsuite2_accounts_columns() ) }} {{ netsuite.apply_source_relation() }} FROM base
```

--------------------------------

### Generate Surrogate Key for Financial Reporting (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index

This SQL snippet demonstrates the generation of a surrogate key, crucial for uniquely identifying financial records. It dynamically includes fields like 'to_subsidiary_id' and 'accounting_book_id' based on configuration flags, enabling flexible data modeling for reporting.

```sql
with balance_sheet as (
  select *, accounts.account_id, accounts.source_relation, transactions_with_converted_amounts.account_id, transactions_with_converted_amounts.source_relation, accounts.is_balancesheet, accounts.is_income_statement, transactions_with_converted_amounts.reporting_accounting_period_id, transactions_with_converted_amounts.transaction_line_id, transactions_with_converted_amounts.transaction_id, transactions_with_converted_amounts.accounting_period_id, transactions_with_converted_amounts.account_name, transactions_with_converted_amounts.accounting_book_id, transactions_with_converted_amounts.to_subsidiary_id, transactions_with_converted_amounts.subsidiary_id, subsidiaries.fiscal_calendar_id, to_subsidiaries.fiscal_calendar_id
  from transactions_with_converted_amounts
  left join accounts on accounts.account_id = transactions_with_converted_amounts.account_id and accounts.source_relation = transactions_with_converted_amounts.source_relation
  left join subsidiaries on subsidiaries.subsidiary_id = transactions_with_converted_amounts.subsidiary_id and subsidiaries.source_relation = transactions_with_converted_amounts.source_relation
  {% if using_to_subsidiary_and_exchange_rate %}
    left join subsidiaries as to_subsidiaries on to_subsidiaries.subsidiary_id = coalesce(transactions_with_converted_amounts.to_subsidiary_id, subsidiaries.subsidiary_id) and to_subsidiaries.source_relation = transactions_with_converted_amounts.source_relation
  {% endif %}
  left join accounting_periods as reporting_accounting_periods on reporting_accounting_periods.accounting_period_id = transactions_with_converted_amounts.reporting_accounting_period_id and reporting_accounting_periods.source_relation = transactions_with_converted_amounts.source_relation
  and reporting_accounting_periods.fiscal_calendar_id = {% if using_to_subsidiary_and_exchange_rate %}to_subsidiaries{% else %}subsidiaries{% endif %}.fiscal_calendar_id
  left join currencies subsidiaries_currencies on subsidiaries_currencies.currency_id = subsidiaries.currency_id and subsidiaries_currencies.source_relation = subsidiaries.source_relation
  where (accounts.is_balancesheet or transactions_with_converted_amounts.is_income_statement) and transactions_with_converted_amounts.reporting_accounting_period_id is not null
),

surrogate_key as (
  {% set surrogate_key_fields = ['source_relation', 'transaction_line_id', 'transaction_id', 'accounting_period_id', 'account_name', 'account_id'] %}
  {% do surrogate_key_fields.append('to_subsidiary_id') if using_to_subsidiary_and_exchange_rate %}
  {% do surrogate_key_fields.append('accounting_book_id') if multibook_accounting_enabled %}
  select *, {{ dbt_utils.generate_surrogate_key(surrogate_key_fields) }} as balance_sheet_id
  from balance_sheet
)

select * from surrogate_key
```

--------------------------------

### Load Supporting Data Models

Source: https://fivetran.github.io/dbt_netsuite/index

This snippet loads necessary supporting data from other dbt models: 'int_netsuite2__accounts', 'int_netsuite2__accounting_periods', 'stg_netsuite2__subsidiaries', and 'stg_netsuite2__currencies'. These models provide account, accounting period, subsidiary, and currency information, respectively.

```sql
accounts as ( select * from {{ ref('int_netsuite2__accounts') }} ), accounting_periods as ( select * from {{ ref('int_netsuite2__accounting_periods') }} ), subsidiaries as ( select * from {{ ref('stg_netsuite2__subsidiaries') }} ), currencies as ( select * from {{ ref('stg_netsuite2__currencies') }} )
```

--------------------------------

### Configure and Process Netsuite Account Types (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL code defines a dbt model for processing Netsuite account types. It uses Jinja templating for conditional logic based on the target data warehouse (BigQuery, Snowflake, Redshift, Postgres) and applies dbt macros like ref, config, get_netsuite2_accounttype_columns, fivetran_utils.fill_staging_columns, and netsuite.apply_source_relation to prepare the data.

```sql
SELECT {{ fivetran_utils.fill_staging_columns( source_columns=adapter.get_columns_in_relation(ref('stg_netsuite2__account_types_tmp')), staging_columns=get_netsuite2_accounttype_columns() ) }} {{ netsuite.apply_source_relation() }} FROM base ), final AS ( SELECT source_relation, _fivetran_deleted, _fivetran_synced, id AS account_type_id, balancesheet = 'T' AS is_balancesheet, {%- if target.type == 'bigquery' -%} `left` {%- elif target.type == 'snowflake' -%} "LEFT" {%- elif target.type in ('redshift', 'postgres') -%} "left" {%- else -%} left {%- endif -%} = 'T' AS is_leftside, longname AS type_name FROM fields ) SELECT * FROM final
```

--------------------------------

### Load Transactions with Converted Amounts (Incremental)

Source: https://fivetran.github.io/dbt_netsuite/index

This section loads transaction data, applying currency conversions. It uses the 'int_netsuite2__tran_with_converted_amounts' ref and includes a condition for incremental loads, fetching data from the maximum '_fivetran_synced_date' up to the specified 'lookback_window'.

```sql
with transactions_with_converted_amounts as ( select * from {{ ref('int_netsuite2__tran_with_converted_amounts') }} {% if is_incremental() %} where _fivetran_synced_date >= {{ netsuite.netsuite_lookback(from_date='max(_fivetran_synced_date)', datepart='day', interval=lookback_window) }} {% endif %} )
```

--------------------------------

### Construct Transaction Details Model (SQL/Jinja)

Source: https://fivetran.github.io/dbt_netsuite/index

This is the main CTE 'transaction_details' which constructs the final transaction details table. It conditionally selects columns based on Jinja variables like 'multibook_accounting_enabled' and 'using_to_subsidiary'. It joins various CTEs to enrich transaction line data with information from transactions, accounts, subsidiaries, customers, items, locations, nexuses, and vendors.

```sql
transaction_details as (
  select 
    {% if multibook_accounting_enabled %}
      transaction_lines.accounting_book_id,
      transaction_lines.accounting_book_name,
    {% endif %}
    {% if using_to_subsidiary and using_exchange_rate %}
      transactions_with_converted_amounts.to_subsidiary_id,
      transactions_with_converted_amounts.to_subsidiary_name,
      transactions_with_converted_amounts.to_subsidiary_currency_symbol,
    {% endif %}
      transaction_lines.source_relation,
      transaction_lines.transaction_line_id,
      transaction_lines.memo as transaction_memo,
      not transaction_lines.is_posting as is_transaction_non_posting,
      transaction_lines.is_main_line,
      transaction_lines.is_tax_line,
      transaction_lines.is_closed,
      transactions.transaction_id,
      transactions.status as transaction_status,
      transactions.transaction_date,
      transactions.due_date_at as transaction_due_date,
      transactions.transaction_type as transaction_type,
      transactions.nexus_id,
      nexuses.country as nexus_country,
      nexuses.state as nexus_state,
      nexuses.tax_agency_id,
      vendors__nexuses.alt_name as tax_agency_alt_name,
      transactions.is_nexus_override,
      transactions.is_tax_details_override,
      transactions.tax_point_date,
      transactions.is_tax_point_date_override,
      transaction_lines.transaction_line_fivetran_synced_date,
      transactions.transaction_number,
      coalesce(transaction_lines.entity_i
```

--------------------------------

### Create Netsuite Accounting Books Staging Table (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL code defines a dbt model for staging Netsuite accounting books. It utilizes the netsuite_union_connections macro to combine data from multiple sources and applies configurations for multibook accounting.

```sql
{{ netsuite.netsuite_union_connections( connection_dictionary=var('netsuite2_sources'), single_source_name='netsuite2', single_table_name='accounting_book', default_identifier='accountingbook' ) }}
```

--------------------------------

### Balance Sheet Model Logic in dbt (SQL/Jinja)

Source: https://fivetran.github.io/dbt_netsuite/index

This SQL query, enhanced with Jinja, constructs the balance sheet. It joins transaction data with account, accounting period, and subsidiary information. Conditional logic handles different account types, balance sheet classifications, and currency conversions using specified rate types. It also includes optional transaction detail columns if configured.

```sql
with transactions_with_converted_amounts as (
 select * from {{ref('int_netsuite__transactions_with_converted_amounts')}}
 ),
 --Below is only used if balance sheet transaction detail columns are specified dbt_project.yml file.
 {% if var('balance_sheet_transaction_detail_columns') != []%}
 transaction_details as (
 select * from {{ ref('netsuite__transaction_details') }}
 ),
 {% endif %}
 accounts as (
 select * from {{ ref('stg_netsuite__accounts') }}
 ),
 accounting_periods as (
 select * from {{ ref('stg_netsuite__accounting_periods') }}
 ),
 subsidiaries as (
 select * from {{ ref('stg_netsuite__subsidiaries') }}
 ),
 balance_sheet as (
 select transactions_with_converted_amounts.transaction_id,
 transactions_with_converted_amounts.transaction_line_id,
 reporting_accounting_periods.accounting_period_id as accounting_period_id,
 reporting_accounting_periods.ending_at as accounting_period_ending,
 reporting_accounting_periods.full_name as accounting_period_full_name,
 reporting_accounting_periods.name as accounting_period_name,
 lower(reporting_accounting_periods.is_adjustment) = 'yes' as is_accounting_period_adjustment,
 lower(reporting_accounting_periods.is_closed) = 'yes' as is_accounting_period_closed,
 transactions_with_converted_amounts.account_category as account_category,
 case when (lower(accounts.is_balancesheet) = 'f' and reporting_accounting_periods.year_id = transaction_accounting_periods.year_id) then 'Net Income'
 when lower(accounts.is_balancesheet) = 'f' then 'Retained Earnings'
 else accounts.name end as account_name,
 case when (lower(accounts.is_balancesheet) = 'f' and reporting_accounting_periods.year_id = transaction_accounting_periods.year_id) then 'Net Income'
 when lower(accounts.is_balancesheet) = 'f' then 'Retained Earnings'
 else accounts.type_name end as account_type_name,
 case when lower(accounts.is_balancesheet) = 'f' then null else accounts.account_id end as account_id,
 case when lower(accounts.is_balancesheet) = 'f' then null else accounts.account_number end as account_number
 --The below script allows for accounts table pass through columns.
 {{ netsuite.persist_pass_through_columns(var('accounts_pass_through_columns', []), identifier='accounts') }},
 case when lower(accounts.is_balancesheet) = 'f' and lower(accounts.general_rate_type) in ('historical', 'average') then -converted_amount_using_transaction_accounting_period
 when lower(accounts.is_balancesheet) = 'f' then -converted_amount_using_reporting_month
 when lower(accounts.is_balancesheet) = 't' and lower(accounts.is_leftside) = 'f' and lower(accounts.general_rate_type) in ('historical', 'average') then -converted_amount_using_transaction_accounting_period
 when lower(accounts.is_balancesheet) = 't' and lower(accounts.is_leftside) = 't' and lower(accounts.general_rate_type) in ('historical', 'average') then converted_amount_using_transaction_accounting_period
 when lower(accounts.is_balancesheet) = 't' and lower(accounts.is_leftside) = 'f' then -converted_amount_using_reporting_month
 when lower(accounts.is_balancesheet) = 't' and lower(accounts.is_leftside) = 't' then converted_amount_using_reporting_month
 else 0 end as converted_amount,
 case when lower(accounts.type_name) = 'bank' then 1
 when lower(accounts.type_name) = 'accounts receivable' then 2
 when lower(accounts.type_name) = 'unbilled receivable' then 3
 when lower(accounts.type_name) = 'other current asset' then 4
 when lower(accounts.type_name) = 'fixed asset' then 5
 when lower(accounts.type_name) = 'other asset' then 6
 when lower(accounts.type_name) = 'deferred expense' then 7
 when lower(accounts.type_name) = 'accounts payable' then 8
 when lower(accounts.type_name) = 'credit card' then 9
 when lower(accounts.type_name) = 'other current liability' then 10
 when lower(accounts.type_name) = 'long term liability' then 11
 when lower(accounts.type_name) = 'deferred revenue' then 12
 when lower(accounts.type_name) = 'equity' then 13
 when (lower(accounts.is_balancesheet) = 'f' and reporting_accounting_periods.year_id = transaction_accounting_periods.year_id) then 15
 when lower(accounts.is_balancesheet) = 'f' then 14
 else null end as balance_sheet_sort_helper
 --Below is only used if balance sheet transaction detail columns are specified dbt_project.yml file.
 {% if var('balance_sheet_transaction_detail_columns') %}
 , transaction_details
```

--------------------------------

### Union Connections for Netsuite Accounts

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This Jinja code snippet utilizes the 'netsuite_union_connections' macro to union data from multiple Netsuite connections for the 'account' table. It allows for defining connections via a dictionary and specifying a default identifier for single-source scenarios.

```jinja
{{ config(enabled=var('netsuite_data_model', 'netsuite') == var('netsuite_data_model_override','netsuite2')) }}

{{ netsuite.netsuite_union_connections( connection_dictionary=var('netsuite2_sources'), single_source_name='netsuite2', single_table_name='account', default_identifier='account' ) }}
```

--------------------------------

### Conditional Model Configuration (Jinja)

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This Jinja code snippet defines configuration for a dbt model, specifically controlling its enabled state based on various variables. It checks for the data model override and the inclusion of customer or vendor subsidiary relationships.

```jinja
{% set using_customer_subsidiary_relationships = var('netsuite2__using_customer_subsidiary_relationships', true) %}
{% set using_vendor_subsidiary_relationships = var('netsuite2__using_vendor_subsidiary_relationships', true) %}
{{ config( enabled=( var('netsuite_data_model', 'netsuite') == var('netsuite_data_model_override','netsuite2') and (using_customer_subsidiary_relationships or using_vendor_subsidiary_relationships) ) ) }}
```

--------------------------------

### Create Netsuite Accounting Period Fiscal Calendars Staging Model

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This dbt model stages Netsuite accounting period fiscal calendar data. It is configured using Jinja for conditional execution and employs a Netsuite macro to union data from multiple Netuite connections, specifying source and table names.

```sql
{{ config(enabled=var('netsuite_data_model', 'netsuite') == var('netsuite_data_model_override','netsuite2')) }}
{{ netsuite.netsuite_union_connections( connection_dictionary=var('netsuite2_sources'), single_source_name='netsuite2', single_table_name='accounting_period_fiscal_calendars', default_identifier='accountingperiodfiscalcalendars' ) }}
```

--------------------------------

### Create Netsuite Accounting Periods Model

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This dbt model transforms Netsuite accounting period data into a structured format. It uses Jinja for configuration and Fivetran utilities for schema filling and source relation application. It also includes logic for pass-through columns and filters out deleted records.

```sql
SELECT
    source_relation,
    _fivetran_synced,
    id AS accounting_period_id,
    parent AS parent_id,
    periodname AS name,
    CAST(startdate AS DATE) AS starting_at,
    CAST(enddate AS DATE) AS ending_at,
    closedondate AS closed_at,
    CASE WHEN isquarter = 'T' THEN true ELSE false END AS is_quarter,
    CASE WHEN isyear = 'T' THEN true ELSE false END AS is_year,
    CASE WHEN isadjust = 'T' THEN true ELSE false END AS is_adjustment,
    CASE WHEN isposting = 'T' THEN true ELSE false END AS is_posting,
    CASE WHEN closed = 'T' THEN true ELSE false END AS is_closed,
    CASE WHEN alllocked = 'T' THEN true ELSE false END AS is_all_locked,
    CASE WHEN arlocked = 'T' THEN true ELSE false END AS is_ar_locked,
    CASE WHEN aplocked = 'T' THEN true ELSE false END AS is_ap_locked
    -- The below macro adds the fields defined within your accounting_periods_pass_through_columns variable into the staging model
    {{ netsuite.fill_pass_through_columns(var('accounting_periods_pass_through_columns', [])) }}
FROM
    fields
WHERE
    NOT COALESCE(_fivetran_deleted, false)
```

--------------------------------

### Create Netsuite Accounting Periods Staging Model

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This dbt model stages Netsuite accounting period data. It employs a Jinja template for conditional model execution and utilizes a Netsuite macro to union data from multiple Netuite connections. It handles temporary table transformations and applies schema mapping.

```sql
{{ config(enabled=var('netsuite_data_model', 'netsuite') == var('netsuite_data_model_override','netsuite2')) }}
{{ netsuite.netsuite_union_connections( connection_dictionary=var('netsuite2_sources'), single_source_name='netsuite2', single_table_name='accounting_period', default_identifier='accountingperiod' ) }}
```

--------------------------------

### Conditionally Load Vendor Categories CTE (SQL/Jinja)

Source: https://fivetran.github.io/dbt_netsuite/index

This Jinja-templated SQL snippet conditionally defines the 'vendor_categories' CTE. It is only included if the 'using_vendor_categories' variable, which is set via dbt vars, is true. This allows for dynamic inclusion of vendor category data based on configuration.

```sql
{% if using_vendor_categories %}
vendor_categories as (
  select * from {{ ref('stg_netsuite2__vendor_categories') }}
),
{% endif %}
```

--------------------------------

### Configure Netsuite2 Transaction Details Model Variables (Jinja)

Source: https://fivetran.github.io/dbt_netsuite/index

This snippet defines Jinja variables used to configure the netsuite2__transaction_details model. These variables control aspects like multibook accounting, subsidiary usage, exchange rate calculations, vendor categories, and pass-through columns for various Netsuite entities. They also set a lookback window for incremental data loading.

```jinja
{%- set multibook_accounting_enabled = var('netsuite2__multibook_accounting_enabled', false) -%}
{%- set using_to_subsidiary = var('netsuite2__using_to_subsidiary', false) -%}
{%- set using_exchange_rate = var('netsuite2__using_exchange_rate', true) -%}
{%- set using_vendor_categories = var('netsuite2__using_vendor_categories', true) -%}
{%- set accounts_pass_through_columns = var('accounts_pass_through_columns', []) -%}
{%- set departments_pass_through_columns = var('departments_pass_through_columns', []) -%}
{%- set locations_pass_through_columns = var('locations_pass_through_columns', []) -%}
{%- set subsidiaries_pass_through_columns = var('subsidiaries_pass_through_columns', []) -%}
{%- set transactions_pass_through_columns = var('transactions_pass_through_columns', []) -%}
{%- set transaction_lines_pass_through_columns = var('transaction_lines_pass_through_columns', []) -%}
{%- set lookback_window = var('lookback_window', 3) -%}
```

--------------------------------

### Generate Balance Sheet Transaction Detail Data (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index

This SQL query generates a detailed balance sheet view. It dynamically includes columns specified in the 'balance_sheet_transaction_detail_columns' Jinja variable and handles different conversion amounts based on account type. The query joins 'transactions_with_converted_amounts', 'accounts', 'accounting_periods', and 'transaction_details' tables.

```sql
select
    transactions_with_converted_amounts.transaction_id,
    transactions_with_converted_amounts.transaction_line_id,
    reporting_accounting_periods.accounting_period_id as accounting_period_id,
    reporting_accounting_periods.ending_at as accounting_period_ending,
    reporting_accounting_periods.full_name as accounting_period_full_name,
    reporting_accounting_periods.name as accounting_period_name,
    lower(reporting_accounting_periods.is_adjustment) = 'yes' as is_accounting_period_adjustment,
    lower(reporting_accounting_periods.is_closed) = 'yes' as is_accounting_period_closed,
    'Equity' as account_category,
    'Cumulative Translation Adjustment' as account_name,
    'Cumulative Translation Adjustment' as account_type_name,
    null as account_id,
    null as account_number,
    {% if var('accounts_pass_through_columns') %}
        {% for field in var('accounts_pass_through_columns') %}
            null as {{ field.alias if field.alias else field.name }},
        {% endfor %}
    {% endif %}
    case
        when lower(account_category) = 'equity' or is_income_statement then converted_amount_using_transaction_accounting_period
        else converted_amount_using_reporting_month
    end as converted_amount,
    16 as balance_sheet_sort_helper --Below is only used if balance sheet transaction detail columns are specified dbt_project.yml file.
    {% if var('balance_sheet_transaction_detail_columns') %}
    , transaction_details.{{ var('balance_sheet_transaction_detail_columns') | join (', transaction_details.')}}
    {% endif %}
from transactions_with_converted_amounts
--Below is only used if balance sheet transaction detail columns are specified dbt_project.yml file.
{% if var('balance_sheet_transaction_detail_columns') != []%}
left join transaction_details on transaction_details.transaction_id = transactions_with_converted_amounts.transaction_id and transaction_details.transaction_line_id = transactions_with_converted_amounts.transaction_line_id
{% endif %}
left join accounts on accounts.account_id = transactions_with_converted_amounts.account_id
left join accounting_periods as reporting_accounting_periods on reporting_accounting_periods.accounting_period_id = transactions_with_converted_amounts.reporting_accounting_period_id
where reporting_accounting_periods.fiscal_calendar_id = (select fiscal_calendar_id from subsidiaries where parent_id is null)
  and (lower(accounts.is_balancesheet) = 't' or transactions_with_converted_amounts.is_income_statement)

union all

select
    transactions_with_converted_amounts.transaction_id,
    transactions_with_converted_amounts.transaction_line_id,
    reporting_accounting_periods.accounting_period_id as accounting_period_id,
    reporting_accounting_periods.ending_at as accounting_period_ending,
    reporting_accounting_periods.full_name as accounting_period_full_name,
    reporting_accounting_periods.name as accounting_period_name,
    lower(reporting_accounting_periods.is_adjustment) = 'yes' as is_accounting_period_adjustment,
    lower(reporting_accounting_periods.is_closed) = 'yes' as is_accounting_period_closed,
    'Equity' as account_category,
    'Cumulative Translation Adjustment' as account_name,
    'Cumulative Translation Adjustment' as account_type_name,
    null as account_id,
    null as account_number,
    {% if var('accounts_pass_through_columns') %}
        {% for field in var('accounts_pass_through_columns') %}
            null as {{ field.alias if field.alias else field.name }},
        {% endfor %}
    {% endif %}
    case
        when lower(account_category) = 'equity' or is_income_statement then converted_amount_using_transaction_accounting_period
        else converted_amount_using_reporting_month
    end as converted_amount,
    16 as balance_sheet_sort_helper --Below is only used if balance sheet transaction detail columns are specified dbt_project.yml file.
    {% if var('balance_sheet_transaction_detail_columns') %}
    , transaction_details.{{ var('balance_sheet_transaction_detail_columns') | join (', transaction_details.')}}
    {% endif %}
from transactions_with_converted_amounts
--Below is only used if balance sheet transaction detail columns are specified dbt_project.yml file.
{% if var('balance_sheet_transaction_detail_columns') != []%}
left join transaction_details on transaction_details.transaction_id = transactions_with_converted_amounts.transaction_id and transaction_details.transaction_line_id = transactions_with_converted_amounts.transaction_line_id
{% endif %}
left join accounts on accounts.account_id = transactions_with_converted_amounts.account_id
left join accounting_periods as reporting_accounting_periods on reporting_accounting_periods.accounting_period_id = transactions_with_converted_amounts.reporting_accounting_period_id
left join accounting_periods as transaction_accounting_periods on transaction_accounting_periods.accounting_period_id = transactions_with_converted_amounts.transaction_accounting_period_id
where reporting_accounting_periods.fiscal_calendar_id = (select fiscal_calendar_id from subsidiaries where parent_id is null)
  and transaction_accounting_periods.fiscal_calendar_id = (select fiscal_calendar_id from subsidiaries where parent_id is null)
  and (lower(accounts.is_balancesheet) = 't' or transactions_with_converted_amounts.is_income_statement)
) select * from balance_sheet
```

--------------------------------

### Create Netsuite Accounting Periods Fiscal Calendars Staging Model (Temporary)

Source: https://fivetran.github.io/dbt_netsuite/index

This dbt model creates a temporary staging table for Netsuite accounting period fiscal calendars. It unions connections using `netsuite.netsuite_union_connections` and specifies default identifiers.

```sql
{{ netsuite.netsuite_union_connections( connection_dictionary=var('netsuite2_sources'), single_source_name='netsuite2', single_table_name='accounting_period_fiscal_calendars', default_identifier='accountingperiodfiscalcalendars' ) }}
```

--------------------------------

### Configure Netsuite2 Transaction Details Model (SQL/Jinja)

Source: https://fivetran.github.io/dbt_netsuite/index

This Jinja-templated SQL code configures the 'netsuite2__transaction_details' dbt model. It specifies the materialization strategy (table or incremental), partitioning, clustering, unique key, and incremental strategy based on the target database type. It also defines the file format for data storage.

```sql
{{ config(
  enabled=var('netsuite_data_model', 'netsuite') == var('netsuite_data_model_override','netsuite2'),
  materialized='table' if target.type in ('bigquery', 'databricks', 'spark') else 'incremental',
  partition_by = {'field': 'transaction_line_fivetran_synced_date', 'data_type': 'date', 'granularity': 'month'} if target.type not in ['spark', 'databricks'] else ['transaction_line_fivetran_synced_date'],
  cluster_by = ['transaction_id'],
  unique_key='transaction_details_id',
  incremental_strategy = 'merge' if target.type in ('bigquery', 'databricks', 'spark') else 'delete+insert',
  file_format='delta'
) }}
```

--------------------------------

### SQL: Generate Surrogate Key for Balance Sheet Entries

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL snippet defines a Common Table Expression (CTE) named `surrogate_key` that generates a unique surrogate key for balance sheet entries. It dynamically includes fields in the key generation based on configuration variables like `using_to_subsidiary_and_exchange_rate` and `multibook_accounting_enabled`. The `dbt_utils.generate_surrogate_key` macro is used for this purpose.

```sql
surrogate_key as (
{% set surrogate_key_fields = ['source_relation', 'transaction_line_id', 'transaction_id', 'accounting_period_id', 'account_name', 'account_id'] %}
{% do surrogate_key_fields.append('to_subsidiary_id') if using_to_subsidiary_and_exchange_rate %}
{% do surrogate_key_fields.append('accounting_book_id') if multibook_accounting_enabled %}
select *, {{ dbt_utils.generate_surrogate_key(surrogate_key_fields) }} as balance_sheet_id 
from balance_sheet
)
```

--------------------------------

### Staging Model for Netsuite Classes

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL code defines the staging model for Netsuite classes. It selects all columns from a temporary table and applies transformations, including filling staging columns and source relation. It also handles pass-through columns defined in the 'classes_pass_through_columns' variable.

```sql
with base as (
 select * from {{ ref('stg_netsuite2__classes_tmp') }}
), 
fields as (
 select {{ fivetran_utils.fill_staging_columns( source_columns=adapter.get_columns_in_relation(ref('stg_netsuite2__classes_tmp')), staging_columns=get_netsuite2_classes_columns() ) }} {{ netsuite.apply_source_relation() }} from base 
), 
final as (
 select
 source_relation,
 _fivetran_synced,
 id as class_id,
 externalid as class_external_id,
 name,
 fullname as full_name,
 isinactive = 'T' as is_inactive,
 _fivetran_deleted
 --The below macro adds the fields defined within your classes_pass_through_columns variable into the staging model
 {{ netsuite.fill_pass_through_columns(var('classes_pass_through_columns', [])) }}
 from fields
)

select * from final
```

--------------------------------

### Configure NetSuite Data Model

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This Jinja configuration snippet enables the NetSuite data model if the specified variable is set to 'netsuite'. It's a common pattern for conditional model execution in dbt.

```sql
{{ config(enabled=var('netsuite_data_model', 'netsuite') == 'netsuite') }}
```

--------------------------------

### Create Netsuite Account Types Staging Table (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL code defines a dbt model for staging Netsuite account types. It uses the netsuite_union_connections macro to consolidate account type data from multiple Netsuite connections, ensuring proper handling for the 'account_type' table with a default identifier.

```sql
{{ netsuite.netsuite_union_connections( connection_dictionary=var('netsuite2_sources'), single_source_name='netsuite2', single_table_name='account_type', default_identifier='accounttype' ) }}
```

--------------------------------

### Create Netsuite Accounting Period Fiscal Calendar Staging Table (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL code defines a dbt model for staging Netsuite accounting fiscal calendar periods. It uses Fivetran utilities to fill staging columns and applies source relation information. The final selection cleans and renames columns for clarity.

```sql
SELECT {{ fivetran_utils.fill_staging_columns( source_columns=adapter.get_columns_in_relation(ref('stg_netsuite2__accounting_period_fiscal_cal_tmp')), staging_columns=get_netsuite2_accountingperiodfiscalcalendars_columns() ) }} {{ netsuite.apply_source_relation() }} FROM base
```

--------------------------------

### Netsuite Pass Through Columns for Accounts

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This Jinja snippet facilitates the pass-through of custom columns from the accounts table in Netsuite. It ensures that all relevant account information is available in the dbt model.

```jinja
{{ netsuite.persist_pass_through_columns(accounts_pass_through_columns, identifier='accounts') }}
```

--------------------------------

### Create Netsuite Accounting Periods Staging Model

Source: https://fivetran.github.io/dbt_netsuite/index

This dbt model unions multiple Netsuite connections to create a consolidated staging table for accounting periods. It utilizes the `netsuite.netsuite_union_connections` macro to handle the union and define a default identifier.

```sql
{{ netsuite.netsuite_union_connections( connection_dictionary=var('netsuite2_sources'), single_source_name='netsuite2', single_table_name='accounting_period', default_identifier='accountingperiod' ) }}
```

--------------------------------

### NetSuite2 Entity Subsidiary Relationships Model (SQL/Jinja)

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL model, enhanced with Jinja, generates a unified view of entity-subsidiary relationships in NetSuite. It dynamically includes customer and/or vendor data based on configuration variables. The model joins staging tables to add detailed information about relationships, currencies, and subsidiaries. It outputs a final table containing comprehensive subsidiary relationship data.

```sql
select * from final
```

--------------------------------

### Create Netsuite Accounting Book Subsidiaries Staging Table (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL code defines a dbt model for staging Netsuite accounting book subsidiaries. It uses netsuite_union_connections to union data from multiple connections, specifically for the accounting_book_subsidiaries table, and enables multibook accounting features.

```sql
{{ netsuite.netsuite_union_connections( connection_dictionary=var('netsuite2_sources'), single_source_name='netsuite2', single_table_name='accounting_book_subsidiaries', default_identifier='accountingbooksubsidiaries' ) }}
```

--------------------------------

### SQL: Join Transaction Details with Subsidiary and Account Information

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL snippet demonstrates joining transaction details with subsidiary, account, and accounting period information. It includes conditional joins based on `using_to_subsidiary_and_exchange_rate` and `multibook_accounting_enabled` flags. The primary input is the `transactions_with_converted_amounts` CTE, and outputs include joined fields from accounts, subsidiaries, and reporting periods.

```sql
left join accounts on accounts.account_id = transactions_with_converted_amounts.account_id and accounts.source_relation = transactions_with_converted_amounts.source_relation 
left join subsidiaries on subsidiaries.subsidiary_id = transactions_with_converted_amounts.subsidiary_id and subsidiaries.source_relation = transactions_with_converted_amounts.source_relation 
{% if using_to_subsidiary_and_exchange_rate %} 
left join subsidiaries as to_subsidiaries on to_subsidiaries.subsidiary_id = coalesce(transactions_with_converted_amounts.to_subsidiary_id, subsidiaries.subsidiary_id) and to_subsidiaries.source_relation = transactions_with_converted_amounts.source_relation 
{% endif %} 
left join accounting_periods as reporting_accounting_periods on reporting_accounting_periods.accounting_period_id = transactions_with_converted_amounts.reporting_accounting_period_id and reporting_accounting_periods.source_relation = transactions_with_converted_amounts.source_relation 
and reporting_accounting_periods.fiscal_calendar_id = {{ 'to_subsidiaries' if using_to_subsidiary_and_exchange_rate else 'subsidiaries' }}.fiscal_calendar_id 
left join currencies subsidiaries_currencies on subsidiaries_currencies.currency_id = subsidiaries.currency_id and subsidiaries_currencies.source_relation = subsidiaries.source_relation 
where (accounts.is_balancesheet or transactions_with_converted_amounts.is_income_statement) and transactions_with_converted_amounts.reporting_accounting_period_id is not null
```

--------------------------------

### Create Netsuite Accounting Books Table (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL code defines a dbt model for detailing Netsuite accounting books. It transforms data from the temporary staging table, filling staging columns and applying source relation information. The final selection renames and formats columns, including boolean conversions.

```sql
{{ fivetran_utils.fill_staging_columns( source_columns=adapter.get_columns_in_relation(ref('stg_netsuite2__accounting_books_tmp')), staging_columns=get_netsuite2_accounting_books_columns() ) }} {{ netsuite.apply_source_relation() }} FROM base
```

--------------------------------

### Netsuite Pass Through Columns for Locations

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This Jinja snippet allows for passing through additional columns from the locations table in Netsuite. It extends the data available for location-based analysis.

```jinja
{{ netsuite.persist_pass_through_columns(locations_pass_through_columns, identifier='locations') }}
```

--------------------------------

### Create Netsuite Accounting Book Subsidiaries Table (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This SQL code defines a dbt model for a table containing Netsuite accounting books and their respective subsidiaries. It processes data from a temporary staging table, applying Fivetran utilities for column filling and source relation mapping. The final selection cleans and standardizes column names.

```sql
{{ fivetran_utils.fill_staging_columns( source_columns=adapter.get_columns_in_relation(ref('stg_netsuite2__accounting_book_subsidiaries_tmp')), staging_columns=get_netsuite2_accountingbooksubsidiaries_columns() ) }} {{ netsuite.apply_source_relation() }} FROM base
```

--------------------------------

### dbt Model Configuration: NetSuite2 Balance Sheet

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

Configures the NetSuite2 balance sheet dbt model. It sets materialization, partitioning, clustering, unique keys, incremental strategy, and file format based on target environment variables and dbt project settings. It also enables the model only when the appropriate NetSuite data model variable is set.

```sql
{% set multibook_accounting_enabled = var('netsuite2__multibook_accounting_enabled', false) -%}
{%- set using_to_subsidiary_and_exchange_rate = (var('netsuite2__using_to_subsidiary', false) and var('netsuite2__using_exchange_rate', true)) -%}
{%- set balance_sheet_transaction_detail_columns = var('balance_sheet_transaction_detail_columns', []) -%}
{%- set accounts_pass_through_columns = var('accounts_pass_through_columns', []) -%}
{%- set lookback_window = var('lookback_window', 3) -%}
{{ config(
    enabled=var('netsuite_data_model', 'netsuite') == var('netsuite_data_model_override','netsuite2'),
    materialized='table' if target.type in ('bigquery', 'databricks', 'spark') else 'incremental',
    partition_by = {'field': '_fivetran_synced_date', 'data_type': 'date', 'granularity': 'month'} if target.type not in ['spark', 'databricks'] else ['_fivetran_synced_date'],
    cluster_by = ['transaction_id'],
    unique_key='balance_sheet_id',
    incremental_strategy = 'merge' if target.type in ('bigquery', 'databricks', 'spark') else 'delete+insert',
    file_format='delta'
) }}

```

--------------------------------

### Load Department and Currency CTEs (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index

This SQL code defines CTEs for 'departments' and 'currencies'. These CTEs load data from staging tables 'stg_netsuite2__departments' and 'stg_netsuite2__currencies' respectively, providing essential dimensional data for transaction details.

```sql
departments as (
  select * from {{ ref('stg_netsuite2__departments') }}
),
currencies as (
  select * from {{ ref('stg_netsuite2__currencies') }}
)
```

--------------------------------

### NetSuite Pass-Through Columns Jinja Template

Source: https://fivetran.github.io/dbt_netsuite/index

This Jinja template dynamically includes pass-through columns for various NetSuite tables like transactions, transaction_lines, accounts, and locations. It allows users to define custom columns to be included in the dbt model without modifying the core template.

```jinja
{{ netsuite.persist_pass_through_columns(transactions_pass_through_columns, identifier='transactions') }}
{{ netsuite.persist_pass_through_columns(transaction_lines_pass_through_columns, identifier='transaction_lines') }}
{{ netsuite.persist_pass_through_columns(accounts_pass_through_columns, identifier='accounts') }}
{{ netsuite.persist_pass_through_columns(locations_pass_through_columns, identifier='locations') }}
```

--------------------------------

### Generate Surrogate Key for NetSuite Income Statement

Source: https://fivetran.github.io/dbt_netsuite/index

This SQL script generates a surrogate key for the NetSuite income statement data. It dynamically includes 'to_subsidiary_id' and 'accounting_book_id' in the key generation based on Jinja variables like 'using_to_subsidiary', 'using_exchange_rate', and 'multibook_accounting_enabled'. The output is a CTE named 'surrogate_key'.

```sql
surrogate_key as ( {% set surrogate_key_fields = ['source_relation', 'transaction_line_id', 'transaction_id', 'accounting_period_id', 'account_name'] %} {% do surrogate_key_fields.append('to_subsidiary_id') if using_to_subsidiary and using_exchange_rate %} {% do surrogate_key_fields.append('accounting_book_id') if multibook_accounting_enabled %} select *, {{ dbt_utils.generate_surrogate_key(surrogate_key_fields) }} as income_statement_id from income_statement ) select * from surrogate_key
```

--------------------------------

### Union Netsuite Connections for Accounts (SQL)

Source: https://fivetran.github.io/dbt_netsuite/index

This SQL code snippet utilizes the 'netsuite.netsuite_union_connections' macro to combine data from multiple Netsuite connections for the 'account' table. It allows for a single source name and table name, with a default identifier, simplifying the union process for account data across different Netsuite instances.

```sql
{{ config(enabled=var('netsuite_data_model', 'netsuite') == var('netsuite_data_model_override','netsuite2')) }}

{{ netsuite.netsuite_union_connections(
    connection_dictionary=var('netsuite2_sources'),
    single_source_name='netsuite2',
    single_table_name='account',
    default_identifier='account'
) }}
```

--------------------------------

### Conditional Vendor Category Logic in Netsuite

Source: https://fivetran.github.io/dbt_netsuite/index_!/overview

This Jinja code block conditionally includes vendor category information based on the `using_vendor_categories` variable. It handles specific transaction types like vendor bills and credit memos.

```jinja
{% if using_vendor_categories %}
case when lower(transactions.transaction_type) in ('vendbill', 'vendcred') then vendor_categories__transactions.vendor_category_id else vendor_categories__transaction_lines.vendor_category_id end as vendor_category_id,
case when lower(transactions.transaction_type) in ('vendbill', 'vendcred') then vendor_categories__transactions.name else vendor_categories__transaction_lines.name end as vendor_category_name,
{% endif %}
```

--------------------------------

### Configure Netsuite2 Balance Sheet Model

Source: https://fivetran.github.io/dbt_netsuite/index

This snippet shows how to configure the Netsuite2 balance sheet model using dbt variables. It defines settings for multibook accounting, subsidiary and exchange rate usage, transaction detail columns, pass-through columns, and a lookback window for incremental updates. It also specifies materialization, partitioning, clustering, unique keys, incremental strategy, and file format based on the target data warehouse.

```sql
{%- set multibook_accounting_enabled = var('netsuite2__multibook_accounting_enabled', false) -%} {%- set using_to_subsidiary_and_exchange_rate = (var('netsuite2__using_to_subsidiary', false) and var('netsuite2__using_exchange_rate', true)) -%} {%- set balance_sheet_transaction_detail_columns = var('balance_sheet_transaction_detail_columns', []) -%} {%- set accounts_pass_through_columns = var('accounts_pass_through_columns', []) -%} {%- set lookback_window = var('lookback_window', 3) -%} {{ config( enabled=var('netsuite_data_model', 'netsuite') == var('netsuite_data_model_override','netsuite2'), materialized='table' if target.type in ('bigquery', 'databricks', 'spark') else 'incremental', partition_by = {'field': '_fivetran_synced_date', 'data_type': 'date', 'granularity': 'month'} if target.type not in ['spark', 'databricks'] else ['_fivetran_synced_date'], cluster_by = ['transaction_id'], unique_key='balance_sheet_id', incremental_strategy = 'merge' if target.type in ('bigquery', 'databricks', 'spark') else 'delete+insert', file_format='delta' ) }}
```
Tokens:
Raw