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

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)

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 --autodetect --time_partitioning_type DAY --time_partitioning_expiration 7776000 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 --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

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

Professional Cloud Architect

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)

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 --autodetect --time_partitioning_type DAY --time_partitioning_expiration 7776000 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

Explanations

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.

View source documentation

Professional Cloud Architect

Bq Load Json Partitioning Schema Optimization

Professional Cloud Architect

Bq Load Json Partitioning Schema Optimization

What the bq load command is

How it meets the requirements

Key advantages

Why this is optimal

Related questions

What the `bq load` command is