2.1 Organizing and Exploring Tabular, Text, Image and Video Data

2.1 Organizing and Exploring Tabular, Text, Image and Video Data

Blueprint reference: Section 2.1, "Organizing and exploring different data types (e.g., tabular, text, and images) for efficient experimenting, training, and serving."

The word "efficient" carries the weight in that bullet. Every question in this area is really asking: given this data type and this access pattern, what layout keeps exploration cheap, training fast, and serving correct?

Storage Decision by Data Type

The rule that resolves most scenarios: BigQuery for exploration and analytics, Cloud Storage for bulk training reads, Feature Store for anything that must be identical at training and serving time.

Making Tabular Exploration Cheap

BigQuery bills on bytes scanned, so exploration cost is a layout decision, not a query-writing decision.

• Partitioning splits a table physically by a date, timestamp, integer range, or ingestion time. A query filtering on the partition column prunes whole partitions and never reads them. This is the single largest cost lever on a large fact table.
• Clustering sorts data within each partition by up to four columns. Filters and joins on the leading clustered columns read fewer blocks. Clustering helps high-cardinality columns where partitioning would create too many partitions.

CREATE TABLE `analytics.events`
PARTITION BY DATE(event_ts)
CLUSTER BY customer_id, event_type
AS SELECT * FROM `raw.events`;

Two habits worth building. Use TABLESAMPLE SYSTEM (1 PERCENT) for exploratory profiling rather than scanning the full table, and require partition filters (require_partition_filter = TRUE) on very large tables so an accidental full scan is rejected rather than billed.

For distribution and null profiling before any training, ML.FEATURE_INFO on a trained model or plain APPROX_QUANTILES and COUNTIF(col IS NULL) aggregates answer most questions in a single pass.

Organizing Unstructured Data

Cloud Storage has a flat namespace; "directories" are prefixes. Two layout rules follow:

Prefer shallow, well-chosen prefixes. A layout like gs://bucket/images/2026/09/01/… makes date-scoped listing fast. A layout that buries files under one prefix per user identifier makes listing millions of objects slow and awkward to shard across workers.

Keep metadata in BigQuery, bytes in Cloud Storage. A metadata table holding URI, label, split assignment, capture date, and quality flags lets you answer "how many labelled night-time images do we have from EMEA" with SQL, then materialize the matching URI list for the training job. Object tables formalize this: a BigQuery table over a Cloud Storage prefix that exposes each file as a row with its URI, size, content type, and updated time, queryable with SQL, governed by IAM, and directly consumable by generative SQL functions.

CREATE EXTERNAL TABLE `media.frames`
WITH CONNECTION `us.gcs_conn`
OPTIONS (object_metadata = 'SIMPLE',
         uris = ['gs://media-bucket/frames/*']);

The Small-File Problem

This is the most frequently tested storage pathology on the exam. Training on millions of individual small files means one storage request per file, per epoch. Request latency, not bandwidth, becomes the bottleneck, GPUs sit idle, and utilization graphs show accelerators at 15–25% while the host CPU pegs at 100%.

The fix is consolidation into large sharded files — typically 100 MB to a few hundred MB each — in a format designed for sequential access:

Shard count should be a comfortable multiple of the worker count so every worker gets whole shards and the reader can interleave across shards for randomness.

Region co-location is the other half. Training in us-central1 against a bucket in europe-west1 adds cross-region latency and egress cost to every read. Data and compute belong in the same region.

Splits That Do Not Leak

Splitting is a data-organization decision, and getting it wrong invalidates every metric downstream.

• Deterministic hashing. Assign splits by hashing a stable key: MOD(ABS(FARM_FINGERPRINT(customer_id)), 10). Buckets 0–7 train, 8 validate, 9 test. The same customer always lands in the same split, even as new rows arrive.
• Group by entity, not by row. If a customer has 40 events, splitting rows randomly puts the same customer in train and test, and the model memorizes rather than generalizes.
• Time-based splits for temporal problems. Forecasting and anything with drift must train on the past and test on the future. Random splits let the model see the future and report accuracy that production will never reproduce.
• Store the split. Write the assignment into a column rather than recomputing it, so every experiment and every retraining run uses the identical partition.

Exam Traps

• Random splits on time-series data. Always a wrong answer for forecasting.
• Row-level splits on grouped entities. Leakage disguised as good performance.
• Millions of small files feeding a GPU. Consolidate into sharded TFRecord or Parquet.
• Cross-region reads. Co-locate bucket and training job.
• Copying unstructured data into BigQuery. Use object tables instead of loading blobs.