/
Access and query data in your data warehouse
  • Verified

    • Access and query data in your data warehouse

    Owned by Ian Fondrk (Unlicensed)
    Last updated: Jun 24, 2024
    9 min read

    Table of contents

    Access your data

    Once you set up your Leapfin data warehouse account (using either Snowflake Secure Data Share or Leapfin’s data warehouse), you will see the following databases upon logging into your account:

    • LEAPFIN_DATA

    • LEAPFIN_SANDBOX (if available)

    Data tables overview

    Within your Leapfin databases, Leapfin generates the following data tables which you can use to query your accounting data.

    • The tables that Leapfin populates are based on your specific account data. As such, while all tables displayed below will be visible, some may not populate if there is no associated data.

    • The data within these tables updates as soon as a run is complete.

    The table below outlines general information about your data tables. For an in-depth look at each data table, see Understanding your Leapfin data schema

    Table

    Description

    Table

    Description

    Journal entry-level tables

    VW_AG_JOURNAL_ENTRIES

    • 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.

    VW_AG_JOURNAL_ENTRIES_MONTHLY

    • Aggregates all journal entries across all accounting records by month.

    • Each line of this table represents a monthly journal line.

    • This table is commonly used when generating monthly or annual JE reports, as the monthly table yields better query performance.

    Individual accounting record tables

    VW_FT_CONTRACT

    Aggregates all contract records in Leapfin.

    VW_FT_CREDIT

    Aggregates all credit records in Leapfin.

    VW_FT_CREDIT_NOTE

    Aggregates all credit note records in Leapfin.

    VW_FT_CUSTOMER

    Aggregates all customer records in Leapfin.

    VW_FT_DISCOUNT

    Aggregates all discount records in Leapfin.

    VW_FT_DISPUTE

    Aggregates all dispute records in Leapfin.

    VW_FT_FEE

    Aggregates all fee records in Leapfin.

    VW_FT_INVOICE

    Aggregates all invoice records in Leapfin.

    VW_FT_LINE_ITEM

    Aggregates all line item records in Leapfin.

    VW_FT_PAYMENT

    Aggregates all payment records in Leapfin.

    VW_FT_RECOGNITION_EVENT

    Aggregates all event records in Leapfin.

    VW_FT_REFUND

    Aggregates all refund records in Leapfin.

    VW_FT_SETTLEMENT

    Aggregates all payout/settlement/disbursement records in Leapfin.

    VW_FT_TAX

    Aggregates all tax records in Leapfin.

    Account-level tables

    VW_LK_ACCOUNT

    Aggregates the details of each ledger account in your chart of accounts.

    VW_LK_ACCOUNT_GROUP

    Aggregates the details of each account group in your chart of accounts.

    Linking tables

    VW_RL_LINK

    Displays how the various accounting records are linked to one another.

    Preview a data table

    If you want to preview a data table without running a full query, select the desired table on the Databases tab and click the magnifying glass icon in the preview panel. This is helpful when you need to identify specific columns to query.

    Query your data

    Think of querying data in Snowflake as asking questions to the database to retrieve specific information from your financial data. Snowflake queries are written in SQL (Structured Query Language) and are used to interact with the data stored in Snowflake databases. Queries can perform various operations, including retrieving data, aggregating data, and more. You can use queries to analyze your financial data to make data-driven decisions, ensure compliance, and contribute to the financial health and strategic planning of the organization.

    Create and run a new query

    Use the following steps to query your Leapfin data contained in the tables above:

    1. Log into your organization’s Snowflake account (if using Secure Data Share) or Leapfin’s data warehouse.

    2. Click Worksheets in the left navigation.

    1. Select an existing worksheet from the list or click the + button to create a new SQL Worksheet.

    2. Type your SQL query syntax in the space provided. See the sections below for detailed instructions on using fundamental SQL query commands and examples using Leapfin data.

    1. Click the play button to run your query.

    If you want to create a new SQL worksheet, click the + button at the top of the page.

    Manage your queries

    Use the Worksheets tab in the left panel to search, organize, and manage your worksheets. This functionality is helpful if you want to save a query to use multiple times.

    Fundamental SQL query commands

    The following section outlines the fundamental SQL query commands and provides syntax examples for each. These queries can be combined and modified to suit more complex requirements. Understanding these basics provides a solid foundation for exploring and analyzing data in Snowflake.

    • For more detailed guidance, refer to Snowflake’s SQL Command Reference documentation.

    • For common Leapfin-specific queries, see the Leapfin-specific example queries section in this document

    Select specific data to query

    Use the SELECT clause to specify the table columns from which you want to retrieve data (or use * to SELECT data from all columns in a table). Use the FROM command to indicate the table from which you want to retrieve data. You can also use the WHERE clause to filter rows in those tables based on a specified condition or set of conditions. If you want to combine multiple conditions in your query, use the AND operator.

    Basic query syntax:

    SELECT column1, column2 FROM table_name WHERE condition; AND condition 2 AND condition 3

    Example query using Leapfin data:

    SELECT * FROM VW_FT_LINE_ITEM WHERE decoded_leapfin_id_vt:integration_id = 555 AND custom_fields_vt:productName = 'License' AND date_dt BETWEEN '2022-08-01' AND '2022-08-31' LIMIT 100;

    Limit query results

    Use the LIMIT clause to restrict the number of rows returned by a query. It is often employed to limit the result set to a specified number of rows, making it useful when you want to table or analyze only a subset of the data. As a best practice, Leapfin recommends adding a LIMIT to every query to stay within the limits of your query allotment.

    Basic query syntax:

    SELECT column1, column2 FROM table_name LIMIT number_of_rows;

    Example query using Leapfin data:

    SELECT * FROM VW_FT_LINE_ITEM WHERE decoded_leapfin_id_vt:integration_id = 555 LIMIT 100;

    Join data tables

    Use the JOIN command to combine rows from two or more tables based on a related column. Joining is a crucial part of querying relational databases and is used to retrieve data that spans multiple tables. When joining, you can also use the ON clause in conjunction with the JOIN keyword to specify the condition for joining tables. When you perform a join operation to combine rows from two or more tables, the ON clause is used to define the criteria for matching rows between the tables.

    Basic query syntax:

    SELECT columns FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

    Example query using Leapfin data:

    SELECT * FROM vw_ft_line_item li JOIN vw_ag_journal_entries je ON je.leapfin_id = li.leapfin_id

    Sort query results in ascending or descending order

    Use the ORDER BY clause to sort query results in either ascending (ASC) or descending (DESC) order. The default order is ascending, but you can also specify a descending order for one or more columns.

    Basic query syntax:

    SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

    Example query using Leapfin data:

    SELECT * FROM VW_FT_LINE_ITEM ORDER BY START_DATE_DT ASC LIMIT 100;

    Aggregate data

    Use aggregate functions SUM, AVG, and COUNT to perform a calculation on a set of values and return a single value. Use AS to name the column used to return the aggregate amount. These functions operate on a group of rows specified by the GROUP BY clause in a query. Aggregate functions are commonly used to summarize or aggregate data in a meaningful way, such as calculating totals, averages, counts, or other summary statistics.

    Basic query syntax:

    SELECT column1, column2, ..., aggregate_function(column) AS aggregate column name FROM table_name GROUP BY column1, column2, ...;

    Example query using Leapfin data:

    SELECT account, SUM (je.amount) as total_amount FROM VW_AG_JOURNAL_ENTRIES je GROUP BY account

    Query scenario walkthrough

    The following section provides a step-by-step walkthrough of how to input a query using the SQL fundamentals outlined above.

    Example scenario

    You are preparing a report for a month-end financial retable and have two key sources of data:

    • Journal Entries (VW_AG_JOURNAL_ENTRIES): Think of this as your detailed ledger, recording every transaction (entry) with its date (date_dt) and amount (amount).

    • Account List (VW_LK_ACCOUNT): This is like your chart of accounts, listing various accounts with their names (display_name) and unique account numbers (account).

    Your task is to prepare a report showing the name of each account along with the transactions that occurred in the current month and their amounts.

    How to compile a query for this scenario:

    1. Use the SELECT clause to display the account name (a.display_name), the date of each transaction (je.date_dt), and the transaction amount (je.amount).

      1. Identifiers (je, a) act like shorthand references for your data sources. When referencing columns like date_dt or display_name, you can add these identifiers to indicate whether the information comes from the ledger (je) or the chart of accounts (a). This clarity is crucial when dealing with multiple data sources that might have columns with the same name but different meanings.

    SELECT a.display_name, je.date_dt, je.amount

    1. Use FROM to indicate that you first want to pull data from the VW_AG_JOURNAL_ENTRIES (je) table.

    SELECT a.display_name, je.date_dt, je.amount FROM VW_AG_JOURNAL_ENTRIES je

    1. Use JOIN to join the VW_AG_JOURNAL_ENTRIES table with the VW_LK_ACCOUNT table (a). This is like combining your transaction records with the account names to provide context to each entry.

    SELECT a.display_name, je.date_dt, je.amount FROM VW_AG_JOURNAL_ENTRIES je JOIN VW_LK_ACCOUNT a

    1. Use the ON clause to indicate that the JOIN is based on the account number (je.account = a.account). This ensures that each transaction in the ledger is correctly matched with its corresponding account name from the chart of accounts.

    SELECT a.display_name, je.date_dt, je.amount FROM VW_AG_JOURNAL_ENTRIES je JOIN VW_LK_ACCOUNT a ON je.account = a.account

    1. Use the WHERE clause to filter the transactions to include only those from the current month. The date_trunc function truncates the dates to the first day of the month, allowing comparison on a month-to-month basis.

    SELECT a.display_name, je.date_dt, je.amount FROM VW_AG_JOURNAL_ENTRIES je JOIN VW_LK_ACCOUNT a ON je.account = a.account WHERE date_trunc('MONTH', je.date_dt) = date_trunc('MONTH', current_date)

    1. Finally, use the LIMIT clause to restrict the report to the first 100 transactions to keep it concise and manageable.

    SELECT a.display_name, je.date_dt, je.amount FROM VW_AG_JOURNAL_ENTRIES je JOIN VW_LK_ACCOUNT a ON je.account = a.account WHERE date_trunc('MONTH', je.date_dt) = date_trunc('MONTH', current_date) LIMIT 100;

    Helpful Leapfin-specific query examples

    Query accounting records to reconcile to your integration reports

    SELECT SUM (amount) FROM vw_ft_line_item WHERE decoded_leapfin_id_vt:integration_id = ### AND custom_fields_vt:productName = 'License' AND date_dt between 'YYYY-MM-DD' and 'YYYY-MM-DD' AND custom_fields_vt:originalAmount - discount_amount != 0 AND currency_code = 'USD' LIMIT 10;

    Query journal entries to reconcile your Revenue dashboard to your general ledger

    SELECT * FROM vw_ag_journal_entries je WHERE je.leapfin_data_type = 'LeapfinLineItem' AND je.document_date_dt between 'YYYY-MM-DD' and 'YYYY-MM-DD' AND je.product_name = 'License' AND je.decoded_leapfin_id_vt:integration_id = ### AND je.account = '1001-2000-RC' LIMIT 10;

    Query line items from the invoices created in the last month with an open status

    SELECT li.* FROM vw_ft_invoice i JOIN vw_rl_link lnk ON li.leapfin_id = lnk.leapfin_id JOIN vw_ft_line_item li ON lnk.linked_leapfin_id = i.leapfin_id WHERE date_trunc('month', i.date_dt) = date_trunc('month', add_months(current_date, -1)) AND status in ('open') LIMIT 10;

    Query to determine which Leapfin accounting records do not have associated journal entries

    SELECT * FROM core_276.rep.vw_ft_line_item li WHERE li.date_dt between 'YYYY-MM-DD' and 'YYYY-MM-DD' AND li.accounting_transactions_vt = '[]' LIMIT 10;

    Query transactions caused by a locking push

    SELECT je.leapfin_data_type, coalesce(split(je.je_metadata_vt:ruleName::text,'from')[0],'') rule_typeproduct_name , round(sum(je.amount),2) as amount , round(sum(je.amount_cr),2) as amount_cr , round(sum(je.amount_dr),2) as amount_dr FROM core_###.core.ag_cor_journal_entries_monthly je JOIN core_###.rep.vw_lk_account a on je.account = a.account WHERE TRUE AND (je.month_id = ###### AND date_trunc('month',je.document_date_dt) <'YYYY-MM-DD' AND coalesce(split(je.je_metadata_vt:ruleName::text,'from')[0],'') ilike '%locking treatment%' ) GROUP BY all

    Result caching

    Result caching is a Snowflake feature that allows you to store the results of previously executed queries so that they can be reused when the same query is executed again. Snowflake caches and persists query results for every executed query; however, there is a limit to how many query results can be stored in the result cache. If this limit is reached, you will encounter a Result Cache Limit Reached error.

    There are three methods you can use to resolve this issue:

    • Contact Snowflake support to increase the result cache size.

    • Use the NO RESULT_CACHE query hint.

    • Use the CLEAR RESULT CACHE command to manually clear the result cache for specific queries.

    Related content