Performance Cost Optimization & Resource Efficiency Questions

Direct answer

Put a regional collector in front of each source region that batches and compresses
before any cross-region hop, decouple the client-facing latency SLA (service level agreement: the promised response-time
commitment made to users) from the slower cross-region step by acknowledging locally first, and centralize only the reduced,
deduplicated stream, because sending raw, uncompressed telemetry across regions is what
blows both the latency budget and the cost ceiling at once.

Structured elaboration

Architecture, end to end: a regional edge collector in each source region batches,
compresses, and tags incoming telemetry, writing into a regional durable queue. A regional
stream processor reads from that queue to deduplicate, filter out low-value fields, and
aggregate where possible. Only that reduced, compressed stream crosses the region
boundary, landing at a central ingestion endpoint, which writes into tiered storage: a
short hot window for anything the latency SLA needs to be queryable against, and a cheaper
archive tier for everything older.

Latency lever: keep the collector's batch-flush interval small, on the order of one to
two seconds, and count a successful write to the local, same-region durable queue as
"ingested" for the SLA. Cross-region replication happens asynchronously after that local
acknowledgment, so it never sits on the path the SLA is measured against.

Cost lever: compress before crossing any region boundary, since structured telemetry
typically compresses five to ten times with a general-purpose algorithm; drop known-noisy
or unused fields at the regional processor before they ever leave the region, since bytes
never sent can't be billed as egress; and apply a storage lifecycle policy so only the
SLA-relevant recent window sits in a warm, queryable tier while everything older moves to
archive.

Worked example

1TB/day raw ingested across all source regions. Assume the regional filter drops 30
percent of raw fields as low-value, leaving 700GB/day, and that compression achieves
roughly a 6x ratio on the remainder.

At an illustrative $0.02/GB inter-region rate, that's 3,500 times $0.02, about
$70/month for the cross-region hop itself, small because filtering and compression did
most of the work before the boundary.

Regional compute: 3 source regions, each with a small always-on processing fleet of 4
instances at an illustrative $0.15/hour, is 12 instances times $0.15 times 730 hours,
about $1,314/month.

Central storage: a 7-day hot window on the post-filter, pre-compression volume of
700GB/day (the same post-filter figure established above, not the original 1TB/day raw
volume, since only that reduced stream is ever written centrally) is 4.9TB, at an
illustrative $0.023/GB/month, about $113/month; the remaining roughly 23 days of a 30-day
retention moved to an illustrative $0.001/GB/month archive tier is about $16/month.

Add an illustrative $300/month for the managed streaming layer itself. Total estimated
spend is roughly $70 plus $1,314 plus $113 plus $16 plus $300, about $1,813/month, well
inside the $10,000 ceiling, leaving substantial headroom for multi-zone redundancy on the
regional compute layer and a central query and monitoring layer.

Trade-offs and pitfalls

Filtering at the edge is a one-way door; a field dropped in a region cannot be recovered
later during an investigation, so involve whoever owns incident response before deciding
what counts as noise. Async cross-region replication that is only checked after an
incident risks silent lag piling up unnoticed; monitor replication lag as its own signal.
A single global compression setting ignores that different regions' traffic can compress
very differently; tune per source type if the mix is heterogeneous.

Tiers

• Hot: recently written or frequently accessed data, for example the last 7 to 30 days, kept on fast, more expensive storage for low-latency queries.
• Warm: older data still queried occasionally, roughly 30 to 180 days, on cheaper storage with slightly higher access latency.
• Cold or archive: rarely accessed data kept mainly for compliance or retention, beyond a tenant's configured window, on the cheapest storage class, with retrieval that can take minutes to hours.

Migration workflow

An automated lifecycle policy evaluates each object's age and last-access time on a schedule, for example nightly, and moves it to the next tier once it crosses that tenant's configured threshold. Migrations run as asynchronous background jobs, never inline with a user's query, and each move updates a metadata catalog so future lookups know where to find the object without guessing.

Tenant-level cost allocation

