Compliance Reporting – Big-4 Audit & Consulting Firm (Azure)

Sanitized, docs-only Compliance Reporting case study for a Big-4 Audit & Consulting Firm on Azure
(BFSI • Audit/Compliance • Healthcare-style datasets • Batch Pipeline (ELT) + Streaming Pipeline (ETL) + ML risk scoring).

Patterns only – no client code, no client data.

---

Quick Facts

• Domain: BFSI • Audit/Compliance • Healthcare-style data
• Cloud: Azure (ADF, Azure SQL, Synapse, Snowflake, Power BI, Azure ML, Key Vault, RBAC)
• Pipelines: Streaming Pipeline (ETL) for near-real-time risk signals; Batch Pipeline (ELT) for historical compliance reporting
• Throughput (simulated): ~250 events/second peak, ≈ 22M events/day across all sources
• SLOs (simulated):
  • p95 end-to-end latency to compliance marts < 90 seconds for hot events
  • p99 daily batch completion by T+1 06:00 UTC
  • ≥ 97% DQ pass rate on critical controls, DLQ rate < 0.5%
• Outputs: Snowflake compliance marts + risk_scores table, Power BI dashboards for auditors & clinical governance
• Security: RBAC, network isolation (Private Link), encryption with Key Vault-managed keys, full auditability

---

1. What this project is about

This repo documents a Compliance Reporting data platform built for a Big-4 Audit & Consulting Firm working with healthcare clients on Azure.

The goal is to provide HIPAA-style, audit-ready reporting and ML-based risk scoring on top of healthcare transactions, while keeping Data Quality, Data Governance, and Data Lineage explicit and testable.

1.1 Inputs

All numbers are simulated but realistic and can be tuned per environment.

Primary input streams (Streaming Pipeline – ETL):

• De-identified healthcare transactions (claims, encounters, lab events) emitted as JSON to Azure Event Hubs
• Typical event payload: patient surrogate keys, facility, clinical/billing event type, amount, and metadata
• Peak rate: ~250 events/second during business hours, ≈ 22M events/day

Batch inputs (Batch Pipeline – ELT):

• Nightly extracts from:
  • EHR systems (encounters, diagnoses, procedures)
  • Billing & claims systems
  • Reference/master data (providers, facilities, insurers, products)
• Landed by Azure Data Factory into Azure Data Lake Storage Gen2 (raw) as parquet/JSON files
• Historical replays/backfills supported for ≥ 2 years of data

1.2 Outputs

The platform creates two main output families in Snowflake:

1. Compliance marts (Batch ELT)

   • fact_transactions_compliance
   • dim_patient_surrogate
   • dim_facility
   • fact_phi_access_audit
   • Partitioned by event_date and clustered by facility_id, line_of_business

2. ML risk scoring outputs (Streaming ETL + Batch ELT)

   • risk_scores table with:
     • score in [0, 1]
     • score_band ∈ {LOW, MEDIUM, HIGH, CRITICAL}
     • model_version, features_version, pipeline_run_id
   • Used directly by Power BI dashboards and compliance workflows

Power BI surfaces compliance KPIs (exceptions, delayed filings, high-risk cohorts) and ML-driven risk scores to auditors, clinical review boards, and operational teams.

1.3 What the pipeline actually does (business logic)

High-level business logic:

1. Ingest & normalize

   • Streaming events from Event Hubs and batch extracts from ADF land in Synapse (raw/bronze).
   • Schemas are validated using JSON data contracts (transactions.schema.json, risk_scores.schema.json).

2. Standardize & enrich

   • Normalize codes (diagnosis, procedure, insurance, product).
   • Map PHI identifiers to surrogate keys with synthetic masking for lower environments.
   • Attach reference data (provider specialty, facility risk tier, payer type).

3. Compliance rule evaluation

   • Encode HIPAA-style rules (e.g., timeliness, access control, completeness) as SQL and Spark checks.
   • Compute compliance flags at transaction level: late_submission_flag, missing_consent_flag, etc.
   • Failed checks are routed to DLQs or exception tables for manual remediation.

