Build a Mini Lakehouse at Home: A Portfolio Project

Quickstart

If you only have an afternoon, build the smallest version of a lakehouse that still teaches the important lessons: idempotent ingestion, layered transforms, and a serving layer your BI tool can query.

Overview

“Build a Mini Lakehouse at Home” is a portfolio-first project: you’re not trying to clone a full enterprise platform. You’re building a compact system that demonstrates the same engineering principles hiring managers look for: reproducibility, clear data contracts, and the ability to debug failures end-to-end.

The goal is not a perfect “lakehouse definition” debate. The goal is hands-on fluency: you’ll handle ingestion, schema drift, idempotency, transforms, and a real consumer (BI). Once you can do this locally, moving to managed tools in the cloud becomes a tooling decision — not a conceptual leap.

Core concepts

Before you touch tools, lock in a few mental models. These are the “why” behind lakehouse-style projects — and they’re what you’ll talk about in interviews.

Lakehouse, in practical terms

A lakehouse combines two worlds: a data lake (cheap files, flexible storage) and a warehouse (fast SQL, governance, BI). In a home project, that usually means: keep raw data as files (Parquet), do transformations with SQL, and expose curated tables to dashboards.

Medallion architecture (Bronze / Silver / Gold)

The medallion pattern is less about buzzwords and more about debugging: you want to know whether a bug happened during ingestion, cleaning, or business transforms. Separate layers turn “mystery failures” into “oh, this broke in Silver because a timestamp changed format”.

Idempotent pipelines

Idempotent means: if you run the pipeline twice for the same time window, you get the same result and you don’t duplicate data. This is the single most “production” concept you can demonstrate at home.

Schema & data contracts

A “contract” is simply an agreement: which columns exist, what their types mean, and what values are allowed. Without contracts, every downstream query becomes a fragile guess.

Serving layer vs compute layer

Your compute layer reads and transforms (files → tables). Your serving layer is what tools query (stable SQL endpoint). For a home project, Postgres is a great serving layer: everyone understands it, Metabase connects easily, and you can show indexes, constraints, and query performance basics.

Step-by-step

This guide uses a “home-friendly” stack: MinIO (local S3), DuckDB (fast SQL over files), Postgres (serving warehouse), and Metabase (BI). You can swap tools later — the architecture stays the same.

Step 0 — Create a repo structure that looks professional

Step 1 — Run MinIO + Postgres + Metabase locally

The quickest “real stack” is a single Docker Compose file. Keep it boring and obvious: stable ports, named volumes, and one place to configure credentials.

services:
  minio:
    image: minio/minio:latest
    command: server /data --console-address ":9001"
    ports:
      - "9000:9000"
      - "9001:9001"
    environment:
      MINIO_ROOT_USER: ${MINIO_ROOT_USER:-minio}
      MINIO_ROOT_PASSWORD: ${MINIO_ROOT_PASSWORD:-minio12345}
    volumes:
      - minio_data:/data

  postgres:
    image: postgres:16
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: ${PGUSER:-lakehouse}
      POSTGRES_PASSWORD: ${PGPASSWORD:-lakehouse123}
      POSTGRES_DB: ${PGDATABASE:-lakehouse}
    volumes:
      - pg_data:/var/lib/postgresql/data

  metabase:
    image: metabase/metabase:latest
    ports:
      - "3000:3000"
    depends_on:
      - postgres

volumes:
  minio_data:
  pg_data:

Step 2 — Ingest into Bronze as Parquet (repeatable & partitioned)

Your ingestion job should be safe to re-run. A simple pattern: fetch data for a date window, write a Parquet file partitioned by ingest_date, and record a small run log. Even if you later switch to a scheduler, the job stays the same.

"""
Minimal Bronze ingestion example.

- Pulls time-series data from a public API (no keys).
- Writes Parquet partitioned by ingest_date.
- Creates an append-only run log (CSV/JSON is fine for a mini project).

Tip: keep raw fields as-is in Bronze. Clean types in Silver.
"""
from __future__ import annotations

import json
from datetime import date, timedelta
from pathlib import Path

import pandas as pd
import requests

