CryptoPulse-DWH: Real-Time Volatility Analytics

A serverless Data Warehouse solution designed to ingest high-frequency cryptocurrency exchange rates and calculate statistical volatility metrics (OHLC, Standard Deviation) in real-time.

How it Works

1. Data Ingestion (The Heartbeat)

The system is powered by an Azure Function App with a Timer Trigger.

• Interval: Every 5 seconds.
• Extraction: Connects to the Binance Spot API to fetch real-time bidPrice and askPrice.
• Transformation: Calculates spreads and mid-rates, then maps them to a consistent dim_time hierarchy.
• Loading: Streams data into fact_exchange_rates using an UPSERT logic to handle concurrency.

2. The Volatility Engine

At the turn of every hour, the system triggers a Batch Calculation:

• SQL Logic: Utilizes T-SQL CTEs to aggregate the previous hour's snapshots.
• Metrics: Generates Open, High, Low, Close (OHLC), Standard Deviation, and Hourly Change %.
• Materialized Views: Hourly data is further aggregated into Daily, Weekly, and Monthly Materialized Views to optimize dashboard performance.

3. Visualization (Grafana Dashboard)

Data is visualized via Grafana, connected directly to the Azure SQL instance.

• Real-time Panels: Live spread tracking and price action.
• Historical Panels: Trend analysis from Materialized Views (reduces query time by 90%).

Installation & Setup

Prerequisites

• Azure Account (SQL Database & Function App)
• Binance API Keys
• Grafana Instance

Deployment

1. Database: Execute the scripts in /sql/schema.sql to build the Star Schema.
2. Environment: Create a .env file based on .env.example.
3. Azure Function: Deploy the /azure_function folder using VS Code or Azure CLI.

Running the Local Dashboard Bridge

If using the local tool (pdc.exe) to stream data or manage the dashboard:

1. Ensure your authentication token is active.
2. Run via CMD: pdc.exe --token <YOUR_OWN_TOKEN>

Schema Design

The project utilizes a Star Schema to enable high-performance OLAP queries, focusing on financial fact tables linked to time and currency dimensions.