Table Source API Code Samples

Selection of code samples that demonstrate the use of the Table Source API. Copy and edit these code samples to suit your needs.

When using the code samples, remember to replace:

{vanity_name} with your tenant name
{api_key} with your API key
{security_token} with a valid security token

These code samples may not include all available parameters and request body fields for each endpoint. For the endpoint's full request schema, see "Table Source" in API Reference.

Retrieve table sources

Use this sample request to retrieve a list of all table sources in the tenant.

Copy

cURL sample request: Retrieve table sources

1
2
3
curl -X GET --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/tables' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}'

The response returns every table's UUID, basic information, and data columns.

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
{
    "tables": [
        {
            "uuid": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
            "objectName": "employee_custom_data",
            "basicInformation": {
                "displayName": "Employee Custom Data",
                "description": "Stores supplemental employee attributes not available in the core HR system."
            },
            "columns": [
                { "name": "employee_id",    "dataType": "STRING",  "isPrimaryTimestamp": false },
                { "name": "badge_number",   "dataType": "STRING",  "isPrimaryTimestamp": false },
                { "name": "office_floor",   "dataType": "INTEGER", "isPrimaryTimestamp": false },
                { "name": "effective_date", "dataType": "DATE",    "isPrimaryTimestamp": true  }
            ],
            "canChildrenInherit": false,
            "canChildrenInheritData": false
        }
    ]
}

Retrieve a table source

Use this sample request to retrieve a specific table source.

Copy

cURL sample request: Retrieve table source with ID a1b2c3d4-e5f6-7890-abcd-ef1234567890

1
2
3
curl -X GET --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/tables/a1b2c3d4-e5f6-7890-abcd-ef1234567890' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}'

The response returns the table's UUID, basic information, and data columns.

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
{
    "tables": [
        {
            "uuid": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
            "objectName": "employee_custom_data",
            "basicInformation": {
                "displayName": "Employee Custom Data",
                "description": "Stores supplemental employee attributes not available in the core HR system."
            },
            "columns": [
                { "name": "employee_id",    "dataType": "STRING",  "isPrimaryTimestamp": false },
                { "name": "badge_number",   "dataType": "STRING",  "isPrimaryTimestamp": false },
                { "name": "office_floor",   "dataType": "INTEGER", "isPrimaryTimestamp": false },
                { "name": "effective_date", "dataType": "DATE",    "isPrimaryTimestamp": true  }
            ],
            "canChildrenInherit": false,
            "canChildrenInheritData": false
        }
    ]
}

Retrieve a table source's metadata

Use this sample request to retrieve the current metadata for a table, such as the key-value pairs to apply to the table if you commit the transaction. To set a table's metadata, see Run a SQL job within a transaction.

Optionally, pass the transactionId query parameter to retrieve the metadata in an open transaction.

Copy

cURL sample request: Retrieve metadata for table source with ID a1b2c3d4-e5f6-7890-abcd-ef1234567890

1
2
3
curl -X GET --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/tables/a1b2c3d4-e5f6-7890-abcd-ef1234567890/metadata' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}'

The response returns the table's UUID, metadata, record count, and last updated time.

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
{
    "tableId": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    "transactionId": "",
    "metadata": {
        "version": "42",
        "source_system": "workday",
        "last_loaded_by": "integration_user"
    },
    "recordCount": 15243,
    "recordsUpdatedAt": "2026-02-14T08:30:00Z"
}

Create a table source

Use this sample request to create a new table source. Assign a unique objectName but do not provide a uuid. Visier generates UUIDs automatically for new objects.

Copy

cURL sample request: Create a table source

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/tables' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "objectName": "employee_custom_data",
    "basicInformation": {
        "displayName": "Employee Custom Data",
        "description": "Stores supplemental employee attributes not available in the core HR system."
    },
    "columns": [
        { "name": "employee_id",    "dataType": "STRING",  "isPrimaryTimestamp": false },
        { "name": "badge_number",   "dataType": "STRING",  "isPrimaryTimestamp": false },
        { "name": "office_floor",   "dataType": "INTEGER", "isPrimaryTimestamp": false },
        { "name": "effective_date", "dataType": "DATE",    "isPrimaryTimestamp": true  }
    ],
    "canChildrenInherit": false,
    "canChildrenInheritData": false
}'

