Everything we have built so far shares one comfortable assumption: that the data is already inside Google Cloud. The history came from Cloud SQL, the events arrive over Pub/Sub, the exports land in alpinashop-catalogo. But AlpinaShop, like any real company, has data that lives elsewhere and arrives by other means.

There are three concrete cases, and none of them is exotic:

• The warehouse ERP is a MySQL 8 running on a physical server in the Sabadell offices. It holds the real stock, the goods received, the suppliers and the purchase costs. Nobody is going to migrate it this year: it works, it is wired up to the weighing scale and to the label printer, and the vendor charges for every change.
• The carrier emails a CSV every month with the shipments, the incidents and the real delivery times. Semicolon-separated, dates in dd/mm/yyyy format, amounts with a decimal comma, city names in capitals with no accents, and a DESTINATARIO column that mixes first name and surname.
• The backpack supplier offers a REST API with its up-to-date catalogue: references, cost prices, availability and technical specifications.

Lucía needs all three. She needs to cross the ERP's purchase costs with the sales in BigQuery to work out the real margin per product. She needs the carrier's delivery times to explain why customer reviews are dropping in certain areas. And she needs the supplier's catalogue to spot discontinued products that are still published on the website.

Lucía knows SQL. She knows a great deal of SQL. But she has never written an Apache Beam pipeline in her life and is not about to start, and Dani has his own to-do list. If every new file requires a development project, that data will never arrive.

Cloud Data Fusion exists for exactly that gap: a visual data integration tool where pipelines are built by dragging blocks around and data is cleansed while you look at it, without writing code. In this lesson you will use it to build the ERP pipeline, you will cleanse the carrier's CSV with Wrangler, you will understand the field-level lineage that is its greatest virtue, you will set up replication with change data capture from MySQL, and — this matters just as much — you will learn when not to use it, because it carries an hourly cost that shapes the entire decision.

1. The problem of data that is not born in Google Cloud
2. What a visual ETL/ELT is and who it serves
3. What Data Fusion is: managed CDAP
4. Editions, hourly cost and its consequences
5. Creating the instance and understanding what has been created
6. The Studio: sources, transformations and sinks
7. Wrangler: cleansing the carrier's CSV while looking at the data
8. The real pipeline: the ERP's MySQL into alpinashop_analitica
9. Connectors and plugins from the Hub
10. Deploying, running and scheduling
11. What happens underneath: ephemeral Dataproc
12. Field-level data lineage
13. Replication with CDC from the ERP's MySQL
14. When Data Fusion, when Dataflow, when Datastream, when bq load
15. AlpinaShop's reasoned decision

This is called data integration, and it is the least glamorous and most time-consuming work in any analytics project. Industry surveys repeat it without variation: between 60% and 80% of the effort in a data project goes into getting the data to arrive, not into analysing it.

The concrete problems, with AlpinaShop's examples:

Every one of these problems can be solved by writing code. The trouble is that solving them by writing code for each source, and maintaining that code when the carrier changes the format, is continuous work that a small business of 40 people cannot afford to hand to Dani.

ETL stands for extract, transform, load: the data is pulled out of the source, transformed outside, and loaded into the destination already clean. ELT swaps the last two around: the data is loaded raw and transformed inside the destination, taking advantage of its power.

With modern warehouses such as BigQuery, the clear trend is ELT: load raw and transform with SQL. But there is one part that remains unavoidably ETL: getting the data out of the source and up to the door. That is the extraction, and it is exactly where Data Fusion earns its keep.

A visual ETL is a tool where the pipeline is built on a canvas with blocks instead of with code. Who does it serve?

It serves very well:

• Analysts like Lucía, who know the business and the data but do not program distributed pipelines.
• Small teams with no dedicated data engineers.
• Standard integrations: read a table, clean a few fields, write into another table.
• Organizations that need documented traceability of where each piece of data comes from (audits, compliance).

It serves badly:

