CCNA Data Analysis And Presentation Questions

Time travel reads a consistent version of the table as of a past timestamp, so concurrent streaming inserts cannot skew results mid-query. This satisfies the stem's consistency constraint on a constantly updated table, unlike a plain query that may observe rows arriving during execution.

This expression calculates the profit margin by subtracting total cost from total revenue and dividing by total revenue. Using SUM aggregates the values correctly before the division, ensuring the result is a percentage. This is the correct way to compute a ratio of aggregates in Looker Studio.

Clustering on `event_type` sorts data within each partition by that column. When queries filter on `event_type` and the partition column, BigQuery can skip blocks that do not match the cluster value, reducing bytes scanned. This directly addresses the high cost while preserving the existing partitioning strategy.

A date range control enables users to adjust the time period for the data shown. This is a key interactive feature that allows sales team members to view performance over different periods, such as last week, last month, or a custom range. The charts automatically refresh based on the selected dates.

A Looker Explore presents a curated view of a model, letting users pivot, filter and visualise data without SQL. This satisfies the stem's requirement of exploring and visualising data, distinguishing it from dashboards, which display pre-built, fixed visualisations.

Enabling 'Require partition filter' forces every query against the table to include a partition filter, rejecting unfiltered scans. This prevents full-table scans across the petabyte dataset, directly enforcing the cost-optimisation constraint by limiting bytes read.

This approach aggregates revenue by month and category, then uses RANK() partitioned by month to rank categories within each month. Filtering for ranks 1 through 5 yields the top 5 per month. It is efficient and standard in BigQuery for top-N per group analysis, and correctly handles ties if needed.

BigQuery ML lets you create, train and evaluate machine learning models directly in BigQuery using SQL statements such as CREATE MODEL, keeping data in place. This satisfies the described purpose of running machine learning through SQL rather than external frameworks.

Fan-out occurs when a one-to-many join multiplies fact rows, because the join key lacks a properly defined primary key on the dimension side. Looker then aggregates inflated counts, producing the duplicate data observed. Defining the primary key correctly in LookML prevents this row multiplication, satisfying the stem's duplicate-data constraint.

A histogram bins a numeric column into ranges and plots frequency per bin, revealing the value distribution's shape, spread and skew. This directly satisfies the stem's requirement to see how values in a column are distributed.

Declaring a primary_key marks the dimension as unique, so Looker treats that view as the "one" side of a join and applies symmetric aggregates when aggregating measures across the relationship. This prevents fan-out duplication of totals, satisfying the stem's requirement to describe the primary_key's impact on join behaviour.

INFORMATION_SCHEMA.JOBS exposes per-job metadata — bytes processed, slot milliseconds, user and statement type — for completed queries in a project or region. Querying it lets you rank jobs by cost and pinpoint expensive patterns, satisfying the requirement to monitor usage and identify costly queries.

Vertex AI Workbench provides managed JupyterLab notebook instances integrated with Google Cloud, handling provisioning, scaling and security. It satisfies the managed-environment constraint because Google operates the underlying compute rather than you maintaining your own notebook server.

Filtering directly on the partition column lets BigQuery prune partitions and read only the 30 days required, which is the single biggest lever on scan cost for partitioned tables. Selecting just the date and user identifier keeps the scanned columns narrow. Together these choices minimize bytes billed while still producing the daily active user counts the stakeholder requested.

BigQuery spills to disk when a query's intermediate data exceeds available memory per slot. Inefficient JOINs or GROUP BY operations producing large shuffles are the typical cause, forcing temporary data onto disk and slowing execution.

A window function such as AVG over a 7-day range computes the rolling average inside BigQuery, and a view or scheduled query exposes that result to Looker Studio. This places the calculation in the database as requested and keeps the time series current as new events arrive, which is exactly what the analyst asked for.

A custom query connector executes your own SQL against the underlying database, returning the result set as a data source. This directly satisfies the stem's requirement for an arbitrary SQL query, unlike fixed-schema connectors such as BigQuery tables or Google Sheets, which expose predefined tables or ranges rather than free-form SQL.

A filter control allows viewers to select values from a dimension, and the selection applies to all charts on the page that use that data source. Using the region dimension as the control field enables multi-select and updates all charts, meeting the requirement exactly. This is the standard way to add interactive filtering.

Looker Studio is Google Cloud's native BI and dashboarding service, connecting directly to BigQuery datasets as a data source. This satisfies the stem's requirement for a valid way to visualise BigQuery data within the Google Cloud ecosystem.

