Prepare and test your skills
Prepare and test your skills
Free sample question, answered and explained below. The Professional Cloud Architect practice bank has 2,335 more like it. Try 10 free: instant score, no account.
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:
retail_dw.orders must be partitioned daily on an existing order_timestamp column.WHERE clause to avoid costly accidental full-table scans.RECORD fields.Which bq command-line invocation correctly configures the table parameters and loads the files?
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/*.jsonThis command attempts to load newline-delimited JSON data using an inline schema definition string while setting column-based time partitioning.
FIELD:DATA_TYPE), bq does not support RECORD (STRUCT) types, custom field modes, or column descriptions. Complex nested schemas must be defined in a JSON schema file.--time_partitioning_expiration, which means partitions will never expire automatically, resulting in unbounded long-term storage consumption.--require_partition_filter, leaving queries unprotected against unbounded full-table scans.FIELD:DATA_TYPE).NULLABLE.The dataset contained only flat scalar columns, required no nested records, and did not necessitate lifecycle expiration or query filter restrictions.
bq mk --table --schema=./order_schema.json retail_dw.orders and then run gcloud storage cp gs://retail-bucket/orders/*.json bq://retail_dw.ordersThis command attempts to create a table using bq mk and then use gcloud storage cp to copy data from Cloud Storage directly into a BigQuery table.
gcloud storage cp is an object storage management tool designed to copy data between Cloud Storage buckets or local filesystems; it does not support streaming or batch loading into BigQuery tables via a pseudo-URI like bq://.bq mk command as written lacks the parameters for --time_partitioning_field, --time_partitioning_expiration, and --require_partition_filter.gcloud storage is strictly for managing buckets and objects in Google Cloud Storage.bq mk requires explicit partitioning flags if created prior to loading.bq load, the BigQuery Data Transfer Service, the Storage Write API, or SQL LOAD DATA statements.You were creating an unpartitioned table via bq mk and transferring files between Cloud Storage buckets using gcloud storage cp before initiating a separate bq load job.
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.jsonbq load command isThe 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.
--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.--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.--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../order_schema.json schema file allows BigQuery to apply explicit data types, RECORD (STRUCT) elements, and field modes (NULLABLE or REQUIRED).NEWLINE_DELIMITED_JSON formatted files directly from Cloud Storage bucket wildcards.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.
bq load --source_format=NEWLINE_DELIMITED_JSON --autodetect --time_partitioning_type DAY --time_partitioning_expiration 7776000 retail_dw.orders gs://retail-bucket/orders/*.jsonThis command uses schema auto-detection (--autodetect) to infer data types by scanning up to the first 500 rows of data from Cloud Storage, and creates an ingestion-time partitioned table.
--time_partitioning_field is omitted, BigQuery creates an ingestion-time partitioned table rather than partitioning on the business column order_timestamp.--require_partition_filter, allowing unconstrained full-table scans that can lead to significant on-demand query billing overruns.--autodetect flag cannot enforce predefined schemas with explicit field modes or custom documentation descriptions, and it creates the schema based solely on sample data inspection.NULLABLE._PARTITIONTIME instead of user-specified timestamp columns.You needed a quick ad-hoc load for unpartitioned or ingestion-time partitioned data where the schema is unknown beforehand and query scan limits are unnecessary.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated