Error INVALID_TEMP_OBJ_REFERENCE when trying to create a view

Persist the temporary object to a location, then create your view.

Written by lucas.rocha

Last published at: January 16th, 2025

Problem

When creating a VIEW using a temporary object as a source, you encounter an INVALID_TEMP_OBJ_REFERENCE error.

Example error message

[INVALID_TEMP_OBJ_REFERENCE] Cannot create the persistent object `main`.`default`.`table` of the type VIEW because it references to the temporary object `thisIsView` of the type VIEW. Please make the temporary object `thisIsView` persistent, or make the persistent object `main`.`default`.`table` temporary. SQLSTATE: 42K0F

Cause

Temporary objects are limited to a session and are not persistent.

Solution

First, persist the temporary object to a location, such as a materialized view or a new table. Then create your VIEW.

Note

Temporary views have limited scope and persistence, and aren't registered to a schema or catalog.

For more information, please refer to the What is a view? (AWS | Azure | GCP) documentation.