Skills Demonstrated: Azure Cloud Architecture · Advanced SQL (Stored Procedures, Views, CTEs, Window Functions) · Database Normalization (3NF) · Azure Functions · REST API Design · Queue-Triggered Pipelines · NoSQL (MongoDB) · CI/CD with GitHub Actions · IaC (Bicep) · Python · C#
┌──────────────────────────────────────────────────────────────────┐
│ URBAN PULSE ANALYTICS │
│ │
│ Data Sources → Azure Event Hub → Azure Functions (Queue) │
│ ↓ │
│ Bronze Layer (Raw ADLS Gen2 Blob) │
│ ↓ │
│ Silver Layer (Azure SQL — Cleaned/Normalized) │
│ ↓ │
│ Gold Layer (Aggregated Views + Analytics API) │
│ ↓ │
│ REST API (Azure App Service) → Dashboard │
└──────────────────────────────────────────────────────────────────┘
Stack:
- Compute: Azure Functions v4 (C#), Azure App Service (.NET 8)
- Storage: Azure SQL Database, Azure Blob Storage (ADLS Gen2), Azure Storage Queue
- Messaging: Azure Storage Queues (Queue-triggered Functions)
- Monitoring: Application Insights, Azure Monitor
- IaC: Azure Bicep
- CI/CD: GitHub Actions → Azure
urban-pulse-analytics/
├── backend/
│ ├── api/ # .NET 8 REST API (Azure App Service)
│ │ ├── Controllers/
│ │ ├── Models/
│ │ ├── Services/
│ │ └── UrbanPulse.Api.csproj
│ ├── functions/ # Azure Functions (Queue + HTTP triggers)
│ │ ├── QueueIngestFunction.cs
│ │ ├── HttpQueryFunction.cs
│ │ └── UrbanPulse.Functions.csproj
│ ├── sql/ # All SQL — schema, stored procs, views
│ │ ├── 01_schema.sql
│ │ ├── 02_stored_procedures.sql
│ │ ├── 03_views.sql
│ │ ├── 04_seed_data.sql
│ │ └── 05_analytics_queries.sql
│ └── migrations/ # Flyway-style versioned migrations
│ ├── V1__initial_schema.sql
│ └── V2__add_analytics_views.sql
├── frontend/ # React dashboard (Azure Static Web Apps)
│ ├── src/
│ └── package.json
├── infrastructure/ # Azure Bicep IaC
│ ├── main.bicep
│ └── modules/
├── tests/
│ ├── api.tests/
│ └── sql.tests/
├── .github/
│ └── workflows/
│ ├── ci.yml # Run tests on every PR
│ └── cd.yml # Deploy to Azure on merge to main
├── .vscode/
│ ├── extensions.json
│ └── launch.json
├── docker-compose.yml # Local dev (SQL Server + API)
└── README.md
- VS Code with extensions (see
.vscode/extensions.json) - Docker Desktop
- Azure CLI
- .NET 8 SDK
- Azure Functions Core Tools v4
- Node.js 20+ (for dashboard)
git clone https://github.com/SiyaMathe/urban-pulse-analytics.git
cd urban-pulse-analytics
cp .env.example .env
# Edit .env with your Azure connection stringsdocker-compose up -d # Starts SQL Server + Azurite (local Azure storage emulator)cd backend/sql
# Using sqlcmd (comes with SQL Server tools)
sqlcmd -S localhost,1433 -U sa -P YourPassword123! -i 01_schema.sql
sqlcmd -S localhost,1433 -U sa -P YourPassword123! -i 02_stored_procedures.sql
sqlcmd -S localhost,1433 -U sa -P YourPassword123! -i 03_views.sql
sqlcmd -S localhost,1433 -U sa -P YourPassword123! -i 04_seed_data.sqlcd backend/api
dotnet restore
dotnet run
# API available at https://localhost:7001cd backend/functions
func start
# Functions available at http://localhost:7071cd frontend
npm install
npm run dev
# Dashboard at http://localhost:5173# Login
az login
# Create resource group
az group create --name urban-pulse-rg --location southafricanorth
# Deploy infrastructure (Bicep)
az deployment group create \
--resource-group urban-pulse-rg \
--template-file infrastructure/main.bicep \
--parameters @infrastructure/parameters.prod.jsonAdd the following secrets to your GitHub repo (Settings → Secrets → Actions):
| Secret | Description |
|---|---|
AZURE_CREDENTIALS |
Output of az ad sp create-for-rbac |
AZURE_SQL_CONNECTION_STRING |
Azure SQL connection string |
AZURE_STORAGE_CONNECTION_STRING |
Azure Storage connection string |
AZURE_FUNCTIONAPP_NAME |
Your Function App name |
AZURE_WEBAPP_NAME |
Your Web App name |
Then every push to main triggers the full CI/CD pipeline automatically.
The schema is fully normalized to 3NF with surrogate PKs throughout.
City (1) ──────< Sensor (*) >────── SensorReading (*)
|
└──── SensorType (1)
Alert (*) >────── Sensor (1)
Alert (*) >────── AlertType (1)
AnalyticsSnapshot (*) >────── City (1)
See backend/sql/01_schema.sql for the full normalized schema with all constraints.
| Feature | Location |
|---|---|
| 3NF Normalized Schema | 01_schema.sql |
| Stored Procedures (CRUD + business logic) | 02_stored_procedures.sql |
| Complex Views with CTEs | 03_views.sql |
| Window Functions (LAG, LEAD, RANK) | 05_analytics_queries.sql |
| Aggregations + GROUP BY ROLLUP | 05_analytics_queries.sql |
| Dynamic SQL | 02_stored_procedures.sql |
| Transactions + Error Handling | 02_stored_procedures.sql |
| MongoDB NoSQL queries | backend/nosql/ |
# Run all tests
dotnet test
# Run SQL tests (uses tSQLt framework concepts)
cd tests/sql.tests
sqlcmd -S localhost,1433 -U sa -P YourPassword123! -i run_tests.sqlInstall all recommended extensions at once:
- Open VS Code in the project root
- Press
Ctrl+Shift+P→ "Extensions: Show Recommended Extensions" - Click "Install All"
Or install manually (see .vscode/extensions.json).
- API Swagger Docs:
https://<your-webapp>.azurewebsites.net/swagger - Dashboard:
https://<your-staticwebapp>.azurestaticapps.net - Function Health:
https://<your-functionapp>.azurewebsites.net/api/health