BI Engine is an in-memory analysis service that accelerates queries on BigQuery data. By reserving memory for the dataset, Looker Studio queries can be served from memory, drastically reducing latency for interactive charts and filters. This directly addresses slow report performance for large datasets.

Centralised LookML modelling enforces governance by defining dimensions, measures and business logic once in a Git-versioned repository, so every report and dashboard inherits identical definitions. This satisfies the stem's requirement for consistent, auditable data definitions across the organisation, eliminating duplicated logic and drift between teams.

Query Plan explanation exposes the execution graph, showing per-stage timing, shuffle and spill volumes, and row counts, so you can pinpoint the exact stage causing slowness. It satisfies the stem's requirement for a valid BigQuery performance analysis method, since the plan is generated from actual execution statistics rather than estimates.

A drop-down filter adds interactivity by letting viewers select dimension values that immediately constrain every chart bound to that data source. This satisfies the stem's requirement for interactive report elements, since filtering dynamically re-queries the underlying data rather than displaying a static, pre-rendered view.

Connected Sheets links a Google Sheets tab to a BigQuery data source and supports scheduled refreshes, so the data updates each morning without manual intervention. This directly meets the requirement that non-technical staff see current figures automatically, and it retains the live connection so the numbers stay aligned with the BigQuery table.

Prefixing a shell command with an exclamation mark executes it in the notebook's underlying shell rather than as Python, so !ls lists the current working directory's files. This is the standard Jupyter-based mechanism in Vertex AI Workbench.

BigQuery dry runs parse and validate the SQL, resolve table and column references, and return an estimate of bytes processed without actually executing the query or incurring scan charges. This lets the analyst catch syntax or reference errors and judge cost before committing to a real run. It directly matches the goal of validation plus cost estimation on production tables.

Replacing the cross-join with a specific JOIN condition eliminates the Cartesian product, reducing the intermediate result from 10 billion squared rows to only matching pairs. This directly addresses the timeout constraint, since BigQuery's distributed shuffle cannot materialise such an explosive intermediate dataset within the query's execution limits.

A time series chart is designed to display data over time, with the date field as the dimension. Using `order_date` as the dimension and a record count metric (e.g., COUNT(*)) will show the number of orders per day. This directly meets the requirement of total orders per day for the last 30 days.

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.

REGEXP_EXTRACT_ALL returns an array of all non-overlapping matches for a pattern, so specifying a pattern like `(fast|slow|broken)` captures each occurrence. Unnesting the array turns each match into a row, and grouping by the extracted word yields the count per word across all feedback rows. This is a standard BigQuery pattern for word-frequency analysis in a single query.

Looker Studio supports reusable components that can be placed on multiple pages while each instance retains page-level filter controls. This keeps a single chart definition linked to the BigQuery data source, so the revenue logic lives in one place. Each page's region filter narrows that page's instance independently, satisfying the requirement without duplicating the data source or the view.

This filter computes the first day of the previous month and the first day of the current month, giving an exact previous-calendar-month range. Both bounds are constant expressions on the partition column, so BigQuery prunes to only the relevant date partitions, minimizing bytes processed. It also correctly excludes the current month's data, which matches the monthly refresh requirement.

Scaling up the machine type allocates more vCPUs and memory to the Workbench instance, accelerating notebook build, kernel startup, and package installation. This directly reduces build time when the bottleneck is compute or memory resources.

Data Preview reads a limited number of rows from the table so analysts can inspect schema and sample values without running a query or incurring scan costs. This satisfies the stem by showing representative table data rather than full results or metadata.

Clustering physically sorts data by customer_id, so filters on that column scan only relevant blocks rather than the whole table. This directly satisfies the stem's requirement to improve performance for frequent customer_id filtering, and unlike partitioning, clustering suits high-cardinality columns where per-value partitions would be impractical.

Setting a default date range of 'Last 30 days' on a date dimension makes the report automatically filter data relative to the current date each time it loads. Since the underlying table is updated daily, the report will always reflect the most recent 30 days without any manual changes. This is the standard, dynamic solution in Looker Studio.

Drill-down functionality allows users to click on a data point (e.g., a country) to see more detailed data (e.g., cities within that country). By configuring a geo chart with a hierarchy of country and city, and enabling drill-down, users can navigate from a high-level view to a detailed view. This directly satisfies the drill-down requirement.