4. ML risk scoring

   • A curated feature set is passed into an Azure ML risk model (or Synapse Spark ML) to assign risk scores per transaction/patient.
   • Predictions are written to the risk_scores Snowflake table with strong versioning (model_version, features_version).

5. Reporting & auditability

   • Compliance marts and risk scores power Power BI dashboards, exports for regulatory filings, and reviewer queues.
   • Every pipeline run is tagged by pipeline_run_id and stored in audit tables for replay and traceability.

---

2. Architecture overview (Batch & Streaming Pipelines)

This project combines Streaming Pipeline (ETL) and Batch Pipeline (ELT) on Azure.

2.1 L2 Architecture – Compliance Reporting on Azure

flowchart LR
  subgraph Sources
    ehr[EHR Systems]
    billing[Billing & Claims]
    ref[Reference & Master Data]
  end

  ehr --> adf[Azure Data Factory<br/>Batch Ingestion]
  billing --> adf
  ref --> adf

  adf --> adls[Azure Data Lake Gen2<br/>(Raw Zone)]
  adls --> syn_raw[Azure Synapse<br/>SQL / Spark (Raw)]
  syn_raw --> syn_cur[Synapse (Curated)]
  syn_cur --> snow_mart[Snowflake<br/>Compliance Marts]
  snow_mart --> pbi[Power BI<br/>Compliance Dashboards]

  subgraph Streaming
    evh[Azure Event Hubs]
    syn_stream[Synapse Spark Streaming]
  end

  evh --> syn_stream
  syn_stream --> syn_cur

  syn_cur --> aml[Azure ML<br/>Risk Model]
  aml --> snow_scores[Snowflake<br/>risk_scores]
  snow_scores --> pbi

Loading

Key points:

• Streaming Pipeline (ETL): Event Hubs → Synapse Spark Streaming → Synapse Curated → Snowflake risk_scores.
• Batch Pipeline (ELT): ADF → ADLS Gen2 → Synapse (Raw/Curated) → Snowflake compliance marts → Power BI.
• Governance & security: enforced with RBAC, Key Vault-backed secrets, Private Link, and row/column-level rules in Synapse/Snowflake.
• Lineage: captured using Microsoft Purview and Snowflake query history.

---

3. Dataflow & ML pipeline – event journey

3.1 Event-level dataflow (simplified sequence)

sequenceDiagram
  participant Src as Source System (EHR/Billing)
  participant ADF as ADF / Event Hubs
  participant SYN as Synapse (Raw → Curated)
  participant AML as Azure ML Risk Model
  participant SNF as Snowflake (Marts + risk_scores)
  participant PBI as Power BI / Auditors

  Src->>ADF: Emit transaction (JSON/Parquet)
  ADF->>SYN: Land to raw tables / files
  SYN->>SYN: Validate against transactions.schema.json<br/>Apply DQ checks & masking
  SYN->>SYN: Enrich with reference/master data
  SYN->>AML: Send feature set for scoring (batch or mini-batch)
  AML-->>SYN: Predicted risk score + band + model_version
  SYN->>SNF: Load compliance facts + risk_scores
  SNF-->>PBI: Dashboards & exports for audits / review boards

Loading

Streaming events from Event Hubs follow an almost identical journey but enter directly through Synapse Spark Streaming instead of pure batch.

3.2 Pipelines in this repo

Streaming Pipeline (ETL) – near-real-time risk updates:

• Event Hubs → Synapse Spark Streaming → Synapse Curated → Snowflake risk_scores.
• Latency SLO: p95 < 90 seconds from event ingestion to risk_scores row.
• Primary purpose: give auditors and clinical governance near-real-time visibility into risky activity.

Batch Pipeline (ELT) – historical compliance reporting:

• ADF → ADLS Gen2 → Synapse Raw → Synapse Curated → Snowflake compliance marts → Power BI.
• Runs hourly, daily, and ad-hoc for backfills.
• Ensures T+1 compliance reporting for up to 2+ years of historical data.

---

4. Extra care for the first ML pipeline

