Modern Data Stack Pipeline

Enterprise-Grade Data Engineering & Analytics Engineering Pipeline

An end-to-end Modern Data Stack built using Python, PostgreSQL, SSIS, dbt, and Power BI for scalable ETL, dimensional modeling, analytics engineering, and business intelligence.

Overview

Modern organizations generate large volumes of operational data from multiple business systems. Converting this raw data into reliable, analytics-ready datasets requires scalable ETL pipelines, standardized data models, automated quality checks, and business intelligence solutions.

This project demonstrates the implementation of a complete Modern Data Stack using industry-standard data engineering and analytics engineering practices.

The pipeline automates the complete data lifecycle—from ingestion of raw enterprise datasets to dimensional modeling and executive dashboards—using modular, production-inspired architecture.

Project Objectives

Build a scalable end-to-end ETL pipeline.
Automate data ingestion and transformation.
Design an enterprise PostgreSQL data warehouse.
Implement layered dbt transformations.
Develop analytics-ready dimensional models.
Apply automated data quality testing.
Create reusable business metrics.
Deliver executive dashboards through Power BI.
Follow modern analytics engineering best practices.

Technology Stack

LayerTechnologiesProgrammingPythonDatabasePostgreSQLETLPython, SSISAnalytics EngineeringdbtData ModelingStar SchemaBusiness IntelligencePower BIVersion ControlGit & GitHub

Key Features

Enterprise-grade ETL pipeline
Automated data ingestion
PostgreSQL Data Warehouse
SSIS integration pipeline
Layered dbt architecture
Star schema dimensional modeling
Automated data validation
Modular SQL transformations
Analytics-ready data marts
Executive Power BI dashboards
Documentation-driven engineering
Reusable analytics models

Enterprise Architecture

                                   Raw Enterprise Data
┌─────────────────────────────────────────────────────────────────────────────┐
│                                                                             │
│     Excel Files     CSV Files     Financial Reports     Operational Data     │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘
                                     │
                                     ▼
                         Python ETL Automation Pipeline
                     (Extract → Clean → Transform → Load)
                                     │
                                     ▼
                        PostgreSQL Enterprise Data Warehouse
                                     │
                                     ▼
                        SSIS Data Integration & Validation
                                     │
                                     ▼
                          dbt Analytics Engineering Layer
                                     │
             ┌───────────────────────┴────────────────────────┐
             │                                                │
             ▼                                                ▼
      Dimension Tables                                  Fact Tables
             │                                                │
             └───────────────────────┬────────────────────────┘
                                     ▼
                           Power BI Business Dashboard

Data Pipeline Workflow

Raw Enterprise Data
        │
        ▼
──────────────────────────────
      Python ETL Pipeline
──────────────────────────────
Extract
        │
        ▼
Data Cleaning
        │
        ▼
Transformation
        │
        ▼
Data Validation
        │
        ▼
PostgreSQL Database
        │
        ▼
SSIS Pipeline
        │
        ▼
dbt Transformations
        │
        ▼
Analytics Warehouse
        │
        ▼
Power BI Dashboards

dbt Layered Architecture

                        Sources
                           │
        ┌──────────────────┼──────────────────┐
        │                  │                  │
        ▼                  ▼                  ▼
     data             gearbox      innovation_pipeline
                                           │
                                           ▼
                                   protein_actual

                           │
                           ▼

──────────────────────────────────────────────
           STAGING LAYER
──────────────────────────────────────────────

stg_data

stg_gearbox

stg_innovation_pipeline

stg_protein_actual

                           │
                           ▼

──────────────────────────────────────────────
        INTERMEDIATE LAYER
──────────────────────────────────────────────

int_sales

int_projects

                           │
                           ▼

──────────────────────────────────────────────
          MART LAYER
──────────────────────────────────────────────

Dimensions

├── dim_product

├── dim_project

├── dim_date

├── dim_factory

└── dim_region

Facts

├── fact_sales

└── fact_projects

Repository Structure

modern-data-stack-pipeline
│
├── data
│   ├── raw_data
│   └── processed_data
│
├── python
│   ├── extract.py
│   ├── transform.py
│   ├── load.py
│   ├── main.py
│   └── utils.py
│
├── database
│   ├── schema.sql
│   └── warehouse_design.sql
│
├── ssis
│   ├── Turbo_ETL_Project.dtproj
│   └── Packages
│
├── dbt
│   └── turbo_transformations
│       ├── models
│       │   ├── staging
│       │   ├── intermediate
│       │   └── marts
│       │
│       ├── macros
│       ├── tests
│       ├── snapshots
│       ├── analyses
│       ├── seeds
│       ├── target
│       └── dbt_project.yml
│
├── power Bi
│
├── docs
│
├── requirements.txt
│
├── .gitignore
│
└── README.md

ETL Process

Step 1 — Extract

Read raw enterprise Excel datasets
Load operational business reports
Import financial and innovation datasets

↓

Step 2 — Transform

Clean invalid records
Standardize data types
Normalize column names
Remove duplicates
Handle missing values
Apply business rules

↓

Step 3 — Load

Load processed data into PostgreSQL
Maintain relational integrity
Prepare warehouse-ready tables

↓

Step 4 — Analytics Engineering

Create staging models
Build intermediate business models
Design star schema dimensions
Generate fact tables
Execute automated dbt tests

↓

Step 5 — Business Intelligence

Build executive dashboards
Perform KPI analysis
Enable interactive reporting
Support business decision-making

Data Warehouse Design

The analytics warehouse follows a Star Schema architecture to optimize analytical queries, simplify reporting, and improve dashboard performance.

The warehouse is organized into Dimension Tables that provide descriptive business context and Fact Tables that store measurable business metrics.

