CareerByteCode
ByteBlogby CareerByteCode

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.

Data Engineering Project for Your Resume on Google Cloud

✍️ 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.

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →

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.

The pipeline
Daily CSV files land in Cloud Storage, load into a raw BigQuery table, are cleaned into a staging table and a report table, and checked before anyone sees them

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.json

Step 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.csv
orders_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
Setup
Step 1 and 2: the bucket, two datasets, the raw table and 5,000 orders a day

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-02
Copying 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: OK

Step 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.

Pipeline runs
Two normal days: landed, loaded, transformed, 4 checks pass; the report by day and city

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-03
orders_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 check

The 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,1

All 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:  5025

5,025 raw rows before and after: idempotent.

Break it, fix it, run it again
Day 3 fails 3 checks; version 2 dedupes and quarantines, all checks pass; a re-run keeps 5,025 rows

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
Numbers and clean up
15,025 raw rows, 14,998 clean, 2 quarantined, 14.4 MB scanned; then 0 datasets and 0 buckets left

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 seeWhyFix
Error while reading data on loadA value does not fit the schema typeCheck the line number in the error; fix the feed or the schema
Numbers double after a re-runThe day was appended againDelete the day before loading, as pipeline.sh does
Not found: DatasetDataset in another location, or not createdCreate it with bq --location=us-east1 mk --dataset
A check counts NULL as a rowEmpty CSV fields load as NULLTest IS NULL OR = '', as in checks.sql
The pipeline continues after a failureNo set -e and no exit code from the checksKeep both, as in pipeline.sh

What next

Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.

Read next →dbt with BigQuery: Clean Data Like an Analytics Engineer⏱ 14 min

⭐ Keep learning with CareerByteCode

Hand-picked to go deeper on this topic
News, guides and updates from the CareerByteCode team
🏅 Expert writer

The CareerByteCode team writes about what is new on careerbytecode.in: courses, labs, exams, events and the community, which is free to join for influencers and content creators in tech.

Get 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.

💬 Comments (0)

Top · Newest

Sign in to comment

Related blogs

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →