Project overview
Terpli needed to evolve its legacy Redshift analytics pipeline into a BigQuery architecture without losing continuity with historical data. We built a Node.js executor for GCP that discovers, orders, and executes versioned SQL scripts by stage, records every statement status in BigQuery, sends Slack notifications, and emits structured logs to Cloud Logging. The pipeline runs as a Cloud Run Job triggered daily by Cloud Scheduler, with its image in Artifact Registry, secrets in Secret Manager, and infrastructure managed with Terraform.
Challenge
Keep Terpli's old Redshift-replicated layer running alongside the new BigQuery layer while preserving backups, statement-level traceability, partial restart controls, and a final layer consumable by dashboards, APIs, and data science.
Solution
Implemented a GCP orchestrator with BACKUP, NEW_TERPLI, and MERGED stages: EXPORT DATA snapshots to Cloud Storage, SQL transformation for the new Terpli layer in BigQuery, and merged_* views that union old Terpli and new Terpli with explicit origin metadata for analytics consumption.
Tech Stack
- Node.js
- BigQuery
- Cloud Run Jobs
- Terraform
- GitHub Actions
Technical scope
- Node.js orchestration for ordered SQL script stages
- Cloud Run Jobs with Cloud Scheduler daily execution
- EXPORT DATA backups to Cloud Storage
- Structured logs, statement status, and Slack alerts