Extract-Transform-Load (ETL)

Our ETL capability allows you to import data into our platform. The process starts with you populating a defined set of tables in our ETL SQL schema. After the tables are filled with data, you provide a connection string to your Microsoft SQL Server database. This connection string grants our ETL system access to your database, enabling the data to be imported into the staging environment. Once the data is validated, you can promote it to the production environment.

1. Populating the ETL SQL Schema

Our ETL SQL schema defines the tables and columns that our system recognises and expects to receive data in. These tables serve as the template for the data import process.

The schema consists of various tables, each with predefined columns that specify the data types and relationships. You will populate these tables with the relevant data from your external systems. Once the tables are filled, the data is ready for import into our platform. You must ensure that the data is correctly formatted and inserted into the appropriate tables within the schema.

Schema Reference

2. Importing Data into the Staging Environment

Endpoint: POST /etl/import

Description:

This endpoint allows you to submit an MSSQL connection string to a database populated with data based on our ETL SQL schema. Our ETL system will access the provided database and import the data into the staging environment for validation.

Request Payload:

{
    "connectionString": "your-mssql-connection-string-here"
}

Response:

• Status Code: 202 (Accepted)

Response:

{
    "message": "Import request queued successfully.",
    "requestId": "unique-request-id"
}

Process:

• You provide the MSSQL connection string to a database populated with data that follows the ETL schema.
• Our system connects to the provided database and begins importing the data into a provisioned tenant in the staging environment.

3. Promoting to Production

Endpoint: POST /etl/promote-to-production

Description:

Once the import is successfully completed and validated in the staging environment, this endpoint allows the user to promote the data to the production environment.

Request Payload:

{
    "requestId": "unique-request-id"
}

Response:

{
    "message": "Promotion to production started.",
    "requestId": "unique-request-id"
}

Process:

• The system will begin the promotion process, moving the data from the staging environment to the production environment.
• The status of this process can be monitored via the GET /etl/import-status/{requestId} endpoint.