Google Cloud Data Engineering Services

Data Engineering & Modern Data Platforms · Lesson 56 · Modern Data Platforms and Cloud

Lesson Overview

A Google Cloud design for TechLearningPro often arrives as one box labeled BigQuery, with Pub/Sub, Dataflow, Dataproc, and Cloud Composer drawn around it because the icons were on the same slide. BigQuery can hold the completion mart. It should not hold every workload by accident, and the services around it are not free decorations on the Kafka topics this course already runs.

AWS Data Engineering Services and Azure Data Engineering Services assigned four workloads to one owner each. This lesson assigns them again, on Cloud Storage, BigQuery, Dataflow, Pub/Sub, Dataproc, and the managed Kafka service when the Kafka contract must stay. The workloads are unchanged.

1. The completion mart, grain (learner_id, lesson_id, attempt_number), definition completion-v4, on the board by 06:00, with about forty Monday readers.
2. One day of raw progress for investigation, never certified as the rate.
3. A provisional activity tile at five minutes, labeled provisional.
4. One enrollment change feed, the Debezium slot from CDC with Kafka and Debezium, unless a cutover retires it.

Volumes stay put: about 80 million progress events a day, about 40 GB of compacted Parquet, about 200,000 enrollment changes, about 120,000 purchase lines. Payment data stays out of the lake.

BigQuery is the strongest default serving engine in this set when the team is willing to make SQL the transform and to separate slot reservations between the load and the board. That is a workload judgment. It is not a reason to rewrite a correct PySpark job on day one, and it is not a reason to replace Kafka with Pub/Sub because both move messages.

Learning Objectives

By the end of this lesson you will be able to:

1. Separate Cloud Storage objects from BigQuery native storage, and say when a repeated query should stop reading the object each time.
2. Explain on-demand bytes billed versus slot reservations and editions, and assign the board and the ELT job to different reservations.
3. Partition the mart on event_date, cluster on the filter columns, and turn on a required partition filter.
4. Write a day-scoped MERGE that stays idempotent on the completion grain and prunes the partition it updates.
5. Place Dataflow and Beam on the provisional tile, and keep the certified rate on a scheduled merge.
6. State Pub/Sub's delivery contract, including what exactly-once delivery covers, and what a BigQuery sink still has to deduplicate.
7. Choose Dataproc Serverless for existing Spark, Dataflow for a Beam streaming graph, and BigQuery SQL when the transform is relational.
8. Decide among the current Kafka cluster, Google Cloud Managed Service for Apache Kafka, and Pub/Sub without a dual-publish.
9. Treat Datastream as a single-slot replacement for Debezium, with the same WAL page as lesson 46.
10. Record reopen conditions in the same table shape as the AWS and Azure lessons.

Prerequisites

• Lessons 54 and 55, so the four-workload table is familiar and this lesson does not re-argue the questions.
• Data Formats and Serialization and the lake lessons. Cloud Storage is a flat object store. A table commit is still a table commit.
• ETL and ELT Architecture. A scheduled MERGE is ELT. A Dataflow job that reimplements it is a second definition.
• Event Time, Windows and Late Data and Structured Streaming with Spark if the tile moves to Beam. Windowing concepts carry. The runner changes.
• Lesson 46 and Real-Time Data Quality and Monitoring. Slot health and freshness cursors do not get replaced by a green Dataflow job.
• /learn/kafka for the log. /learn/postgresql for enrollment.

Detailed Core Concepts

Cloud Storage is the object layer, without a directory rename

Objects live in buckets under gs://tlp-data-prod/raw/progress/event_date=2026-11-10/part-00017.snappy.parquet. The prefix looks like a directory. It is a name. There is no hierarchical namespace and no directory rename of the kind ADLS Gen2 gives Spark. Writers land new objects. A table format or a metadata pointer publishes a set of them. Deleting the old objects before the new ones are the ones the table names is how a day vanishes. The lakehouse lessons still own that commit. This lesson only insists that the bucket is not asked to append to a Parquet object.

Uniform bucket-level access should be on. Object ACLs then stay out of the design, and IAM on the bucket is the grant. Storage classes are Standard, Nearline, Coldline, and Archive, plus Autoclass if you want the service to move objects by observed access. Standard covers the incident window. Colder classes cover bytes you can wait on, with minimum storage duration and retrieval behavior you will price later. Autoclass is a policy, not a backup. Versioning or soft delete, enabled on purpose, is what an overwrite needs. A second region is a restore drill you have done, or it is only a bill.

The raw progress landing from Kafka Connect can target this bucket the same way it targeted S3 or ADLS. Changing clouds does not require a new bus.

BigQuery stores columns and sells either bytes or slots

