Building a Hadoop + Spark Data Warehouse with a Text-to-SQL AI Assistant

Building a data warehouse is a classic data engineering task. But what if you could add an AI assistant that allows users to query the warehouse using natural language?

I recently finished a project that does exactly this. Here is a breakdown of the architecture and how the Text-to-SQL feature works.

📦 The Dataset & Tech Stack

I used the Olist Brazilian E-commerce dataset (approx. 100k orders).

• Storage & Compute: Hadoop 3.3.6 (HDFS + YARN), Hive 4.0.0, Spark 3.5.1
• Metastore: PostgreSQL 16
• Visualization: Tableau Public

🏗️ The 4-Layer Architecture

I implemented a standard dimensional data warehouse model:

1. ODS (Operational Data Store): Raw data ingestion.
2. DWD (Data Warehouse Detail): Cleaned and conformed data (using PySpark for ETL).
3. DWS (Data Warehouse Summary): Aggregated data for specific domains (e.g., user behavior, product sales).
4. ADS (Application Data Service): The final tables used for BI reporting.

Pro tip: I used Parquet + Snappy for storage efficiency and enforced ETL through Spark SQL to avoid Hive-on-Tez permission headaches.

🧠 The AI Twist: Text-to-SQL Assistant

The most interesting part of this project is the Text-to-SQL assistant.

I integrated an LLM (Qwen) via prompt engineering. By feeding it the schema descriptions, business rules, and few-shot examples, the AI generates Hive SQL based on user questions.

In my tests, it generated SQL that matched the official ADS-layer results 100% of the time on 3 complex business questions. The interface is built with Streamlit.

📈 Actionable Insights

The warehouse isn't just for show. One of the key insights from the RFM (Recency, Frequency, Monetary) analysis was that the "Important to retain" customer segment makes up 23.5% of the user base—making them the prime target for re-engagement campaigns.

🔗 Check out the Code

The entire ETL process, schema definitions, and the Streamlit AI assistant code are open-sourced.

👉 GitHub Repo: https://github.com/3254988796aaa-code/olist-data-warehouse

Let me know what you think! Have you tried integrating LLMs into traditional data stack workflows?