ML.EVALUATE is the correct function to assess the performance of a trained model. For linear regression, it returns metrics like R-squared, mean absolute error, and mean squared error. It compares predicted values against actual values in a dataset, providing a quantitative evaluation of model accuracy.

BI Engine provides an in-memory analysis layer that accelerates BigQuery queries used by dashboards, keeping interactive filtering responsive for executives. Combined with a Looker Studio report over BigQuery, it satisfies the performance side of the requirement while preserving the direct query path to BigQuery data.

A drop-down list control lets viewers select one or more values from a dimension, such as region, to filter the data displayed in charts. This meets the requirement to filter by region dynamically. It is a common interactive control in Looker Studio and can be linked to multiple charts.

Granting the partner's service account BigQuery Data Viewer at dataset level satisfies the cross-project least-privilege constraint. IAM policies accept identities from any Google Cloud project, so no project-level or organisation-wide binding is needed. Dataset-scoped access limits exposure to that single dataset, unlike broader roles that would grant visibility across the project.

TIMESTAMP_TRUNC truncates a timestamp to the specified granularity, here HOUR, discarding finer components. It operates natively on the timestamp type without string conversion, making it the most efficient way to bucket millisecond timestamps for hourly aggregation.

Filters added at the data source level are inherited by every chart that uses that data source, which guarantees the amount > 0 condition applies report-wide. This satisfies the requirement of filtering all viewers' views without altering the BigQuery table, and it avoids the maintenance burden of duplicating the same filter on each 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.

A date range control lets users select a specific time period, which then filters all charts on the page that use a date dimension. This provides the required interactivity for filtering sales data by date range, allowing users to focus on specific periods and analyze trends over time.

A Single Value visualization computes one aggregate—here the average—over the dashboard's underlying query, satisfying the requirement for a single dashboard-wide metric. Unlike table or chart tiles, it renders exactly one number without dimensional breakdown, directly meeting the stem's constraint of displaying the average across the entire dashboard.

The `fields` parameter in an Explore definition explicitly whitelists which fields appear in that Explore's menu, hiding all others. If a specific set of fields is missing, this parameter is restricting visibility, satisfying the stem's constraint that only certain fields are absent rather than the whole Explore.

RANK() OVER (PARTITION BY region ORDER BY sales DESC) assigns a rank within each region independently, letting you filter to ranks 1-5 per region. The PARTITION BY region clause is the mechanism that satisfies the per-region top-5 requirement.

ML.EXPLAIN_PREDICT produces predictions together with explanations that attribute each prediction to the contributing input features, using methods such as Shapley values. This lets the team see which customer attributes most influenced churn predictions. It is designed for exactly this interpretability need and works with supported model types trained in BigQuery ML.

This query correctly filters the last 30 days using DATE_SUB, groups by product_id, sums revenue, applies a HAVING clause to filter aggregated totals over $10,000, and orders by total revenue descending. The alias total_revenue is valid in HAVING and ORDER BY in BigQuery. It matches all requirements precisely.

Scheduled Reports run a Look or dashboard on a defined cadence and deliver the results by email to specified recipients, removing manual delivery. This satisfies the stem's stated benefit of automatically emailing reports to stakeholders.

Dot notation traverses the nested RECORD structure directly, resolving the 'city' field within the 'address' record without unnesting. BigQuery supports this path syntax for accessing nested fields, satisfying the requirement to reach a field inside a record.

A Looker Explore queries BigQuery directly, so the line chart always reflects the latest data without extraction or scheduling. Time-series line charts suit daily sales trends, and Looker's persistent derived tables or caching handle the six-hourly refresh cadence.

BI Engine is an in-memory analysis service that accelerates SQL queries, including those from Looker Studio. By creating a BI Engine reservation and assigning it to the project containing the dataset, queries become faster and more concurrent, reducing load times and timeouts. This directly addresses performance without altering data.

Rows written via the streaming API initially reside in a buffer before being committed to managed storage, and operations such as certain exports and some query paths may not see those rows immediately. The dashboard and the direct BigQuery report can therefore be evaluated against marginally different points in time, producing small total differences that resolve on their own once the buffer flushes.

A persistent disk attached to the Workbench instance stores data independently of the instance's running state, so notebooks survive stopping and restarting. Saving to the boot disk or instance memory would be lost, whereas the attached persistent disk satisfies the durability requirement.

Table Calculations compute values in Looker's query layer using LookML functions such as offset and pivot_offset, deriving year-over-year growth from returned rows. This satisfies the stem's constraint of calculating growth without modifying the underlying SQL.