• Complex business logic with nested conditions and state.
• Teams that already have engineers and mature version control: a visual pipeline versions far worse than a .py file.
• Streaming with windows and event time: that is what Beam is for.
• Tight budgets with sporadic use, for the reason set out in section 4.

And an honest warning worth making from the start: code-free does not mean knowledge-free. To build a pipeline in Data Fusion you have to understand schemas, types, joins, keys and partitions exactly as you would if you were programming it. What you save is the syntax and the infrastructure, not the thinking.

Cloud Data Fusion is the managed version of CDAP (Cask Data Application Platform), an open source data integration platform that Google acquired and offers as a service.

Its components:

That last row is the most important one for understanding the product: Data Fusion does not execute anything itself. It translates the visual pipeline into a Spark or MapReduce job and runs it on a Dataproc cluster that it creates on the fly. That explains its behaviour, its start-up time and a good part of its cost, and we will develop it in section 11.

Being managed CDAP has another relevant consequence: the pipelines are portable. A pipeline exported from Data Fusion can be imported into a self-managed CDAP anywhere. It is not a closed proprietary format.

Here is the characteristic that conditions every decision about this product, and it deserves to go up front rather than be hidden away at the end.

Check the current prices in the official documentation; what matters is the model, not the exact figure.

And the model is this: you pay per hour that the instance exists, whether you use it or not. Not per pipeline run, nor per byte processed. The instance is an environment that is switched on.

Let us do the sums for AlpinaShop, because this is the conversation you have to have with management:

And on top of that you have to add the cost of the Dataproc cluster that is spun up to run each pipeline, which in practice is usually smaller but is not zero.

The consequence is direct and has to be said without decoration: a permanent Data Fusion instance costs more per month than the whole of the rest of AlpinaShop's data platform put together. BigQuery costs a few euros, Pub/Sub cents, batch Dataflow cents. A permanent Data Fusion Basic would cost over a thousand euros.

That does not invalidate the product: for a company with twenty data sources and an integration team, a thousand euros a month is a bargain against the salaries it saves. But for a small business with three sources the equation does not add up, and that has to be said.

The strategy that does work in a small business is to treat the instance the way we treated the Dataproc clusters in 04-03: as ephemeral. Switch it on to develop, switch it off when you are done; and the pipelines that are already deployed run all the same, because the work is done by Dataproc. We will come back to this in section 15.

Creation takes between 15 and 25 minutes. That is not a fault: a complete CDAP environment is being provisioned in a Google-managed project.

That "managed project" detail matters for permissions. Data Fusion creates the instance in a Google-owned project (the tenant project) and acts on yours through a service agent, which has to be granted permissions explicitly:

Access to the interface:

And connectivity with Sabadell, which is the real requirement for reading from the ERP. There are three options, in order of preference:

For AlpinaShop, Marta sets up a Cloud VPN tunnel with routing towards the ERP subnet, consistent with everything covered in 03-01: the MySQL still has no public IP and the traffic travels encrypted. The detailed configuration of hybrid connectivity is 07-03's territory.

The Studio is a canvas. On the left there is a palette of blocks grouped by category; you drag them onto the canvas and connect them with arrows that represent the flow of the data.

The blocks are classified like this:

Every block has a configuration panel: connection string, table, output schema, specific options. And every block declares an output schema — the list of fields with their types — which is propagated to the next one. That propagation is what makes the lineage of section 12 possible.

The pipeline we are going to build, drawn out:

Notice two things in the diagram, because they reproduce the good practices of the previous lessons:

• The rejects branch exists here too. The Error Collector block picks up the records that a Wrangler could not process and sends them to a quarantine destination, exactly like Beam's tagged outputs in 04-02.
• A single source feeds two branches. The canvas is a directed graph, not a line, just like Beam's graph.

Wrangler is the piece that on its own justifies learning Data Fusion. It is an interactive environment where you load a sample of the data, see it as a table, and apply transformations — called directives — while watching the effect immediately.

We start from the carrier's real CSV, exactly as it arrives:

The problems are obvious at a glance: ; separator, European dates, decimal commas, nulls as -, stray whitespace, inconsistent capitalisation, first name and surname bundled together, and a delivery with no date.

