# Data engineering project: an end-to-end pipeline on Databricks, from raw files to AI

An end-to-end data pipeline takes data from where it is made to where it is used, and proves each step: files land in storage, a contract check and an incremental load write bronze, cleaning and keys make silver, business tables make gold, and governed views, dashboards, an app copy and an AI index serve it. A job runs it on a schedule, tests and a bundle ship it, and a cost report watches it. Start the project from a brief of testable requirements, not from the tools.

- Page: https://tecyfy.com/data-engineering/course/capstone
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 14: Final project, The brief and the pipeline
- Parts of this module: The brief and the pipeline (https://tecyfy.com/data-engineering/course/capstone); The production readiness review (https://tecyfy.com/data-engineering/course/capstone/review); Build it: run it end to end and write it up (https://tecyfy.com/data-engineering/course/capstone/build-it)

At the end of this part you can:

- Turn a business request into requirements a pipeline can be checked against
- Lay out an end-to-end pipeline from raw files to a dashboard, an app and an AI answer, and say where each step runs
- Write down the design decisions, such as batch or stream, with what would change them

The course built the shop's data 1 piece at a time, each module adding 1 part to the same project: one shop's orders, from raw files to a dashboard and an AI answer. This module puts the pieces together and checks them as a whole. It writes no new technique; every step names the module that taught it, and each of those modules' "Build it" parts rebuilds its piece from nothing. This part is the plan, [part 2](/data-engineering/course/capstone/review) the checklist it must pass, and [part 3](/data-engineering/course/capstone/build-it) the run.

## The brief

A pipeline is built for someone. Before any tool, write down what they need, in words a check can test. The shop's owner asked for 7 things:

1. **Yesterday's sales on the dashboard by 07:00 UTC**, every day, by day and by country.
2. **Analysts never see a full email address.** Personal data stays with the people allowed to see it.
3. **A bad file never reaches the dashboard**, and someone is told the same morning.
4. **Any day can be loaded again**, to fix a mistake or fill a gap, with 1 command and no duplicates.
5. **A change ships only after its tests pass**, and production runs only reviewed code.
6. **The cost is known**: each run, each month, and an alert before it runs away.
7. **The help assistant answers from the current help articles**, and says where each answer came from.

Each line is a requirement and, in part 2, a check. "Fast" and "reliable" are not in it, because nobody can test them; "by 07:00" and "with no duplicates" can be.

## The pipeline, step by step

The shop's data moves through 11 steps. On Databricks each is a table, a task or a setting in 1 catalog, with bronze, silver and gold as its schemas, the layering Databricks calls the medallion architecture:

| Step | What it does | On Databricks | Built in |
| --- | --- | --- | --- |
| Land | The shop's system drops 1 order file a night | A volume, `bronze/landing` | [Module 6](/data-engineering/course/getting-data-in) |
| Check the contract | Columns, types and rules checked before the load | A job task | [Module 11](/data-engineering/course/testing-and-ci-cd/data-contracts) |
| Bronze | Every file loaded once, as it arrived | Auto Loader into a bronze table | [Module 6](/data-engineering/course/getting-data-in/build-it) |
| Silver | Typed, checked rows, 1 per order; customers with their history | A pipeline with expectations, MERGE, type 2 dimension | Modules [2](/data-engineering/course/sql-and-data-modelling), [4](/data-engineering/course/delta-lake-and-iceberg) and [7](/data-engineering/course/medallion-pipelines) |
| Gold | Sales per day, spend per customer | Tables built after a quality gate | [Module 7](/data-engineering/course/medallion-pipelines/build-it) |
| Govern | Who reads what; emails masked | Unity Catalog grants, a column mask, tags | [Module 5](/data-engineering/course/unity-catalog) |
| Serve | Checked tables, a metric view, a dashboard, Genie, an app copy, a share | SQL tasks, Lakebase, Delta Sharing | [Module 8](/data-engineering/course/serving-data) |
| Run | Every night, for a date, with retries and alerts | A Lakeflow job | [Module 10](/data-engineering/course/orchestration) |
| Ship | Tests on every change, deploys from Git | A bundle and GitHub Actions | [Module 11](/data-engineering/course/testing-and-ci-cd) |
| Watch cost | Cost per run and month, a budget | Billing system tables | [Module 12](/data-engineering/course/performance-and-cost) |
| Feed AI | Help chunks, an index kept in sync, answers with sources | AI Search, AI Functions | [Module 13](/data-engineering/course/data-for-ai) |

Read the table top to bottom as 1 order's journey: a line in a file at night, a bronze row, a silver row, part of a day's total in gold, a bar on the dashboard at 07:00. The modules taught the steps on your computer first, and each "On Databricks" section said what changes there: volume paths, Unity Catalog names, `SparkSession.builder.getOrCreate()`.

## The decisions to write down

Most of a pipeline is decided before it is written. Write each decision down with its reason and with what would change it, so the next person, or you in a year, can tell a choice from an accident:

- **Batch, not a stream.** The data arrives once a night and is read once a morning. [Module 9's](/data-engineering/course/streaming/on-databricks) rule: a stream that never stops is for when someone acts on the data within minutes. *It changes if* the shop wants sales within the hour during a sale.
- **Silver and gold in a declarative pipeline, or in scripts.** [Module 7](/data-engineering/course/medallion-pipelines) built them as a Spark Declarative Pipelines project, which manages the order of the tables and their expectations; [module 10](/data-engineering/course/orchestration) loaded a day with scripts, which keeps every step in plain Python. Both pass the brief. Choose 1, and say why: a team that already writes SQL tables may prefer the pipeline, a team that tests every function in Python the scripts.
- **Replace the day, never append.** Every task writes its day's rows in place of what was there, so a rerun or a backfill is the same command. *It never changes*: requirement 4 depends on it.
- **Serverless jobs in standard mode.** [Module 12](/data-engineering/course/performance-and-cost): a few minutes of start-up costs nothing against a 07:00 deadline, and standard mode uses fewer DBUs. *It changes if* the job no longer finishes in time.
- **Personal data in silver, behind a mask.** [Module 5](/data-engineering/course/unity-catalog): gold has no full emails, so most people never need access to silver at all.

## The repository

Everything the pipeline is lives in 1 Git repository, deployed by [module 11's](/data-engineering/course/testing-and-ci-cd/bundles) bundle. Module by module, it fills up like this:

```text
shop/
├── databricks.yml              # the jobs, their schedules and the dev and prod targets (module 11)
├── pyproject.toml              # the shop package, built as a wheel
├── contracts/orders.yaml       # what an order file must look like (module 11)
├── src/
│   ├── shop/                   # the logic, as functions the tests import
│   ├── check_contract.py       # the job's tasks, 1 file each (modules 10 and 11)
│   ├── load_day.py
│   ├── check_day.py
│   ├── quality.py
│   ├── sales.sql               # the serving layer's tables and checks (module 8)
│   ├── lookup.sql
│   ├── check_serving.py
│   ├── chunk_help.py           # the help chunks and the index sync (module 13)
│   └── sync_index.py
├── tests/                      # pytest, on rows made up for each case (module 11)
├── .github/workflows/shop.yml  # test, deploy to dev, deploy to prod (module 11)
└── README.md                   # the design document (part 3)
```

If silver and gold are module 7's pipeline instead of scripts, its `transformations/` folder joins `src/`, and the bundle runs the pipeline as a task. The catalog's layout, its grants and its mask are set once, by module 5's `setup_catalog.py` and its SQL, and belong in the repository too, so a new workspace can be set up from it.

Nothing in the tree is new. [Part 2](/data-engineering/course/capstone/review) is the checklist the whole must pass before the owner relies on it.

## Check yourself

1. The shop's orders arrive as 1 file a night, and the brief asks for yesterday's sales on the dashboard by 07:00. A teammate proposes a stream from Kafka. What does the brief call for?

   A. A scheduled batch job, because nothing in the brief needs data sooner than once a day
   B. A stream, because streams are always more up to date
   C. A stream, because batch jobs cannot be retried
   D. Both, so the dashboard has 2 sources

2. A marketing analyst asks for customers' email addresses in the gold layer for a campaign. Which design keeps the brief's rule that analysts never see a full email?

   A. Copy the emails into a gold table only the analyst can read
   B. Give the analyst SELECT on silver.customers
   C. Turn the email mask off for the campaign week
   D. Leave gold without emails, and give the campaign team membership of the group the mask lets through, so the full address is shown only to them

### Answers

1. A. Module 9's rule is that a stream is worth its cost when someone acts on the data within minutes. A dashboard read in the morning, from a file that arrives once a night, is a daily batch job; a stream would run all day to deliver the same numbers. If the brief later asks for sales within 5 minutes, the decision changes, which is why it is written down with its reason.
2. D. Module 5 put personal data in silver behind a column mask that asks who is reading. Adding the campaign team to the group the mask allows gives them the addresses wherever the table is read, with the access recorded in 1 place. A copy in gold would carry the emails outside the mask, and turning the mask off exposes them to everyone.

## Questions

### What is a good data engineering portfolio project?

One that takes real or realistic data from raw files to something people use, a dashboard, an app or an AI answer, and shows the work around the code. Include an incremental load that can be rerun, data quality checks that stop bad data, tests and a deployment from Git, access rules for personal data, and what it costs to run. Write a short design document with the decisions and their reasons, because that is what an interviewer asks about.

### What does an end-to-end data pipeline include?

Ingestion from the sources into raw tables, cleaning and modelling into trusted tables, business tables for analytics, and serving to the people and systems that use them. Around those: a schedule and retries, checks that stop bad data, access control, monitoring and alerts, tests and automated deployment, and a cost report. Databricks calls the raw, trusted and business layers bronze, silver and gold, the medallion architecture.

## Sources

- [What is the medallion lakehouse architecture?](https://docs.databricks.com/aws/en/lakehouse/medallion)
- [Best practices for reliability](https://docs.databricks.com/aws/en/lakehouse-architecture/reliability/best-practices)
- [Best practices for operational excellence](https://docs.databricks.com/aws/en/lakehouse-architecture/operational-excellence/best-practices)