SQL Data Engineering Project

End-to-end SQL project for ingesting raw CSV data, transforming it into an OLTP model, loading a dimensional warehouse, and running analytics, quality checks, performance tests, and monitoring.

Project Layout

sql-data-engineering-project/
├── database/
├── data/
├── etl/
├── warehouse/
├── analytics/
├── data_quality/
├── performance/
├── monitoring/
├── tests/
└── docs/

Tech Stack

• PostgreSQL 14+
• SQL (psql-compatible scripts)
• Optional Python tools (linting/automation)

CI

• GitHub Actions workflow: .github/workflows/ci.yml
• Runs the full pipeline (./run_all.sh) on pushes and pull requests to main.
• Scheduled monitoring workflow: .github/workflows/monitoring_schedule.yml
• Runs weekly and on manual trigger; publishes monitoring logs as workflow artifacts.

Quick Start

One command end-to-end (recommended):

chmod +x run_all.sh
./run_all.sh

Run with Docker (PostgreSQL + pipeline):

docker compose up -d postgres
docker compose --profile run run --rm pipeline

Manual execution:

• Create database (example):

createdb sqldataengineering

• Set environment values in .env.

• Initialize core schemas and tables:

psql "$DATABASE_URL" -f database/schema.sql
psql "$DATABASE_URL" -f database/tables.sql
psql "$DATABASE_URL" -f database/constraints.sql
psql "$DATABASE_URL" -f database/indexes.sql

• Load source data and run ETL:

psql "$DATABASE_URL" -f etl/extract.sql
psql "$DATABASE_URL" -f etl/transform.sql
psql "$DATABASE_URL" -f etl/load.sql

• Build warehouse model (includes SCD Type 2 update + fact load):

psql "$DATABASEURL" -f warehouse/starschema.sql

• Run quality checks, analytics, and tests:

psql "$DATABASEURL" -f dataquality/validation_queries.sql
psql "$DATABASEURL" -f analytics/revenueanalysis.sql
psql "$DATABASEURL" -f tests/testdata_load.sql
psql "$DATABASEURL" -f tests/testscd_logic.sql
psql "$DATABASEURL" -f tests/testquality_checks.sql
psql "$DATABASEURL" -f tests/testcustomer_segmentation.sql

Pipeline Flow

• Extract CSVs into staging raw tables.
• Transform and standardize datatypes + deduplicate records.
• Load cleaned data into OLTP tables with upserts.
• Warehouse load dimensions/facts and apply SCD Type 2 for customers.
• Analyze KPIs and run fraud/retention/segmentation logic.
• Monitor row counts and anomaly signals.

Notes

• SQL is written for PostgreSQL.
• etl/extract.sql uses \copy, so run with psql from project root.
• Example data is included in data/raw/.
• Architecture and data model diagrams are generated from DOT sources: