Migrating GA4 Data from BigQuery to S3/Redshift
Summary
A company successfully migrated its Google Analytics 4 (GA4) user behavior data from BigQuery to a hybrid AWS S3/Redshift architecture, reducing annual costs from ₩50M (~\$36,000) to approximately ₩3.4M (~\$2,555). Initially, a direct AWS Glue pull from BigQuery incurred ₩16M (~\$11,500) in just four days, primarily due to NAT Gateway, cross-AZ bandwidth, GCP egress, and Glue DPU charges. The optimized "TO-BE" architecture shifts to a GCP-side "push" model, utilizing BigQuery Scheduled Queries for transformation and Cloud Run with rclone for GCS to S3 transfer. This design eliminated expensive AWS Glue Spark and NAT Gateway costs, achieving a daily operating cost of ₩9,500 (~\$7). The solution also implements hot/cold data tiering, keeping two years of data in Redshift and older data in S3, and includes an idempotent UPSERT loading process and a daily retention pipeline. Query performance was improved by designating `parsed_event_date` as a Redshift Sort Key.
Key takeaway
For Data Engineers or MLOps Engineers managing cross-cloud data pipelines, if you are migrating large GA4 datasets from GCP BigQuery to AWS Redshift/S3, you should prioritize a "push" architecture from GCP. This approach significantly reduces egress and NAT Gateway costs, preventing unexpected bills like the ₩16M (~\$11,500) incurred by a "pull" strategy. Implement BigQuery transformations and use serverless tools like Cloud Run with `rclone` for efficient transfers.
Key insights
Migrating GA4 data from BigQuery to AWS requires a "push" architecture to avoid high cross-cloud egress and NAT Gateway costs.
Principles
- Transform data where it originates to minimize transfer volume.
- Prefer "push" over "pull" for cross-cloud data transfers to control egress costs.
- Implement hybrid storage (hot/cold) to balance performance and cost for large datasets.
Method
The proposed method involves BigQuery Scheduled Queries for transformation and GCS export, followed by a Cloud Run rclone job to push data to S3, then idempotent UPSERT into Redshift, and a daily retention pipeline for hot/cold tiering.
In practice
- Use BigQuery Scheduled Queries for pre-processing GA4 data before export.
- Employ `rclone` via Cloud Run for cost-effective GCS to S3 data transfers.
- Design an idempotent UPSERT for Redshift loads to handle re-runs and data integrity.
Topics
- GA4 Data Migration
- BigQuery
- AWS Redshift
- AWS S3
- Cloud Data Cost Optimization
- Data Pipeline Architecture
- rclone
Best for: Data Engineer, MLOps Engineer, AI Architect
Related on AIssential
Editorial summary, takeaway, and curation by AIssential. Original article published by Data Engineering on Medium.