CCNA Pde Analysis Ml Questions

AutoML Tables allows setting class weights to address imbalance; it will adjust the loss function accordingly.

Online serving with Bigtable satisfies the sub-10ms latency constraint because Bigtable provides low-latency row-key lookups for feature values at prediction time. Vertex AI Feature Store uses Bigtable as its online serving store, retrieving the latest feature values for each entity directly, rather than scanning historical data as batch serving would.

AutoML Vision trains image classification models from labelled datasets using Google's managed infrastructure, requiring no custom code or algorithm design. It accepts the 10,000 labelled images and handles training automatically, satisfying the constraint of building a defect-detection model quickly without training from scratch.

Partition pruning is driven by filters on the partitioning column. Without an `event_date` predicate, BigQuery must scan all partitions, even though clustering on `user_id` helps within each partition. Adding a date filter restricts the scan to relevant partitions, dramatically reducing bytes processed and cost.

BI Engine provides an in-memory analysis layer that caches frequently accessed data, accelerating Looker Studio queries without schema changes or materialised views. It satisfies the stem's constraint of improving dashboard performance while leaving the underlying BigQuery tables untouched, and integrates natively with Looker Studio.

The `bq load` command with `--source_format=CSV` and `--skip_leading_rows=1` directly loads the CSV, skipping the header. This is the fastest method for bulk loading without transformation, leveraging BigQuery's native load job which can parallelize reading from Cloud Storage.

BigQuery Omni supports BigQuery ML, allowing you to train and run models on cross-cloud data.

RUNNING_SUM computes cumulative sums.

RANK() assigns positions ordered by the window's ORDER BY, and tied values receive the same rank, leaving gaps before the next rank. It satisfies the explicit gap requirement, unlike DENSE_RANK(), which numbers ties consecutively without gaps.

Vertex AI Feature Store provides a centralised repository that stores feature values and serves them online at low latency or in bulk for training, keeping features consistent between training and serving. This satisfies the need to store and serve features at scale.

Dataplex data quality rules support a regex rule type, letting you define a pattern that each value in the email column must match. This satisfies the requirement to validate email format without writing custom SQL assertions.

ARIMA_PLUS automatically handles seasonality, trend and holiday effects, and performs automatic model selection and hyperparameter tuning within BigQuery ML. This satisfies the minimal manual tuning constraint while producing accurate 90-day daily sales forecasts from historical data.

Dataflow provides serverless, autoscaling Apache Beam processing with exactly-once semantics, satisfying the stem's real-time aggregation requirement. Its native Pub/Sub source and BigQuery sink connectors handle windowed streaming aggregations without managing compute infrastructure, unlike Dataproc's cluster-based Spark or BigQuery's load-only ingestion.

BI Engine acceleration requires a reservation assigned to the project; the 5 TB dataset and dozens of concurrent dashboard queries exceed the free per-project capacity, so a reservation guarantees dedicated in-memory cache for Looker Studio.

APPROX_COUNT_DISTINCT uses HyperLogLog++ sketches to estimate cardinality within a small, bounded memory footprint, returning results in seconds rather than the minutes an exact COUNT(DISTINCT) would need on a large table. This directly satisfies the stem's low-latency requirement for estimating distinct website visitors.

LAG() is a navigation window function that returns a column's value from a preceding row within the same partition, evaluated by the ORDER BY timestamp. It satisfies the stem's requirement to retrieve the previous row's value, unlike LEAD(), which looks forward.

BigQuery BI Engine reserves memory for sub-second query acceleration and caches frequently accessed data, so repeated Looker queries against the BigQuery connection return in under a second. It satisfies the stated requirement of in-memory caching rather than relying on disk-based results cache.

BigQuery Omni runs BigQuery SQL directly against data held in AWS S3 through Anthos-hosted compute, returning results without extracting or copying the data into Google Cloud. This satisfies the constraint that the S3 data must remain in place while a single query spans both sources.

w_reg controls L2 regularization strength on the learned factors. Raising it from 0.1 to 0.5 penalises large factor values more heavily, constraining model complexity and reducing overfitting on the sparse user-item interaction data described in the stem.

The `bq load` command can load large files from Cloud Storage into BigQuery. Specifying `--source_format=CSV` and `--skip_leading_rows=1` handles the header row. This method is cost-effective (no data egress) and leverages BigQuery's parallel load capabilities for fast ingestion.

