Skip to main content

Streaming Orders and History into Amazon S3 / Athena

Overview​

This document explains how to configure and use the Amazon Simple Storage Service (Amazon S3) and Amazon Athena as data warehouses for the Execution Server (ES).

Amazon S3 is a scalable, high-speed, web-based cloud storage service designed for online backup and archiving data and applications on Amazon Web Services.

Once you setup streaming for historical orders and trades into S3, you can use serverless services like Athena and QuickSight to run data analytics.

S3 Data warehouse quicksight

Configure S3​

To configure S3, follow these steps:

  1. Create an S3 Bucket using the AWS Management Console, as described in the Amazon S3 Guide. You will need a bucket name and region for the Execution Server (ES) configuration.
  2. Create a separate AWS user called "ember-s3-loader" for the ES to load data into the S3 bucket. Only grant the user read and write permissions for this bucket.
  3. Log in to the AWS Management Console as "ember".
  4. Click on the username > My Security Credentials.
  5. Under Access Keys for CLI, SDK, & API Access, click on Create Access Key. Note the API access key and secret key generated by AWS.

Configure the Execution Server​

The Execution Server can export data into S3 in two modes:

  • Live Mode: A special daemon service exports data in near real-time.
  • Batch Mode: A periodic process exports all recently accumulated data in batches.

To configure the ES, add the following section to $EMBER_HOME/ember.conf:

warehouse {
  s3 {          # unit id, you will use it when you run the app, it might be any
    live = true         # false for batch mode (true is default)
    commitPeriod = 1h   # repeatedly commits not full batch at the specified period (5s is default)

    messages = [                               
      ${template.warehouse.s3.messages} {      # loader which loads order messages
        filter.settings.inclusion = null       # EVENTS or TRADES, null means no filter
        loader.settings {               # default   # description
            bucket = "ember-s3-data"   # -         # AWS S3 bucket name   
            region = "us-east-2"       # -         # AWS S3 bucket region
            accessKey = "******"       # -         # AWS API access key
            secretKey = "******"       # -         # AWS API secret key
            minBatchSize = 0           # 0         # min number of records in uploaded batch
            maxBatchSize = 0           # 0         # max number of records in uploaded batch
        }
      }
    ]

    orders = [                               
      ${template.warehouse.s3.orders} {        # loader which loads closed orders
         loader.settings {               # default   # description
            bucket = "ember-s3-data"   # -         # AWS S3 bucket name   
            region = "us-east-2"       # -         # AWS S3 bucket region
            accessKey = “******"       # -         # AWS API access key
            secretKey = "******"       # -         # AWS API secret key
            minBatchSize = 0           # 0         # min number of records in uploaded batch
            maxBatchSize = 0           # 0         # max number of records in uploaded batch
        }
      }
    ]
  }
}

danger

For production setups, store the API key and secret key in the Hashicorp Vault or hash them using the Mangle tool. For more information, refer to the Ember Configuration Guide.

Control S3 Object Size​

minBatchSize & maxBatchSize​

Parameters minBatchSize and maxBatchSize are optional attributes that can be used in combination with commitLimit to control the size of S3 objects uploaded by the utility.

  • When the maxBatchSize value is > 0, the utility uploads the next batch as soon as it reads the maxBatchSize messages without waiting for the current commitPeriod to end. 
  • When minBatchSize is specified, and the number of records in the batch is less than minBatchSize, the utility does not write the batch at the end of the commitPeriod.
  • In the batch mode, after retrieving the last message from the journal, the utility uploads all the remaining messages in the batch regardless of the minBatchSize value.

storageClass​

To facilitate managing exported S3 objects so that they are stored cost effectively throughout their lifecycle, we also support an optional storageClass attribute that allows setting the Amazon S3 storage class of created S3 objects.

For instance, to use Intelligent Tiering instead of the default Standard storage class, the storageClass attribute in the S3 warehouse message or order configuration would need to be set like this:

loader.settings {    
    …
    storageClass = “INTELLIGENT_TIERING”
}

Note that instead of setting the S3 storage class when data is exported, users have an option to transition S3 objects to different storage classes during their lifecycle by configuring the S3 bucket lifecycle policy.

For example, the policy could be to transition objects to the S3 Intelligent Tiering storage class 30 days after they were exported or to archive them to the S3 Glacier storage class after one year.

For more information, see the Object Lifecycle Management section in the Amazon Simple Storage Service Developer Guide.

Start Exporting Data​