The response returns the table's UUID, basic information, and data columns.

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
{
    "uuid": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    "objectName": "employee_custom_data",
    "basicInformation": {
        "displayName": "Employee Custom Data",
        "description": "Stores supplemental employee attributes not available in the core HR system."
    },
    "columns": [
        { "name": "employee_id",    "dataType": "STRING",  "isPrimaryTimestamp": false },
        { "name": "badge_number",   "dataType": "STRING",  "isPrimaryTimestamp": false },
        { "name": "office_floor",   "dataType": "INTEGER", "isPrimaryTimestamp": false },
        { "name": "effective_date", "dataType": "DATE",    "isPrimaryTimestamp": true  }
    ],
    "canChildrenInherit": false,
    "canChildrenInheritData": false
}

Update a table source

Use this sample request to update an existing table source, such as modifying the structure, columns, or metadata. In PUT requests, the definition in your API call replaces the prior definition entirely. You must provide the complete definition. If you omit values from the update request, those values are removed from the table source.

Copy

cURL sample request: Update table source with ID a1b2c3d4-e5f6-7890-abcd-ef1234567890

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
curl -X PUT --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/tables/a1b2c3d4-e5f6-7890-abcd-ef1234567890' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "objectName": "employee_custom_data",
    "basicInformation": {
        "displayName": "Employee Custom Data",
        "description": "Updated: Stores supplemental employee attributes and remote work status."
    },
    "columns": [
        { "name": "employee_id",    "dataType": "STRING",  "isPrimaryTimestamp": false },
        { "name": "badge_number",   "dataType": "STRING",  "isPrimaryTimestamp": false },
        { "name": "office_floor",   "dataType": "INTEGER", "isPrimaryTimestamp": false },
        { "name": "is_remote",      "dataType": "BOOLEAN", "isPrimaryTimestamp": false },
        { "name": "effective_date", "dataType": "DATE",    "isPrimaryTimestamp": true  }
    ],
    "canChildrenInherit": true,
    "canChildrenInheritData": false
}'

The response returns the table's UUID, basic information, and data columns.

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
{
    "uuid": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    "objectName": "employee_custom_data",
    "basicInformation": {
        "displayName": "Employee Custom Data",
        "description": "Updated: Stores supplemental employee attributes and remote work status."
    },
    "columns": [
        { "name": "employee_id",    "dataType": "STRING",  "isPrimaryTimestamp": false },
        { "name": "badge_number",   "dataType": "STRING",  "isPrimaryTimestamp": false },
        { "name": "office_floor",   "dataType": "INTEGER", "isPrimaryTimestamp": false },
        { "name": "is_remote",      "dataType": "BOOLEAN", "isPrimaryTimestamp": false },
        { "name": "effective_date", "dataType": "DATE",    "isPrimaryTimestamp": true  }
    ],
    "canChildrenInherit": true,
    "canChildrenInheritData": false
}

Delete a table source

Use this sample request to delete an existing table source. This removes the table source definition but does not delete the associated data. The next SQL job that runs will delete the data.

Copy

cURL sample request: Delete table source with ID a1b2c3d4-e5f6-7890-abcd-ef1234567890

1
2
3
curl -X DELETE --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/tables/a1b2c3d4-e5f6-7890-abcd-ef1234567890' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}'

The response returns a 200 OK response.

Start a transaction

Use this sample request to start a transaction for SQL operations on a table source. Specify tablesToModify to lock tables for writing during the transaction. Omit tablesToModify to make the transaction read-only.

Copy

cURL sample request: Start a writing transaction for the table employee_custom_data

1
2
3
4
5
6
7
8
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "expireAfterSeconds": 3600,
    "tablesToModify": ["employee_custom_data"]
}'

The response returns the transaction's ID, status, creation information, expiration, and tables to modify.

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
{
    "transactionId": "9f8e7d6c-5b4a-3210-fedc-ba9876543210",
    "status": "OPEN",
    "createdAt": "2026-02-14T08:00:00Z",
    "createdBy": "integration_user@example.com",
    "expireAt": "2026-02-14T09:00:00Z",
    "startedAt": "",
    "closedAt": "",
    "tablesToModify": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
}

Retrieve a transaction's status

Use this sample request to retrieve the current status and details of a specific transaction. If the transaction was dispatched to analytic tenants using the tenants field in a SQL job, the response returns a children array showing the latest state of each tenant's transaction, including when work began and when the child transaction closed.

Copy

