Data Warehouses, Data Lakes, ETL, ELT and Airflow Pipelines

data warehouse and data lake

A warehouse stores curated modeled analytical data, while a data lake retains diverse data in scalable object or distributed storage with governance.

ETL and ELT

ETL transforms data before warehouse loading, while ELT loads source-shaped data first and performs transformations within the target platform.

data ingestion

Data ingestion transfers source records into a platform in batch or streams while preserving identifiers, timestamps.

data quality layer

A data quality layer validates schema, completeness, uniqueness, ranges, relationships and freshness before records become trusted analytical inputs.

medallion architecture

Medallion architecture organizes lake data into raw bronze, cleaned and conformed silver and business-ready gold layers with traceable promotion rules.

Airflow DAG

An Airflow directed acyclic graph declares tasks and dependencies for a workflow schedule; each DAG run represents one logical interval or trigger.

operator and task

An Airflow operator is a reusable task template, while a task is the operator bound to a DAG and a task instance is its execution in one DAG run.

scheduler and executor

A scheduler divides and assigns work; executors run the assigned tasks and report results, metrics and failures.

idempotent pipeline

An idempotent pipeline can safely repeat a logical run without creating duplicate final effects, usually through stable keys.

pipeline observability

Pipeline observability combines run state, latency, throughput, freshness, lineage.

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

Modern Data Stack

What is the Modern Data Stack?

A set of cloud-native tools for data engineering:

Ingestion:      Fivetran, Airbyte  → extract from sources
Storage:        Snowflake, BigQuery, Redshift → cloud DWH
Transformation: dbt (data build tool) → SQL transformations
Orchestration:  Apache Airflow → schedule and monitor pipelines
BI:             Looker, Tableau, Metabase → visualization

DBT (Data Build Tool)

dbt allows data transformation using SQL models:

-- models/marts/sales_summary.sql
SELECT
    date_trunc('month', order_date) as month,
    product_category,
    SUM(revenue) as total_revenue,
    COUNT(*) as order_count,
    AVG(revenue) as avg_order_value
FROM {{ ref('stg_orders') }}   -- reference another dbt model
WHERE order_status = 'completed'
GROUP BY 1, 2
ORDER BY 1, 3 DESC

Apache Airflow

Airflow orchestrates complex workflows as DAGs (Directed Acyclic Graphs):

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime

def extract(): print("Extracting data...")
def transform(): print("Transforming data...")
def load(): print("Loading to DWH...")

with DAG("etl_pipeline", schedule_interval="@daily",
         start_date=datetime(2024, 1, 1)) as dag:

t1 = PythonOperator(task_id="extract", python_callable=extract)
    t2 = PythonOperator(task_id="transform", python_callable=transform)
    t3 = PythonOperator(task_id="load", python_callable=load)

t1 >> t2 >> t3   # define dependencies

Data Lake vs Data Warehouse

Data Lake

A Data Lake stores all data in its raw, native format — structured, semi-structured and unstructured.

"Store everything; process when needed"

Feature	Description
Storage	Raw data (any format)
Schema	Schema-on-read (define when querying)
Users	Data scientists, ML engineers
Technologies	HDFS, Amazon S3, Azure Data Lake
Cost	Very low (commodity storage)

Risk: Data Lake can become a Data Swamp without proper governance (unusable raw data).

Data Warehouse

Feature	Description
Storage	Processed, cleaned, structured data
Schema	Schema-on-write (defined at load time)
Users	Business analysts, BI tools
Technologies	Snowflake, BigQuery, Redshift
Cost	Higher (compute + storage)

Lakehouse

Lakehouse = Data Lake + Data Warehouse (modern approach):

• Store raw data in Data Lake
• Apply DWH features (ACID, schema enforcement) on top
• Technologies: Delta Lake (Databricks), Apache Iceberg, Apache Hudi

Data Warehouse

What is a Data Warehouse?

A Data Warehouse (DWH) is a central repository integrating data from multiple sources for:

• Business intelligence (BI)
• Reporting and analytics
• Decision support

Key characteristics:

• Subject-oriented (organized by topic: sales, finance, HR)

Integrated (combines data from multiple systems)

• Non-volatile (data is not updated; only new data added)
• Time-variant (historical data matters)

Data Warehouse Architecture

Data Warehouse Schemas

Star Schema

                   FACT TABLE
                   (Sales_Fact)
                   +----------+
                   | sales_id |
                   | date_id  | ──> Date_Dim  (Jan, Feb, daily...)
                   | prod_id  | ──> Product_Dim (Name, Category, Price)
                   | cust_id  | ──> Customer_Dim (Name, City, Segment)
                   | store_id | ──> Store_Dim  (Location, Manager)
                   | amount   |
                   | quantity |
                   +----------+

Star Schema: One fact table; dimension tables connect directly. Simple; fast queries; denormalized

Snowflake Schema

Extension of Star Schema where dimension tables are normalized into sub-dimensions.

Product_Dim
  └── Category_Dim
        └── Department_Dim

Snowflake Schema: More normalized; reduces redundancy; more joins needed.

Data Engineering Lifecycle

The Data Engineering Lifecycle

Source → Ingestion → Storage → Transformation → Serving

Stage 1: Source

Stage 2: Ingestion

Moving data from sources to storage:

• Batch Ingestion — periodic bulk loads (daily, hourly)
• Stream Ingestion — real-time continuous data flow

Tools: Apache Kafka, AWS Kinesis, Apache Nifi, Fivetran, Airbyte

Stage 3: Storage

Where data is stored:

• Data Lake — raw, unprocessed data (S3, Azure Data Lake, HDFS)

Data Warehouse — processed, structured analytical data (Snowflake, BigQuery, Redshift)

NoSQL — flexible structured data (MongoDB, Cassandra)

Stage 4: Transformation

Converting raw data to useful form:

• Cleaning — remove duplicates, handle nulls

Enrichment — add additional context

• Aggregation — summarize data

Normalization/Denormalization — structure for analytics

Tools: Apache Spark, dbt, AWS Glue

Stage 5: Serving

Delivering data to end users:

• BI Dashboards — Tableau, Power BI, Looker
• Reports — scheduled automated reports
• ML Models — trained and deployed models
• APIs — serve data to applications

Data Quality and Governance

Data Quality Dimensions

Dimension	Description	Example
Accuracy	Data is correct	Customer age = 28 (not 228)
Completeness	No missing values	All orders have shipping dates
Consistency	Same data across systems	Customer name same in CRM and DWH
Timeliness	Data is up-to-date	Yesterday's sales in DWH today
Uniqueness	No duplicate records	Each customer has one ID
Validity	Data follows rules	Email has @ and domain

Data Governance

Data Governance is the framework for managing data availability, usability, integrity and security.

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 Mart

What is a Data Mart?

A Data Mart is a subset of a Data Warehouse focused on a specific business function or department.

Data Warehouse (entire enterprise data)
         |
   +-----+-----+------+
   |           |       |
Sales DM   Finance DM  HR DM
(sales team) (finance)  (HR team)

Data Warehouse vs Data Mart

Feature	Data Warehouse	Data Mart
Scope	Enterprise-wide	Departmental
Size	TB to PB	GB to TB
Subject	All subjects	Single subject (sales, finance)
Build time	Months to years	Weeks to months
Users	All analytical users	Specific department
Update frequency	Less frequent	More frequent
Cost	Very high	Lower

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 |
+----------+         +----------+         +----------+