To begin streaming Execution Server data into S3, use the data-warehouse service. This service reads the Ember Journal and uploads all trading messages and completed orders into an Amazon S3 bucket in JSON format.

note

Active orders are not exported until they are complete (completely filled, cancelled, or rejected). However, messages concerning active orders are exported immediately.

Run Ember's data-warehouse script with single argument that specifies the S3 data warehouse:

export EMBER_HOME=/deltix/emberhome
/deltix/ember/bin/data-warehouse **s3**

Journal term​

On special occasions, clients may delete the Ember Journal. When this happens, the entire trading history on which Ember OMS operates, including message sequence numbers, resets. This presents a problem for existing data warehouse pipelines, where the sequence numbers of stored messages might seem to emerge from the future. To remedy this situation, Ember introduces the concept of a journal term, which increases each time the journal is created.

S3 data warehouse directory structure uses journal term to organize stored data:

S3 Data warehouse wizard 0

Types of stored data​

Ember S3 Data Warehouse can be configured to store:

  • Messages: Encompasses all trading flow messages, including trading requests (order submissions, cancellations, modifications) and trading events (order ACKs/NACKs, cancellation ACKs/NACKs, fills, etc.). You can configure a filter to reduce this data to trade events only (fills).

  • Orders: Records the final state of each order that Ember processed.

Batches & Objects​

The utility collects messages and orders into a batch according to the batchLimit and batchPeriod values specified in ember.conf. As soon as the number of records collected reaches the number specified by batchLimit, or the time spanned by collected records reaches batchPeriod, the utility uploads them to S3 and stores them in an S3 object.

The S3 objects are created under keys in this format, where records_type is messages or orders:

<records_type>/term=<journal_id>/date=<date>/hour=<hour>/<date>_<time>_<sequence>.json.gz

date, time and sequence refer to the timestamp and sequence number of the last message or order included in the S3 object. For example:

messages/term=1672826718396/date=2023-11-30/hour=15/2023-11-30_15-03-57_3258881654.json.gz
note

File and folder names use UTC time zone.

The following screenshot shows a sample of data warehouse directory layout in AWS Console:

S3 Data warehouse wizard 1

In batch mode, the data-warehouse utility exits as soon as all recent data has been exported. If you re-run the script, it uploads only new data that the Execution Server accumulated since the last export.

JSON Samples​

Here are some sample JSON records in uploaded S3 objects.

JSON Sample of MESSAGE:

{"Term":1568250585001,"Type":"OrderTradeReportEvent","Sequence":576312,"Timestamp":"2019-09-12 09:48:44.472","SourceId":"SIMULATOR","DestinationId":"SOR","OrderId":"1568250646326","OriginalOrderId":null,"CorrelationOrderId":"1568250646326","ParentSourceId":"TUSER1","ParentOrderId":"1dkhg9sl7:31131","RequestId":null,"ExternalOrderId":null,"EventId":"BA813ECD-A757-41E8-8390-C01AF464279D","ReferenceEventId":null,"OrderStatus":"PARTIALLY_FILLED","Symbol":"BTC/USD","InstrumentType":"FX","Currency":null,"ExchangeId":"COINBASE","TraderId":"mtwain","Account":"Silver","ClearingAccount":null,"Side":"SELL","TimeInForce":"IMMEDIATE_OR_CANCEL","ExpireTime":null,"Quantity":70.0,"MinQuantity":null,"DisplayQuantity":null,"OrderType":"LIMIT","LimitPrice":1.34237,"StopPrice":null,"PegDifference":null,"AveragePrice":10128.39227872884,"CumulativeQuantity":55.28729508,"RemainingQuantity":14.71270492,"TradePrice":10127.53,"TradeQuantity":2.464,"Commission":null,"CommissionCurrency":null,"CounterPartySourceId":null,"CounterPartyOrderId":null,"SettlementDate":null,"TradeDate":null,"Reason":null,"VendorRejectCode":-2147483648,"DeltixRejectCode":-2147483648,"MultiLegReportingType":null,"AggressorSide":"ORDER_INITIATOR_IS_AGGRESSOR","OrderUnknown":false,"CancelType":null,"ExecRestatementReason":null,"Flags":-2147483648,"UserData":null,"Attributes":[{"Key":1115,"Value":"Quote"}]}

JSON Sample of ORDER:

