Choosing storage for prepared data

Data that has been cleaned and checked has to live somewhere, and the right place depends on how it will be used. On Tuesday, November 10, Ravi gives Mateo a list of six things to store and asks him to choose a home for each: the raw till files, the curated sales tables, the 40 TB product photo library, the shop's live product catalogue, a store-level stock table that the new mobile app will read thousands of times a minute, and seven years of tax records that must be kept but will almost never be read. No single service is right for all six.

Matching data to a storage service

The exam often describes a workload and asks for the storage service. The deciding questions are the shape of the data, how it is accessed, how much there is, and how fast reads and writes must be.

A common exam trap is choosing BigQuery for an application's day-to-day database. BigQuery is built for analysis: scanning millions of rows to compute a total. It is not built for an app that updates one order at a time thousands of times a minute. The reverse trap is running large analytical reports against Cloud SQL, which will be slow and will compete with the shop's customers for the database. Operational data lives in operational databases, and copies flow to BigQuery for analysis, which is exactly what Datastream does for the shop.

Cloud Storage: classes and lifecycle

Cloud Storage offers several storage classes. They have the same speed and durability; they differ in the monthly price of storing data, the price of reading it back, and the minimum storage duration, the period you pay for even if you delete the object sooner.

Data does not have to stay in one class. Object Lifecycle Management rules move objects to a colder class or delete them when conditions are met, such as an object's age. Autoclass goes further and moves each object between classes automatically based on how often it is actually read, which suits data whose access pattern nobody can predict. Birchline's landing bucket uses a lifecycle rule: till files stay in Standard for 30 days while they might be reloaded, move to Coldline, and are deleted after 400 days, a little over the 13 months Finance wants to be able to reprocess.

{
  "rule": [
    {"action": {"type": "SetStorageClass", "storageClass": "COLDLINE"},
     "condition": {"age": 30, "matchesStorageClass": ["STANDARD"]}},
    {"action": {"type": "Delete"},
     "condition": {"age": 400}}
  ]
}

The photo library is a different case. Photos of current products are read constantly by the website and the merchandising team; photos of discontinued products are almost never read but must be kept for marketing archives. Nobody knows in advance which product will be discontinued, so Autoclass fits better than a fixed age rule. Mateo compares the yearly cost of a few options using illustrative prices, to see why the choice matters for 40 TB.

# Yearly storage cost for the 40 TB photo library under different choices (illustrative prices).
tb = 40
gb = tb * 1000
price_per_gb_month = {"Standard": 0.023, "Nearline": 0.013, "Coldline": 0.006, "Archive": 0.0025}
retrieval_per_gb   = {"Standard": 0.0,   "Nearline": 0.01,  "Coldline": 0.02,  "Archive": 0.05}
read_gb_per_year = 0.25 * gb * 12        # a quarter of the library read each month (current products)

for cls in price_per_gb_month:
    storage = gb * price_per_gb_month[cls] * 12
    reads = read_gb_per_year * retrieval_per_gb[cls]
    print(f"{cls:9s} storage {storage:8,.0f}  retrieval {reads:8,.0f}  total {storage + reads:8,.0f} dollars a year")

hot_share = 0.25   # roughly the current products, kept in Standard by Autoclass
mixed = gb * hot_share * price_per_gb_month["Standard"] * 12 + gb * (1 - hot_share) * price_per_gb_month["Archive"] * 12
print(f"Mixed (a quarter Standard, the rest Archive, few cold reads): about {mixed:,.0f} dollars a year")

Standard  storage   11,040  retrieval        0  total   11,040 dollars a year
Nearline  storage    6,240  retrieval    1,200  total    7,440 dollars a year
Coldline  storage    2,880  retrieval    2,400  total    5,280 dollars a year
Archive   storage    1,200  retrieval    6,000  total    7,200 dollars a year
Mixed (a quarter Standard, the rest Archive, few cold reads): about 3,660 dollars a year

The numbers make the point clearly. Standard for everything costs about 11,000 dollars a year. Archive looks cheapest for storage alone, at about 1,200 dollars, but reading a quarter of the library every month from Archive adds about 6,000 dollars of retrieval charges, which makes it dearer than Coldline at about 5,280 dollars in total. The mixed approach, which is roughly what Autoclass achieves on its own by keeping busy photos warm and letting the rest cool down, comes to about 3,660 dollars. The prices are illustrative and the real ones should be taken from the current price list for the chosen location, but the shape of the comparison holds: a cold class is cheap only for data that really is cold.

Location types

Every bucket and dataset has a location, and the exam guide asks you to choose among the types. A region is a single geographic place, such as northamerica-northeast2 in Toronto. A dual-region is a specific pair of regions; data is replicated across both, so it survives the loss of one region and can be read in either. A multi-region is a large area, such as US, EU or ASIA, where Google chooses where to keep copies. A zone is a single data centre area inside a region; Cloud Storage offers zonal buckets only for its Rapid storage, aimed at very high-performance AI and analytics work, and those buckets give up many features, including use with BigQuery.

For Birchline in Canada, location is also a privacy decision. Customer data must stay in Canada, and there is no Canadian multi-region; the multi-regions are US, EU and ASIA. Google's documentation lists a configurable dual-region for Canada that pairs Montréal and Toronto. The team uses regional Toronto buckets and datasets for analytics, to keep data next to BigQuery, and a Montréal and Toronto dual-region bucket for the photo library and for backups that must survive the loss of a region.

One rule ties location to the rest of the course: BigQuery loads and queries work best when the data and the dataset are in the same location, and some operations require it. A load from a bucket in one region into a dataset in another can fail or incur transfer charges. BigQuery data preparation, for example, requires its sources and destination to share a location, as lesson 4 noted. Choosing locations deliberately at the start saves painful moves later.

BigQuery storage

BigQuery storage has its own pricing rules that reward good table design. A table or partition that has not been modified for 90 consecutive days moves automatically to long-term storage, which costs about half as much, with no change in performance. Partitioning by date therefore saves money as well as query cost: last year's partitions become long-term storage while this month's stay active. A dataset can be billed on logical bytes (uncompressed) or physical bytes (compressed, including time travel data), and physical billing is often cheaper for data that compresses well.

BigQuery also keeps recent history. Time travel lets you query or restore a table as it was at any point in the last seven days by default, configurable down to two days, which is invaluable after a bad MERGE. After that, a further seven-day fail-safe period lets Google recover data in an emergency, though not on demand. And table expiration deletes tables or partitions automatically after a set time, which keeps scratch datasets from growing forever and enforces retention rules.

Long-term retention

The seven years of tax records are a retention problem rather than an analytics one. The Canada Revenue Agency generally expects business records to be kept for six years from the end of the tax year they relate to, and Finance keeps seven for safety. The records are exported as Parquet to an Archive-class bucket, and a retention policy on the bucket prevents anyone from deleting or overwriting objects before seven years have passed. Locking the policy makes it permanent, so it is done only after Finance and legal have agreed the period.

Mateo's answers