cURL sample request: Retrieve the status of transaction with ID 9f8e7d6c-5b4a-3210-fedc-ba9876543210

1
2
3
curl -X GET --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}'

The response returns the transaction's UUID, status, creation information, expiration, and tables to modify.

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
{
    "transactionId": "9f8e7d6c-5b4a-3210-fedc-ba9876543210",
    "status": "OPEN",
    "createdAt": "2026-02-14T08:00:00Z",
    "createdBy": "integration_user@example.com",
    "expireAt": "2026-02-14T09:00:00Z",
    "startedAt": "2026-02-14T08:00:05Z",
    "closedAt": "",
    "tablesToModify": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
}

The following sample response shows a committed transaction where analytic tenant jobs completed. The children array reports the status, start time, and close time for each tenant's transaction.

View sample response: Dispatched transaction with child statuses

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
{
    "transactionId": "9f8e7d6c-5b4a-3210-fedc-ba9876543210",
    "status": "COMMITTED",
    "createdAt": "2026-02-14T08:00:00Z",
    "createdBy": "integration_user@example.com",
    "expireAt": "2026-02-14T09:00:00Z",
    "startedAt": "2026-02-14T08:00:05Z",
    "closedAt": "2026-02-14T08:05:30Z",
    "tablesToModify": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"],
    "summary": {
      "open": 0,
      "resolving": 0,
      "committed": 3,
      "rolled_back": 0
    },
    "children": [
        {
            "tenantCode": "WFF_j1r~c1o",
            "status": "COMMITTED",
            "startedAt": "2026-02-14T08:00:10Z",
            "closedAt": "2026-02-14T08:04:55Z"
        },
        {
            "tenantCode": "WFF_j1r~c2o",
            "status": "COMMITTED",
            "startedAt": "2026-02-14T08:00:11Z",
            "closedAt": "2026-02-14T08:05:22Z"
        }
    ]
}

Upload an input file to a transaction

Use this sample request to send files to a transaction. SQL jobs reference an input file as a local, temporary view. For example, the input file can contain data to insert into your sources, IDs to match against to change or delete rows, or any other input for your SQL query.

Copy

cURL sample request: Upload input file new_employees.csv to transaction with ID 9f8e7d6c-5b4a-3210-fedc-ba9876543210

1
2
3
4
curl -X PUT --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/inputs/new_employees.csv' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-F 'file=@new_employees.csv'

The response returns a 200 OK response.

Extend a transaction's expiry

This sample request extends the expiry time of an open transaction. Use this endpoint when a long-running operation may outlast the expireAfterSeconds value set when the transaction was started. The new expiry is calculated from the time the extend request is received, not from the original expiry.

Copy

cURL sample request: Extend transaction 9f8e7d6c-5b4a-3210-fedc-ba9876543210 by one hour

1
2
3
4
5
6
7
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/extend' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "expireAfterSeconds": 3600
}'

The response returns the transaction's UUID, status, creation information, expiration, and tables to modify.

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
{
    "transactionId": "9f8e7d6c-5b4a-3210-fedc-ba9876543210",
    "status": "OPEN",
    "createdAt": "2026-02-14T08:00:00Z",
    "createdBy": "integration_user@example.com",
    "expireAt": "2026-02-14T10:00:00Z",
    "startedAt": "2026-02-14T08:00:05Z",
    "closedAt": "",
    "tablesToModify": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
}

Run a SQL job within a transaction

Jobs contain one or more ordered actions. Each action type targets a different SQL operation. All actions run within the same transaction and you can combine them for more complex operations.

query

This sample request runs a query action that executes a SQL SELECT statement. Specifying output writes the results to a file. You can download the output file after the job completes.

Copy

cURL sample request: Run a SQL job with query action for transaction 9f8e7d6c-5b4a-3210-fedc-ba9876543210 and write output to query_results.csv

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "query": {
                "query": "SELECT employee_id, badge_number, office_floor FROM employee_custom_data WHERE effective_date >= '\''2026-01-01'\''",
                "timeoutSeconds": 120,
                "output": {
                    "filename": "query_results.csv",
                    "format": { "csv": { "header": true } }
                }
            }
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "11223344-aabb-ccdd-eeff-001122334455",
    "tableAccess": {
        "read": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"],
        "write": []
    }
}

Optionally, you can roll back the transaction if the query returns no rows, as shown in the following rollbackOnEmpty sample. This is useful for guarding a data load against unexpected conditions, such as verifying that a staging table contains rows before committing them.