Wrangler's directives are written one per line and applied in order. They can be generated from each column's context menu, but it is far quicker to type them:

The most useful directives, grouped:

Steps 10 and 11 of the example deserve a comment.

Step 10 uses filter-rows-on, which discards silently. If you would rather keep what was discarded so you can review it — and normally you should — use send-to-error, which sends the record to the pipeline's Error Collector instead of throwing it away:

Step 11 is a compliance decision, not a cleansing one:

GDPR warning. The carrier's CSV contains the recipient's first name and surname, which is identifying personal data. For analysing delivery times and incidents, that data adds absolutely nothing: the postcode and the order identifier are enough. Removing it at the cleansing stage, before it reaches the analytical warehouse, is data minimisation by design and is the correct practice. Any processing of real personal data must be reviewed by a compliance professional or the DPO before going to production. All the data in this course is fictitious.

Wrangler's advantage over writing this in Python or SQL is not power — Python can do all of this and more — but the feedback loop: you apply a directive and immediately see its effect on 100 real rows. When parse-as-simple-date fails because there is a differently formatted date on row 47, you see it there and then, not once the pipeline has been running in production for twenty minutes.

Let us tackle the integration Lucía really wants: bringing in the purchase costs and the stock from the ERP so she can calculate the real margin per product.

Block 1 — Source: Database. Configured with:

• Plugin type: Database (generic JDBC).
• JDBC driver: the MySQL connector, which has to be uploaded beforehand from the Hub or as a custom plugin.
• Connection string: jdbc:mysql://10.20.0.15:3306/erp_almacen (private IP reachable over the VPN).
• Import Query: the query that extracts the data.

Two fundamental details of this configuration:

• ${fecha_desde} and ${fecha_hasta} are Data Fusion macros: runtime arguments. They let the same pipeline serve both the full initial load and the daily incremental one, without duplicating it. The orchestrator in 04-06 will pass it the values.
• $CONDITIONS with numSplits: if you set Split-By Field Name = sku and Number of Splits = 4, Data Fusion fires four queries in parallel over different ranges. That speeds up the initial load enormously, but it puts four times as much load on the ERP's MySQL. With a production database that is also serving the warehouse weighing scale, you have to be prudent: one or two splits, and run in the small hours.

Block 2 — Transform: Wrangler. Cleansing specific to the ERP:

Block 3 — Analytics: Group By. Weighted average cost per SKU:

• Group by fields: sku
• Aggregates:
  • Sum(cantidad) → unidades_compradas
  • Avg(coste_unitario_eur) → coste_medio_eur
  • Min(fecha_movimiento) → primera_compra
  • Max(fecha_movimiento) → ultima_compra
  • Count(*) → num_movimientos

Block 4 — Sink: BigQuery.

• Dataset: alpinashop_analitica
• Table: costes_producto_erp
• Operation: Upsert keyed on sku (updates if it exists, inserts if not)
• Truncate Table: disabled
• Service Account: [email protected]
• Location: europe-west1

The Upsert option is what makes the pipeline re-runnable without duplicating. It is the equivalent of SQL's MERGE, and it is what lets you launch the pipeline twice without spoiling anything — the same idempotency property we were chasing in 04-04.

Block 5 — Error Collector → Sink GCS. The records diverted with send-to-error go to gs://alpinashop-datalake/cuarentena/erp/, in JSON format, with the reason for the rejection.

And with that data finally in BigQuery, Lucía can at last answer her question with SQL:

That query, sorted by the lowest margin, is exactly the kind of result that changes decisions: products that sell a lot and leave little behind. It was not possible before this lesson because the cost lived on a server in Sabadell.

The Hub is the catalogue from which plugins are installed into the instance. The main categories:

For AlpinaShop's third source, the backpack supplier's API, the HTTP plugin is used:

