Alibaba MaxCompute, in depth: partitions, lifecycle, Tunnel loading and the scanned-bytes cost model, with a worked daily pipeline

What MaxCompute is, and what it is not

Think of MaxCompute as three things behind one endpoint. First, storage: column-oriented tables grouped into projects, with standard, infrequent-access and archive storage classes. Second, a set of compute engines that read and write those tables: SQL, MapReduce, Spark, and MaxFrame, a distributed Python dataframe framework. Third, a transfer service, Tunnel, for bulk upload and download.

It is built for throughput, not latency. A typical SQL job reads gigabytes to terabytes, takes seconds to minutes, and writes a whole partition. It is not a transactional database, and you should not point an application's request path at it. For interactive queries Alibaba offers an acceleration mode, MaxQA (the docs describe it as MCQA 2.0), which aims to return results in seconds. Delta tables add primary-key upserts and incremental reads for teams that need change data rather than full rewrites.

The project is the unit of ownership: tables, resources, access policies and properties. Use one per environment, for example sales_dev and sales_prod, so a development job cannot overwrite production data.

Architecture of a daily pipeline

The layering in the diagram is a common convention, not a product feature. Raw operational data lands in an ods_ layer exactly as extracted. A cleaning job writes dwd_ tables with fixed types and deduplicated keys. Dimension tables (dim_) hold small reference data. Aggregates for dashboards go to ads_ tables. Each layer is partitioned by business day, and each daily job reads one day of its input and overwrites one day of its output.

That last property is the important one. A job that only ever writes partition pt=20261003 with INSERT OVERWRITE is idempotent: rerun it after a failure or a bug fix and the partition is replaced, not duplicated. Every other operational rule in this article follows from keeping jobs shaped like that.

Tables, partitions and lifecycle

Here is the DDL for the raw and cleaned order tables. The partition column is a string holding the business date, which keeps partition names readable and sortable.

CREATE TABLE IF NOT EXISTS ods_orders (
    order_id     STRING,
    customer_id  STRING,
    amount_cents BIGINT,
    currency     STRING,
    status       STRING,
    created_at   DATETIME,
    updated_at   DATETIME,
    raw_json     STRING
)
PARTITIONED BY (pt STRING)
LIFECYCLE 400;

CREATE TABLE IF NOT EXISTS dwd_orders (
    order_id     STRING,
    customer_id  STRING,
    amount_cents BIGINT,
    currency     STRING,
    status       STRING,
    created_at   DATETIME
)
PARTITIONED BY (pt STRING)
LIFECYCLE 800;

Two limits shape this design. A partitioned table can hold at most 60,000 partitions, and the documentation calls that a hard limit that cannot be raised. Daily partitions use 365 a year, so they are safe for a long time. Hourly partitions use 8,760 a year and reach the cap in under seven years. A second-level partition such as pt=day, region=xx with 50 regions uses 18,250 a year and reaches the cap in just over three years. Count before you add a partition level.

The second is how LIFECYCLE works. The value is in days and can be set only on the table, not per partition. For a partitioned table, each partition is reclaimed when it has not been modified for that many days, measured from its LastModifiedTime, not from the date in its name. That has a consequence people rarely expect. If you backfill last year's partitions today, they live for another 400 days from today, and your storage bill and any retention policy you promised are both off by a year. Lifecycle is a storage-reclaim tool, not a compliance control. If you must delete data by business date, run an explicit ALTER TABLE ... DROP PARTITION job.

Projects can enforce some hygiene for you. The project property odps.table.lifecycle can be set to require a LIFECYCLE clause on every new table, and odps.sql.allow.fullscan controls whether a query may scan a partitioned table without a partition filter. Both are set with SETPROJECT key=value;. Turning full scans off in production is the single cheapest protection against the cost failure described below.

Loading data through Tunnel

Data arrives through Tunnel, either directly through the SDKs or through tools built on it. In Python, PyODPS wraps Tunnel in a table writer. The sketch below loads one day of extracted orders into the raw table, creating the partition if needed.

import os
from odps import ODPS

o = ODPS(
    os.environ["ALIBABA_CLOUD_ACCESS_KEY_ID"],
    os.environ["ALIBABA_CLOUD_ACCESS_KEY_SECRET"],
    "sales_prod",
    os.environ["MAXCOMPUTE_ENDPOINT"],
)

def load_day(day, rows):
    t = o.get_table("ods_orders")
    part = f"pt={day}"
    # Make the load idempotent: drop what a failed earlier attempt left behind.
    o.execute_sql(f"ALTER TABLE ods_orders DROP IF EXISTS PARTITION (pt='{day}')")
    with t.open_writer(partition=part, create_partition=True) as writer:
        writer.write(rows)          # rows: lists in column order, without pt

Tunnel has documented quotas, and a loader that ignores them fails in confusing ways under load. These are the values the Tunnel FAQ listed on the check date:

So: write few, large blocks; one session per partition per load; one committer per table; and retries that start a fresh session. For near-real-time feeds, batch by minute or hour upstream; one commit per event will hit the limits.

How a SQL job is priced

Under pay-as-you-go, a standard SQL job is priced as:

fee = input data scanned (GB) x SQL complexity x unit price per GB

Input data is what the job actually reads after partition filtering and column pruning, which is why both matter so much. Complexity is a step function of a keyword count. The documentation defines the count as the number of JOINs, GROUP BYs, ORDER BYs, DISTINCTs and window functions, plus a term for INSERT, UPDATE and DELETE statements that is never less than one, and maps it to a factor:

