Cloud Storage Tiering for BigQuery

Storage cost optimization for a BigQuery data lake involves three interacting dimensions: tiering by access frequency, physical vs. logical billing, and pricing model selection. The right combination can reduce costs 3-5x compared to default configurations.

Storage Tiers by Access Frequency

Cloud Storage offers four tiers with decreasing cost but increasing retrieval penalties. The right tier depends on how frequently data is queried, not how important it is.

For BigLake tables backed by Cloud Storage, the tier directly affects both storage cost and query cost. A BigLake query against Coldline data pays the retrieval fee on top of BigQuery compute costs. This makes tier selection a cost optimization decision that affects your BigQuery bill, not just your Cloud Storage bill.

Mapping Tiers to Data Age

A practical tiering strategy segments data by access frequency, which usually correlates with age:

Hot data (0-90 days): Store in native BigQuery tables or BigLake Iceberg tables on Standard-class Cloud Storage. This is your active analytical window — dashboards, ad-hoc queries, and dbt transformations all hit this data regularly. The cost of Standard storage is justified by zero retrieval fees.

Warm data (90 days to 1 year): Move to BigLake tables on Nearline Cloud Storage. This data gets queried occasionally — quarterly reviews, year-over-year comparisons, investigation into specific incidents. The 50% storage savings outweigh the small retrieval fee for infrequent queries.

Cold data (1+ years): BigLake tables on Coldline, queried only when needed. Historical analysis, regulatory audits, and compliance queries hit this tier. At $0.004/GB/month, storing a terabyte costs $4/month — practically free compared to keeping it in Standard storage at $20/month.

Archive (compliance-only): Data you’re legally required to keep but never expect to query. Archive storage at $0.0012/GB/month with a $0.05/GB retrieval fee. Only use this for true cold storage where the 365-day minimum retention and high retrieval cost are acceptable.

Lifecycle Policies

Cloud Storage lifecycle policies automate transitions between tiers. You define rules, and Google moves objects automatically. This eliminates the need for manual data management or scheduled migration scripts.

{
  "lifecycle": {
    "rule": [
      {
        "action": { "type": "SetStorageClass", "storageClass": "NEARLINE" },
        "condition": { "age": 30, "matchesStorageClass": ["STANDARD"] }
      },
      {
        "action": { "type": "SetStorageClass", "storageClass": "COLDLINE" },
        "condition": { "age": 90, "matchesStorageClass": ["NEARLINE"] }
      },
      {
        "action": { "type": "Delete" },
        "condition": { "age": 365 }
      }
    ]
  }
}

This configuration moves objects from Standard to Nearline at 30 days, Nearline to Coldline at 90 days, and deletes at 365 days. Adjust the retention timeline for compliance requirements — some industries require 3, 5, or 7 years of data retention.

A critical detail: lifecycle policies operate on individual objects, not BigQuery partitions. If you’re using BigLake tables with Hive-style partitioning, each partition’s data files are separate objects in Cloud Storage. This means lifecycle policies naturally tier data by age when your partitions are date-based — older partition files age out and get moved to cheaper tiers automatically.

Autoclass for Iceberg Tables

For BigLake Iceberg tables, enable Autoclass on the underlying bucket instead of writing manual lifecycle rules. Autoclass automatically moves data and metadata files to optimal storage classes based on actual access patterns, not just age.

# Enable Autoclass on a bucket
gcloud storage buckets update gs://data-lake-bronze \
    --enable-autoclass \
    --autoclass-terminal-storage-class=ARCHIVE

Autoclass is more intelligent than static lifecycle rules because it responds to real access patterns:

• A 6-month-old partition that suddenly gets queried (because an analyst is investigating a historical issue) gets moved back to a hotter tier automatically
• Metadata files that Iceberg accesses frequently stay in Standard storage even if the data files move to Coldline
• The --autoclass-terminal-storage-class flag controls how cold objects can get (default is Nearline; set to Archive for maximum savings on truly dormant data)

The trade-off: Autoclass charges a small management fee ($0.0025/1000 objects/month) and can’t be combined with manual lifecycle rules on the same bucket. For most data lake buckets with varied access patterns, Autoclass is simpler and cheaper than trying to predict access frequency with static rules.

Physical Billing in BigQuery

For data stored directly in BigQuery (native tables, not BigLake), switching from logical to physical billing often reduces storage costs significantly.

Logical billing (default): You pay for the uncompressed logical size of your data. $0.02/GB/month for active data, $0.01/GB/month for long-term (untouched for 90+ days).

Physical billing: You pay for the compressed physical bytes on disk. $0.04/GB/month for active, $0.02/GB/month for long-term. Time travel and fail-safe storage are billed separately.

The math depends on your compression ratio. BigQuery’s columnar compression typically achieves 3-4x reduction on analytical data. A table that’s 100 GB logically might be 25 GB physically. At logical billing, that’s $2/month. At physical billing, $1/month. The 50% savings compounds across your entire warehouse.

-- Check compression ratios before switching
SELECT
  table_schema,
  table_name,
  ROUND(total_logical_bytes / POW(1024, 3), 2) AS logical_gb,
  ROUND(total_physical_bytes / POW(1024, 3), 2) AS physical_gb,
  ROUND(total_logical_bytes / NULLIF(total_physical_bytes, 0), 1)
    AS compression_ratio
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE total_logical_bytes > 0
ORDER BY total_logical_bytes DESC
LIMIT 20;

If your compression ratio exceeds 2:1, physical billing saves money. Since typical data compresses 6-17x, most organizations benefit from switching. Physical billing is set at the dataset level, not per-table.

Combined with long-term storage pricing (50% reduction for data untouched for 90 days), mature datasets in BigQuery can cost around $0.005/GB/month. That’s cheaper than most object storage when you account for the query capabilities included.

The Three Dimensions Together

Storage cost optimization works across three interacting dimensions:

1. Storage tiering: Segment data by access frequency. Hot data in BigQuery or Standard Cloud Storage; cold data in Nearline/Coldline.

2. Physical billing: Switch BigQuery datasets to physical billing once you’ve verified compression ratios exceed 2:1.

3. Pricing model: Pipeline projects with predictable query patterns benefit from Editions pricing with autoscaler slots. Analytics projects with unpredictable ad-hoc queries work better with on-demand pricing and daily cost quotas.

Don’t apply the same pricing model to both use cases. A production dbt pipeline that runs the same transformations daily has a predictable slot profile. An analytics team exploring data ad-hoc has a bursty, unpredictable profile. Separate them into different BigQuery projects with different pricing configurations, and optimize each independently.

The combination of Cloud Storage tiering for the data lake, physical billing for native BigQuery storage, and appropriate compute pricing typically reduces total storage costs by 60-80% compared to keeping everything in default configurations.