Data Engineering Project for Your Resume on Google Cloud
What you build: a daily batch pipeline on Cloud Storage and BigQuery with layers, data quality checks, quarantine and idempotent re-runs. Every step tested.

✍️ Translate this blog into Hindi, Tamil or Telugu
Most "data engineering projects" on resumes are one SQL query on a public dataset. Interviewers look for something else: data that lands somewhere, a pipeline that loads and transforms it, data quality checks that stop bad data, a run you can repeat without doubling the numbers, and real figures you can quote. In this lab you build that on Google Cloud with Cloud Storage and BigQuery, feed it a dirty day of data on purpose, and fix the pipeline so bad rows never reach the report.
Who it is for: students and engineers moving into data engineering who want one strong, explainable project. Time: about 1.5 hours. Cost: BigQuery free tier (this lab scanned 14.4 MB of the 1 TB free each month) and a few files in Cloud Storage.
Every command below was run for real by the CareerByteCode Team, and every output is the real result we got. We typed the commands in a Cloud Shell-like Linux VM (Debian with gcloud, bq and python3); Cloud Shell in your browser has the same tools.
Part A: the project in plain English
An online shop sends a file of orders every day. Your pipeline must turn those files into a trusted report: revenue per day and city.

Words you will use:
- Landing zone: where raw files arrive unchanged (a Cloud Storage bucket). Keep them: you can always re-run from them.
- Raw, staging, report (or bronze, silver, gold): three layers. Raw is exactly what arrived; staging is cleaned and typed; the report is what dashboards read.
- Schema: the column names and types, written down (
schema.json). Loading with an explicit schema catches type errors early. - Data quality check: a query that counts rows breaking a rule. 0 means pass.
- Quarantine: a table for rows that break rules, kept for inspection instead of deleted or used.
- Idempotent: running the same day twice gives the same result, not double.
- Real-life example: a restaurant kitchen. Deliveries land at the back door (raw), cooks wash and cut (staging), plates go out (report), and the chef tastes before anything leaves (checks). Spoiled boxes go to one side with a label (quarantine).
Before you start
A Google Cloud project with billing linked (the free trial works) and Cloud Shell open. BigQuery and Cloud Storage are on by default in new projects.
Part B: the lab
Step 1: the bucket, the datasets and the raw table
Why: one bucket for the landing zone, one dataset for raw tables and one for clean tables. The raw table is created from schema.json, so its columns and types are fixed and written down:
[
{"name": "order_id", "type": "STRING", "mode": "REQUIRED"},
{"name": "customer_id", "type": "STRING"},
{"name": "city", "type": "STRING"},
{"name": "product", "type": "STRING"},
{"name": "quantity", "type": "INT64"},
{"name": "amount", "type": "NUMERIC"},
{"name": "ordered_at", "type": "TIMESTAMP"}
]The bucket name is made from a hash of the project id, so it is the same every time you run the lab but never shows your project id:
# A bucket for raw files (the "landing zone"), a dataset for raw tables and one for clean tables
echo "dataeng-$(echo -n $PROJECT_ID | md5sum | cut -c1-8)" > .bucket
gcloud storage buckets create gs://$(cat .bucket) --location=us-east1 --uniform-bucket-level-access --public-access-prevention > /dev/null && echo "bucket created"
bq --location=us-east1 mk --dataset shop_raw > /dev/null && bq --location=us-east1 mk --dataset shop > /dev/null && echo "datasets shop_raw and shop created"
bq mk --table shop_raw.orders schema.json > /dev/null && echo "table shop_raw.orders created with schema.json"Creating gs://dataeng-2c18be0d/...
bucket created
datasets shop_raw and shop created
table shop_raw.orders created with schema.jsonStep 2: two days of orders
Why: a small Python generator makes 5,000 orders a day (same day = same data, so your numbers match ours). With --dirty it adds what real feeds send: 25 orders twice, one negative amount, one empty customer id. Save it as gen_orders.py:
# gen_orders.py <date> <rows> [--dirty]: write orders_<date>.csv, a day of online orders (same seed = same data).
# --dirty adds the problems real feeds have: repeated rows, a negative amount and an empty customer id.
import csv, random, sys
day, rows = sys.argv[1], int(sys.argv[2])
random.seed(day)
cities = ["Chennai", "Bengaluru", "Mumbai", "Delhi", "Hyderabad", "Pune"]
products = [("keyboard", 1499), ("mouse", 599), ("monitor", 9999), ("headset", 2499), ("webcam", 3299)]
out = []
for i in range(rows):
p, price = random.choice(products)
qty = random.choice([1, 1, 1, 2, 3])
out.append([f"{day.replace('-', '')}-{i:05d}", f"C{random.randint(1, 4000):04d}", random.choice(cities), p, qty, price * qty,
f"{day}T{random.randint(0, 23):02d}:{random.randint(0, 59):02d}:00"])
if "--dirty" in sys.argv:
out += out[:25] # 25 orders sent twice
out[30][5] = -price # a negative amount
out[31][1] = "" # no customer id
with open(f"orders_{day}.csv", "w", newline="") as f:
w = csv.writer(f)
w.writerow(["order_id", "customer_id", "city", "product", "quantity", "amount", "ordered_at"])
w.writerows(out)
print(f"orders_{day}.csv: {len(out)} rows")# Two normal days of orders, 5,000 a day (the generator is in Python, same seed = same data every time)
python3 gen_orders.py 2026-11-01 5000 && python3 gen_orders.py 2026-11-02 5000
head -3 orders_2026-11-01.csvorders_2026-11-01.csv: 5000 rows
orders_2026-11-02.csv: 5000 rows
order_id,customer_id,city,product,quantity,amount,ordered_at
20261101-00000,C1725,Mumbai,monitor,1,9999,2026-11-01T10:04:00
20261101-00001,C1770,Mumbai,webcam,1,3299,2026-11-01T04:47:00
Step 3: the pipeline
Why: one script runs the whole day, and stops at the first failure: 1) copy the file to the landing zone, 2) delete that day from the raw table (so a re-run replaces it), 3) load the file with the schema, 4) rebuild the staging and report tables, 5) run the checks. Save it as pipeline.sh:
#!/bin/bash
# pipeline.sh <date>: the whole daily pipeline. 1 land the file in Cloud Storage, 2 load it into the raw table,
# (a re-run first removes that day), 3 rebuild the clean staging table and the daily report table, 4 run the data quality checks. Stops on the first failure.
set -e
D=$1; B=gs://$(cat .bucket)
gcloud storage cp -q orders_$D.csv $B/raw/orders_$D.csv
bq query --quiet --use_legacy_sql=false "DELETE FROM shop_raw.orders WHERE DATE(ordered_at) = '$D'" > /dev/null # re-runs replace the day, never double it
bq load --quiet --source_format=CSV --skip_leading_rows=1 shop_raw.orders $B/raw/orders_$D.csv schema.json
bq query --quiet --use_legacy_sql=false < ${TRANSFORM:-transform.sql} > /dev/null
bq query --quiet --use_legacy_sql=false --format=csv < checks.sql | tail -n +2 > checks.csv
cat checks.csv | awk -F, '{printf "check %-28s %s (%s rows)\n", $1, ($2 == 0 ? "PASS" : "FAIL"), $2}'
if awk -F, '$2 != 0 {bad = 1} END {exit !bad}' checks.csv; then echo "pipeline for $D: FAILED a data quality check"; exit 1; fi
echo "pipeline for $D: OK"Version 1 of the transformation trusts the feed: it copies raw rows to staging and builds the report. Save it as transform.sql:
-- staging version 1: copy the raw rows as they are. It trusts the feed.
CREATE OR REPLACE TABLE shop.orders AS
SELECT order_id, customer_id, city, product, quantity, amount, ordered_at
FROM shop_raw.orders;
-- report: revenue per day and city, the table a dashboard reads
CREATE OR REPLACE TABLE shop.daily_city_revenue AS
SELECT DATE(ordered_at) AS day, city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM shop.orders
GROUP BY day, city;The checks, one rule per line. The last one checks that the report adds up to the same total as staging. Save it as checks.sql:
-- data quality checks: each line counts the rows that break one rule. 0 everywhere = PASS
SELECT 'duplicate order_id' AS rule, COUNT(*) AS bad_rows FROM (SELECT order_id FROM shop.orders GROUP BY order_id HAVING COUNT(*) > 1)
UNION ALL SELECT 'missing customer_id', COUNT(*) FROM shop.orders WHERE customer_id IS NULL OR customer_id = ''
UNION ALL SELECT 'negative or zero amount', COUNT(*) FROM shop.orders WHERE amount <= 0
UNION ALL SELECT 'report total differs', IF((SELECT SUM(revenue) FROM shop.daily_city_revenue) = (SELECT SUM(amount) FROM shop.orders), 0, 1)
ORDER BY rule;# Run the daily pipeline for both days: land, load, transform, check
chmod +x pipeline.sh && ./pipeline.sh 2026-11-01 && ./pipeline.sh 2026-11-02Copying file://orders_2026-11-01.csv to gs://dataeng-2c18be0d/raw/orders_2026-11-01.csv
.
check duplicate order_id PASS (0 rows)
check missing customer_id PASS (0 rows)
check negative or zero amount PASS (0 rows)
check report total differs PASS (0 rows)
pipeline for 2026-11-01: OK
Copying file://orders_2026-11-02.csv to gs://dataeng-2c18be0d/raw/orders_2026-11-02.csv
.
check duplicate order_id PASS (0 rows)
check missing customer_id PASS (0 rows)
check negative or zero amount PASS (0 rows)
check report total differs PASS (0 rows)
pipeline for 2026-11-02: OKStep 4: the report
# The report table a dashboard would read: revenue per day and city
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT day, city, orders, revenue FROM shop.daily_city_revenue ORDER BY day, revenue DESC LIMIT 6"+------------+-----------+--------+---------+
| day | city | orders | revenue |
+------------+-----------+--------+---------+
| 2026-11-01 | Pune | 890 | 5133205 |
| 2026-11-01 | Hyderabad | 797 | 4822412 |
| 2026-11-01 | Delhi | 850 | 4759381 |
| 2026-11-01 | Chennai | 807 | 4738606 |
| 2026-11-01 | Bengaluru | 810 | 4714627 |
| 2026-11-01 | Mumbai | 846 | 4689366 |
+------------+-----------+--------+---------+This is the table a Looker Studio dashboard or a manager's spreadsheet would read.

