Storing the data

A retail analytics team has a 12 TB BigQuery table already partitioned by DATE(order_timestamp). Most dashboard queries filter on a specific date range AND a specific customer_id (there are roughly 8 million distinct customer IDs). Currently these queries scan the entire day's partition even when only one customer is needed, driving up on-demand costs. Without changing the partitioning strategy, what is the most effective way to reduce bytes scanned for these filtered queries?

A retail analytics team is designing a BigQuery table to store customer orders. Each order has one customer and multiple line items (product, quantity, price). Analysts run frequent aggregations across orders and line items, and query cost and performance are top priorities. The team is deciding how to model the relationship between orders and their line items in BigQuery. What is the recommended schema design?

A retail analytics team lands raw daily CSV and Parquet files in a Cloud Storage data lake. Analysts run ad-hoc exploratory queries only a few times a week over recent files, and the raw files are frequently overwritten by an upstream process. The team wants to minimize storage duplication and avoid an ETL step, while accepting slower query performance for these occasional queries. What is the most appropriate way to make this data queryable in BigQuery?

A retail analytics team is designing a BigQuery dataset for daily sales reporting. Analysts frequently query aggregated revenue by product category, region, and date. The team debates whether to keep normalized dimension tables (products, stores) separate from a large fact table or to consolidate the data. Query cost and performance on large scans are the primary concerns, and the dimension tables change infrequently. Which schema approach best fits BigQuery's architecture?

A retail analytics team stores a large fact table in BigQuery keyed by a numeric customer_id (values 1 to roughly 5,000,000). Nearly all analytical queries filter on a specific range of customer_id to process a subset of customers at a time, but the table has no meaningful timestamp column that queries filter on. The team wants to prune data scanned and reduce query cost without changing how analysts write their WHERE clauses. Which BigQuery design should they use?

A retail analytics team runs a dashboard that refreshes every few minutes. The underlying query aggregates daily sales totals by store and product category from a large, continuously appended BigQuery fact table (billions of rows). The same aggregation is executed hundreds of times per hour, and the team is concerned about repeatedly scanning the full table and the associated query cost. They want the aggregated results to stay automatically up to date as new rows arrive, with minimal maintenance overhead. What should they do?

A retail analytics team stores five years of point-of-sale transactions in a single BigQuery table (~8 TB). Nearly all analyst queries filter on a transaction_date range (usually the last 30-90 days) and frequently also filter or group by store_id. Current queries scan the entire table, driving up on-demand query costs. The team wants to minimize bytes scanned without rewriting the analysts' query patterns. What table design should you recommend?

A retail analytics team loads daily sales events into a BigQuery table partitioned by ingestion date. Compliance requires that only the last 90 days of detailed event data be queryable, and older data should be automatically removed to control storage cost without manual cleanup jobs. Analysts still run frequent queries filtered on the event date. What is the most operationally efficient way to meet this requirement?

A retail analytics team runs predictable, heavy BigQuery workloads every business day: several large scheduled ETL jobs plus dashboards that refresh throughout working hours. Their monthly on-demand bytes-scanned costs have become large and unpredictable, and finance wants a stable, capped monthly spend for the analytics workload. They also occasionally run one-off ad hoc exploration queries that should not block the production ETL jobs. Which approach best meets the requirement for predictable cost while isolating workloads?

A retail analytics team stores a customer dimension table in BigQuery. They must preserve full historical attribute values (e.g., a customer's address as it was at the time of each past order) so that historical reports remain accurate even after a customer's details change. Analysts frequently join fact tables to this dimension using the value that was effective at the transaction date. Which schema design should the team implement?

An industrial IoT company ingests sensor readings from 50,000 devices, each emitting one reading per second. The primary access pattern is retrieving the most recent readings for a single device over the last hour, and occasionally scanning a device's readings across a full day. The team chose Cloud Bigtable for its write throughput and low-latency reads. Which row key design best supports these access patterns while avoiding hotspotting?

An industrial monitoring platform collects sensor readings from 500,000 devices, each emitting one metric every second (roughly 43 billion writes per day). Analysts need single-digit-millisecond lookups of the most recent readings for any given device over a specified time range, and the workload will grow to millions of devices. Which storage system and design best fits these requirements?

A retail company is building a data lake on Google Cloud. They ingest raw JSON and CSV files from hundreds of point-of-sale systems into a landing area. Requirements: files must be immediately queryable by ad hoc BigQuery jobs during the first 30 days, retained for at least 7 years for compliance, and stored as cheaply as possible after the active analysis window since they are rarely accessed afterward. The team wants a single, cost-optimized storage approach that avoids manual file movement. Which design best meets these needs?

A large retailer is reorganizing its analytics platform around a data mesh. Each business domain (Sales, Inventory, Marketing) must own its own data products, control access, and publish curated datasets that other domains can discover and subscribe to without copying raw data. The central platform team wants to enable self-service data sharing across domains and organizations while keeping each domain autonomous in BigQuery. Which approach best supports this data mesh model on Google Cloud?

You are building a mobile app for a retail loyalty program. Each user has a document containing profile data, points balance, and a list of coupons. The app must let users view and edit their profile even when offline, then automatically sync changes when connectivity returns. The dataset is a few hundred bytes per user across millions of users, with mostly single-document reads and writes keyed by user ID. Which storage system best fits these requirements?

You are building a collaborative messaging feature for a mobile app. Clients must receive new messages within a chat room in near real time without polling, and the app needs to work while the device is briefly offline, syncing changes when connectivity returns. Message volume per room is moderate (thousands of messages), and you expect millions of rooms. Which storage system best fits these requirements?

A retail startup is building a product catalog service. Each product can have wildly different attributes (electronics have wattage and screen size; clothing has sizes and materials), and new product categories with new attributes are added frequently without downtime. The service must support single-item lookups by product ID and simple queries filtering on a handful of indexed fields, serving a mobile app with millions of reads per day across multiple regions. Which storage system best fits these requirements?

A retail company is building a product catalog service. Each product is a flexible document with varying attributes (some have color/size, others have volume/weight), and the mobile app must fetch a single product by its ID with sub-10-millisecond latency and support ad-hoc queries on individual product fields. The catalog has about 5 million products and receives moderate read traffic with occasional single-document updates. There is no requirement for complex multi-table joins or analytical aggregation. Which storage service best fits these requirements?

A mobile gaming company is building a global leaderboard and player-state service. The workload consists of millions of small key-based reads and writes per second (player profiles keyed by player ID), requires single-digit millisecond latency, and must scale horizontally as the player base grows into the hundreds of terabytes. The team does not need multi-row ACID transactions or complex SQL joins. Which storage service should they choose?

A retail company is building an order-processing application for a single country. The workload is transactional (frequent inserts and updates with ACID guarantees), uses a well-defined relational schema with joins, and peaks at roughly 4,000 transactions per second. The team wants a fully managed relational database but explicitly wants to avoid the cost of horizontal, globally distributed scaling because all users and operations are within one region. Which storage service best fits these requirements?