For a high volume of small UPDATE and DELETE operations on individual rows, a clustered and partitioned table is the most efficient design. Clustering physically sorts the data based on the values in one or more columns. When DML statements include a filter (WHERE clause) on these clustering columns, BigQuery can quickly locate the specific data blocks containing the rows to be modified without scanning the entire table or partition. This significantly improves performance and reduces the cost associated with these point-specific DML operations, which is crucial for the described real-time workload.

A. Standard, non-partitioned table.

Without partitioning or clustering, DML operations would require a full table scan to locate the target rows, making it extremely inefficient and costly for frequent, small updates.

C. BigQuery Storage Write API for streaming updates.

The Storage Write API is optimized for high-throughput data ingestion (appending new rows), not for modifying or deleting existing rows. It does not support UPDATE or DELETE DML statements.

D. Materialized Views.

Materialized views are designed to accelerate query performance by pre-calculating and caching results. They do not improve the performance of DML operations on the base table; in fact, they can add overhead.

1. Google Cloud Documentation - Introduction to clustered tables: "Clustering can also improve the performance of data manipulation language (DML) statements that modify a small number of rows. When you run an UPDATE or DELETE statement on a clustered table with a WHERE clause, BigQuery uses clustering to find the blocks that contain the rows to be modified."

2. Google Cloud Documentation - Manage clustered tables: "When you submit a DML UPDATE, DELETE, or MERGE statement that modifies data in a clustered table, BigQuery uses the filter expression to prune the blocks that are scanned."

3. Google Cloud Documentation - Introduction to partitioned tables: "When you use DML to update or delete rows in a partitioned table, you can use the partition column in a WHERE clause to limit the partitions that are scanned." This shows how partitioning complements clustering for DML optimization.

4. Google Cloud Documentation - Introduction to the BigQuery Storage Write API: The documentation describes the API's purpose for "streaming ingestion" and "batch loading," confirming it is for adding new data, not modifying existing data via DML.