Vertex AI Model Registry provides a centralised repository for managing model versions, storing artefacts, and tracking lineage. It satisfies the requirement to register models by assigning each trained model a versioned entry with metadata, enabling deployment and monitoring. Combined with Datasets and Experiments, it completes the MLOps workflow the engineer needs.

BigQuery's load job expects numeric columns to contain plain numbers without currency symbols or thousands separators. The safest approach is to clean the data before loading, either by editing the CSV or using a tool like Cloud Dataflow or Dataprep to transform the values. Alternatively, you can load as STRING and then transform in BigQuery, but preprocessing is often simpler. This ensures the load succeeds without errors.

Vertex AI Prediction deploys the SavedModel to an endpoint serving online predictions with low latency, satisfying the stem's requirement. Unlike batch prediction, it provisions a persistent endpoint for real-time inference, and unlike custom training, it consumes the already-trained artefact directly without retraining.

Access filters in LookML can be used to restrict queries to a specific partition range, reducing full table scans.

Partitioning by event_time allows BigQuery to prune partitions when queries filter on that column, dramatically reducing bytes scanned. Requiring a partition filter prevents accidental full-table scans. This is the primary cost-control mechanism for large time-series tables and directly addresses the frequent event_time filters.

ARIMA_PLUS handles seasonality, trends and holiday effects automatically, which suits five years of daily sales data containing weekly and yearly patterns. It also supports forecasting directly in BigQuery ML without exporting data, satisfying the requirement to build a time-series forecast for inventory demand.

The Storage Write API provides exactly-once delivery semantics when you use a stream name and specify offsets for each record. This ensures that retried records are not duplicated. It is designed for high-throughput streaming and supports deduplication natively, making it the correct choice for preventing duplicates on retries.

A sequential split keeps all training observations earlier in time than evaluation observations, preserving temporal order. This satisfies the forecasting constraint, since random splitting would leak future information into training and produce misleadingly optimistic evaluation metrics.

BigQuery external tables allow querying data directly from Cloud Storage without loading, providing minimal setup and no storage cost in BigQuery. They support standard SQL and can be joined with native BigQuery tables. This matches the requirement for ad-hoc queries with minimal cost and setup.

A repeated RECORD allows storing multiple emails per user while preserving the nested structure. Queries can use UNNEST to flatten the array and filter by email type, and dot notation to access fields. This denormalized approach is efficient in BigQuery, avoiding joins and enabling fast retrieval of all emails for a user.

BigQuery natively supports nested and repeated fields via RECORD and REPEATED types. When JSON is loaded with such a schema, nested objects become RECORDs and arrays become REPEATED fields. Analysts can then use dot notation to access nested fields and UNNEST to flatten arrays in queries, meeting the requirement without transformation.

Vertex AI Workbench provides managed JupyterLab notebook instances with pre-installed deep learning frameworks such as TensorFlow and PyTorch, plus optional GPU accelerators. This satisfies the requirement for a managed notebook environment ready for custom TensorFlow training without manual framework installation.

APPROX_COUNT_DISTINCT uses HyperLogLog++ sketches to estimate cardinality, scanning far less data than COUNT(DISTINCT)'s exact deduplication. This satisfies the stem's demand for faster approximate distinct counts on large columns, trading a small, bounded error rate for substantially reduced query cost and latency.

Vertex AI Workbench provides managed Jupyter notebook instances with native integration into Vertex AI training, letting you author and launch custom TensorFlow training jobs from the same environment. It satisfies the managed notebook constraint without provisioning or patching your own compute.

An access filter applies a user attribute to a dimension or field, restricting each user's query results to rows matching their attribute value. This delivers dynamic row-level security in Looker without duplicating models per region.

The view object maps a LookML view to an underlying BigQuery table and declares its dimensions and measures, including total revenue. It satisfies the requirement to define the table structure and the revenue measure in one reusable object.

Consolidating small files into larger ones reduces the number of read operations and metadata overhead, which is the primary cause of slowness. Dataflow can then read each large file efficiently, improving throughput and lowering cost. This is the recommended pattern for many small files.

The BigQuery Lineage API exposes column- and table-level lineage captured from query jobs, tracing how each table is derived from upstream tables. This directly satisfies the requirement to track derivation lineage, unlike Dataplex or Data Catalog, which handle discovery and governance metadata.

