Free Associate Data Practitioner practice test: 30 questions with answers

Sub-minute freshness needs a streaming path, and Pub/Sub with a streaming Dataflow pipeline is the standard managed design for it. Nightly loads and hourly copies cannot meet a seconds-level requirement. The Cloud SQL federated query is the tempting wrong answer, but it reads the operational database, adds load to it, and is not a streaming ingestion pattern.

Standardising to ISO 8601 before the load makes the column consistent and loadable into a DATE type, so every downstream query behaves the same way. Loading as STRING pushes the cleanup onto every analyst and invites inconsistent results. Raising max_bad_records only hides rejected rows, and allow_jagged_rows concerns rows with missing or extra columns, not date formats.

Profiling the null rate and quarantining rows with a missing key keeps the failure visible, lets you count it, and lets the business decide how to treat those sales. An INNER JOIN drops them without a trace, and inventing zero IDs creates false matches. Casting to FLOAT64 does not make a missing key joinable.

A saved cursor allows a restart without re-pulling everything, and keeping raw responses lets you reprocess after a transformation bug without calling the API again. Backoff on rate limits and server errors helps transient failures succeed. Turning off logging hides failures, and silent schema inference lets bad or unexpected data reach the target.

Partitioning the rows by the business key and keeping the most recent one removes repeats even when their ingestion timestamps differ. SELECT DISTINCT including the timestamp keeps every duplicate because each has a different value. Clustering only changes physical layout, and summing amounts double-counts the orders.

An external table reads the Parquet files where they sit, so no second copy is stored in BigQuery. Loading as CSV would misread the binary Parquet format, and streaming row by row is a write path that duplicates data. Copying the bucket adds storage rather than avoiding it.

Event time, not arrival time, decides which reading is latest, so the greatest event timestamp wins. Event-time windowing places late messages in the period they describe. Keeping the most recently received message can keep an older reading that arrived late, and log or message ID order has no relation to when a device reported.

Quarantining an unexpected schema keeps the target stable and makes the change visible to the owner, who can then decide how to adopt it. Automatic addition on a load is risky for uncontrolled changes, though it is valid in some pipelines. Renaming columns breaks existing queries, and recreating the table every night loses history.

A scheduled aggregate table reduces the bytes each refresh scans, and a view restricted to a recent window stops the dashboard from reading old partitions. Connecting to the raw table with no filters maximises scan cost, and the PDF and spreadsheet options do not change the query at all.

When a one-to-many join runs before an aggregate, each order is repeated once per matching discount row, which inflates the sum. Caching or time zone differences would produce a timing gap rather than a consistent overstatement. FLOAT64 causes small rounding differences, not an 18% increase.

A line chart shows change over an ordered time axis, which is what the executive needs. A pie chart compares parts of one whole rather than a trend across 24 periods, and a scatter plot against product identifier has no meaningful ordering. The stacked bar mixes in products the question does not ask about.

Segmenting the metric and comparing against a same-period baseline shows whether the rise is a real change in buying behaviour or just a shift in the mix of customers and channels. A forecasting model does not explain a past change, and publishing before checking invites a wrong story. Removing top-percentile orders without review discards possibly genuine data.

A truncated axis exaggerates small differences because bar length is read against the baseline, so readers overestimate the gap. The bars can still show values below 80, the legend is unrelated to the axis, and the publishing tool does not reject charts for this reason.

Documented definitions and a visible freshness time let executives check what a number means and whether it is current, which builds trust. Extra decimal places imply false precision, hidden filters limit useful checks, and per-second refresh is unnecessary for a budget review and adds load.

The median is the middle value and is not pulled upward by a small number of extreme incomes, so it best represents a typical household here. The mean is the distorted measure the question warns about, the total says nothing about a typical household, and the maximum describes an outlier.

Ingestion-time partitioning is a valid option when the source has no usable date, and filtering on the partition column lets BigQuery skip the other partitions and so scan fewer bytes. A table has a single partitioning column, and partitioned tables can also be clustered. A schema is still required whatever the partitioning.

A policy tag enforces column-level access inside BigQuery, so analysts who lack the Fine-Grained Reader role cannot read the email values even when they query the table. Asking analysts to behave is not a control, and a stripped export creates a second copy that must be maintained. Encryption with a shared key does not restrict who can read the column.

Archive storage is the lowest-cost class for data that is rarely accessed, and a lifecycle rule moves objects there automatically, while a retention lock stops early deletion. Deleting logs after 30 days would break the seven-year requirement, and expiring the BigQuery partitions after 90 days deletes the audit record too early.

Partition expiration removes whole old partitions automatically, which is cheap and needs no scheduled job. A weekly DELETE on an unpartitioned table scans the table each time and needs upkeep. BigQuery time travel is limited to seven days, so it cannot be used as a retention control.

An assertion in the pipeline checks every load against the allowed list and blocks or flags violations at the point they enter the warehouse. Relying on analysts to spot problems is late and inconsistent. Free text removes the check entirely, and renaming the column does not validate any values.

Data Viewer on the single dataset allows reading its tables, and Job User lets the analyst run query jobs. Data Editor on the whole project allows writing to every dataset, and BigQuery Admin on the dataset includes management rights the analyst does not need. Owner is the broadest role of all.

Group-based bindings let access follow team membership without per-person edits, and a recorded owner makes responsibility clear. Sharing a key by email exposes a credential, the Owner role is far broader than the team needs, and granting all authenticated users reads the data to anyone with a Google account.

Sensitive Data Protection inspects data and identifies infoTypes such as email addresses and phone numbers, which is exactly what discovery requires. Monitoring tracks system health and Billing tracks spend, so neither reveals what the data contains. Audit logs record who accessed what, not the content of columns.

Cloud Composer, which is managed Apache Airflow, gives you dependencies between tasks, a sensor that waits for the file, and per-task retries with logging. The VM cron loop has none of the dependency or retry handling, and a five-minute Cloud Scheduler trigger keeps firing with no notion of which steps already succeeded. The manual notebook depends on a person being available.

MERGE on a business key or overwriting a single partition makes a rerun produce the same result, so it is safe to repeat. Appending and deduplicating in the dashboard leaves duplicates in the data itself, and truncating the full table is far more disruptive than the failure warrants. Running manually does not address duplication.

A timeout or SLA alert on the orchestrated task fires when the run is late, so the team learns about it without watching the job. Manual checks are slow and unreliable, changing the schedule does nothing about duration, and verbose project-wide logging produces noise rather than an alert.

The all_done rule runs a task once all upstream tasks have finished, regardless of their outcome, which fits cleanup. all_success, the default, skips cleanup when the load fails. one_success and the none_failed variant depend on upstream outcomes that include failure, so they are also wrong here.

A sensor with a timeout waits for the file as long as is reasonable and then raises an alert, so late data is handled and visible. A fixed start time breaks when the file is late, endless minute-by-minute retries waste resources, and silently skipping a day loses data without anyone knowing.

Declaring the transformation as downstream of the load makes the scheduler wait for the load to succeed before starting it. Identical start times run the tasks in parallel with no ordering at all, and a fixed one-hour gap cannot guarantee the load has finished. Retry settings control reruns after a failure and do not create the dependency.

Retries with a delay recover from transient timeouts, and an execution timeout stops a hung call from blocking the DAG indefinitely. Lengthening the schedule does not change how the API behaves, and removing retries makes intermittent failures more disruptive. A bigger machine does not help when the wait is on a remote service.