• URL: https://api.proveedor-montana.example/v2/catalogo
• HTTP Method: GET
• Headers: Authorization: Bearer ${api_token} — where ${api_token} is a macro resolved from Secret Manager (03-06), never written into the pipeline.
• Format: json
• JSON/XML Result Path: $.productos — the path within the response where the array lives.
• Pagination Type: Link Header or Increment an Index, depending on what the API supports.

Pagination is the thing most often forgotten: without configuring it, only the first N results are fetched and nobody notices until products go missing.

If a source has no plugin, there are three ways out: write one in Java (CDAP is extensible), use the Python Evaluator for one-off transformations, or — the most sensible — export the data to Cloud Storage with a script and read it from there. The last one is usually the right answer for an exotic, low-volume source.

A pipeline in Data Fusion has two states: draft (editable, previewable) and deployed (immutable, executable, versioned).

The workflow:

1. Preview. Runs the pipeline over a small sample without writing to the destination. It shows the data coming out of each block. It is the equivalent of Beam's DirectRunner and you should always use it before deploying.
2. Deploy. Freezes the pipeline with a version number. To change it, a new version is created.
3. Run. Executes it. Runtime arguments (the macros) can be passed in.
4. Schedule. Schedules recurring runs with a cron expression.

All of this can also be done over the REST API, which is how the orchestrator in 04-06 will invoke it:

Compute profiles define what the Dataproc cluster running the pipeline will look like: number of workers, machine type, network, service account. This is where the execution cost is controlled:

That profile applies exactly what we learned in 04-03: private network with no public IP, its own service account, pinned image version and self-destruction on idleness.

When you press Run, this is what actually happens:

Very practical consequences of knowing this:

Start-up takes 2 to 5 minutes. That is not Data Fusion being slow: it is the time to create the cluster. That is why Data Fusion is no use whatsoever for anything that needs low latency. A pipeline that moves 200 rows takes almost as long as one that moves 20 million, because the dominant cost is the start-up.

The real cost is the sum of two things: the instance hours (always) plus the Dataproc hours (per run). A daily 8-minute pipeline with 3 nodes is a few cents of Dataproc, but the instance keeps billing 24 hours a day.

You can reuse a cluster. By configuring a profile that points to an existing cluster instead of creating one, the start-up time disappears. It makes sense when many pipelines run back to back, for instance in a nightly window: create the cluster, launch ten chained pipelines, destroy it.

Spark errors show up in the Dataproc logs. When a pipeline fails on memory (OutOfMemoryError in an executor) or on data skew, the diagnosis is exactly the one from 04-03: look at the Spark interface and the stages. Data Fusion does not hide that reality from you, it only wraps it.

For many organizations, this is the main reason to pay for Data Fusion.

Lineage answers two questions that sound trivial and that almost no company can answer:

• "Where exactly does this field in the report come from?"
• "If I change this column in the ERP, what breaks?"

Data Fusion records lineage automatically as each pipeline runs, at two levels:

Dataset lineage: which sources feed which destinations, with which pipeline and when.

Field lineage: which specific source column produces which destination column, and what operations it went through along the way.

Nobody draws that diagram: Data Fusion generates it from the run. And it answers the question that in many companies costs days of archaeology: if somebody asks why the margin in the management report looks odd, the lineage shows that coste_medio_eur comes from a division by 100 and an unweighted average — which, incidentally, is a debatable decision that the lineage leaves in plain sight.

Why it matters so much:

That last row is especially valuable in a migration: knowing what depends on the ERP before touching it. And the one before it connects directly with what we will see in 04-07, where Dataplex consolidates the lineage of the whole platform, not just Data Fusion's.

A batch pipeline that runs every night leaves the data up to 24 hours behind, and on top of that loads the ERP with a heavy query. The alternative is change data capture (CDC): instead of querying the table, you read the database's transaction log and replicate the changes as they happen.

Preparation on the ERP's MySQL:

binlog_format = ROW is non-negotiable: with STATEMENT, the binlog stores the SQL statements, not the resulting rows, and replication cannot rebuild the state. It is the first requirement to verify with the ERP vendor.

In Data Fusion you use the Replication module (Enterprise edition):