This query groups by `customer_id` and uses SUM to aggregate total sales and MAX to find the most recent order date. It correctly produces one row per customer with the desired columns. The GROUP BY clause ensures aggregation per customer, and the aggregate functions operate on the appropriate columns. This is the standard and efficient way to compute such per-customer metrics in BigQuery.

The Storage Write API supports exactly-once semantics when using application-created streams. By assigning a unique offset to each record within a stream, BigQuery can deduplicate records on write. This ensures that even if the client retries, duplicates are not inserted. This is the recommended approach for streaming ingestion with deduplication requirements. It provides low-latency, exactly-once delivery without additional batch processing.

Clustering sorts the data by the specified column and stores it in blocks, allowing BigQuery to prune unnecessary blocks when a query filters on that column. This reduces the bytes scanned and improves performance for queries that filter on `customer_id`. Since the table is not partitioned, clustering is the appropriate technique to achieve the goal without altering the partitioning scheme.

The LAG window function accesses data from a previous row in the same result set without the need for a self-join. Partitioning by `customer_id` ensures that the previous order is for the same customer, and ordering by `order_date` defines the sequence. LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) returns the amount of the previous order, or NULL if there is no previous row. This is the most efficient and readable solution.

Column-level security with data masking attaches policy tags to sensitive PII columns, so BigQuery automatically redacts or hashes those values in query results for users lacking the required role, satisfying the masking requirement without rewriting queries.

Custom container images let the notebook instance run with bespoke dependencies, frameworks or system packages beyond the default image. This satisfies reproducibility and version-pinning needs during exploratory data analysis when specific library builds are required.

RANK() is a navigation function that assigns a rank to each row within a partition, ordered by a specified expression. Partitioning by region and ordering by sales yields per-region sales rankings, satisfying the ranking half of the requirement; a separate function handles consecutive-month differences.

Setting skip_leading_rows to 1 tells BigQuery to ignore the first row (the header). Enabling autodetect allows BigQuery to infer the schema from the data. This combination is ideal for CSV files with headers when you don't want to manually define the schema. It is a common practice for loading external data efficiently.

A LookML parameter with Liquid templating injects a user-supplied value into the generated SQL at query time, letting business users enter an arbitrary date range not bound to any existing dimension. This satisfies the stem's user-input-at-query-time constraint.

A weight column lets AutoML Tables apply per-row loss multipliers, so positive examples at 5% prevalence can contribute proportionally more during training. This directly addresses the stem's imbalance constraint without resampling, preserving all 95% negative rows while preventing the model from defaulting to the majority class.

BI Engine is an in-memory analysis layer that caches BigQuery data and accelerates dashboard queries, requiring no changes to the underlying tables or materialised views. Enabling it for the project directly reduces the several-second load times observed in Looker Studio.

Allowed lateness tells Dataflow to keep window state for 10 minutes after the watermark passes, so late events are included. Using early triggers provides low-latency partial results, and late triggers update them. This satisfies both correctness for late data and low latency for on-time events.

This statement creates a new table partitioned by day on the 'timestamp' column and populates it with data from the last 7 days. The PARTITION BY DATE(timestamp) clause ensures that the table is partitioned by day, which improves query performance and reduces cost. The WHERE clause filters the data appropriately. This is the correct syntax for creating a partitioned table from a query.

BigQuery Omni supports multi-cloud analytics without data movement.

Deploying to a Vertex AI endpoint serves AutoML Tables models for online prediction with the low-latency response the stem requires. Batch prediction cannot meet sub-100 ms per-request latency, and BigQuery ML export does not provide managed online serving.

This window function correctly partitions by customer_id, orders by order_date, and defines the frame from the start of the partition to the current row, producing a running total per customer. The ROWS clause ensures cumulative sum includes all previous rows and the current row, which is exactly what is needed for each order.

BigQuery Omni runs BigQuery queries directly against AWS S3 and Azure Blob Storage via Anthos-hosted compute, returning results without extracting or duplicating the data. This satisfies the stem's requirement to join Google Cloud and S3 data while querying S3 in place, avoiding egress and movement costs.

PIVOT rotates rows into columns, directly satisfying the requirement to turn product categories into separate columns. BigQuery supports the PIVOT operator, which aggregates values using an aggregate function alongside a FOR clause naming the category column and an IN list of the values to become column headers.

Parsing the JSON and converting the ISO 8601 string to a native timestamp type ensures that BigQuery receives a proper TIMESTAMP value. Dataflow's BigQueryIO can then write it correctly. This approach handles time zones and formatting consistently, and is the standard way to transform string timestamps in a pipeline.