Break it: a dirty day
Day 3 arrives with orders sent twice, a negative amount and an empty customer id:
# Break it: day 3 arrives dirty (orders sent twice, a negative amount, no customer id). The checks stop the pipeline
python3 gen_orders.py 2026-11-03 5000 --dirty
./pipeline.sh 2026-11-03orders_2026-11-03.csv: 5025 rows
Copying file://orders_2026-11-03.csv to gs://dataeng-2c18be0d/raw/orders_2026-11-03.csv
.
check duplicate order_id FAIL (25 rows)
check missing customer_id FAIL (1 rows)
check negative or zero amount FAIL (1 rows)
check report total differs PASS (0 rows)
pipeline for 2026-11-03: FAILED a data quality checkThe checks caught all three problems (25 duplicate orders, 1 missing customer, 1 negative amount) and the pipeline failed, so nobody should trust today's report until it is fixed. The "report total" check passed: the report still matched staging, but staging itself was wrong. That is why you need checks on the data, not only on the totals.
Fix it: dedupe and quarantine
Why: version 2 of the transformation keeps one row per order (ROW_NUMBER() over order_id) and moves rows that break a rule to orders_quarantine with the reason, instead of into the report. Save it as transform_v2.sql:
-- staging version 2: keep ONE row per order_id, and send rows that break a rule to a quarantine table
-- instead of the report, so a bad feed can never change the numbers people see.
CREATE OR REPLACE TABLE shop.orders_quarantine AS
SELECT *, CASE WHEN customer_id IS NULL OR customer_id = '' THEN 'missing customer_id' ELSE 'negative or zero amount' END AS reason
FROM shop_raw.orders
WHERE customer_id IS NULL OR customer_id = '' OR amount <= 0;
CREATE OR REPLACE TABLE shop.orders AS
SELECT * EXCEPT (copy_no) FROM (
SELECT order_id, customer_id, city, product, quantity, amount, ordered_at,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY ordered_at DESC) AS copy_no
FROM shop_raw.orders
WHERE customer_id IS NOT NULL AND customer_id != '' AND amount > 0
)
WHERE copy_no = 1;
CREATE OR REPLACE TABLE shop.daily_city_revenue AS
SELECT DATE(ordered_at) AS day, city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM shop.orders
GROUP BY day, city;We run day 3 again with version 2 (the pipeline replaces the day, so nothing is loaded twice):
# Fix it: transform version 2 keeps one row per order and moves broken rows to a quarantine table. Re-run day 3
TRANSFORM=transform_v2.sql ./pipeline.sh 2026-11-03
bq query --quiet --use_legacy_sql=false --format=csv "SELECT reason, COUNT(*) AS n FROM shop.orders_quarantine GROUP BY reason ORDER BY reason" | tail -n +2 | sed 's/^/quarantined: /'🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโน499Build the projects →Copying file://orders_2026-11-03.csv to gs://dataeng-2c18be0d/raw/orders_2026-11-03.csv
.
check duplicate order_id PASS (0 rows)
check missing customer_id PASS (0 rows)
check negative or zero amount PASS (0 rows)
check report total differs PASS (0 rows)
pipeline for 2026-11-03: OK
quarantined: missing customer_id,1
quarantined: negative or zero amount,1All checks pass, and the two broken rows wait in quarantine with their reason, ready for someone to ask the shop's IT team about them.
Step 5: run it again, get the same numbers
Why: pipelines get re-run all the time (a retry after an error, a late file, a fix). If a re-run adds the day again, every report doubles. Ours deletes the day first:
# Idempotent: running the same day again gives the same numbers (the day is replaced, not added twice)
Q="SELECT COUNT(*) FROM shop_raw.orders WHERE DATE(ordered_at) = '2026-11-03'"
echo "raw rows for day 3 before: $(bq query --quiet --use_legacy_sql=false --format=csv "$Q" | tail -1)"
TRANSFORM=transform_v2.sql ./pipeline.sh 2026-11-03 | tail -1
echo "raw rows for day 3 after: $(bq query --quiet --use_legacy_sql=false --format=csv "$Q" | tail -1)"raw rows for day 3 before: 5025
Copying file://orders_2026-11-03.csv to gs://dataeng-2c18be0d/raw/orders_2026-11-03.csv
.
pipeline for 2026-11-03: OK
raw rows for day 3 after: 50255,025 raw rows before and after: idempotent.

