Database Automation Pipeline (ETL Proof-of-Concept)

This repository contains a simple Python script (database_pipeline_poc.py) demonstrating a basic Extract, Transform, Load (ETL) process. It serves as a proof-of-concept for automating the ingestion of data from a source (CSV file) into a structured database (SQLite) for analysis.

Purpose

The goal of this project is to showcase foundational skills in:

1. Data Extraction: Reading data from a common file format.
2. Data Transformation: Performing basic cleaning and restructuring using Pandas.
3. Data Loading: Populating a relational database table.
4. SQL Querying: Retrieving and aggregating data from the database.

This demonstrates understanding of the core principles behind building automated data pipelines for research or business intelligence purposes.

Setup

This script requires Python 3 and the pandas library.

1. Ensure Python 3 is installed. You can check by typing python --version or python3 --version in your terminal.
2. Install pandas: If you don't have pandas installed, open your terminal or command prompt and run:

   pip install pandas
   

How to Run

1. Clone the repository (or download the files):

   git clone https://github.com/MaxHaro/database-automation-poc
   cd database-automation-poc
   

2. Run the script:

   python database_pipeline_poc.py
   

What the Script Does

When you run the script:

1. It first checks if sample_energy_data.csv exists. If not, it creates a small sample file.
2. Extract: It reads the data from sample_energy_data.csv into a Pandas DataFrame.
3. Transform: It converts the 'Generation_GWh' column to 'Generation_TWh' and ensures the 'Year' column is an integer.
4. Load: It creates (or replaces) an SQLite database file named energy_data.db and loads the transformed data into a table called annual_generation.
5. Query: It executes three sample SQL queries against the database and prints the results to the console, demonstrating data retrieval and aggregation.

You will see output messages indicating the progress of each step and the results of the SQL queries.

Technologies Used

• Python 3
• Pandas: For data manipulation and reading/writing CSVs.
• SQLite3: Python's built-in module for interacting with SQLite databases.