E-commerce ETL & Analytics with PostgreSQL

Overview

This repo contains a compact end-to-end mini data engineering project using PostgreSQL (pgAdmin). It creates synthetic data (1,000 customers, 100 products, 50k orders), ingests into staging tables, transforms into a star schema (dimension + fact tables), adds indexes & materialized views, and runs analytics queries.

Files

• 01_create_schemas_and_raw_tables.sql
• 02_generate_synthetic_data.sql
• 03_etl_build_star_schema.sql
• 04_indexes_materialized_views_and_analytics.sql

How to run

1. Install PostgreSQL and pgAdmin.
2. Create database ecommerce_db.
3. Open pgAdmin → Connect to database → Query Tool.
4. Run the files in order: 01 → 02 → 03 → 04.

Notes

• All data is generated inside Postgres using generate_series() — no external files needed.
• Materialized view warehouse.mv_daily_revenue can be refreshed: REFRESH MATERIALIZED VIEW warehouse.mv_daily_revenue;