Skip to content
BranchesTags

Repository files navigation

SaaS Cloud SQL Analytics Pipeline

Python 3.11+ Azure

A production-grade SaaS analytics data pipeline on Azure, demonstrating layered data architecture from raw ingestion through warehouse modeling, data quality enforcement, and business metrics.

Architecture

Generator ──► Azure Blob (raw/) ──► Azure SQL ──► Power BI / Jupyter
                                       │
                          ┌─────────────┼─────────────┐
                          ▼             ▼             ▼
                      raw.* ──► stg.* ──► wh.* ──► metrics.*
                                          │
                                      dq.* (gate)

Data flow: Synthetic generator → Azure Blob Storage (raw landing zone) → Azure SQL Database with five explicit layers:

Layer Schema Purpose
Raw raw.* 5 landing tables, all NVARCHAR, ingestion timestamps
Staging stg.* Typed views with TRY_CAST, NULL filtering, case normalization
Warehouse wh.* Star schema: 3 dimensions + 4 fact tables
Data Quality dq.* Automated checks with pass/fail gate enforcement
Metrics metrics.* 6 analytics-ready views for BI consumption

Technologies

Category Stack
Cloud Azure Blob Storage, Azure SQL Database
Languages Python 3.11+, T-SQL
Data Processing pandas, NumPy, SQLAlchemy, pyodbc
Orchestration Azure Data Factory (spec), Python CLI
Visualization Power BI (spec), Plotly, Matplotlib, Seaborn
CI/CD GitHub Actions
IaC Bicep (Azure Resource Manager)
Containerization Docker, Docker Compose
Testing pytest, pytest-cov

Key Features

  • Layered Architecture: RAW → STAGING → WAREHOUSE → METRICS with clear separation
  • Data Quality Gates: Pipeline halts if any DQ check fails (negative MRR, duplicate users, date logic, orphan invoices)
  • SaaS Metrics: MRR, ARR, NRR waterfall, churn rate, cohort retention, LTV/CAC, trial conversion
  • Realistic Data Generator: 150K users with upgrades, downgrades, churn (geometric distribution), and 20% reactivation
  • Infrastructure as Code: Bicep template for reproducible Azure resource provisioning
  • CI Pipeline: Unit tests and SQL file checks on push/PR, plus secrets-gated Azure SQL integration parity validation

Dataset Scale

Validated large-scale run (data/extract_big):

  • Users: 200,000
  • Subscriptions: 244,608
  • Invoices: 1,163,599
  • Feature Usage Events: 2,909,253
  • Support Tickets: 87,241

Total records processed across core transactional tables: ~4.6M.

Validation Status

  • Data quality checks: PASS (dq.latest_data_quality_results)
  • SQL <-> Notebook metric parity: PASS (_parity_check.py)
  • NRR phantom-month bug: FIXED (MAX(metrics.monthly_nrr.month_start) == MAX(metrics.monthly_mrr.month_start))

Dashboard Evidence

5 Power BI pages are captured in:

  • powerbi/screenshots/executive_overview.png
  • powerbi/screenshots/revenue_waterfall.png
  • powerbi/screenshots/cohort_retention.png
  • powerbi/screenshots/unit_economics.png
  • powerbi/screenshots/product_engagement.png

Dashboard Preview

Executive Overview Revenue Waterfall
Executive Overview Revenue Waterfall
Cohort Retention Unit Economics
Cohort Retention Unit Economics
Product Engagement / Trial Funnel
Product Engagement

Project Structure

cloud_sql/
├── generator/
│   └── generate_data.py          # Synthetic SaaS data generator
├── pipeline/
│   ├── upload_to_blob.py         # Azure Blob Storage uploader
│   ├── load_raw_to_sql.py        # CSV → Azure SQL raw tables
│   └── run_sql_pipeline.py       # SQL script executor with DQ gates
├── sql/
│   ├── 00_schemas.sql            # Schema bootstrap (raw, stg, wh, dq, metrics)
│   ├── 01_raw_tables.sql         # Raw landing tables
│   ├── 02_staging_views.sql      # Typed cleanup views
│   ├── 03_warehouse_schema.sql   # Star schema (dim/fact tables)
│   ├── 04_load_warehouse.sql     # ETL: staging → warehouse
│   ├── data_quality_checks.sql   # DQ framework with gate enforcement
│   ├── 05_metrics_layer.sql      # Business metric views
│   └── 06_insight_queries.sql    # Example analyst queries
├── notebooks/
│   ├── 01_eda_saas_data.ipynb              # Exploratory data analysis
│   └── 02_sql_insights_visualization.ipynb # Metric visualizations
├── tests/
│   ├── conftest.py               # Shared pytest fixtures
│   ├── test_generator.py         # Generator unit tests (38 tests)
│   └── test_pipeline.py          # Pipeline unit tests (20 tests)
├── infra/
│   └── main.bicep                # Azure IaC (Storage + SQL)
├── docs/
│   └── adf_pipeline.md           # ADF orchestration spec
├── powerbi/
│   └── dashboard_spec.md         # 5-page dashboard requirements
├── .github/workflows/
│   └── ci.yml                    # GitHub Actions CI pipeline
├── Dockerfile                    # Multi-stage (base + dev/Jupyter)
├── docker-compose.yml            # Pipeline + Jupyter services
├── requirements.txt
└── .env.example                  # Credential template