Tag every object at write time with its tenant ID, and track bytes stored per tier per tenant, not just total bytes, through the metadata catalog or the storage provider's usage reporting. Multiply bytes per tier by that tier's per-gigabyte rate to get a per-tenant storage cost breakdown, for example a tenant holding 500GB in the hot tier at an illustrative $0.023/GB/month and 2,000GB in the cold tier at $0.001/GB/month owes 500 times $0.023 plus 2,000 times $0.001, about $11.50 plus $2.00, roughly $13.50/month total; this is what lets you show a tenant, or finance, exactly what their retention choices cost, and it's the basis for any tenant-tier pricing, for example charging more for a tenant who chooses a longer hot-tier retention window.

Metadata tracking for tier placement

A lightweight index, which could be a database table rather than the storage system itself, mapping object ID to current tier, last-access time, and tenant ID. Every read path checks this index first, so a query for cold data can either transparently trigger a retrieval with an SLA-appropriate (service level agreement: the retrieval-time commitment made to that tenant) wait, or return an "in progress" response, rather than the application guessing where an object lives.

Guaranteeing performance SLAs per tenant

• Enforce a tenant's chosen retention or tier thresholds independently, since one tenant's SLA, say hot access for 90 days, shouldn't force everyone else onto the same, more expensive default.
• For premium tenants with strict low-latency guarantees even on older data, either keep their hot tier longer or add a caching layer in front of the warm tier for their traffic, funded by what they're paying for that SLA.
• Monitor retrieval latency by tier and tenant, and alert if a tenant's actual retrieval times are trending toward violating their contracted SLA, since a lifecycle policy that's too aggressive about demoting data is the most common cause.

Cost comparison dimensions

• Storage: per-gigabyte-month rate on each platform, including whether compressed or columnar storage is billed differently, and whether storage and compute are billed together or separately, since that changes how idle data costs behave.
• Compute pricing model: credit-based, per-second, or fixed-cluster pricing; model your actual query patterns against each, since a bursty, ad hoc workload costs very differently under per-second billing than under a fixed always-on cluster.
• Data egress: cost of exporting data out of the current platform during migration, and any ongoing egress if the new platform lives in a different region or provider than where data is produced or consumed.
• Concurrency limits: what happens to cost and performance under your peak concurrent-query load, whether the new platform needs a burst feature to handle it, and whether that feature's billing changes the comparison.
• Ongoing maintenance: engineering time for tuning, patching, and operating each platform, translated into a dollar figure via engineer-hours, since a platform that's cheaper on paper but needs more hands-on tuning isn't actually cheaper.

Build this as a like-for-like table against your real workload's actual data volume and query pattern, not vendor list-price examples, since list prices rarely reflect real-world discount tiers or your specific access pattern.

Migration plan

1. Pilot: move a single non-critical workload, or a read replica of production data, to the new platform first, and run representative queries against it to validate both the cost model and correctness.
2. Dual-run and monitor: for workloads being migrated, run both platforms in parallel for a defined window, comparing cost and performance side by side on real production load, not just the pilot's synthetic load.
3. Define rollback triggers up front: for example, if actual migrated-workload cost exceeds the pre-migration estimate by a set margin, or query latency and concurrency handling regress past an agreed threshold, roll that workload back to the original platform rather than pushing through on hope.
4. Progressive cutover: migrate workload by workload, not everything at once, revalidating cost and performance at each step, so a problem surfaces on a small slice of traffic rather than the whole platform.
5. Full cutover and decommission: only after migrated workloads have run clean against their rollback triggers for a defined period, decommission the old platform's capacity to actually realize the projected savings; keeping both running past that point erases the cost case for migrating at all.

Approach

Two things have to happen: first, turn the 90-day histogram into a safe utilization threshold (the highest CPU utilization we can run at without p95, the 95th-percentile, latency crossing the SLO, service-level objective, the latency target being protected), and second, use that threshold to size and choose an instance type by replaying a demand trace against each candidate's cost.

Step 1: for each candidate utilization threshold, look only at historical hours where utilization was at or below that value, and check whether p95 latency in just those hours stayed under the SLO. The highest threshold that still passes is the safe target, minus a small safety margin, since production tail spikes tend to be worse than what a sample already captured.

Step 2: reconstruct each hour's implied demand (requests per second) from its recorded utilization and a reference instance's rated capacity. For each candidate instance type, simulate autoscaling hour by hour: at the safe utilization target, how many instances of that type are needed to serve that hour's demand, and what does it cost. Summing hourly cost across the whole window gives a total cost per candidate; the cheapest feasible candidate wins.