Copy

cURL sample request: Roll back the transaction if the query returns no rows

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "query": {
                "query": "SELECT 1 FROM new_employees LIMIT 1",
                "timeoutSeconds": 30,
                "rollbackOnEmpty": true
            }
        },
        {
            "upsert": {
                "source": "new_employees",
                "target": "employee_custom_data",
                "keys": ["employee_id", "effective_date"]
            }
        },
        {
            "commit": {}
        }
    ],
    "inputs": [
        {
            "id": "new_employees",
            "filename": "new_employees.csv",
            "format": { "csv": { "header": true } }
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "99aabbcc-ddeeff00-1122-3344-556677889900",
    "tableAccess": {
        "read": ["new_employees"],
        "write": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
    }
}

upsert

This sample request runs an upsert action and a commit action. The upsert action:

Replaces target rows with source rows that match on the specified keys.
Inserts source rows into target if there is no matching key.

After updating and inserting rows, the SQL job commits the changes to the source data and ends the transaction.

Copy

cURL sample request: Upsert rows from input view new_employees into employee_custom_data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "upsert": {
                "source": "new_employees",
                "target": "employee_custom_data",
                "keys": ["employee_id", "effective_date"]
            }
        },
        {
            "commit": {}
        }
    ],
    "inputs": [
        {
            "id": "new_employees",
            "filename": "new_employees.csv",
            "format": { "csv": { "header": true } }
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "55667788-aabb-ccdd-eeff-112233445566",
    "tableAccess": {
        "read": ["new_employees"],
        "write": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
    }
}

replace

This sample request runs a replace action and a commit action. The replace action:

Replaces rows in target with all rows in source.
Keeps rows that exist in both source and target.

After replacing rows, the SQL job commits the changes to the source data and ends the transaction.

Copy

cURL sample request: Replace all rows in employee_custom_data from input view full_snapshot

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "replace": {
                "source": "full_snapshot",
                "target": "employee_custom_data"
            }
        },
        {
            "commit": {}
        }
    ],
    "inputs": [
        {
            "id": "full_snapshot",
            "filename": "full_snapshot.csv",
            "format": { "csv": { "header": true } }
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "aabbccdd-1122-3344-5566-778899aabbcc",
    "tableAccess": {
        "read": ["full_snapshot"],
        "write": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
    }
}

reset

This sample request runs a reset action and a commit action. The reset action rolls a table back to its latest snapshot as of the specified timestamp. This undoes changes made after the specified point in time. After resetting the table, the SQL job commits the changes to the source data and ends the transaction.

Copy

cURL sample request: Reset employee_custom_data to its state as of 2026-01-01

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "reset": {
                "table": "employee_custom_data",
                "asOfTimestamp": "2026-01-01T00:00:00.000"
            }
        },
        {
            "commit": {}
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "ddeeff00-3344-5566-7788-99aabbccdde0",
    "tableAccess": {
        "read": [],
        "write": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
    }
}

setMetadata

This sample request uses the setMetadata action to attach key-value pairs to a table. The metadata is applied to the table when the transaction commits. You can use metadata values as preconditions in subsequent jobs to implement idempotency or version-checking patterns.

You can set a table's metadata using the setMetadata action when executing a job, then commit the metadata by committing the transaction.

Copy

cURL sample request: Set metadata on employee_custom_data and commit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "setMetadata": {
                "table": "employee_custom_data",
                "metadata": {
                    "version": "43",
                    "source_system": "workday",
                    "last_loaded_by": "integration_user"
                }
            }
        },
        {
            "commit": {}
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "ff001122-5566-7788-99aa-bbccddeeff00",
    "tableAccess": {
        "read": [],
        "write": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
    }
}

commit

This sample request runs a commit action The commit action:

Executes all pending changes in the transaction.
Closes the transaction.

Usually, commit is the last action in a SQL job that also performs data modifications, as shown in the upsert, replace, reset, and setMetadata examples. The following sample only contains the commit action. Use this to commit a transaction that remained open after a previous job ran with keepTransactionOpenOnError: true.

Copy

cURL sample request: Commit transaction 9f8e7d6c-5b4a-3210-fedc-ba9876543210

1
2
3
4
5
6
7
8
9
10
11
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "commit": {}
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "ccddeeff-0011-2233-4455-66778899aabb",
    "tableAccess": {
        "read": [],
        "write": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
    }
}