A scatter plot plots one numerical variable on each axis, exposing correlation, clusters and outliers between them. This directly satisfies the stem's requirement to show the relationship between two numerical variables, unlike categorical or single-measure charts.

Setting `value_format_name` on the measure in the LookML view file applies a named format model, such as `usd`, directly to the measure's definition. This satisfies the stem's requirement to correct the display format of the total revenue measure while preserving its existing summation behaviour.

When a Looker Studio data source is defined by a custom query, the query result is cached and reused for the data source's freshness setting, commonly twelve hours by default. The dashboard therefore shows the cached snapshot rather than re-querying BigQuery each time it loads. Reducing the freshness interval or switching the source to a direct table reference restores more current data.

Clicking the refresh icon triggers a re-run of the dashboard's underlying queries against the database, fetching current data rather than serving cached results. This directly satisfies the stem's requirement to reload data within Looker, since the refresh control is the interface element that forces query re-execution and updates every tile.

Partition pruning only occurs when the query filters on the partitioning column. Without that filter, BigQuery cannot eliminate any partitions, so it reads every partition, resulting in a full table scan and higher bytes billed.

Query History exposes each query's execution duration and generated SQL, letting you pinpoint whether latency stems from complex joins, missing aggregates, or full-table scans before touching dashboard configuration. This directly satisfies the stem's requirement to identify the first diagnostic step for a slow-loading Looker dashboard, since execution metrics reveal the underlying bottleneck.

BigQuery's web UI provides a built-in 'Save results' menu that writes directly to a Google Sheet, avoiding exports to Cloud Storage or the API. This satisfies the requirement for the easiest method, since no external tooling or intermediate file transfer is needed.

Filtering on the partitioned column lets BigQuery prune partitions and scan only matching data, so bytes billed drop sharply from the full 500GB. This directly satisfies the stem's cost-reduction constraint, since BigQuery charges by bytes read, and partition elimination is the cheapest lever available.

A scatter chart plots individual data points on two axes, making it ideal for visualizing the relationship between two metrics. By placing spend on the X-axis and revenue on the Y-axis, each campaign appears as a point. Campaigns with high spend and low revenue will appear in the lower-right area, enabling quick identification of outliers.

Because the table is at line-item grain, counting rows would inflate the denominator whenever an order contains multiple items. Dividing summed revenue by COUNT(DISTINCT order_id) yields the true average per order, and grouping by country produces one value per country. This is the standard way to express a ratio measure in Looker when fan-out would otherwise distort the calculation.

Partitioning the table by the timestamp column (e.g., daily partitions) allows BigQuery to scan only the partitions that satisfy the time filter. This dramatically reduces the amount of data processed, lowering cost and improving performance. For future queries with similar time filters, the partition pruning applies automatically. This is the recommended approach for large time-series datasets in BigQuery.

Vertex AI Workbench instances run as a service account; client libraries and the BigQuery API use its credentials automatically, so no manual key or user login is needed. This satisfies the stem's private-dataset access requirement through IAM roles on that account.

Table calculations operate on the results already returned by the query, so offset() can reference the previous row's revenue value within the month-ordered result set. Dividing the difference by the prior month's revenue yields a growth percentage, and because the calculation lives in the Looker tile it recalculates automatically whenever the dashboard runs with fresh data, requiring no manual monthly edit.

Prefixing a command with ! in a Jupyter notebook cell executes it as a shell command on the instance, so pip installs seaborn into the notebook's Python environment. This satisfies the stem's requirement for installing the library in the Vertex AI Workbench instance.

`count_distinct` aggregates by counting unique values in a dimension, directly satisfying the requirement for distinct customer counts. Unlike `count`, which tallies every row including duplicates, it deduplicates at query time. This makes it the precise measure type for counting distinct customers in a Looker model.

A date range control binds a filter directly to the report canvas, letting viewers adjust the start and end dates at view time without opening the report editor. This satisfies the stem's requirement for dynamic, user-driven date selection, since the control writes the chosen range into the chart queries rather than hard-coding it.

A materialized view pre-computes and stores the aggregated results, so the report queries a much smaller dataset. This significantly reduces query time and cost. Since the view is refreshed automatically, the report remains current. The layout of the report remains the same; only the underlying data source changes to a more efficient one.

A WITH clause defines a named common table expression that BigQuery evaluates once and references throughout the query, avoiding repeated subquery scans. It is the most efficient syntax for a temporary table used later in the same query.