Step 6: your resume numbers
# Your resume numbers, measured: rows processed, days, checks, and how much data each run scanned
bq query --quiet --use_legacy_sql=false --format=csv "SELECT COUNT(*) AS raw_rows, (SELECT COUNT(*) FROM shop.orders) AS clean_rows, (SELECT COUNT(*) FROM shop.orders_quarantine) AS quarantined, COUNT(DISTINCT DATE(ordered_at)) AS days FROM shop_raw.orders" | tail -1 | awk -F, '{print "raw rows " $1 ", clean rows " $2 ", quarantined " $3 ", days " $4}'
bq ls -j -n 50 --format=json | python3 -c "import json, sys; j = [x for x in json.load(sys.stdin) if x['configuration'].get('query')]; b = sum(int(x['statistics'].get('totalBytesProcessed', 0)) for x in j); print(f'{len(j)} query jobs, {b / 1e6:.1f} MB scanned in total (the free tier is 1 TB a month)')"raw rows 15025, clean rows 14998, quarantined 2, days 3
20 query jobs, 14.4 MB scanned in total (the free tier is 1 TB a month)Your resume lines (with the numbers you measured)
- Built a daily batch pipeline on Google Cloud (Cloud Storage landing zone, BigQuery raw, staging and report layers) for 3 days and 15,025 order rows.
- Added 4 automated data quality checks; a dirty feed (25 duplicates, 2 invalid rows) was stopped before reaching the report.
- Made the pipeline idempotent and added deduplication and a quarantine table; 14,998 clean rows, 2 quarantined.
- Kept cost near zero: 20 queries scanned 14.4 MB in total, inside the BigQuery free tier.
POC: prove it works
- Two normal days passed all 4 checks and built the report (Steps 3 and 4).
- The dirty day failed 3 checks and stopped the pipeline (Break it).
- Version 2 removed the duplicates, quarantined the 2 broken rows and passed (Fix it).
- A re-run kept exactly 5,025 rows for the day (Step 5).
Clean up (delete everything)
# Clean up: both datasets with their tables, and the bucket
bq rm -r -f -d shop > /dev/null && bq rm -r -f -d shop_raw > /dev/null && echo "datasets deleted"
gcloud storage rm -r gs://$(cat .bucket) --quiet > /dev/null 2>&1; echo "left: $(bq ls --format=csv 2>/dev/null | grep -cE '^shop(_raw)?$' ) datasets, $(gcloud storage ls 2>/dev/null | grep -c dataeng-) buckets"datasets deleted
left: 0 datasets, 0 buckets
FAQ
Why not just use the raw table in the report? Raw data changes shape, repeats and breaks. The layers let you fix problems in one place (staging) without touching what arrived.
How would this run every day? With a scheduler: Cloud Scheduler calling a Cloud Run job, a Cloud Composer (Airflow) DAG, or BigQuery scheduled queries for the SQL steps. We ran it by hand here.
Is this dbt? It is the same idea in plain SQL files. dbt adds dependencies between models, tests as config and documentation; see our dbt lab when you want the next step.
Why delete and reload a day instead of MERGE? For whole-day files it is simple and clearly idempotent. For changes to single rows, MERGE on the order id is better.
How big can this go? BigQuery handles billions of rows with the same SQL. Partition the raw table by day (PARTITION BY DATE(ordered_at)) so each run reads only its day.
Troubleshooting
| You see | Why | Fix |
|---|---|---|
Error while reading data on load | A value does not fit the schema type | Check the line number in the error; fix the feed or the schema |
| Numbers double after a re-run | The day was appended again | Delete the day before loading, as pipeline.sh does |
Not found: Dataset | Dataset in another location, or not created | Create it with bq --location=us-east1 mk --dataset |
| A check counts NULL as a row | Empty CSV fields load as NULL | Test IS NULL OR = '', as in checks.sql |
| The pipeline continues after a failure | No set -e and no exit code from the checks | Keep both, as in pipeline.sh |
What next
- Ask the same data questions in plain English: Text-to-SQL: Ask BigQuery Questions in Plain English.
- Put a delivery pipeline around your code: DevOps Project for Your Resume: Full CI/CD on Google Cloud.
- More hands-on posts: ByteBlog. Practice in ByteLabs and see the courses.
Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.
⭐ Keep learning with CareerByteCode
Hand-picked to go deeper on this topicGet CareerByteCode Team's next blog by email
One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.
We email you once to confirm. By subscribing you agree to the privacy policy.
Related blogs
CareerByteCode Teamdbt with BigQuery: Clean Data Like an Analytics Engineer
What you build: a dbt project on BigQuery that cleans messy raw CSVs into a tested mart table; the tests really catch a duplicate order and an unknown status, then pass 11 of 11 after the fix.
#bigquery
CareerByteCode TeamRedis Caching: Make Your API 10x Faster
What you build: an API on a slow 1-million-row database with and without a Redis cache on a free VM, load-tested (about 20x more requests per second), plus stale data and a full cache broken and fixed
#bigquery
CareerByteCode TeamSpanner Free Trial: Global SQL Database Hands-On
What you build: a free trial Cloud Spanner instance with interleaved tables, real duplicate and orphan errors, a unique index, a Python app saving an order and points in one transaction, and a stale r
#bigquery
CareerByteCode TeamBigQuery ML: Train a Model with Just SQL
What you build: a churn prediction model written in SQL with BigQuery ML, scored and used for predictions, in your own Google Cloud account. Every step tested.
💬 Comments (0)
Top · NewestSign in to comment