Star Schema

                         dim_date
                             │
                             │
                             │
dim_product ───────── fact_sales ───────── dim_region
                             │
                             │
                             │
                        dim_factory


                        dim_project
                             │
                             │
                             ▼
                     fact_projects

Dimension Models

dim_product

Stores standardized product information used across all analytical workloads.

AttributeDescriptionGrainOne row per unique productPrimary Keyproduct_codeRecords370

dim_project

Stores innovation project master data and project attributes.

AttributeDescriptionGrainOne row per project and sourcePrimary Keyproject_name + project_sourceRecords322

dim_date

Provides a reusable calendar dimension for time-based analytics.

AttributeDescriptionGrainOne row per calendar dateRecords49

dim_region

Stores standardized geographical information.

AttributeDescriptionRecords83

dim_factory

Stores manufacturing facility information.

AttributeDescriptionRecords4

Fact Models

fact_sales

Central fact table containing transactional sales metrics.

Business Metrics

Sales Quantity
Quantity (Ton)
Gross Sales
Normal Discount
Total Discount
Net Sales
Gross Profit
Gross Profit %

Grain

One row per product, region, factory, channel and sales month.

Records

262,893+

fact_projects

Stores innovation pipeline and product development metrics.

Business Metrics

Expected Revenue (24 Months)
Expected Revenue (2026)
Actual Monthly Sales
Actual Gross Profit
Actual Net Profit

Records

322

dbt Models

LayerModelsSources4Staging4Intermediate2Dimensions5Facts2Total Models13

Data Quality

Data quality is enforced using dbt tests to ensure consistency, integrity, and reliability across the warehouse.

Implemented validations include:

Primary Key validation
Unique constraint validation
Not Null validation
Source freshness validation
Business rule validation
Duplicate detection
Referential integrity checks

Project Statistics

MetricValueSource Systems4dbt Models13dbt Tests15Staging Models4Intermediate Models2Dimension Tables5Fact Tables2Products370Projects322Regions83Factories4Calendar Dates49Sales Records262,893+

Engineering Highlights

Developed a complete end-to-end Modern Data Stack using Python, PostgreSQL, SSIS, dbt, and Power BI.
Designed a layered dbt architecture consisting of staging, intermediate, and mart layers for maintainable SQL transformations.
Built a dimensional warehouse using Star Schema principles to improve analytical performance.
Standardized product dimensions by consolidating duplicate product descriptions into 370 unique business entities.
Generated analytics-ready fact tables containing over 262K sales records for reporting and dashboarding.
Implemented automated documentation and metadata using dbt.
Applied automated data quality testing to ensure consistency and reliability across analytical models.
Built reusable SQL models to simplify downstream reporting and business intelligence development.

Performance Optimization

The project incorporates several engineering best practices:

Modular SQL transformations
Layered data architecture
Reusable business logic
Automated documentation
Automated testing
Optimized dimensional modeling
Clean separation of staging and business logic
Enterprise-ready warehouse design
Maintainable project structure
Scalable analytics engineering workflow

dbt Documentation

The project leverages dbt to automate analytics engineering workflows and maintain comprehensive project documentation.

Implemented documentation includes:

Source Documentation
Model Documentation
Column-Level Documentation
Data Lineage Graph
Automated Data Quality Tests
Metadata Generation
Business Logic Documentation

dbt Lineage Graph

(Add Screenshot Here)

docs/dbt_lineage.png

Installation

Clone the Repository

git clone https://github.com/adityamsr2606/modern-data-stack-pipeline.git

cd modern-data-stack-pipeline

Create Virtual Environment

python -m venv .venv

Activate Environment

Windows

.venv\Scripts\activate

Linux / macOS

source .venv/bin/activate

Install Dependencies

pip install -r requirements.txt

Running the Python ETL Pipeline

python python/main.py

Running SSIS Package

Execute the SSIS package from Visual Studio or SQL Server Integration Services Catalog.

Running dbt

Navigate to the dbt project.

cd dbt/turbo_transformations

Install dependencies.

dbt deps

Validate the project.

dbt debug

Run all models.

dbt run

Run tests.

dbt test

Generate documentation.

dbt docs generate

Launch documentation.

dbt docs serve

Project Workflow

Extract
   │
   ▼
Transform
   │
   ▼
Load
   │
   ▼
PostgreSQL
   │
   ▼
SSIS
   │
   ▼
dbt
   │
   ▼
Star Schema
   │
   ▼
Power BI

Future Enhancements

CI/CD using GitHub Actions
Docker containerization
Apache Airflow orchestration
Incremental dbt models
Snapshot implementation
Data observability
Great Expectations integration
Snowflake migration
Azure Data Factory integration
Cloud deployment
Real-time streaming pipeline
Automated monitoring dashboards

Skills Demonstrated

Data Engineering

Python ETL
PostgreSQL
SSIS
SQL
Data Warehousing

Analytics Engineering

dbt
Star Schema
Data Modeling
Data Quality
Data Documentation

Business Intelligence

Power BI
KPI Development
Dashboard Design
Business Reporting

Software Engineering

Git
GitHub
Modular Architecture
Documentation
Version Control

Repository Statistics

Language        : Python + SQL

Architecture    : Modern Data Stack

Warehouse       : PostgreSQL

Transformation  : dbt

Visualization   : Power BI

Data Models     : 13

Dimension Tables: 5

Fact Tables     : 2

Tests           : 15

Sales Records   : 262,893+

Products        : 370

Projects        : 322

Contributing

Contributions, suggestions, and improvements are welcome.

If you would like to improve this project:

Fork the repository
Create a feature branch
Commit your changes
Open a Pull Request

Author

Aditya Mohan Srivastava