{"Term":1568250585001,"OpenSequence":69826,"CloseSequence":69833,"SourceId":"SOR","DestinationId":"SIMULATOR","OrderId":"1568250593156","ParentSourceId":"TUSER1","ParentOrderId":"1dkhg9sl7:3595","ExternalOrderId":null,"Account":"Gold","ClearingAccount":null,"TraderId":"slem","Symbol":"ETH/BTC","InstrumentType":"FX","ExchangeId":"POLONIEX","Currency":"ETH","Side":"SELL","TimeInForce":"IMMEDIATE_OR_CANCEL","ExpireTime":null,"OrderStatus":"CANCELED","OpenTime":"2019-09-12 02:09:45.503","CloseTime":"2019-09-12 02:09:45.511","OrderType":"LIMIT","LimitPrice":1.37123,"StopPrice":null,"Quantity":8.0,"DisplayQuantity":null,"MinQuantity":null,"CumulativeQuantity":0.0,"AveragePrice":0.0,"Reason":null,"VendorRejectCode":-2147483648,"DeltixRejectCode":-2147483648,"UserData":null,"Attributes":null}

Recovering data from warehouse​

We provide a Python sample that demonstrates how extract trades that fall in given time range from S3 data warehouse into simple CSV file.

Alternatively you can use Journal Importer tool to recover journal from S3 data warehouse.

Athena Queries​

Amazon Athena is a service that enables a data analyst to perform interactive queries in the Amazon Web Services public cloud on data stored in Amazon S3.

To run Athena queries on the message and order data you exported to S3 and create your database and tables, follow the steps in the Athena User Guide. 

Create Tables​

You can create Messages and Orders tables using JSON data exported to the deltix-ember-algotest-s3 bucket in the deltixdb database.

To create the tables:

  1. Copy the queries in the Messages Table Query and Orders Table Query sections to the Athena Query Editor tab.

  2. Update the database and bucket names in the S3 URL to match your database and bucket.

  3. Run the queries:

    CREATE DATABASE deltixdb

    S3 Athena table creation

  4. Run the following command to load the partitions:

    MSCK REPAIR TABLE deltixdb.messages
    MSCK REPAIR TABLE deltixdb.orders

The data is ready for querying.

You may need to periodically reload partitions to see newly added dates:

S3 Athena partitions

For more information on working with partitioned tables, see the Athena documentation.

Messages Table Query​

The following statement creates the Messages table:

