Two Data Sources, Zero Orchestrators — Building an F1 Analytics Pipeline on AWS Glue

Disclaimer: I'm not affiliated with or endorsed by AWS. This is a personal hobby project, and some of the behaviour described here was found by trial and error rather than from the docs. Spark Declarative Pipelines is new in AWS Glue 6.0 and may change, so check the official AWS Glue documentation first.

In my first Spark Declarative Pipelines post I walked through the basic idea: declare the tables you want, let AWS Glue 6.0 work out the dependency order, and skip the orchestrator entirely. That demo used one CSV source flowing bronze → silver → gold. It worked, but it was a tidy example — one source, one shape, one straight line.

Real pipelines are messier. So I built one properly, using Formula 1 race data, and deliberately made it harder: data arriving from a live API as deeply nested JSON, a second completely unrelated data source, and dimension tables that need to be deduplicated. This post walks through that build, and the things I only learned by breaking it.

New to SDP? The short version: Spark Declarative Pipelines (SDP) is a framework in AWS Glue 6.0 where you declare tables instead of writing scripts that build them in order. You reference one table from another by name, and the engine figures out the dependency graph, the execution order, and what can run in parallel — all inside a single Glue job. My first post covers the fundamentals if you want them.

First, the use case: what are we actually building?

Let's park AWS for a minute and talk about the data, because the data shape is what drives every design decision later.

I'm using f1api.dev, a free public Formula 1 API. You ask it for one race at a time:

GET https://f1api.dev/api/2024/1/race

That's season 2024, round 1 — the Bahrain Grand Prix. What comes back is a single, deeply nested JSON document describing everything about that race. Trimmed down heavily for readability:

{
  "season": 2024,
  "races": {
    "raceName": "Bahrain Grand Prix",
    "date": "2024-03-02",
    "round": "1",
    "circuit": [
      {
        "circuitId": "bahrain",
        "circuitName": "Bahrain International Circuit",
        "city": "Sakhir",
        "country": "Bahrain",
        "corners": 15
      }
    ],
    "results": [
      {
        "position": "1",
        "grid": "1",
        "points": 26,
        "driver": { "driverId": "max_verstappen", "name": "Max", "surname": "Verstappen", "number": 1 },
        "team":   { "teamId": "red_bull", "teamName": "Red Bull Racing" }
      },
      { "position": "2", "points": 18, "driver": { "driverId": "perez", "...": "..." }, "...": "..." }
    ]
  }
}

Look at the shape, because this is the first real problem:

• Race facts live at the top (name, date, round)
• The circuit is an array nested one level down
• The results are an array of 20 entries — one per driver — and inside each entry there's a nested driver object and a nested team object

You cannot answer "which driver scored the most points this season?" from a folder full of documents shaped like that. You need flat tables. That flattening is the pipeline's first job.

The questions I want answered

The whole point of the pipeline is to produce tables that answer questions like:

• Which driver scored the most points, won the most races, and had the best average finishing position?
• Which team scored the most, and how many different drivers did they field?
• Which circuits have been raced at, how many times, and what's the lap record there?

Why three layers?

This is the medallion architecture — bronze, curated (often called silver), and gold. It sounds formal, but the idea is simple: don't try to go from raw JSON to a polished dashboard table in one step. Do it in three, where each step has one job.

Bronze — land it, then flatten it.

First, raw_race stores the API response more or less as it arrived. One row per pipeline run. Nothing cleaned, nothing dropped. If I later discover a bug in my parsing logic, the original data is still sitting here and I can reprocess it.

Then four tables flatten that nesting into something usable:

Table	What one row means
race_results_unnested	One driver's result in one race (20 rows per race)
race_drivers	A driver's profile, as it appeared in that race
race_teams	A team's profile, once for each of its drivers in that race
race_circuits	The circuit that race was held at

Curated — one row per thing.

Here's the catch with that bronze layer: every single race response re-sends the full profile of every driver and team. Load 24 races and you have 24 copies of Max Verstappen's profile and about 48 copies of Red Bull's team record (one for each of its two drivers, every race) — most of them identical, some slightly different as the season progresses and championship counts change.

For analytics I want exactly one current row per driver. So the curated layer deduplicates:

