1. Home
  2. Course Detail

You are now watching:

SQL Database Introduction / of SQL Database Introduction

Database vs Data Warehouse vs Data Lake vs Data Lakehouse

1️⃣ Database

What it is: Stores current / transactional data of applications.

  • Structured data
  • Fast read & write
  • Used for day-to-day transactions
  • Supports CRUD

Examples: Banking transactions, Orders, Employee records

Technologies: MySQL, PostgreSQL, SQL Server, Oracle, MongoDB

2️⃣ Data Warehouse

What it is: Stores historical curated data for analytics & reporting.

  • Historical data
  • Structured & cleaned
  • Optimized for analytics & BI
  • Read optimized

Examples: Revenue trends, Sales reports, Dashboards

Technologies: Redshift, Snowflake, BigQuery, Synapse

3️⃣ Data Lake

What it is: Stores raw data in any format.

  • Structured, semi-structured, unstructured
  • Cheap storage
  • Used for Big Data, ML
  • Schema on Read

Examples: Logs, JSON, Images, IoT data

Technologies: S3, ADLS, GCS, HDFS

4️⃣ Data Lakehouse

What it is: Combination of Data Lake + Data Warehouse.

  • Stores raw + structured data
  • Supports BI & ML
  • High performance
  • ACID + Governance

Examples: Unified analytics platforms

Technologies: Databricks, Delta Lake, Iceberg, Hudi

Comparison Table

Feature Database Data Warehouse Data Lake Data Lakehouse
Stores Live data Historical curated Raw Raw + Curated
Type Structured Structured Any Any
Purpose Transactions Analytics Storage + Big Data Analytics + ML
Cost Medium High Low Medium
Schema On Write On Write On Read Both
Simple Analogy
Database = Daily notebook ✔
Data Warehouse = Organized report book 📘
Data Lake = Big storage room 🗃️
Data Lakehouse = Smart storage + reporting center 🏠

📄 CSV vs 🗄️ MySQL Database vs 🏦 Redshift Data Warehouse

⚙️ Feature 📄 CSV File 🗄️ Database (MySQL) 🏦 Data Warehouse (Redshift)
🎯 Purpose Simple data storage in files Store operational / application data Store large historical business data for analytics
📏 Data Size Handling Small to medium Medium to large Very large (TBs to PBs)
🧾 Data Type Structured but loose schema Strict structured schema Structured + optimized for analytics
💾 Storage Format File (text-based) Tables in DB Columnar storage
⚡ Performance Slow for large data Fast for OLTP operations Very fast for analytical queries
📚 Use Case Exporting, sharing, storing small datasets Daily transactions, applications Business analytics, reporting, dashboards
❓ Querying Limited (Excel, scripts) SQL queries SQL + analytics optimized queries
👥 Concurrency No concurrency handling Supports multiple users Supports massive parallel users
🔐 Data Integrity No constraints Strong constraints (PK, FK) Data quality via ETL processes
📌 Indexing No indexing Indexes supported Columnar indexing + distribution keys
📈 Scaling Manual file handling Vertical & horizontal scaling Massive parallel scaling
💰 Cost Free Depends on hosting Cloud cost (pay per usage)
🧪 Examples .csv files MySQL, PostgreSQL AWS Redshift, Snowflake, BigQuery