Part 4 · 9 chapters · ~50 min

Serving and analytics: Bigtable, BigQuery, and their twins

Part 11 established two jobs with three orders of magnitude between their latency requirements: serve one account’s history in milliseconds, and answer questions across all accounts in seconds. Both clouds have a good answer to each, and the serving layer is close to a coin flip. The warehouse is not, and this part says why without being tribal about it.

The two jobs from Part 11, restated

worked numbers

serving   "what happened to THIS account?"
             single-digit ms, 18,000/s at peak, 20M accounts

asking    "what happened across ALL accounts?"
             seconds, a few hundred queries a day, billions of rows

two jobs, two stores, and conflating them is the mistake.

what each store must do

Serving: point read and bounded range scan by account, at any scale, with predictable latency. No aggregation.
Asking: scan billions of rows over a few columns, with aggregation, without touching the money path.
Both: fed from the Part 4 event log and the CDC stream, and both are derived copies that could be rebuilt.

Bigtable, and why DynamoDB is the AWS answer

	Bigtable	DynamoDB
Model	Sorted map, row key ordered	Hash partition + sort key
Range scans	Across row keys, globally sorted	Within a partition only
Cross-partition scan	Natural	Requires a scan or a GSI
Capacity	Provisioned nodes	On-demand or provisioned
Scale to zero	No, nodes are always on	Yes, on-demand
Item size	100 MB per row	400 KB per item
Secondary indexes	None	GSIs and LSIs
Cost at our volume	Node-hour dominated	Request-and-storage dominated

the structural difference that matters

Bigtable is globally sorted; DynamoDB is sorted within a partition. For "this account, newest fifty" they are equivalent, because both scope to one key. For "every account in this shard, in time order" Bigtable does it natively and DynamoDB needs a GSI or a scan. Our access pattern is per-account, so both work, and the decision falls to cost shape and operational familiarity rather than capability.

Row keys versus partition keys: the same problem

Part 11 chapter 125 designed a Bigtable row key and chapter 126 named the hot tablet as the hot row wearing another costume. The DynamoDB version is identical.

AWS

GCP

= ACCT#a7f3e2
SK = ENTRY#<rev-ts>#<journal>

Query by PK with ScanIndexForward=false and a limit. Reverse timestamp is unnecessary because the sort direction is a query parameter.

Hot partition limit: 3,000 RCU / 1,000 WCU.

row key = acct#a7f3e2#<rev-ts>#<journal>

Prefix scan with a limit. Reverse timestamp is necessary, because sorting is ascending and lexicographic.

Hot tablet: one node’s worth of throughput.

worked numbers

the same rule, stated once:

  put the high-cardinality, evenly-distributed attribute first.

  timestamp first → every write lands in one place
  account first   → writes spread, one account stays contiguous

the fee revenue account was already split 64 ways in Part 3,
which fixes the hot key here for free. the layers compose.

BigQuery versus Redshift versus Athena

	BigQuery	Redshift	Athena
Model	Serverless, fully managed	Provisioned clusters, or Serverless	Query over S3
Storage	Managed, columnar	Managed, columnar	Your files, your format
Pricing	Per byte scanned, or slots	Per cluster-hour, or RPU	Per byte scanned
Concurrency	High, elastic	Bounded by cluster	Bounded by service quota
Performance ceiling	Very high, Dremel	High with good distribution keys	Moderate
Operational burden	Almost none	Vacuum, distribution keys, sizing	Almost none
Fit here	Strongest	Fine, more tuning	Good for the archive

the honest comparison

BigQuery is the better product, and saying so is not tribal: the separation of storage and compute is genuinely more complete, concurrency does not require sizing a cluster, and the operational surface is close to zero. Redshift is capable and asks more of you, particularly around distribution keys and vacuum. Athena is the right answer for querying the Part 15 archive in place, and on AWS I would use Athena for the archive and Redshift only if the warehouse workload justified a cluster.

Separation of storage and compute, and what it means for cost

worked numbers

coupled (classic Redshift, self-managed)
  storage and compute scale together
  idle cluster still costs full price
  you pay for the peak, all the time

separated (BigQuery, Athena, Redshift Serverless)
  storage is cheap and permanent
  compute is summoned per query and released
  you pay for what you scan
  and a careless query can cost real money in seconds

the cost controls that follow

Partition and cluster the tables, per Part 11 chapter 128, which was a 160x difference for the same answer.
Require a partition filter, so a forgotten WHERE fails rather than bills.
Per-project or per-user quotas, so one bad query cannot consume the month.
Materialised aggregates for dashboards, because a dashboard refreshing every minute must never scan the base table.
Reserved capacity when usage is predictable: BigQuery slots or Redshift reserved nodes are substantially cheaper than on-demand at steady volume.

