Warehouse

The Basics

The Twisp ledger exports all data to AWS Redshift and exposes APIs to interact with the data using arbitrary SQL queries. When enabled on your tenant, this warehouse provides a convenient way to execute analytical queries or unload data to your own data lake or warehouse.

Components of Warehouse

GraphQL Interface

Twisp exposes the Redshift Data API in GraphQL format for execution of SQL queries. The most commonly used API interactions are described here.

Execute Statement

The execute statement API allows you to execute a statement on Redshift.

Copymutation ExecuteStatement($SQL:String! = "SELECT TRUE") {
  warehouse{
    executeStatement(input:{
      SQL: $SQL
    }) {
      id
    }
  }
}

Describe Statement

Describe Statement allows you to check on the status of a running query and retrieve metadata about it's results.

Copyquery DescribeStatement($id:String!) {
  warehouse{
    describeStatement(input:{
      id:$id
    }) {
      resultRows
      resultSize
      status
      error
    }
  }
}

Cancel Statement

Cancel statement cancels a running statement.

Copymutation CancelStatement($id:String!) {
  warehouse{
    cancelStatement(
      input:{
        id
      }
    )
  }
}

Get Statement Result

Get Statement Result allows you to fetch the results of a query via the API with paging. If the result is particularly large, you may opt to instead use the UNLOAD capabilty to export data in a suitable format to an S3 bucket of your choosing.

Copyquery GetStatementResult($id:String!) {
  warehouse{
    getStatementResult(input:{
      id:$id
      #nextToken:""
    }) {
      records {
        fields {
          type
          value {
            str
            bytes
            isNull
          }
        }
      }
      nextToken
    }
  }
}

Views and Schemas

Twisp maintains two views for each entity in the ledger:

• entity: the latest version of any entity stored in the Twisp ledger.
• entity_history: all versions of a particular record stored in the Twisp ledger.

Each record contains a number of header rows prepended with record_:

• record_begin - A unique timestamp of when the Twisp database transaction began.
• record_rowid - A uuid identifier of the particular item in Twisp.
• record_status - An enumeration indicating whether this record is deleted.
• record_tenantid - A uuid indicating the tenant that this record belongs to.
• record_version - An monotonically increasing unsigned integer indicating the version of the record.

These columns allow for incremental exports via record_begin, which are guaranteed to be unique per transaction. In other words, records with the same timestamp originated within the same transaction. The record_rowid and record_version together uniquely identify a particular version of a record and is a convenient way to de-duplicate rows in the event you use overlapping timestamps to import data.

The following entities are available in the warehouse:

• public.account
• public.account_history
• public.account_set
• public.account_set_history
• public.account_set_member
• public.account_set_member_history
• public.balance
• public.balance_history
• public.calculation
• public.calculation_history
• public.entry
• public.entry_history
• public.journal
• public.journal_history
• public.tran_code
• public.tran_code_history
• public.transaction
• public.transaction_history
• public.transaction_exception
• public.transaction_exception_history
• public.workflow_execution
• public.workflow_execution_history

The schemas between each entity and entity_history view are identical and are provided below.

account

Every Account and Account Set in Twisp has an account record stored.

account_set

The account_set table has all Account Sets in the Twisp ledger.

account_set_member

The account_set_member table maintains the Account Set tree membership details. The member_id can refer to either an Account Set or Account, based on member_type.

balance

Contains all Balance records and versions. Note that the dimension column is of VARBYTE type. If unloading, you must convert into a base64 encoded string:

CopySELECT FROM_VARBYTE(dimension,'base64') AS dimension FROM balance;

calculation

Contains the calculation definitions in the Ledger.

entry

All of the journal entries in the Ledger.

journal

All of the journals in the Ledger.

tran_code

All of the Transaction Code definitions in the Ledger.

transaction

All of the Transactions posted in the Ledger.

transaction_exception

All Transaction Exceptions for WARN and VOID velocity control enforcements.

velocity_control

All Velocity Controls defined in the ledger.

velocity_limit

All Velocity Limits defined in the Ledger.

workflow_execution

A log of all workflow executions for workflow.execute.

Warehouse Operations

Use GraphQL queries and mutations to execute queries and describe results in the data warehouse:

• Query.warehouse.describeStatement(): Describe the status of executing query
• Query.warehouse.describeTable(): Describe a table.
• Query.warehouse.getStatementResult(): Retrieve the result of a table.
• Query.warehouse.listDatabases(): List databases available to query.
• Query.warehouse.listSchemas(): List schemas available in database.
• Query.warehouse.listStatements(): List recent executing statements.
• Query.warehouse.listTables(): List tables in schema.
• Mutation.warehouse.executeStatementSync(): Execute a SQL statement and synchronously wait for result.
• Mutation.warehouse.batchExecuteStatement(): Execute a number of SQL statements asynchronously.
• Mutation.warehouse.cancelStatement(): Cancel a running statement.
• Mutation.warehouse.executeStatement(): Execute a single SQL statement asynchronously.