1. Source: MySQL, with the connection and the cdc_datafusion user.
2. Table selection: movimientos_stock, proveedores, articulos.
3. Destination: BigQuery, dataset alpinashop_analitica, with a staging prefix.
4. Source assessment: checks permissions and configuration before starting.
5. Initial snapshot + continuous streaming.

Data Fusion first loads a full snapshot of the tables and then applies the changes continuously, writing into staging tables and running a periodic MERGE against the final tables.

Important considerations about CDC, to weigh up before committing:

That point about deletes is the strongest technical argument in favour of CDC. An incremental pipeline reading WHERE fecha_modificacion > X never finds out that a row has been deleted, and the analytical warehouse accumulates phantom records indefinitely. CDC does see the DELETE.

The alternative worth considering is Datastream, a Google service dedicated exclusively to CDC, simpler and cheaper than switching on Enterprise in Data Fusion just for this. It is in the table in the next section.

The honest table, which is what you should take away from this lesson:

And translated into decision rules:

Use bq load when the data is already in Cloud Storage in the right shape. It is free and there is nothing to maintain. Always start by asking yourself whether this is enough. Half the pipelines that exist in the world are unnecessary.

Use Datastream when the goal is to replicate a whole database into BigQuery with low latency and delete capture, without transforming anything. It is simpler and cheaper than Data Fusion Enterprise for that specific task, and it supports MySQL, PostgreSQL, Oracle and SQL Server.

Use Data Fusion when there are several heterogeneous sources, non-trivial transformations and cleansing are needed, the team has no programming profile, and lineage is a real requirement (audit, compliance). And when the volume of work justifies the cost of the instance.

Use Dataflow when there is streaming with event-time semantics, complex or state-dependent transformations, or when the team prefers versionable code with automated tests.

And there is one very sensible combination that you see a lot in practice: Datastream to replicate the raw data + BigQuery SQL to transform it (pure ELT). No Data Fusion, no Dataflow, no instances to pay for. For many cases, including a good part of AlpinaShop's, it is the most efficient answer.

With all of the above on the table, Lucía and Marta decide as follows:

The Sabadell ERP → Datastream, not Data Fusion. The requirement is to replicate three tables with low latency and capture deletes. There is no transformation: the costs are calculated afterwards with SQL in BigQuery, where Lucía is perfectly at home. Datastream costs a fraction of an Enterprise instance and there is no instance to switch off. The transformations from section 8 (upper case, cents to euros, movement type labels) become a BigQuery view, which as a bonus ends up versioned in Git.

The carrier's monthly CSV → Data Fusion, with an ephemeral instance. Here it wins: the file is genuinely dirty and the eleven Wrangler directives are written in twenty minutes while looking at the data, against a couple of days of development and testing in Beam. It is monthly, so the instance is switched on, run and switched off. With the Basic edition and under 120 hours a month, the cost is zero. The instance is managed like this:

The backpack supplier's API → a scheduled Cloud Function. That is 400 products in JSON, once a week. Spinning up a three-node Dataproc cluster for five minutes to read 400 records is disproportionate. Forty lines of Python in Cloud Functions (06-03) triggered by Cloud Scheduler solve the case for cents.

The general rule AlpinaShop adopts, which works as a reusable criterion:

1. Is the data already in Cloud Storage in table shape? → bq load.
2. Do you need to replicate a database without transforming it? → Datastream + BigQuery views.
3. Is it a small volume from an API? → scheduled Cloud Function.
4. Is it a dirty, recurring file that an analyst is going to clean? → Data Fusion with an ephemeral instance.
5. Is there streaming, windowing or complex logic? → Dataflow.
6. Is it algorithmic with Spark libraries? → Dataproc Serverless.

That list, with the discipline of going through it in order and stopping at the first option that works, is what stops a small business's data platform from ending up costing what a multinational's does.

Leaving the instance switched on. It is the expensive mistake with this service and it bears repeating: a forgotten Basic instance costs over a thousand euros a month without running a single pipeline. Switch it off or delete it.

