Querying a view that references another view returns no records.

[DataEngineer]

I have a view that compares a table to a different view. If I run the SQL (on the right) that comprises the view, I get the expected record output.  
Now if I SELECT * the view itself (on the left), no data is returned.

I have troubleshooted this by materializing the referenced view (view_2) as a new table and then calling that table instead of view_2. The same record output is returned as expected.

Why is this happening? A view should be able to reference another view. 

[ms4446]

There are several possible reasons why your view in Google Cloud BigQuery is not returning any records, even though the SQL query that defines it works as expected:

1. Permissions: Ensure you have the necessary permissions to query both views and access the underlying datasets and tables.

2. Schema Changes: Check if the schemas of the underlying tables and views have changed since the views were created. BigQuery views are logical and re-evaluate the data at each query, so they should adapt to schema changes unless those changes invalidate the SQL of the view.

3. Query Parameters: Remember that views in BigQuery cannot reference query parameters, which could be a limitation in certain query designs.

4. Temporary Objects and Wildcard Table Queries: Views cannot reference temporary user-defined functions, temporary tables, or be used in wildcard table queries.

5. View Referencing Another View: This is supported in BigQuery. Ensure that:

   • Both views are in the same dataset.
   • They use the same SQL dialect (Standard SQL or Legacy SQL).

6. Troubleshooting Steps:

   • Try creating a new view that references the materialized table instead of the referenced view.
   • Consider dropping and recreating the view.
   • If issues persist, contact Google Cloud support for further assistance.

7. Additional Troubleshooting Tips:

   • Query the views using the BigQuery UI to identify any errors in your SQL queries.
   • Enable BigQuery audit logging to monitor the queries executed against your views.
   • Use the bq show command to view details of your views, which can help identify issues with the view definitions.
   • Check for query complexity and simplify if necessary.
   • Verify data freshness, as views reflect the current state of the underlying data.
   • Consider disabling caching during troubleshooting to see if it affects the results.

[DataEngineer]

---

---

The only thing I can think of that might be a possible cause in my case is the use of time travel.

> @ms4446 wrote:
>Temporary Objects and Wildcard Table Queries: Views cannot reference temporary
>user-defined functions, temporary tables, or be used in wildcard table queries.

Does this include time travel?

FOR SYSTEM_TIME AS OF

 In my example, view_2 is using a time travel table using FOR SYSTEM_TIME AS OF.

[ms4446 · Accepted solution]

Hi @DataEngineer , 

Yes, the use of time travel queries in BigQuery can indeed introduce complexities when referencing views. Time travel queries, by their nature, are point-in-time snapshots and do not reflect the latest state of the underlying tables. Consequently, when a view (like view_2 in your example) uses a time travel query, its results may not represent the current state of the data. This can lead to inaccuracies or incomplete data when another view references view_2.

To address this issue, consider the following approaches:

• Use the Latest State of the Table: If historical data is not a requirement for your analysis, modify view_2 to avoid using a time travel query. This ensures that the view reflects the most current data.

• Materialize the Data: While BigQuery does not support traditional materialized views as of my last update, you can create a physical table that periodically captures the state of view_2. This approach can stabilize the data used by the parent view, but it requires managing data refreshes to keep the materialized data up-to-date.

• Revise the Query Approach: Consider using a different query strategy, such as directly joining the relevant tables. This can bypass the complexities introduced by time travel queries and might provide a more straightforward solution to your data needs.

[DataEngineer]

Thanks for the clarification. Historical data is necessary to the query. I will work something else out with the additional information you've provided.