How GoTerra Cut Costs and Boost Speed: BigQuery‑to‑MaxCompute Performance Secrets

Background

GoTerra, a top Southeast Asian tech group serving billions of users, needed to migrate its data warehouse from Google BigQuery to Alibaba MaxCompute to curb soaring costs while maintaining high‑throughput transaction, risk‑control, and logistics workloads. After handling SQL syntax conversion and storage format changes, performance optimization became the critical bottleneck, with three core challenges:

Highly complex business scripts: over 10,000 production‑grade SQL statements across payment, logistics, and risk‑control domains.

Massive incremental feature rollout: more than 600 new functions (e.g., append2.0, unnest) added during migration, exponentially increasing optimization difficulty.

Extreme delivery timeline: less than four months to migrate all workloads while meeting strict SLA requirements.

Performance‑Optimization Methodology

The team abandoned a coarse‑grained “blind‑fold” approach and built a data‑driven, layered optimization framework. By classifying queries according to the 80/20 rule—80% of resource consumption stems from 20% of high‑frequency or inefficient queries—they focused resources on the most impactful bottlenecks.

High‑frequency queries : greatest optimization payoff.

Inefficient queries : contain full‑table scans, unexpected CROSS JOIN, etc.

Critical business queries : must meet SLA guarantees.

On top of this classification they established a performance baseline and continuously evaluated optimization effects by comparing pre‑ and post‑tuning metrics.

Auto Partition Optimization

Auto Partition tables emulate BigQuery’s time‑unit column partitioning but generate partition columns dynamically via the trunc_time function. While improving partition‑management flexibility, the generated partition column could not be directly used for pruning, leading to two problems:

Read performance degradation due to full‑partition scans.

Severe resource waste from redundant data scans.

To enable pruning, the team designed a dynamic partition‑pruning scheme that rewrites expressions on the original timestamp column ( ts) into equivalent expressions on the derived partition column ( pt). Three scenarios were covered:

Basic pruning : direct expression mapping without functions.

Non‑equivalent function conversion : fold timezone or date‑format functions (e.g., TODATE(FROM_UTC_TIMESTAMP(ts,'Asia/Jakarta'))) into a superset expression.

Equivalent function conversion : replace datetrunc(ts,'day') with trunc_time(ts,'day') when semantics match.

Result: MaxCompute achieved static‑partition‑level performance while retaining the flexibility of Auto Partition.

UNNEST Optimization for Complex Types

In BigQuery, UNNEST efficiently flattens ARRAY columns. After migration, the team had to emulate this using LATERAL VIEW + EXPLODE or CROSS JOIN, which caused:

Redundant execution plans with multiple table scans and joins.

Data explosion from Cartesian products.

Inability to handle deeply nested ARRAY structures.

They introduced a unified UNNEST framework based on an internal plan that supports sub‑plan APPLY operators, enabling:

Internal plan generation without affecting external plan optimizations (push‑down, column pruning).

Merging adjacent TableFunctionScan operators and applying subtree merges.

Executor coordination between outer and inner plans.

Performance gains: 1‑10× speedup and elimination of OOM errors for large nested UNNEST scenarios.

Large‑Query Graph Optimization

Super‑large queries generate operator graphs with millions of nodes, causing high memory usage and slow traversal. The team applied three techniques:

Graph optimization : pattern‑match and replace sub‑graphs with more efficient operators.

Digest reuse : cache and reuse full‑digest, type‑digest, and scalar‑function digest information, reducing hash‑and‑equality costs by orders of magnitude.

Value : reduced typical query optimization time from >15 minutes to ~1 minute and peak memory from >5 GB to ~1 GB.

Intelligent Tuning

Big‑data jobs suffer from missing statistics, opaque UDFs, and thousands‑line SQL, making cardinality estimation and stage‑resource planning difficult. Intelligent Tuning collects rich runtime statistics and feeds them back into the CBO (cost‑based optimizer) and stage‑concurrency controller, achieving:

Automatic plan selection based on historical execution patterns.

Dynamic adjustment of stage concurrency to avoid resource waste.

Automatic acceleration of under‑provisioned stages.

Result: typical jobs saved 87 % of resources and achieved ~45 % speedup.

Conclusion & Future Outlook

The migration demonstrated that MaxCompute can deliver half the compute‑unit cost of BigQuery for financial ETL workloads and cut end‑to‑end query latency by 83 % for BI scenarios. Future directions include deeper AI‑driven auto‑tuning, native geographic‑type support, heterogeneous‑compute integration, finer‑grained resource elasticity, and enhanced observability for high‑availability.