Scenario practice questions

You are ingesting CSV files into BigQuery using the bq load command. The CSV files have a header row, and you want BigQuery to automatically detect the schema and skip the header. Which flags should you include?

Trap 1: --autodetect --header=true

--autodetect is correct, but --header=true is not a recognized flag in the bq load command. BigQuery uses --skip_leading_rows to skip header rows. Without that flag, the header row would be treated as data, potentially causing type mismatches or extra rows. So this combination would fail to skip the header as required.

Trap 2: --infer_schema --skip_rows=1

Neither --infer_schema nor --skip_rows is a valid bq load flag. BigQuery's schema inference is enabled with --autodetect, and header skipping is done with --skip_leading_rows. Using these invalid flags would result in a command error and no data load, so it does not meet the requirement.

Trap 3: --schema --header

The --schema flag expects a comma-separated list of column definitions, so it does not auto-detect the schema. The --header flag is not a valid bq load flag; the correct flag to skip a header row is --skip_leading_rows. Using these would either cause an error or require you to manually specify the schema, which contradicts the requirement for automatic detection.

A
--autodetect --header=true
Why it fails: --autodetect is correct, but --header=true is not a recognized flag in the bq load command. BigQuery uses --skip_leading_rows to skip header rows. Without that flag, the header row would be treated as data, potentially causing type mismatches or extra rows. So this combination would fail to skip the header as required.
B
--infer_schema --skip_rows=1
Why it fails: Neither --infer_schema nor --skip_rows is a valid bq load flag. BigQuery's schema inference is enabled with --autodetect, and header skipping is done with --skip_leading_rows. Using these invalid flags would result in a command error and no data load, so it does not meet the requirement.
C
--autodetect --skip_leading_rows=1
The --autodetect flag tells BigQuery to infer the schema from the data, and --skip_leading_rows=1 tells it to ignore the first row, which is the header. Together, these flags allow a seamless load of CSV files with headers into a new table. This is the standard approach for ad-hoc loads when you do not want to define a schema manually.
D
--schema --header
Why it fails: The --schema flag expects a comma-separated list of column definitions, so it does not auto-detect the schema. The --header flag is not a valid bq load flag; the correct flag to skip a header row is --skip_leading_rows. Using these would either cause an error or require you to manually specify the schema, which contradicts the requirement for automatic detection.

Question 2easymultiple choice

A colleague must load a 200 GB uncompressed CSV file from a Cloud Storage bucket into a BigQuery table once, with the lowest possible load cost and no transformation. They want the simplest option that BigQuery handles natively. What should they use?

Trap 1: A Cloud Data Fusion pipeline with a Cloud Storage source and…

Cloud Data Fusion is a managed CDAP environment with its own instance cost and pipeline design effort, which is disproportionate for a single untransformed CSV load. It is intended for visual, repeatable transformation pipelines, not one-off bulk ingestion, so it violates the simplicity and lowest-cost goals stated in the scenario.

Trap 2: A Dataflow pipeline using the TextIO source and BigQueryIO sink

Dataflow can read CSV from Cloud Storage and write to BigQuery, but it provisions Dataflow workers and incurs per-vCPU-hour charges, which is far more expensive than a direct load for a single untransformed file. It also adds pipeline code and monitoring overhead that the scenario explicitly wants to avoid, making it a poor fit for a one-time simple ingestion.

Trap 3: A Dataproc job running Spark to read the CSV and write to BigQuery

Dataproc spins up a cluster of Compute Engine VMs and bills for cluster time, which is unnecessary and expensive for a single CSV load with no transformation. Spark adds schema handling and job code, so it fails the simplicity requirement. This approach makes sense for heavy transformation, not for a direct bulk ingest.

A
bq load with the CSV file specified as a Cloud Storage URI
The bq load command ingests data directly from Cloud Storage into a BigQuery table without charging for a separate compute service, and it is the native, simplest path for a one-time CSV load. BigQuery charges only for storage after the load, so this meets the lowest-cost and no-transformation requirements for a 200 GB file.
B
A Cloud Data Fusion pipeline with a Cloud Storage source and BigQuery sink
Why it fails: Cloud Data Fusion is a managed CDAP environment with its own instance cost and pipeline design effort, which is disproportionate for a single untransformed CSV load. It is intended for visual, repeatable transformation pipelines, not one-off bulk ingestion, so it violates the simplicity and lowest-cost goals stated in the scenario.
C
A Dataflow pipeline using the TextIO source and BigQueryIO sink
Why it fails: Dataflow can read CSV from Cloud Storage and write to BigQuery, but it provisions Dataflow workers and incurs per-vCPU-hour charges, which is far more expensive than a direct load for a single untransformed file. It also adds pipeline code and monitoring overhead that the scenario explicitly wants to avoid, making it a poor fit for a one-time simple ingestion.
D
A Dataproc job running Spark to read the CSV and write to BigQuery
Why it fails: Dataproc spins up a cluster of Compute Engine VMs and bills for cluster time, which is unnecessary and expensive for a single CSV load with no transformation. Spark adds schema handling and job code, so it fails the simplicity requirement. This approach makes sense for heavy transformation, not for a direct bulk ingest.

