A data lakehouse combines the best of data lakes and data warehouses — giving you cheap, scalable storage with the reliability and performance of a warehouse. In this guide, you’ll build one from scratch using open-source tools.
By the end, you’ll have a working lakehouse with raw ingestion, transformation layers, and a query-ready serving layer.
What Is a Data Lakehouse?
A data lakehouse is an architecture that sits on top of a data lake (like S3 or ADLS) but adds:
- ACID transactions — no more corrupted reads during writes
- Schema enforcement — reject bad data before it enters your tables
- Time travel — query data as it existed at any point in the past
- Indexing and caching — fast query performance without moving data to a separate warehouse
Think of it as: S3 + Delta Lake + Spark = Data Lakehouse.
Architecture Overview
Here’s the high-level architecture we’ll build:
┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ Raw Data │──▶ │ Bronze │───▶│ Silver │────▶ Gold
│ (Sources) │ │ (Raw) │ │ (Cleaned) │ (Aggregated)
└─────────────┘ └─────────────┘ └─────────────┘
CSV, JSON, Delta Lake Delta Lake Delta Lake
APIs, DBs on S3/ADLS on S3/ADLS on S3/ADLS
This follows the Medallion Architecture pattern: Bronze (raw) → Silver (cleaned) → Gold (business-ready).
Prerequisites
- A cloud account (AWS, Azure, or GCP)
- Python 3.9+
- Apache Spark 3.5+
- Delta Lake 3.x
- A cloud storage bucket (S3, GCS, or ADLS)
Step 1: Set Up Your Storage Layer
Create a bucket with the following folder structure:
s3://my-lakehouse/
├── bronze/
├── silver/
└── gold/
On AWS, create the bucket via CLI:
aws s3 mb s3://my-lakehouse
aws s3api put-object --bucket my-lakehouse --key bronze/
aws s3api put-object --bucket my-lakehouse --key silver/
aws s3api put-object --bucket my-lakehouse --key gold/
Step 2: Configure Spark with Delta Lake
Install the required packages:
pip install pyspark==3.5.0 delta-spark==3.0.0
Create a Spark session with Delta Lake support:
from pyspark.sql import SparkSession
from delta import configure_spark_with_delta_pip
builder = (
SparkSession.builder
.appName("DataLakehouse")
.config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension")
.config("spark.sql.catalog.spark_catalog", "org.apache.spark.sql.delta.catalog.DeltaCatalog")
.config("spark.hadoop.fs.s3a.access.key", "YOUR_ACCESS_KEY")
.config("spark.hadoop.fs.s3a.secret.key", "YOUR_SECRET_KEY")
)
spark = configure_spark_with_delta_pip(builder).getOrCreate()
Step 3: Build the Bronze Layer (Raw Ingestion)
The bronze layer stores raw data exactly as it arrives — no transformations, no cleaning. This is your single source of truth.
from pyspark.sql.functions import current_timestamp, lit
def ingest_to_bronze(source_path, table_name, source_format="csv"):
"""Ingest raw data into the bronze layer."""
df = (
spark.read
.format(source_format)
.option("header", "true")
.option("inferSchema", "true")
.load(source_path)
)
# Add metadata columns
df_with_metadata = (
df
.withColumn("_ingested_at", current_timestamp())
.withColumn("_source_file", lit(source_path))
)
# Write as Delta table
(
df_with_metadata.write
.format("delta")
.mode("append")
.save(f"s3://my-lakehouse/bronze/{table_name}")
)
print(f"Ingested {df_with_metadata.count()} rows into bronze.{table_name}")
# Example: ingest sales data
ingest_to_bronze("s3://raw-data/sales_2024.csv", "sales")
ingest_to_bronze("s3://raw-data/customers.json", "customers", "json")
Step 4: Build the Silver Layer (Cleaned and Validated)
The silver layer is where you clean, deduplicate, and validate data. This is the layer most analysts and data scientists query.
from pyspark.sql.functions import col, when, trim, lower
from delta.tables import DeltaTable
def bronze_to_silver_sales():
"""Clean and transform sales data from bronze to silver."""
bronze_sales = spark.read.format("delta").load(
"s3://my-lakehouse/bronze/sales"
)
silver_sales = (
bronze_sales
# Remove nulls in critical columns
.filter(col("order_id").isNotNull())
.filter(col("amount").isNotNull())
# Clean string fields
.withColumn("customer_name", trim(col("customer_name")))
.withColumn("product_category", lower(trim(col("product_category"))))
# Fix data types
.withColumn("amount", col("amount").cast("decimal(10,2)"))
.withColumn("order_date", col("order_date").cast("date"))
# Handle invalid amounts
.withColumn("amount", when(col("amount") > 0, col("amount")).otherwise(0))
# Deduplicate
.dropDuplicates(["order_id"])
# Add processing metadata
.withColumn("_processed_at", current_timestamp())
)
# Write to silver layer
(
silver_sales.write
.format("delta")
.mode("overwrite")
.option("overwriteSchema", "true")
.save("s3://my-lakehouse/silver/sales")
)
print(f"Silver sales: {silver_sales.count()} clean rows")
bronze_to_silver_sales()
Step 5: Build the Gold Layer (Business-Ready Aggregations)
The gold layer contains pre-aggregated, business-ready datasets optimized for dashboards and reporting.
from pyspark.sql.functions import sum, count, avg, month, year
def silver_to_gold_monthly_revenue():
"""Create monthly revenue summary in the gold layer."""
silver_sales = spark.read.format("delta").load(
"s3://my-lakehouse/silver/sales"
)
monthly_revenue = (
silver_sales
.groupBy(
year("order_date").alias("year"),
month("order_date").alias("month"),
"product_category"
)
.agg(
sum("amount").alias("total_revenue"),
count("order_id").alias("total_orders"),
avg("amount").alias("avg_order_value")
)
.orderBy("year", "month")
)
(
monthly_revenue.write
.format("delta")
.mode("overwrite")
.save("s3://my-lakehouse/gold/monthly_revenue")
)
print(f"Gold monthly revenue: {monthly_revenue.count()} rows")
silver_to_gold_monthly_revenue()
Step 6: Enable Time Travel and Versioning
One of the biggest advantages of a lakehouse is time travel. You can query any previous version of your data:
# Query the silver sales table as it was 2 versions ago
df_old = (
spark.read
.format("delta")
.option("versionAsOf", 2)
.load("s3://my-lakehouse/silver/sales")
)
# Or query by timestamp
df_yesterday = (
spark.read
.format("delta")
.option("timestampAsOf", "2024-01-15")
.load("s3://my-lakehouse/silver/sales")
)
# View the history of changes
delta_table = DeltaTable.forPath(spark, "s3://my-lakehouse/silver/sales")
delta_table.history().show(truncate=False)
Step 7: Add Schema Enforcement
Prevent bad data from corrupting your tables with schema enforcement:
from pyspark.sql.types import StructType, StructField, StringType, DecimalType, DateType
# Define expected schema
sales_schema = StructType([
StructField("order_id", StringType(), nullable=False),
StructField("customer_name", StringType(), nullable=True),
StructField("product_category", StringType(), nullable=True),
StructField("amount", DecimalType(10, 2), nullable=False),
StructField("order_date", DateType(), nullable=False),
])
# This will REJECT data that doesn't match the schema
(
new_data.write
.format("delta")
.mode("append")
.option("mergeSchema", "false") # Strict mode
.save("s3://my-lakehouse/silver/sales")
)
Step 8: Automate with a Simple Pipeline
Tie everything together in a single pipeline script:
from datetime import datetime
def run_lakehouse_pipeline():
"""Run the full Bronze → Silver → Gold pipeline."""
print(f"Pipeline started at {datetime.now()}")
# Step 1: Ingest raw data into Bronze
ingest_to_bronze("s3://raw-data/sales_today.csv", "sales")
print("Bronze layer updated.")
# Step 2: Clean and transform into Silver
bronze_to_silver_sales()
print("Silver layer updated.")
# Step 3: Aggregate into Gold
silver_to_gold_monthly_revenue()
print("Gold layer updated.")
# Step 4: Optimize tables (compact small files)
delta_table = DeltaTable.forPath(spark, "s3://my-lakehouse/silver/sales")
delta_table.optimize().executeCompaction()
# Step 5: Clean up old versions (keep 30 days)
delta_table.vacuum(retentionHours=720)
print(f"Pipeline completed at {datetime.now()}")
run_lakehouse_pipeline()
Querying Your Lakehouse
Once built, you can query your lakehouse using multiple tools:
- Spark SQL — for batch processing and transformations
- Trino/Presto — for interactive SQL queries
- AWS Athena — for serverless querying on S3
- Power BI / Tableau — connect directly to the gold layer for dashboards
# Register Delta tables as SQL tables
spark.sql("""
CREATE TABLE IF NOT EXISTS gold_monthly_revenue
USING delta
LOCATION 's3://my-lakehouse/gold/monthly_revenue'
""")
# Query with SQL
spark.sql("""
SELECT year, month, product_category, total_revenue
FROM gold_monthly_revenue
WHERE year = 2024
ORDER BY total_revenue DESC
""").show()
Cost Optimization Tips
- Partition your data by date or category to avoid full table scans
- Use Z-ordering on frequently filtered columns for faster queries
- Run VACUUM regularly to delete old file versions and save storage costs
- Use spot instances for Spark clusters to reduce compute costs by 60-90%
- Compact small files with OPTIMIZE to reduce the number of file reads
Conclusion
You’ve just built a complete data lakehouse with:
- A Bronze layer for raw data ingestion
- A Silver layer for cleaned, validated data
- A Gold layer for business-ready aggregations
- ACID transactions and schema enforcement via Delta Lake
- Time travel for auditing and debugging
- An automated pipeline to keep it all running
The data lakehouse pattern is rapidly replacing traditional data warehouse setups because it offers the same reliability at a fraction of the cost — all on open-source tools.
In the next post, we’ll dive deeper into the Medallion Architecture and how to design each layer for maximum query performance.