CertifyDeck
Start for free
All cheat sheets
Associate Data Practitioner · CHEAT SHEET

Google Cloud Certified Associate Data Practitioner

Key concepts grouped by official exam weight.

Download as PDF (free, no sign-up)

1. Data Preparation and Ingestion

30–30%

Prepare and process data

  • In ETL, data is transformed before it reaches BigQuery (Dataflow, Cloud Data Fusion), useful with an existing process or to save BigQuery resources; in ELT, raw data is loaded and transformed inside with SQL and Dataform, the approach Google generally recommends. ETLT combines a light pre-load step, such as removing sensitive data, with SQL transformations after loading; exporting results to other systems is reverse ETL.
  • Storage Transfer Service is the managed service for online copies from Amazon S3, Azure, public URLs, HDFS, on-premises file systems with agents or other buckets, on a schedule or event-driven. Transfer Appliance is a physical device for large volumes when bandwidth is lacking; gcloud storage suits small, ad hoc copies. BigQuery Data Transfer Service loads SaaS or Cloud Storage data into BigQuery, and Database Migration Service moves databases.
  • Knowledge Catalog (formerly Dataplex) offers profile scans, with column statistics such as nulls, distinct and top values, and auto data quality scans, which validate BigQuery tables with built-in or SQL rules, thresholds, incremental scope, scores and email reports. In Dataform, assertions fail if their query returns rows; SQL can also count nulls and duplicates.
  • BigQuery data preparation cleans BigQuery tables with Gemini suggestions, filter, deduplicate and validation steps with an error table; SQL offers SAFE_CAST, TRIM, UPPER, UPDATE and ROW_NUMBER. Cloud Data Fusion Wrangler cleans visually before loading, batch only; Dataflow cleans in streaming or batch, with templates and UDFs or Apache Beam, and sends failed rows to a dead-letter table.

Extract and load data into appropriate Google Cloud storage systems

  • BigQuery loads Avro, CSV, JSON, ORC and Parquet data. Avro, Parquet and ORC are self-describing: BigQuery reads the schema from the data and can read them in parallel even with internal compression codecs. CSV doesn't support nested or repeated data, JSON must be newline-delimited, and CSV and JSON need schema auto-detection or an explicit schema; gzip is their only supported compression and slows the load.
  • BigQuery Data Transfer Service schedules managed loads into BigQuery from SaaS applications, Cloud Storage, other clouds and data warehouses, with no code and with backfills. Database Migration Service migrates databases into Cloud SQL and AlloyDB for PostgreSQL, continuously or one time. Dataflow runs Apache Beam pipelines or Google-provided templates, and Cloud Data Fusion builds visual, no-code pipelines.
  • BigQuery is the store for SQL analysis of large structured datasets, and Cloud Storage holds unstructured objects of any format. Cloud SQL is the managed relational database for MySQL, PostgreSQL and SQL Server; Spanner is relational with transactional consistency at global scale; Bigtable is a low-latency key-value store for time-series and IoT data; Firestore is the serverless document database.
  • A regional bucket stores data across zones of one region with no replication charges; dual-regions and multi-regions replicate asynchronously across regions and keep serving in a regional outage, and dual-regions let you pick the regions and enable turbo replication. In BigQuery a multi-region gives no regional redundancy; Cloud SQL high availability protects against a zone outage, not a regional one.
  • Structured data follows a fixed schema and fits relational databases such as Cloud SQL or BigQuery tables; semi-structured data such as JSON carries keys but no rigid schema and fits Firestore or BigQuery JSON columns. Unstructured data (images, audio, video, free text) is stored as objects in Cloud Storage and analyzed from BigQuery through object tables.

2. Data Analysis and Presentation

27–27%

Identify data trends, patterns, and insights by using BigQuery and Jupyter notebooks

  • In GoogleSQL, WHERE filters rows before aggregation, GROUP BY with SUM, COUNT or COUNTIF summarizes groups, HAVING filters groups and GROUP BY ROLLUP adds subtotals. Window functions with OVER and PARTITION BY (RANK, ROW_NUMBER, LAG) compute per-row rankings and changes, and QUALIFY filters their results; to explore a table, use the preview options instead of SELECT * with LIMIT.
  • Colab Enterprise provides managed notebooks inside BigQuery Studio, with IAM-controlled access, runtimes that are provisioned and shut down for you, SQL cells whose results are saved as DataFrames and visualization cells for no-code charts. BigQuery DataFrames runs pandas-style code in BigQuery without moving the data, and notebooks can be scheduled with results stored in Cloud Storage.
  • To answer business questions in BigQuery, BigQuery ML contribution analysis compares test and control data to find the segments behind a change, data insights and data canvas use Gemini to suggest questions and explore in natural language, and Connected Sheets brings the data into Sheets without SQL. In SQL, SAFE_DIVIDE avoids errors in ratios and SUM OVER () computes shares of the total.