Choosing Enterprise without needing it. It is only required for CDC, high concurrency and high availability. Basic covers most cases in a small business at less than half the price.

Using Data Fusion for tiny volumes. A Dataproc cluster to process 400 rows is disproportionate. The start-up takes longer than the work.

Not versioning the pipelines. Even though they are visual, they should be exported to JSON and kept in Git. Without that, a deleted instance takes months of work with it and there is no change history.

Parallelising the extraction without measuring the impact on the source. Number of Splits = 8 against the ERP's MySQL can take the warehouse weighing scale out of service. Start at 1 and go up while measuring.

Writing credentials into the pipeline configuration. Use macros resolved from Secret Manager. A pipeline exported to JSON with the password inside ends up in Git, and that is a security incident.

Forgetting pagination in the HTTP plugin. You fetch the first 100 records and nobody notices until products go missing from a report.

Skipping the Preview. It is free, takes seconds and shows exactly what comes out of each block. Deploying without previewing means launching a cluster to discover a type error.

Not using Upsert in the sink. With Insert, every re-run duplicates rows. With Upsert on the business key, the pipeline is idempotent and can be relaunched without fear.

Tip: use macros from the very start. Dates, paths, table names and credentials as ${variable}. It turns a rigid pipeline into a reusable, orchestrable one.

Tip: always put an Error Collector in. Rejected records must go somewhere, with their reason. It is the same discipline as Beam's quarantine in 04-02.

Tip: review the lineage after every deployment. It is the best way to verify that the pipeline does what you think it does, and it costs nothing.

The backpack supplier sends this tab-separated text file:

Write the Wrangler directives that produce a clean dataset with: sku in upper case with no whitespace; nombre in title case; pvp_eur and coste_eur as decimal numbers without the word EUR; stock as an integer, with - converted to 0; fecha_alta as a yyyy-MM-dd date, tolerating N/D as null; activo as a boolean; and a calculated column margen_pct. Records with no REF must be diverted to error.

Design the complete pipeline that reads the carrier's monthly CSV from gs://alpinashop-datalake/transportista/2026/03/envios.csv, cleanses it, joins it with the pedidos table in alpinashop_analitica to add the country and the channel, calculates the delay in days between fecha_pedido and fecha_entrega, and writes into alpinashop_analitica.envios. State: each block with its type and essential configuration, how to handle shipments whose pedido_id does not exist in pedidos, which operation to use in the sink and why, and which macros you would define.

AlpinaShop absorbs a competitor and inherits four new integrations. For each one, choose between bq load, Datastream, Data Fusion, Dataflow, Dataproc Serverless or Cloud Function, and justify it including an order-of-magnitude estimate of the monthly cost:

1. An 80 GB PostgreSQL 14 with the competitor's customer history, which must be replicated into BigQuery with under 5 minutes of lag and capturing deletes.
2. A weekly Excel file of 3,000 rows sent by the purchasing team, with columns that get renamed every other week and hand-typed data.
3. A stream of 2,000 events per second from a mobile app that has to be aggregated into 5-minute windows before being stored.
4. A nightly 12 GB Parquet dump that the competitor already drops into a bucket, with the exact schema of the target table.

Solution 1

Expected result over the three sample rows:

The three points being assessed: distinguishing - (which means zero units) from N/D (which means unknown data, that is, null) — confusing them would falsify any stock report; protecting the division in the calculated column; and diverting to error instead of filtering silently, so that a file with empty references leaves a trace.

Solution 2

Block 1 — Source: GCS

• Path: gs://alpinashop-datalake/transportista/${anyo}/${mes}/envios.csv
• Format: text (one row per line; parsing is done in Wrangler because the separator is ;)
• Service Account: sa-fusion-pipelines@...

Block 2 — Transform: Wrangler The directives from section 7, including the drop of first name and surname for data minimisation, and send-to-error for rows with no pedido_id.

Block 3 — Source: BigQuery

• Dataset/Table: alpinashop_analitica.pedidos
• Import Query (better than reading the whole table):