the shape worth internalising

Per-byte pricing makes cost a function of schema design rather than of usage. That is a genuinely different mental model from a provisioned cluster, and it is why Part 11 treated partitioning as a cost control rather than a performance one. An engineer who designs the table well saves more than a team optimising queries later.

The lakehouse: S3 plus Iceberg, GCS plus BigLake

AWS

GCP

for storage, Iceberg as the table format, Glue Catalog for metadata, queried by Athena, Redshift Spectrum or EMR.

Open formats throughout. The Part 15 archive and the analytics lake are the same files.

GCS for storage, BigLake tables giving BigQuery governed access to external data, with Iceberg support.

Same property: one copy of history, two access patterns.

why the table format is not optional

Atomic commits, so a reader never sees a half-written update.
Time travel, which Part 11 chapter 131 needed for reproducible regulatory reports and Part 15 chapter 185 made pin number one.
Schema evolution by column id, so adding or renaming does not corrupt old files.
Row-level deletes, which is what makes the Part 15 chapter 189 erasure story workable at all.

the architectural payoff, restated

Part 15 made the point and it is worth repeating here because the cloud makes it concrete: the compliance archive and the analytics lake are the same Parquet files in the same bucket. Storing them twice would double cost and, worse, create two versions of history that could diverge. One copy, two consumers, one truth.

Streaming ingestion on each platform

AWS

GCP

Kinesis Firehose

into S3, buffering by size or time, with optional format conversion to Parquet.

For DynamoDB serving: a Lambda on the Kafka topic doing batched writes.

Dataflow or the BigQuery Storage Write API for streaming inserts, or Pub/Sub’s native BigQuery subscription which needs no code at all.

For Bigtable: Dataflow, or a Cloud Run consumer.

Path	Latency	Cost shape	Use for
Streaming insert	Seconds	Per row	Fraud features, live dashboards
Micro-batch to object storage	5 to 15 min	Free to load	Most analytics
Daily batch	Hours	Free	Financial reporting, aligned to the close

the alignment point, again

Part 11 chapter 129 insisted that financial reporting loads use the same cut-off timestamp as the Part 10 close. On a managed streaming pipeline that alignment is easy to lose, because the pipeline has its own schedule. Make the close cut-off an explicit input to the reporting load, or the CFO’s dashboard and the signed trial balance will disagree and nobody will be able to say which is right.

Caching: ElastiCache and Memorystore

Part 3 put Redis in front of balance reads with a watermark on every cached value, and was explicit that it holds derived data only.

AWS

GCP

ElastiCache for Redis

, cluster mode enabled for sharding, multi-AZ with automatic failover.

Or MemoryDB if you want Redis with durability, which is a different product and costs more.

Memorystore for Redis, standard tier for HA, or the cluster tier for sharding.

Functionally equivalent for our use.

the configuration that matters here

Eviction policy. allkeys-lru is right for a balance cache: it is derived data and evicting it costs a database read, not correctness.
Multi-AZ with failover, because a cache outage at 18,000 reads a second becomes a database incident immediately.
No persistence needed, and that is the point. Part 1 established Redis is never the ledger, so losing it entirely is a performance event rather than a correctness one.
Separate clusters per purpose. Balance cache, rate limit counters and session state should not share an eviction pool, or a burst in one evicts another.

the failure drill worth running

Flush the cache in production, deliberately, during business hours. If the system survives, the Part 3 cache design was right and the fallback works. If the database falls over, you have discovered that the cache is load-bearing for correctness rather than performance, which is a design problem and better found on a Tuesday afternoon than at 3am.

The decision, with numbers

AWS

GCP

DynamoDB

on-demand for serving, S3 plus Iceberg plus Athena for the lake and archive, Redshift Serverless only if a cluster workload emerges, ElastiCache for the balance cache.

Roughly $6,000 to $9,000 per month, dominated by DynamoDB requests and storage.

Bigtable for serving, BigQuery for the warehouse, GCS plus BigLake for the lake, Memorystore for the cache.

Roughly $7,000 to $10,000 per month, with Bigtable node-hours as a floor regardless of traffic.

the answer

"The serving layer is a coin flip: DynamoDB and Bigtable both do per-account history well, and the key design is the same problem on both, which Part 3 already solved by splitting the fee account. The warehouse is not a coin flip: BigQuery is the stronger product, and if analytics were central to this business rather than supporting it, that alone could decide the platform. On AWS I would use Athena over Iceberg for the archive and add Redshift only when a workload justifies a cluster. And one copy of history serves both compliance and analytics, because two copies is two versions of the past."