How m03jam14/insightengine works
Project Type and Purpose
• This is a data‐driven analytics dashboard built on Tableau, focused on visualizing Canadian variable mortgage rates (2015–2024).
• It combines ETL pipelines, a data warehouse, statistical analysis modules, and an interactive Tableau front end for financial and policy stakeholders.
Main Components
a. External Data Sources
• Bank of Canada rate publications (API or flat files)
• Housing affordability indices, economic indicators, quarterly financial data
b. Data Processing & Engineering
• Python ETL scripts (ingest, clean, normalize)
• SQL jobs for staging, transformation, and loading into the warehouse
• Automated scheduler (cron, Airflow, or similar)
c. Data Warehouse
• Staging schema for raw data
• Fact tables for processed rate series and calculated indices
• Dimension tables (dates, rate environments, geographies)
d. Advanced Analytics Layer
• Statistical modules (volatility calculations, time‐series decomposition, correlation analysis)
• Stored procedures or Python/R scripts invoked by SQL or scheduler
e. Presentation Layer
• Tableau Desktop workbooks (.twbx)
• Tableau Server for publication, user access, and scheduled data refresh
• Interactive front end: dashboards, parameter controls, filters
f. Users & External Interfaces
• Financial institutions, policy analysts accessing via web browsers
• API or export endpoints for downstream reporting tools
Relationships and Data Flow
1. External Data Sources → Python ETL (extract, validate)
2. Python ETL → Staging Schema in Data Warehouse
3. SQL Transformations & Analytics Modules → Fact/Dimension Tables
4. Data Warehouse → Tableau Server Data Extracts (scheduled refresh)
5. Tableau Server → End‐user Dashboards (interactive analysis)
Architectural Patterns & Design Principles
• Modular ETL (separation of extraction, transformation, loading)
• ELT vs. in‐database analytics for performance (push analytics into SQL where possible)
• Layered architecture: raw → curated → presentation
• Scalable scheduling: handle daily or real‐time updates
• Responsive & caching strategies on Tableau Server for sub‐2‐second load times
Technologies & Frameworks
• Python (ETL orchestration, validation scripts)
• SQL (data modeling, analytic views, stored procedures)
• Data warehouse (PostgreSQL, Snowflake, or equivalent)
• Tableau Desktop / Server (2021.1+)
• Scheduler (Airflow, cron jobs, or Tableau scheduling)
• Version control (Git for code, workbook versioning)