> For the complete documentation index, see [llms.txt](https://ankit-apdc.gitbook.io/system-design/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ankit-apdc.gitbook.io/system-design/big-data/edw/edw-concepts.md).

# EDW Concepts

## MPP (massively parallel processing) Database 

* <https://www.sisense.com/glossary/mpp-database/>

## Table Distribution Options

#### Reference

* <https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute>
* <https://techcommunity.microsoft.com/t5/datacat/choosing-hash-distributed-table-vs-round-robin-distributed-table/ba-p/305247>
*

### Hash Distribution

#### Hash Function and Properties

* A [hash function](https://en.wikipedia.org/wiki/Hash_function) is any function that can be used to map data of arbitrary size to fixed-size
* A hash function is a mathematical function that converts a input value into another compressed numerical value.&#x20;
* The input to the hash function is of arbitrary length but output is always of fixed length.
* Properties
  1. Efficiently computable.
  2. Should uniformly distribute the keys (Each table position equally likely for each key)
* <https://www.tutorialspoint.com/cryptography/cryptography_hash_functions.htm>

#### When to use Hash Function for Distribution

*

### Round Robin Distribution

### Replicated

## Data Movement

* **ShuffleMoveOperation**: Redistributes data from one distributed table to another distributed table, changing the distribution column.
* **PartitionMoveOperation**: Data moved from distributions to Control Node. Usually for Aggregations.
* **BroadcastMoveOperation**: When a distributed table needs to become replicated for join compatibility
* **TrimMoveOperation**: When a replicated table needs to become distributed
* **MoveOperationData**: Moved from Control Node back to Compute Nodes resulting in a replicated table for further processing.
* **RoundRobinMoveOperation**: Redistributes data to Round Robin Table.

## Statistics

Reference Links

* <https://www.red-gate.com/simple-talk/databases/sql-server/learn/statistics-in-sql-server/>
* <https://www.sqlshack.com/sql-server-statistics-and-how-to-perform-update-statistics-in-sql/#:~:text=SQL%20Server%20statistics%20are%20essential%20for%20the%20query,cardinality%29.%20The%20query%20optimizer%20should%20be%20updated%20regularly.>
* <https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/sql-server-statistics-basics/>

## Optimization with Index

* <https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?toc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Ftoc.json&bc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Fbreadcrumb%2Ftoc.json&view=sql-server-ver16&preserve-view=true&viewFallbackFrom=azure-sqldw-latest>

#### Reference Links

* <https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver16>
*

### Clustered Column Index

Instead of storing an entire row or rows in a page, one column from many rows is stored in that page. It is this difference in architecture that gives the columnstore index a very high level of compression along with reducing the storage footprint and providing massive improvements in read performance.

Relevant Links:

* <https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/what-are-columnstore-indexes/>
* <https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-ver16>
* [https://www.patrickkeisler.com/2014/04/what-is-non-clustered-columnstore-index.htm](https://www.patrickkeisler.com/2014/04/what-is-non-clustered-columnstore-index.html)
* <https://www.tutorialsteacher.com/sqlserver/indexes#:~:text=%20Create%20a%20Clustered%20Index%20using%20Table%20Designer,table%20name%20from%20the%20File%20menu.%20More%20>

### Heap

* <https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index>
*

### Clustered

### Secondary