Capstone Project — StepRite Databricks Lakehouse

This is the capstone project for the course: a complete, end-to-end Lakehouse build for a real (fictional) company, StepRite. Everything from the earlier chapters — Lakeflow Connect, Spark Declarative Pipelines, Lakeflow Jobs — comes together here into a single production-shaped project.

This lecture is the project brief: the business requirements, the architecture decisions behind the design, and the guidelines that shape how the rest of the build should be approached. It's written as an assignment, not a walkthrough — the goal is for you to design and build this yourself, using everything covered in the course so far. If you've completed the rest of the course, every technique used here should already be familiar; anything genuinely new will be introduced exactly where it's needed.

The Business: StepRite

StepRite is a mid-size online footwear retailer — real shoes, real warehouses, real customers.

The business itself is intentionally simple. The point of this project is design and engineering practice, not understanding a complicated business domain.

Why This Project Exists

StepRite isn't hiring for this project because Lakehouse architecture is trendy — they're hiring because three real, expensive problems are already costing them money and trust.

• VP of Finance needs a revenue report every morning. Today, it's a spreadsheet manually stitched together from three different sources, arrives a day late, and the numbers didn't match twice this quarter due to human error in the compilation. The business can't trust its own numbers.
• Marketing can't answer "who are my best customers?" or "who's about to churn?" without pulling three people into a room for half a day, exporting and cross-referencing spreadsheets — because customer data, order history, and support data all live in different systems that don't talk to each other.
• Merchandising/procurement reorders stock based on gut feel and a monthly Excel report that's already stale by the time anyone reads it. Popular sizes go out of stock; unpopular ones pile up as dead stock. Both are real money: blocked capital on one side, lost revenue on the other.

Three pain points, three different departments — that's the actual reason this project exists.

What You've Been Given: The Source Systems

An important constraint to internalize before designing anything: you don't get to choose your source systems. They already exist, with whatever limitations they have, and the architecture has to work within them.

In short: two transactional systems that can't absorb heavy analytical load, two batch file sources already running on a daily cycle that can't be pushed faster, and one raw event stream that's completely untouched so far.

Architecture Decisions

Decision 1: How do we get data out of the transactional systems?

Two systems (orders, customers) and the storefront event stream are all backed by relational databases. Three options exist for pulling data out of them, and two of them don't survive contact with the constraints already established:

• Query the OLTP database directly, on demand. Attractive because it needs no new infrastructure — but every analytical query becomes extra load on a system built for transactional traffic. A heavy query at the wrong moment can genuinely slow down live checkout for paying customers. Rejected outright — this is a risk the business cannot accept.
• Nightly batch export, the same pattern already used for the file-based sources. This removes the load problem entirely, since the live system is never touched during the day. But it reintroduces the exact staleness problem this project exists to fix — the VP of Finance explicitly wants numbers that aren't a day late, and marketing wants signals close to real time.
• Change Data Capture (CDC). A tool like Debezium sits next to the transactional database and reads its change log — not the live tables — streaming out every insert, update, and delete as a small event. Zero extra query load on the source, and changes surface in minutes rather than a day later.

The CDC decision — the only option that satisfies both constraints

CDC via Debezium is the decision — for orders, order items, customers, and the same approach for the clickstream. Not because CDC is a fashionable term, but because it's the only one of the three options that doesn't break a constraint already agreed to be non-negotiable.