CREATE EXTERNAL TABLE IF NOT EXISTS deltixdb.messages (
  `type` string,
  `sequence` bigint,
  `timestamp` timestamp,
  `sourceid` string,
  `destinationid` string,
  `orderid` string,
  `originalorderid` string,
  `correlationorderid` string,
  `parentsourceid` string,
  `parentorderid` string,
  `requestid` string,
  `externalorderid` string,
  `eventid` string,
  `referenceeventid` string,
  `orderstatus` string,
  `symbol` string,
  `instrumenttype` string,
  `currency` string,
  `exchangeid` string,
  `traderid` string,
  `account` string,
  `clearingaccount` string,
  `side` string,
  `timeinforce` string,
  `expiretime` timestamp,
  `quantity` double,
  `minquantity` double,
  `displayquantity` double,
  `ordertype` string,
  `limitprice` double,
  `stopprice` double,
  `pegdifference` double,
  `averageprice` double,
  `cumulativequantity` double,
  `remainingquantity` double,
  `tradeprice` double,
  `tradequantity` double,
  `commission` double,
  `commissioncurrency` string,
  `counterpartysourceid` string,
  `counterpartyorderid` string,
  `settlementdate` timestamp,
  `tradedate` timestamp,
  `reason` string,
  `vendorrejectcode` int,
  `deltixrejectcode` int,
  `multilegreportingtype` string,
  `aggressorside` string,
  `orderunknown` boolean,
  `canceltype` string,
  `execrestatementreason` string,
  `flags` int,
  `userdata` string,
  `attributes` array<struct<key:int, value:string>> 
)
PARTITIONED BY (
  `term` bigint,
  `date` date,
  `hour` tinyint
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://deltix-ember-algotest-s3/messages/'
TBLPROPERTIES ('has_encrypted_data'='false');

Orders Table Query​

The following statement creates the Orders table:

CREATE EXTERNAL TABLE IF NOT EXISTS deltixdb.orders (
  `opensequence` bigint,
  `closesequence` bigint,
  `sourceid` string,
  `destinationid` string,
  `orderid` string,
  `parentsourceid` string,
  `parentorderid` string,
  `externalorderid` string,
  `account` string,
  `clearingaccount` string,
  `traderid` string,
  `symbol` string,
  `instrumenttype` string,
  `exchangeid` string,
  `currency` string,
  `side` string,
  `timeinforce` string,
  `expiretime` timestamp,
  `orderstatus` string,
  `opentime` timestamp,
  `closetime` timestamp,
  `ordertype` string,
  `limitprice` double,
  `stopprice` double,
  `quantity` double,
  `displayquantity` double,
  `minquantity` double,
  `cumulativequantity` double,
  `averageprice` double,
  `reason` string,
  `vendorrejectcode` int,
  `deltixrejectcode` int,
  `userdata` string,
  `attributes` array<struct<key:int, value:string>> 
)
PARTITIONED BY (
  `term` bigint,
  `date` date,
  `hour` tinyint
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://deltix-ember-algotest-s3/orders/'
TBLPROPERTIES ('has_encrypted_data'='false');

Query Examples​

Total count of filled orders:

select count(*) as "# filled orders" 
from orders 
where CumulativeQuantity > 0

Total count of filled orders for 3 days grouped by order source:

select date, sourceId, count(*) as "# filled orders" 
from orders 
where date between date('2022-11-07') and date('2022-11-09')
and CumulativeQuantity > 0
group by date, sourceId 
order by date

Count of matches: Here we count all trade event messages issued by the matching engine and divide results by 2 since every match emits two trade events.

select date, count(*)/2 as "# matches" 
from messages 
where date between date('2022-09-04') and date('2022-09-10')
and type = 'OrderTradeReportEvent'
group by date
order by date
note

Where possible, use partition keys like date as the first condition of your WHERE query. This limits the amount of data Athena has to scan and saves you money.

The cumulative size of buy orders for BTC/USD:

select sum(CumulativeQuantity) 
from orders 
where side = 'BUY' and symbol='BTC/USD'

Show what kind of order each source sends to the KRAKEN exchange:

select distinct SourceId, OrderType 
from orders 
where ExchangeId = 'KRAKEN'

Show the last 100 orders with their submission timestamp in America/New_York timezone:

select SourceId, DestinationId, OrderId, OpenTime AT TIME ZONE 'America/New_York' as OpenTime from orders
order by OpenTime desc 
limit 100

Show the last 100 trades:

select * 
from messages 
where Type ='OrderTradeReportEvent' 
order by timestamp desc
limit 100

Please share your queries with us!

Adding a QuickSight Dashboard​

To add a dashboard for the Orders table, follow these steps:

  1. Open the QuickSight Console. If you are accessing it for the first time, a basic subscription will do.
  2. Define a dataset:
    1. Click Datasets > New.
    2. For the dataset type, click Athena > Next.
    3. Provide a name for the dataset (for example: "orders-prod") and click Next.
    4. Select the Athena database you created
    5. Select the Orders table > Next.
    6. Select Directly query your data > Visualize.
      QuickSight brings you to the dashboard editor.
  3. Edit the dashboard:
    1. Switch the dashboard Visual type to Line chart:
      S3 Athena chart types
    2. For the X axis, choose date.
    3. For the Y axis, choose count(orderId).
    4. For Color, specify the order sourceId.
      This displays the order count by order source on any given date:
      S3 Athena query

Athena Views​

In some cases, it may be helpful to prepare data analytics.

For example, it may be useful to chart order value in Amazon QuickSight. In such cases Athena Views may be very helpful:

CREATE OR REPLACE VIEW orders_view AS
SELECT *, averagePrice*cumulativeQuantity as totalValue
FROM orders
WHERE cumulativeQuantity > 0;

Performance​

The following performance was observed on a standard (fastest) S3 storage class using the compressed JSON format.

Upload Performance​

An Ember Stress test was used to generate a realistic sample of trading activity (matching engine).

The S3 data warehouse pipeline peaked at around 15000 orders/second, which produced around 45000 trading messages/second.

Query Performance​

Test dataset:

  • 10 GB of compressed JSON in Orders (roughly 300 million orders).
  • 30 GB of compressed JSON in Messages (roughly 850 million messages).

A typical query of the Orders table took 2 minutes 15 seconds, while a query of the Messages table took about 11 minutes.

Partitioning data into sub-folders according to typical use cases may drastically improve these query times.

Deltix is collecting client feedback. Please let us know what are your load patterns.

Message Identity​

You can identify records in Orders and Messages using the composite key {Term, Sequence}.

  • Sequence comes from the upstream Ember Order Management System (OMS).
    • Each message processed by the OMS is assigned a unique sequence number.
    • Not all messages are stored into the data warehouse. For example, some internal system control messages are skipped. Hence, you may see gaps in sequence numbers which otherwise increase monotonously.
    • System operators may periodically clear Ember’s journal. This action restarts message sequence numbers (and increases Term). When this happens, new messages start at a different term.
  • Term identifies journal creation time.
    • Term remains the same for all messages written since Ember journal creation.
    • Each time the journal is cleared and re-created, the value specified as Term increases.

Example​

TermSequenceData
15463008001OrderNewRequest
15463008003OrderNewEvent
.........
1546300800413891The last message before journal is reset
Operator resets Ember’s journal (will result in new term and message sequence reset)
15619392001OrderCancelRequest
15619392002OrderCancelRequest
.........
note

You can rely on the fact that {Term, Sequence} are always increasing to implement the before-after ordering of messages.

The following Amazon IAM permission can be given to the IAM user (if you use API keys) or role to access S3 bucket deltix-ember-algotest-s3 used in the examples of this document:

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Effect":"Allow",
         "Action":[
            "s3:ListBucket",
            "s3:GetBucketLocation"
         ],
         "Resource":"arn:aws:s3:::deltix-ember-algotest-s3"
      },
      {
         "Effect":"Allow",
         "Action":[
            "s3:PutObject",
            "s3:PutObjectAcl",
            "s3:GetObject",
            "s3:GetObjectAcl",
            "s3:DeleteObject"
         ],
         "Resource":"arn:aws:s3:::deltix-ember-algotest-s3/*"
      }
   ]
}

