> For the complete documentation index, see [llms.txt](https://claudiu-stanciu.gitbook.io/software-craft/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://claudiu-stanciu.gitbook.io/software-craft/big-data/data-formats.md).

# Data modelling

## **Normalization**

* normalized
  * reduce data(field) duplication
  * data storage is more efficient for write performance
  * simple queries run faster
  * data is scattered across multiple tables
* denormalized
  * duplicate data across tables to improve read performance over write performance
  * data exists in 1 table, as opposed to multiple tables
* check <https://en.wikipedia.org/wiki/Database_normalization#Normal_forms>

## Transactional Schema (OLTP)

* normalized schema
* used for OLTP - transactional workloads

## Star Schema (OLAP)

* easiest schema design for data warehouse/mart
* data is organized into a **central fact table** that contains the measures of interest, surrounded by **dimension tables** that describe the attributes of the measures.
* used for OLAP - analytical workloads
* fact table: usually numeric values that can be aggregated
* dimentional table: groups of hierarchies and descriptors that define the facts (attributes)
* data is joined through FKs
* data can be denormalized, to improve reporting reads
* fact table examples: sales transactions, weather measurements&#x20;
* dimension table examples: client info, seller info, product info

## Snowflake Schema (OLAP)

* snowflake schema is a generalization of a star schema
* similar to star schema, but dimensions are [normalized](https://en.wikipedia.org/wiki/Normalization_\(database\)) into multiple related tables (linked through FKs)
* some database developers compromise by creating an underlying snowflake schema with [views](https://en.wikipedia.org/wiki/View_\(database\)) built on top of it that perform many of the necessary joins to simulate a star schema.
* Some [OLAP](https://en.wikipedia.org/wiki/OLAP) multidimensional database modeling tools are optimized for snowflake schemas.[\[3\]](https://en.wikipedia.org/wiki/Snowflake_schema#cite_note-3)
* [Normalizing](https://en.wikipedia.org/wiki/Database_normalization) attributes results in storage savings, the tradeoff being additional complexity in source query joins.