A native BigQuery table is columnar storage inside the service. An external table is a schema over Cloud Storage objects. The first query of an external table reads objects. The fiftieth query of an external table reads objects again. A native table, loaded or created with CREATE TABLE AS SELECT or a MERGE, reads BigQuery storage, which is the right place for a mart the board hits all morning. External tables remain the right place for a raw day you touch rarely, and for checking a landing before you publish.

Compute is slots, the workers the query engine uses. Two commercial shapes sit on that fact.

• On-demand pricing bills bytes processed, with a minimum per table referenced. A partition-pruned query against a few columns of the mart processes those bytes. SELECT * on an external JSON year processes a different number. The unit price is public and regional. The design lever is bytes.
• Editions and reservations sell slot-hours. You attach a reservation to a project, a folder, or an administration project, with a baseline and a maximum. Autoscale moves between them. The board project and the ELT project should not share one reservation if the merge is allowed to consume every slot the dashboard needs at 05:50. Two reservations are workload isolation. They are not two marts.

A reservation with a high maximum and a shared assignment will look cheap in a diagram and queue the board behind a backfill. A reservation left at a high baseline all weekend is the idle-cluster problem in slot clothing. Baseline is the spend you accept when the office is empty. Maximum is the spend you accept on Monday. Confirm current edition names and floors on the price list when you buy, because the packaging has changed and the mechanism, slots versus bytes, has not.

Partition the mart by event_date. Daily partitions for a few years are a few hundred partitions, which is a healthy count. Hourly partitions on this table multiply the metadata and produce small partitions the board never filters to. Cluster by the columns inside the partition that selective queries filter, up to four, such as lesson_id and learner_id. Clustering does not replace the partition. A filter on learner_id without event_date still scans partitions. require_partition_filter = true makes that scan a query error. Turn it on for the mart and for raw external tables that are large enough to hurt.

Partition expiration deletes old partitions. Use it on raw staging you have promised to drop. Do not set a short expiration on completion_attempt because a template said every table should expire. The mart's retention is a product decision lesson 61 will tie to policy. Time travel on BigQuery is a window of days, configurable inside the documented range, for recovering a dropped table or a bad merge. It is not a year-long archive. Fail-safe recovery beyond the time-travel window is a support path, not a query you can schedule.

The Storage Write API is the current streaming write path. A default stream is at-least-once: a retry can append twice, so the reader dedupes. A committed stream uses offsets so the client can retry without appending a second copy, which is application-level exactly-once into that stream when the client keeps the offset. Batch MERGE from a staged table does not need either stream. Use the write API for the provisional tile or for a landing table. Use SQL for the certified grain.

Materialized views can hold the Monday aggregate when the view's refresh rules match the query. BI Engine can cache a dashboard's working set. Both are serving accelerators. They are certified only if they read the published definition. A materialized view that computes its own idea of completion is another mart.

Authorized views and row access policies are the grant tools. An authorized view runs with access to the base table while the analyst's identity does not have that access. A row access policy filters seller_id from the caller's identity. Lesson 57 tests the bypass. This lesson's rule is that the board identity cannot read the raw external table of progress if that table is broader than the mart.

Dataflow runs Beam, which the tile may need and the mart does not

Dataflow is the managed runner for Apache Beam. A pipeline is a graph of transforms. Batch and streaming are both supported. They are not the same job with a flag flipped after the fact: the source, the window, the trigger, and the sink have to match the clock you mean. Streaming Engine moves shuffle off the workers you size by hand. Workers still have a machine type, and autoscaling still has a maximum you should set so a hot key cannot hire an unbounded bill.

The provisional tile is the Dataflow-shaped workload. Progress events have event time. The tile wants five-minute windows and an honest lateness policy, which lessons 32 and 45 already designed. Beam can express that window. A BigQuery scheduled query every five minutes can also compute a provisional count from a landing table, with more latency jitter and less window machinery. Choose Dataflow when the window, the late data, and the triggers are the product. Choose the scheduled query when the provisional number is "rows landed in the last five minutes" and a window implementation would be theater.

The certified completion rate is a daily definition with a version. It belongs in a MERGE you can diff in review. A streaming job that updates the same table continuously will fight the publish mark, and a late event will move yesterday's rate during today's board meeting. Land late facts into the next publish, or into the provisional table. Do not let the tile write definition_version = completion-v4.

Drain and cancel are different stops. Drain finishes in-flight work and then stops. Cancel stops. Use drain when the pipeline's sink must flush a window you still want. Use cancel when the output is wrong and more output makes it worse. A job labeled healthy in the console can still be falling behind. Backlog and data freshness on the job are the cursors, and lesson 47's rule still applies: the product's freshness is the applied event time, not the runner's happiness.

Templates that copy Pub/Sub into BigQuery are starting points. The completion rule does not live inside a template parameter you have not reviewed.

Pub/Sub, managed Kafka, and the log you have

