Acquiring Data Dictionary

The Highnote Acquiring Data Dictionary provides all the data available for querying your payment transactions, items, and orders.

The queries and objects below demonstrate how you can construct on-demand reports and generate metrics as needed.

This section lists the data tables and associated fields available to query your Highnote data.

The payment_transaction_event table is mutable and always displays the current state.

Use the following fields to query your Payment Transactions.

The payment_instruction table is mutable and always displays the current state.

Use the following fields to query your Payment Instructions.

The payment_order_item table is mutable and always displays the current state.

Use the following fields to query your Payment Orders.

This acquiring query library includes a selection of common Highnote queries in the Snowflake dialect. We demonstrate how to generate the following types of reports:

• Foundational reports
• Transaction detail reports
• Aggregated financial reports
• Distribution reports

The queries below include a defined date range which you can edit as necessary:

Copy code snippet

To see the full data set, remove the BETWEEN condition in each query:

Copy code snippet

Foundational reports are essential for understanding the system's structure and configuration. These reports identify available accounts, ledger types, and their relationships, providing the foundational context needed to interpret all other financial reports.

The following query returns all accounts and associated ledgers.

Transaction detail reports monitor individual transactions and their impact on account balances over time. These reports show the complete flow of funds through the system, including running balances, transaction linkages, and reconciliation details necessary for auditing and troubleshooting.

The following query returns the running balance of a settlement account, showing how each transaction affects the account balance over time.

The following query returns all transactions and running balances for a Product Funding account, linking each ledger entry to its corresponding payment event for reconciliation purposes.

The following query generates a parameterized monthly ledger report for a Product Funding Account, showing all transactions and balance changes for a specified date range in reverse chronological order.

Aggregated reports consolidate transaction data into meaningful business metrics. These reports provide executive-level insights into settlement volumes, payout amounts, and platform fees across different time periods and payment brands.

The following query generates a monthly settlement report for a defined date range, summarizing completed payouts by payment brand with counts, gross amounts, merchant fees, and net settlement totals.

The following query summarizes daily payout batches for a defined date range, showing the number of transactions and total amount disbursed in each payout run.

The following query calculates the total platform fees earned from all completed payouts during a defined date range.

Distribution reports reveal how the payment system operates behind the scenes. These reports show payment routing rules, fund distribution logic, and product-level performance metrics that help optimize payment operations and understand revenue allocation.

The following query retrieves payment distribution instructions for a defined date range, showing how order amounts are split across different financial accounts based on percentage-based routing rules.

The following query analyzes cleared transactions by payment brand and product/item type for a defined date range, showing total sales amounts for each combination.