Cloud Data Warehouse · dbt · NL-to-SQL

Financial Intelligence
Platform

Enterprise financial data governance platform built on Snowflake and dbt. Real OHLCV market data, 500k simulated banking transactions, and 200k consumer loan records are loaded into Snowflake and transformed through versioned dbt models into analytics-ready tables. A LangChain NL-to-SQL agent powered by Llama converts plain-English business questions into validated SQL, with schema-aware prompts, sqlglot safety checks, and a 3-attempt retry loop. Automated data quality checks and append-only lineage tracking run after every pipeline execution.

Try the Live Demo Schema Diagram

●5 Snowflake tables, 700k+ rows ●dbt models with full test coverage ●NL-to-SQL with Llama via Ollama ●Automated DQ checks after every ingest ●Append-only lineage tracking

Python Snowflake dbt FastAPI LangChain Ollama Llama sqlglot pandas

Try It

Checking...

Ask the NL-SQL Agent

Ask any business question in plain English. A local Llama model generates the SQL, runs it against a DuckDB & Snowflake warehouse of 700k+ financial records, and returns real results in seconds. This runs live on my machine via a Cloudflare Tunnel.

Ask a business question

Try an example

Which merchant categories have the highest confirmed fraud rate this year?

Fraud type breakdown by transaction channel with confirmation rate and average confidence score

Top 10 accounts by total value of confirmed fraud in the last 90 days

High-confidence fraud events above 0.85 showing merchant category, channel, and fraud type

For each loan grade show default rate, average FICO score, and average interest rate ranked by default risk

Which loan purpose has the highest default rate and what is the average DTI for those borrowers?

Goldman Sachs monthly closing price and total volume trend over the last 12 months

Average daily high-low spread for JPM, GS, BAC, and C over the last 6 months

Which fraud model version has the best precision rate broken down by fraud type?

Monthly flagged transaction trend this year with average amount and total flagged value

Loan portfolio cohort by grade showing total funded amount, average DTI, and charge-off rate

Data quality pass rate trend by dataset across the last 10 validation runs

Sending to Llama model on my machine...

Generated SQL

SELECT

Visualization

CHART

Results

0 rows

Natural Language

→

Local Llama (Ollama)

→

SQL Generation

→

DuckDB & Snowflake Warehouse

→

Results

Scale

What Was Built

An enterprise-grade financial data governance platform. Raw financial data flows from source systems through a dbt transformation layer into a Snowflake cloud warehouse, with a natural language query interface powered by a local LLM agent.

500k

Simulated retail banking transactions, 9 merchant categories, 3-year window, BLS weight distributions

200k

Simulated consumer loan records, grade A to G, modeled on LendingClub distribution patterns, 7-year history

~26k

Real OHLCV market data rows from yfinance, 5 years of daily prices across US financial sector tickers

~850

Fraud detection events derived from flagged transactions. 4 fraud types with confidence scores

Cloud Warehouse Architecture

The platform follows an ELT pattern: raw financial data lands in Snowflake staging tables, then dbt models apply business logic, grain definitions, and data quality assertions to produce analytics-ready output. All transformation logic is version-controlled and replayable.

NL-to-SQL Agent

A LangChain agent converts plain-English questions into validated SQL using a schema-aware system prompt, few-shot examples, and a 3-attempt retry loop. sqlglot blocks any non-SELECT statement before it touches the database.

Automated Data Quality

After every ingestion run, inline DQ validators check row counts, null rates, value distributions, and date ranges across all 4 data tables. Results are written to data_quality_log and exposed via /query/dq.

System Design

End-to-End Architecture

Three layers: a Python ingestion pipeline that loads raw financial data into Snowflake staging tables, a dbt transformation layer that builds analytics models with built-in DQ tests, and an NL-SQL agent that bridges plain-English questions to Snowflake SQL.

yfinance API

Data Simulators

→

dbt Models

→

Snowflake Warehouse

Lineage Tracker

→

NL-SQL Agent (Llama)

→

FastAPI

dbt as the transformation layer. Raw tables land via Python ingestion into Snowflake staging. dbt ref() dependencies define the transformation DAG: staging models clean and type-cast raw data, mart models apply business logic, aggregations, and fraud classification. Every model is documented, testable, and independently replayable.

Append-only lineage enforced at the application layer. After each pipeline run, a lineage entry is written for every table, recording source, row count, ingest timestamp, and DQ pass rate. The log is never overwritten, creating an audit trail across pipeline iterations.

Data Layer

Five Warehouse Tables

Transaction and loan-level records aren't available outside a production banking environment, so three of the tables are built from simulated data, calibrated against real-world statistical distributions and produced deterministically with NumPy (seed=42). Market data is fetched live from the yfinance API on each ingestion run. All four are loaded into Snowflake staging and transformed through dbt staging and mart models into the final analytics schema.

Table 01

market_data

Fetches real daily OHLCV prices for 20 US financial stocks using yf.download() with a 5-year lookback. Flattened from MultiIndex to per-row format: (symbol, trade_date, open, high, low, close, adj_close, volume). A UNIQUE(symbol, trade_date) constraint prevents duplicate rows on re-fetch. Approximately 26,000 rows total.

Table 02

transactions

500,000 simulated retail banking transactions over a 3-year window. Nine merchant categories with BLS Consumer Expenditure Survey weight distributions (Grocery 28%, Online Retail 18%, Restaurant 14%, and more). Channel split: ATM, POS, ONLINE, WIRE. 0.17% fraud flag rate (is_flagged = TRUE) seeds the fraud_events table.

