Denormalization Analytics

This repository is part of the project Prompt Engineering for Data Engineering aimed at Data Analytics, which explores how prompt engineering techniques and LLMs can help automate the denormalization of relational models and the creation of ETL pipelines to better serve analytical workloads.

Repository Structure

• Experiment_schemes/: Training, validation, and benchmark database schemas (AdventureWorks, Northwind, TPC-H, Pagila, etc.).
• Prompts/: Structured prompt templates (RISEN framework, OBT, and seniority-based prompts).
• Reports/: Holds the individual text reports generated for each schema, organized by the LLM provider (Gemini, Ollama_API, Ollama_local).
• Results/: Hold the individual results generated for each schema, organized by the LLM provider. Also,a comprehensive Statistical_results/ subfolder. This subfolder includes CSV datasets (e.g., descriptive_statistics.csv, lmm_rouge.csv) and visual plots (e.g., interaction_bleu.png, qq_completeness.png) for deep statistical analysis.
• Scripts/: Core evaluation scripts, LLM API handlers, and statistical analysis tools.

Available Options

Models (3 options):

1. Gemini 3.1-Pro (API) - requires GEMINI_API_KEY
2. Ollama Qwen3-coder:480b-cloud (Cloud API) - requires OLLAMA_API_KEY
3. Ollama Qwen3.5:4b (Local) - requires local Ollama instance running

Prompts (6 options):

• RISEN.txt (Standard framework: Role-Input-Steps-Expectation-Narrowing)
• RISEN-few-shot.txt (Enriched with input-output examples)
• OBT.txt (Enforces the One-Big-Table denormalization strategy)
• junior.txt (Generated junior-level data engineer persona)
• mid-level.txt (Generated mid-level data engineer persona)
• senior.txt (Generated senior-level data engineer persona)

The prompts from /Prompts/Generated were generated by GPT 5.5 with high thinking effort, and they were used to build the prompts by userlevel, which means junior, mid-level and senior.

Schemas:

• 16 core benchmark databases evaluated from the Experiment_schemes/Train/ directory.

---

Getting started

Follow these steps to run the main scripts locally. The examples below assume a Linux / bash environment.

1. Clone the repository

git clone https://github.com/Palacio-dev/Denormalization_Analytics.git
cd Denormalization_Analytics

2. Create and activate a Python virtual environment

python3 -m venv venv
source venv/bin/activate

3. Install dependencies

pip install --upgrade pip
pip install -r requirements.txt

4. Set the enviromment

• In order to run the experiment with the Gemini's API, you first need to set one Gemini API key , you can read more about it here : GEMNI_API. Once you got acess to a API, create a .env file in the root of the repository and create a variable called GEMINI_API_KEY.

	GEMINI_API_KEY="your_gemini_api_key_here"

• You can also run the experiment locally with free models available at Ollama. There are two possibilites, running locally with the model qwen3.5:4b or running via the Ollama's API with the model qwen3-coder:480b-cloud. For running locally, you need to download the model on your machine. It can be done with the command in the terminal:

 ollama pull qwen3.5:9b

Then, run ollama:

 ollama serve

For running via the Ollama's API, you first need to create an account in Ollama's site and get an API key. Then, put it on the .env file with the variable OLLAMA_API_KEY.

	OLLAMA_API_KEY="your_ollama_api_key_here"

5. Run the denormalization experiment

Currently there are 3 available LLM models, 7 prompts and 17 normalziled schemas to run the experiment. You will select one possibility of each when running the experiment.

cd Scripts
python3 run_experiment.py

This script provides an interactive CLI for running denormalization experiments with three different LLM models, five prompts, and seventeen schemas. Alternatively you can also run all experiment of a given model using the flag --auto-run.

cd Scripts
python3 run_experiment.py --auto-run [gemini,ollama_api,ollama_local]

Workflow

========

1. Run the experiment: python3 run_experiment.py

2. Choose your model (1-3)

3. Choose your prompt (1-6)

4. Choose your schema (1-16)

5. Confirm your selection

6. The LLM will generate the denormalized schema:

   • Output streams to terminal in real-time
   • Result is automatically saved to Results/ folder

Output files

============

Results are saved as: Results/experiment_YYYY-MM-DD_HH-MM-SS_MODEL_SCHEMA_PROMPT.txt

Each file contains:

• Metadata header (timestamp, model, prompt, schema)
• Full LLM-generated denormalized schema output

Result Handling:

• LLM output streams to stdout as it's generated
• Full result is captured and saved after generation completes
• Timestamps are in YYYY-MM-DD_HH-MM-SS format

6. Evaluate denormalization

The evaluate denormalization script automatically evaluates the quality of denormalized SQL models compared to their relational counterparts using NLP metrics (BLEU, ROUGE, METEOR) and structural analysis.

Usage

Basic Usage

cd Scripts
python evaluate_denormalization.py <relational_model.txt> <denormalized_model.txt>

With Output File

cd Scripts
python evaluate_denormalization.py <relational_model.txt> <denormalized_model.txt> -o report.txt

Run evaluation for all results

cd Scripts
bash run_all_evaluations.sh

What the Script Does

1. Parses SQL Files: Reads and parses both SQL model files
2. Pairs Identifiers: Automatically matches equivalent columns between models
3. Calculates Average LD: Evaluates the average Edit Distance between the paired Identifiers.
4. Calculates Completeness: Evaluates if information was lost or added
5. Evaluates Correctness: Checks data type preservation.
6. Computes Metrics: Calculates BLEU, ROUGE, and METEOR scores
7. Generates Report: Produces a comprehensive evaluation report

Evaluation Criteria

Completeness

• Ratio = 1.0: Same amount of attributes between relational and denormalized schemas.
• Ratio < 1.0: Information may have been lost.
• Ratio > 1.0: New information may have been added.

Correctness

• Data type preservation
• Average Edit Distance
• NLP metrics (BLEU, ROUGE, METEOR).

Metrics

• BLEU: Measures n-gram overlap (scale: 0-1, higher is better)
• ROUGE: Measures recall-oriented similarity (scale: 0-1, higher is better)
• METEOR: Considers synonyms and stemming (scale: 0-1, higher is better)

Output Format

The script generates a detailed report with:

1. Model overview (tables and attributes count)
2. Completeness analysis
3. Correctness evaluation with issue detection
4. Pair-by-pair metric comparison
5. Overall metrics summary

License

This project is licensed under the MIT License.