Data Warehouses and Data Lakes

Overview of Data Warehouses and Data Lakes

• Data warehouses and data lakes are essential components in modern data architectures.
• Data warehouses and data lakes serve different purposes but are often used together to manage and analyze data.

Data Warehouse

• A data warehouse is a centralized system for storing, managing, and analyzing structured data.
• Data warehouses are optimized for reporting, business intelligence (BI), and analytics.
• Data warehouses are designed like organized libraries where data is cleaned, transformed, and stored for querying and generating insights.

Data Warehouse Key Characteristics

• Data is organized into tables with predefined schemas (rows and columns).
• Examples include sales records, customer data, and financial metrics.
• Schema-on-Write: Data is transformed and structured via ETL processes before loading into the warehouse, ensuring consistency and usability.
• Uses relational database technologies (e.g., SQL) designed for fast querying and aggregation.
• Supports BI tools like Tableau or Power BI.
• Data warehouses store large volumes of historical data for trend analysis and decision-making.
• Examples of data warehouses: Amazon Redshift, Google BigQuery, Snowflake, Microsoft Azure SQL Data Warehouse.

Data Warehouse Use Cases

• Generating sales reports for executives.
• Analyzing customer behavior over time.
• Supporting dashboards with precomputed metrics.

Data Warehouse Pros

• High performance for structured queries.
• Reliable and consistent data due to preprocessing.
• Great for business users who need ready-to-use data.

Data Warehouse Cons

• Expensive to scale with massive, raw data.
• Less flexible for unstructured or semi-structured data.
• Requires significant upfront design and maintenance.

Data Lake

• A data lake is a centralized repository that stores raw, unprocessed data in its native format (structured, semi-structured, or unstructured).
• It is like a vast "lake" where data flows in from various sources and sits until needed for analysis.

Data Lake Key Characteristics

• Raw Data: Data is stored as-is, without immediate transformation.
• Data can include JSON files, logs, images, videos, or database dumps.
• Schema-on-Read: The structure is applied when the data is accessed, providing flexibility but requiring more processing at query time.
• Built on cost-effective storage solutions (e.g., object storage like AWS S3).
• Data lakes can handle massive volumes of diverse data.
• Ideal for data science, machine learning, and big data processing.
• Raw data is processed on-demand using tools like Apache Spark or Hadoop.
• Examples of data lakes: AWS S3, Azure Data Lake, Google Cloud Storage.

Data Lake Use Cases

• Training machine learning models with raw datasets.
• Storing IoT sensor data for real-time analysis.
• Exploring unstructured data like social media feeds or text logs.

Data Lake Pros

• Highly flexible, supporting all data types.
• Cost-effective for storing large volumes of raw data.
• Suited for data scientists and engineers who need unprocessed data.

Data Lake Cons

• Can become a "data swamp" if not managed properly (poor metadata or governance).
• Requires more technical expertise to process and query.
• Slower for traditional BI unless paired with additional tools.

Key Differences Between Data Warehouse and Data Lake

• Data Type: Data Warehouse = Structured, Data Lake = Structured, semi-structured, unstructured.
• Schema: Data Warehouse = Schema-on-write (predefined), Data Lake = Schema-on-read (on-demand).
• Processing: Data Warehouse = ETL (transform before load), Data Lake = ELT (transform after load).
• Users: Data Warehouse = Business analysts, BI users, Data Lake = Data scientists, engineers.
• Cost: Data Warehouse = Higher (compute-heavy), Data Lake = Lower (storage-heavy).
• Purpose: Data Warehouse = Reporting, dashboards, Data Lake = Advanced analytics, ML.

How Data Warehouses and Data Lakes Work Together

• Organizations often use both data lakes and data warehouses.
• Data Lake as "Raw Storage": Collects all incoming data in its original form.
• Data Warehouse as "Refined Layer": Pulls curated, structured data from the lake (via ETL/ELT pipelines) for business reporting.
• Retail Example:
  • A retailer collects raw clickstream data from its website into a data lake.
  • Data engineers process the raw data and load it into a data warehouse.
  • Business analysts query the warehouse for daily reports, and data scientists use the lake for predictive modeling.

Real-World Analogy

• Data Warehouse: Resembles a neatly organized filing cabinet for quickly finding specific documents.
• Data Lake: Resembles a giant storage shed where everything is dumped in boxes which requires digging and sorting to find anything.

Quick Recap

• Data Warehouse: Structured, preprocessed, analytics-ready, great for BI.
• Data Lake: Raw, flexible, scalable, ideal for advanced use cases.

Data Warehouse Architecture

