| Aspect | ETL | ELT |
|---|
| Order | Extract → Transform → Load | Extract → Load → Transform |
| Transform | Before loading (middleware) | After loading (in warehouse) |
| Best for | Traditional data warehouses | Cloud data warehouses |
| Tools | Informatica, Talend, SSIS | dbt, Spark, BigQuery SQL |
| Flexibility | Schema-on-write | Schema-on-read |
| Aspect | Data Warehouse | Data Lake | Lakehouse |
|---|
| Data | Structured | Raw (all types) | All types |
| Schema | Schema-on-write | Schema-on-read | Both |
| Format | SQL tables | Files (Parquet, JSON) | Tables + files |
| Users | Analysts | Data scientists | Both |
| Cost | Expensive (storage + compute) | Cheap storage | Optimized |
| Examples | Snowflake, Redshift | S3, ADLS | Delta Lake, Iceberg |
| Aspect | OLTP | OLAP |
|---|
| Purpose | Transaction processing | Analytics |
| Queries | Simple, frequent | Complex, aggregations |
| Data | Current, operational | Historical, analytical |
| Schema | Normalized (3NF) | Denormalized (star/snowflake) |
| Row count | Thousands-millions | Millions-billions |
| Response | Milliseconds | Seconds-minutes |
┌──────────┐
│ Fact │
│ Table │
│(sales) │
└──┬──┬──┬─┘
│ │ │
┌─────┘ │ └─────┐
▼ ▼ ▼
┌──────┐ ┌──────┐ ┌──────┐
│ Dim │ │ Dim │ │ Dim │
│ Time │ │Prod │ │Store │
└──────┘ └──────┘ └──────┘
- Fact table: Measurements (sales amount, quantity)
- Dimension tables: Context (time, product, store, customer)
- Star: One level of dimensions around fact
- Snowflake: Dimensions normalized into sub-dimensions