BigQuery date partitioned tables can limit the data scan by partitions to help keep the query cost low and improve query performance. However, Google's documents do not give much clue how to use partitioned tables to create views that support partition queries. I did some research and found the way to do it, even with views that are created from joining partitioned tables.
To expose the partitioning pseudo-column, create a query similar to this one:
If you save the query as a view, you can limit the query partitions by using the date column in your WHERE clause.
The above example is probably too simple for any actual query. We often create views because we have complex queries that joins multiple tables. In that case, to make the views support partitions, it is just as simple as creating multiple date columns as the above example, and make sure your query of the view contains WHERE clause that limit the search of these date columns. Here is an example of joining two partition tables:
When you query this view, and you want to limit the query to the data of 2018-03-20, you can do this:
I wish I could combine the different date columns as one, so I tried to join the date columns as keys in the ON statement, but that doesn't help. It seems you always have to have one date column for each partitioned table in the query, that's a little bit annoying but it will help lower the costs.