• Data warehouses consolidate data from multiple sources, transform it into a consistent format, and make it readily available for querying and reporting.

Data Warehouse Architecture: Data Sources

• Data comes from operational systems such as CRM, ERP, and transactional databases (e.g., MySQL or Oracle).
• Example: Sales data from a point-of-sale system or customer info from a marketing platform.

Data Warehouse Architecture: ETL Pipeline

• Extract: Raw data is pulled from various sources.
• Transform: Data is cleaned, normalized, and structured (e.g., converting dates to a standard format, removing duplicates).
• Load: Processed data is stored into the warehouse.
• Tools: Apache Airflow, Talend, Informatica, or cloud-native options like AWS Glue are used.

Data Warehouse Architecture: Storage Layer

• Uses a relational database management system (RDBMS) optimized for columnar storage or massively parallel processing (MPP).
• Data is organized into tables with schemas (fact tables for metrics like sales, dimension tables for details like time or location).
• Example: Snowflake uses a cloud-native architecture separating compute and storage for scalability.

Data Warehouse Architecture: Query Engine

• SQL-based querying allows for fast aggregations (e.g., SELECT SUM(sales) FROM orders GROUP BY region).
• Often paired with BI tools (e.g., Looker, Power BI) for visualization.

Data Warehouse Architecture: Access Layer

• End users (analysts, executives) access the data via dashboards, reports, or ad-hoc queries.

Data Warehouse Key Design Concepts

• Star Schema: A central fact table (e.g., sales transactions) linked to dimension tables (e.g., customer, product, time).
• Snowflake Schema: A more normalized version of the star schema, reducing redundancy but increasing complexity.
• Data Marts: Subsets of the warehouse tailored for specific departments (e.g., a marketing data mart).

Data Warehouse Practical Example

• A retail chain extracts data daily from stores, online sales, and inventory systems.
• ETL processes aggregate sales by region and product, loading it into a warehouse like Amazon Redshift.
• Analysts query the data to find top-performing stores or seasonal trends.

Data Warehouse Tools

• Cloud-Based: Snowflake, Google BigQuery, Azure Synapse Analytics.
• On-Premises: Teradata, Oracle Exadata.
• Open-Source: Apache Hive (less common for pure warehousing today).

Data Warehouse Challenges

• Cost: Scaling compute and storage can be expensive.
• Rigidity: Changing schemas after setup is time-consuming.
• Latency: ETL processes can delay data availability.

Data Lake Architecture

• Data lakes store massive amounts of raw data (structured, semi-structured, or unstructured) and process it on-demand for machine learning and real-time analytics.

Data Lake Architecture: Data Sources

• Various data sources are used (IoT streams, social media feeds, log files, videos, database exports, etc.).
• Example: Raw JSON logs from a web app or CSV files from sensors.

Data Lake Architecture: Ingestion Layer

• Data is ingested as-is, often using streaming (e.g., Apache Kafka) or batch processes (e.g., AWS Data Pipeline).
• No immediate transformation is performed, and data is dumped in.

Data Lake Architecture: Storage Layer

• Built on scalable, low-cost object storage (e.g., AWS S3, Azure Blob Storage).
• Data is stored in its native format, organized into folders or partitions.
• Metadata catalogs (e.g., AWS Lake Formation, Apache Hive Metastore) track what’s where.

Data Lake Architecture: Processing Layer

• Tools like Apache Spark, Databricks, or Presto process the raw data when needed.
• Schema-on-read is used to define the structure at query time (e.g., parsing JSON into columns dynamically).

Data Lake Architecture: Consumption Layer

• Data scientists use it for ML models (e.g., TensorFlow, PyTorch).
• Engineers refine it into a warehouse or build real-time apps.
• Analysts might access curated subsets via tools like Apache Superset.

Data Lake Key Design Concepts

• Zones:
  • Raw Zone: Untouched ingested data.
  • Processed Zone: Cleaned or transformed data.
  • Curated Zone: Ready-to-use datasets for specific purposes.
• Data Governance: Metadata tagging and access controls are critical to avoid chaos.
• File Formats: Optimized formats like Parquet or ORC improve performance over raw CSV or JSON.

Data Lake Practical Example

• A streaming platform like Netflix streams raw user activity logs into a data lake on AWS S3.
• Data engineers use Spark to process the logs into recommendation models.
• Analysts pull the curated data into a warehouse for quarterly reporting.

Data Lake Tools

• Storage: AWS S3, Google Cloud Storage, Hadoop HDFS.
• Processing: Apache Spark, Flink, Databricks.
• Cataloging: AWS Glue Data Catalog, Delta Lake.

