/
Understanding your Leapfin data schema

Understanding your Leapfin data schema

  • Verified

    • Table of contents

    Overview

    The data tables within your Leapfin data warehouse provide a reliable and efficient way to store, organize, and retrieve your Leapfin accounting data, supporting accurate and streamlined accounting processes. Data tables organize your accounting data in a structured format, with each table corresponding to a specific data point, such as accounting records, ledger accounts, and journal entries. Snowflake uses a relational database structure, allowing tables to be linked to run queries based on specific relationships. For example, you can link an invoice table with a journal entry table to associate specific invoices with their journal entries.

    This guide details each data table contained within your data warehouse and how they relate to one another.

    Data table relationships

    When querying data, it is important to understand how each table relates to one another to identify specific data in various tables. For example, you may need to identify specific dispute records when querying the journal entry table. As shown in the diagram below, for most tables (not all), the primary identifier is the LEAPFIN_ID column, which is the unique ID Leapfin assigns to each accounting record. Using the previous example, you could identify each specific dispute record based on its LEAPFIN_ID when querying the journal entry table.

    The chart below displays how each table relates to one another and identifies the common data columns between each.

    image-20240514-135236.png

    Journal entry data tables

    Journal entry tables aggregate journal entry data across all accounting records.

    VW_AG_JOURNAL_ENTRIES

    The VW_AG_JOURNAL_ENTRIES table aggregates all journal entries across all accounting records at the daily level. Each line of this table represents a daily journal line. This table is commonly used as the base for aggregated journal entry reports, such as when loading journal entry data to Netsuite. You can use this table to group journal entries across all accounting records.

    Name

    Description

    Name

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    LEAPFIN_DATA_TYPE

    type of accounting record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains the following values:

    • external_code: the unique identifier of this record. This is external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATA_VT

    all data fields of this accounting record in json format.

    DATE_DT

    the book date of the journal entry. Value format "2022-01-31"

    DOCUMENT_DATE_DT

    the date when this accounting record is created in external systems outside Leapfin. Value format "2022-01-31"

    PRODUCT_NAME

    the name of the product associated with the accounting record.

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNT

    the unique identifier of ledger account impacted by this journal line. To get the account name and other details, this ACCOUNT field can be joined with VW_LK_ACCOUNT table (see Chart of Accounts for details).

    ACCOUNTING_SIDE

    the booking direction of the journal line. Acceptable values are "dr" and "cr".

    JE_PAIR_VT

    the journal entry that contains this journal lines.

    JE_METADATA_VT

    Contains rule ID and description

    JE_MEMO_VT

    Contains journal entry tags

    AMOUNT

    the amount of the journal line. A positive number means that the booking direction of the journal line (e.g. "dr") matches with the increasing side of the ledger account (e.g. "dr"). A negative number means the booking direction (e.g. "cr") differs from the account side (e.g. "dr")

    AMOUNT_DR

    the debit amount of the journal line.

    AMOUNT_CR

    the credit amount of the journal line.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_AG_JOURNAL_ENTRIES_MONTHLY

    The VW_AG_JOURNAL_ENTRIES_MONTHLY table consolidates all journal entries across all accounting records and aggregates those journal entries by calendar month. Each line of this table represents a monthly journal entry line. This table provides additional aggregation on top of the VW_AG_JOURNAL_ENTRIES table. When generating monthly or annual journal entry reports, the VW_AG_JOURNAL_ENTRIES_MONTHLY table yields better query performance.

    Name

    Description

    Name

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    LEAPFIN_DATA_TYPE

    type of accounting record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains the following values:

    •  external_code: the unique identifier of this record. This is external id received by Leapfin.

    •  integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    •  leapfin_data_type: type of accounting record, i.e. "LeapfinLineItem", "LeapfinInvoice", etc

    •  leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    •  raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATA_VT

    all data fields of this accounting record in json format.

    MONTH_ID

    the year-month of the journal entry. Value format "202201".

    DOCUMENT_DATE_DT

    the date when this accounting record is created in external systems outside Leapfin. Value format "2022-01-31"

    PRODUCT_NAME

    the name of the product associated with the accounting record.

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNT

    the unique identifier of ledger account impacted by this journal line. To get the account name and other details. This ACCOUNT field can be joined with VW_LK_ACCOUNT table (see Chart of Accounts for details).

    ACCOUNTING_SIDE

    the booking direction of the journal line. Acceptable values are "dr" and "cr".

    JE_PAIR_VT

    the journal entry that contains this journal lines.

    JE_METADATA_VT

    Contains rule ID and description

    JE_MEMO_VT

    Contains journal entry tags

    AMOUNT

    the amount of the journal line. A positive number means that the booking direction of the journal line (e.g. "dr") matches with the increasing side of the ledger account (e.g. "dr"). A negative number means the booking direction (e.g. "cr") differs from the account side (e.g. "dr")

    AMOUNT_DR

    the debit amount of the journal line.

    AMOUNT_CR

    the credit amount of the journal line.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    Accounting record data tables

    VW_FT_INVOICE

    This table aggregates data for all of your invoice accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains following values:

    • external_code: unique identifier of this record. This is external id received by Leapfin.

    • integration_id: unique identifier of the data source. This is internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinInvoice"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is internal id created by Leapfin.

    DATE_DT

    the date of when this Event happened.

    STATUS

    current status of this invoice. Acceptable values are:

    • "drafted": the invoice is created but has not been sent to customers

    • "open": the invoice is issued to customers for payment

    • "paid": the invoice is paid by customers

    • "uncollectible": the invoice is issued but never paid successfully. Payment is not longer expected to be received.

    • "voided": the invoice is canceled after issued to customers

    DUE_DATE_DT

    the date when the payment for this invoice is due.

    ISSUED_DATE_DT

    the date when this invoice is issued. If not issued yet, the value is null. In most cases this will be the same as date. However, if invoices can be in a 'drafted' state, then this date can be different.

    UNCOLLECTIBLE_DATE_DT

    the date when the payment for this invoice becomes uncollectible. If invoice has not become uncollectible or has been paid, then the value is null. Typically used for dunning/bad debt rule scenarios.

    CURRENCY_CODE

    3-digit ISO code, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    PAID_DATE_DT

    the date when this invoice is paid. Typically used for invoice to payment matching.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in json format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the account.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in json format.

    TOTAL

    total amount of invoice including tax and discount. This amount is derived by Leapfin based on the Line Item, Tax, and Discount associated with the Invoice.

    SUBTOTAL

    total amount of invoice excluding tax and discount. This amount is derived by Leapfin based on the Line Item associated with the Invoice.

    EXTERNAL_TOTAL

    total amount of invoice including tax and discount. This amount is received from external source.

    EXTERNAL_SUBTOTAL

    total amount of invoice excluding tax and discount. This amount is received from external source.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_LINE_ITEM

    This table aggregates data for all of your line item accounting records in Leapfin.

    Name

    Description

    Name

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains the following values:

    • external_code: the unique identifier of this record. This is external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinLineItem"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date when this line-item is created in billing system.

    START_DATE_DT

    for subscription item, this is the start date of the subscription.

    END_DATE_DT

    for subscription item, this is the end date of the subscription.

    DESCRIPTION

    general description of the line-item

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    DISCOUNT_AMOUNT

    amount of the discount associated with the line-item. The value should be non-negative.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    QUANTITY

    the number of units purchased.

    AMOUNT

    the amount of the purchased goods or services excluding tax and discount. The value should be non-negative.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_DISCOUNT

    This table aggregates data for all of your discount accounting records in Leapfin.

    Name

    Description

    Name

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in json format. It contains the following values:

    • external_code: the unique identifier of this record. This is external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinDiscount"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date of when this discount is created in billing system.

    CURRENCY_CODE

    3-digit ISO code of amount field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    DESCRIPTION

    A description of the Discount record.

    START_DATE_DT

    For Discounts that are subscription in nature (i.e. time-series based), this is the start date of the period.

    END_DATE_DT

    For Discounts that are subscription in nature (i.e. time-series based), this is the end date of the period.

    AMOUNT

    amount of money impacted by this Discount. The value should be non-negative. This is the primary amount of this record.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_CREDIT

    This table aggregates data for all of your credit accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin.

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in JSON format. It contains the following values:

    • external_code: unique identifier of this record. This is an external id received by Leapfin.

    • integration_id: unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinCredit"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    Unique identifier for your account

    DATE_DT

    the date-time of when this credit was issued or applied.

    DESCRIPTION

    description of the Credit

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.

    AMOUNT

    amount of the credit. The amount should be non-negative. The direction of the credit (i.e. the accounting impact of the credit amount) is derived from the TYPE field.

    TYPE

    The type of credit. Acceptable values are:

    • "issuance": this is representing the credit is being issued.

    • "application": represents this credit record is being used.

    • "adjustment" represents this credit record is an adjustment of credits.

    START_DATE_DT

    the date when the credit becomes available.

    END_DATE_DT

    the date when the credit expires.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in json format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_TAX

    This table aggregates data for all of your tax accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in JSON format. It contains the following values:

    • external_code: the unique identifier of this record. This is an external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinTax"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date of when this tax is created in billing system.

    DESCRIPTION

    general description of the tax.

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record

    AMOUNT

    the amount of the tax. The amount should be non-negative

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: the unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: the amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the account.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_PAYMENT

    This table aggregates data for all of your payment accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in JSON format. It contains the following values:

    • external_code: the unique identifier of this record. This is an external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinPayment"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date of when this payment is created by payment processor.

    STATUS

    current status of the payment. Acceptable values are:

    • "succeeded": the payment is successfully processed / settled.

    • "failed": the payment failed during processing.

    • "pending": the payment has been initiated but still being processed.

    DESCRIPTION

    general description of the payment .

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record

    SUCCEEDED_DATE_DT

    the date when the payment is successfully processed / settled.

    EXCHANGE_RATES_VT

    a list of exchange rates used to calculate currency conversions. Each rate contains the following values:

    • currencyCode: 3-digit ISO code, e.g. “USD”, "GBP", etc. This is the target currency of this record.

    • rate: conversion rate from primary currency to target currency. i.e. amount in primary currency * rate = converted amount in target currency.

    CUSTOM_FIELDS_VT

    additional fields (i.e. key-value pairs) about the accounting record in JSON format

    ACCOUNTING_TRANSACTIONS_VT

    a list of journal entries associated with this accounting record. Each item contains the following values:

    • date: the date of the journal entry

    • entries: a list of journal lines within the journal entry. Each line contains the following values:

      • account: the unique identifier of the accounting account. This is an internal id created by Leapfin (refer to VW_LK_ACCOUNT).

      • amount: the amount of money booked towards the account.

      • accountingSide: an indicator of whether the amount is a debt ("dr") or credit ("cr") to the acount.

    • comments: additional fields (i.e. key-value pairs) on the journal entry in JSON format.

    AMOUNT

    the amount of the payment. The amount should be non-negative

    CREATED_AT_DT

    the date-time when the accounting record is created by Leapfin. Value format: "2022-07-14 23:01:26.400"

    UPDATED_AT_DT

    the date-time when the accounting record is last updated by Leapfin. Value format: "2022-09-01 06:09:57.379"

    VW_FT_REFUND

    This table aggregates data for all of your refund accounting records in Leapfin.

    Column

    Description

    Column

    Description

    LEAPFIN_ID

    unique identifier created by Leapfin

    DECODED_LEAPFIN_ID_VT

    decoded version of LEAPFIN_ID in JSON format. It contains the following values:

    • external_code: the unique identifier of this record. This is an external id received by Leapfin.

    • integration_id: the unique identifier of the data source. This is an internal id created by Leapfin.

    • leapfin_data_type: type of accounting record, i.e. "LeapfinRefund"

    • leapfin_suffix: additional string to distinguish different accounting records when the same external_code is received from the data source.

    • raw_data_type: type of raw data record received from the data source.

    ORGANIZATION_ID

    unique identifier of the account. This is an internal id created by Leapfin.

    DATE_DT

    the date of when this refund is created by payment processor.

    CURRENCY_CODE

    3-digit ISO code of AMOUNT field, e.g. “USD”, "GBP", etc. This is the primary currency of this record.