Storing data

Database Design

Lis Sulmont

Curriculum Manager

Structuring data

1. Structured data

Follows a schema
Defined data types & relationships

_e.g., SQL, tables in a relational database _

2. Unstructured data

Schemaless
Makes up most of data in the world

e.g., photos, chat logs, MP3

3. Semi-structured data

Does not follow larger schema
Self-describing structure

e.g., NoSQL, XML, JSON

# Example of a JSON file
"user": {
     "profile_use_background_image": true, 
     "statuses_count": 31, 
     "profile_background_color": "C0DEED", 
     "followers_count": 3066, 
     ...

Structuring data

Diagram showing the spectrum between structured and unstructured data

¹ Flower by Sam Oth and Database Diagram by Nick Jenkins via Wikimedia Commons https://commons.wikimedia.org/wiki/File:Languages_xml.png

Storing data beyond traditional databases

Traditional databases
- For storing real-time relational structured data ? OLTP
Data warehouses
- For analyzing archived structured data ? OLAP
Data lakes
- For storing data of all structures = flexibility and scalability
- For analyzing big data

Data warehouses

Optimized for analytics - OLAP
- Organized for reading/aggregating data
- Usually read-only
Contains data from multiple sources
Massively Parallel Processing (MPP)
Typically uses a denormalized schema and dimensional modeling

Data marts

Subset of data warehouses
Dedicated to a specific topic

Amazon Redshift, Google Bog Query and Azure SQL Data Warehouse

Data mart as a subset of a data warehouse

Data lakes

Store all types of data at a lower cost:
- e.g., raw, operational databases, IoT device logs, real-time, relational and non-relational
Retains all data and can take up petabytes
Schema-on-read as opposed to schema-on-write
Need to catalog data otherwise becomes a data swamp
Run big data analytics using services such as Apache Spark and Hadoop
- Useful for deep learning and data discovery because activities require so much data

Storing data

Structuring data

Structuring data

Storing data beyond traditional databases

Data warehouses

Data lakes

ETL

ELT

Let's practice!