Question 3mediummultiple choice

Your analytics team stores clickstream events in Cloud Storage as newline-delimited JSON files. They want to query this data with BigQuery using SQL without loading it into native BigQuery storage, and they want the table definition to refresh automatically when new files land in the bucket. What should you configure?

Trap 1: Create a Dataplex asset over the bucket and query it with the…

Dataplex assets and zones are used for organizing, discovering, and governing data; Dataplex does not expose a general-purpose SQL endpoint to query raw Cloud Storage objects the way BigQuery does. While Dataplex can catalog the data, the query engine that runs standard SQL over those files is BigQuery. This choice misstates the capability of Dataplex and does not satisfy the query requirement.

Trap 2: Mount the bucket with Cloud Storage FUSE on a Dataproc cluster and…

Cloud Storage FUSE and Dataproc plus Spark SQL can query files, but this introduces a persistent cluster and a different SQL dialect rather than BigQuery SQL. It does not produce a BigQuery table that refreshes as new files arrive, and it adds operational cost and management overhead. The requirement specifically calls for BigQuery SQL with an automatically refreshed table definition, which this approach does not deliver.

Trap 3: Load the JSON files into a native BigQuery table using a recurring…

Loading into native storage would copy data into BigQuery-managed storage, which contradicts the requirement to query the data in place without loading it. A scheduled Composer job also introduces orchestration overhead and latency, and it would re-load or append records rather than reflect new files automatically. It solves a different problem than querying externally and does not provide automatic metadata refresh of the source layout.

A
Create a BigQuery external table over the Cloud Storage bucket and enable automatic metadata refresh.
A BigQuery external table lets you query data that lives in Cloud Storage directly with SQL, so no ingestion into BigQuery native storage is needed. Enabling automatic metadata refresh makes BigQuery detect new files and update the table's partition and file metadata on its own, which is exactly the behavior requested. The external table definition stays decoupled from the physical data, and the refresh interval is governed by the max_staleness setting.
B
Create a Dataplex asset over the bucket and query it with the Dataplex SQL endpoint.
Why it fails: Dataplex assets and zones are used for organizing, discovering, and governing data; Dataplex does not expose a general-purpose SQL endpoint to query raw Cloud Storage objects the way BigQuery does. While Dataplex can catalog the data, the query engine that runs standard SQL over those files is BigQuery. This choice misstates the capability of Dataplex and does not satisfy the query requirement.
C
Mount the bucket with Cloud Storage FUSE on a Dataproc cluster and query it with Spark SQL.
Why it fails: Cloud Storage FUSE and Dataproc plus Spark SQL can query files, but this introduces a persistent cluster and a different SQL dialect rather than BigQuery SQL. It does not produce a BigQuery table that refreshes as new files arrive, and it adds operational cost and management overhead. The requirement specifically calls for BigQuery SQL with an automatically refreshed table definition, which this approach does not deliver.
D
Load the JSON files into a native BigQuery table using a recurring Cloud Composer job.
Why it fails: Loading into native storage would copy data into BigQuery-managed storage, which contradicts the requirement to query the data in place without loading it. A scheduled Composer job also introduces orchestration overhead and latency, and it would re-load or append records rather than reflect new files automatically. It solves a different problem than querying externally and does not provide automatic metadata refresh of the source layout.

Question 4easymultiple choice

A data analyst is working in BigQuery and needs to create a visualization that shows the relationship between advertising spend and revenue for each day over the past month. The data is in a table with columns `date`, `ad_spend`, and `revenue`. They want to quickly identify if there is a correlation. Which type of chart should they use in Looker Studio?

Trap 1: Time series chart

A time series chart displays a metric over time, typically with date on the x-axis and one or more metrics on the y-axis. It is excellent for showing trends, but it does not directly show the correlation between two metrics because it plots them against time, not against each other.

Trap 2: Pie chart

A pie chart shows parts of a whole as slices, which is useful for proportional breakdowns of a single categorical dimension. It cannot display two numerical metrics simultaneously to show correlation, and using it for this purpose would be misleading and ineffective.

Trap 3: Bar chart

A bar chart compares categorical data or shows a metric across categories. It is not suitable for showing the relationship between two continuous variables like ad spend and revenue. While it can display both metrics side by side, it does not reveal correlation patterns.

