Bronze/Silver/Gold data architecture with 23 tables, 226 SQL queries, and 98% infrastructure cost reduction.
Recognizing that PorchPass's Excel-based infrastructure needed a scalable cloud counterpart, Nick designed and deployed a comprehensive BigQuery Medallion Architecture as the foundation for the enterprise data platform.
The architecture follows industry-standard Bronze/Silver/Gold data layers: Bronze ingests raw data from Midwest's SFTP exports, Silver applies cleansing, normalization, and business rule transformations, and Gold produces investor-ready analytical datasets. The implementation comprises 23 BigQuery tables, 15 views, 9 automated stored procedures, and 226 SQL queries.
A critical decision was the infrastructure cost analysis comparing GCP Cloud Composer ($300-500/month) versus BigQuery Scheduled Queries plus Cloud Functions ($5-10/month). By selecting the lightweight approach, Nick reduced monthly infrastructure costs by 98% while preserving full automation — saving approximately $5,880 annually.
Interactive demo embedded above. Some features may require full-screen view.