Manage SQL definitions for an output

Unlike other Foundry compute workflows, Furnace SQL allows a tabular output to retain multiple SQL definitions. A definition contains either a single write query or multiple statements in a transaction block. One definition is marked Active.

The active definition is the SQL that runs whenever the table is rebuilt, either on schedule or on demand. Selecting a different active definition changes future rebuilds but does not immediately run the definition.

View SQL definitions

To view the definitions associated with an output:

1. Open the output in Dataset Preview.
2. Select the Details tab.
3. Expand the Job spec section.

Each definition displays its SQL operation type, last run, query ID, and SQL. The active definition is identified by the Active label.

From a definition's options menu, you can:

• Select Set as active definition to use its SQL for subsequent scheduled and on-demand rebuilds.
• Select Open in SQL Studio to edit or run the SQL in SQL Studio.

Create additional definitions

Running a write query creates a definition for each output. The first definition for an output becomes active. Except for CREATE OR REPLACE TABLE, each subsequent write retains the existing definitions, adds a definition, and makes the new definition active unless you use the adhoc hint. A CREATE OR REPLACE TABLE query replaces the existing definitions for the recreated output.

Use the adhoc SQL hint to run a one-time write and retain it as an additional definition without changing the active definition.

Backfill historical data

Consider an Iceberg table at /Sales/Orders that receives new orders from a daily input. Its active definition appends the latest rows whenever the table is built on schedule or on demand:

Copied!
1
2
3
INSERT INTO `/Sales/Orders`
SELECT order_id, customer_id, order_date, total
FROM `/Sources/Orders daily`;

Suppose you later discover a historical input containing orders that are missing from the output. Run the backfill as an ad hoc definition:

Copied!
1
2
3
4
5
/*+ adhoc */
INSERT INTO `/Sales/Orders`
SELECT order_id, customer_id, order_date, total
FROM `/Sources/Orders history`
WHERE order_date < DATE '2026-01-01';

The historical rows are appended immediately, and the backfill SQL is retained as an additional definition. The daily query remains active, so subsequent scheduled and on-demand builds continue to append from /Sources/Orders daily.

Change an Iceberg table property

Consider an Iceberg table at /Manufacturing/Sensor readings that receives hourly sensor data. Its active definition is:

Copied!
1
2
3
INSERT INTO `/Manufacturing/Sensor readings`
SELECT recorded_at, sensor_id, reading
FROM `/Sources/Sensor readings hourly`;

To use zstd compression for data files written in the future, run the property change as an ad hoc definition:

Copied!
1
2
3
4
5
/*+ adhoc */
ALTER TABLE `/Manufacturing/Sensor readings`
SET TBLPROPERTIES (
  'write.parquet.compression-codec' = 'zstd'
);

The property is updated once without making the ALTER TABLE statement active. Existing data files are unchanged, while data files produced by later runs of the active definition use zstd compression.

Data Lineage

Data Lineage reflects the inputs referenced by all retained SQL definitions, including inactive and ad hoc definitions. As a result, an input can appear in lineage even when it is not referenced by the active definition.