OLTP (Online Transaction Processing)

• encompasses most applications:, e.g. e-commerce, banking, etc.
• Is user-facing
  • Must be fast, low latency, and concurrent (i.e. able to support many users)
• Workload mainly involve a small set of common queries
• Accesses look like random reads and small writes

OLAP (Online Analytical Processing)

Mainly involves tasks done by analysts and data scientists.

• mainly involves tasks done by analysts and data scientists
• work is mainly done on the backend
  • users may see the end result of work, but no direct interaction
  • mainly involves batch workloads - low concurrency typically
• Workload mainly involves complex analytics (ad hoc)
• Access involves full table scans (big data)

As there are different patterns for the various workloads for business, a database cannot be tuned for either access pattern.

• large OLAP workloads also consume resources

Thus, we use a light database for fast access and a data warehouse for mass storage and OLAP queries.

Data is moved from a database to a data warehouse via Extract, Transform, Load pipelines (ETL)

• Extract ⇒ pull data from database
• Transform ⇒ put into different schema that is more suited for OLAP, BI, etc.
  • Must clean/validate data in step, as some bad data can ruin tasks like training
  • Converting schema should be done (different workload)
  • Fields must be transformed