An end-to-end data engineering pipeline that transforms raw SaaS transactional data into a governed analytics layer for Customer Lifetime Value (CLV) and churn risk analysis. The project demonstrates practical ELT design, dimensional modeling fundamentals, and business intelligence reporting using a production-style local stack — PostgreSQL, dbt Core, and Power BI — with a fully designed path to cloud deployment.
Note: All data used in this project is synthetically generated (
scripts/generate_mock_data.py) and does not represent real customers.
Architectural Note: The current, active phase of this pipeline runs entirely on a local PostgreSQL data warehouse with dbt Core. The target cloud architecture — a Google Cloud Storage landing layer and BigQuery warehouse — has been fully designed and implemented as Infrastructure as Code using Terraform, available in the
terraform/directory. This configuration has not yet been applied to a live GCP environment, so The Terraform configuration has been fully implemented, but it has not yet been applied to provision resources in a live GCP environment.
The pipeline uses a decoupled local ELT architecture with a multi-layered schema approach inside a PostgreSQL instance (saas_dw), enforcing separation between raw and modeled data.
graph LR
subgraph "Operational Data (raw schema)"
A[(Raw Customers)] -->|Injected| C
B[(Raw Orders)] -->|Injected| D
end
subgraph "dbt Transformation Layer (analytics schema)"
C[stg_customers] -->|Data Cleansing & Casting| E[dim_customers_churn]
D[stg_orders] -->|Aggregation & Joins| E
end
subgraph "Presentation & BI Layer"
E -->|Native Import Connection| F[Power BI Dashboard]
end
style A fill:#e1f5fe,stroke:#03a9f4,stroke-width:2px
style B fill:#e1f5fe,stroke:#03a9f4,stroke-width:2px
style C fill:#fff3e0,stroke:#ff9800,stroke-width:2px
style D fill:#fff3e0,stroke:#ff9800,stroke-width:2px
style E fill:#e8f5e9,stroke:#4caf50,stroke-width:2px
style F fill:#fffde7,stroke:#fdd835,stroke-width:2px
The dbt lineage graph for this project is available via dbt docs generate and is also shown in the Screenshots section below.
- Schema Separation: Functional division between raw operational tables (
rawschema) and clean reporting structures (analyticsschema). - Idempotent SQL Transformations: Built with declarative SQL and dbt macro references (
{{ ref() }}) for deterministic, repeatable runs. - Rule-Based Risk Tagging: Conditional logic classifies customers into retention states (
active,at_risk,churned) based on order recency and value. - Cloud Infrastructure as Code: A complete Terraform configuration defining the target GCP architecture (Google Cloud Storage and BigQuery resources). The infrastructure definitions are fully implemented and version-controlled, but have not yet been applied in a live GCP environment.
- End-to-End Delivery: Covers the full path from raw table generation to an executive-facing dashboard.
- Data Cleansing: Normalizes common string issues (whitespace, casing) in the staging layer.
- Lightweight Serving Layer: Uses database views to serve the analytics layer without added infrastructure.
| Metric | Value |
|---|---|
| Source tables (raw schema) | 2 (customers, orders) |
| Staging models | 2 (stg_customers, stg_orders) |
| Mart models | 1 (dim_customers_churn) |
| Pipeline type | ELT (PostgreSQL + dbt Core) |
| Dataset | Synthetic, generated via scripts/generate_mock_data.py |
| Execution | Manual dbt run (local development workflow) |
| BI layer | 1 Power BI dashboard (saas_churn_analytics.pbix) |
| Cloud infrastructure | Terraform IaC implemented for GCS + BigQuery; not yet applied to a live environment |
- Dimensional Modeling Fundamentals: Kimball-inspired staging → mart structuring at a single fact-table scope. (Splitting into true dimension and fact tables is on the roadmap — see below.)
- Analytics Engineering: SQL (CTEs, window functions, date-interval logic) orchestrated through dbt Core.
- BI Reporting: Semantic layer connectivity, dashboard design, and metric development in Power BI Desktop.
- Infrastructure as Code (IaC): Authored a complete Terraform configuration provisioning a GCS landing bucket and BigQuery dataset for the target-state cloud data warehouse. The configuration is implemented and version-controlled; deployment to a live GCP environment is pending.
saas-modern-data-stack
├─ data_lake
│ └─ raw
├─ dbt_project
│ └─ saas_project
│ ├─ dbt_project.yml
│ ├─ docs
│ │ └─ images
│ │ ├─ dbt_data_lineage.png
│ │ └─ powerbi_saas_dashboard.png
│ ├─ macros
│ ├─ models
│ │ ├─ marts
│ │ │ └─ dim_customers_churn.sql
│ │ └─ staging
│ │ ├─ src_saas.yml
│ │ ├─ stg_customers.sql
│ │ └─ stg_orders.sql
│ └─ README.md
├─ LICENSE
├─ README.md # Root documentation (this file)
├─ reports
│ └─ saas_churn_analytics.pbix # Power BI Desktop dashboard file
├─ requirements.txt
├─ scripts
│ ├─ generate_mock_data.py
│ └─ mock_data.sql # SQL script for local raw data seed setup
└─ terraform # Terraform IaC for target GCP architecture (implemented, not yet applied)
├─ main.tf # GCS bucket and BigQuery dataset definitions
└─ provider.tf
Clone the repository and set up a Python virtual environment:
git clone <your-repository-url>
cd saas-modern-data-stack
python -m venv venv
.\venv\Scripts\Activate.ps1
pip install -r requirements.txt- Open your SQL client (e.g., pgAdmin) and connect to your local PostgreSQL instance.
- Run
scripts/mock_data.sqlto initialize the schemas and populate the raw tables with synthetic data.
Ensure your local profile (~/.dbt/profiles.yml) points to your active PostgreSQL instance:
saas_project:
outputs:
dev:
type: postgres
host: localhost
user: postgres
password: your_secure_password
port: 5433 # Set your local listener port
dbname: saas_dw
schema: analytics
threads: 4
target: devcd dbt_project/saas_project
dbt runExpected Return: Done. PASS=3 WARN=0 ERROR=0 TOTAL=3
dbt docs generate --compile
dbt docs serve --port 8005Open http://localhost:8005 to view model descriptions and the dependency graph.
The consolidated reporting view (analytics.dim_customers_churn) is imported into Power BI Desktop via a native PostgreSQL connection.
Note: all customer records shown are synthetic mock data generated for this project.
- CLV Tracking: Surfaces Customer Lifetime Value to help prioritize high-value accounts.
- Churn Risk Distribution: A donut chart showing the split across
active/at_risk/churnedsegments. - At-Risk Customer List: A filtered grid of customers flagged
at_risk, demonstrating how this data could feed a retention campaign (mock data only — no real PII involved).
- True Dimensional Model: Split
dim_customers_churninto separate dimension tables (e.g.dim_customer,dim_date) and a fact table, moving from a single denormalized mart toward a full star schema. - Automated Data Quality Tests: Add dbt core tests (
unique,not_null) in a dedicated schema validation file undertests/. - CI/CD Pipeline: Configure GitHub Actions to lint SQL via SQLFluff and run dbt tests on pull requests.
- GCP Deployment: Apply the existing Terraform configuration to provision the GCS landing bucket and BigQuery dataset in a live GCP environment, then repoint dbt from local PostgreSQL to BigQuery.
Amirhossein Qaderi — Data Engineer | Data Analyst
This project is licensed under the MIT License — see the LICENSE file for details.