View, table, materialized view in Big Query

[ammarkh]

We started using GCP platform, we started by streaming our AWS RDS MYSQL database using GCP Streams which streams the database tables into Google cloud storage. Then , using Google Dataflow, the tables from GCS are copied into BigQuery. This is an automated solution.

As a result, we will be having our OLTP database in BigQuery as a data set.

Our ultimate goal is connect Tableau to BigQuery in which each dashboard connects to a specific view (or table? or materialized view?)

My question is:

suppose we want to get data from tables order and customer from the BQ.

1) shall we use BQ views to get (orders join customers)? this will always make run the select/join when dashboard loads or filters.
is this a good way? or will this be slow?
2) shall we create a new table for the query result ? if yes, how this table will be populated with new data
coming from the streaming? (as new data is inserted and old data gets updated)
3) or using materialized view in BQ? also how data is auto populated here from the streamed tables?
4) or is there another recommended way in BQ ?

Thanks

[raul_saucedo · Accepted solution]

There is not a correct answer. You need to use the correct process depending on many factors, like how big your database is, the structure of the database, the structure of the queries you are using, the regions of the database, and the architecture of all the BigQuery processes.

You can see these documents about some best practices from BigQuery.

[ammarkh]

Thanks @raul_saucedo for your answer