Appendix A: Data Format​

This section describes the format of the two tables used to warehouse Ember trading history.

Orders Table​

The Orders table captures the final state of each order.

ColumnExampleDescription
Term1546300800Identifies sequence term. See “Message Identity” section above. Since: Ember 1.4
OpenSequence312321304Identifies sequence sequence number of the message that created the order (usually OrderNewRequest).  See “Message Identity” section above.
CloseSequence312321312Identifies sequence sequence number of the message that completed (closed) the order (usually OrderCancelEvent, OrderRejectEvent, OrderTradeReportEvent, etc.).  See “Message Identity” section above.
SourceIdCLIENT52Order source, ALPHANUMERIC(10)
DestinationIdTWAPOrder destination, ALPHANUMERIC(10)
OrderIdICAP1983EE23Identifies each order for Execution Server, unique per-source. OrderID is assigned by order source.
ParentSourceIdCONTROLIdentifies source of parent order (optional)
ParentOrderIdICAP321XX1Identifies parent order (optional)
ExternalOrderIdZZ132131Optional order identifier assigned to the order by execution venue. For example, if we send order to execution venue, like CME they assign their own order identifier. This identifier can be subsequently used to locate this order on CME.
AccountGoldIdentifies order account
TraderIdjdoeIdentifies trader who submitted this order
SymbolEUR/USDOrder symbol (in symbology configured inside Deltix system)
InstrumentTypeFXInstrument type
ExchangeIdHOTSPOTDestination exchange (if available)
CurrencyUSDOrder currency. Optional. Usually used only for orders that use term currency (rather than base currency).
CurrencyCode840Same as Currency field but represented as ISO currency code
SideBUYOrder side
TimeInForceDAYOrder time In force
ExpireTime2019-02-27 17:00:00.000Order expiration time (only for GOOD_TILL_DATE orders)
OrderStatusCANCELLEDFinal state of the order
OpenTime2019-02-27 16:51:48.002Order submission time
CloseTime2019-02-27 16:51:48.120Order completion time
OrderTypePEG_TO_MIDPOINTOrder type
LimitPrice1.33Limit price. Can be specified for LIMIT, STOP_LIMIT, PEGGED, or CUSTOM order types
StopPrice1.20Stop price. Can be specified for STOP and STOP_LIMIT order types.
Quantity10000Order quantity
DisplayQuantity1000Order display quantity (some time described as “max show quantity” or “max floor quantity”), where applicable.
MinQuantity1000Minimum fill quantity (where applicable).
CumulativeQuantity1500.50Cumulative filled quantity
AveragePrice132.56Average fill price
Text“Cancelled by user request”For cancelled or rejected orders this field contains textual reason.
VendorRejectCode1003Vendor specific reject code. For example CME’s.
DeltixRejectCode120Reject code in Deltix classification
UserDataAlgo123User-defined cookie provided at order submission time.

