/Postgres-ELT-Data-Pipeline

An ELT data pipeline project utilizing docker and postgress (both source and destination dbs)

Primary LanguagePython

ELT Data Pipeline Project

Extract Load and Transform (ELT) refers to the process of extracting data from source systems, loading the data into the Data Warehouse environment and then transforming it afterwards using in-database operations such as SQL. It relies on having the capacity to initially store large volumes of raw data.

Project Repo Structure

  1. docker-compose.yaml: This file contains the configuration for Docker Compose, which is used to orchestrate multiple Docker containers. It defines three services:

    • source_postgres: The source PostgreSQL database.
    • destination_postgres: The destination PostgreSQL database.
    • elt_script: The service that runs the ELT script.

  2. elt_script/Dockerfile: This Dockerfile sets up a Python environment and installs the PostgreSQL client. It also copies the ELT script into the container and sets it as the default command.

  3. elt_script/elt_script.py: This Python script performs the ELT process. It waits for the source PostgreSQL database to become available, then dumps its data to a SQL file and loads this data into the destination PostgreSQL database.

  4. source_db_init/init.sql: This SQL script initializes the source database with sample data. It creates tables for users, films, film categories, actors, and film actors, and inserts sample data into these tables.

How It Works

  1. Docker Compose: Using the docker-compose.yaml file, three Docker containers are spun up:

    • A source PostgreSQL database with sample data.
    • A destination PostgreSQL database.
    • A Python environment that runs the ELT script.

  2. ELT Process: The elt_script.py waits for the source PostgreSQL database to become available. Once it's available, the script uses pg_dump to dump the source database to a SQL file. Then, it uses psql to load this SQL file into the destination PostgreSQL database.

  3. Database Initialization: The init.sql script initializes the source database with sample dummy data. It creates several tables and populates them with sample data.

Running the Project

  1. Clone this repository.
  2. Ensure you have Docker and Docker Compose installed on your machine.
  3. Navigate to the repository directory and run docker-compose up.
  4. Once all containers are up and running, the ELT process will start automatically.
  5. After the ELT process completes, you can access the source and destination PostgreSQL databases on ports 5433 and 5434, respectively.