Writing back data to the warehouse
Cargo provides multiple ways of managing data across connected services, including systems of record and data warehouses. This flexibility allows precise updates to tables and records in the underlying systems.
Before you begin
Ensure you have a system of record configured in your workspace.
Add SQL write node
In the workflow editor, create a link from the start node to add a new node. In the nodes catalog that appears, search for SQL write node.
Configure SQL write node
In the SQL write node, configure the database operation:
- Enter the database name
- Select the schema and table
- Choose the action to perform
Available actions:
- Insert - always creates a new row
- Upsert - creates a new row if no matching row was found, otherwise updates it
- Update - updates matching row without creating new entries
- Delete - removes matching row
Define matching details
After configuring the database details, set up the data matching:
- For all actions except INSERT, specify matching criteria
- Choose a unique identifier column (e.g., ID or email)
- Set the matching value to exactly match the identifier column
The connector will use these settings to locate the correct records for updates.
Define mapping
Now the connector knows where to look for the relevant data or where to add new data. It also knows how to match existing data in case of modification.
The next step is to define what data should be inserted or updated in the database.
Specify each data point using a mapping entry. Each entry needs to target a specific column from the table and provide a value for the update or the insert. Once mapping is provided, the node is ready to run.
Finish line
You've learned how to write data back to a configured data warehouse using the SQL write node. This enables you to:
- Keep your data warehouse in sync with other tools by creating, updating and removing data as needed
- Maintain a single source of truth for your business data