rollback

This sample request runs a rollback action. The rollback action discards all pending data changes in the transaction and closes it. A standalone rollback job is useful to close the transaction without waiting for it to expire.

You can use rollback to conditionally abort a transaction where:

The query action has rollbackOnEmpty: true.
rollback is the final action.

Copy

cURL sample request: Roll back transaction 9f8e7d6c-5b4a-3210-fedc-ba9876543210

1
2
3
4
5
6
7
8
9
10
11
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "rollback": {}
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "ddeeff00-1122-3344-5566-778899aabbcc",
    "tableAccess": {
        "read": [],
        "write": []
    }
}

process

This sample request runs a process action. The process actions starts a processing job that generates a new data version from the current table data. Omit dataCategoryIds to let Visier determine the data categories to process based on the modified tables in the transaction.

Copy

cURL sample request: Start a processing job for all affected data categories

1
2
3
4
5
6
7
8
9
10
11
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "process": {}
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "12345678-abcd-ef01-2345-6789abcdef01",
    "tableAccess": {
        "read": [],
        "write": []
    }
}

The following sample request combines upsert, setMetadata, commit, and process actions in a SQL job. The process action starts a processing job after committing the SQL changes. A processing job generates a new data version from the updated table data.

Copy

cURL sample request: Upsert rows, set metadata, commit, and start a processing job

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "upsert": {
                "source": "new_employees",
                "target": "employee_custom_data",
                "keys": ["employee_id", "effective_date"]
            }
        },
        {
            "setMetadata": {
                "table": "employee_custom_data",
                "metadata": {
                    "version": "44"
                }
            }
        },
        {
            "commit": {}
        },
        {
            "process": {
                "dataCategoryIds": ["c1d2e3f4-a5b6-7890-cdef-012345678901"]
            }
        }
    ],
    "inputs": [
        {
            "id": "new_employees",
            "filename": "new_employees.csv",
            "format": { "csv": { "header": true } }
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "12345678-abcd-ef01-2345-6789abcdef01",
    "tableAccess": {
        "read": ["new_employees"],
        "write": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
    }
}

preconditions

The following sample request uses preconditions to guard against duplicate or out-of-order loads. The SQL job only proceeds if the version metadata on employee_custom_data matches the expected value. If the precondition is not met, the job fails without modifying any data.

Copy

cURL sample request: Upsert with a version precondition on employee_custom_data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "upsert": {
                "source": "new_employees",
                "target": "employee_custom_data",
                "keys": ["employee_id", "effective_date"]
            }
        },
        {
            "setMetadata": {
                "table": "employee_custom_data",
                "metadata": { "version": "44" }
            }
        },
        {
            "commit": {}
        }
    ],
    "inputs": [
        {
            "id": "new_employees",
            "filename": "new_employees.csv",
            "format": { "csv": { "header": true } }
        }
    ],
    "preconditions": [
        {
            "table": "employee_custom_data",
            "metadata": { "version": "43" }
        }
    ]
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "fedcba98-7654-3210-fedc-ba9876543210",
    "tableAccess": {
        "read": ["new_employees"],
        "write": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
    }
}

keepTransactionOpenOnError

If any action in a job fails, the transaction rolls back. To suppress this behavior and leave the transaction open, set keepTransactionOpenOnError: true. This allows you to inspect the state or retry with a different job.

The following sample request upserts rows but keeps the transaction open if the job fails, so that you can commit or roll back manually.

Copy

cURL sample request: Upsert rows and keep the transaction open on error

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
-d '{
    "actions": [
        {
            "upsert": {
                "source": "new_employees",
                "target": "employee_custom_data",
                "keys": ["employee_id", "effective_date"]
            }
        },
        {
            "commit": {}
        }
    ],
    "inputs": [
        {
            "id": "new_employees",
            "filename": "new_employees.csv",
            "format": { "csv": { "header": true } }
        }
    ],
    "keepTransactionOpenOnError": true
}'

The response returns the job ID and the tables to read and write during the SQL job.

View sample response

Copy

1
2
3
4
5
6
7
{
    "jobId": "eeff0011-2233-4455-6677-8899aabbccdd",
    "tableAccess": {
        "read": ["new_employees"],
        "write": ["employee_custom_data", "uuid.`a1b2c3d4-e5f6-7890-abcd-ef1234567890`"]
    }
}