Messages Table​

The Messages table records all order-related activity in real time.

More specifically, this table records order requests (original submission, cancellation, and order modification requests) and order events (for example, order acknowledgement, cancellation confirmation, or traders).

To get a better understanding of trading workflows in the Execution Server, refer to the Trading Data Model document.

ColumnExampleDescription
TypeOrderTradeReportEventIdentities type of message. See Trading Data Model for list of event types.
Term1546300800Identifies sequence term. See “Message Identity” section above. Since: Ember 1.4
Sequence312321312Unique number that represents ES message sequence, can be used as unique synthetic timestamp. See “Message Identity” section above.
Timestamp2019-02-27 16:51:48.123Message timestamp
SourceIdCLIENT52Order source, ALPHANUMERIC(10)
DestinationIdTWAPOrder destination, ALPHANUMERIC(10)
OrderIdICAP1983EE23Identifies order for Execution Server, unique per-source.
OriginalOrderIdICAP1983EE22For order replacement request, as well as events that relate to cancel replace workflow (such as PendingReplace, ReplaceReject, and Replace ACK) this field identifies original order in cancel-replace chain.
CorrelationOrderIdICAP1983EE00Identity of the first order in cancel-replace chain. Same as OrderID for orders that do not (yet) participate in cancel-replace workflow.
ParentSourceIdCONTROLIdentifies source of parent order (optional)
ParentOrderIdICAP321XX1Identifies parent order (optional)
RequestIdXCL#554For order cancel request, as well as cancel ACK and cancel NACK events this field identifies specific cancel request.
ExternalOrderIdZZ132131Optional order identifier assigned to the order by execution venue
EventIdAAAT31231Optional attribute available for events coming from some venues. Allow identifying duplicate events. May have different uniqueness scope, but must be unique at least in context of single order. NOTE: OMS is responsible for filtering out duplicate events before they reach data warehouse or other downstream consumers.
ReferenceEventIdUsed by trade correction and cancellation events to identify previously communicated event that has to be corrected or cancelled.
OrderStatusPARTIALLY_FILLEDOrder status (available for order events only).
SymbolEUR/USDOrder symbol (in symbology configured inside Deltix system)
InstrumentTypeFXInstrument type
CurrencyUSDOrder currency. Optional. Usually used only for orders that use term currency (rather than base currency).
ExchangeIdHOTSPOTDestination exchange (if available) for outbound messages and source exchange for inbound messages. For example, fills will report their exchange in this field.
AccountGoldIdentifies order account
TraderIdjdoeIdentifies trader who submitted this order
SideBUYOrder side
TimeInForceGOOD_TILL_CANCELOrder time in force condition
ExpireTime2019-02-27 17:00:00.000Order expiration time (only for GOOD_TILL_DATE orders)
Quantity100.50Order quantity
MinQuantity10Minimum quantity to execute (optional order request attribute)
DisplayQuantity5Minimum quantity to display on exchange floor (optional order request attribute)
OrderTypeLIMITOrder Type
LimitPrice1.33Limit price. Can be specified for LIMIT, STOP_LIMIT, PEGGED, or CUSTOM order types
StopPrice1.20Stop price. Can be specified for STOP and STOP_LIMIT order types.
PegDifference0.03Peg offset, in order money. Optional attribute for PEGGED order types.
AveragePrice1.325Average execution price (order events only).
CumulativeQuantity25Cumulative executed quantity (order events only).
RemainingQuantity75.50Remaining order quantity (part of original order quantity that is still working on the market)
TradePrice1.321Trade events only: price of individual trade described by this event. Not to be confused with average price of all trade events reported so far for an order (AveragePrice field).
TradeQuantity5Trade events only: size of individual trade described by this event. Not to be confused with total executed size reported so far by all trade events of an order (CumulativeQuantity field).
Commission0.0001Trade commission (when known)
CommissionCurrencyUSDTrade commission currency (when known, by default assume order currency)
CounterPartySourceIdCLIENT17Identifies source of  other side of the trade (when reported)
CounterPartyOrderIdFKA12312312Identifies order that took the other side of the trade (when reported).
SettlementDateTrade settlement date (when reported)
TradeDateTrade date (when reported)
ReasonMarket is closedReason communicated for cancel or reject events.
VendorRejectCode1003Vendor specific reject code. For example CME’s.
DeltixRejectCode120Reject code in Deltix classification
MultiLegReportingTypeUsed for trade reports when order instrument is exchange traded-synthetic. Identifies single-leg trade or whole contract trade of multi-legged security.
AggressorSideReports our side as passive or aggressive role in this trade.
OrderUnknownFlag used by order Cancel Reject events.
CancelTypeEnum used by Cancel events.
ExecRestatementReasonUsed by Order Restate Events to classify restate type
Flags3Order flags. Bitmask containing various order flags. For example, bit 0 marks manual order.
UserDataFoo152User-provided order tag
\=> Attributes.Key6201Custom order attribute key (numeric, corresponds to custom FIX tags specified during order submission)
\=> Attributes.ValueQuantXCustom order attribute value (text, corresponds to custom FIX tags specified during order submission)