Pub/Sub is a managed message bus. A topic has subscriptions: pull, push, a BigQuery subscription, a Cloud Storage subscription. The default delivery contract is at-least-once. Ordering keys give order for one key and reduce parallelism for that key. A dead-letter topic takes messages after a maximum number of delivery attempts. Seek, inside the retention window, replays messages still retained. Retention is the rewind. It is not infinite.

Exactly-once delivery is a feature of pull subscriptions in a single region, with supported client libraries. It stops a successfully acknowledged message from being redelivered by that subscription. It does not make a downstream BigQuery append exactly-once by itself. If the subscriber acknowledged after a write that later is ambiguous, or a second path writes the same event, the table still needs event_id. Use the feature when you are on pull in one region and your client supports it. Keep the primary key anyway.

A BigQuery subscription lands messages into a table without a service you operate. It is a landing hose with a table shape. Dedup into the mart with SQL. Do not point the board at the subscription table.

Pub/Sub is not Kafka. It does not give you a compacted topic, a transactional producer, or the Connect and Streams programs from Module 4. Google Cloud Managed Service for Apache Kafka exists so a team that needs that contract can run Kafka as a managed cluster on this cloud. The pilot is the same list as the Event Hubs pilot: transactions, compaction if you use it, Streams state, the schema registry, and Connect. A new topic that only GCP services produce, and that no Kafka client consumes, can be Pub/Sub. The progress topic is not that topic until the pilot says otherwise.

Dual-publishing each progress event to Kafka and to Pub/Sub creates two cursors and two landings. They will disagree during a retry. One log.

Dataproc, Datastream, and Composer

Dataproc and Dataproc Serverless for Spark run the Spark you already have. Use them when Module 3's jobs are the reviewed artifact and a BigQuery SQL rewrite is a risk you have not accepted. Do not run Dataproc beside Dataflow for the same definition. Serverless batches fit a daily job that should not leave a cluster up. A standing cluster fits a workload that is busy enough to justify it, which this morning mart, alone, is not.

Datastream can CDC PostgreSQL into Cloud Storage or BigQuery using logical replication. The slot on the primary can still retain WAL. Choose Datastream when you are retiring the Debezium topic and every subscriber has a new home. Keep Debezium when the topic is the contract. Two slots are the same outage as on the other clouds.

Cloud Composer runs Airflow and bills an environment while it exists, including quiet hours. Move the DAGs from lessons 20 and 21 there when Airflow remains the publisher and you want it managed. A single daily MERGE does not need Composer. A BigQuery scheduled query, or Cloud Scheduler calling a workflow, is enough until the graph has the dependencies Airflow was keeping. Two schedulers that both merge completion_attempt are two publishers.

Selection

Architecture and Design Diagrams

mermaidCopy
flowchart LR
  subgraph sources [Sources]
    PG["PostgreSQL enrollment"]
    APP["Progress producers"]
  end
  subgraph log [One log]
    K["Kafka or managed Kafka"]
  end
  subgraph gcs [Cloud Storage]
    RAW["raw/progress"]
  end
  subgraph bq [BigQuery]
    EXT["External raw table"]
    STG["Staging table"]
    MART["completion_attempt"]
  end
  subgraph iso [Reservations]
    ELT["ELT slots"]
    BOARD["Board slots"]
  end
  PG --> K
  APP --> K
  K --> RAW
  RAW --> EXT
  EXT --> STG
  STG --> MART
  ELT --> STG
  BOARD --> MART
  MART --> MON["Monday board"]
  EXT --> INV["Single-day investigation"]

The tile is a separate graph so its writes cannot be mistaken for the mart.

mermaidCopy
flowchart LR
  TOPIC["Progress topic or a dedicated tile subscription"] --> DF["Dataflow Beam window"]
  DF --> PROV["provisional.progress_tile"]
  PROV --> TILE["Tile labeled provisional"]
  MART2["completion_attempt"] --> BOARD2["Certified board"]

If the source is still Kafka, the Dataflow Kafka IO reads that topic. Pub/Sub appears in the diagram only after a pilot replaces the log, or for a new producer.

Technical Explanation

Bytes, partitions, and the dry run

Before a scheduled query exists, a dry run returns bytes processed and does not run the query. The bq CLI exposes it with --dry_run. The number is the design review. A date predicate on a partitioned native table drops the bytes to the partitions touched, and column projection drops them again to the columns named. A dry run of SELECT * without event_date, against a table that requires a partition filter, fails. That failure is the control working.

Clustering reduces bytes further when the filter is selective inside the partition. It is not a guarantee that a learner_id lookup reads one row group the way a primary-key lookup does in PostgreSQL. It is a layout that helps. Measure the dry run before and after a cluster change on a realistic query. Do not add four cluster columns because four are allowed.

A query that joins the mart to a raw external table can process the raw scan even when the mart prune is perfect. The board's SQL is not allowed to reference the raw external table. Give the board a dataset it can read and leave raw in a dataset it cannot.