Retrieve a job's status

This sample request retrieves the status of a SQL job and any child jobs spawned from it. Use this endpoint to monitor the job until it reaches a terminal state, such as SUCCEEDED or FAILED.

Copy

cURL sample request: Retrieve the status of job 11223344-aabb-ccdd-eeff-001122334455

1
2
3
curl -X GET --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/jobs/11223344-aabb-ccdd-eeff-001122334455' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}'

The response includes an overall status, a summary of SQL job counts by state, a processingSummary of any processing jobs spawned by a process action, and a tenants array with per-tenant details including output filenames and processing job IDs.

View sample response: Running SQL job

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
{
    "jobId": "11223344-aabb-ccdd-eeff-001122334455",
    "status": "RUNNING",
    "summary": {
        "pending": 0,
        "running": 1,
        "succeeded": 0,
        "failed": 0
    },
    "processingSummary": {
        "pending": 0,
        "running": 0,
        "succeeded": 0,
        "failed": 0
    },
    "tenants": [
        {
            "tenantCode": "WFF_j1r",
            "status": "RUNNING",
            "message": "",
            "processingJobIds": [],
            "jobId": "11223344-aabb-ccdd-eeff-001122334455"
        }
    ]
}

The following sample response shows a failed job. The message field in the tenant entry contains the error detail. When a job fails, any uncommitted data changes in the transaction roll back (unless keepTransactionOpenOnError: true was set).

View sample response: Failed SQL job

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
{
    "jobId": "99aabbcc-ddeeff00-1122-3344-556677889900",
    "status": "FAILED",
    "summary": {
        "pending": 0,
        "running": 0,
        "succeeded": 0,
        "failed": 1
    },
    "processingSummary": {
        "pending": 0,
        "running": 0,
        "succeeded": 0,
        "failed": 0
    },
    "tenants": [
        {
            "tenantCode": "WFF_j1r",
            "status": "FAILED",
            "message": "Precondition not met: metadata key 'version' expected '43' but was '42'",
            "processingJobIds": [],
            "jobId": "99aabbcc-ddeeff00-1122-3344-556677889900"
        }
    ]
}

The following sample response shows a multi-tenant job that has committed data and spawned processing jobs in all analytic tenants.

View sample response: Multi-tenant SQL job with processing jobs

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
{
    "jobId": "12345678-abcd-ef01-2345-6789abcdef01",
    "status": "SUCCEEDED",
    "summary": {
        "pending": 0,
        "running": 0,
        "succeeded": 2,
        "failed": 0
    },
    "processingSummary": {
        "pending": 0,
        "running": 0,
        "succeeded": 2,
        "failed": 0
    },
    "tenants": [
        {
            "tenantCode": "WFF_j1r~c1o",
            "status": "SUCCEEDED",
            "message": "",
            "processingJobIds": ["aaaabbbb-1111-2222-3333-444455556666"],
            "jobId": "22334455-bbcc-ddee-ff00-112233445566"
        },
        {
            "tenantCode": "WFF_j1r~c2o",
            "status": "SUCCEEDED",
            "message": "",
            "processingJobIds": ["bbbbcccc-2222-3333-4444-555566667777"],
            "jobId": "33445566-ccdd-eeff-0011-223344556677"
        }
    ]
}

The following sample response shows a multi-tenant job where the SQL data changes succeeded for all tenants but the Visier processing job subsequently failed for one tenant. The overall status is FAILED because at least one tenant reached a terminal failure state. The committed data changes are not rolled back in this case. The PROCESSING_FAILED status indicates that only the downstream processing job failed.

View sample response: Multi-tenant SQL job with a processing failure

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
{
    "jobId": "12345678-abcd-ef01-2345-6789abcdef01",
    "status": "FAILED",
    "summary": {
        "pending": 0,
        "running": 0,
        "succeeded": 2,
        "failed": 0
    },
    "processingSummary": {
        "pending": 0,
        "running": 0,
        "succeeded": 1,
        "failed": 1
    },
    "tenants": [
        {
            "tenantCode": "WFF_j1r~c1o",
            "status": "SUCCEEDED",
            "message": "",
            "processingJobIds": ["aaaabbbb-1111-2222-3333-444455556666"],
            "jobId": "22334455-bbcc-ddee-ff00-112233445566"
        },
        {
            "tenantCode": "WFF_j1r~c2o",
            "status": "PROCESSING_FAILED",
            "message": "Processing job failed: data version could not be generated",
            "processingJobIds": ["bbbbcccc-2222-3333-4444-555566667777"],
            "jobId": "33445566-ccdd-eeff-0011-223344556677"
        }
    ]
}

