Data Lakehouse vs Data Warehouse vs Data Lake

Data architecture has evolved to meet different analytic needs. Data Warehouses emerged in the 1980s to support business intelligence (BI) and reporting. They store structured, processed data optimized for query performance. Data Lakes arose in the 2010s to handle big data, storing raw data in its native format (structured, semi-structured, or unstructured) for data science and exploratory analytics. Data Lakehouses combine the flexibility of data lakes with the reliability and performance of data warehouses, enabling both BI and machine learning (ML) on a single platform.

Data Warehouse: Data is ingested through ETL (Extract, Transform, Load) processes. Extraction pulls data from sources, transformation cleans and structures it (e.g., applying schemas, aggregations), and loading writes it into a relational database optimized for queries. Storage uses columnar formats (e.g., columnstore indexes in Azure Synapse dedicated SQL pool) for compression and fast scan rates. Queries are executed using SQL engines that leverage indexing, partitioning, and materialized views. Typical latency: hours (batch processing).

Data Lake: Data is ingested in its raw form (e.g., CSV, JSON, Parquet, images) to cheap, scalable object storage like Azure Data Lake Storage Gen2 (ADLS Gen2). A schema-on-read approach is used: the schema is applied at query time, not at write time. Processing engines like Apache Spark (via Azure Databricks or Synapse Spark pools) read the data, apply transformations, and produce results. Data lakes support batch, streaming, and interactive analytics. Storage is hierarchical (ADLS Gen2) with POSIX-like access control lists (ACLs).

Data Lakehouse: Combines data lake storage (ADLS Gen2) with a metadata layer (e.g., Delta Lake, Apache Iceberg) that provides ACID transactions, schema enforcement, and versioning. The storage layer remains cheap and scalable, while the metadata layer enables warehouse-like features: time travel (querying previous versions), upserts (merge operations), and data quality constraints. Compute engines (Spark, SQL) access the same data concurrently. Delta Lake, used in Azure Databricks and Synapse, is the most common implementation.

Create an external table in Synapse using PolyBase:

-- Create external data source
CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (
    LOCATION = 'abfss://container@storageaccount.dfs.core.windows.net',
    CREDENTIAL = MyCredential
);

-- Create external file format
CREATE EXTERNAL FILE FORMAT MyFileFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

-- Create external table
CREATE EXTERNAL TABLE dbo.SalesExternal (
    SaleID INT,
    ProductID INT,
    SaleDate DATE,
    Amount DECIMAL(10,2)
)
WITH (
    LOCATION = '/sales/',
    DATA_SOURCE = MyDataSource,
    FILE_FORMAT = MyFileFormat
);

Verify Delta Lake table history:

-- In Databricks or Synapse Spark pool
DESCRIBE HISTORY sales_table;
-- Shows operations: write, delete, merge, etc.

Check ADLS Gen2 hierarchical namespace:

az storage account show --name mystorageaccount --resource-group myrg --query "isHnsEnabled"

A large retailer uses Azure Databricks with Delta Lake on ADLS Gen2 to unify their data. Previously, they had a data warehouse for sales reports and a data lake for clickstream logs. Analysts had to use separate tools. With a lakehouse, they ingest point-of-sale transactions (structured) and web clickstream (semi-structured) into Delta tables. They run BI reports using Databricks SQL and train recommendation models using Spark ML. Issues arise when Delta table optimization is neglected: small file accumulation (from streaming) degrades query performance. They mitigate by running OPTIMIZE and VACUUM nightly. They set Delta log retention to 7 days to control storage costs.

A bank uses Azure Synapse dedicated SQL pool for regulatory reporting. Data from core banking systems is extracted nightly, transformed in Azure Data Factory (cleaning, aggregating, applying business rules), and loaded into the warehouse. The warehouse uses hash-distributed fact tables on account ID to minimize data movement. Reports are generated via Power BI with DirectQuery. Common misconfiguration: using round-robin distribution for large fact tables, causing excessive data shuffling. The fix: switch to hash distribution on the join key. Performance considerations: scaling up the SQL pool during batch loads (e.g., from DW1000c to DW2000c) and scaling down after.

An energy company collects sensor data from wind turbines (JSON, 10 TB/day) into ADLS Gen2. They use Azure Data Lake as a landing zone for raw telemetry. Data scientists access the raw files with Azure Databricks to build anomaly detection models. The lake's schema-on-read allows them to experiment with different schemas without reprocessing. Problems arise when many small files are created (e.g., 10,000 files per hour), overwhelming the NameNode in HDFS-like namespace. They implement a file compaction job using Spark to coalesce files into 256 MB chunks. They also use Azure Data Lake Storage tiering: data older than 30 days moves to cool tier, older than 1 year to archive.