W3C web logs, ingested raw → served as a business-ready Power BI datamart, on a fully serverless Azure stack with zero static credentials.
ETL or ELT? ELT, mostly. Raw logs land untouched in Bronze, get enriched in-lake in Silver - GeoIP + computed fields, and dbt models the star schema after the load into Azure SQL - the medallion pattern: load raw, transform in place, model last. The name stays ETL because that's what recruiters search for - and the Silver → Azure SQL leg really is ETL: enriched before it's exported. Same pipeline, either label.
Click the preview to open the PDF export of the 7-page Power BI dashboard (live report on request)
Bronze → Silver → Azure SQL → dbt → Power BI, on Databricks serverless, with OpenLineage → Marquez observing every orchestrated stage across both engines. Power BI reads the dbt marts directly from Azure SQL - no file exports; the reports do not emit lineage.
flowchart LR
classDef source fill:#3b82f6,color:#fff,stroke:#1e40af
classDef ingest fill:#10b981,color:#fff,stroke:#047857
classDef dlt fill:#8b5cf6,color:#fff,stroke:#6d28d9
classDef sql fill:#f59e0b,color:#fff,stroke:#d97706
classDef dbtclass fill:#ef4444,color:#fff,stroke:#dc2626
classDef bi fill:#ec4899,color:#fff,stroke:#db2777
classDef lineage fill:#7c3aed,color:#fff,stroke:#5b21b6
source["93 W3C IIS log files<br/>2009–2011"]:::source
adls["ADLS Gen2<br/>raw-logs container"]:::ingest
bronze["DLT Bronze (serverless)<br/>parse • validate • partition"]:::dlt
silver["DLT Silver (serverless)<br/>GeoIP enrichment • Bronze CDC feed"]:::dlt
jdbc["JDBC Export (notebook_task)<br/>pymssql batch write"]:::sql
azsql["Azure SQL Serverless<br/>dbo.raw_enriched"]:::sql
dims["Airflow: export_dimensions<br/>SCD Type 2 + MERGE dims"]:::dbtclass
dbt["dbt - 16 models • 121 tests<br/>dual-dialect T-SQL / PostgreSQL"]:::dbtclass
csv["18 CSV exports<br/>self-serve download"]:::bi
powerbi["Power BI<br/>7-page dashboard<br/>weekly refresh via Power Automate"]:::bi
marquez["OpenLineage → Marquez<br/>cross-engine lineage"]:::lineage
source -->|"ABFSS path"| adls
adls -->|"Auto Loader"| bronze
bronze -->|"Change Data Feed"| silver
silver -->|"collect() + pymssql"| jdbc
jdbc --> azsql
azsql --> dims
dims -->|"Dataset trigger"| dbt
dbt -->|"reads marts via Azure SQL"| powerbi
dbt -->|"self-serve exports"| csv
dims -.->|"task events"| marquez
dbt -.->|"quality facet"| marquez
Every piece, in one line:
| Component | What it does |
|---|---|
| Azure Databricks DLT | Serverless Bronze → Silver: custom W3C parser, GeoIP enrichment, Bronze Change Data Feed - zero cluster management |
| PySpark jobs | Raw Spark beyond DLT: RDD log parser (Bronze), GeoIP/UA UDF enrichment (Silver), skew-resilient range backfill - salt + broadcast + Delta replaceWhere |
| Azure SQL | Serverless warehouse; Silver → Azure SQL via pymssql export |
| Apache Airflow | 4 DAGs wired by dataset triggers - ingestion → dimensions → dbt, no polling |
| dbt | 16 models compiling against both T-SQL and PostgreSQL from one source |
| Power BI | 7-page live dashboard on a semantic contract: logic in dbt, presentation in BI |
| Terraform | Entire Azure estate as code, GitHub→Azure auth via OIDC - zero static secrets |
| Unity Catalog | Governed catalogs, schemas, volumes, cross-pipeline spark.table() reads |
| CI/CD | 7 workflows: lint, test, dbt-compile, terraform plan→apply, smoke test |
| Observability | 3 Grafana dashboards, 8 Prometheus alert rules - DAG duration to data freshness |
| OpenLineage → Marquez | Cross-engine lineage for every run, plus a custom w3cDataQuality facet |
| Docker dev stack | Same 16-service pipeline locally - Airflow on Celery+Redis, Spark, Postgres, Grafana - for fast iteration and CI |
| Highlight | Why It Matters |
|---|---|
| JDBC export - Silver → Azure SQL via pymssql with tracking-table idempotency. | Databricks serverless only supports JDBC reads, not writes, so the export uses pure-Python pymssql. Deep dive |
| Skew-resilient PySpark backfill - idempotent Delta replaceWhere range rewrite with salted repartition over (log_date, salt) and a broadcast crawler-IP join. | Tested on a 14x hotspot fixture: partition max/min ratio drops 14 → 1.47, plan shows BroadcastHashJoin, rerun converges to identical row counts. Deep dive |
| Dual-dialect dbt - all 16 models compile against PostgreSQL (dev/CI) and T-SQL (Azure SQL/prod) via inline dialect branches, no duplicate model files. | One model, two databases, one source of truth. Deep dive |
| Terraform with OIDC - two Terraform parts provision the whole estate, including the GitHub→Azure auth chain itself. | Zero static credentials: the runner assumes an Azure AD identity via token exchange, not client secrets. One terraform apply from scratch. Deep dive |
| 4-layer observability - Grafana dashboards, Prometheus alerting on a StatsD stream, Azure Monitor alerts, and OpenLineage lineage. | DAG durations, container health, data freshness, and pipeline lineage are all tracked from day one. Deep dive |
| Metric | Value |
|---|---|
| Requests served | 155.6K across 88 active countries (BI) / 30+ GeoIP-resolved (Silver) |
| Traffic | 62% human / 38% bot, 9.7% 404 rate |
| Bronze rows ingested | 153,380 - 0 dropped at Bronze (7 of 10 DLT quality-gate checks) |
| Silver rows exported | 153,377 to Azure SQL via pymssql (3 filtered: invalid country) |
| DLT quality-gate checks | 10 - 7 Bronze + 3 Silver (@dlt.expect_or_drop) |
| dbt models | 16 (10 staging + 6 marts), dual-dialect T-SQL/PostgreSQL |
| dbt data tests | 121 (not_null, unique, accepted_values, relationships, expression_is_true, singular) |
| pytest | 629 total (599 in CI: 482 unit + 92 terraform + 25 DAG integrity) |
| Partition skew (backfill test) | max/min 14 → 1.47 via salted repartition over (log_date, salt); BroadcastHashJoin verified in plan |
| Orchestration | 4 Airflow DAGs, dataset-triggered; 7 GitHub Actions workflows |
| Observability | 3 Grafana dashboards (23 panels), 8 Prometheus + 2 Azure Monitor alerts |
| Cost | ~$0–100/mo - serverless auto-scales to zero, $50 warning / $100 hard cap |
The 7-page Power BI dashboard (PDF export):
Orchestration in action - Airflow DAG graphs and gantts, Databricks workflow + DLT pipeline runs, and the Power Automate refresh schedule:
The Azure estate - resource group, ADLS, the Azure SQL schema and table row counts, Unity Catalog:
Observability - Grafana dashboards and the Prometheus targets/alert rules behind them:
CI/CD - the 4 parallel CI jobs, terraform plan→apply→smoke-test CD run, and the rollback story:
DBT modeling - the dbt lineage graph and the star-schema dimension tables behind the Power BI report:
| Decision | Alternative | Why This Won |
|---|---|---|
Dual-dialect dbt: inline {% raw %}{% if target.type == 'sqlserver' %}{% endraw %} branches |
Per-dialect model files (_azure.sql) |
dbt would parse both as independent models - duplicate DAG entries. Inline branches keep one source of truth. |
| Serverless DLT over classic clusters | Fixed job clusters with VMs | Zero infrastructure management: scales to zero when idle, no cluster tuning ever. |
SCD Type 2 for dim_geolocation over append-only/Type 1 |
In-place overwrite | Full attribute history plus current-state performance, via a T-SQL MERGE ... OUTPUT pattern in the Azure SQL load (Airflow export_dimensions); the local Postgres path stays SCD1-style upserts. |
| Thin Power BI reports - transforms stay in dbt/SQL | Logic embedded in Power BI DAX | The report is a presentation layer over a semantic contract; the warehouse stays the single source of truth. |
| OIDC over static secrets | API keys / client secrets in Azure DevOps | Zero static Azure credentials: federated identity via token exchange, Terraform-managed from repo to role assignment. |
tuple(row) over row.asDict() |
DataFrames in the JDBC export loop | Avoids dict construction overhead — the 153K-row feed to pymssql executemany uses SparkRow.__iter__ directly. |
| Weekly Power BI refresh via a Power Automate cloud flow | Streaming / DirectQuery to the lakehouse | The source is 2009-2011 historical logs - a weekly refresh validates all 5 upstream layers once, without idle compute spend. |
All 15 decisions, with alternatives and reasoning: Design Decisions.
docker compose -f pipeline/docker-compose.yaml up -d # 16-service Airflow+Spark+PG+Grafana stack
dbt deps --project-dir pipeline/dbt/w3c --profiles-dir pipeline/dbt
dbt run --project-dir pipeline/dbt/w3c --profiles-dir pipeline/dbt
dbt test --project-dir pipeline/dbt/w3c --profiles-dir pipeline/dbt
uv run pytest tests/ -m "not integration and not dbt_compile"
cd terraform/platform && terraform init -backend=false && terraform test- Backfilling a date range:
spark-submit pipeline/spark/jobs/silver_backfill.pyre-runs full Silver enrichment for alog_daterange via DeltareplaceWhere- details: docs/README-full.md
The 16-service compose stack this spins up:
Full local, lineage, and Azure instructions: docs/README-full.md.
The complete design document - all 11 component deep dives, every proof image, the full design-decisions table - lives at docs/README-full.md.
- LAAD - ATM log aggregation & diagnostics platform with Kafka streaming, 3-layer ML/heuristic anomaly detection, and an Agentic RAG diagnostic assistant with multi-signal confidence fusion.
- DevSync - full-stack project tracker with real-time collaboration and GitHub OAuth integration
- StockLens - FinTech mobile app: OCR receipt scanning, portfolio analytics, LSTM forecasting, self-built MCP server
Built with Azure, Databricks, dbt, Airflow, OpenLineage, Terraform, Python, and SQL.