The following sample shell script polls the endpoint until the job reaches a terminal state, then prints the final status.

Copy

Sample shell script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
TRANSACTION_ID="9f8e7d6c-5b4a-3210-fedc-ba9876543210"
JOB_ID="11223344-aabb-ccdd-eeff-001122334455"
BASE_URL="https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/${TRANSACTION_ID}/jobs/${JOB_ID}"

while true; do
  RESPONSE=$(curl -s -X GET --url "${BASE_URL}" \
    -H 'apikey:{api_key}' \
    -H 'Cookie:VisierASIDToken={security_token}')

  STATUS=$(echo "${RESPONSE}" | jq -r '.status')

  if [ "${STATUS}" != "PENDING" ] && [ "${STATUS}" != "RUNNING" ]; then
    echo "Job reached terminal state: ${STATUS}"
    echo "${RESPONSE}" | jq .
    break
  fi

  echo "Job status: ${STATUS}. Retrying in 5 seconds..."
  sleep 5
done

Download an output file from a transaction

This sample request downloads a complete output file generated by a query action within a transaction. To generate output files, use the output parameter in the query action when executing a job.

For large output files, download by file part for better performance. See List output file parts and Download an output file part.

Copy

cURL sample request: Download output file query_results.csv from transaction 9f8e7d6c-5b4a-3210-fedc-ba9876543210

1
2
3
4
curl -X GET --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/outputs/query_results.csv' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
--output query_results.csv

The response returns a 200 OK response.

List output file parts

Visier splits all output files into numbered parts. This sample request retrieves the number of parts available for a given output file. Each part is a complete, self-contained file in the output format. Parts are 0-indexed. For an output with numberOfParts: 3, the part numbers are 0, 1, and 2. For large output files, download by file part for better performance.

Copy

cURL sample request: List parts for output file query_results.csv in transaction 9f8e7d6c-5b4a-3210-fedc-ba9876543210

1
2
3
curl -X GET --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/outputs/query_results.csv/parts' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}'

The response returns the transaction ID, filename, and number of parts in the output file.

View sample response

Copy

1
2
3
4
5
{
    "transactionId": "9f8e7d6c-5b4a-3210-fedc-ba9876543210",
    "filename": "query_results.csv",
    "numberOfParts": 3
}

Download an output file part

This sample request downloads a single part of a multi-part output file. Each part is a complete, self-contained file in the output format. Parts are 0-indexed. For an output with numberOfParts: 3, the part numbers are 0, 1, and 2. For large output files, download by file part for better performance.

For CSV outputs with header: true, each part includes its own header row. For Parquet outputs, each part is a complete Parquet file. Process each part independently rather than concatenating their raw contents.

Copy

cURL sample request: Download part 0 of output file query_results.csv from transaction 9f8e7d6c-5b4a-3210-fedc-ba9876543210

1
2
3
4
curl -X GET --url 'https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/9f8e7d6c-5b4a-3210-fedc-ba9876543210/outputs/query_results.csv/parts/0' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
--output query_results_part0.csv

The response returns a 200 OK response.

The following sample shell script downloads all parts of an output file and writes each part to a separate file.

Copy

TRANSACTION_ID="9f8e7d6c-5b4a-3210-fedc-ba9876543210"
FILENAME="query_results.csv"
BASE_URL="https://{vanity_name}.api.visier.io/v1alpha/data/table-sources/transactions/${TRANSACTION_ID}/outputs/${FILENAME}"

# Retrieve the number of parts
NUM_PARTS=$(curl -s -X GET --url "${BASE_URL}/parts" \
  -H 'apikey:{api_key}' \
  -H 'Cookie:VisierASIDToken={security_token}' \
  | python3 -c "import sys,json; print(json.load(sys.stdin)['numberOfParts'])")

# Download each part to a separate file
for i in $(seq 0 $((NUM_PARTS - 1))); do
  curl -s -X GET --url "${BASE_URL}/parts/${i}" \
    -H 'apikey:{api_key}' \
    -H 'Cookie:VisierASIDToken={security_token}' \
    --output "query_results_part${i}.csv"
done