MERGE and the grain

Staging holds one day, already deduplicated on (learner_id, lesson_id, attempt_number). The merge updates that partition and inserts new grains. A second run with the same staging rows updates the same keys to the same values. That is the idempotency. Staging that contains two rows per grain makes the merge reject the match or apply an arbitrary pair, depending on the statement. Deduplicate in staging and fail the publish if the duplicate count is not zero.

require_partition_filter means the merge's ON clause or a search condition must prune event_date. A merge that touches every partition to update one day is a mistake the option is there to make loud.

The scheduled query runs in the ELT project, on the ELT reservation. On success, a view published.completion_attempt is what the board project can read. The view's authorizing dataset does not grant the base staging table. Publishing is "the view's definition points at data that passed the checks," which you can implement as an authorized view over the mart plus a row that records the published date in a small table the view joins. The mechanics can be a view. The rule is that a half-finished merge is not what forty people select.

Streaming writes and duplicates

The Storage Write API default stream can retry a row into existence twice. The tile table keeps event_id and the tile's query dedupes. The certified mart does not read the tile table. If you use committed streams, the client offset is what prevents a retry from appending twice on that stream. A second pipeline that also inserts the same event_id does not know about that offset. The key is still mandatory.

Pub/Sub exactly-once delivery, where you enabled it and your client qualifies, covers redelivery of acked messages on that subscription. A BigQuery subscription is a different subscriber with its own contract. Read the subscription type before you claim the table is duplicate-free. The grain check is the claim you can defend without a footnote.

Dataflow freshness is not mart freshness

A streaming job's backlog is how much input it has not processed. The tile's promise is about event time of closed windows, including the grace from lesson 45. Page on the tile when closed windows are late against that promise. Page on the mart when the published event_date is late against 06:00. A single "pipeline lag" chart that adds those numbers will page the wrong person. Lesson 47 already separated cursors. Dataflow's job state is one more green light that is not a cursor.

A hot key on a window, such as every event in one popular lesson tumbling into one pane, will slow a worker and raise autoscaling. Cap the workers. Skew is a data shape. Buying a larger maximum without a cap is how the tile pays for a launch traffic spike with idle workers afterward if you forget to set the minimum back. Minimum workers are a baseline spend, the same idea as a reservation baseline.

Datastream and the primary

Datastream's PostgreSQL source uses a replication slot. If the stream pauses, WAL can grow on the primary. The task status in the console is not the disk measurement. The query lesson 46 uses remains the page. A BigQuery target that is "almost caught up" can still be a slot that never confirms. One slot means you have turned Debezium's slot off after subscribers moved, not that both are "temporary."

Failure behavior worth fixing in design

A scheduled query that runs twice because Composer and BigQuery's scheduler both fire it will merge twice. If the merge is idempotent, the rows survive and the cost doubles. If someone "optimized" the publish as INSERT INTO the mart, the rows duplicate and the grain check is the only alarm. Prefer the merge. Prefer one scheduler.

A dry run nobody looked at becomes a job that processes terabytes on Monday because the filter was a timestamp function the planner could not prune. DATE(event_time) = ... on a table partitioned by event_time can disable pruning when the expression hides the column. Filter on the partition column directly. Confirm with the dry run, not with hope.

Canceling a streaming job that the tile needs, because a deploy felt safer than a drain, drops the in-flight window. The tile then shows a hole that looks like learner behavior. Drain, or accept the hole in the provisional label and backfill the window from the log. The log is why Kafka retention still matters after Dataflow exists.

Practical Implementation or Design Walkthrough

Problem

Publish tlp.completion_attempt in BigQuery by 06:00. Raw Parquet is in Cloud Storage. The board reads an authorized view on a reservation of its own. Exploration uses on-demand in another project and cannot select the raw dataset without a date. The provisional tile is a Dataflow job writing provisional.progress_event, and it is not allowed to write the mart. Kafka stays. Pub/Sub is not introduced for progress in this walkthrough.

Native mart and raw external table

sqlCopy
CREATE SCHEMA IF NOT EXISTS tlp
OPTIONS (location = 'US');

CREATE OR REPLACE TABLE tlp.completion_attempt (
  learner_id STRING NOT NULL,
  lesson_id STRING NOT NULL,
  attempt_number INT64 NOT NULL,
  completed_at TIMESTAMP,
  event_date DATE NOT NULL,
  definition_version STRING NOT NULL
)
PARTITION BY event_date
CLUSTER BY lesson_id, learner_id
OPTIONS (
  require_partition_filter = TRUE,
  description = 'Grain (learner_id, lesson_id, attempt_number). definition completion-v4.'
);

