Simple Data Lakehouse Pipeline

This project demonstrates a complete, script-based data pipeline that extracts data from multiple sources (PDF, CSV, TXT), transforms it, and loads it into a PostgreSQL data warehouse. Finally, it runs analyses on the warehouse data to produce visualizations.

Features

• Automated ETL Pipeline: Scripts to handle each stage of the ETL process.
• Multi-Format Data Ingestion: Processes PDF, CSV, and plain text files.
• Data Lake Structure: Uses a simple raw and staging zone to manage data flow.
• Star Schema Data Warehouse: Builds a well-structured star schema in PostgreSQL for optimized analytical queries.
• Data Analysis & Visualization: Generates insightful plots and word clouds from the processed data.
• Modular Design: Each part of the pipeline is a separate, easy-to-understand Python script.

Tech Stack

• Language: Python 3
• Data Manipulation: Pandas
• Database: PostgreSQL
• Python-DB Connector: SQLAlchemy, Psycopg2
• PDF Extraction: PyMuPDF
• Visualization: Matplotlib, Seaborn, WordCloud

Project Structure

.
├── analyze.py         # 4. Runs analysis on the warehouse and creates plots
├── createpdf.py       # Utility to generate sample PDF data
├── data/              # Source data files
├── datalake/          # Simple data lake for staging data
│   ├── raw/           # Raw, untouched data categorized by file type
│   └── staging/       # Cleaned, processed data in CSV format
├── ingest.py          # 1. Ingests data from /data to /datalake/raw
├── load.py            # 3. Loads data from /staging into the PostgreSQL warehouse
├── output/            # Destination for generated analysis plots
├── process.py         # 2. Transforms data from /raw to /staging
└── requirements.txt   # Python dependencies

Setup and Installation

1. Clone the Repository

git clone https://github.com/azharpratama/dlh-datalake.git
cd dlh-datalake

2. Create a Python Virtual Environment

It's recommended to use a virtual environment to manage dependencies.

python3 -m venv venv
source venv/bin/activate

3. Install Dependencies

pip install -r requirements.txt

4. Set up PostgreSQL Database

The easiest way to run PostgreSQL is by using Docker.

docker run --name datalake-db -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=warehouse -p 5432:5432 -d postgres

This command starts a PostgreSQL container with the default credentials and database name that the load.py script expects. The database will be available on localhost:5432.

How to Run the Pipeline

The pipeline scripts are designed to be run in a specific order.

Step 1: Generate Sample Data (Optional)

The report.pdf file is included, but if you need to regenerate it, run:

python createpdf.py

Step 2: Run the Full Pipeline

Execute the scripts one by one in the following order:

# 1. Ingest raw files into the data lake
python ingest.py

# 2. Process raw files and save them to the staging area
python process.py

# 3. Load staged files into the data warehouse
python load.py

# 4. Run analysis and generate visualizations in the output/ directory
python analyze.py

After running these commands, the output/ directory will contain the generated PNG images from the analysis.

Pipeline Stages Explained

• ingest.py: Copies files from the data directory into the datalake/raw directory, organizing them into subdirectories based on their file extension (e.g., csv, pdf).
• process.py: Reads files from datalake/raw.
  • PDFs: Extracts structured financial data using regular expressions.
  • CSVs: Cleans the data by adding headers and removing duplicates/nulls.
  • TXTs: Parses tweet data, cleans the text, and extracts hashtags/mentions.
  • All processed data is saved as clean CSV files in datalake/staging.
• load.py: Connects to the PostgreSQL database. It first creates the star schema (dimension and fact tables) if they don't exist. Then, it reads the CSV files from datalake/staging and populates the warehouse tables.
• analyze.py: Connects to the populated data warehouse and runs SQL queries to perform three analyses: a word cloud for tweets, a correlation plot for sensor data, and a market share comparison bar chart for financial data. The resulting plots are saved to the output directory.