What are the key differences between OLAP and OLTP databases.
Specifically in terms of implementation (rather than use cases).
OLAP is of course primarily used for reporting while OLTP is used for handling transactions.
I understand that OLAP databases are optimized for read over write, and that OLAP databases contain more denormalised data.
What other characteristics set the two apart?
Advertisement
Answer
OLTP:
-
As the name suggest “Online Transaction Processing”, this is used for more transaction needs like “INSERT/SELECT/UPDATE/DELETE”.
-
Low Response Time.
-
There are the original source of data.
-
Usually data is stored in 3NF form.
-
ACID properties are necessarily followed.
OLAP:
-
As the name suggest “Online Analytical Platform”, used for analytical queries and in general are used for complex analytical queries and drawing inferences.
-
Periodic batch processing jobs are run here.
-
Typically de-normalized with fewer tables; use of star and/or snowflake schemas.
-
NOT necessarily follows ACID properties.