Databricks output

Load rows into a Databricks table (append or replace) through a Unity Catalog volume.

Category

Output

Connection

Databricks

What it does

Loads the incoming rows into a Databricks table through a saved Connection. Rows are staged as a single columnar file in a Unity Catalog volume, loaded into the table, and the staged file is then removed. The table is created automatically if it doesn't exist.

Inputs and outputs

When to use it

• Land prepared data in the lakehouse for reporting or downstream notebooks
• Refresh a reporting table on a schedule
• Move data from a database, a file or an API into Databricks

Examples

Pre-SQL: TRUNCATE TABLE main.default.stg_orders · Post-SQL: MERGE INTO main.default.orders t USING main.default.stg_orders s ON t.id = s.id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT * · OPTIMIZE main.default.orders

main.sales.orders

/Volumes/main/default/trinium_stage

Write mode: truncate

Tips

• Each pre- and post-SQL statement has its own ⤢, the SQL Studio, with the connection's tables and columns beside the statement. It has no Run button: a Test run executes none of these statements.
• Pre-SQL and post-SQL run on this same connection, around the load. Pre-SQL runs and commits BEFORE the load starts; post-SQL runs only AFTER it has finished successfully, so a failed load never fires the merge.
• One statement per row, run in order. If one fails the run stops there, and the message names which statement it was and what the database said.
• A Test run (Run to here) writes nothing and executes none of them, so testing stays safe on a pipeline whose pre-SQL truncates a table.
• The staging volume is required, and it must be a Unity Catalog volume path beginning with /Volumes/. Create an empty one for this purpose; nothing is left behind in it.
• The access token needs permission to write files as well as run SQL. A token scoped to BI tools passes the Test button and reads fine, then fails to stage the file.
• Each run stages a uniquely named file on purpose: the warehouse remembers files it has already loaded, so a fixed name would make the second run quietly load nothing.
• The load is not all-or-nothing: a truncate and the load that follows are separate steps, so a failure in between can leave the table empty. Re-running fixes it.
• An auto-created table gets column types derived from the incoming data. If the table already exists, its own types win.
• There is no upsert mode: merging is a MERGE against a staging table, which is better built explicitly as its own step.

Related nodes