The documented standard SQL unit price in the default public-cloud regions was USD 0.0438 per GB on the check date. The pricing page's own example is a 40 GB full scan at complexity 1 costing USD 1.75, while selecting only three columns from the same table cost USD 0.26. MapReduce and Spark jobs are billed by compute hours instead of scanned bytes, and reserved capacity is billed per compute unit (CU) per hour, so a team with steady heavy load compares its monthly scanned-GB bill with the cost of reserved CUs.

You do not have to guess. COST SQL <statement>; estimates a statement's input size and complexity without running it. Put that in code review for any new scheduled job, and in a pre-flight check for ad hoc queries over large tables.

The daily job, made idempotent

The daily cleaning job reads one raw partition, deduplicates by order ID keeping the latest status, and overwrites one cleaned partition. The scheduler passes the business date in.

INSERT OVERWRITE TABLE dwd_orders PARTITION (pt = '${day}')
SELECT order_id, customer_id, amount_cents, currency, status, created_at
FROM (
    SELECT order_id, customer_id, amount_cents, currency, status, created_at,
           ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
    FROM ods_orders
    WHERE pt = '${day}'
      AND order_id IS NOT NULL
) t
WHERE rn = 1;

From Python, the same job runs with execute_sql, which blocks until the instance finishes, or run_sql plus wait_for_success() if you want to submit several jobs and wait on them together. Both accept a hints dictionary for per-job settings. A small orchestration wrapper adds the checks a scheduler does not do for you:

def run_day(day, sql_template):
    sql = sql_template.replace("${day}", day)
    inst = o.run_sql(sql)
    inst.wait_for_success()                      # raises if the job failed
    with o.execute_sql(
        f"SELECT COUNT(*) AS n FROM dwd_orders WHERE pt = '{day}'"
    ).open_reader() as reader:
        n = next(iter(reader))[0]
    if n == 0:
        raise RuntimeError(f"dwd_orders pt={day} is empty; refusing to continue")
    return n

The empty-partition check matters because an overwrite with an empty source succeeds and silently replaces good data with nothing. Better still, compare row counts with a trailing average, and make each layer depend on the previous layer's partition rather than on wall-clock time.

Worked example: one filter, 330 times the cost

Take the dashboard job that builds ads_daily_kpi for one day. It joins one day of dwd_orders to dim_customer, groups by region and orders the result: one JOIN, one GROUP BY and one ORDER BY, plus the INSERT term of one, for a count of 4 and a complexity of 1.5. Suppose a day of the needed columns is 11 GB and the dimension table is 1 GB, so the job reads 12 GB.

Per run: 12 x 1.5 x 0.0438 = USD 0.79. Per year of daily runs: about USD 288.

Now someone edits the job and moves the date filter into the join condition of an outer join, where it no longer prunes partitions, so the job scans the whole year of dwd_orders. The read becomes roughly 365 x 11 + 1 = 4,016 GB, and the cost per run becomes 4,016 x 1.5 x 0.0438 = USD 263.85. That is the same job, about 330 times more expensive, every day, and the output is still correct, so nobody notices until the bill arrives. Three defences catch it: odps.sql.allow.fullscan=false rejects the query outright, COST SQL in review shows the jump, and a daily cost report per job flags any job whose cost changes by more than a set factor.

Failure modes

• Missing partition filter. Cost scales with table age. Disable full scans in production and review COST SQL output.
• Function on the partition column. A filter like WHERE to_date(pt) = ... may not prune. Compare the partition column to literals or parameters directly, and confirm with the estimate.
• Empty overwrite. An upstream failure produces an empty source and the overwrite wipes a good partition. Gate on row counts before and after.
• Lifecycle surprises. Backfilled partitions restart their clock; partitions you never rewrite expire even if you still need them. Set lifecycle per table from a real retention decision and keep explicit drop jobs for compliance.
• Partition explosion. Hourly or multi-level partitions approach the 60,000 cap. Count the partitions a design produces per year before shipping it.
• Tunnel quota errors. Many small loaders hit session-creation and commit-rate limits. Batch per partition and serialise commits per table.
• Skewed joins. One hot or null key leaves a single worker running for an hour. Filter nulls and split heavy keys out.
• Using it as a serving store. Application reads against batch tables are slow and are billed per scan. Export aggregates to a store built for point reads.

Trade-offs and related reading

Choose MaxCompute for large scheduled batch transformation on Alibaba Cloud with no cluster to manage. Pay-as-you-go suits spiky or modest workloads, but bills every careless query; reserved CUs suit steady heavy load you can keep busy.

It is a weaker fit for low-latency serving, for row-at-a-time updates outside Delta tables, and for teams who need one SQL dialect across clouds. If you know BigQuery, the cost model will feel familiar: both bill on bytes scanned and both reward partitioning; see BigQuery partitioning and clustering for the comparison, and columnar storage for why column pruning saves so much.

For the surrounding Alibaba Cloud services, Alibaba OSS covers the object storage most raw exports land in, and Alibaba Tablestore covers a store suited to the point reads MaxCompute should not serve.

What to do next

1. List your tables and count partitions per table; flag any design that will pass 60,000 within five years.
2. Set odps.sql.allow.fullscan=false on production projects, and fix the jobs that start failing.
3. Require a LIFECYCLE clause on new tables, and set each value from a written retention decision.
4. Rewrite every scheduled job to read one partition and INSERT OVERWRITE one partition.
5. Run COST SQL on your ten most frequent jobs and record input GB and complexity as a baseline.
6. Add row-count checks before and after each overwrite.
7. Batch Tunnel loads per partition, use one committer per table, and start a fresh session on retry.
8. Build a daily per-job cost report and alert when any job's cost moves by more than a factor of two.