Data Warehouse

ETL

Extract
Transform
Load process

Design Data Warehouse

1. requirement: business objectives, KPI and data sources
2. schema design
3. choose platform: snowflake, Redshift, bigquery
4. ETL/ELT processes: Designing pipelines to handle high volumes of data while ensuring data quality.
5. Performance optimization: Implementing indexing, partitioning, and caching strategies for fast query execution.

Maintain Data Quality

1. understand data pattern/type from each data sources,
2. validate data during ETL process to check for errors, when new data comes in

Optimize Query Performance

1. indexing: create indexes for quickly look up
2. partitioning: partition large data into small data
3. materialized view: pre-compute and store results for repetitive queries
4. Denormalization: reduce joins by consolidating data

Materialized View

pre-compute query results for future use

1. less computational cost, no need to recompute each time
2. can be refreshed incrementally to maintain up-to-date data
   e.g pre-aggregate daily sales data in a sales reporting system, allowing faster and real-time analysis and visualization during peak hours

Incremental Loading

a technique that to update only new data or changed data

1. timestamp: add last modified to identify if data is changed or new imported
2. change data capture: detect and extract directly from data source via database logs or a triggers
3. Compare current data with previously loaded data to identify changes.