Visualize data and create dashboards in Looker given business requirements

  • Looker user-defined dashboards are created from Create, a folder, a Look or an Explore, and combine query tiles or Look-linked tiles. Dashboard filters, required or linked, and cross-filtering let viewers narrow data; dashboards are shared through the View or Manage Access, Edit folder access levels and sent with Schedule delivery (Weekly, Datagroup update, Run schedule as recipient).
  • Looker uses a semantic model in LookML, version-controlled with Git and with Development Mode, so the business queries governed definitions without writing SQL. Data Studio (formerly Looker Studio) is the no-cost drag-and-drop reporting tool; Data Studio Pro, the paid version, adds organization-owned content in a Google Cloud project with IAM, team workspaces and CMEK.
  • In a LookML view, sql_table_name sets the table queried (project.dataset.table in BigQuery), label changes the display name without breaking references and hidden: yes removes a field from the picker without being a security feature. Dimensions use types such as string, number, yesno or tier and measures sum, count or count_distinct; changes are tested in Development Mode on a personal Git branch.

Define, train, evaluate, and use ML models

  • BigQuery ML trains models with SQL without moving data: linear regression for numeric values, logistic regression to classify, k-means to segment without labels, matrix factorization to recommend, ARIMA_PLUS for time series and contribution analysis to explain metric changes. AutoML on Gemini Enterprise Agent Platform (formerly Vertex AI AutoML) builds codeless models, and BigQuery ML offers AUTOML_CLASSIFIER and AUTOML_REGRESSOR.
  • To use a pretrained LLM on Gemini Enterprise Agent Platform (formerly Vertex AI) from BigQuery, you create a Cloud resource connection in the same location as the dataset, grant its service account the Agent Platform User role and run CREATE MODEL ... REMOTE WITH CONNECTION with an ENDPOINT. AI.GENERATE_TEXT then sends the prompt column to the model and returns result and status next to the input columns.
  • An ML project follows the standard workflow: define the outcome you want, gather and prepare the data, train, evaluate, and finally deploy and predict. When preparing data, clean inconsistencies, avoid data leakage and training-serving skew, and always evaluate on data the model didn't see during training.
  • In BigQuery ML you train with CREATE MODEL, choosing model_type (LINEAR_REG, LOGISTIC_REG, KMEANS, ARIMA_PLUS and others) and input_label_cols, plus options such as DATA_SPLIT_METHOD, AUTO_CLASS_WEIGHTS or the TRANSFORM clause. ML.EVALUATE without data returns the metrics computed during training, and with a table it evaluates new data; ML.CONFUSION_MATRIX and ML.ROC_CURVE complete classifier evaluation.
  • ML.PREDICT runs batch prediction in BigQuery and returns the input columns plus predicted_<label> (and _probs for classifiers), with THRESHOLD for the binary cutoff. ML.FORECAST serves ARIMA_PLUS, ML.EXPLAIN_PREDICT adds per-row attributions and ML.DETECT_ANOMALIES finds anomalies; for online prediction you register the model and deploy it to an endpoint.

3. Data Pipeline Orchestration

18–18%

Design and implement simple data pipelines

  • The tool follows the requirement: Managed Service for Apache Spark for existing Spark and Hadoop jobs (also serverless, with no clusters); Dataflow for Apache Beam batch and streaming pipelines; Cloud Data Fusion for designing pipelines visually; Dataform for version-controlled SQL transformations inside BigQuery; and Managed Service for Apache Airflow for orchestrating DAGs.
  • With ELT you load raw data into BigQuery and transform it there with Dataform, BigQuery pipelines or data preparation; Google recommends it to most customers because it avoids extra tools. ETL transforms before loading, with Cloud Data Fusion or Dataflow, and suits an existing transformation process or reducing BigQuery usage. Exporting results to other systems is reverse ETL.
  • Use Dataform to transform data already in BigQuery with Git-versioned SQL and assertions; Dataflow for batch and streaming Apache Beam code (or its templates and the job builder when you do not want to code); Cloud Data Fusion for visual plugin-based pipelines; and Managed Service for Apache Spark, on clusters or serverless, for existing Spark jobs.

Schedule, automate, and monitor basic data processing tasks

  • BigQuery scheduled queries run GoogleSQL on a recurring schedule with @run_date and @run_time, write with WRITE_TRUNCATE or WRITE_APPEND, support backfills, are paused by disabling them and can run as a service account. Cloud Scheduler sends cron jobs to HTTP or Pub/Sub targets with at-least-once delivery, and in Managed Service for Apache Airflow the DAG sets schedule, catchup and retries.
  • The Dataflow monitoring interface shows the jobs list, the job graph with wall time and bottleneck warnings, Execution details (Stage progress, Stage workflow, and Worker progress for batch only), Job metrics, the Autoscaling tab for streaming, estimated cost, Recommendations, logs with the Diagnostics tab, and data sampling to see the elements each step processes.
  • In Logs Explorer you filter entries by query, severity and time range. Log-based metrics count or distribute values and feed charts and alerts; log-based alerting policies notify you when a message appears. Admin Activity audit logs are always written and Data Access logs are on by default for BigQuery. Sinks route new entries to BigQuery, Cloud Storage or Pub/Sub.
  • A scheduled query is enough for recurring SQL in BigQuery; BigQuery pipelines chain SQL and notebooks without another service; Managed Service for Apache Airflow orchestrates Python ETL/ELT DAGs across services, with backfills; Workflows chains HTTP services serverlessly with millisecond latency; and Managed Service for Apache Spark Workflow Templates run graphs of Spark and Hadoop jobs.
  • A BigQuery subscription writes Pub/Sub messages to a table as they arrive, with no subscriber of your own, when they need no processing; it supports Use table schema, Write metadata, CDC with _CHANGE_TYPE and a dead-letter topic, and delivers at least once. For windowing, aggregation, joins or exactly-once delivery use Dataflow, for example the Pub/Sub to BigQuery template.

