Loading…
Loading…
This lesson covers data normalization techniques and methods for integrating multiple datasets for comprehensive analysis.
Data normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves structuring data according to a series of normal forms.
Benefits of Normalization:
Trade-offs:
Definition: A table is in 1NF if:
Key Principles:
Example - Before 1NF:
OrderID | Customer | Products | Quantities
1 | John | Book, Pen | 2, 5
2 | Mary | Laptop | 1
Example - After 1NF:
OrderID | Customer | Product | Quantity
1 | John | Book | 2
1 | John | Pen | 5
2 | Mary | Laptop | 1
Definition: A table is in 2NF if:
Key Concepts:
Example - Before 2NF:
OrderDetailID | OrderID | ProductID | ProductName | Quantity | Price
1 | 101 | P001 | Laptop | 2 | 999
2 | 101 | P002 | Mouse | 1 | 25
3 | 102 | P001 | Laptop | 1 | 999
Problem: ProductName and Price depend only on ProductID, not the full composite key (OrderID, ProductID)
Example - After 2NF:
OrderDetails:
OrderDetailID | OrderID | ProductID | Quantity
1 | 101 | P001 | 2
2 | 101 | P002 | 1
3 | 102 | P001 | 1
Products:
ProductID | ProductName | Price
P001 | Laptop | 999
P002 | Mouse | 25
Definition: A table is in 3NF if:
Key Concepts:
Example - Before 3NF:
EmployeeID | Name | DepartmentID | DepartmentName | Manager
E001 | John | D001 | IT | Jane
E002 | Mary | D002 | HR | Bob
E003 | Tom | D001 | IT | Jane
Problem: DepartmentName and Manager depend on DepartmentID, not directly on EmployeeID
Example - After 3NF:
Employees:
EmployeeID | Name | DepartmentID
E001 | John | D001
E002 | Mary | D002
E003 | Tom | D001
Departments:
DepartmentID | DepartmentName | Manager
D001 | IT | Jane
D002 | HR | Bob
Boyce-Codd Normal Form (BCNF):
Fourth Normal Form (4NF):
Fifth Normal Form (5NF):
Step-by-Step Approach:
Data aggregation and transformation are essential processes for preparing data for analysis and reporting.
Definition: The process of combining multiple data points into a single summary value.
Types of Aggregation:
Mathematical Aggregations:
Statistical Aggregations:
Time-based Aggregations:
SQL Aggregation Examples:
-- Basic aggregations
SELECT
department,
COUNT(*) as employee_count,
AVG(salary) as avg_salary,
MAX(salary) as max_salary,
MIN(salary) as min_salary
FROM employees
GROUP BY department;
-- Time-based aggregation
SELECT
DATE(order_date) as order_day,
COUNT(*) as daily_orders,
SUM(amount) as daily_revenue
FROM orders
GROUP BY DATE(order_date)
ORDER BY order_day;
-- Window functions for moving averages
SELECT
order_date,
revenue,
AVG(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as moving_avg_7days
FROM daily_revenue;
Definition: The process of converting data from one format or structure to another.
Types of Transformations:
Structural Transformations:
Data Type Transformations:
Value Transformations:
Transformation Examples:
Pivoting Data:
# Before pivoting
Date | Product | Sales
2023-01-01 | Laptop | 1000
2023-01-01 | Mouse | 100
2023-01-02 | Laptop | 1200
2023-01-02 | Mouse | 150
# After pivoting
Date | Laptop_Sales | Mouse_Sales
2023-01-01 | 1000 | 100
2023-01-02 | 1200 | 150
Normalization:
# Min-Max Normalization
normalized_value = (value - min_value) / (max_value - min_value)
# Z-Score Standardization
z_score = (value - mean) / standard_deviation
Categorical Encoding:
# One-Hot Encoding
Category | Encoded_Value
Red | [1, 0, 0]
Green | [0, 1, 0]
Blue | [0, 0, 1]
# Label Encoding
Category | Encoded_Value
Red | 0
Green | 1
Blue | 2
ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) are two approaches to data integration with different advantages and use cases.
Definition: Extract data from sources, transform it to fit operational needs, then load it into the target system.
ETL Workflow:
Extract → Transform → Load
Steps:
Advantages:
Disadvantages:
ETL Use Cases:
ETL Tools:
Definition: Extract data from sources, load it into the target system, then transform it using the target system's capabilities.
ELT Workflow:
Extract → Load → Transform
Steps:
Advantages:
Disadvantages:
ELT Use Cases:
ELT Tools:
| Aspect | ETL | ELT |
|---|---|---|
| Processing Order | Transform before load | Load before transform |
| Data Quality | High (cleansed before load) | Variable (cleansed after load) |
| Performance | Optimized for queries | Optimized for loading |
| Scalability | Limited by ETL servers | Scales with target system |
| Latency | Higher | Lower |
| Storage | Efficient (transformed data) | Higher (raw + transformed) |
| Complexity | Higher | Lower |
| Cost | Higher infrastructure | Lower infrastructure |
Consider ETL when:
Consider ELT when:
Data integration from multiple sources is a common challenge in data analytics, requiring careful planning and execution.
Structured Data Sources:
Semi-structured Data Sources:
Unstructured Data Sources:
Technical Challenges:
Data Quality Challenges:
Semantic Challenges:
Vertical Integration:
Horizontal Integration:
Temporal Integration:
Geographic Integration:
Join Operations:
-- Inner Join: Matching records only
SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.id;
-- Left Join: All records from left table
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id;
-- Full Outer Join: All records from both tables
SELECT a.*, b.*
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;
Union Operations:
-- Union: Combine and remove duplicates
SELECT name, email FROM customers
UNION
SELECT name, email FROM prospects;
-- Union All: Combine without removing duplicates
SELECT name, email FROM customers
UNION ALL
SELECT name, email FROM prospects;
Lookup Operations:
# Dictionary-based lookup
customer_lookup = {
'C001': {'name': 'John', 'tier': 'Gold'},
'C002': {'name': 'Mary', 'tier': 'Silver'}
}
# API-based lookup
def get_customer_info(customer_id):
response = requests.get(f'/api/customers/{customer_id}')
return response.json()
Schema Mapping:
Record Matching:
Matching Algorithms:
# Exact matching
if record1.id == record2.id:
match = True
# Fuzzy matching using Levenshtein distance
from difflib import SequenceMatcher
similarity = SequenceMatcher(None, str1, str2).ratio()
if similarity > 0.8:
match = True
# Probabilistic matching
def calculate_match_score(record1, record2):
score = 0
if record1.name == record2.name:
score += 0.4
if record1.email == record2.email:
score += 0.4
if abs(record1.age - record2.age) <= 1:
score += 0.2
return score
Source Priority:
Timestamp-Based:
Quality-Based:
Manual Resolution:
Planning Phase:
Implementation Phase:
Maintenance Phase:
Scenario: Integrating customer data from CRM, e-commerce, and support systems
Step 1: Data Extraction
# Extract from CRM
crm_data = extract_from_crm_api()
# Extract from e-commerce
ecommerce_data = extract_from_database('ecommerce')
# Extract from support system
support_data = extract_from_support_tickets()
Step 2: Data Transformation
# Standardize formats
crm_data = standardize_dates(crm_data)
ecommerce_data = normalize_phone_numbers(ecommerce_data)
support_data = clean_text_fields(support_data)
# Apply business rules
crm_data = apply_customer_tiers(crm_data)
ecommerce_data = calculate_customer_lifetime_value(ecommerce_data)
Step 3: Data Integration
# Match customers across systems
matched_customers = match_customers(crm_data, ecommerce_data, support_data)
# Resolve conflicts
resolved_customers = resolve_conflicts(matched_customers)
# Create unified view
unified_customer_data = create_unified_view(resolved_customers)
Step 4: Data Loading
# Load to data warehouse
load_to_data_warehouse(unified_customer_data)
# Update master customer table
update_master_customer_table(unified_customer_data)
# Create analytics tables
create_analytics_tables(unified_customer_data)
Challenge: Retail chain with 50 stores, e-commerce platform, and mobile app
Data Sources:
Integration Solution:
Results:
In the next lesson, we'll explore databases and SQL basics to understand how to work with normalized and integrated data effectively.