Domain 1: Data Preparation and Ingestion
- For a BigQuery load with an extra source column, either enable ignore-unknown-values to drop the column or update the destination schema to add it; both resolve the schema mismatch.
- Storage Transfer Service recurring jobs are incremental after the first full run, copying only new or changed objects each scheduled run, which keeps nightly transfers efficient.
- Transfer Appliance is for offline migration of very large datasets when network bandwidth cannot complete an online transfer in time; the device is shipped and loaded over Google's internal network.
- Pub/Sub delivers at-least-once by default, so subscribers can receive duplicate copies of a message and must be idempotent; exactly-once is a separate optional subscription setting.
- Prefix a function with SAFE (for example SAFE.PARSE_DATE) to return NULL on unparseable input instead of failing the whole query; plain PARSE_DATE raises an error on malformed values.
- Firestore's document model lets each document store a different set of fields with no fixed predefined schema, unlike a relational table.
- Cloud SQL natively supports multi-row ACID transactions and joins across normalized tables, plus automated backups with point-in-time recovery to a specific moment.
- Specify the correct source character encoding on a load job to fix garbled text from a non UTF-8 file.
- Cloud Storage object versioning retains prior copies as noncurrent versions when an object is overwritten or deleted; resumable uploads let an interrupted transfer continue near where it stopped.
- Use a partition decorator on the destination table name to target a single ingestion-time partition in a load job.
- Pub/Sub reliable ordering with ordering keys requires publishing through a regional endpoint, not the global endpoint; subscribers can also extend a message's ack deadline while still processing.
- Federated queries push processing to the external database, so large scans against Cloud SQL can be slower than querying native BigQuery tables; BigQuery Data Transfer Service supports only a fixed list of Google and partner SaaS connectors.
- Storage Transfer Service pulling from Amazon S3 incurs AWS egress fees; splitting a large export into several gzip files lets BigQuery load them in parallel.
- Lifecycle rules that transition an object's storage class over time reduce cost as access frequency declines.
Domain 2: Data Analysis and Presentation
- A BigQuery dry run parses and validates SQL and estimates bytes processed without executing the query or incurring on-demand scan charges, making it a safe cost preview.
- COUNT(DISTINCT column) counts unique values, so a customer with five orders is counted once, unlike COUNT(*) which counts every row.
- Reduce BigQuery bytes scanned by filtering on the partition column (partition pruning), selecting only needed columns, and filtering on clustered columns (block pruning); partitioning and clustering can combine.
- The ML.PREDICT input table must contain the same feature columns with compatible types as the training data; it does not need the true label or matching row count.
- In Looker Studio, report edit access and data source edit access are separate grants; sharing as Viewer lets people view and filter without changing chart definitions.
- A scheduled query writes results to a destination BigQuery table that reports and dashboards read from; the notebook Executor can run a saved notebook on a recurring schedule.
- BI Engine is an in-memory acceleration service that speeds up interactive BigQuery queries behind dashboards.
- BigQuery DataFrames (bigframes) offers a pandas-compatible interface that keeps computation in BigQuery; Connected Sheets analyzes BigQuery data via live queries so the sheet never holds every row.
- BigQuery Studio notebooks support both Python and SQL; control notebook cost with idle shutdown, stopping unused instances, and right-sizing machine types.
- Use CSV for a simple, tool-agnostic flat handoff of small result sets to external partners who lack BigQuery or Workspace access.
- Line charts are the standard for tracing how a metric changes across time periods; a CASE WHEN expression returns a label per row based on conditions.
- SUM, AVG, and COUNT are aggregate functions that summarize multiple rows into a single value per group; an unexpected drop in a total should prompt a data-quality check first.
- A leading #standardSQL comment explicitly marks a query to use the Standard SQL dialect, which is already the default; the pandas head() method previews the first rows of a DataFrame.
Domain 3: Data Pipeline Orchestration
- Dataflow is built on Apache Beam's unified model, so the same code runs bounded batch or unbounded streaming pipelines; batch jobs reach a completed state when finished, streaming jobs run continuously.
- Choose Dataflow when a pipeline needs custom Beam code or windowed processing of an unbounded source; Dataflow templates let a job launch from the console with parameters, no source edits needed.
- The Dataflow job graph shows each transform stage with per-stage metrics such as throughput and system lag, the primary place to diagnose stage-level bottlenecks.
- Eventarc delivers at-least-once, so a function may be invoked more than once for the same event and must be idempotent; it can source events from Cloud Audit Logs, not just object notifications.
- In Airflow, a DAG (Directed Acyclic Graph) defines task ordering; the scheduler parses DAGs and decides which task instances run.
- The catchup parameter controls whether missed scheduled intervals are backfilled when a DAG starts; catchup=false skips missed intervals and begins from the most recent one.
- Under Airflow's default trigger rule, downstream tasks do not run when an upstream task fails, and the downstream task is marked upstream_failed.
- Cloud Composer is managed Airflow backed by a GKE cluster and a Cloud SQL metadata database, suited to multi-step pipelines spanning several services with dependencies, retries, and backfills.
- BigQueryInsertJobOperator is the standard Airflow operator for submitting a BigQuery job such as a SQL query.
- Cloud Workflows definitions are written declaratively in YAML or JSON; a scheduled query or Cloud Scheduler trigger is enough for a single dependency-free BigQuery job.
- Use a time-based trigger when a run must occur on a fixed clock cadence regardless of new data; a bounded daily file with a complete dataset is best processed as a batch job.
- Cloud Monitoring alerting policies notify through channels such as email, Slack via webhook, and SMS.
- Prevent duplicate records on a pipeline rerun with a keyed MERGE upsert or by overwriting the affected date partition.
Domain 4: Data Management
- IAM controls which identities can perform which actions; VPC Service Controls restricts where data can move by defining a perimeter, protecting against exfiltration even by an IAM-permitted principal.
- Grant least privilege: replace broad Editor with narrower roles like BigQuery Data Editor and Storage Object Viewer, scope Data Viewer to the single needed dataset, and use time-limited signed URLs for external object access.
- Storage Admin manages buckets including their settings and IAM policy, while Object Admin manages only the objects within them.
- The secretAccessor role reads a secret's payload in Secret Manager; policy tags organized in a taxonomy attach to BigQuery columns to control column-level access.
- Google Cloud encrypts data at rest by default with Google-managed keys; CMEK keys live in Cloud KMS, while CSEK keys are supplied by the customer at each request.
- Cloud KMS automatic key rotation sets how often a new key version is generated on a schedule.
- Modifying a BigQuery table or partition resets long-term storage eligibility to active-rate pricing, restarting the 90-day unchanged clock needed to re-qualify for the discount.
- A table-level partition expiration overrides a dataset's default table expiration and drops each partition independently as it ages; a short default table expiration on a sandbox dataset auto-deletes new tables.
- Sensitive Data Protection inspection jobs scan a source such as a BigQuery table to detect and report sensitive findings; masking replaces characters with a fixed symbol, optionally keeping some like the last four digits.
- Apply labels (for example department:finance) to BigQuery resources so billing export and cost reports can be filtered or grouped by attribute for per-team cost visibility.
- Export Cloud Billing data to BigQuery for queryable, detailed cost records that can be filtered and grouped by resource labels for recurring reports.
- A Cloud SQL cross-region read replica keeps a synchronized copy in another region for failover; data lineage records a dataset's origin and transformation sequence.
- Sharing a BigQuery dataset across projects lets teams query the same data without duplicating it; Object Lifecycle Management can automatically change an object's storage class at a specified age.
Google Cloud Associate Data Practitioner exam tips
- When a question describes duplicate deliveries or a function running twice, the fix is almost always idempotency plus knowing Pub/Sub and Eventarc are at-least-once by default.
- For cost-control questions, reach first for partition filtering, column selection, clustering, dry runs, BI Engine, and the long-term storage discount and what resets it.
- Match the tool to the job: Dataflow for custom or streaming Beam code, Cloud Composer for multi-step cross-service DAGs, Workflows for lightweight service orchestration, and a scheduled query for a single BigQuery job.
- On access questions, pick the narrowest role that meets the need (Data Editor over Editor, Object Admin over Storage Admin, dataset-scoped Data Viewer) and use signed URLs for temporary external access.
- Know the ingestion decision tree: Transfer Appliance for offline bulk, Storage Transfer Service for recurring bucket-to-bucket, BigQuery Data Transfer Service for supported SaaS sources, and Datastream for CDC.
Study guide FAQ
How long is the exam and what score do I need to pass?
The exam runs 120 minutes and reports results as pass or fail (Google does not publish a fixed passing score). It draws from a large pool of questions across four domains covering ingestion, analysis, orchestration, and management.
Do I need to write code or run pipelines for this exam?
No hands-on coding is required, but you must recognize the right service and approach from scenarios, including reading SQL, understanding BigQuery cost behavior, and knowing when to use Dataflow, Composer, or a scheduled query.
What is the difference between Looker and Looker Studio for this exam?
Looker Studio is the free, self-service dashboarding tool with separate report and data-source permissions, while Looker is the enterprise BI platform with a governed semantic model. Both surface BigQuery data for analysis and presentation.
How does BigQuery bill for queries and storage?
On-demand queries are billed by bytes scanned, which you reduce with partition pruning, column selection, and clustering, and can preview with a dry run. Storage is billed as active or long-term, where any table modification resets the 90-day clock back to the active rate.