The partition filter is mandatory: the table has require_partition_filter=TRUE since 04-01, so without it the pipeline would fail. And even if it did not, reading the whole table every month would be throwing money away.

Block 4 — Analytics: Joiner

• Inputs: the Wrangler output (left) and the BigQuery output (right)
• Join type: Left Outer on pedido_id
• Fields: everything from the clean CSV, plus fecha_pedido, pais and canal from the right-hand side

The join type is the key decision in this exercise. With Inner, shipments whose pedido_id does not exist in pedidos would disappear without a trace, and nobody would know that shipments were missing. With Left Outer they are all kept, with pais and canal null, and those nulls are the signal that there is an integrity problem to investigate: orders from months outside the date range, or identifiers mistyped in the carrier's file.

Block 5 — Transform: Wrangler (second one)

Block 6 — Sink: BigQuery

• Table: alpinashop_analitica.envios
• Operation: Upsert keyed on envio_id
• Partition field: fecha_entrega; Cluster field: pais

Why Upsert. The carrier's file tends to be resent corrected when there are errors, and it sometimes includes shipments from the previous month that were delivered late. With Insert, every resend would duplicate rows and the delivery-time report would lie. With Upsert on envio_id, re-running the pipeline as many times as necessary always produces the same result: it is idempotent, exactly the same principle we demanded of the Pub/Sub consumers in 04-04.

Block 7 — Error Collector → Sink GCS

• Path: gs://alpinashop-datalake/cuarentena/envios/${anyo}/${mes}/
• Format: json

Macros to define: ${anyo}, ${mes}, ${fecha_desde}, ${fecha_hasta}. With them, the same pipeline serves any month and a whole history can be reprocessed by changing only the arguments. Without them, you would have to duplicate the pipeline or edit it every month.

Solution 3

1. An 80 GB PostgreSQL replicated into BigQuery, under 5 minutes of lag, with deletes → Datastream. That is literally its definition: managed CDC over PostgreSQL, code-free, with DELETE capture — which an incremental load by date would never detect. Data Fusion Enterprise could also do it, but it would demand the expensive edition (~$3,000/month of instance) to do exactly the same thing. Datastream is billed per GB processed: the initial 80 GB load plus the daily changes put the cost in the order of a few tens of euros a month. The subsequent transformation is done with views in BigQuery, free in effort and versionable in Git.

2. A weekly Excel of 3,000 rows with changing columns and hand-typed data → Data Fusion with an ephemeral instance. This is the textbook case for Wrangler: dirty data typed by people, an unstable schema, and an analyst — not a programmer — who needs to fix it while looking at the data. Programming it in Beam would mean redeploying code every time purchasing renamed a column. With a Basic instance switched on for one hour a week, that is 4 hours a month, well inside the 120 free ones: effective cost zero, plus a few cents of Dataproc per run. The key word in the answer is ephemeral: if the instance is left switched on, the same solution costs over a thousand euros a month.

3. 2,000 events per second aggregated into 5-minute windows → Dataflow. Streaming with time windows: Beam's exclusive territory. Data Fusion does not do this well, bq load does not apply and a Cloud Function cannot hold window state. A streaming pipeline with 2-3 permanent workers is in the order of €100-150 a month, a cost that has to be taken on consciously because it is the only tool that meets the requirement. If the business could tolerate 15 minutes of lag instead of 5, the alternative — a Pub/Sub subscription to Cloud Storage plus micro-batches — would cost a fraction; it is worth asking before switching the pipeline on.

4. A nightly 12 GB Parquet with the exact schema of the target table → bq load. With no transformation and the schema already correct, there is nothing to process. Batch loading into BigQuery is free and Parquet carries the schema inside it, so you do not even have to declare it. One command in the 04-06 orchestrator, triggered by the bucket notification we set up in 04-04. Cost: €0 of processing, only the storage for the 12 GB. Any other option on this list would be paying to make a copy, and that is exactly the reflex to correct: the first question in the face of any integration is "is bq load enough?".