Table 03

loans

200,000 simulated consumer loan records over a 7-year history. Grade A through G, each with calibrated parameters for interest rate, default probability, income range, and FICO score, modeled on LendingClub distribution patterns. Statuses: Current, Fully Paid, Charged Off, Default, Late.

Table 04

fraud_events

Derived from the ~850 flagged transactions. Each event adds: fraud_type (card_not_present, account_takeover, identity_theft, merchant_fraud), a model confidence_score (0.0 to 1.0), model_version, and is_confirmed (TRUE = confirmed, FALSE = cleared, NULL = pending review).

Table 05

data_quality_log

Written automatically after every ingestion run. Each of the 4 data tables gets a DQ check row: total_checks, passed_checks, failed_checks, success_rate. Checks cover row count ranges, null rates, value domain validation, and date range plausibility. Queryable via GET /query/dq or through the NL-SQL agent.

Lineage

lineage.json

An append-only JSON file at data/lineage.json records a lineage entry for every table after ingestion: source system, row count, ingest timestamp, and DQ pass rate. Never overwritten, creating an audit trail of every pipeline run. Exposed via GET /query/lineage/{'{'}table{'}'}.

AI Agent

Natural Language to SQL Agent

The agent converts plain-English questions into Snowflake SQL using a schema-aware system prompt with all 5 table definitions, few-shot examples for common patterns, and a retry loop that feeds SQL execution errors back to the model.

Agent Flow

# 1. Schema-aware system prompt
system = """
You are a senior data analyst.
Convert the question into a DuckDB SQL SELECT.
Output ONLY the SQL. No markdown, no preamble.
Never generate INSERT, UPDATE, DELETE, DROP...
Always LIMIT (max 500) unless single aggregate.
SCHEMA: {schema}
"""

# 2. Few-shot examples + question
msgs = [SystemMessage(system), HumanMessage(few_shot + q)]

# 3. Local Llama via Ollama OpenAI-compat endpoint
response = llm.invoke(msgs)   # localhost:11434/v1
sql = extract(response.content)  # strip ```sql fences

# 4. sqlglot safety check (blocks non-SELECT)
stmts = sqlglot.parse(sql, dialect="duckdb")
assert all(isinstance(s, Select) for s in stmts)

# 5. Execute, retry up to 3x on error
df = conn.execute(sql).df()
        

Sample Questions

Question	Tables
Which merchant categories have the highest confirmed fraud rate this year?	transactions + fraud_events
Fraud type breakdown by channel with confirmation rate and avg confidence score	transactions + fraud_events
Top 10 accounts by total confirmed fraud value in the last 90 days	transactions + fraud_events
High-confidence fraud events above 0.85 with merchant category and channel	transactions + fraud_events
Loan grade default rate, avg FICO, and avg interest rate ranked by risk	loans
Which loan purpose has the highest default rate and average DTI?	loans
Goldman Sachs monthly closing price and volume trend last 12 months	market_data
Avg daily high-low spread for JPM, GS, BAC, and C over the last 6 months	market_data
Fraud model version precision rate broken down by fraud type	fraud_events
Monthly flagged transaction trend this year with avg amount and total value	transactions

sqlglot as the safety layer. Before any SQL touches the Snowflake warehouse, sqlglot parses the statement and checks that every node is a Select expression. Any INSERT, UPDATE, DELETE, DROP, or CREATE returns an error immediately without executing, making the API safe to expose without authentication.

REST API

FastAPI Endpoints

Five endpoints exposed via FastAPI. The query endpoint runs the NL-SQL agent against the Snowflake warehouse. The others expose schema metadata, data quality results, and lineage records.

POST /query Ask a plain-English question. Returns generated SQL, rows, columns, explanation.

GET /query/schema All 5 table names with current row counts.

GET /query/dq Latest DQ check results from data_quality_log ordered by run_ts DESC.

GET /query/lineage/{'{'}table{'}'} All lineage records for a given table from lineage.json.

GET /health Liveness check. Polled by the demo page status indicator.

Engineering Notes

Key Design Decisions

Choices made during development and the reasoning behind them.

ELT over ETL with dbt

Raw financial data is loaded first, transformed second. Python ingestion writes to Snowflake staging tables without applying business logic. dbt then applies transformations, grain definitions, and aggregations as versioned SQL models. Transformation logic is auditable, testable with dbt test, and replayable independently of ingestion.

sqlglot as the SQL Safety Layer

Before any generated SQL reaches the Snowflake warehouse, sqlglot parses the statement and asserts every node is a Select expression. INSERT, UPDATE, DELETE, DROP, or CREATE are rejected before execution. This makes the /query endpoint safe to expose without authentication, even against a production-grade warehouse.

Ollama OpenAI-compat Endpoint

LangChain's ChatOpenAI is pointed at the local Ollama OpenAI-compatible endpoint with temperature=0.0. No custom Ollama integration needed. Any model registered with Ollama works without code changes: the model name is a single configurable setting.

Seed-Based Data Simulation

All data simulators use np.random.default_rng(seed=42). Every pipeline run produces identical data given the same parameters. BLS Consumer Expenditure Survey weights for transactions and LendingClub grade parameters for loans are hardcoded distributions, ensuring statistical reproducibility across analytics testing cycles.

Financial IntelligencePlatform