# OLTP and OLAP

## Online Transactional Processing Systems (OLTP)

* Operational systems
* Used for transactional type operations
* Store day to day business transactions
* Minimize Locking
* Not suitable for Reporting
* Simple Queries
* Commonly owned by different departments (Acc, Sales, CRM)
* Do not share/exchange data
* Can be Relational (SQL, DB2, Oracle) or Non-Relational (Cassandra, MongoDB, Hadoop)
* Fast Data Insert and Update

<figure><img src="https://3167488510-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FN5Oxfr1ogoEGQMX2dzNM%2Fuploads%2F4JTfzWFli34LcggQ7bDy%2Fimage.png?alt=media&#x26;token=8bb86970-f850-4f75-8d2d-72ed14f33d32" alt=""><figcaption></figcaption></figure>

## Online Analytical Processing System (OLAP)

* Enables data analysis and reporting for faster decision making
* Storage structured so that many individuals from different departments can access the data
* ETL transfers the data from the source systems to a data warehouse
* More accessible for end-users
* Make strategic decisions
* Store historical Data
* Single point of truth
* Higher Data Quality and Data Integrity
* Higher Read performance

<figure><img src="https://3167488510-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FN5Oxfr1ogoEGQMX2dzNM%2Fuploads%2FQkyunaVxetVaDGpowJpW%2Fimage.png?alt=media&#x26;token=3a3ba6e0-ba7a-4768-a50b-331eaf972796" alt=""><figcaption></figcaption></figure>

## OLTP vs OLAP

<figure><img src="https://3167488510-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FN5Oxfr1ogoEGQMX2dzNM%2Fuploads%2FZYerFH41tNg17W7hhNes%2Fimage.png?alt=media&#x26;token=dfdaff5a-f637-42e5-92d9-4acb0b481e3d" alt=""><figcaption></figcaption></figure>

## Data Architecture Example

<figure><img src="https://3167488510-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FN5Oxfr1ogoEGQMX2dzNM%2Fuploads%2FelhhqsQOZtbW19iMM8oH%2Fimage.png?alt=media&#x26;token=a58feded-38d6-4558-8596-36e8437f2d7b" alt=""><figcaption></figcaption></figure>

### Data sources

* Applications, operational systems (OLTP)
* Can be Relational, Non-relational, files, etc.
* Might have data quality issues

### ETL

* Any activity related to the manipulation of data
* E (Extract), T (Transform), L (Load)
* ELT is becoming an alternative on Cloud Environments

### Staging

* Temporary tables
* Use ingested tables as a source for raw data
* 1:1 copy of the tables from source
* Can be used to test for data quality

### Storage

* Cleaned and transformed data
* High levels of data integrity and quality
* Can be stored in Data Warehouse or Data Marts (OLAP)

### Reporting

* Used for BI reporting by end-users