You have seen the least showy and most real side of a data platform: the data that is not born in the cloud. The ERP's MySQL in Sabadell, the carrier's CSV with its European dates and its nulls written as a hyphen, and the backpack supplier's API. None of them publishes to Pub/Sub, none of them writes Parquet, and Lucía needs all three to calculate the real margin per product and explain why reviews are dropping in certain areas.

You have understood what a visual ETL/ELT is and, above all, who it serves: profiles who have mastered the business and SQL but are not going to write Beam, and organizations that need documented traceability. And also who it does not serve, which matters just as much. You know that Data Fusion is managed CDAP, with its Studio, its Wrangler, its Hub and its lineage record, and that underneath it executes nothing: it translates the visual graph into Spark and spins up an ephemeral Dataproc, which explains its 2-5 minutes of start-up and why it is no use for low latencies.

You have put the cost up front instead of hiding it: Developer, Basic and Enterprise editions, billed per hour that the instance exists, used or not, with a permanent Basic instance costing more than the whole of the rest of AlpinaShop's platform put together. That fact does not disqualify the product — for a company with twenty sources it is cheap against the salaries it saves — but it does force the ephemeral instance strategy in a small business.

You have cleansed the carrier's CSV with Wrangler and its directives: semicolon parsing, trim, nulls written as a hyphen, dd/MM/yyyy dates converted to a date type, decimal commas, splitting the recipient column, title case, derived columns, and the final drop of first name and surname for data minimisation, with the explicit GDPR warning and the requirement for compliance review. You have weighed up the tool's real advantage, which is not power but the feedback loop: you see the effect of each directive on real data instantly.

You have designed the ERP pipeline with its date macros, its partitioned reading with the prudence not to knock over the warehouse weighing scale, its cleansing, its aggregation and its Upsert sink so that it is idempotent and re-runnable. You know the Hub and its connectors, the HTTP plugin with its easily forgotten pagination, the Preview → Deploy → Run → Schedule flow, the compute profiles that control the underlying cluster, and the REST API through which the orchestrator will invoke it. You have seen field-level lineage, which answers "where does this column come from?" and "what breaks if I change this?" without days of archaeology, and CDC replication from MySQL with its non-negotiable binlog_format = ROW and its great argument: it is the only way to find out about deletes.

And you have closed with the honest table and with AlpinaShop's reasoned decision, which is not "let us use Data Fusion for everything" but an ordered list: first bq load if it is enough, then Datastream if it is replication, then a Cloud Function if it is a small job, then Data Fusion with an ephemeral instance if the file is dirty and an analyst is cleaning it, and Dataflow or Dataproc if there is streaming or algorithms. Going through that list in order and stopping at the first option that works is what separates a proportionate data platform from a ruinously expensive one.

And now a new problem appears, one that is a direct consequence of the success of the previous four lessons. AlpinaShop has a nightly Cloud SQL export, a load into BigQuery, a Dataflow pipeline, a Spark job on Dataproc Serverless, a monthly Data Fusion pipeline, several aggregation queries and a materialised view to refresh. That is seven or eight processes that depend on one another: there is no sense in launching the aggregation before the load has finished, nor in refreshing the business view with half-loaded data.

Right now that is handled by a cron on a virtual machine that launches scripts at fixed times, worked out by eye with plenty of margin. It works until the first night the export takes twenty minutes longer than usual: then the load reads an incomplete file, the aggregation computes over partial data, the management report wakes up with false figures, and nobody finds out until somebody looks at them mid-morning.

In 04-06, Cloud Composer and Workflows, we will solve that. You will see why a cron is not an orchestrator and what coordinating dependencies, retries, alerts and reprocessing really means. You will meet Cloud Composer — managed Apache Airflow — with its DAGs, tasks, operators and sensors, and you will write the complete DAG for AlpinaShop's nightly process commented line by line, with the clear warning that Composer is expensive for a small business. And you will meet Workflows, serverless orchestration in YAML with no fixed cost, with Cloud Scheduler for the trigger, which is where AlpinaShop is going to start.