Appendix B: Athena Table Column Data Types​

Orders Table​

ColumnType
TypeString ('OrderNewRequest', 'OrderReplaceRequest', 'OrderCancelRequest', 'OrderStatusRequest', 'OrderDiscardRequest', 'OrderPendingNewEvent', 'OrderNewEvent', 'OrderRejectEvent', 'OrderPendingCancelEvent', 'OrderCancelEvent', 'OrderCancelRejectEvent', 'OrderPendingReplaceEvent', 'OrderReplaceEvent', 'OrderReplaceRejectEvent', 'OrderTradeReportEvent', 'OrderTradeCancelEvent', 'OrderTradeCorrectEvent', 'OrderStatusEvent', 'OrderRestateEvent')
SequenceBigint
TimestampTimestamp
SourceIdString
DestinationIdString
OrderIdString
OriginalOrderIdString
CorrelationOrderIdString
ParentSourceIdString
ParentOrderIdString
RequestIdString
ExternalOrderIdString
EventIdString
ReferenceEventIdString
OrderStatusString ('PENDING_NEW', 'NEW', 'REJECTED', 'PENDING_CANCEL, 'CANCELED', 'PENDING_REPLACE', 'REPLACED', 'PARTIALLY_FILLED', 'COMPLETELY_FILLED', 'EXPIRED', 'SUSPENDED')
SymbolString
InstrumentTypeString ('EQUITY', 'ETF', 'BOND', 'INDEX', 'FX', 'OPTION', 'FUTURE', 'SYNTHETIC', 'CUSTOM')
CurrencyString
ExchangeIdString
TraderIdString
AccountString
SideString ('BUY', 'SELL', 'SELL_SHORT', 'SELL_SHORT_EXEMPT')
TimeInForceString('DAY', 'GOOD_TILL_CANCEL', 'AT_THE_OPENING', 'IMMEDIATE_OR_CANCEL', 'FILL_OR_KILL', 'GOOD_TILL_CROSSING', 'GOOD_TILL_DATE', 'AT_THE_CLOSE')
ExpireTimeTimestamp
QuantityDouble
MinQuantityDouble
DisplayQuantityDouble
OrderTypeString ('CUSTOM', 'MARKET', 'LIMIT', 'STOP', 'STOP_LIMIT', 'PEG_TO_MARKET', 'PEG_TO_MIDPOINT', 'PEG_TO_PRIMARY', 'MARKET_ON_CLOSE', 'LIMIT_ON_CLOSE', 'LIMIT_OR_BETTER', 'PREVIOUSLY_QUOTED')
LimitPriceDouble
StopPriceDouble
PegDifferenceDouble
AveragePriceDouble
CumulativeQuantityDouble
RemainingQuantityDouble
TradePriceDouble
TradeQuantityDouble
CommissionDouble
CommissionCurrencyString
CounterpartyIdString
SettlementDateTimestamp
TradeDateTimestamp
ReasonString
VendorRejectCodeInt
DeltixRejectCodeInt
MultiLegReportingTypeString ('SINGLE_SECURITY', 'INDIVIDUAL_LEG_SECURITY', 'MULTI_LEG_SECURITY')
AggressorSideString ('ORDER_INITIATOR_IS_PASSIVE', 'ORDER_INITIATOR_IS_AGGRESSOR')
OrderUnknownBoolean ('false', 'true')
CancelTypeString ('GENERAL', 'DONE_FOR_DAY', 'EXPIRED')
ExecRestatementReasonString ('GT_RENEWAL_RESTATEMENT', 'VERBAL_CHANGE', 'REPRICING_OF_ORDER', 'BROKER_OPTION', 'PARTIAL_DECLINE_OF_ORDER_QTY', 'CANCEL_ON_TRADING_HALT', 'CANCEL_ON_SYSTEM_FAILURE', 'MARKET_OR_EXCHANGE_OPTION', 'CANCELED_NOT_EST' , 'WAREHOUSE_RECAP', 'OTHER')
FlagsInt
UserDataString
AttributesArray<Struct<Key: Int, Value: String>>

Messages Table​

ColumnType
TypeString ('OrderNewRequest', 'OrderReplaceRequest', 'OrderCancelRequest', 'OrderStatusRequest', 'OrderDiscardRequest', 'OrderPendingNewEvent', 'OrderNewEvent', 'OrderRejectEvent', 'OrderPendingCancelEvent', 'OrderCancelEvent', 'OrderCancelRejectEvent', 'OrderPendingReplaceEvent', 'OrderReplaceEvent', 'OrderReplaceRejectEvent', 'OrderTradeReportEvent', 'OrderTradeCancelEvent', 'OrderTradeCorrectEvent', 'OrderStatusEvent', 'OrderRestateEvent')
SequenceBigint
TimestampTimestamp
SourceIdString
DestinationIdString
OrderIdString
OriginalOrderIdString
CorrelationOrderIdString
ParentSourceIdString
ParentOrderIdString
RequestIdString
ExternalOrderIdString
EventIdString
ReferenceEventIdString
OrderStatusString ('PENDING_NEW', 'NEW', 'REJECTED', 'PENDING_CANCEL, 'CANCELED', 'PENDING_REPLACE', 'REPLACED', 'PARTIALLY_FILLED', 'COMPLETELY_FILLED', 'EXPIRED', 'SUSPENDED')
SymbolString
InstrumentTypeString ('EQUITY', 'ETF', 'BOND', 'INDEX', 'FX', 'OPTION', 'FUTURE', 'SYNTHETIC', 'CUSTOM')
CurrencyString
ExchangeIdString
TraderIdString
AccountString
SideString ('BUY', 'SELL', 'SELL_SHORT', 'SELL_SHORT_EXEMPT')
TimeInForceString('DAY', 'GOOD_TILL_CANCEL', 'AT_THE_OPENING', 'IMMEDIATE_OR_CANCEL', 'FILL_OR_KILL', 'GOOD_TILL_CROSSING', 'GOOD_TILL_DATE', 'AT_THE_CLOSE')
ExpireTimeTimestamp
QuantityDouble
MinQuantityDouble
DisplayQuantityDouble
OrderTypeString ('CUSTOM', 'MARKET', 'LIMIT', 'STOP', 'STOP_LIMIT', 'PEG_TO_MARKET', 'PEG_TO_MIDPOINT', 'PEG_TO_PRIMARY', 'MARKET_ON_CLOSE', 'LIMIT_ON_CLOSE', 'LIMIT_OR_BETTER', 'PREVIOUSLY_QUOTED')
LimitPriceDouble
StopPriceDouble
PegDifferenceDouble
AveragePriceDouble
CumulativeQuantityDouble
RemainingQuantityDouble
TradePriceDouble
TradeQuantityDouble
CommissionDouble
CommissionCurrencyString
CounterpartyIdString
SettlementDateTimestamp
TradeDateTimestamp
ReasonString
VendorRejectCodeInt
DeltixRejectCodeInt
MultiLegReportingTypeString ('SINGLE_SECURITY', 'INDIVIDUAL_LEG_SECURITY', 'MULTI_LEG_SECURITY')
AggressorSideString ('ORDER_INITIATOR_IS_PASSIVE', 'ORDER_INITIATOR_IS_AGGRESSOR')
OrderUnknownBoolean ('false', 'true')
CancelTypeString ('GENERAL', 'DONE_FOR_DAY', 'EXPIRED')
ExecRestatementReasonString ('GT_RENEWAL_RESTATEMENT', 'VERBAL_CHANGE', 'REPRICING_OF_ORDER', 'BROKER_OPTION', 'PARTIAL_DECLINE_OF_ORDER_QTY', 'CANCEL_ON_TRADING_HALT', 'CANCEL_ON_SYSTEM_FAILURE', 'MARKET_OR_EXCHANGE_OPTION', 'CANCELED_NOT_EST' , 'WAREHOUSE_RECAP', 'OTHER')
FlagsInt
UserDataString
AttributesArray<Struct<Key: Int, Value: String>>