CREATE OR REPLACE EXTERNAL TABLE tlp.progress_raw (
  event_id STRING,
  learner_id STRING,
  lesson_id STRING,
  event_time TIMESTAMP,
  event_type STRING,
  position_seconds INT64
)
WITH PARTITION COLUMNS (
  event_date DATE
)
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://tlp-data-prod/raw/progress/*'],
  hive_partition_uri_prefix = 'gs://tlp-data-prod/raw/progress',
  require_hive_partition_filter = TRUE
);

No partition expiration is set on the mart. Raw object's lifecycle is a bucket rule, reviewed with retention, not a surprise on the certified table. Confirm the external-table partition syntax against the current BigQuery documentation when you create it. The design intent is a date column taken from the prefix and a required filter.

Stage one day, then merge

Assume a reviewed SQL statement or a Dataproc job has filled tlp.completion_attempt_staging for one event_date, with definition_version = 'completion-v4', and has already collapsed the grain. The publish:

sqlCopy
-- Fail the task if this returns rows, before MERGE.
SELECT learner_id, lesson_id, attempt_number, COUNT(*) AS n
FROM tlp.completion_attempt_staging
WHERE event_date = DATE '2026-11-10'
GROUP BY 1, 2, 3
HAVING COUNT(*) > 1;

MERGE INTO tlp.completion_attempt AS t
USING (
  SELECT * FROM tlp.completion_attempt_staging
  WHERE event_date = DATE '2026-11-10'
) AS s
ON t.event_date = DATE '2026-11-10'
 AND s.event_date = DATE '2026-11-10'
 AND t.learner_id = s.learner_id
 AND t.lesson_id = s.lesson_id
 AND t.attempt_number = s.attempt_number
WHEN MATCHED THEN UPDATE SET
  completed_at = s.completed_at,
  definition_version = s.definition_version
WHEN NOT MATCHED THEN INSERT (
  learner_id, lesson_id, attempt_number,
  completed_at, event_date, definition_version
) VALUES (
  s.learner_id, s.lesson_id, s.attempt_number,
  s.completed_at, s.event_date, s.definition_version
);

The constant event_date predicate is what pruning and require_partition_filter need. A rerun merges the same day again. Follow with a count of the partition compared to staging. Publish the view only after that count matches.

Dry-run the board's query in review:

textCopy
bq query --dry_run --use_legacy_sql=false \
  'SELECT lesson_id, COUNT(*) AS attempts
   FROM tlp.completion_attempt
   WHERE event_date BETWEEN DATE "2026-11-03" AND DATE "2026-11-10"
     AND definition_version = "completion-v4"
   GROUP BY 1'

Record totalBytesProcessed. A number that looks like the whole table means the filter did not prune. Fix the SQL before it is scheduled. The ELT merge runs under the ELT reservation. This select, from the board project, runs under the board reservation. The exploration project is a third assignment, on-demand, without access to scan raw without a date.

Provisional tile, not the mart

This pipeline is a sketch you can run on the Dataflow runner with apache-beam 2.59 or newer. It writes the provisional dataset. It does not write tlp.completion_attempt.

pythonCopy
"""Provisional five-minute landing. Not completion-v4."""
import json
import apache_beam as beam
from apache_beam.options.pipeline_options import PipelineOptions


def parse(payload: bytes) -> dict:
    row = json.loads(payload.decode("utf-8"))
    return {
        "event_id": row["event_id"],
        "learner_id": row["learner_id"],
        "lesson_id": row["lesson_id"],
        "event_time": row["event_time"],
    }


def run() -> None:
    options = PipelineOptions(
        project="tlp-data",
        region="us-central1",
        runner="DataflowRunner",
        temp_location="gs://tlp-data-prod/tmp/dataflow",
        streaming=True,
    )
    table = "tlp-data:provisional.progress_event"
    with beam.Pipeline(options=options) as pipeline:
        (
            pipeline
            | "Read" >> beam.io.ReadFromPubSub(
                subscription="projects/tlp-data/subscriptions/progress-tile"
            )
            | "Parse" >> beam.Map(parse)
            | "Write" >> beam.io.WriteToBigQuery(
                table=table,
                method=beam.io.WriteToBigQuery.Method.STORAGE_WRITE_API,
            )
        )


if __name__ == "__main__":
    run()

If progress still lives in Kafka, replace the Pub/Sub read with the Beam Kafka IO pointed at that topic and do not create the subscription "just so the example matches." The snippet uses Pub/Sub because that IO is the common Dataflow landing, and the decision record has to say which log is real. The write method can give this sink exactly-once behavior on Dataflow for retries of this pipeline. It does not dedupe a second writer. The tile view should still SELECT distinct event_id. The table is provisional. Windowing and allowed lateness belong in the pipeline when the tile's promise is a closed five-minute window. Adding WindowInto without a trigger and a lateness you copied from lesson 45 is how the tile silently drops late events. Bring those parameters across on purpose, or keep this job as a landing and compute the window in SQL where you can read it.

