ETL vs ELT; Data Cleansing and Transformation; Data Modeling

ETL vs ELT

ETL — Extract, Transform, Load

Process:

Source Data → EXTRACT → TRANSFORM (outside warehouse) → LOAD into DWH

When ETL?

When transformation is complex
When target system has limited compute

Traditional data warehouse era

When data governance requires transformation before storage

ELT — Extract, Load, Transform

Process:

Source Data → EXTRACT → LOAD into DWH (raw) → TRANSFORM (inside DWH)

When ELT?

Modern cloud warehouses (BigQuery, Snowflake) with massive compute
Need to keep raw data available
Transformations defined later
Data Lake approach

ETL vs ELT Comparison

Feature	ETL	ELT
Transformation location	External ETL server	Inside target system
Order	Extract → Transform → Load	Extract → Load → Transform
Data in target	Clean/processed	Raw + Processed
Speed	Slower (external transform)	Faster (utilize DWH power)
Cloud-native	No	Yes
Flexibility	Less	More
Tools	SSIS, Informatica, Talend	dbt, Spark, cloud-native
Era	Traditional	Modern

Data Cleansing and Transformation

Data Cleansing

Data cleansing / data cleaning involves detecting and correcting corrupt, inaccurate or irrelevant data.

Common Data Quality Issues:

Issue	Example	Solution
Missing Values	Age field is NULL	Imputation (mean, mode, median) or drop
Duplicates	Same customer twice	Deduplication (keep one record)
Incorrect format	Date stored as string	Convert to proper date type
Outliers	Salary = $0 or $1 billion	Investigate and fix or remove
Inconsistent encoding	"Male", "male", "M", "m"	Standardize to one format
Extra spaces	" Alice "	Trim whitespace
Wrong data types	Age stored as float	Cast to integer
Invalid values	Age = -5	Validate and correct

Data Transformation Types

Transformation	Description	Example
Mapping	Convert from one format to another	"M" → "Male"
Filtering	Remove unwanted rows/columns	Remove test accounts
Aggregation	Summarize data	Sum of daily sales
Joining	Combine multiple datasets	Customer info + Order data
Splitting	Split one column into multiple	FullName → FirstName + LastName
Normalization	Scale values to standard range	Salary 0-1 scale
Enrichment	Add data from external sources	Add city from zip code

Data Modeling

What is Data Modeling?

Data modeling is the process of designing how data is organized, stored and related in a database.

Types of Data Models

Model	Description	Use
Conceptual	High-level entities and relationships	Business communication
Logical	Detailed entities, attributes and relationships	Database design
Physical	Actual database tables, columns, data types	Implementation

ER Diagram (Entity-Relationship)

+----------+         +----------+         +----------+
| Customer |---1---<| Order    |>---1---| Product  |
|----------|         |----------|         |----------|
| cust_id  |         | order_id |         | prod_id  |
| name     |         | cust_id  |         | name     |
| email    |         | date     |         | price    |
| city     |         | total    |         | category |
+----------+         +----------+         +----------+