The problem
Most of my projects had one data source. I wanted to see what breaks with five
Every ETL toy project I'd built before this pulled from one API and wrote to one table. That's not really what a data engineering job looks like. So I picked something with enough variety to actually force the issues: batch REST APIs, a live streaming feed, and change data capture off a real database, all landing in the same warehouse and getting reconciled into one set of tables a frontend can query.
Flicker pulls the roughly 2,000 most popular films from TMDB, enriches them with critic scores from OMDb and trailer stats from YouTube, scores YouTube comment sentiment off a Kafka stream with VADER, and runs a simulated Postgres table tracking each film's theatrical run through Debezium CDC. All five land in a Bronze/Silver/Gold warehouse on DuckDB, orchestrated daily by Airflow, and a Next.js app reads the Gold layer for the dashboards and a weekly newsletter.
Architecture
Five sources, one warehouse
The batch side (ingestion/*.py) hits the TMDB, OMDb, and YouTube APIs and upserts raw rows into Bronze by ID, so re-running a script twice doesn't duplicate anything. The streaming side is separate: Debezium tails the Postgres write-ahead log for a film_lifecycle table and publishes every insert, update, and delete to Kafka, and a Python consumer flattens each Debezium envelope (before-image, after-image, operation type) into Bronze. Current state for a film's theatrical run gets rebuilt entirely from that change stream, not from a batch snapshot.
dbt then takes Bronze through Silver (typed, deduplicated staging models) into Gold (the marts the frontend actually queries: ROI by decade, buzz vs. critics, hype vs. box office). DuckDB is the warehouse locally and MotherDuck is the cloud copy the deployed app reads from.
Orchestration
Why the daily DAG is strictly sequential
The daily pipeline DAG runs ingest_tmdb, then enrich_omdb, then fetch_trailers, then dbt build, then a health check, all one after another with no fan-out. That was a deliberate call, not a missed optimization: DuckDB is a single-writer database, so two ingestion tasks writing at the same time would just fight over the file lock. Serializing the DAG and setting max_active_runs=1 was the actual fix for that, not a retry loop.
There's a separate weekly DAG for the newsletter that runs an hour after the streaming refresh finishes rebuilding Gold, so subscribers get picks based on that week's sentiment data instead of stale numbers.
What broke
Two bugs from getting this onto Vercel
Bug 1 — Turbopack silently broke the native DuckDB binding
The Next.js app uses @duckdb/node-api, a native binding, to query MotherDuck directly from API routes. Production builds ran fine under Turbopack. Then any dynamically-rendered route (one reading query params or a request body) built without errors and then crashed at runtime once deployed on Vercel. Turbopack just wasn't externalizing the native module correctly for those routes.
// package.json
"build": "next build --webpack"Switching the production build to Webpack fixed it outright. I never dug into exactly why Turbopack's externalization logic missed this case, I just confirmed Webpack handled it and moved on.
Bug 2 — DuckDB had no HOME directory to write to
Past the build issue, the same routes still failed on Vercel. Vercel's Lambda runtime doesn't set HOME, and DuckDB needs a writable home directory to resolve its config and extension state (MotherDuck ships as an extension). Locally this never came up because my shell always has HOME set.
if (!process.env.HOME) process.env.HOME = "/tmp";
const instance = await DuckDBInstance.create(dbPath, {
home_directory: process.env.HOME || "/tmp",
});Falling back to /tmp, which always exists in a Lambda, fixed it. While I was in there I also changed the connection cache from storing a resolved connection to caching the in-flight connect promise, and serialized all queries onto one queue, so concurrent requests on a cold start await one connection attempt instead of each racing to open their own and crashing the function.
Design decision
API routes, not server components, for anything touching DuckDB
All the DuckDB access goes through API routes under web/src/app/api, and the pages themselves are client components that fetch from those routes instead of querying the warehouse directly in a server component. That keeps the native binding out of the React render path entirely, which mattered once I saw how easily it could crash a route under the wrong bundler.
One thing I haven't gone back to clean up: the CDC side is a simulated Postgres table, not a real production database, because setting up an actual operational system just to CDC off it felt like a lot of infrastructure for a portfolio project. The Debezium and Kafka plumbing around it is real and behaves the same way it would against a real database, but I know the seed data itself is synthetic, and I haven't pretended otherwise.