Problem

You create a view on an underlying table with the intent of keeping the view columns synchronized with the table columns. You notice new columns added to the table are not immediately available and accessible in the view.

Example

After creating a table and view, when you select from the table, it returns both columns. When you select from the view, it only returns one column.

%sql
-- create a table and view
CREATE OR REPLACE TABLE my_table (c1 INT);
CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table;

-- add a column to the table
ALTER TABLE my_table ADD COLUMNS (c2 INT);

-- select from the table
SELECT * FROM my_table; -- ➡️ returns both `c1` and `c2` columns

-- select from the view
SELECT * FROM my_view; -- ➡️ only returns column `c1`

Cause

A view created using select *  stores the column names and data types of the underlying table at the time of view creation. Any subsequent changes to the table structure, such as adding new columns, are not automatically reflected in the view because view definition is not dynamically linked to the underlying table structure.

Solution

There are two approaches available. 

1. Rebuild the existing view.
2. Recreate the view using WITH SCHEMA EVOLUTION.

Rebuild the existing view

Use the following code to initially replace an existing view. If the view has existing permissions or grants, they need to be reapplied.

CREATE OR REPLACE VIEW <your-view> AS SELECT * FROM <your-table>;

Alternatively, if your tools allow it, use the following code to rebuild the view with ALTER VIEW to keep existing permissions or grants intact.

ALTER VIEW <your-view> AS SELECT * FROM <your-table>;

Recreate the view using WITH SCHEMA EVOLUTION

If you use Databricks Runtime 15.3 or above, you can recreate the view with the WITH SCHEMA EVOLUTION schema binding syntax. WITH SCHEMA EVOLUTION allows the view to adapt to changes in the schema of the query due to changes in the underlying object definitions. 

In the following code, the example from the problem statement is modified to include WITH SCHEMA EVOLUTION. After applying, both columns are returned when you select from the view.

-- create a table and view
CREATE OR REPLACE TABLE my_table (c1 INT);
CREATE OR REPLACE VIEW my_view WITH SCHEMA EVOLUTION AS SELECT * FROM my_table;


-- add a column to the table
ALTER TABLE my_table ADD COLUMNS (c2 INT);

-- select from the table
SELECT * FROM my_table; -- ➡️ returns both `c1` and `c2` columns

-- select from the view
SELECT * FROM my_view; -- ➡️ returns both `c1` and `c2` columns

For more information, refer to the CREATE VIEW (AWS | Azure | GCP)  and ALTER VIEW (AWS | Azure | GCP) documentation.