Quick Start

1. Install Dependencies

pip install -r requirements.txt

2. Generate Data

# Demo dataset (5K users, fast)
python generator/generate_data.py --users 5000 --output-dir data/extract

# Full dataset (150K users)
python generator/generate_data.py --users 150000 --output-dir data/extract

3. Configure Azure Credentials

cp .env.example .env
# Edit .env with your Azure Storage and SQL credentials

4. Upload to Azure Blob

python pipeline/upload_to_blob.py \
  --input-dir data/extract \
  --connection-string "$AZURE_STORAGE_CONNECTION_STRING" \
  --container raw --prefix saas

5. Build SQL Layers

python pipeline/run_sql_pipeline.py \
  --sql-server "$AZURE_SQL_SERVER" \
  --sql-database "$AZURE_SQL_DATABASE" \
  --sql-user "$AZURE_SQL_USER" \
  --sql-password "$AZURE_SQL_PASSWORD"

6. Load Data

python pipeline/load_raw_to_sql.py \
  --sql-server "$AZURE_SQL_SERVER" \
  --sql-database "$AZURE_SQL_DATABASE" \
  --sql-user "$AZURE_SQL_USER" \
  --sql-password "$AZURE_SQL_PASSWORD" \
  --input-dir data/extract --truncate

7. Re-run Warehouse + Metrics

python pipeline/run_sql_pipeline.py \
  --sql-server "$AZURE_SQL_SERVER" \
  --sql-database "$AZURE_SQL_DATABASE" \
  --sql-user "$AZURE_SQL_USER" \
  --sql-password "$AZURE_SQL_PASSWORD"

8. Validate Metric Parity

python _parity_check.py

Expected terminal ending:

Overall: PASS

Data Model

Dimensions

  • wh.dim_users — User attributes, acquisition data, trial info
  • wh.dim_plans — Pricing tiers (Basic $29 / Growth $99 / Pro $299 / Enterprise $999)
  • wh.dim_date — Calendar dimension (auto-generated from data range)

Facts

  • wh.fact_subscriptions — Subscription lifecycle events (new, upgrade, downgrade, churn, reactivation)
  • wh.fact_invoices — Monthly billing records with payment status
  • wh.fact_usage — Feature usage tracking (api_calls, projects, reports, integrations, seats)
  • wh.fact_support — Support tickets with CSAT scores and resolution times

Metrics Views

View Description
metrics.monthly_mrr MRR, ARR, active customer count
metrics.monthly_churn Customer and revenue churn rates
metrics.monthly_nrr Net Revenue Retention with expansion/contraction waterfall
metrics.retention_cohort Cohort retention matrix for heatmap
metrics.ltv_cac_analysis Unit economics by acquisition channel
metrics.trial_conversion_analysis Trial funnel metrics

Testing

# Run all tests
python -m pytest tests/ -v

# With coverage report
python -m pytest tests/ -v --cov=generator --cov=pipeline --cov-report=term-missing

58 tests covering: date utilities, plan selection logic, user generation, lifecycle simulation, CSV output, SQL execution order, file-to-table mapping, and ODBC driver resolution.

Docker

# Generate data
docker compose run pipeline

# Launch Jupyter notebooks
docker compose up jupyter
# Access at http://localhost:8888

Infrastructure as Code

Provision Azure resources with Bicep:

az deployment group create \
  -g <resource-group> \
  -f infra/main.bicep \
  --parameters sqlAdminUser=<user> sqlAdminPassword=<password>

Creates: Storage Account + Blob container + SQL Server + Database + Firewall rule.

Notebooks

Notebook Purpose
01_eda_saas_data.ipynb User demographics, trial conversion, subscription lifecycle, revenue patterns, feature usage, support analysis
02_sql_insights_visualization.ipynb MRR trend, churn rate, NRR waterfall, cohort retention heatmap, LTV/CAC ratios, acquisition funnel

Both notebooks work offline from CSV data — no Azure connection required for demo.

Power BI evidence artifacts are in powerbi/screenshots/ (5 pages) and mapped in powerbi/README.md.

Layer Definitions

  • RAW: raw.raw_users, raw.raw_subscriptions, raw.raw_invoices, raw.raw_feature_usage, raw.raw_support_tickets
  • STAGING: Typed cleanup views (stg.stg_*)
  • WAREHOUSE: Star schema (wh.dim_*, wh.fact_*)
  • DATA QUALITY: dq.data_quality_checks + dq.latest_data_quality_results
  • METRICS: Business marts (metrics.monthly_mrr, metrics.monthly_churn, metrics.monthly_nrr, metrics.ltv_cac_analysis, metrics.trial_conversion_analysis, metrics.retention_cohort)

License

MIT

About

Production-grade SaaS analytics pipeline on Azure — layered SQL architecture, Jupyter notebooks, CI/CD, Docker, IaC

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors

Languages