Cross-Location transactions

Depot supports, in a limited way, transactions that span across multiple technologically separate locations.

These are the currently supported cross-location transactions geometries:

You can exchange data between Snowflake and Aurora (Postgres) locations, and between Aurora locations hosted on different cluster.

note

You may also exchange data between Snowflake locations hosted on different accounts, although it should be far more efficient to use Snowflake native facilities to make entities from one account be visible in the other account.

To perform a cross-location transaction between supported location types, you simply need to perform a cross-dataset transaction, specifying both the (target) dataset and the source dataset Id for each transaction:

const replica1 = props.secondary.depot.runTransaction(this, 'Replica', {
actions: [
    {
      operation: TransactionRequest.Operation.MERGE,
      source: 'my.Source',
      target: 'my.Target',
      sourceDatasetId: props.primary.datasetId, // we copy from here ...
    },
  ],
  datasetId: props.secondary.datasetId,  // ... to here
  resultPath: sfn.JsonPath.DISCARD,
});

all regular operations (MERGE, UPSERT, INSERT_IGNORE, DELETE, PATCH) are supported provided that the target database supports them.

note

Internally, a cross-location engine may be (is) expanded into a sequence of an export transaction followed by an import transaction with the specified operation. It uses a format that is a common denominator between what the source and target locations can use.

Depot is free to choose any format that suits the purpose; at this moment however, this happens to always be JSON Lines (JSONL).

It is possible to perform multiple operations in a single transaction, provided that cross-location operations come frome the same source location (can span multiple datasets) and target the same dataset. It is permissible to also have operations that only operate in the target dataset (e.g. a Stored Procedure call if the target supports that) mixed together with the cross-location operations.

info

There are no transactional guarantees that span two separate database engines. The write to the target database is considered transactional within that database, but prior to that, the data extraction from the source database is not considered transactional with respect to the writes into the target database.