1 of 27

Data Warehouse

2 of 27

Index

OLAP vs OLTP
What is data warehouse
BigQuery

Cost
Partitions and Clustering
Best practices
Internals
ML in BQ

3 of 27

OLAP vs OLTP

	OLTP	OLAP
Purpose	Control and run essential business operations in real time	Plan, solve problems, support decisions, discover hidden insights
Data updates	Short, fast updates initiated by user	Data periodically refreshed with scheduled, long-running batch jobs
Database design	Normalized databases for efficiency	Denormalized databases for analysis
Space requirements	Generally small if historical data is archived	Generally large due to aggregating large datasets

4 of 27

	OLTP	OLAP
Backup and recovery	Regular backups required to ensure business continuity and meet legal and governance requirements	Lost data can be reloaded from OLTP database as needed in lieu of regular backups
Productivity	Increases productivity of end users	Increases productivity of business managers, data analysts, and executives
Data view	Lists day-to-day business transactions	Multi-dimensional view of enterprise data
User examples	Customer-facing personnel, clerks, online shoppers	Knowledge workers such as data analysts, business analysts, and executives

5 of 27

What is a data warehouse

OLAP solution
Used for reporting and data analysis

6 of 27

BigQuery

Serverless data warehouse

There are no servers to manage or database software to install

Software as well as infrastructure including

scalability and high-availability

Built-in features like

machine learning
geospatial analysis
business intelligence

BigQuery maximizes flexibility by separating the compute engine that analyzes your data from your storage

7 of 27

BigQuery Cost

On demand pricing

1 TB of data processed is $5

Flat rate pricing

Based on number of pre requested slots
100 slots → $2,000/month = 400 TB data processed on demand pricing

8 of 27

Partition in BQ

9 of 27

Clustering in BigQuery

10 of 27

BigQuery partition

Time-unit column
Ingestion time (_PARTITIONTIME)
Integer range partitioning
When using Time unit or ingestion time

Daily (Default)
Hourly
Monthly or yearly

Number of partitions limit is 4000

Resource: https://cloud.google.com/bigquery/docs/partitioned-tables

11 of 27

BigQuery Clustering

Columns you specify are used to colocate related data
Order of the column is important
The order of the specified columns determines the sort order of the data.
Clustering improves

Filter queries
Aggregate queries

Table with data size < 1 GB, don’t show significant improvement with partitioning and clustering
You can specify up to four clustering columns

12 of 27

BigQuery Clustering

Clustering columns must be top-level, non-repeated columns

DATE
BOOL
GEOGRAPHY
INT64
NUMERIC
BIGNUMERIC
STRING
TIMESTAMP
DATETIME

13 of 27

Partitioning vs Clustering

Clustering	Partitoning
Cost benefit unknown	Cost known upfront
You need more granularity than partitioning alone allows	You need partition-level management.
Your queries commonly use filters or aggregation against multiple particular columns	Filter or aggregate on single column
The cardinality of the number of values in a column or group of columns is large�

14 of 27

Clustering over paritioning

Partitioning results in a small amount of data per partition (approximately less than 1 GB)
Partitioning results in a large number of partitions beyond the limits on partitioned tables
Partitioning results in your mutation operations modifying the majority of partitions in the table frequently (for example, every few minutes)

15 of 27

Automatic reclustering

As data is added to a clustered table

the newly inserted data can be written to blocks that contain key ranges that overlap with the key ranges in previously written blocks
These overlapping keys weaken the sort property of the table

To maintain the performance characteristics of a clustered table

BigQuery performs automatic re-clustering in the background to restore the sort property of the table
For partitioned tables, clustering is maintained for data within the scope of each partition.

16 of 27

BigQuery-Best Practice

Cost reduction

Avoid SELECT *
Price your queries before running them
Use clustered or partitioned tables
Use streaming inserts with caution
Materialize query results in stages

17 of 27

BigQuery-Best Practice

Query performance

Filter on partitioned columns
Denormalizing data
Use nested or repeated columns
Use external data sources appropriately
Don't use it, in case u want a high query performance
Reduce data before using a JOIN
Do not treat WITH clauses as prepared statements
Avoid oversharding tables

18 of 27

BigQuery-Best Practice

Query performance

Avoid JavaScript user-defined functions
Use approximate aggregation functions (HyperLogLog++)
Order Last, for query operations to maximize performance
Optimize your join patterns

As a best practice, place the table with the largest number of rows first, followed by the table with the fewest rows, and then place the remaining tables by decreasing size.