Medallion Architecture on GCP

Building a Production-Style Medallion Architecture with Talend and BigQuery: What Actually Broke (and How I Fixed It)

A hands-on data engineering project: 18 Talend jobs, Terraform-provisioned infrastructure, centralized logging, and a fully orchestrated Bronze → Silver → Gold pipeline on Google BigQuery.

Why I Built This

Most Talend tutorials show you a happy path: drag three components onto a canvas, click Run, watch the green checkmarks appear. That’s fine for learning the UI, but it doesn’t prepare you for what actually happens when you build something end-to-end — a real data source, a real cloud warehouse, real infrastructure-as-code, and a real orchestrator that has to run 18 jobs in the correct order without falling over.

I wanted a portfolio project that would hold up to technical scrutiny in an interview: something where I could talk not just about what I built, but about the five or six things that broke along the way and how I diagnosed them. This article is that story.

The goal: implement a complete medallion architecture (Bronze → Silver → Gold) using Talend Open Studio for Big Data as the ETL engine and BigQuery as the warehouse, provisioned with Terraform, versioned in Git, with centralized logging and one-click orchestration.

The Data

I used Google’s public dataset bigquery-public-data.thelook_ecommerce — a simulated clothing e-commerce platform with seven tables: users, orders, order_items, products, inventory_items, distribution_centers, and events (clickstream data). Together they total roughly 3.2 million rows, which is small enough to iterate quickly but large enough to hit real performance and memory constraints — which turned out to matter a lot later.

I picked this dataset specifically because it forces you to touch every major transformation pattern: joins, aggregations, deduplication, slowly changing dimensions, pivots, and window functions. If your dataset only needs a SELECT * and a WHERE clause, you’re not really testing your pipeline.

Architecture at a Glance

bigquery-public-data.thelook_ecommerce
                │
                ▼
      BRONZE (7 jobs) — raw ingestion + ingestion metadata
                │
                ▼
      SILVER (5 jobs) — cleaning, SCD2 history, enrichment
                │
                ▼
      GOLD (5 jobs) — business aggregates, ready for BI
                │
                ▼
   run_full_pipeline_orchestration — 18 chained tRunJob components

Infrastructure — BigQuery datasets, a dedicated service account with a custom least-privilege IAM role, and staging buckets — is provisioned entirely through Terraform, organized as reusable modules (bigquery, iam, secrets) instantiated per environment (dev, preprod, prod).

Setting Up the Infrastructure

Rather than clicking around the GCP console, everything lives in Terraform:

module "bigquery" {
  source     = "../../modules/bigquery"
  project_id = var.project_id
  region     = var.region
  env        = "dev"
}

module "iam" {
  source     = "../../modules/iam"
  project_id = var.project_id
  env        = "dev"
}

The bigquery module creates the four datasets (bronze, silver, gold, and a technical _metadata dataset holding a job_logs table). The iam module creates a dedicated service account per environment with a custom IAM role — not roles/bigquery.admin — scoped down to exactly the permissions a Talend job needs: read/write on data, create/run BigQuery jobs, nothing more. That’s the least-privilege principle in practice, not just in a slide deck.

One real wrinkle: since I was working with a single personal GCP project rather than three separate ones for dev/preprod/prod, I had to add a Terraform variable (additional_staging_bucket_envs) that lets one service account’s IAM condition cover multiple staging buckets. In a proper multi-project setup you’d never need this — each environment gets its own isolated service account on its own bucket — but it’s a realistic compromise worth documenting rather than hiding.

terraform apply provisions everything in about a minute. From there, the DDL for all three layers is applied via bq query.

Building the Jobs

Bronze: Getting Data In Without Lying to Yourself

Each Bronze job follows the same skeleton: tBigQueryInput (a cross-project query against bigquery-public-data) → tMap (adds ingestion metadata) → tBigQueryOutput (loads into the target dataset via GCS staging).

The first real gotcha showed up almost immediately: BigQuery TIMESTAMP columns come back from tBigQueryInput as raw Unix epoch strings with decimals — something like "1784515965.6902039" — not as a formatted date string. Declaring the column as Date with a pattern in the schema throws a NumberFormatException the moment you try to run it, because Talend’s date parser expects a formatted string, not a number.

The fix: declare these columns as String in the input schema, then convert explicitly inside the tMap:

row1.created_at == null ? null : new java.util.Date((long)(Double.parseDouble(row1.created_at) * 1000))