• dim_drivers — one row per driver (the most recent version of their profile)
• dim_teams — one row per team
• dim_circuits — one row per circuit
• fact_race_results — one row per (race, driver): the actual measurements — grid position, finishing position, points scored

If you've seen a star schema before, that's exactly what this is: dimensions describe the who/what/where, and the fact table records what happened, with the numbers.

Gold — the answers.

Three summary tables, each one a direct answer to one of the questions above: driver_performance_summary, team_performance_summary, and circuit_summary. These are what a dashboard or an Athena query would actually read.

One run = one race

One design decision worth calling out because it shapes everything: each pipeline run fetches exactly one race. You tell it the season and round when you start the job:

scripts/run_pipeline.sh --profile my-profile --season 2024 --round 1 --mode run --wait

Run it again with --round 2 and the pipeline accumulates — bronze grows by one more race, and the curated and gold layers recompute to include it. After running rounds 1 and 2, my gold table showed Verstappen on 2 wins and 51 points, Perez on 36, Leclerc on 28 — which matches the real 2024 championship standings after two races — always a nice moment when the numbers come out right.

The pipeline at a glance

All of the above runs as one Glue job. Not one job per layer, not a Step Functions state machine tying them together — one job, with all tables registered as Apache Iceberg tables in the Glue Data Catalog.

In SDP you write two kinds of tables, and the difference matters:

• Streaming tables process only what's new since the last run. Bronze uses these, so re-running never reprocesses an old race.
• Materialized views fully recompute every run. Curated and gold use these, because "the latest row per driver" and "total points per driver" genuinely need to look at everything each time.

Adding a second data source (and why F1 needed one)

Here's where I actually tested the "zero orchestration" claim.

The F1 API tells me a circuit's name, city, country, length, and corner count. It does not tell me whether it's a street circuit (Monaco, Singapore, Las Vegas — public roads closed for the weekend) or a permanent racetrack (Silverstone, Spa, Suzuka — purpose-built facilities). That's exactly the kind of attribute you'd want to slice results by — for example, comparing how drivers perform on street circuits versus permanent ones.

That data simply isn't in the API. It has to come from somewhere else — which makes it a perfect second data source, and a genuinely different kind of source:

	raw_race	circuit_track_type
Where it comes from	Live external API	Static CSV files in S3
How it arrives	An HTTP call during the job run	Uploaded by a deploy script
Depends on	Nothing — it's a root	Nothing — also a root

Two completely independent branches. They never touch in bronze. They meet for the first time in the curated layer, in exactly one place:

CREATE MATERIALIZED VIEW dim_circuits AS
SELECT rc.circuit_Id, rc.name, rc.city, rc.country, ctt.track_type, rc.length, rc.corners, ...
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY circuit_Id ORDER BY race_Date DESC, load_date_time DESC) AS _rn
  FROM race_circuits
) rc
LEFT JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY circuit_Id ORDER BY load_date_time DESC) AS _rn
  FROM circuit_track_type
) ctt ON rc.circuit_Id = ctt.circuit_Id AND ctt._rn = 1
WHERE rc._rn = 1;

