Professional Data Engineer · CHEAT SHEET
Google Cloud Certified Professional Data Engineer
Key concepts grouped by official exam weight.
Download as PDF (free, no sign-up)1. Designing data processing systems
22–22%Designing for security and compliance
- Grant predefined roles on the lowest possible resource: BigQuery Data Viewer on the dataset and Job User on the project, and use dedicated service accounts for Dataflow workers. IAM Conditions limit access in time, and organization policies, inherited by every descendant, enforce guardrails such as domain-restricted sharing or public access prevention.
- Google encrypts data at rest by default; with CMEK you control the key encryption keys in Cloud KMS, in the same location as the dataset, and with Cloud EKM the key material stays in an external key manager. Autokey creates keys on demand, gcp.restrictNonCmekServices enforces CMEK, and rotating a key does not re-encrypt existing BigQuery tables.
- Sensitive Data Protection discovery continuously locates PII and de-identification transforms it: AES-SIV and format-preserving encryption are reversible with the key, HMAC-SHA-256 is not, and date shifting keeps intervals. In BigQuery, dynamic data masking with policy tags shows masked values to Masked Readers without changing their queries.
- The gcp.resourceLocations constraint limits where new resources are created, and a BigQuery dataset location cannot change after creation. Pub/Sub message storage policies, the Dataflow job region and regional log buckets keep data in one region; BigQuery Omni queries data in S3 without moving it out of AWS.
- For regulatory retention, Bucket Lock and Object Retention Lock in Cloud Storage and locked log buckets prevent deletion or shorter retention. Data Access audit logs are disabled by default except in BigQuery; Access Transparency and Access Approval log and approve access by Google personnel, and Assured Workloads applies control packages such as HIPAA.
Designing for reliability and fidelity
- To clean data already loaded into BigQuery, Dataform manages SQLX transformations with Git and a dependency tree, and BigQuery data preparation applies Gemini suggestions with deduplication and validation steps that route rows to an error table. Before loading, Cloud Data Fusion Wrangler cleans visually and Dataflow sends failed rows to a dead-letter table. Gemini in BigQuery generates SQL from natural language.
- Managed Airflow orchestrates Python DAGs that span clouds and on-premises sites; Workflows is the serverless option with no idle cost for a few steps, and Dataform schedules its runs with workflow configurations. In Airflow, sensors in reschedule mode free the worker. Dataflow is monitored through its job interface and Cloud Monitoring alerts on the Failed and System lag metrics.
- BigQuery managed disaster recovery offers soft failover, with no data loss and both regions up, and hard failover, which can lose unreplicated data; scheduled queries must be recreated after failover. Cloud SQL combines high availability across zones with cross-region replicas that you promote, dual-region Spanner gives zero RPO within one country, and dual-region buckets with turbo replication serve data without path changes.
- Spanner offers atomic read-write transactions across tables with external consistency, and lock-free read-only transactions. BigQuery supports multi-statement transactions across tables, but with a table-level lock and no queuing. Bigtable is atomic only per row; with several clusters it is eventually consistent, and read-your-writes requires routing to the same cluster.
- Dataform validates every run with assertions (nonNull, uniqueKey, rowConditions or manual SQLX) and tests logic with unit tests over mocked inputs. Knowledge Catalog auto data quality validates tables with no infrastructure using built-in and custom SQL rules, incremental scope, quality scores, email notifications, export to BigQuery and publication of results to the catalog.
Designing for flexibility and portability
- Choose services that absorb future requirements without a redesign: Datastream to move from nightly exports to near real-time CDC, Managed Service for Apache Kafka to keep Kafka clients, serverless Spark to pay only while running, Dataflow to reuse the same logic for batch and streaming, continuous queries to react in SQL, and new Pub/Sub subscriptions to add consumers.
- BigQuery Omni queries S3 or Blob Storage data with BigLake tables where it lives and can write results back to that cloud. BigLake tables delegate access and enforce row and column security for Spark too; Iceberg managed tables keep one open copy in your bucket with DML and streaming. Beam on Dataflow can change runner without rewrites, and gcp.resourceLocations limits where resources are created.
- Knowledge Catalog (formerly Dataplex Universal Catalog) discovers Cloud Storage files with discovery scans that publish BigLake tables when you supply a connection, profiles tables with full or incremental data profile scans, and organizes metadata with aspects and entry types. Its search mixes natural language and keywords and respects permissions; in a data mesh each domain has raw and curated zones.
Designing data migrations
- Migration planning starts with an inventory of workloads, dependencies, teams and requirements; for a data warehouse, the BigQuery migration assessment analyzes metadata and query logs and proposes the target state and plan. First movers should be non-critical, loosely coupled workloads, and each workload's strategy depends on how much downtime it can tolerate.
- Each tool has its case: BigQuery Data Transfer Service loads warehouses such as Teradata or Redshift into BigQuery, incrementally too; Database Migration Service moves databases to Cloud SQL and AlloyDB with continuous migrations; Datastream replicates CDC changes to BigQuery over private VPN or Interconnect connectivity; Transfer Appliance covers missing bandwidth, and the validation tool compares source and target.
2. Ingesting and processing the data
25–25%Planning the data pipelines
- Pub/Sub provides export subscriptions to BigQuery, Cloud Storage and Bigtable when messages need no processing, and import topics for external sources such as Amazon Kinesis Data Streams. When transformation or exactly-once delivery is needed, use Dataflow with Google-provided templates or managed I/O for Kafka, Iceberg and BigQuery; Managed Service for Apache Kafka fits when the Kafka API must stay portable.
- Lightweight per-message changes fit Pub/Sub Single Message Transforms; SQL ELT inside BigQuery with dependencies, assertions and incremental tables belongs in Dataform, and Cloud Data Fusion offers visual pipelines on ephemeral Spark clusters. For orchestration, Managed Airflow suits DAG-based ETL/ELT with backfills, and Workflows suits serverless, low-latency chaining of HTTP services.
- Dataflow workers without external IPs reach Google APIs through Private Google Access or Private Service Connect; Cloud NAT is only needed for external services. Without Dataflow Shuffle or Streaming Engine, an ingress rule on the Dataflow network tag must allow ports 12345 and 12346. Shared VPC needs the full subnetwork URL, and VPC Service Controls builds perimeters against exfiltration.
- Google encrypts data at rest and in transit by default; with CMEK you control keys in Cloud KMS, in hardware with Cloud HSM or outside Google with Cloud EKM. Dataflow requires regional keys in its region and Pub/Sub recommends global keys; in BigQuery a dataset default key affects only new tables and an organization policy can require CMEK. Interconnect links are encrypted with HA VPN or MACsec.
Building the pipelines
- In Dataflow, records that fail parsing go to a tagged output and a dead-letter destination instead of a Pub/Sub dead-letter topic. In BigQuery, SAFE_CAST turns conversion errors into NULL; Dataform assertions (nonNull, uniqueKey) validate every run, and Cloud Data Fusion Wrangler cleans batch data visually with directives on a preview.
- Dataflow (Apache Beam) fits windowed stream aggregation; Managed Service for Apache Spark cluster deployment fits Hadoop, Hive or custom-config migrations, and serverless fits new Spark jobs billed per run. Managed Service for Apache Kafka keeps existing Kafka clients; BigQuery and Cloud Storage subscriptions and continuous queries avoid separate pipelines.
- Dataflow groups streams into fixed, sliding or session windows; the watermark marks late data, which is dropped unless an allowed lateness is set, and triggers with accumulating or discarding mode decide what each pane emits. For enrichment, Beam offers the Enrichment transform with BigTableEnrichmentHandler and BigQuery offers AI.GENERATE_TEXT over a Gemini remote model.
- BigQuery Data Transfer Service schedules managed loads into BigQuery from Amazon S3, Google Ads and other sources, with backfills. Storage Transfer Service copies files between clouds, on-premises storage and Cloud Storage; Transfer Appliance moves data offline when the network is the limit. Datastream replicates database changes in near real time, and load jobs are cheapest for data that changes rarely.
- Pub/Sub import topics ingest continuously from Amazon Kinesis or Cloud Storage, and Avro or Protocol Buffer topic schemas reject nonconforming messages. BigQuery queries operational databases with EXTERNAL_QUERY and S3 data with BigQuery Omni and BigLake tables without copying it. Kafka Connect links clusters with MirrorMaker 2.0 and Pub/Sub, and the Cloud Data Fusion Hub offers plugins.
Deploying and operationalizing the pipelines
- Workflows orchestrates HTTP services and connectors serverlessly with millisecond latency and per-step billing; Managed Airflow (formerly Cloud Composer) orchestrates batch ETL in Python DAGs with operators, sensors, retries, deferrable operators and event triggers through the REST API. Dataform schedules its own runs with workflow configurations.
- Cloud Build runs tests on each pull request and deploys artifacts. In Dataflow, Flex Templates package the pipeline as a Docker image in Artifact Registry and graph_validate_only validates an update without stopping the job. Dataform connects to Git, through Developer Connect for private networks, and uses one release configuration per environment with a Git commitish and project override.
3. Storing the data
20–20%Selecting storage systems
- Start from how the data will be read and written: in Bigtable, design the row key for reads by key, prefix or range and avoid keys that start with a timestamp; in BigQuery, partition by date and cluster on high-cardinality columns, use search indexes for row lookups and the Storage Read API for bulk parallel reads; in Cloud Storage, use Autoclass when access is unpredictable.
- Choose the service by access pattern and consistency: Spanner for relational SQL with external consistency across regions; AlloyDB for PostgreSQL with analytics on live transactional data; Cloud SQL for managed MySQL, PostgreSQL or SQL Server; Bigtable for single-keyed data at scale; Firestore for documents, including MongoDB compatibility; Memorystore for in-memory caching; BigQuery for analytics.
- In Cloud Storage, match the class to access frequency (Standard for hot data, Nearline monthly, Coldline quarterly, Archive less than yearly) and keep hot data in the same region as compute. In BigQuery, partitions qualify for long-term storage one by one, and with physical billing you should load incrementally instead of overwriting tables, because replaced data stays billed for time travel and fail-safe.
- Object Lifecycle Management applies Delete and SetStorageClass rules based on age, noncurrent versions and other conditions; a retention policy locked with Bucket Lock prevents early deletion. In BigQuery, partition or table expiration deletes data automatically, and time travel restores deleted tables within its window, after which the fail-safe period begins.
Planning for using a data warehouse
- When migrating, transfer the schema unchanged first and optimize it later with clustering and nested and repeated fields (ARRAY of STRUCT) for parent-child data queried together. Facade views isolate consumers from schema changes, INT64 keys make joins cheaper, and NOT ENFORCED primary and foreign keys let the optimizer eliminate joins.
- In BigQuery, denormalize, because the extra storage cost is worth the performance gain, preferably with nested and repeated fields rather than full flattening, which causes shuffling; star schemas are usually already optimized. Single-row updates belong in an OLTP database such as Cloud SQL, and in Bigtable each entity is kept in a single row because transactions are row-scoped.
- Turn each business requirement into a concrete choice: residency in one country, a BigQuery region in that country rather than a multi-region; bounded freshness on CDC tables, max_staleness; workload isolation and cost attribution, reservations with assignments; open format with DML, Apache Iceberg managed tables; cheap test copies, clones; audit copies, snapshots.
- Shape the warehouse architecture to each access pattern: incremental materialized views for repeated, always-fresh aggregations; BI Engine with preferred tables and partitioning for dashboards; continuous queries with EXPORT DATA to Pub/Sub for event-driven processing; reverse ETL to Bigtable for low-latency lookups; and destination tables to avoid repeating expensive transformations.
Using a data lake
- To manage the lake, Knowledge Catalog discovery scans catalog Cloud Storage files and register them as BigLake tables; access is delegated to the table's connection, so analysts should not read the objects directly. Control costs with custom query quotas per project or per user, and with Requester Pays when third parties download the data; raw and curated zones separate data by readiness.
- To process a Cloud Storage data lake, Apache Iceberg managed tables in BigQuery support DML and streaming in your own bucket, while BigLake tables are read-only and object tables enable inference on unstructured data. Metadata caching avoids listing millions of files, and Managed Service for Apache Spark serverless runs PySpark without managing clusters.
- To monitor a data lake, Knowledge Catalog offers data profile scans, auto data quality scans with rules, scores and email alerts, and data lineage to trace errors and impact. In Cloud Storage, Storage Insights gives organization-wide visibility, Data Access logs must be enabled explicitly, and Pub/Sub notifies object changes.
Designing for a data platform
- Knowledge Catalog (formerly Dataplex Universal Catalog) is the platform's metadata layer: it catalogs BigQuery automatically, registers Cloud Storage data as BigLake or object tables through discovery scans, imports metadata from external sources with managed connectivity, and organizes context with entry groups, entry types, aspects and glossaries. AI agents query it through its remote MCP server.
- In a data mesh, domains own their data and publish it as products, while central teams define governance and provide a self-service platform with IaC templates that are secure by default. In Knowledge Catalog, a lake represents a domain, raw and curated zones organize the teams, and data products add access groups and contracts. Authorized views and BigQuery sharing expose data without copying it.
4. Preparing and using data for analysis
15–15%Preparing data for visualization
- BigQuery connects to Connected Sheets for analyzing large tables from spreadsheets, to Data Studio through its connector with owner's, viewer's or service account credentials, to Looker through a service account or per-user OAuth, and to other tools through the ODBC and JDBC drivers, which can read with the Storage Read API. When CMEK is enforced, a project default key encrypts temporary results.
- Precalculating fields avoids repeating expensive transformations in every dashboard query: materialize results in a destination table, build incremental tables in Dataform, schedule queries with the @run_date parameter, or use Looker persistent derived tables and aggregate tables with aggregate awareness. In Data Studio, data source calculated fields are reused across all reports.
- BI Engine is an in-memory analysis service that accelerates BI tool queries without changing them; it is reserved per region, and preferred tables limit acceleration to what matters. Incremental materialized views precompute aggregates, return fresh data and enable smart tuning, and the max_staleness option lowers cost when somewhat stale data is acceptable.
- To diagnose slow BigQuery queries, the execution graph and query plan show the stages, their timings and performance insights such as slot contention, insufficient shuffle quota, high cardinality joins or partition skew. Common fixes are selecting only the needed columns, filtering with constant expressions to prune partitions, filtering skewed keys early, and querying INFORMATION_SCHEMA.JOBS.
- In BigQuery, policy tags protect columns: Fine-Grained Reader sees real values and Masked Reader sees them through a masking rule (SHA-256 hash when tables must still be joined, email mask, date year mask, Nullify). Row access policies filter which rows each group sees, and Sensitive Data Protection data profiles locate sensitive columns before you tag them.
Preparing data for AI and ML
- BigQuery ML applies automatic preprocessing and lets you define manual preprocessing in the TRANSFORM clause, which is reapplied by ML.EVALUATE and ML.PREDICT; a TRANSFORM_ONLY model reuses the rules in batch. ML.FEATURES_AT_TIME and ML.ENTITY_FEATURES_AT_TIME prevent data leakage, and Feature Store on Gemini Enterprise Agent Platform serves features kept in BigQuery online.
- Object tables expose unstructured Cloud Storage files in BigQuery with access delegation and row-level security. For RAG, AI.CHUNK_DOCUMENT splits PDFs, AI.EMBED or AI.GENERATE_EMBEDDING create embeddings and VECTOR_SEARCH retrieves context for AI.GENERATE. Queries and documents must use the same embedding model, and pre-filtering needs columns stored in the index.
Sharing data
- BigQuery analysis rules are defined on views (privacy_policy) or in data clean rooms: aggregation threshold (minimum distinct privacy units per row), differential privacy (cumulative budget) and list overlap, with JOIN_ALL, JOIN_ANY, JOIN_BLOCKED or JOIN_NOT_REQUIRED join restrictions. Query templates limit execution to approved queries, and egress controls block copies and exports.
- To publish data without copying it, BigQuery sharing offers private or public exchanges (allAuthenticatedUsers with Analytics Hub Viewer and Subscriber), commercial listings through Cloud Marketplace and shared Pub/Sub topics. Inside the organization, authorized datasets publish views and Knowledge Catalog data products add contracts and access requests; Requester Pays moves Cloud Storage access charges to requesters.
- In Data Studio, credentials decide who sees the data: Owner's Credentials use the owner's authorization, Viewer's Credentials each viewer's own, and Service Account Credentials a service account (BigQuery only). Reusable data sources, download restrictions and data freshness support governance and cost; Looker centralizes metrics in LookML, and Connected Sheets shares data without granting BigQuery access.
- BigQuery sharing (formerly Analytics Hub) shares datasets and Pub/Sub topics without replicating them: publishers create listings in data exchanges and subscribers get read-only linked datasets. The roles/analyticshub.* roles separate administration, publishing and subscribing; data egress options limit copies and exports, and SCHEMATA_LINKS and SHARED_DATASET_USAGE show subscriptions and usage.
5. Maintaining and automating data workloads
18–18%Optimizing resources
- To minimize cost per business need, first define latency and throughput objectives. In Dataflow, FlexRS lowers the cost of batch jobs that are not time sensitive, and at-least-once mode lowers the cost of streaming that tolerates duplicates. In BigQuery, use maximum bytes billed per query, custom daily quotas and incremental loads instead of overwrites. In Spark, Spot secondary workers suit non-critical workloads.
- Critical workloads need their own capacity, not borrowed capacity. In BigQuery, give their reservation enough baseline slots, which are always available, and assign load jobs with the PIPELINE job type to leave the shared pool that has no guarantees. In Dataflow, set a worker floor with min_num_workers; in Managed Airflow, raise the worker maximum when the queue grows; in Spark, use non-preemptible secondary workers.
- A persistent cluster suits frequent or repeated workloads without paying for cluster creation and deletion each time, for example through workflow templates with a cluster selector. A job-scoped ephemeral cluster is created, used and deleted by a managed-cluster workflow template or Managed Airflow, always with data in Cloud Storage or BigQuery. For ad hoc use, Cluster Scheduled Deletion removes the cluster after an idle period.
Designing automation and repeatability
- A Managed Airflow DAG is a Python file with the DAG definition, operators and their dependencies, declared with >> and <<. Each task should be an idempotent unit with retries enabled, and pools where APIs have limits. Branches are joined with trigger rules such as none_failed_min_one_success, one DAG triggers another with TriggerDagRunOperator, and heavy or GPU tasks run on a separate GKE cluster.
- Repeatable scheduling depends on scope: BigQuery scheduled queries for recurring SQL, Dataform workflow configurations for time-based Dataform runs, Workflows with Cloud Scheduler for lightweight serverless orchestration that calls HTTP APIs, and Managed Airflow for complex pipelines. Runs are repeatable when they use parameters such as run_date or the data interval, and catchup controls whether past runs are created.
Organizing workloads based on business requirements
- With BigQuery editions, reservations group slots into isolated pools assigned to projects, folders or organizations; the most specific assignment wins and None forces on-demand billing. Baseline slots are always available, autoscaling adds slots up to a maximum, and commitments lower the cost of the steady baseline. Idle slots are shared only within the same administration project.
- BigQuery runs queries as interactive jobs by default, intended to start as quickly as possible. Batch queries, requested with bq query --batch or the BATCH priority, have lower priority and stay queued when the reservation is busy; once started they run like interactive queries. Queue timeouts are configured separately for each priority, and the priority column in INFORMATION_SCHEMA.JOBS records which one was used.
Monitoring and troubleshooting processes
- Observability combines several sources. In BigQuery, the INFORMATION_SCHEMA.JOBS views record every job, including failed ones, with slots and bytes; Cloud Logging holds the audit logs; and the operational health and resource utilization charts summarize slots, concurrency and errors per reservation. In Dataflow, Cloud Monitoring provides metrics such as system lag and custom Counter metrics for alerting.
- To monitor planned usage, combine Cloud Billing budgets with actual and forecasted spend thresholds, which alert without blocking, and the slot estimator, which uses historical usage to size reservations. The quota page counts allocated slots, including idle ones, while the utilization charts show consumed slots; the Analysis Slots Attribution line item attributes reservation costs to each project.
- In BigQuery, a 403 with rateLimitExceeded is a short-term limit fixed by retrying with exponential backoff, while quotaExceeded is a longer-term limit that calls for reviewing the workload; INFORMATION_SCHEMA.JOBS and Cloud Audit Logs help diagnose it. Custom QueryUsagePerDay and QueryUsagePerUserPerDay quotas cap on-demand spend, whereas an alerts-only Cloud Billing budget does not cap it.
- BigQuery reservations group slots into isolated pools assigned to projects, folders or organizations; an assignment to None puts a project back on on-demand. Batch queries have lower priority than interactive ones, and a maximum concurrency target guarantees minimum slots per query. In Dataflow and Managed Service for Apache Spark, capacity is managed by autoscaling the workers.
Maintaining awareness of failures and mitigating impact
- In streaming mode Dataflow retries bundles with failing elements indefinitely, so a dead-letter queue with branching outputs is advised; snapshots save state so updates can be rolled back. Managed Airflow recommends task retries, Managed Service for Apache Spark offers restartable jobs with failure limits, and Pub/Sub sets unacknowledged messages aside in a dead-letter topic.
- Dataflow uses all zones of the selected region by default, which makes jobs more resilient to zonal capacity shortages, and it should run in the region of its sources and sinks. A Managed Service for Apache Spark cluster lives in a single zone, even in High Availability mode. Managed Airflow offers multi-zone highly resilient environments and, for regional outages, a failover environment in another region loaded from snapshots.
- BigQuery time travel lets you query or restore changed or deleted data within a window of days, and deleted tables are recovered by copying them with the @ decorator; table snapshots keep states for longer. Cloud Storage recommends soft delete, Cloud SQL point-in-time recovery restores into a new instance, and Spanner extends version_retention_period, although PITR does not protect against dropping the database.
- Cloud SQL high availability replicates synchronously to a standby in another zone and fails over automatically; cross-region replicas are promoted manually for regional recovery. Memorystore for Redis Cluster needs at least one replica per shard to be multi-zone, Bigtable fails over automatically with multi-cluster routing, and BigQuery managed disaster recovery offers hard or soft failover.