Introduction

In Part 1 of this series, we introduced our Stock Market Analytics Platform built on Google Cloud Platform (GCP). We covered the architecture, infrastructure deployment with Terraform, batch processing with Dataproc/Spark, and real-time data processing with Kafka.

In this second part, we'll explore the remaining components: data transformation with dbt, workflow orchestration with Airflow, data visualization, performance optimization, and monitoring.

Data Transformation with dbt

After collecting both historical and real-time stock data in BigQuery, we use dbt (data build tool) to transform this raw data into analytics-ready models.

dbt Project Structure

Our dbt project follows a three-layer architecture:

1. Staging Models: Initial data cleaning and standardization
   • stg_daily_prices: Cleans historical daily price data
   • stg_realtime_prices: Standardizes streaming data
   • stg_company_metadata: Company information and sector classification
2. Intermediate Models: Business logic and transformations
   • int_daily_metrics: Combines batch and streaming data
   • int_technical_indicators: Calculates technical analysis metrics
   • int_volatility_measures: Computes volatility and risk metrics
3. Mart Models: Final presentation layer for analysis
   • mart_stock_performance: Stock-level performance metrics
   • mart_sector_performance: Sector-level aggregations
   • mart_market_indicators: Market-wide indicators

Transformation Logic

Our transformations include:

1. Time-based Aggregations: Daily, weekly, monthly views of price data
2. Technical Indicators: Moving averages, RSI, MACD, Bollinger Bands
3. Statistical Analysis: Volatility, beta, correlation matrices
4. Sectoral Analysis: Industry grouping and comparative metrics

Testing and Documentation

Each dbt model includes:

• Schema tests to validate data integrity
• Data quality assertions