Create the Pub/Sub subscription only after the log decision. A Kafka source does not need a shadow topic.

Operations

One scheduler. If that is a BigQuery scheduled query, Composer is not also merging. Alerts: the scheduled query failed, the partition count disagreed with staging, the dry-run bytes for the board query jumped by an order of magnitude, Dataflow backlog for the tile exceeded the provisional promise, and the PostgreSQL slot retained WAL. Job success is not in that list as a freshness metric. The published partition's event_date against the clock is the mart freshness.

Enterprise Real-World Scenarios

TechLearningPro puts the mart in BigQuery and leaves the log alone

The account is Google Cloud. Analysts already want SQL. The team loads curated attempts into tlp.completion_attempt, partitions and clusters as above, and schedules the merge on the ELT reservation. The board project has a smaller reservation and an authorized view. Exploration is a third project, on-demand, with the external table's required partition filter. A Dataproc Serverless batch remains only for the one PySpark quality check that has not been rewritten, and it writes staging, not a private mart.

Someone proposes Pub/Sub, a BigQuery subscription, and Dataflow as the new progress platform in the same quarter. The review keeps Dataflow only if the tile's windows earn a runner. The certified path stays the merge. Kafka stays until the managed Kafka pilot or a Pub/Sub pilot passes the Module 4 checklist, and only one of those replaces the topic. Datastream is written down as the enrollment reopen, not deployed beside Debezium.

The first month's evidence is dry-run bytes, slot-hours by reservation, and whether 06:00 held. That evidence is what lesson 58 turns into a cost review. If the board's reservation sits at a high baseline all week to save Monday, the baseline is the waste. If on-demand exploration scans raw because a filter was wrapped in a function, the bytes are the defect.

Northline can live in one BigQuery project layout and still needs two reservations

Northline's sellers each query their own orders, about 500,000 lines a day, and finance closes the month. BigQuery row access policies on seller_id, using the caller's identity, are a direct fit. Finance is a group that the policy allows to see every seller, or finance reads a close table the policy does not cover because the close table is a published monthly grain with its own grants. The portal identity is tested by selecting another seller's seller_id and getting zero rows. A looker or BI filter on seller_id is not the test.

The close and the portal share the mart's definition and do not share a reservation if the monthly close is heavy enough to queue instructors. Two reservations, one table. A Dataflow job that also aggregates GMV "in real time" into a second certified table will disagree with the close on refunds that arrive late. Real time, if it exists, is provisional. The close is the merge with close_month and a definition version.

Composer enters Northline's design when the close depends on a file from a bank, a dunning extract, and the order table, with the graph Airflow already expresses. It does not enter because a single merge felt lonely without a cluster. The environment's idle cost is a line you accept for the graph, or you keep the scheduled query.

Best Practices

• Put the certified grain in a native partitioned table. Leave rare raw reads on external tables with a required date filter.
• Split ELT, board, and exploration across reservations or on-demand projects. One maximum slot cap for all three will spend Monday on the loudest job.
• Filter on the partition column itself. Dry-run the board query and keep the byte count in review.
• Merge a deduplicated staging day. Fail the publish when the grain is not unique.
• Keep streaming writes in a provisional dataset with event_id. Do not let them update completion-v4.
• Cap Dataflow workers. Drain when the in-flight window is still wanted.
• One log: Kafka, managed Kafka, or Pub/Sub, after a pilot that includes the features you use.
• One enrollment slot: Debezium or Datastream, with the WAL page unchanged.
• Add Composer when the DAG needs it. A scheduled query is a complete publisher for a single merge.
• Write reopen conditions in the same shape as lessons 54 and 55.

Common Mistakes and Anti-Patterns

The board queries the external raw table. Every refresh rereads objects, bytes stay high, and two analysts can define completion differently. The board reads the published native table.

A function wrapped around the partition column. The dry run shows a full scan, the filter "looks" selective, and require_partition_filter may not save you if a filter is present and still not prune-friendly. Compare bytes. Filter the column directly.

One reservation for backfill and dashboards. The backfill is correct and the board is late. Separate assignments. A high baseline on the board reservation all weekend is the other version of the same mistake.

Dataflow as the certified mart. Late events move yesterday's rate in front of the board. The tile has its own table and its own label. The merge owns completion-v4.

Pub/Sub beside Kafka for the same event. Two deliveries, two landings, a retry on one side. One log. Exactly-once delivery on a pull subscription does not reconcile a dual-publish.

A BigQuery subscription table as the official grain. It is a landing. It is at-least-once unless you have proven otherwise for that subscription type. Dedup in SQL into the mart, or do not certify the landing.

Partition expiration copied onto the mart. Raw lifecycle is not mart retention. Time travel will not get a year back. Set expiration only where the contract is to delete.

Datastream and Debezium together. Two slots, one primary disk. Cut over, then drop the old slot.