Scope note: this project does not involve installing or configuring Debezium itself. The premise is that Debezium is already running, dropping flattened CDC JSON files into a landing zone — fully compliant with real Debezium output — and the project picks up the story from there, using Auto Loader to consume from that landing zone. (Lakeflow Connect was considered as an alternative to Debezium, but its CDC connectors are still in beta as of this recording — only Microsoft SQL Server is GA — so it's set aside in favor of Debezium for this project.)

Decision 2: How do we ingest the file-based sources?

Products, categories, and inventory arrive as files, not database load — but a reliable mechanism is still needed: one that picks up new files automatically, never processes the same file twice, and doesn't need new custom code every time a source folder changes. That's exactly Auto Loader's job — incremental file discovery, schema inference, and schema evolution, all built in, rather than reinventing exactly-once file tracking by hand.

The Complete Architecture

StepRite's complete data architecture

Putting it together: the two transactional systems and the clickstream flow through Debezium (or Lakeflow Connect), landing as files in an S3 bucket mapped as a Unity Catalog volume. The product catalog and inventory snapshots land in the same volume via direct upload or API. From there, Auto Loader is the single ingestion mechanism for everything sitting in that landing zone — regardless of whether a file originated as a CDC event or a native CSV export — feeding into bronze, then silver, then gold.

Decision 3: Why medallion layers, not straight to gold?

The tempting shortcut is building directly from raw ingested data to whatever report the business asked for. That breaks quickly: something upstream will always change — a schema shift, a data quality issue discovered weeks in — and if the final report sits directly on raw data, there's no clean, trusted version of history to fall back to. You end up manually reprocessing raw files by hand.

Why layers — Bronze, Silver, and Gold each answer a different question

• Bronze — a durable, replayable record of exactly what arrived, so a downstream rebuild never has to re-touch the source systems.
• Silver — what arrived isn't the same as what's true. CDC events become real history (SCD Type 2), and file-based sources get genuine validation rather than a raw copy.
• Gold — the business wants answers, not tables. Answers come from joins and aggregations over silver, not straight off raw bronze.

The Five Gold Outputs

Connecting gold back to the actual stakeholders who asked for something:

Two stakeholders — Growth and Operations — haven't come up yet in the pain points above, but their needs follow the same pattern: Growth needs clickstream data joined with completed orders to see which marketing channel actually converts; Operations needs to know whether promised delivery timelines are actually being met.

Why five separate gold tables, rather than one big table joining everything? These five stakeholders don't share a refresh schedule, an owner, or a failure blast radius. Forcing Growth's need for a fast refresh and Finance's comfort with daily refresh onto one shared pipeline serves neither well — and a small bug fix to funnel logic shouldn't be able to take down fulfillment reporting at the same time. Five separate materialized views and separate transformation files is what lets five different teams depend on this system without depending on each other's failures.

Design Guidelines for Production

Three more decisions shape this project — not about ingestion or transformation, but about what happens after the system goes live and keeps running for months.

What happens after this works once — three production risks and their mitigations

1. Referential integrity: declare it, don't enforce it, monitor it. A team that ignores referential integrity entirely risks gold-layer joins silently dropping or double-counting rows — nobody notices until finance asks why a quarter's revenue doesn't match, and then someone has to debug the entire pipeline to find one bad record. Hard-enforcing referential integrity on every write doesn't scale at Lakehouse volumes, and worse, since orders, order items, and customers each arrive via independent CDC streams, an order item can legitimately arrive before its parent order — a hard rule would reject perfectly valid data purely because of timing. The resolution: sample data used for development should have valid foreign keys throughout, but the actual solution declares foreign key constraints in Unity Catalog without enforcing them — documenting the relationship for tools and the query optimizer without ever blocking a write — and separately monitors referential integrity as a scheduled data quality check that alerts on any orphaned rows.

2. Deploy through a separate production workspace with a CI/CD gate. A team with a single workspace and no deployment gate is one small typo away from a bug going live directly against the same catalog the CFO's dashboard reads from. The resolution: a genuinely separate production workspace with its own Unity Catalog, where nothing reaches it directly — every change passes through a CI/CD pipeline with a manual approval step before anything touches production.

3. Build testing in from the start. A team with no tests finds out their silver transformation is broken from an angry email, not from their own monitoring. The resolution: unit tests, integration tests, and data quality checks as code, built at the point in the project where skipping them would actually hurt — not bolted on as an afterthought, and not front-loaded as pure theory either.

Your Assignment

Design and build StepRite's Lakehouse yourself, using the architecture and decisions above as your brief. The project breaks down into six modules — this lecture covers the first part of Module 1; everything after that is yours to build.

1. Project foundations — design the architecture (done, above), then set up your environment and seed sample data for the project.
2. Ingestion — build the three ingestion patterns discussed here (CDC via Debezium-style landing files, Auto Loader for file-based sources) for real, into the bronze layer.
3. Transformation — build the silver layer and all five gold data marts.
4. Orchestration — tie everything together into one scheduled job.
5. Testing — unit tests, integration tests, and data quality checks as code.
6. DABs & CI/CD — package the project with Databricks Asset Bundles and promote it through a real approval gate into a production workspace.

Use this document as your specification. Where a design decision isn't fully specified here — table schemas, exact SCD implementation details, specific data quality rules — that's an intentional part of the exercise: make the call, and be ready to justify it the same way every decision above was justified, against the actual constraints StepRite is working under.

Want to See the Full Build?

This brief sets up the project; it doesn't walk through the implementation. For a complete, end-to-end build of this exact capstone — every module, every design decision, all the way through production deployment — the full video course is available here:

Databricks Data Engineering with AWS

Build it yourself first. It'll make the full walkthrough far more useful when you do watch it.