A large-scale transformation project replacing manual, Excel-based reporting with a fully automated, cloud-native analytics platform on Google Cloud Platform (GCP). The platform centralizes data from multiple enterprise systems and delivers scalable analytics through Power BI dashboards.
The platform follows the Medallion Architecture — organizing data into progressively refined layers that improve quality, governance, and performance for reporting workloads.
| Layer | Purpose |
|---|---|
| Bronze | Raw ingested data from source systems |
| Silver | Cleaned, standardized, and deduplicated data |
| Gold | Business-ready datasets optimized for analytics |
| Source | Description | Ingestion Method |
|---|---|---|
| Salesforce | CRM and sales pipeline data | Fivetran |
| Oracle HCM | Human Capital Management system | Fivetran |
| Oracle PPM | Project Portfolio Management data | Airflow |
| Static CSV Files | Manual operational datasets | Airflow |
Fivetran automates ingestion of structured data from enterprise SaaS systems — handling Salesforce and Oracle HCM with managed connectors, incremental loading, automated schema detection, and fault-tolerant replication into BigQuery.
Custom ingestion logic for Oracle PPM and Static CSV datasets is orchestrated via Apache Airflow on Google Cloud Composer, with scheduled workflows, incremental loads, and built-in pipeline monitoring and retries.
The Bronze layer stores raw data directly from source systems with minimal transformation. Schema is aligned to source systems, historical data is preserved with ingestion timestamps, and the layer follows an append-only design — serving as the single source of truth for raw enterprise data.
The Silver layer applies data cleansing, standardization, deduplication, schema normalization, and joins across related datasets — implemented via BigQuery SQL and scheduled transformation jobs.
The Gold layer provides aggregated metrics and curated business entities with optimized query performance. These datasets form the semantic layer consumed directly by Power BI dashboards.
Business insights are delivered via Power BI dashboards querying Gold layer datasets. A single dashboard can integrate data from multiple Gold layer sources.
| Dashboard | Purpose |
|---|---|
| Bookings | Sales and revenue booking insights |
| SOP | Sales operating performance |
| Infographics | Executive business summaries |
| Utilization | Workforce and project utilization |
Utilized Fivetran to ingest raw data from Oracle Fusion, Oracle Fusion PPM, Salesforce, SAP, and Static CSV files. Built and maintained connectors and ingestion pipelines automating data replication into the analytics platform.
Created and maintained Airflow DAGs using Google Cloud Composer for Oracle PPM and Static CSV sources. Implemented incremental loading strategies and scheduled pipelines for efficient processing.
Developed transformation logic using BigQuery SQL across Silver and Gold layers. Converted legacy Oracle SQL reports into BigQuery-compatible syntax for cloud-native compatibility.
Designed and optimized BigQuery views for efficient Power BI consumption. Migrated existing dashboards from Excel-based sources to BigQuery Gold layer datasets — improving performance, scalability, and data reliability.
Implemented query optimization techniques including partitioning to improve performance and reduce data processing costs.
| Benefit | Detail |
|---|---|
| Automation | Eliminated data silos by centralizing 5+ enterprise sources into a single source of truth |
| Scalability | Cloud-native architecture supports enterprise data growth |
| Governance | Secure access control and data masking across all sensitive fields |
| Performance | Optimized BigQuery datasets enable fast dashboard queries |
| Business Insights | Unified dashboards provide real-time decision support |
Let’s build a data platform that scales with your business.
Get in Touch