Skip to main content

Writeback cells

Writeback cell types are a unique cell type that let you write dataframes back to your database. These cells provide a simple and easy method of doing so, without the need for more advanced SQL queries or external Python libraries.

Allow a data connection#

Writeback cells work on a double opt-in model:

  • Each data connection must specifically be configured to allow writeback.
  • The credentials used in the data connection must have permission to writeback to the database.

To enable writeback for a connection, an Admin must head to the Workspace assets section of the Settings and enable the Allow connection to be used in Writeback cells setting. You can also choose to only share this connection with a certain group of people using the permissions outlined here.

Supported data connections#

Some data connection types are not supported in Writeback cells.

warning

Athena, Dremio, Trino, & Transform connectors are not supported in Writeback cells.

The Writeback cell can only be used with the following data connection types:

  • Postgres
  • Snowflake
  • BigQuery
  • Redshift
  • Databricks
  • MySQL
  • MariaDB
  • SQL Server

Writeback to a database#

A Writeback cell can be created from the Add cell menu. Once the cell has been created, you will be prompted to select a connection, database, and schema to writeback to, as well as two key options:

  • To overwrite the specified table if it already exists or append data (inserting new rows) to it
  • A static name for the table or a dynamic name for the table, based on a Python variable
caution

Dynamic table names that rely on a variable with many possible values can lead to the creation of many tables in your database. These tables will exist until they are dropped manually.

When the cell is run, the dataframe will be written back to the database and the cell will return metadata regarding the writeback.

Configure write mode options#

By default, writeback cells have write mode turned off in order to avoid continually writing to your database. If you do not change the Write mode option, Writeback cells will only execute if you manually run the cell.

In addition to the manual method, there are three different automated write mode settings for Writeback cells, which can be independently enabled after selecting the Write mode menu at the upper-right of the cell:

  • Logic session: The dataframe will be written when the cell is executed in by an Editor while developing. When this setting is off, developers can still manually run the cell to writeback to the database.
  • App session: The dataframe will be written when the cell is run in a Published App.
  • Scheduled run: The dataframe will be written when the cell is run during scheduled runs of the project.

Writeback to a database via code#

Hex also provides a Python package (hextoolkit) that makes it easier to write to your database. This can be useful if you need to wrap the writeback code in more fine-grained, pythonic, logic.

import hextoolkithex_data_connection = hextoolkit.get_data_connection(<"Data Connection Name">)writeback_metadata = hex_data_connection.write_dataframe(df=<dataframe_name>, database="<database_name>", schema="<schema_name>", table="<table_name>", overwrite=<True/False>)
tip

You can also use the Duplicate as Python cell option in the Writeback cell menu to generate this code.

It is also possible to write to your database using completely custom logic using multi-statement SQL queries or using Python and a connector library for your database.