Writeback

Writeback copies data from DataMind OS into a table of an external database, or publishes it to a RabbitMQ exchange. Use it when another system needs the results of your Lake or Warehouse work, or of a query you write over your data.

What Writeback sends

Each Writeback table takes its rows from one of these sources:

• a deployed Lake table
• a deployed Warehouse table
• a SQL query you write over your DataMind OS data

The rows go into a table that already exists in the destination. Writeback does not create tables there, so create the table in your database tools first, then import it. See Import destination tables.

Destinations

A destination is an external system that Writeback writes to. The Add destination picker offers these types:

• MSSQL, Postgres, Clickhouse, Oracle, MySQL and MariaDB
• Google BigQuery
• RabbitMQ

Other connector types, such as Snowflake, files or APIs, are not offered as destinations. Some Writeback controls say data source where you might expect destination, for example Add data source at the foot of a dashboard card.

For the connection forms, the connection test and the rest of destination management, see Add and manage destinations. For the tree of destinations and tables, see The Writeback screen.

Set up a destination and a Writeback table

1. Open WriteBack in the sidebar.
2. Click Add destination, or press Alt+N. Pick the type, fill in the connection form and click Add. Writeback creates a workflow named after the destination, with (Standard) added to the name.
3. In the panel next to the tree, tick the tables you want to write to and click Import. Imported tables are not mapped yet, so their icon is amber.
4. Double-click the table. On General, check the Workflow. Turn Table Selection on to pick a deployed Lake or Warehouse table, or turn it off to write a SQL query. Click Apply.
5. On Columns, map each destination column to a source column and set the Data Type each value is converted to. Click Apply. Saving the mapping deploys the table, and it counts as Mapped.
6. Run the table by hand from its Settings tab, or run or schedule its workflow. See Synchronize a table and read the Sync Log and Run, schedule and monitor Writeback.

Each step has its own page: Add and manage destinations, Import destination tables, Configure a Writeback table, Map columns with Data types for columns and Data domains. For the editor around the steps, see Create and edit a Writeback table and Save and revert your changes.

For a guided walk-through, see Put results in front of people. Lineage shows Writeback tables next to the Lake and Warehouse tables they read. If a message stops you along the way, see Writeback troubleshooting.

When a table runs

• A table runs only when it is deployed (Mapped). An Unmapped table does not run, by hand or on its workflow.
• If you change the source on General and no column stays mapped, the table is no longer deployed. Map its columns again to bring it back.
• A table runs with the workflow it belongs to. To move it to another workflow of the same destination, see Transfer tables between workflows.
• Synchronize is greyed out while a run of that table is in progress.
• By default, a run reads all the rows its source returns. To read only recent rows on a RabbitMQ destination, set Sync mode to Incremental in the Publish Window. See Writeback sync types.

What happens to your rows

• Each run adds the rows it reads to the destination table. Writeback never updates or deletes rows that are already there.
• Large results are sent to the destination in batches.
• Target columns with no mapping, including columns you skipped on Columns, receive empty (NULL) values on every run. Make sure those columns accept empty values. See Map columns.
• Each mapped value is converted to the data type set for its target column on Columns before it is written. See Data types for columns.
• If a run fails part-way, the rows already written stay in the destination. The next run sends the same rows again.
• For RabbitMQ, each run publishes the rows as JSON messages to the exchange instead of inserting them into a table. The Publish Window sends only recent rows, and it appears only for RabbitMQ destinations. See Publish to RabbitMQ.

Permissions

Data permissions control destinations and Writeback tables. Four of them matter most:

• View WriteBack Objects lets you open the area and see destinations and tables.
• Add/Modify WriteBack Objects lets you add destinations and change tables.
• Execute/Abort WriteBack Objects lets you run a table with Synchronize.
• Remove WriteBack Objects lets you delete destinations and tables.

Importing destination tables also needs Add/Modify Lake Objects. Workflow permissions control the Writeback workflows. For the full list, see Writeback permissions.

Questions, answered

Does Writeback update existing rows in my database?

No. Each run adds the rows it reads. Existing rows are not updated or deleted, so clean up or deduplicate them in the destination if you need to.

Which systems can I write to?

Only the types in the Add destination picker: MSSQL, Postgres, Clickhouse, Oracle, MySQL, MariaDB, Google BigQuery and RabbitMQ.

Can I write the result of a SQL query instead of a whole table?

Yes. Turn Table Selection off on General, write the query and click Validate. See Configure a Writeback table.

Can I write to a table that does not exist yet in the destination?

No. Writeback writes only to tables that already exist in the destination and that you have imported. Create the table in the destination with your database tools, then import it. See Import destination tables.

I ran a table twice and now have duplicate rows. Why?

Each run sends the rows it reads, so running the same table twice sends the same rows twice. Limit the rows your source returns, or, for RabbitMQ, use the Publish Window. See Publish to RabbitMQ.

Do I need to create a workflow before I can use Writeback?

No. Adding a destination creates a workflow named after the destination, with (Standard) added to the name, and the tables you import are placed on it. You can change the workflow of a table on General, or move tables between workflows. See Run, schedule and monitor Writeback.

Is the data converted on the way out?

Yes. Each mapped value is converted to the data type set for its target column on Columns. See Map columns and Data types for columns.