Dataplex Data Quality enforces row-level and column-level rules directly on BigQuery tables, supporting not-null checks and regex pattern matching through its built-in rule types. This satisfies the stem's requirement to validate the 'email' column's nullability and format, with results surfaced as data quality scores and scan reports.

`CREATE MODEL` with `model_type='tensorflow'` and a `gs://` `model_path` is the supported import path for TensorFlow SavedModels into BigQuery ML, satisfying the stem's requirement to load a pre-trained model from Cloud Storage for batch prediction via `ML.PREDICT`.

RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW defines a dynamic window based on the timestamp value, so each row's average includes all readings from the same sensor within the preceding 7 days. This is the correct approach for time-based rolling aggregates in BigQuery and avoids self-joins or manual date arithmetic.

Splitting chronologically by timestamp keeps all training observations earlier than every test observation, so the model never learns from future values. Random splitting would leak future information backwards into training, violating the no-leakage constraint for forecasting time-series data.

LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) retrieves the preceding row's timestamp within each user's partition, satisfying the per-user sequential comparison the stem requires. Subtracting it from the current event_time yields the seconds elapsed since that user's previous event, without collapsing rows as aggregation would.

PIVOT aggregates revenue with SUM, grouping by the remaining column (year) and spreading quarter values into Q1–Q4 columns, exactly matching the required year-per-row, quarter-per-column report. The IN list supplies the literal quarter values to become column headers, so no explicit GROUP BY is needed.

JSON_EXTRACT_SCALAR extracts a scalar value from a JSON string using a JSONPath expression. It returns the value as a STRING, which is suitable for extracting user_id. This is the correct function for this scenario because it handles JSON parsing and returns a scalar, not a JSON object.

To add a column that extracts the month from a date, you use the EXTRACT function: `EXTRACT(MONTH FROM sale_date) AS sale_month`. This returns an integer representing the month. You can include this expression in the SELECT list of your query, and when you create a table from the query, the new column will be included. This is the correct way to derive the month from a date column.

Correct. Dataplex Lineage provides comprehensive data lineage for BigQuery and other Google Cloud data services.

Providing an explicit schema ensures BigQuery interprets the date column as DATE regardless of the string format. This is the most reliable method, especially when the format matches the expected DATE literal. It avoids ambiguity and guarantees correct type recognition, which is essential for subsequent date functions.

High bias means the model underfits because its hypothesis space is too simple for the underlying relationship. Adding predictive features such as bedrooms and square footage increases model capacity, satisfying the stem's requirement to reduce bias rather than variance.

Sliding-window validation trains on an expanding or rolling past window and validates on the immediately following period, so every fold respects chronological order. This directly prevents future leakage during hyperparameter tuning, satisfying the stem's requirement to split time-series data without exposing the model to later observations.

ARIMA_PLUS decomposes the time series into trend, seasonal and holiday components, automatically detecting weekly and yearly seasonality across the two years of history. Setting holiday_region incorporates retail holiday effects into the forecast, directly satisfying the requirement to account for seasonality and holidays over the eight-week horizon.

Data blending lets Looker Studio combine two BigQuery data sources by matching a shared dimension, producing a joined result inside the report. This satisfies the requirement to join sales transactions with customer demographics without writing SQL or pre-joining the tables in BigQuery.

Dataplex provides a unified data catalogue with automatic metadata discovery and lineage tracking across BigQuery, Cloud Storage and other sources, satisfying the requirement for automatic lineage capture. Its built-in Data Catalog integration and lineage API record transformations without manual annotation, which is precisely the unified, cross-source lineage capability the scenario demands.

BigQuery uses partition pruning when the query filters on the partition column, and cluster pruning when it filters on the clustering columns. If analysts are not filtering on customer_id, or if the filter is not applied correctly (e.g., using a function on the column), clustering benefits are lost. By ensuring queries filter directly on customer_id and the partition column, BigQuery can scan only relevant partitions and blocks, reducing bytes scanned and improving performance. This is the correct approach to optimize the query.

APPROX_QUANTILES returns approximate quantile boundaries from a sample, satisfying the stem's demand for faster results on large datasets. It computes quantiles in a single pass with bounded memory, unlike exact PERCENTILE_CONT which requires full sorting. This makes it a valid Approximate Aggregation Function alongside APPROX_COUNT_DISTINCT.

Allows you to build and run end-to-end ML pipelines, including preprocessing.