4. Data Management

25–25%

Configure access control and governance

  • Least privilege in IAM means granting the most limited predefined role at the smallest scope (a dataset or a bucket instead of the project), using one service account per component and avoiding basic roles and service account keys in production. IAM Conditions let access expire automatically, and Project IAM Admin manages allow policies without granting data access.
  • A permission (SERVICE.RESOURCE.VERB) allows one action and a role bundles permissions. Basic roles give broad access across all services, can only be granted on the whole project in Cloud Storage and give READER, WRITER or OWNER on new BigQuery datasets; Google maintains predefined roles; you maintain custom roles, which never change without your control.
  • Uniform bucket-level access (recommended) disables ACLs and leaves only IAM, and is required for managed folders and IAM Conditions on the bucket; fine-grained access with ACLs suits per-object permissions or migrations from Amazon S3. Public access prevention overrides allUsers and allAuthenticatedUsers, and signed URLs give time-limited access without an account.
  • BigQuery sharing (formerly Analytics Hub) shares datasets and Pub/Sub topics without replication: the publisher creates listings in a data exchange and the subscriber gets a read-only linked dataset or a linked subscription. It beats exporting copies when you need always-current data, usage metrics or egress restrictions; data clean rooms add analysis rules for sensitive data.

Configure lifecycle management

  • Choose the class by access frequency: Standard for frequently accessed data, Nearline for once a month or less, Coldline for at most once a quarter and Archive for less than once a year, such as regulatory records; all give millisecond access. Autoclass moves each object by its usage when the pattern is unpredictable, with Nearline or Archive as the terminal class.
  • In Cloud Storage, an Object Lifecycle Management rule with the Delete action and conditions such as age or daysSinceNoncurrentTime removes objects automatically; it waits while a retention policy is unfulfilled. In BigQuery, table expiration, partition expiration and the dataset default table expiration delete data; the dataset default only applies to new tables.
  • For archives in Cloud Storage, choose the class by read frequency: Nearline (monthly), Coldline (quarterly) or Archive (less than once a year); Autoclass fits unpredictable access. Bucket Lock and object holds prevent deletion, and BigQuery and Cloud SQL can export data to Cloud Storage to archive it.

Identify high availability and disaster recovery strategies for data in Cloud Storage and Cloud SQL

  • Cloud SQL offers automated and on-demand backups, final backups and backups retained after instance deletion, and point-in-time recovery that creates a new instance; enhanced backups use Backup and DR Service with a central backup vault. Cloud Storage recommends soft delete over Object Versioning, and BigQuery offers time travel and table snapshots.
  • In Cloud SQL, high availability replicates synchronously to a standby in another zone with automatic failover; asynchronous, read-only read replicas offload queries, and cross-region replicas serve regional DR and migrations. Cloud Storage offers dual-region turbo replication and cross-bucket replication; BigQuery offers cross-region dataset replication.
  • A regional bucket keeps data across zones of one region; a dual-region uses the pair of regions you choose and a multi-region uses data centers Cloud Storage chooses, both active-active with no path changes. In Cloud SQL, high availability uses a primary and a secondary zone within the region, and multi-regional locations are only used for backups.

Apply security measures and ensure compliance with data privacy regulations

  • Google default encryption (the GMEK of the guide) encrypts everything at rest with no setup. CMEK keeps keys in Cloud KMS, manual or with Autokey, to control rotation, access and logging; BigQuery, Cloud SQL and Cloud Storage support it. CSEK, in Cloud Storage, supplies your own key with each request and Google does not store it.
  • Cloud KMS creates and manages the customer-managed encryption keys (CMEK) that protect BigQuery, Cloud Storage or Cloud SQL data at rest: you control location, rotation, IAM access and the state of each key version. The service agent needs the CryptoKey Encrypter/Decrypter role, the key ring must match the data location, and Autokey creates keys as resources are created.
  • Encryption at rest protects data stored on disk: Google encrypts all content with AES-256 without configuration, and CMEK or CSEK only change who controls the keys. Encryption in transit protects data while it travels: TLS at the Google Front End for APIs, automatic encryption of internal-IP traffic in the VPC and, for Cloud SQL, the SSL mode or the Cloud SQL Auth Proxy.
Practice this certification