import random
import math

# Synthetic stand-in for a 90-day histogram of (avg CPU utilization, p95 latency) per hour.
# Latency is modeled as rising sharply as utilization approaches 100%, the realistic
# queueing-delay shape that makes a utilization threshold meaningful.
random.seed(7)

def simulate_history(hours=720, base_latency_ms=25, noise=4):
    history = []
    for h in range(hours):
        daily = 50 + 35 * math.sin(2 * math.pi * (h % 24) / 24 - math.pi / 2)
        weekly = 5 * math.sin(2 * math.pi * (h % 168) / 168)
        util = max(5, min(97, daily + weekly + random.gauss(0, 6)))
        latency = max(base_latency_ms, base_latency_ms / (1 - util / 100) + random.gauss(0, noise))
        history.append((util, latency))
    return history

history = simulate_history()
SLO_MS = 100

def p95(values):
    s = sorted(values)
    return s[max(0, math.ceil(0.95 * len(s)) - 1)]

def find_safe_utilization(history, slo_ms, step=1):
    best = None
    for threshold in range(step, 100, step):
        bucket = [lat for util, lat in history if util <= threshold]
        if len(bucket) < 20:
            continue
        if p95(bucket) <= slo_ms:
            best = threshold
        else:
            break  # p95 only gets worse as the threshold rises
    return best

raw_threshold = find_safe_utilization(history, SLO_MS)
SAFETY_MARGIN_PTS = 5
target_utilization = (raw_threshold - SAFETY_MARGIN_PTS) / 100

REFERENCE_CAPACITY_RPS = 250
demand_trace = [(util / 100) * REFERENCE_CAPACITY_RPS for util, _ in history]

candidates = {
    "general_purpose": {"capacity_rps": 200, "hourly_cost": 0.10},
    "cpu_optimized":   {"capacity_rps": 320, "hourly_cost": 0.17},
    "memory_optimized":{"capacity_rps": 150, "hourly_cost": 0.20},
}

def simulate_cost(demand_trace, capacity_rps, hourly_cost, target_util):
    total_cost, total_hours, peak = 0.0, 0, 0
    for demand in demand_trace:
        needed = max(1, math.ceil(demand / (capacity_rps * target_util)))
        total_cost += needed * hourly_cost
        total_hours += needed
        peak = max(peak, needed)
    return total_cost, total_hours, peak

results = {name: simulate_cost(demand_trace, cfg["capacity_rps"], cfg["hourly_cost"], target_utilization)
           for name, cfg in candidates.items()}
best_name = min(results, key=lambda n: results[n][0])

peak_demand = max(demand_trace)
naive_cfg = candidates[best_name]
naive_count = math.ceil(peak_demand / (naive_cfg["capacity_rps"] * target_utilization))
naive_cost = naive_count * naive_cfg["hourly_cost"] * len(demand_trace)

print(f"Sample window: {len(history)} hours")
print(f"Safe utilization threshold from histogram (raw): {raw_threshold}%")
print(f"Autoscaling target utilization (with {SAFETY_MARGIN_PTS}pt safety margin): {target_utilization*100:.0f}%")
print()
print("Per-candidate result over the sample window:")
for name, (cost, hours, peak) in results.items():
    print(f"  {name:17s} total_cost=${cost:8.2f}  instance_hours={hours:5d}  peak_instances={peak}")
print()
print(f"Chosen instance type: {best_name} (lowest total cost)")
print(f"Autoscaled total cost: ${results[best_name][0]:.2f}")
print(f"Naive fixed-for-peak baseline cost (same type): ${naive_cost:.2f}")
print(f"Savings from autoscaling vs fixed-for-peak: {100*(1 - results[best_name][0]/naive_cost):.1f}%")

Output:

Sample window: 720 hours
Safe utilization threshold from histogram (raw): 77%
Autoscaling target utilization (with 5pt safety margin): 72%

Per-candidate result over the sample window:
  general_purpose   total_cost=$  102.90  instance_hours= 1029  peak_instances=2
  cpu_optimized     total_cost=$  125.29  instance_hours=  737  peak_instances=2
  memory_optimized  total_cost=$  236.60  instance_hours= 1183  peak_instances=3

