Note
💬 Join the Community! Have questions about configuring Apache Airflow with Docker, dbt data modeling, or DAX measures in Power BI? Join our GitHub Discussions!
A modern, production-grade data engineering project featuring a full ETL/ELT pipeline for the Maven Analytics CRM + Sales dataset. This platform extracts raw operational data, transforms it into a robust star schema using dbt, and delivers actionable insights through a 5-page Power BI executive dashboard.
- Automated Orchestration: End-to-end pipeline managed by Apache Airflow (Astronomer).
- Modular Modeling: Data transformation layer built with dbt Core, ensuring DRY principles and version-controlled SQL.
- Star Schema Architecture: Optimized for analytical performance with conformed dimensions and clear fact grain.
- Enterprise Visualization: 5-page interactive Power BI report covering executive KPIs, agent performance, and pipeline health.
- Data Quality Framework: Multi-stage validation including dbt tests, custom SQL checks, and schema enforcement.
- Containerized Infrastructure: Fully portable environment using Docker and Astro CLI.
The pipeline follows a Medallion-style approach modified for a Warehouse:
- Staging: Raw data loaded into Postgres as-is from Parquet snapshots.
- Warehouse (dbt): Modular transformations create a cleaned, typed, and modeled Star Schema.
- Reporting: Power BI consumes the
analytics.*schema for optimal report performance.
flowchart TD
subgraph Ingestion ["📤 Ingestion & Storage"]
Raw["Raw Data (Maven Analytics CSVs)"]
Parquet[("📦 Parquet Snapshots\n(Immutable Raw Lake)")]
end
subgraph Staging ["🐘 PostgreSQL Staging"]
PGStaging[("raw.sales_staging\n(Raw Tables)")]
end
subgraph Transformation ["⚡ dbt Core Transformations"]
DBTModels[("analytics.dim_* & fact_*\n(Star Schema Models)")]
DBTTests{"🔍 dbt Validation\n(Schema & Data Tests)"}
end
subgraph Quality ["🛡️ Quality & Governance"]
Checks["quality_checks.py\n(Parity & Integrity Suite)"]
end
subgraph BI ["📊 Business Intelligence"]
PBI["Microsoft Power BI\n(5-Page Executive Report)"]
end
Raw -->|"extract.py"| Parquet
Parquet -->|"load_staging.py"| PGStaging
PGStaging -->|"dbt run"| DBTModels
DBTModels -->|"dbt test"| DBTTests
DBTTests -->|"Pass"| Checks
Checks -->|"Validated"| PBI
subgraph Orchestration ["⚙️ Apache Airflow (Astro CLI)"]
Airflow["Task & DAG Orchestration"]
end
Airflow -.-> Raw
Airflow -.-> PGStaging
Airflow -.-> DBTModels
style Raw fill:#e53935,stroke:#333,stroke-width:2px,color:#fff
style Parquet fill:#fb8c00,stroke:#333,stroke-width:2px,color:#fff
style PGStaging fill:#1e88e5,stroke:#333,stroke-width:2px,color:#fff
style DBTModels fill:#43a047,stroke:#333,stroke-width:2px,color:#fff
style DBTTests fill:#8e24aa,stroke:#333,stroke-width:2px,color:#fff
style Checks fill:#00acc1,stroke:#333,stroke-width:2px,color:#fff
style PBI fill:#fbc02d,stroke:#333,stroke-width:2px,color:#000
style Airflow fill:#37474f,stroke:#ffd54f,stroke-width:2px,color:#fff
The project is fully containerized, providing a consistent environment for all services including PostgreSQL, Airflow, and pgAdmin.
| Service | Monitoring Tool | Status |
|---|---|---|
| Airflow | Astro UI | ✅ Active |
| Database | SQLTools / pgAdmin | ✅ Active |
| Containers | Docker Desktop | ✅ Active |
Visualizing the successful execution of the end-to-end extraction and transformation pipeline.
The full containerized stack including scheduler, worker, and database services.
Detailed view of the active service containers.
The final output is a high-impact, 5-page executive report.
Won revenue, win rate, average deal size, and at-risk pipeline at a glance. Monthly trend and full pipeline funnel side by side.
Detailed breakdown of sales agent efficiency and manager-level performance.
Visualizing sales stages, stall rates, and conversion funnels to identify bottlenecks.
Analysis of product performance and geographic revenue distribution.
The database is structured using a Star Schema for optimal query performance. Dimensions like dim_accounts, dim_products, and dim_sales_teams support the central fact_sales_pipeline.
Exploration of the analytics schema and dimension tables within VS Code SQLTools.
| Layer | Technology | Description |
|---|---|---|
| Orchestration | Apache Airflow | Pipeline scheduling and management via Astro CLI. |
| Transformation | dbt Core | SQL modeling, testing, and documentation for the warehouse. |
| ETL / Scripting | Python 3.11 | pandas, SQLAlchemy, PyArrow for high-performance processing. |
| Database | PostgreSQL 16 | Containerized warehouse with staging and analytical schemas. |
| Infrastructure | Docker | Containerization for consistent dev/prod environments. |
| Visualization | Power BI | Advanced DAX modeling and interactive dashboard design. |
- Docker & Docker Compose
- Astro CLI
- Python 3.11+
Run the following to spin up the entire data platform:
astro dev startCopy the environment template and configure your local credentials:
cp .env.example .envIf you wish to run components individually:
# Run dbt transformations
cd crm_warehouse_dbt
dbt run
# Run quality checks
python etl/quality_checks.pyWe enforce high data standards at every step:
- dbt Tests: Primary keys, relationships, and accepted values.
- Custom Checks:
- Parity: Row count matching between staging and warehouse.
- Freshness: Ensuring data is up-to-date.
- Referential Integrity: Fact rows must resolve to valid dimensions.
- Python Quality: Ruff for linting and Pytest for transformation logic.
- Data Source: Maven Analytics — CRM + Sales
- License: MIT
Built with ❤️ by Shaan