Note the null check — several columns like delivered_at or sold_at are legitimately empty (an order that hasn’t shipped yet), and skipping that check gets you a NullPointerException on your very first successful run through the happy-path rows.

The Silent Bug That Taught Me the Most

Here’s the one I’m most glad happened, because it’s the kind of bug that never shows up in a tutorial. While building bronze_order_items_ingestion, I mis-wired the tMap: the output column product_id ended up mapped to row1.order_id instead of row1.product_id. Both are integers, so nothing crashed. No compile error, no runtime exception. The job ran, reported success, and moved on.

I only caught it downstream, in Silver, when a join between order_items and products produced a suspiciously low match rate — 42,165 rows out of an expected ~181,000. A quick diagnostic query told the story:

SELECT COUNT(DISTINCT product_id) FROM bronze.order_items
-- returned 125,153 — nearly identical to the order count

product_id had, in effect, become a copy of order_id. The lesson generalizes well beyond Talend: a mapping error between two columns of the same type produces no technical error at all — it just quietly corrupts data. The only defense is a sanity check downstream (distinct-value counts, referential checks against a known dimension) rather than trusting a green “Exit code = 0.”

Silver: Where I Abandoned the “Visual” Pattern on Purpose

The original design called for Silver transformations built as tMap chains with lookups against reference tables — the canonical Talend pattern. In practice, once I started writing joins, SCD2 logic, and aggregations, I found it dramatically faster and more debuggable to push that logic directly into SQL executed via tBigQuerySQLRow, a component that runs an arbitrary query against BigQuery without going through a row-by-row dataflow or GCS staging at all.

This is worth calling out explicitly: the “correct” Talend pattern isn’t always the right tool. A SQL statement can be tested in isolation in the BigQuery console before it’s ever pasted into a component, which turned out to matter enormously for debugging speed.

silver_users_scd2 implements a full Slowly Changing Dimension Type 2 in two SQL steps:

-- Step 1: close out changed versions
UPDATE silver.users_scd2
SET valid_to = CURRENT_TIMESTAMP(), is_current = FALSE
WHERE is_current = TRUE AND user_id IN (
  -- users whose tracked attributes differ from the current bronze snapshot,
  -- compared with IS DISTINCT FROM to handle NULLs correctly
)

-- Step 2: insert new current versions
INSERT INTO silver.users_scd2 (...)
SELECT ... FROM bronze.users s
LEFT JOIN silver.users_scd2 cur ON cur.user_id = s.id AND cur.is_current = TRUE
WHERE cur.user_id IS NULL

The elegance here is that step 2 doesn’t need to distinguish “brand new user” from “user whose attributes changed” — after step 1, both cases simply have no is_current = TRUE row, so the same WHERE cur.user_id IS NULL clause catches both.

Gold: Window Functions Doing the Heavy Lifting

The Gold layer produces five aggregate tables, and this is where BigQuery’s window functions genuinely earn their keep:

• **RANK() OVER (PARTITION BY category ORDER BY total_revenue DESC)** ranks products within each category without a separate self-join.
• **MIN() OVER (PARTITION BY user_id)** computes each customer’s cohort month (their first order month) inline, again without an extra join.
• **LAG() OVER (PARTITION BY event_date ORDER BY step_order)** powers the daily conversion funnel by pulling the previous step’s session count into the same row, wrapped in SAFE_DIVIDE to avoid crashing on a zero denominator.

The pivot table (sales by category and month) doesn’t use an actual PIVOT clause — instead it’s conditional aggregation, SUM(CASE WHEN category = 'X' THEN sale_price ELSE 0 END) repeated per category. Less elegant than a native pivot, but portable and easy to reason about.

Centralized Logging: Making the Pipeline Observable

Every job — all 17 of them — generates a UUID run_id at the start (via a tJava component writing to Talend’s shared globalMap), and writes a row to a central _metadata.job_logs table at the end, with job name, layer, environment, status, and duration.

globalMap.put("run_id", java.util.UUID.randomUUID().toString());
globalMap.put("start_time", System.currentTimeMillis());

This sounds trivial and mostly was — except for a recurring class of bug worth naming: string-concatenation SQL and Java escaping are a minefield. A single misplaced backslash-quote produces a Talend compile error that points at a line number but gives you no visual cue about what’s actually wrong:

// broken — stray escape, malformed quote sequence
+ "  CURRENT_TIMESTAMP(), NULL, TRUE, "\"" + (String)globalMap.get("run_id") + "\""" "

// correct
+ "  CURRENT_TIMESTAMP(), NULL, TRUE, \"" + (String)globalMap.get("run_id") + "\" "

