Temporary tables versus permanent staging tables in Synapse ETL

Local temporary tables (#table)

Temporary tables are created with a hash prefix (CREATE TABLE #temp_orders (...)). They are scoped to the current user session and are stored in the cluster's distributed tempdb.

• Fast lifecycle: Synapse automatically drops temporary tables when the pipeline session terminates, eliminating manual cleanup scripts.
• Round-robin default: Temporary tables default to DISTRIBUTION = ROUND_ROBIN, providing fast write speeds for small intermediate transformations.
• Limitations: Temporary tables cannot use Clustered Columnstore Indexes in older pool versions, and extensive tempdb space usage can cause cluster-wide storage exhaustion during large ETL batches.

Permanent staging tables

Permanent staging tables are standard tables defined in a dedicated database schema (such as stg.orders).

CREATE TABLE stg.orders (
    order_id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    amount DECIMAL(18,2)
)
WITH (
    DISTRIBUTION = HASH(customer_id), -- Colocated with target table!
    CLUSTERED COLUMNSTORE INDEX
);

Permanent staging tables provide key pipeline advantages:

• Colocated joins: By defining the staging table with the exact same HASH distribution key as the target fact table, the subsequent MERGE or INSERT statement executes as a colocated join across the 60 distributions with zero network shuffle.
• Parallel bulk loading: Permanent tables natively support the high-throughput COPY INTO command from Azure Data Lake Storage.

Use permanent staging tables with matching HASH keys for major recurring ingestion pipelines. Use temporary tables for lightweight intermediate filtering and session calculations.