Data Lake Challenges

• Data Swamps: results from a lack of governance
• Complexity: Requires skilled engineers to process raw data.
• Security: Managing access to diverse datasets is tricky.

Comparing Workflow: Data Warehouse

1. Define schema.
2. Build ETL pipeline.
3. Load structured data.
4. Query for reports.

Comparing Workflow: Data Lake

1. Ingest raw data.
2. Store in native format.
3. Process on-demand.
4. Use for analytics/ML.

When To Use Which

• Data Warehouse: Provides reliable, structured data for consistent reporting (e.g., “What were our Q1 sales?”).
• Data Lake: Handles diverse, raw data for exploration or advanced analytics (e.g., “Can we predict customer churn?”).

Modern Trends

• Lakehouse Architecture: Combines the best features of both data lakes and data warehouses.
• Examples: Databricks Delta Lake, Snowflake’s external tables.
• Cloud-Native: using cloud providers for flexibility and cost savings
• ELT over ETL: Load raw data first, transform later.

Hands-On Analogy

• Data Warehouse: A chef prepares a meal and serves it ready-to-eat.
• Data Lake: Provide raw ingredients and allows customized cooking.

Key Features Summarized

• Data Warehouse (DW): Structured, centralized, designed for analytical processing and BI.
  • Stores well-organized, pre-processed data.
  • Implements schema-on-write.
  • Optimized for OLAP for reporting and analytics.
  • Ensures high data quality through cleansing, transformation, and integrity.
• Data Lake: Scalable, flexible, holds vast amounts of raw and unstructured data.
  • Stores all data types with schema-on-read for flexibility.
  • Optimized for AI and big data with the risk of becoming a data swamp without governance.

Key Differences Summarized

Conclusion

• A hybrid approach combines the structured querying power of a Data Warehouse with the flexibility of a Data Lake
• Databricks and Snowflake data lakehouses are examples.
• A data warehouse provides structured, fast analytics.
• A data lake provides scalable, flexible storage for big data & AI.
• A data lakehouse has qualities of both.

Schema-on-Write Definition

• the data structure is defined before the data is written or stored.
• deciding upfront how the data will be organized then transforming the data to fit that as it’s loaded.

Schema-on-Write How It Works

• 1. Predefined Structure: creates a schema (e.g., a table with columns like customer_id INT, name VARCHAR purchase_date DATE).
• 2. Transformation First: Raw data is processed via ETL to match the schema.
• 3. Ready-to-Use: Data is structured and optimized for querying once loaded.

Schema-on-Write Where It's Used

• Data Warehouses rely on schema-on-write to ensure data is clean and quey ready

Example

• a Retail company collecting sales data:

Key Points

• Raw data: Messy CSV with inconsistent formats
• Define a table: sales (customer_id INT, customer_name VARCHAR(50)
• date DATE, amount DECIMAL).
• ETL cleans standardize dates reduce Duplicates convert to decimals
• Data is ready for SQL queries

Schema- on-Write Pros

• Fast Queries: Data is pre structured to run quickly and efficiently
• Consistency : Same clean, reliable dataset for everyone
• Ease for and users:Business analyst can use SQL or BI tools

Schema-on-Write Cons

• Upfront Effort: Designing the schema and building ETL pipelines time and planning.
• Inflexibility: Any raw data change needs needs a schema to be updated
• Lost Details: Some raw data might be discarded if it doesn’t fit the schema.

Schema-on-Read definition

• Data is stored in its raw, unprocessed form, applies the structure when read or accessed.

Schema-on-Read How it Works

• Data is dumped as is, enforcing no structure
• You must define how to parse or interpret it
• The same raw data can be structured many ways
• data lakes are use schema-on-read to handle diverse raw data , ex: AWS S3, Azure Data Lake, Hadoop HDFS.

Real World Example

• Store in S3 as-is with no transformation.
• Use Apache Spark to read it:
• Apply schemas when they cast or parse.

Pros

• can store any type structured data
• No upfront processing allows massive volumes quickly
• revisits the original data

Cons

• Processing takes more computing power and time
• User needs technical skills in Python or SQL
• Users might interpret data differently

Analogy

• Schema-on-Write: A librarian organizes books into labeled shelves
• Schema-on-Read: A storage unit full of boxes needs to be sorted

Implications

• Use Schema-on-Write when needing reliable data
• Use Schema-on-Read when exploring raw data
• data warehouse ensures a CEO gets a consistent dashboard
• A data lake lets machine learning lets experiment with raw data