Solved: View schema not updated

samirlv

hello team, we are having an issue regarding schema of a view not being update.

the view is a simple "select * from A" , after deleting a column from A the view schema is still taking into account the deleted column even after a refresh (through UI). is this expected behavior or it should have been aligned with the underlying table A ?

As a workaround we re-created manually the view but i'm wandering if this should have been handled by bigquery automatically.

thanks team

ms4446

In BigQuery, views are virtual tables defined by a stored SQL query. When you query a view, BigQuery executes the underlying query against the current state of the tables, not the state at the time the view was created. While views are designed to simplify querying and provide abstraction, BigQuery has certain behaviors and limitations around how it manages schema updates when underlying tables change.

Expected Behavior

When the schema of the underlying table is modified (e.g., by deleting a column), BigQuery does not automatically update the view's schema. This is because a view is essentially a snapshot of the query at the time of its creation. If you use SELECT * in the view definition, the schema will include all columns that were present when the view was created, even if some are later removed from the table.

If a column used in the view is deleted from the underlying table, queries against the view will fail at runtime with an error stating that the column does not exist. However, the view schema itself in the BigQuery UI might still show the old structure until the view is manually refreshed or recreated.

Proper Handling and Best Practices

Recreate or Refreshthe View: The most straightforward way to update the schema is to recreate or refresh the view using CREATE OR REPLACE VIEW or ALTER VIEW.
Automate View Refreshes: Automate the process of recreating or refreshing views using scripting (e.g., the bq command-line tool or the BigQuery API) or a CI/CD pipeline. This is particularly important in environments where table schemas change frequently.
Avoid SELECT * in Production Views: Explicitly list the columns you need in your view definitions. This makes your queries more robust to changes in the underlying tables and prevents unexpected errors if new columns are added or existing columns are removed.
Schema Change Scripts: When modifying table schemas, always include the corresponding view updates as part of your deployment process. BigQuery's scripting capabilities can help automate these updates.

View solution in original post

ms4446

In BigQuery, views are virtual tables defined by a stored SQL query. When you query a view, BigQuery executes the underlying query against the current state of the tables, not the state at the time the view was created. While views are designed to simplify querying and provide abstraction, BigQuery has certain behaviors and limitations around how it manages schema updates when underlying tables change.

Expected Behavior

When the schema of the underlying table is modified (e.g., by deleting a column), BigQuery does not automatically update the view's schema. This is because a view is essentially a snapshot of the query at the time of its creation. If you use SELECT * in the view definition, the schema will include all columns that were present when the view was created, even if some are later removed from the table.

If a column used in the view is deleted from the underlying table, queries against the view will fail at runtime with an error stating that the column does not exist. However, the view schema itself in the BigQuery UI might still show the old structure until the view is manually refreshed or recreated.

Proper Handling and Best Practices

Recreate or Refreshthe View: The most straightforward way to update the schema is to recreate or refresh the view using CREATE OR REPLACE VIEW or ALTER VIEW.
Automate View Refreshes: Automate the process of recreating or refreshing views using scripting (e.g., the bq command-line tool or the BigQuery API) or a CI/CD pipeline. This is particularly important in environments where table schemas change frequently.
Avoid SELECT * in Production Views: Explicitly list the columns you need in your view definitions. This makes your queries more robust to changes in the underlying tables and prevents unexpected errors if new columns are added or existing columns are removed.
Schema Change Scripts: When modifying table schemas, always include the corresponding view updates as part of your deployment process. BigQuery's scripting capabilities can help automate these updates.