BRONZE_DIR = Path("data/bronze/open_meteo")  # mirror your object store layout
RUNLOG_DIR = Path("data/runlogs")
BRONZE_DIR.mkdir(parents=True, exist_ok=True)
RUNLOG_DIR.mkdir(parents=True, exist_ok=True)

def fetch_day(lat: float, lon: float, day: date) -> pd.DataFrame:
    url = "https://api.open-meteo.com/v1/forecast"
    params = {
        "latitude": lat,
        "longitude": lon,
        "start_date": day.isoformat(),
        "end_date": day.isoformat(),
        "hourly": "temperature_2m,precipitation,wind_speed_10m",
        "timezone": "UTC",
    }
    r = requests.get(url, params=params, timeout=30)
    r.raise_for_status()
    payload = r.json()
    hourly = payload.get("hourly", {})
    df = pd.DataFrame(hourly)
    df["location_lat"] = lat
    df["location_lon"] = lon
    df["source"] = "open-meteo"
    df["ingest_date"] = day.isoformat()
    return df

def write_bronze(df: pd.DataFrame) -> Path:
    part = df["ingest_date"].iloc[0]
    out_dir = BRONZE_DIR / f"ingest_date={part}"
    out_dir.mkdir(parents=True, exist_ok=True)
    out_path = out_dir / "hourly.parquet"
    df.to_parquet(out_path, index=False)
    return out_path

def append_runlog(window_start: date, window_end: date, rows: int, files: list[str]) -> None:
    record = {
        "run_utc": pd.Timestamp.utcnow().isoformat(),
        "window_start": window_start.isoformat(),
        "window_end": window_end.isoformat(),
        "rows": rows,
        "files": files,
    }
    (RUNLOG_DIR / "bronze_runs.jsonl").open("a", encoding="utf-8").write(json.dumps(record) + "\n")

if __name__ == "__main__":
    # Example: last 3 days for one location
    lat, lon = 48.1486, 17.1077  # Bratislava-ish; replace with your target environment
    end = date.today()
    start = end - timedelta(days=2)

    all_frames: list[pd.DataFrame] = []
    files: list[str] = []
    d = start
    while d <= end:
        df_day = fetch_day(lat, lon, d)
        out_file = write_bronze(df_day)
        all_frames.append(df_day)
        files.append(str(out_file))
        d += timedelta(days=1)

    df_all = pd.concat(all_frames, ignore_index=True)
    append_runlog(start, end, rows=len(df_all), files=files)

Step 3 — Transform with SQL (Silver) and publish marts (Gold)

For a mini lakehouse, SQL is your superpower. Use it to standardize types, handle nulls, and define stable business logic. The easiest pattern is: read Bronze Parquet with a compute engine, produce clean Silver views/tables, then write Gold tables to Postgres for BI.

-- DuckDB example: read Bronze Parquet, build Silver/Gold, and write Gold to Postgres.
-- Run in DuckDB CLI or via a small Python runner.
-- Note: You can also keep everything in Postgres if you prefer; the patterns stay the same.

-- 1) Read Bronze files (schema-on-read)
CREATE OR REPLACE VIEW bronze_weather AS
SELECT
  CAST(time AS TIMESTAMP) AS ts_utc,
  CAST(temperature_2m AS DOUBLE) AS temperature_c,
  CAST(precipitation AS DOUBLE) AS precipitation_mm,
  CAST(wind_speed_10m AS DOUBLE) AS wind_speed_kmh,
  CAST(location_lat AS DOUBLE) AS location_lat,
  CAST(location_lon AS DOUBLE) AS location_lon,
  CAST(source AS VARCHAR) AS source,
  CAST(ingest_date AS DATE) AS ingest_date
FROM read_parquet('data/bronze/open_meteo/ingest_date=*/hourly.parquet');

-- 2) Silver: standardize + basic quality rules
CREATE OR REPLACE TABLE silver_weather AS
SELECT
  ts_utc,
  temperature_c,
  precipitation_mm,
  wind_speed_kmh,
  location_lat,
  location_lon,
  source,
  ingest_date
FROM bronze_weather
WHERE ts_utc IS NOT NULL
  AND temperature_c IS NOT NULL;

