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

3254988796aaa-code — Thu, 05 Nov 2026 03:30:55 +0000

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:

ODS (Operational Data Store): Raw data ingestion.
DWD (Data Warehouse Detail): Cleaned and conformed data (using PySpark for ETL).
DWS (Data Warehouse Summary): Aggregated data for specific domains (e.g., user behavior, product sales).
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?

How I Extracted Data from 783 PDFs with 97% Verified Accuracy (A Data Engineering Case Study)

3254988796aaa-code — Thu, 05 Nov 2026 03:27:14 +0000

Have you ever tried to extract structured data from hundreds of messy PDF documents? It's a nightmare.

In this post, I want to share a recent project where I built an end-to-end pipeline to extract a (company, period) × 40 fields data panel from 783 real A-share annual report PDFs.

But the real challenge wasn't just extraction—it was verifiability. How do you prove the extracted numbers are actually correct?

Here is how I solved it.

🏗️ The Architecture

The pipeline is designed to be fully reproducible. It goes through several stages:

Cataloging & Page Packing: Handling huge PDF files efficiently.
Extraction: Using PyMuPDF and customized rules to locate tables.
Validation & Quality Gates: The core of the project.
Panel Construction: Merging everything into a tidy dataset.

🛡️ The Core Feature: "Why should anyone trust this table?"

Every data engineer knows that extraction is easy, but data quality is hard. I built three orthogonal validators into the system:

Accounting identity checks: Do the numbers add up?
Cross-period reconciliation: Measuring the same metric from two independent reports and comparing them.
Scope checks: Ensuring the data matches the expected boundaries.

The Quality Gate

I used a quality_rules.yaml file to define pass/fail thresholds. If the pipeline fails a gate, it exits with a non-zero code. This prevents bad data from silently polluting the final dataset.

📊 The Results

To prove the system works, I manually verified 234 measurement points.

The result? The parser achieved 97.12% accuracy (Wilson 95% CI: 93.85%–98.67%).

More importantly, the system knows what it doesn't know. It explicitly rejected 212 abstracts where column counts didn't match, refusing to output bad numbers rather than guessing.

🔗 Open Source

If you are interested in how the pipeline was built, or if you are dealing with similar PDF extraction challenges, the full code and reproducible workflow are available on GitHub.

I'd love to hear your feedback on the cross-period reconciliation design!

👉 GitHub Repo: https://github.com/3254988796aaa-code/annual-report-data-base

DEV Community: 3254988796aaa-code