Table-to-Table Transactions

Run transaction operations directly from one database table to another, without staging through S3.

Supported Operations

Any operation that accepts a locationUri can use a table URI instead of an S3 path:

UPSERT, PATCH, DELETE, INSERT_IGNORE, INSERT_RECORD, MERGE, ROLLUP

Each behaves the same as its S3-based counterpart (see Transactions).

Source Table URI Formats

Snowflake:

snowflake://DATABASE.SCHEMA.TABLE
snowflake://SCHEMA.TABLE              # uses current database

Aurora / PostgreSQL:

aurora://schema.table
aurora://table                        # uses default schema
postgres://schema.table               # alias for aurora://

Running Manually

The transaction API Step Function is named:

sdp-<environmentId>-transactions-api

Find it in the AWS Step Functions console and start an execution with:

{
  "dataset": {
    "id": "<dataset-id-or-alias>"
  },
  "actions": [
    {
      "operation": "UPSERT",
      "locationUri": "snowflake://MY_DB.STAGING.RAW_ORDERS",
      "target": "orders.Order"
    }
  ]
}

The dataset.id field accepts either the dataset ID or its alias.

Do not include format — it is only needed for S3-based operations.

Column Matching

Columns are matched by name. The target schema's properties are translated using the dataset's SQL naming strategy (e.g. UPPER_SNAKE for Snowflake: customerId → CUSTOMER_ID).

Extra source columns are ignored. Missing source columns will cause an error unless the target property has a column expression.

Use columnMapping if source column names differ:

{
  "columnMapping": {
    "customerId": "CUST_ID",
    "totalPrice": "PRICE_TOTAL"
  }
}

Troubleshooting

Error	Fix
Expected snowflake:// URI	Use snowflake://, aurora://, or postgres://
URI must specify table path	Use format snowflake://[database.]schema.table
Column not found in source	Add missing column, use columnMapping, or add a column expression
Format is required when locationUri is used	Remove format (not needed for table URIs) or fix to a table URI
Duplicate ID error	Deduplicate source data
Null ID error	Populate id values or use a target with an id expression/sequence