-- 3) Gold: analytics mart (daily rollup)
CREATE OR REPLACE TABLE gold_weather_daily AS
SELECT
  DATE_TRUNC('day', ts_utc) AS day_utc,
  source,
  location_lat,
  location_lon,
  AVG(temperature_c) AS avg_temp_c,
  MAX(temperature_c) AS max_temp_c,
  MIN(temperature_c) AS min_temp_c,
  SUM(precipitation_mm) AS total_precip_mm,
  AVG(wind_speed_kmh) AS avg_wind_kmh,
  COUNT(*) AS hourly_points
FROM silver_weather
GROUP BY 1, 2, 3, 4;

-- 4) Publish Gold to Postgres (serving)
INSTALL postgres;
LOAD postgres;

ATTACH 'dbname=lakehouse user=lakehouse password=lakehouse123 host=localhost port=5432' AS pg (TYPE postgres);

CREATE SCHEMA IF NOT EXISTS pg.analytics;

CREATE OR REPLACE TABLE pg.analytics.weather_daily AS
SELECT * FROM gold_weather_daily;

Step 4 — Add tiny but real data quality checks

You don’t need a big testing framework to show maturity. Add a few checks that prove you can protect downstream users. Put them in your transform runner (or a separate “validate” step) and fail fast when something is off.

Step 5 — Make it portfolio-ready (the “demo checklist”)

This is where a tutorial becomes a portfolio project. You’re aiming for an experience where someone can: clone your repo, run one command, and see a dashboard.

Common mistakes

Most mini lakehouse projects fail for the same reasons production pipelines fail — just on a smaller scale. Fix these, and your project becomes dramatically more credible.

FAQ

Do I need Spark to build a mini lakehouse?

No. Spark is great at scale, but a mini lakehouse is about patterns, not cluster management. A fast local SQL engine (like DuckDB) + a serving database (like Postgres) is enough to demonstrate ingestion, layered transforms, and BI consumption.

What makes this a “lakehouse” and not just an ETL demo?

The separation of storage and serving. You keep raw data as files in an object store (the “lake”), you transform through explicit layers (Bronze/Silver/Gold), and you expose curated tables to BI (the “warehouse” behavior). The key is that you can reprocess from raw files whenever logic changes.

Should I choose ETL or ELT for a home project?

Prefer ELT-style thinking: land raw data first (Bronze), then transform with SQL (Silver/Gold). Even if some steps are “ETL-ish” (e.g., moving files), the principle is the same: don’t bake business logic into ingestion.

How do I handle late-arriving or corrected data?

Use partitions and upserts. Re-ingest the affected window (e.g., yesterday/today), then rebuild Silver/Gold for that window and overwrite or MERGE into serving tables. Keep the run log so you can prove what was recalculated.

What should I put in Gold: star schema or one wide table?

Either is fine as long as you clearly define the grain and metric definitions. For a portfolio project, one wide mart table (one row per day per entity) is often easiest to explain and demo. If you want to flex modeling skills, create one fact + one dimension and show joins.

How can I show this project is “production-minded”?

Add reproducibility + checks. Docker Compose for infrastructure, scripted pipeline runs, small data quality checks, and versioned SQL transforms are the signals that matter most.

Cheatsheet

Scan this when you’re building (or debugging) your mini lakehouse at home.

Wrap-up

You just designed a mini lakehouse the right way: raw data lands in an object-store-style lake, transformations are layered and reproducible, and BI queries a stable serving layer. That’s the whole game — the “cloud version” is just different tooling.

Want to go deeper? The related posts below pair well with this project: ETL vs ELT decisions, scalable SQL patterns, and Postgres performance moves you can apply to your serving layer.

Related posts

• ETL vs ELT: The Decision That Shapes Your Stack
• Python for Data Engineers: Clean Pipelines, Not Notebooks
• SQL That Scales: Joins, Windows, and Real-World Patterns
• Explain Plans Demystified: Find the Slow Part Fast
• Star Schema vs Snowflake: Modeling for Analytics
• Postgres Power Moves: Indexes, JSONB, and Partitioning