GCP Data Engineer Pro cheat sheet

Latency decision rule: a bounded source with a minutes-to-hours SLA points to batch (Dataproc, Dataflow batch, BigQuery load); an unbounded source or a seconds-level SLA points to streaming (Pub/Sub plus Dataflow).

FlexRS applies to Dataflow batch jobs only, mixes preemptible and standard workers with delayed scheduling, and can cut batch cost by up to 40%; it cannot be used for streaming jobs.

Dataflow Shuffle offloads group-by and join state from worker disks to the Dataflow service, enabling smaller machine types and faster startup for batch pipelines.

Pub/Sub delivers at-least-once by default; exactly-once delivery is offered on pull subscriptions within one region, and true end-to-end deduplication still requires idempotent keys at the sink.

Datastream performs change data capture from MySQL, PostgreSQL, AlloyDB, SQL Server, or Oracle into BigQuery or Cloud Storage; Pub/Sub carries application-generated event messages instead.

Spanner is selected over Cloud SQL when the design needs horizontal write scaling, multi-region strong consistency, or the 99.999% multi-region SLA; Cloud SQL is regional with a single primary.

Service selection by shape: Bigtable for wide-column high-throughput single-key lookups above roughly 1 TB, BigQuery for analytical scans over large datasets, Cloud SQL for transactional workloads up to tens of TB.

Every pipeline stage should be idempotent - deterministic record keys plus MERGE or upsert - so replayed or duplicated records converge on the same final state.

Storage Transfer Service moves data from Amazon S3, Azure Blob, or HTTP endpoints into Cloud Storage; Transfer Appliance is used for offline petabyte-scale shipping.

A watermark is the point beyond which the system no longer expects earlier data; a window fires when the watermark passes the window end plus the allowed lateness.

Triggers control emission: the default fires once after the watermark; AfterWatermark.withEarlyFirings emits speculative panes and withLateFirings updates panes for data that arrives after the watermark.

Pub/Sub subscription retention is configurable from 10 minutes to 7 days (default 7 days); seek by timestamp or snapshot lets a subscription replay already-acknowledged messages.

The BigQuery Storage Write API gives exactly-once semantics with streams and lower cost than legacy streaming inserts; legacy inserts place rows in a streaming buffer that blocks DML.

Beam join choices: CoGroupByKey for inner, outer, and full joins on a shared key type; side inputs for small enrichment tables; CombinePerKey instead of GroupByKey for aggregations.

Dataflow autoscaling adds or removes workers based on input backlog; --numWorkers sets the initial pool and --maxNumWorkers caps the upper bound for cost control.

Flex templates package the pipeline as a container image in Artifact Registry and are the recommended form for parameterized, orchestrated, or event-driven launches.

Dead-letter pattern: route records that fail parsing or validation to a separate Pub/Sub topic or a BigQuery error table so the main pipeline keeps processing while failures are inspected.

BigQuery partitioning options are ingestion time (_PARTITIONTIME), a DATE, TIMESTAMP, or DATETIME column, or an INTEGER range; pruning occurs only when the filter targets the partition column.

Clustering sorts data within partitions on up to four columns and helps when filters are not on the partition column; partitioning and clustering both reduce bytes billed on on-demand pricing.

BigQuery long-term storage: partitions unmodified for 90 consecutive days are automatically billed at roughly 50% of the active storage rate, with no configuration change.

Cloud Storage minimum storage durations: Standard none, Nearline 30 days, Coldline 90 days, Archive 365 days; deleting earlier than the minimum bills the remaining duration.

Cloud Storage bucket names are globally unique; Object Versioning, retention policies enforced with Bucket Lock, and Autoclass each change cost and immutability behavior.

Bigtable keeps rows sorted lexicographically by row key, with a 4 KB key limit; monotonically increasing keys create hotspots, so hash or reverse the timestamp portion of the key.

Legacy BigQuery streaming inserts leave rows in a streaming buffer where UPDATE and DELETE are not permitted; DML requires loaded data or the Storage Write API.

External tables read files in place from Cloud Storage or BigLake with no load charge, but each query scans them, so columnar Parquet or ORC layouts substantially reduce bytes read.

Spanner relies on TrueTime for externally consistent transactions; primary keys should be UUIDs, hashes, or bit-reversed sequences so writes do not concentrate on one key range.

Cost control: a dry run reports bytes that would be processed, maximum_bytes_billed fails queries that would exceed the limit, and partition pruning plus clustering shrink scanned bytes.

QUALIFY filters the output of window functions without a subquery; ARRAY_AGG with ORDER BY combined with OFFSET or LIMIT is the standard pattern for deduplicating rows per key.

Approximate aggregations APPROX_COUNT_DISTINCT and APPROX_QUANTILES return fast estimates over very large tables with small, bounded error.

Materialized views refresh automatically, can be queried directly, and enable smart tuning so matching base-table queries are rewritten to use them; BI Engine adds sub-second dashboard latency.

BigQuery ML trains in SQL with CREATE MODEL and a model_type such as LINEAR_REG, LOGISTIC_REG, BOOSTED_TREE_CLASSIFIER, KMEANS, or ARIMA_PLUS; evaluate with ML.EVALUATE and score with ML.PREDICT.

INFORMATION_SCHEMA views such as JOBS_BY_PROJECT, TABLE_STORAGE, and PARTITIONS expose job history, storage footprint, and partition metadata directly through SQL.

Row-level security uses row access policies, while column-level security uses policy tags from a Data Catalog taxonomy; both filter or mask data for other users of the same table.

On-demand pricing bills per TiB scanned with 1 TiB of queries free per month; capacity pricing uses slot reservations with baseline and autoscaling maximum slots.

To change a running streaming pipeline use --update with a compatible topology; drain first when stateful transforms change, and cancel only when data loss is acceptable.

Key alerts: Dataflow system lag and worker CPU, Pub/Sub oldest unacknowledged message age, and BigQuery slot utilization or slow query duration.

Cloud Composer DAG tasks must be idempotent and retryable; use sensors, retries, and SLA misses rather than long sleep steps inside a task.

Orchestrator choice: Cloud Scheduler fires cron triggers, Workflows chains HTTP and API calls, and Composer schedules multi-step data pipelines with dependencies.

Failure handling: Pub/Sub dead-letter topics with maximum delivery attempts capture poison messages, and per-record error outputs capture row-level failures in Dataflow.

Dataproc supports idle TTL and scheduled deletion for ephemeral clusters, and graceful decommission for downsizing without killing running jobs.

Grant one service account per workload with predefined data roles rather than primitive owner or editor roles, and add CMEK and VPC Service Controls for regulated data.

Cloud Audit Logs record Admin Activity by default while Data Access logs must be enabled per service (BigQuery is an exception); Budgets alerts flag unexpected spend.