Chosen instance type: general_purpose (lowest total cost)
Autoscaled total cost: $102.90
Naive fixed-for-peak baseline cost (same type): $144.00
Savings from autoscaling vs fixed-for-peak: 28.5%

Key points

• The threshold search only trusts a candidate threshold if it has at least 20 historical samples backing it, otherwise a lucky handful of low-utilization, low-latency hours could produce a falsely high, unsafe threshold.
• The safety margin (5 percentage points here) exists because a 90-day sample never contains every possible traffic pattern; production will eventually see a worse tail than the sample did.
• Sizing is done hour by hour against a demand trace, not against a single peak number, which is what actually lets autoscaling save money instead of just picking the cheapest instance type and provisioning it for the worst hour all month.

Complexity

Finding the threshold is roughly O(H log H) for sorting each candidate bucket across about 100 threshold values (H being the number of historical hours), and simulating cost for each of the K candidate instance types is O(H times K). For a real 90-day, hourly dataset that is about 2,160 hours, this all runs in well under a second.

Edge cases

• If no threshold keeps p95 latency under the SLO even at very low utilization, the SLO itself may be unreachable with the current instance types, which should surface as an explicit error rather than silently picking the least-bad option.
• A candidate whose single-instance capacity is smaller than the busiest hour's demand at the target utilization is still sized correctly here because the count is recomputed per hour, but it is worth flagging separately if a candidate would need an unreasonably large fleet at peak, since that often signals a bad hardware fit rather than a scaling problem.

The primary cost drivers, and why the billing model matters. These three warehouses don't all charge for the same thing, so "cost driver" means something slightly different in each. BigQuery's on-demand pricing bills by bytes scanned per query, no matter how long the query takes to run. Snowflake and Redshift (including Redshift Serverless) instead bill primarily by compute time, credits or capacity-seconds, for however long a virtual warehouse or cluster is actively running, largely independent of how many bytes any individual query happened to touch. That distinction changes what "wasteful" looks like: in a bytes-scanned model, a sloppy SELECT * with no filter is what blows up cost per query; in a compute-time model, an oversized or an idle-but-still-running warehouse is what blows up cost even if every query running on it is efficient.

Beyond that split, three drivers apply broadly across all three systems:

• Data volume actually processed per query. Whether billed as bytes scanned or as the time it takes compute to churn through that data, the amount of data a query has to read is the dominant lever in every one of these systems, which is why partitioning, clustering, and column selection matter everywhere, not just on the bytes-scanned platforms.
• Storage of raw and derived data, usually a much smaller line than compute but not free: this includes not just the base tables but Snowflake's time-travel (the ability to query a table's past state) and fail-safe (an additional recovery-only retention window after time-travel expires) retention windows, or any materialized/cached result sets kept for query acceleration.
• Concurrency and scaling behavior. Multi-cluster or auto-scaling warehouse features that spin up additional compute to handle concurrent query load are convenient but directly translate a spike in dashboard or ad hoc query traffic into a spike in the bill, and idle auto-suspend/resume settings that are too conservative leave a warehouse running (and billing) long after the last query finished.

Two examples where a small change causes a large cost increase

1. Adding one new wide column to a heavily-dashboarded table. If several dashboards or scheduled queries do SELECT * against a fact table, adding a single new large text, JSON, or blob column means every one of those unmodified downstream queries now reads that column's bytes too, on every run, even though nothing about what the dashboards actually display has changed. In a bytes-scanned billing model this shows up directly as a jump in scanned bytes per query, multiplied across every query that touches the table.

2. An upstream schema or join change that turns a one-to-one relationship into a fan-out. If a dimension table that used to have exactly one row per key starts having duplicates (for example, an upstream change starts appending a new row per update instead of overwriting in place), a downstream query joining against it, with its SQL text completely unchanged, silently starts processing and shuffling many more rows than before. In a compute-time billing model this directly extends how long the warehouse has to stay running to finish the same query; in a bytes-scanned model it inflates the intermediate data the query has to process. Either way, nobody touched the query, and the cost increase can be large and easy to miss until someone notices the warehouse bill.