A data architect is configuring an automated pipeline to ingest daily e-commerce order records stored as newline-delimited JSON files in Cloud Storage into BigQuery. The implementation must meet the following operational requirements: The destination table retail_dw.orders must be partitioned daily on an existing order_timestamp column. Stored partitions must automatically expire and be deleted after 90 days (7,776,000 seconds) to control long-term storage costs. Queries against the table must be forced to include a partition filter in the WHERE clause to avoid costly accidental full-table scans. The table must use a predefined schema definition containing nested RECORD fields. Which bq command-line invocation correctly configures the table parameters and loads the files?

Execute `bq load --source_format=NEWLINE_DELIMITED_JSON --time_partitioning_field order_timestamp --time_partitioning_type DAY --time_partitioning_expiration 7776000 --require_partition_filter retail_dw.orders gs://retail-bucket/orders/*.json ./order_schema.json`: ### What the `bq load` command is The **`bq load`** command in the **Google Cloud SDK** creates a BigQuery load job that ingests data from external sources (such as Cloud Storage) into a destination table while simultaneously establishing table properties, schemas, and performance configurations. ### How it meets the requirements - **Field-based Partitioning**: Setting `--time_partitioning_field order_timestamp` and `--time_partitioning_type DAY` configures the destination table to partition data by day according to values in the `order_timestamp` column rather than table ingestion time. - **Storage Cost Management**: Specifying `--time_partitioning_expiration 7776000` defines a partition time-to-live of exactly 90 days in seconds, automatically deleting stale partitions and eliminating unnecessary storage charges. - **Query Cost Controls**: Enabling `--require_partition_filter` enforces mandatory partition filtering on all downstream queries, preventing analysts and BI tools from incurring high scanning costs from unintentional full-table scans. - **Custom Schema with Nested Structures**: Referencing the `./order_schema.json` schema file allows BigQuery to apply explicit data types, `RECORD` (`STRUCT`) elements, and field modes (`NULLABLE` or `REQUIRED`). ### Key advantages - **Automated Lifecycle Management**: Partitions are cleaned up transparently without requiring external cron jobs or scheduled delete queries. - **Guaranteed Query Pruning**: Ensures cost-predictable query operations by rejecting queries lacking partition predicates at parse time. - **Atomic Ingestion and Configuration**: Initializes table schema, partitions, and constraints in one operation during the initial file load. - **Native JSON Support**: Correctly interprets `NEWLINE_DELIMITED_JSON` formatted files directly from Cloud Storage bucket wildcards. ### Why this is optimal This approach configures all data governance, performance tuning, and lifecycle parameters at the point of ingestion using standard, supported `bq` CLI flags without requiring multi-step DDL execution or external orchestration tools.

StudyCerts

Bq Load Json Partitioning Schema Optimization | Intrigued by the art of cloud architecture? Discover how to design, develop, and manage robust, secure, scalable, and dynamic solutions on Google Cloud as you prepare for the Professional Cloud Architect exam! | StudyCerts

Professional Cloud Architect

Intrigued by the art of cloud architecture? Discover how to design, develop, and manage robust, secure, scalable, and dynamic solutions on Google Cloud as you prepare for the Professional Cloud Architect exam!

Intermediate

Prepare and test your skills

Track Readiness

Set Exam Goal

Intermediate

Prepare and test your skills

Track Readiness

Set Exam Goal

Bq Load Json Partitioning Schema Optimization

Section: Google Cloud SDKs (e.g., gcloud, gsutil, and bq)

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

A data architect is configuring an automated pipeline to ingest daily e-commerce order records stored as newline-delimited JSON files in Cloud Storage into BigQuery. The implementation must meet the following operational requirements:

The destination table retail_dw.orders must be partitioned daily on an existing order_timestamp column.
Stored partitions must automatically expire and be deleted after 90 days (7,776,000 seconds) to control long-term storage costs.
Queries against the table must be forced to include a partition filter in the WHERE clause to avoid costly accidental full-table scans.
The table must use a predefined schema definition containing nested RECORD fields.

Which bq command-line invocation correctly configures the table parameters and loads the files?

Execute bq load --source_format=NEWLINE_DELIMITED_JSON --time_partitioning_field order_timestamp --schema order_id:STRING,amount:FLOAT,order_timestamp:TIMESTAMP retail_dw.orders gs://retail-bucket/orders/*.json

Execute bq mk --table --schema=./order_schema.json retail_dw.orders and then run gcloud storage cp gs://retail-bucket/orders/*.json bq://retail_dw.orders

Execute bq load --source_format=NEWLINE_DELIMITED_JSON --autodetect --time_partitioning_type DAY --time_partitioning_expiration 7776000 retail_dw.orders gs://retail-bucket/orders/*.json

Execute bq load --source_format=NEWLINE_DELIMITED_JSON --time_partitioning_field order_timestamp --time_partitioning_type DAY --time_partitioning_expiration 7776000 --require_partition_filter retail_dw.orders gs://retail-bucket/orders/*.json ./order_schema.json

Professional Cloud Architect

Bq Load Json Partitioning Schema Optimization

Related questions