Composer plus a scheduled query, both merging. Double runs. If the publish is an insert, duplicate grains. One scheduler, and a merge regardless.

An unbounded Dataflow max workers setting. A hot lesson launch scales the tile farther than the provisional product is worth. Set a maximum and an alert on backlog.

Troubleshooting and Design Review

The dry run processes far more bytes than the day's partition. The predicate does not reference event_date directly, or the query joins an unpartitioned raw table. Read the bytes and the referenced tables. Fix the SQL. Do not raise the reservation to hide it.

MERGE fails the partition filter check. Add a constant or range predicate on the target's event_date. A filter only on the staging alias does not prune the target.

The grain check fails after a "successful" landing. Staging was not deduplicated, or two schedulers inserted. Find the second writer before you delete rows by hand. The merge should be the only writer of the mart.

The tile is current and the board is yesterday. Those are different cursors. Check the scheduled query and the published view. Dataflow freshness does not move event_date on the mart.

Dataflow backlog grows after a deploy. See whether the deploy canceled instead of drained, and whether a hot key is pinning a pane. Worker count at the maximum with backlog still rising is a skew or a sink limit, not a reason to remove the cap blindly.

Pub/Sub shows a growing backlog on one ordering key. That key is serial. A popular lesson_id used as the ordering key will stick one learner's neighbors behind that lesson. Order only if the product needs order for that key. Progress landing usually needs event_id idempotency more than total order.

Datastream is healthy and the primary disk is not. Query the slot. Stream state is not WAL.

Exploration queries queue the board. They are on the board reservation, or they are in the board project. Move them. On-demand in an exploration project is the isolation.

Design review:

• Which workload does this service own?
• Native table or external objects, and which identity can read each?
• Which reservation runs the statement?
• Does the certified path have one writer?
• If Kafka moved, which features passed? If enrollment moved, is there one slot?
• What is provisional, in the table name and in the label a human sees?

Interview Questions and Answers

Q1. When do you choose BigQuery on-demand, and when a slot reservation, for this mart?

I use on-demand for exploration and for certified queries whose bytes I have dry-run and whose concurrency is low. I use a reservation when Monday has many concurrent readers or I need a ceiling that a backfill cannot steal. I split ELT and the board into two reservations so the merge cannot eat the dashboard's slots. Baseline is what I pay when it is quiet. Maximum is what I pay at the peak. I do not put exploration on the board reservation to avoid creating a project.

Q2. How should the completion table be partitioned and clustered?

Partition by event_date, daily, and require a partition filter. Cluster by the selective columns inside a day, such as lesson_id and then learner_id, within the four-column limit. I do not partition by learner_id. Cardinality would be a partition explosion. I do not use hourly partitions unless a real query filters to an hour and the volume per hour is large enough to matter. I check pruning with a dry run before and after I add a cluster column.

Q3. What does a committed stream on the Storage Write API give you that a retried default stream does not?

A default stream is at-least-once. A retry can append a second copy, so event_id has to save you. A committed stream uses offsets so that same client can retry without a second append. That is exactly-once for that stream's writer, if the client keeps the offset. It does not dedupe some other job that inserts the same event. I still keep event_id. I use streams for landing and for the provisional tile. The certified grain is a MERGE from staging.

Q4. Why is the daily completion rate a scheduled merge instead of a Dataflow job?

The rate is a versioned relational definition with a publish time of 06:00. A merge of one deduplicated day is reviewable and idempotent. Dataflow earns its place on the provisional tile, where event-time windows and lateness are the product. A streaming job that updates the certified table will move closed days when late events arrive. I keep that movement in the provisional dataset, or I wait for the next publish, on purpose.

Q5. What does Pub/Sub exactly-once delivery actually cover?

Where the feature applies, a pull subscription in a single region, with a supported client, does not redeliver a message that was acknowledged. It does not give me Kafka transactions, and it does not make every sink idempotent. A BigQuery subscription is its own contract. I still dedupe on event_id in any table I certify. I do not dual-publish to Kafka and Pub/Sub and then cite exactly-once as the reconciliation.

Q6. Why can Cloud Storage not be the system that appends progress events into one Parquet object?

Objects are replaced as a whole. There is no append API a second writer can trust, and there is no directory rename to publish a set of files the way ADLS Gen2 can. Producers write new objects. A manifest, a native BigQuery load, or a table format commits them. I compact small landing objects before repeated scans. I do not ask the bucket to behave like a table.

Q7. A raw external table was cheap on the first query. Why is the fifth query of the morning a design problem?

Each query reads the objects again. On-demand bills those bytes each time. Forty board refreshes against external raw are forty scans. A native partitioned table reads columnar storage and prunes. I load or merge into native storage when the query repeats. I leave the external table for a rare single-day check, with a required partition filter so a mistake is an error.

