netsuite.yml

version: 2

models:
  - name: int_netsuite__accountxperiod_exchange_rate_map
    description: >
      (Step 1/4) In order to accurately recreate the balance sheet and income statement, 
      it is necessary to convert all transaction amounts into the currency of 
      the parent subsidiary. The logic gets complicated fast, mostly because 
      of the behavior of the balance sheet.  On the balance sheet, the conversion 
      rate you use for a single transaction will differ by accounting period.  
      For example, if a transaction took place in August, and you are generating 
      balances for the December period, you will need to convert the August transaction 
      using the December conversion rate.

      The first step here is to create a mapping of all accounting periods and the 
      respective exchange rates, by subsidiary. This is called period_exchange_rate_map

      Next, we cross join the accounts table to the period_exchange_rate_map, so we can 
      generate a map of exchange rates, by account, accounting period, and subsidiary.
  
  - name: int_netsuite__transaction_lines_w_accounting_period
    description: >
      (Step 2/4) Next, we need to prepare a cleaned version of all transactions we need.  
      Using the transactions and transaction_lines tables, the necessary fields are extracted, 
      and transactions that are deleted, revenue arrangements, or non-posting transactions are 
      filtered out. This is called transaction_lines_w_accounting_period. 
    
  - name: int_netsuite__transaction_and_reporting_periods
    description: >
      (Step 3/4) Once we have the cleaned transaction_lines_w_accounting_periods, we now need to figure out
      which exchange rate we should use for the currency conversion.  The balance sheet complicates 
      things, as conversion rates vary based on the reporting period.  Therefore, all transactions 
      need to be converted not only for the period in which the transaction took place in, but also 
      all subsequent periods. transaction_and_reporting_periods creates the necessary mapping for this.

  - name: int_netsuite__transactions_with_converted_amounts
    description: >
      (Step 4/4) Now that we have the exchange rates and the unconverted amounts, the next step is 
      to calculate the converted total.
      Additionally, we add in a couple of extra fields that will help us in our final balance sheet 
      and income statement queries.

  - name: netsuite__balance_sheet
    description: >
      The balance sheet query uses the transactions_with_converted_amount
      transformation to recreate all lines necessary for the balance sheet.
      Transactions that are not balance sheet transactions are categorized as
      either Retained Earnings or Net Income.  The Cumulative Translation
      Adjustment total, which in most cases does not have transactions
      associated with it, is calculated manually in the second part of the query.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - transaction_line_id
            - transaction_id
            - accounting_period_id
            - account_name
    columns:
    - name: transaction_id
      description: Netsuite internal transaction ID.
      tests:
        - not_null
    - name: transaction_line_id
      description: Netsuite internal transaction line ID.
      tests:
        - not_null
    - name: accounting_period_id
      description: The unique identifier of the accounting period.
    - name: accounting_period_ending
      description: End date of the accounting period
    - name: accounting_period_full_name
      description: Accounting periods full name.
    - name: accounting_period_name
      description: Name of the accounting period.
    - name: is_accounting_period_adjustment
      description: Yes/No field, indicating whether or not the selecting accounting period is an adjustment period.
    - name: is_accounting_period_closed
      description: Yes/No field, indicating whether or not the selecting accounting period is closed.
    - name: account_category
      description: Category of the account.  Options include Asset, Liability, Equity, Expense or Income.
    - name: account_name
      description: Name of the account.
    - name: account_type_name
      description: The accounts type name.
    - name: account_id
      description: The unique identifier of the account.
    - name: account_number
      description: Account number associated with the account.
    - name: converted_amount
      description: Transaction amount, converted into the primary subsidiary's default currency.
    - name: balance_sheet_sort_helper
      description: Helper column for sorting balance sheet records.

  - name: netsuite__income_statement
    description: >
      The income statement query uses the transactions_with_converted_amount transformation 
      to recreate all lines necessary for the income statement.  It also joins in class, 
      department and location information for enhanced reporting. 
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - transaction_line_id
            - transaction_id
            - accounting_period_id
            - account_name
    columns:
    - name: transaction_id
      description: Netsuite internal transaction ID.
      tests:
        - not_null
    - name: transaction_line_id
      description: Netsuite internal transaction line ID.
      tests:
        - not_null
    - name: accounting_period_id
      description: The unique identifier of the accounting period.
    - name: accounting_period_ending
      description: End date of the accounting period.
    - name: accounting_period_full_name
      description: Accounting periods full name.
    - name: accounting_period_name
      description: Name of the accounting period.
    - name: is_accounting_period_adjustment
      description: Yes/No field, indicating whether or not the selecting accounting period is an adjustment period.
    - name: is_accounting_period_closed
      description: Yes/No field, indicating whether or not the selecting accounting period is closed.
    - name: account_name
      description: Name of the account.
    - name: account_type_name
      description: The accounts type name.
    - name: account_id
      description: The unique identifier of the account.
    - name: account_number
      description: Account number associated with the account.
    - name: account_number_and_name
      description: Concatenation of account number and account name.
    - name: class_full_name
      description: Full name of the class.
    - name: location_full_name
      description: Full name of the location.
    - name: department_full_name
      description: Full name of the department.
    - name: converted_amount
      description: Transaction amount, converted into the primary subsidiary's default currency.
    - name: account_category
      description: Category of the account. Options include Asset, Liability, Equity, Expense or Income.
    - name: income_statement_sort_helper
      description: Helper column for sorting income statement records.
    - name: subsidiary_id
      description: The unique identifier of the subsidiary.
    - name: subsidiary_full_name
      description: The full name of the subsidiary.
    - name: subsidiary_name
      description: Name of the subsidiary.

  - name: netsuite__transaction_details
    description: >
      This table uses Netsuite's core table, transaction_lines, and joins a handful of 
      other tables to add more detail to those line items.  For all transactions, you are 
      able to see the associated accounting period, account and subsidiary.  Where applicable, 
      you can also see information about the customer, location, item, vendor, and department.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - transaction_line_id
            - transaction_id
    columns:
    - name: transaction_line_id
      description: Netsuite internal transaction line ID.
      tests:
        - not_null
    - name: transaction_memo
      description: Memo associated with the transaction line.
    - name: is_transaction_non_posting
      description: Yes/No field, indicating whether or not the transaction line is non-posting.
    - name: transaction_id
      description: Netsuite internal transaction ID.
      tests:
        - not_null
    - name: transaction_status
      description: Status of the transaction (Closed, Pending Billing, Billing, etc.).
    - name: transaction_date
      description: Timestamp of the date which the transaction occurred.
    - name: transaction_due_date
      description: Timestamp of the date which the transaction is due.
    - name: transaction_type
      description: Type identifier of the transaction.
    - name: is_transaction_intercompany
      description: Yes/No field, indicating whether or not the transaction is an intercompany transaction or an advanced intercompany transaction.
    - name: accounting_period_ending
      description: End date of the accounting period.
    - name: accounting_period_full_name
      description: Full name of the accounting period.
    - name: accounting_period_name
      description: Name of the accounting period.
    - name: is_accounting_period_adjustment
      description: Yes/No field, indicating whether or not the selecting accounting period is an adjustment period.
    - name: is_accounting_period_closed
      description: Yes/No field, indicating whether or not the selecting accounting period is closed.
    - name: account_name
      description: Name of the account.
    - name: account_type_name
      description: The accounts type name.
      tests:
        - accepted_values:
            values: ['Accounts Receivable', 'Bank', 'Deferred Expense', 'Fixed Asset', 'Other Asset', 'Other Current Asset', 'Unbilled Receivable', 'Prepaid Expense', 'Cost of Goods Sold', 'Expense', 'Other Expense', 'Income', 'Other Income', 'Accounts Payable', 'Credit Card', 'Deferred Revenue', 'Long Term Liability', 'Other Current Liability', 'Equity', 'Retained Earnings', 'Net Income', 'Non Posting', 'Statistical']
            config:
              severity: warn
    - name: account_id
      description: Unique identifier of the account.
    - name: account_number
      description: Account number associated with the account.
    - name: is_account_leftside
      description: Yes/No field indicating whether or not the account is leftside.
    - name: is_accounts_payable
      description: Yes/No field indicating whether or not the account type name includes 'accounts payable'.
    - name: is_accounts_receivable
      description: Yes/No field indicating whether or not the account type name includes 'accounts receivable'.
    - name: is_account_intercompany
      description: Yes/No field indicating whether or not the account type name includes 'intercompany'.
    - name: parent_account_name
      description: Name of the parent account, if parent account relationship exists.  Otherwise, the name of the account.
    - name: is_income_account
      description: Yes/No field indicating whether or not the account is an income account.
    - name: is_expense_account
      description: Yes/No field indicating whether or not the account is an expense account.
    - name: company_name
      description: Name of the company.
    - name: customer_city
      description: City where the customer is located.
    - name: customer_state
      description: State where the customer is located.
    - name: customer_zipcode
      description: Zip Code of the customer.
    - name: customer_country
      description: Country where the customer is located.
    - name: customer_date_first_order
      description: Date customer placed first order.
    - name: customer_external_id
      description: The unique identifier of the external customer reference.
    - name: class_full_name
      description: Full name of the class.
    - name: item_name
      description: Name of the item.
    - name: item_type_name
      description: Type name of the item.
    - name: sales_description
      description: Description of the item for sales purposes.
    - name: location_name
      description: Name of the location.
    - name: location_city
      description: City used as a location reference.
    - name: location_country
      description: Country used as a location reference.
    - name: vendor_type_name
      description: Type name of the vendor.
    - name: vendor_name
      description: Name of the vendor.
    - name: vendor_create_date
      description: Date vendor was created.
    - name: currency_name
      description: Name of the currency used.
    - name: currency_symbol
      description: Symbol used to identify the currency type.
    - name: department_name
      description: Name of the department.
    - name: subsidiary_name
      description: Name of the subsidiary.
    - name: converted_amount
      description: Transaction amount, converted into the primary subsidiary's default currency.
    - name: transaction_amount
      description: Total amount of the transaction line.