I am considering Google BigQuery as my data warehouse option. I have data in Google Cloud SQL, Google Cloud BigTable and exposed REST APIs on top of it to consume data on any UI. I am planning to use same APIs as source in my ETL job which will append data into BigQuery
From this API, I can get daily data. Lets take example - Total entities - 10,000, Measurements types associated with each entity - 1000. So for per year (single entry of each measurement per day) - 365 (no of days) * 10,000 (total entities) * 1000 (total measurements) = 3650000000 (around 4 billion)
Right now, I have 2 choices of schema design:-
- Create single table - one entity id column and 1000 measurement columns
- Use different table year wise (use sharding year wise) and later use UNION queries to fetch data
Please let me know which option would be best in terms of cost, scalability. I understand 2nd option would be more cost effective as it requires few table scans.
Is there any better choices available?