Q8. When does existing PySpark stay on Dataproc instead of moving to Dataflow or to SQL?

When the reviewed artifact is PySpark and a rewrite is risk without a measured benefit. Dataproc Serverless runs that job and writes staging. Dataflow is for a Beam graph, usually the tile's windows, not for hosting an unchanged Spark program. BigQuery SQL replaces the Spark job after someone has compared the definition and the bytes, not because the cloud slide preferred SQL. I do not run all three for one grain.

Q9. How do reservations relate to projects, and why does the ELT job need its own?

A reservation is a pool of slots you assign to projects or folders. The project the scheduled query runs in draws those slots. If ELT and the board share a pool, a backfill at 05:40 queues the dashboard even when both queries are well written. I assign the ELT project to a reservation with a maximum I accept for the merge, and the board project to another with a baseline that covers Monday's readers. Exploration stays on-demand in a project that cannot read raw without a date.

Q10. When would Datastream replace Debezium?

When every subscriber of the enrollment topic has moved, deletes and the target table are designed, and I can turn the old slot off. Datastream still uses logical replication, so a paused stream can retain WAL, and the primary disk page stays. I do not run Datastream beside Debezium as a safety net. Two slots are two ways to fill the disk. A green stream status does not replace the slot query.

Q11. What do you check when a "filtered" query still processes the whole table?

The dry-run byte count and whether the predicate is on the partition column. Wrapping the column in a function, filtering only through a join to a small table the planner cannot push down, or selecting from an external table the board should not see, are the usual causes. require_partition_filter catches the missing predicate. It does not catch a present predicate that does not prune. I keep the byte count next to the SQL in review.

Hands-On Exercises

Exercise: One BigQuery mart, two reservations on paper, one provisional path

Problem statement. Using BigQuery or a local stand-in you label honestly, publish one day of completion_attempt and show that a rerun does not double the grain. If you cannot create two reservations, write the project-to-reservation assignment and which statements run where. Do not create a second bus for the sample. The provisional path may be SQL on a landing table if you cannot run Dataflow. Label it provisional.

Tasks.

1. Complete the owner table for all four workloads, including the Kafka versus Pub/Sub reopen condition.
2. Create the partitioned, clustered table with a required partition filter. Show a query that fails without event_date, and a dry run or an explained plan for a one-week aggregate.
3. Load staging, reject duplicate grains, merge one day, and rerun the merge. Counts stay stable.
4. Show the board identity cannot read the raw external table. State how the authorized view or dataset grant enforces that.
5. Describe the tile's writer and the table it is allowed to touch. Include one sentence on drain versus cancel.
6. Name mart freshness versus tile freshness, as two different pages.

Expected deliverables. Decision record, DDL, merge, rerun evidence, grant note, reservation map, freshness note.

Evaluation rubric.

Lesson Summary

Cloud Storage holds objects and does not publish a table. BigQuery native storage holds the completion mart: partition on event_date, cluster on the selective columns, require a partition filter, merge one deduplicated day, and dry-run the board query before it is scheduled. On-demand bills bytes and fits exploration. Reservations bill slot-hours and must isolate the merge from the board. External tables are for rare reads, not for forty refreshes. Dataflow runs the provisional window when Beam is earning its keep, writes a different dataset, and is stopped with drain or cancel on purpose. Pub/Sub is a bus with at-least-once delivery and a limited exactly-once feature that still leaves the sink a key to enforce. Managed Kafka is how the Module 4 contract moves. Dataproc runs Spark you are not rewriting. Datastream replaces a slot. It does not sit beside one. Composer runs a real DAG and is idle money when a scheduled query is the whole publisher.

Data Platform Security and Governance takes the identities, keys, and row policies these three clouds left as names and turns them into controls you can test. The cost lesson prices the reservation baseline, the unscanned claim that was actually a full scan, and the second mart. DataOps and Data Platform Engineering is where one of these decision records becomes the platform you operate, and the capstone is where the reopen conditions have to survive contact with a single choice.

Further Reading

• Cloud Storage documentation, storage classes, Autoclass, and uniform bucket-level access.
• BigQuery documentation, partitioned tables, clustered tables, reservations and editions, the Storage Write API, materialized views, and MERGE.
• BigQuery dry runs for totalBytesProcessed before you schedule SQL.
• Dataflow documentation and the Apache Beam programming guide, including BigQueryIO write methods and draining a job.
• Pub/Sub documentation, delivery contracts, ordering keys, exactly-once delivery, and BigQuery subscriptions.
• Google Cloud Managed Service for Apache Kafka for the pilot list when the Kafka contract must move.
• Dataproc Serverless and Datastream for Spark you keep and for PostgreSQL CDC.
• Cloud Composer when the Airflow graph, not a single merge, is the publisher.
• Lessons 45, 46, 47, 54, and 55 for windows, the slot, freshness cursors, and the workload table.