Choosing an ingestion approach: batch or streaming

On Tuesday, October 20, Ravi puts the source inventory from lesson 1 back on the screen. "We know what the data looks like and how to clean it," he says. "Now: how does each source get here?" The answer is different for each row of the table, and choosing well depends on a few questions that the exam asks again and again in different forms.

Batch, micro-batch and streaming

Batch ingestion moves data in chunks on a schedule: last night's till files, this month's supplier price lists, a one-time export of ten years of history. It is simple, cheap and easy to re-run, and it is the right choice whenever the people using the data can wait for the next batch.

Streaming ingestion moves each record as it happens, typically within seconds: a click on the website, a payment approval, a sensor reading. It is needed when decisions are made in near real time, such as showing the shop's operations team a live view of checkout failures, or flagging a suspicious order before it ships.

Between the two sits micro-batch: small batches every few minutes. Many "real-time" requirements turn out to be satisfied by data that is five or fifteen minutes old, and micro-batch is often simpler to run than true streaming. Change data capture from a database, covered below, behaves somewhat like a stream of small changes.

The question to ask is not "how fast can we make it?" but "how old can the data be when someone uses it, and what does it cost if it is older?" Mateo goes back to each data owner with that question. Finance needs store sales by eight in the morning for the daily flash report, so nightly batch is enough. The operations team wants to see checkout errors within a minute so they can call the payment provider, which needs streaming. Merchandising looks at web search trends weekly and can use whatever arrives.

Choosing a transfer tool

Google Cloud has several tools for moving data in, and the exam often describes a situation and asks which one fits. The table sorts them by what they move and from where.

Several of these tools are easy to confuse. Storage Transfer Service moves files into Cloud Storage; BigQuery Data Transfer Service loads data into BigQuery tables, usually on a schedule, from sources it has connectors for. Database Migration Service moves a database to another database; Datastream copies changes from a database continuously, typically into BigQuery for analysis. Keeping those four apart answers a surprising number of exam questions.

Online or offline: the bandwidth question

For the one-time move of the old warehouse's history and the 40 TB product photo library, the first question is whether to send the data over the network or ship it on a Transfer Appliance. The answer depends on the amount of data and the bandwidth that can be spared. Birchline's head office has a 1 gigabit per second internet connection, but the network team will allow only about half of it for transfers during business hours.

# How long would it take to send each dataset over the network?
def days_to_transfer(terabytes, link_gbps, usable_share):
    bits = terabytes * 1e12 * 8                 # 1 TB = 10**12 bytes
    seconds = bits / (link_gbps * 1e9 * usable_share)
    return seconds / 86_400

datasets = {"Warehouse history (Parquet)": 0.6, "Product photo library": 40}
for name, tb in datasets.items():
    day = days_to_transfer(tb, link_gbps=1, usable_share=0.5)
    night = days_to_transfer(tb, link_gbps=1, usable_share=0.9)
    print(f"{name:28s} {tb:5.1f} TB  about {day:5.1f} days at half the link, {night:5.1f} days at 90 in 100")

Warehouse history (Parquet)    0.6 TB  about   0.1 days at half the link,   0.1 days at 90 in 100
Product photo library         40.0 TB  about   7.4 days at half the link,   4.1 days at 90 in 100

The warehouse history, exported to compressed Parquet at about 0.6 TB, takes under three hours. The photo library takes a little over seven days at half the link, or about four days with most of the link overnight and at weekends. That is slow but manageable, so the team plans to use Storage Transfer Service with an on-premises agent, which can be limited to a set bandwidth and resumes automatically after interruptions. Transfer Appliance makes sense when an online transfer would take far longer, typically weeks or months, or when the network cannot be spared at all. Google's documentation suggests comparing the online transfer time with the time to order, fill and ship an appliance.

Birchline's ingestion plan

By the end of the session, Mateo has a plan for each source. It is the first page of what will become the team's ingestion design document.

Triggering a batch: schedules and arrival events

A batch load needs something to start it. The simplest trigger is a schedule: run the load at 03:00 every night, after the stores' 02:00 delivery deadline. Cloud Scheduler, BigQuery scheduled queries and the schedulers built into Storage Transfer Service and BigQuery Data Transfer Service all work this way. Schedules are easy to understand, but they assume the data has arrived. If store 019's file turns up at 03:20, a 03:00 load misses it until the next night.

The alternative is to trigger on arrival. Cloud Storage can announce each new object, and Eventarc can route that event to a Cloud Run service or a Cloud Run function that starts a load for just that file. Each store's sales then load minutes after they arrive, and a late file is simply loaded late. The price is more moving parts: one load per file instead of one per night, and a separate check that all 40 files eventually arrived. Birchline starts with a nightly schedule plus a missing-file check, and plans to revisit arrival triggers once the stores move to the new tills.

Whichever trigger is used, the job runs as a service account, not as a person. Ravi creates one service account for the nightly load with only the permissions it needs: read objects in the landing bucket, create load jobs and write to the raw dataset. GCP-222 returns to access control, but the habit starts here: no pipeline should depend on someone's personal account staying active.

Change data capture

The online shop's MySQL database deserves a closer look. Copying the whole orders table every fifteen minutes would mostly copy rows that have not changed, and would put load on the production database. Change data capture (CDC) instead reads the database's own change log, the binary log in MySQL, and sends only inserts, updates and deletes. Datastream does exactly this and can write the changes into BigQuery, where it keeps a table that mirrors the source. The delay is typically seconds to minutes, and the shop database barely notices.

CDC does need some preparation on the source. The database must keep its change log long enough for Datastream to read it, Datastream needs a user with the right permissions, and the network path between Datastream and the database must be set up, for example through private connectivity. The shop team schedules that work with Mateo for early November, and the first Datastream stream is planned for the week of November 16.

Thinking about reliability

Whatever the approach, an ingestion job fails sometimes: a store's upload is late, a network link drops, a source sends a malformed file. Good designs plan for it. Batch loads should be idempotent, so re-running a night does not double it, as lesson 4 explained. Transfers should verify what they copied, which Storage Transfer Service does by checking checksums. Streaming pipelines must expect duplicates and late events, which lessons 7 and 8 cover. And every feed needs a way to notice missing data, which is the subject of lesson 10.