After the third time chasing an invisible character, I adopted a rule: when a concatenated SQL line throws a syntax error, delete the whole line and retype it by hand rather than hunting for the offending character. It’s faster than it sounds, and it sidesteps whatever invisible artifact copy-pasting introduced.

Orchestration: When “It Works Alone” Isn’t Enough

With all 17 jobs individually validated, the next step was a parent job — run_full_pipeline_orchestration — chaining 17 tRunJob components in dependency order (Bronze first, silver_users_scd2 before the other Silver jobs since they join against it, then Gold last).

The first full run crashed with java.lang.OutOfMemoryError: Java heap space, but only on the largest job — bronze_events_ingestion, ingesting 2.4 million clickstream rows — and only when run inside the orchestration, never standalone. The reason: by default, tRunJob executes the child job inside the same JVM process as the parent. Memory from every prior sub-job accumulates until the heaviest job in the chain finally tips it over.

The fix is a single checkbox — “Use an independent process to run subjob” — which launches each child in its own JVM with its own memory allocation. The tradeoff is a few extra seconds of JVM startup time per job, which is a trivial cost against a hard crash.

A full orchestrated run — infrastructure already provisioned, all 18 jobs, cold JVM start on each — takes roughly 25–30 minutes end to end.

A Grab Bag of Smaller Lessons

**tBigQueryOutput uploads to the root of the GCS bucket under the local filename, regardless of what you configure in the File field.** If those two don’t match, you get a confusing Not found: URI gs://.../wrong-path.csv error even though the file is sitting right there under a different name. Match them exactly and the problem disappears.

PowerShell doesn’t support < for input redirection. Coming from Bash, I hit this constantly. Get-Content query.sql | bq query ... is the correct pattern. And if your SQL needs backticks to qualify a project.dataset.table reference, PowerShell here-strings require doubling the backtick, since it’s PowerShell’s own escape character.

**CURRENT_TIMESTAMP() in BigQuery is UTC.** I spent ten confused minutes convinced an orchestration run had silently failed, because a verification query filtered on local time and found nothing — the data was there, just two hours earlier in UTC than I expected.

On Component Ok vs. On Subjob Ok — a genuinely confusing Talend distinction until you internalize the rule: On Subjob Ok is only offered from the first component of a flow (one with no incoming Row link); everywhere else, including the vast majority of this project’s linear job chains, it’s On Component Ok.

What’s Actually in the Repo

• Terraform modules for BigQuery, IAM, and Secret Manager, instantiated per environment
• 18 exported Talend jobs (Bronze/Silver/Gold/Orchestration), organized in a matching folder structure
• A shared Context Group with per-environment variables
• DDL and data-quality-check SQL, versioned independently of the jobs that use them
• A standalone Python reconciliation script that validates row counts across layers and can gate a CI/CD pipeline
• A .gitignore that treats any .json, .pem, or .key file as a credential by default — because the discipline of “I’ll remember not to commit this” doesn’t scale

Everything is on GitHub, with the Talend project exported via Export Items (not something obvious if you’ve never needed to version a Studio project before — the job source lives in Talend’s internal workspace, not as loose files, until you explicitly export it).

What I’d Do Differently

• Separate GCP projects per environment, rather than the shared-project compromise I documented above. It’s the right long-term design; I just didn’t want to pay for three projects on a personal account.
• Capture actual row counts (NB_LINE) from each BigQuery component into the logging table — currently rows_written is a placeholder column I never wired up.
• Wire up the GitLab CI pipeline that’s sitting in the repo as a reference but was never connected to a live runner.
• Add a failure path to logging — right now every log row says SUCCESS because I never built the On Component Error branch that would capture and log an actual failure message.

Closing Thought

None of the interesting parts of this project were the parts that worked on the first try. The value was in the debugging: tracing a silent data-corruption bug back through three layers to a single mis-clicked column in a tMap, understanding why an orchestrator crashes on a job that runs fine alone, learning that a Talend compile error pointing at line 946 might actually be a stray character three concatenations earlier. That’s the kind of pattern recognition that doesn’t show up in a five-minute YouTube tutorial, and it’s exactly what I wanted this project to leave me with.

If you’re building something similar, my one piece of advice: don’t just make the pipeline work — break it on purpose, or let it break naturally and resist the urge to just delete-and-redo. Diagnosing why is where the actual learning lives.

The full source — Terraform modules, exported Talend jobs, SQL, and documentation — is available on GitHub.