Ignore the ROW_NUMBER() noise for a second (that's the deduplication I mentioned — "keep the newest row per circuit"). The important line is LEFT JOIN ... circuit_track_type.

That join is the entire integration. I didn't register the new source anywhere. No config file entry, no trigger, no second job, no DAG definition. SDP reads this SQL file, sees a reference to a table called circuit_track_type, and from that alone works out that it's an upstream dependency of dim_circuits. The two branches stay independent until this join — and per the AWS docs, SDP runs independent branches in parallel.

That's the claim from my first article, and this is the first time I'd properly stress-tested it. It held.

Four lessons the tidy demo didn't teach me

A one-source demo hides most of the real friction. These four cost me the most time. Here they are at a glance, then in detail.

#	The lesson	What I saw	What worked
1	No MERGE or upsert	No built-in way to keep a row's history	Keep only the latest row per key (SCD Type 1)
2	Streaming tables need a streaming source	An INVALID_FLOW_QUERY_TYPE error on the API data	Stage the API response in S3, then read it as a stream
3	Fresh deployments can fail	PATH_NOT_FOUND before any data had landed	Write a tiny .keep marker file into each watched folder
4	Shared code isn't importable	ModuleNotFoundError	One shared bootstrap file, listed first in the manifest

1. There's no MERGE or upsert, so there's no change history

The problem. Databricks' version of declarative pipelines has a built-in feature for tracking how dimension rows change over time (create_auto_cdc_flow). AWS Glue 6.0 SDP has no equivalent.

Why. SDP gives you only two kinds of persisted table to write to: append-only streaming tables and full-recompute materialized views. There's no "update this row" operation.

What worked. I settled for SCD Type 1: keep only the current version of each row, with no history. (SCD Type 2 would keep every past version.) Every curated table works out "latest row per key" with a ROW_NUMBER() window function over the whole bronze history — number each driver's rows newest-first, then keep row 1. It works well. But if you genuinely need history ("what was this team's championship count last month?"), know that going in: there's no built-in escape hatch yet.

2. A streaming table can't be fed from a plain API call

The problem. My first instinct was the obvious one: fetch the JSON from the API, load it into a DataFrame in memory, and return it from the pipeline. Glue rejected it:

AnalysisException: [INVALID_FLOW_QUERY_TYPE.BATCH_RELATION_FOR_STREAMING_TABLE]

Why. A streaming table has to be fed by a streaming source: something Spark can read incrementally while remembering how far it got. A DataFrame built in memory is a one-off batch, so it's refused.

What worked. Stage the data in S3 first:

1. Call the API and get the JSON.
2. Write it to a uniquely named file in an S3 folder, using plain boto3 (not Spark).
3. Read that folder with spark.readStream.

Now it's a genuine streaming source, and Spark's built-in file checkpointing means a re-run never reprocesses yesterday's file. It's an extra moving part that exists purely because a REST API has no natural streaming shape.

3. A brand-new deployment fails before it does anything

The problem. On a fresh deploy, the pipeline failed straight away:

AnalysisException: [PATH_NOT_FOUND] Path does not exist: s3://.../reference-data/circuit_track_type/

Why. Both S3 folders the pipeline watches (the API staging folder and the reference-data folder) are read with spark.readStream(...).load(path). Before any data has landed, those folders don't exist, and Spark doesn't shrug and read zero rows. It fails the job.

What worked. Make sure each folder always exists by writing a tiny empty marker file, .keep, into it. My CDK stack does this for the reference-data folder on every deploy, and the pipeline does it itself for the API staging folder. It's cheap insurance against a failure that only appears on day one of a new environment, which is exactly when you're least expecting it.

4. Shared Python code isn't importable by default

The problem. I keep schema definitions and API-fetch helpers in a shared Python package next to the transformation files. Importing it failed:

ModuleNotFoundError: No module named 'f1_pipeline_lib'

Why. SDP loads each transformation file directly by its file path, and that doesn't add anything to Python's import path (I confirmed this by reading SDP's source). I then tried the documented way to ship extra Python files to a Glue job, the --extra-py-files parameter. It failed with the same error, most likely because SDP runs as a separate process that the setting doesn't reach. I confirmed both failures by running real Glue jobs and reading the tracebacks.

What worked. One tiny shared bootstrap file that fixes the import path, listed first in the pipeline manifest so it always runs before anything else. One file, one place, instead of the same few lines copy-pasted into every transformation file.

Benefits of using SDP in AWS Glue 6.0

• The zero-orchestration claim genuinely holds, even with two independent sources and a deduplicating curated layer. One job, no Step Functions, no Airflow.
• Connecting a new source takes one line. The source still needs its own table definition, but wiring it into the pipeline is just a SQL JOIN or a .readStream.table("x") call — there's nothing to register anywhere else.
• Independent branches run in parallel automatically, according to the AWS docs — no fan-out/fan-in code to write.
• SQL and Python mix freely in the same pipeline — my bronze layer is Python, curated and gold are plain SQL files.
• Streaming tables remember what they've already processed. Each one keeps a checkpoint in S3 — a record of which input it has already consumed — so every run picks up only what's new. I checked this directly on the reference-data table: after adding a second CSV file, the checkpoint's log showed the next run read just that new file, not the original again. The same mechanism means running round 2 adds that race alongside round 1 instead of starting over. It isn't free in the cost sense — you still pay for the compute that runs — but you don't write or maintain any "last processed" bookmark yourself, which is easy to get subtly wrong in a hand-rolled pipeline. (Materialized views don't get this; they recompute from scratch every run, which is the trade-off listed under limitations below.)
• Tables register themselves in the Glue Data Catalog — no manual CREATE TABLE anywhere.
• VALIDATE mode is a genuinely useful dry run that catches dependency and compile errors before you spend money on a full run.

Limitations of using SDP in AWS Glue 6.0

Three of these — materialized views always recomputing, no data quality expectations, and the .withColumn() gotcha — are also listed under Considerations and limitations in the official AWS docs.

• No CDC/MERGE/upsert. No SCD Type 2 out of the box, as described above.
• No data quality expectations. Inline data quality annotations are not supported in the current SDP framework. Some other declarative frameworks let you attach rules like "this column must not be null" directly to a table definition; SDP in AWS Glue 6.0 has no equivalent yet, so any validation is hand-written. My pipeline doesn't have a quality layer yet, so a malformed API response simply fails the run.
• Materialized views always fully recompute. Only streaming tables are incremental — worth budgeting for as your gold aggregations grow.
• Materialized views can read as empty while a job is running. Every run clears each materialized view first (a TRUNCATE TABLE), and the new rows only land when that view's own step finishes. So an Athena query during a run returns zero rows — not an error, and not the previous data — until the refresh completes. I checked this on a real run of this pipeline by polling Athena every few seconds: each of my four materialized views read 0 rows for roughly 30 seconds of a two-minute run, with the gold tables coming back last, while a streaming table stayed readable the whole time. I couldn't find this in the AWS Glue or Apache Spark documentation; I found the cause in Spark's open-source SDP code. In Spark 4.1.1, the version Glue 6.0 runs, it truncates every non-streaming table at the start of an update. Plan for it: don't point a dashboard or a downstream job at these tables while a run is in progress.
• External API sources need a staging workaround to satisfy the streaming-relation requirement.
• Python packaging has sharp edges that I couldn't find documented. The import-path issue, and the fact that --extra-py-files doesn't reach the SDP process, both cost me real debugging time.
• A .withColumn() gotcha. If a downstream table reads another pipeline table and adds a derived column with .withColumn(...), SDP might fail to detect the dependency on the second and later runs, so the downstream table reads stale data from the previous run (a one-run lag). The documented fix is to build derived columns inside a single .select(...) instead, and to avoid operations that force plan resolution, such as .schema or .collect, inside query functions. I followed this from the start — every derived column in my bronze layer is written in a .select(...) — so I never saw the stale-data problem itself. I did hit a similar sharp edge in the same place: adding .distinct() to a streaming read made the pipeline fail with a misleading "table not found" error, and removing it fixed it.
• VALIDATE still runs your code; it just doesn't write the results. A live-API source will happily call the real API during a "dry run" unless you guard against it.

What I took away from this

My first article proved the concept worked. This one was about whether it survives something less convenient — a second unrelated source, nested data that needs real flattening, and state that has to stay correct across many runs. It does, and the "add a source, write one join" story held up exactly as advertised. That still impresses me.

What surprised me is where the effort actually went. Almost none of it was in the declarative pipeline logic — that part really is as clean as promised. It all went into the handful of places where SDP's abstractions meet the realities of Glue and Spark underneath: the streaming-source rule, the missing-folder failure, and the import path. And none of those were things I could have read my way to. Every one came from running a real job, reading the actual CloudWatch traceback, and occasionally reading the framework's own source code to find out what it was really doing.

If you're evaluating SDP for anything beyond a demo, that's my main advice: budget time for that kind of hands-on verification. The gap between "the docs say" and "a real Glue 6.0 job does" is still a real gap today. Just know that the core promise — one job, no orchestrator, dependencies figured out for you — does deliver.

The code for this project is on GitHub.

References

• "One Glue Job, Zero Orchestrators — My First Spark Declarative Pipeline on AWS Glue", DEV Community
• "Build declarative ETL pipelines with AWS Glue 6.0", AWS Big Data Blog
• AWS Glue Spark Declarative Pipelines documentation
• f1api.dev — the free Formula 1 API used in this project

---

About the author

I'm an AWS Community Builder in the Data category. I build small, real projects and write up what the documentation didn't cover.

Questions, or something broke differently for you? Connect with me on LinkedIn or drop a comment below.