Fouzan Ahmed.
All projects
Data EngineeringFinancial AnalyticsAI/LLM IntegrationBusiness Intelligence

APRA Data Engineering & Analytics Platform

Regulatory superannuation filings, from raw Excel to a live Power BI dashboard, fully automated on AWS.

Personal / Portfolio projectApr 2026
View repositoryJump to architecture

Problem

  • APRA (Australia's prudential regulator) publishes fund-level superannuation performance data as Excel workbooks on an irregular weekly cadence. There's no API, no versioning and no built-in way to compare a fund's ranking against its peers over time. Anyone who wants that view has to download the files by hand and rebuild the comparison from scratch every time.
  • I wanted a system that treated this like production data, not a one-off analysis: pulled automatically, validated, modeled into something queryable, and put in front of a dashboard that updates itself.

Architecture

  1. Step 01

    APRA filings

    Weekly public Excel releases

  2. Step 02

    Airflow on EC2

    Scheduled extraction & landing

  3. Step 03

    S3 raw zone

    Immutable source of truth

  4. Step 04

    RDS PostgreSQL

    Idempotent upsert

  5. Step 05

    dbt marts

    4 modeled, tested marts

  6. Step 06

    FastAPI + Power BI

    NL-to-SQL and DirectQuery

A weekly Airflow DAG carries the data from APRA's raw filings to a live dashboard with no manual steps in between.

  • A weekly Airflow DAG running on AWS EC2 downloads the latest APRA filings and lands the raw Excel files in an S3 bucket untouched, so there's always an unmodified copy to fall back to.
  • A load step parses the filings and upserts the cleaned records into an RDS PostgreSQL database. The upsert is idempotent, so reruns after a failure don't duplicate data or require a manual cleanup step.
  • dbt runs against that PostgreSQL data on the same schedule, building four data marts: fund performance rankings, fee-versus-return analysis, and quarterly member flow tracking.
  • A FastAPI service sits in front of the marts and exposes a natural-language-to-SQL endpoint, and Power BI connects directly via DirectQuery for three live report pages.
  • The whole Airflow stack is containerized with Docker Compose, so it starts with one command and behaves the same on any machine.

Engineering decisions

  • Idempotent upsert over append-only. APRA occasionally republishes a filing with corrections. An append-only load would have silently created duplicate or conflicting rows, so every load step upserts on a natural key instead, which also means a failed run can just be re-triggered with no cleanup.
  • dbt as the transformation layer, not stored procedures. Keeping the fund performance rankings, fee analysis and flow tracking as dbt models means every transformation has version-controlled SQL, a lineage graph, and tests attached, instead of logic buried inside an Airflow operator.
  • Schema-aware NL-to-SQL instead of a fine-tuned model. Rather than training anything, the FastAPI endpoint injects the actual dbt mart schemas into the prompt sent to Claude and GPT-4, so generated SQL stays grounded in columns that really exist instead of hallucinated ones.
  • A parallel PySpark verification path. To sanity-check the dbt fund-level calculations, the same logic was reproduced in PySpark over the S3 Parquet files and run as a separate branch of the same Airflow DAG, purely to confirm the SQL and distributed versions agree.

Implementation

  • Airflow DAG: download and land raw filings in S3, then upsert into RDS PostgreSQL, with Slack alerts wired into failure callbacks so a broken run doesn't go unnoticed.
  • Modeled 4 dbt marts: fund performance rankings using RANK() over 1, 3 and 5 year returns; fee-versus-return analysis with percentile scoring and high-fee/low-return flags; and quarterly member inflow and outflow tracking by fund.
  • Added dbt tests (not_null, unique, accepted_values, relationships) on every model, and wired dbt compile plus Python linting into GitHub Actions on every pull request, with dbt test itself gating merges to main.
  • Built the NL-to-SQL API in FastAPI: it takes a plain-English question, injects the relevant dbt mart schema into a Claude / GPT-4 prompt, gets back generated SQL, executes it, and returns live JSON results.
  • Connected Power BI to RDS through DirectQuery for three pages covering fund rankings, fee-versus-return quadrants and member flow trends, then published the report to Power BI Service.
  • Reproduced the dbt fund-level logic in PySpark over the same data in S3 Parquet form, run as a parallel Airflow branch purely for cross-verification against the SQL path.

Challenges

  • APRA's Excel format isn't perfectly consistent release to release, small header and sheet-layout drifts break a naive parser, so the extraction step needed to tolerate minor schema drift without failing the whole run.
  • Making the load step genuinely idempotent took more care than it sounds, since a fund's identifying key isn't always stable across a corrected republish, and the upsert logic had to account for that.
  • Keeping the NL-to-SQL endpoint safe: an LLM generating arbitrary SQL against a real database needs constraints, so the schema injection also scopes what tables and columns the model is allowed to reference.

Results

  • The weekly refresh runs with no manual step from filing release to dashboard update.
  • dbt tests and the GitHub Actions gate catch schema and data-quality breaks before they reach the marts Power BI reads from.
  • The PySpark verification branch confirmed the dbt SQL calculations matched under distributed processing, adding confidence in the fund ranking numbers.
  • Non-technical users can ask a plain-English question through the FastAPI endpoint and get real SQL and live results back, without writing SQL themselves.

Technology

Cloud & Infrastructure

AWS EC2AWS S3AWS RDSDockerDocker Compose

Pipelines & Transformation

Apache AirflowdbtPySparkPythonpandasboto3

AI Integration

FastAPIClaude APIOpenAI GPT-4Natural language to SQL

BI & Reporting

Power BIDAXDirectQuery

DevOps

GitHub ActionsCI/CDdbt tests

More projects

Market Data Warehouse on Snowflake

Personal / Portfolio project

Problem

Backtesting a forecasting model on financial time series is easy to get subtly wrong: without point-in-time discipline, the model ends up training on information it wouldn't have had yet.

Key result

A clustering key fix cut partitions scanned on date filters by roughly an order of magnitude, and Time Travel snapshots kept lookahead bias out of every backtest.

SnowflakeSQLPythonAWS S3Streams & TasksWindow Functions+6 more
ArchitectureGitHubProject details

NewsFlow Autonomous AI Newsletter & Video Agent

Personal project

Problem

Producing a newsletter and a companion video from breaking news end to end usually means a person doing research, writing, narration and publishing by hand, every single cycle.

Key result

The workflow runs from trigger to published newsletter and video with no manual step, and recovers from failures instead of stopping cold.

n8nLangChainLangGraphRAGMCPClaude API+5 more
ArchitectureGitHubProject details