Because this is the first ML-enabled pipeline in the portfolio, a few additional safeguards are built in:

1. Model registry & versioning

   • All models are registered in Azure ML with semantic versioning (model_name, model_version).
   • Each prediction in risk_scores carries model_version, features_version, and pipeline_run_id.

2. Schema & feature contracts

   • Training and serving share the same feature contracts (documented in transactions.schema.json and feature docs).
   • CI checks fail if the scoring schema drifts without an explicit ADR and version bump.

3. Model rollback

   • Config tables in Snowflake define the active model_version and safety thresholds.
   • The RUNBOOK documents a one-click rollback pattern:
     • flip config to previous version,
     • re-deploy scoring endpoint/pipeline,
     • optionally re-score a time window.

4. Fairness, bias, and explainability

   • Protected attributes are either removed, masked, or used only with explicit justification.
   • Simple explainers (feature importance at cohort level) are logged for audits.

5. Monitoring & drift

   • Data drift and score distribution drift metrics are computed daily.
   • Guardrail alerts when a model’s performance deviates from baselines.

6. Environment separation

   • DEV/UAT use synthetic or masked data only.
   • Production access is restricted through RBAC and just-in-time approvals.

Details for replay, backfill, and model rollback are in RUNBOOK.md.

---

5. Repo map (docs-only)

big4-audit-compliance-reporting-azure/
├─ README.md
├─ RUNBOOK.md
├─ SECURITY.md
├─ ETHICS.md
├─ LICENSE
├─ CODEOWNERS
├─ CODE_OF_CONDUCT.md
├─ CONTRIBUTING.md
├─ .pre-commit-config.yaml
├─ .markdownlint.jsonc
├─ .markdownlint-cli2.jsonc
├─ .editorconfig
├─ qc_examples.sql
├─ adr/
│  └─ 0001-use-adf-synapse-snowflake-ml.md
├─ contracts/
│  ├─ transactions.schema.json
│  └─ risk_scores.schema.json
└─ docs/
   ├─ 01-context.md
   ├─ 02-architecture-overview.md
   ├─ 03-pipeline-spec.md
   ├─ 04-sequence-and-dataflow.md
   ├─ 05-schemas-and-data-models.md
   ├─ 06-data-quality.md
   ├─ 07-security-governance.md
   ├─ 08-lineage-observability.md
   ├─ 09-orchestration-ops.md
   └─ 10-cost-slos-roadmap.md

---

6. Docs index

• docs/01-context.md – business context & sanitized storyline
• docs/02-architecture-overview.md – full architecture for Streaming ETL + Batch ELT
• docs/03-pipeline-spec.md – detailed pipeline specifications (sources, sinks, schedules)
• docs/04-sequence-and-dataflow.md – detailed dataflows & sequence diagrams
• docs/05-schemas-and-data-models.md – logical models + Snowflake tables
• docs/06-data-quality.md – DQ rules, SLIs/SLOs, DLQ strategy
• docs/07-security-governance.md – RBAC, Key Vault, network security, compliance notes
• docs/08-lineage-observability.md – lineage with Purview, observability with Log Analytics
• docs/09-orchestration-ops.md – ADF/Synapse orchestration, replay/backfill patterns
• docs/10-cost-slos-roadmap.md – cost guardrails, SLO summary, future enhancements

---

7. Relationship to other projects

This case study represents Phase‑1 of the Azure compliance platform:

• Phase‑1 – Compliance Reporting – Big‑4 Audit & Consulting Firm (Azure):

  • Built the governed Batch ELT + Streaming ETL pipelines for HIPAA-style compliance reporting.
  • Implemented the first ML risk‑scoring pipeline (Azure ML) on top of these compliance datasets.

• Phase‑2 – Compliance & Anomaly Detection – Big‑4 Audit & Consulting Firm (Azure + Databricks):

  • Extended the same compliance data into a full ML anomaly‑detection platform using Azure Databricks, Delta Lake, and MLflow.
  • Focus on reducing rule‑only false positives and improving real‑time risk visibility.

This repo documents Phase‑1; the Databricks anomaly‑detection project is documented separately.