A
Time series chart
Why it fails: A time series chart displays a metric over time, typically with date on the x-axis and one or more metrics on the y-axis. It is excellent for showing trends, but it does not directly show the correlation between two metrics because it plots them against time, not against each other.
B
Scatter chart
A scatter chart plots individual data points using one metric on the x-axis and another on the y-axis, making it ideal for visualizing the relationship and potential correlation between two numerical variables like ad spend and revenue. Each point represents a day, so patterns and outliers are easily seen.
C
Pie chart
Why it fails: A pie chart shows parts of a whole as slices, which is useful for proportional breakdowns of a single categorical dimension. It cannot display two numerical metrics simultaneously to show correlation, and using it for this purpose would be misleading and ineffective.
D
Bar chart
Why it fails: A bar chart compares categorical data or shows a metric across categories. It is not suitable for showing the relationship between two continuous variables like ad spend and revenue. While it can display both metrics side by side, it does not reveal correlation patterns.

Question 5easymultiple choice

You need to load a large CSV file from Cloud Storage into BigQuery. The file has a header row and uses comma delimiters, but the data contains commas within quoted fields. Which BigQuery load option should you use to correctly parse the file?

Trap 1: Set the field delimiter to a pipe character (|)

Changing the delimiter to a pipe would not address the issue of commas within quoted fields; it would actually break parsing if the file uses commas as delimiters. The file already uses commas, and the problem is the embedded commas inside quotes, which BigQuery handles by default with proper CSV parsing.

Trap 2: Set the Quote character to a single quote (')

The file likely uses double quotes for quoting, not single quotes. Changing the quote character to a single quote would cause BigQuery to misinterpret the double quotes, leading to parsing errors. The default double quote is correct for standard CSV.

Trap 3: Enable the AllowQuotedNewlines option

AllowQuotedNewlines allows newlines within quoted fields, not commas. This option is relevant when fields contain line breaks, which is not the case here. Enabling it unnecessarily could impact performance and is not the correct solution for embedded commas.

A
Use the default CSV load with the appropriate schema and let BigQuery handle quoted fields
BigQuery's CSV loader automatically handles quoted fields containing commas when the schema is provided or autodetected. By default, it respects double quotes as field enclosures, so embedded commas within quotes are parsed correctly without additional options.
B
Set the field delimiter to a pipe character (|)
Why it fails: Changing the delimiter to a pipe would not address the issue of commas within quoted fields; it would actually break parsing if the file uses commas as delimiters. The file already uses commas, and the problem is the embedded commas inside quotes, which BigQuery handles by default with proper CSV parsing.
C
Set the Quote character to a single quote (')
Why it fails: The file likely uses double quotes for quoting, not single quotes. Changing the quote character to a single quote would cause BigQuery to misinterpret the double quotes, leading to parsing errors. The default double quote is correct for standard CSV.
D
Enable the AllowQuotedNewlines option
Why it fails: AllowQuotedNewlines allows newlines within quoted fields, not commas. This option is relevant when fields contain line breaks, which is not the case here. Enabling it unnecessarily could impact performance and is not the correct solution for embedded commas.

Question 6easymultiple choice

You need to create a visualization in Looker Studio that shows the proportion of total sales by product category. Which chart type is most appropriate?

Trap 1: Scorecard

A scorecard displays a single metric value, often with a comparison to a target or previous period. It cannot show multiple categories or their proportions. Using a scorecard would only show total sales, not the breakdown by product category, so it fails to meet the requirement.

Trap 2: Time series chart

A time series chart is used to display changes over time, with a date dimension on the x-axis. It is not suitable for showing proportions of a whole at a single point in time. While it could show sales trends by category, it does not convey the proportion of total sales effectively.

Trap 3: Geo chart

A geo chart visualizes data on a map, typically using geographic dimensions like country or region. It is not designed to show proportions of sales by category. Although it can display sales by location, it does not address the need to compare category shares of total sales.

A
Pie chart
A pie chart displays data as slices of a whole, making it ideal for showing proportions or percentages of a total. With product category as the dimension and sales as the metric, each slice represents a category's share of total sales. This directly fulfills the requirement to show proportion.
B
Scorecard
Why it fails: A scorecard displays a single metric value, often with a comparison to a target or previous period. It cannot show multiple categories or their proportions. Using a scorecard would only show total sales, not the breakdown by product category, so it fails to meet the requirement.
C
Time series chart
Why it fails: A time series chart is used to display changes over time, with a date dimension on the x-axis. It is not suitable for showing proportions of a whole at a single point in time. While it could show sales trends by category, it does not convey the proportion of total sales effectively.
D
Geo chart
Why it fails: A geo chart visualizes data on a map, typically using geographic dimensions like country or region. It is not designed to show proportions of sales by category. Although it can display sales by location, it does not address the need to compare category shares of total sales.