GCP Data Engineering Projects for Your Portfolio

Most portfolios say the same thing: I followed a tutorial. A cloud account, a dataset everyone uses, a dashboard at the end. A reviewer has seen it many times and learns nothing from it.

GCP data engineering projects become evidence when they show what a tutorial hides: what was wrong with the data, what happens when the job runs twice, what it costs, and what you would do differently. This guide gives six projects in rising difficulty and a standard for writing them up.

What makes a project count

None of this needs a large project. It needs a finished one.

Start by looking at the data

Whatever you build, the first section of the write-up is a profile of the source. Here is one for a dataset many learners use: New York yellow taxi trips for January 2024 (NYC TLC: trip record data), checked against its data dictionary (NYC TLC: yellow taxi data dictionary).

It ran locally with DuckDB, not on Google Cloud.

import duckdb

db = duckdb.connect()
db.execute("CREATE VIEW trips AS SELECT * FROM read_parquet('../../dedata/yellow_tripdata_2024-01.parquet')")

total = db.execute("SELECT count(*) FROM trips").fetchone()[0]
print(f"rows in the January 2024 file: {total:,}")
print()

CHECKS = {
    "pickup outside January 2024": "tpep_pickup_datetime < '2024-01-01' OR tpep_pickup_datetime >= '2024-02-01'",
    "drop-off not after pickup": "tpep_dropoff_datetime <= tpep_pickup_datetime",
    "trip distance is zero": "trip_distance = 0",
    "fare is zero or negative": "fare_amount <= 0",
    "passenger count missing": "passenger_count IS NULL",
    "trip longer than 24 hours": "tpep_dropoff_datetime - tpep_pickup_datetime > INTERVAL 24 HOUR",
}
print("problem                           rows    share")
for name, condition in CHECKS.items():
    n = db.execute(f"SELECT count(*) FROM trips WHERE {condition}").fetchone()[0]
    print(f"{name:30} {n:>7,}   {n / total:>6.2%}")

RULES = [CHECKS[k] for k in ("pickup outside January 2024", "drop-off not after pickup",
                             "trip distance is zero", "fare is zero or negative")]
kept = db.execute(f"SELECT count(*) FROM trips WHERE NOT ({' OR '.join('(' + r + ')' for r in RULES)})").fetchone()[0]
print()
print(f"kept after four cleaning rules: {kept:,} of {total:,} ({kept / total:.1%}); rejected {total - kept:,}")

days, low, high = db.execute("""
    SELECT count(*), min(n), max(n) FROM (
        SELECT CAST(tpep_pickup_datetime AS DATE) AS d, count(*) AS n FROM trips
        WHERE tpep_pickup_datetime >= '2024-01-01' AND tpep_pickup_datetime < '2024-02-01' GROUP BY 1)""").fetchone()
print(f"daily partitions: {days}, from {low:,} to {high:,} rows a day")

rows in the January 2024 file: 2,964,624

problem                           rows    share
pickup outside January 2024         18    0.00%
drop-off not after pickup          870    0.03%
trip distance is zero           60,371    2.04%
fare is zero or negative        38,341    1.29%
passenger count missing        140,162    4.73%
trip longer than 24 hours           16    0.00%

kept after four cleaning rules: 2,869,585 of 2,964,624 (96.8%); rejected 95,039
daily partitions: 31, from 67,543 to 110,515 rows a day

• A monthly file is not a clean month. Eighteen trips have pickup times outside January, so a pipeline that trusts the file name puts rows in the wrong partitions.
• Some rows cannot be true. In 870 trips the drop-off is not after the pickup, and 60,371 trips covered no distance.
• Missing is different from wrong. Passenger count is absent in 4.73% of rows. That is a decision to make, keep or drop, and to write down.
• Four rules reject 95,039 rows, 3.2%. A write-up that states this, with the rules, is more convincing than one that claims the data was loaded.

This is twenty minutes of work, and it is what separates a project from a copied tutorial. Do it for whatever dataset you choose. A dataset from a field you care about is better still, because you will ask better questions of it.

Six projects

1. A warehouse from public data

Build. Load a year of trips into BigQuery, partitioned by day and clustered by pickup zone. Add a zone lookup table and one reporting table. Answer five written questions in SQL.

Proves. SQL, table design, and that you checked what a query scans before running it.

Cost. Nothing. The BigQuery sandbox needs no card (Google Cloud: BigQuery sandbox). Note that sandbox tables expire after 60 days and it has no DML, so export your SQL and results.

2. A batch pipeline with a quality gate

Build. Files land in Cloud Storage by date. A daily run loads raw, builds a cleaned table, runs checks and publishes a reporting table only if they pass.

Proves. Reruns that do not duplicate, and a bad file that is held back instead of published. Demonstrate both in the README. See building a data pipeline on GCP.

Cost. Within the Free Tier at this size.

3. An event-driven load

Build. A file arriving in a bucket triggers a function that validates it and starts the load. A dedicated service account does the work with the fewest roles it needs.

Proves. Event-driven design and least privilege. List the roles you granted and why. See Cloud Functions on GCP.

Cost. Within the Free Tier.

4. A streaming pipeline with late data

Build. A script publishes events to Pub/Sub, some of them deliberately late. A pipeline writes them to BigQuery and keeps a per-minute table. A dashboard reads the per-minute table.

Proves. That you understand event time, windows and what happens to late events. Show a number changing as late data arrives. See Pub/Sub to Dataflow to BigQuery.

Cost. Dataflow is billed. Run it for an afternoon on trial credit, record the results and delete it.

5. Transformations as code

Build. Move the SQL from project 2 into Dataform or dbt, in Git. Add tests for uniqueness and missing values, and run them automatically on every change.

Proves. Software practice: version control, tests, review. Many applicants have a pipeline. Few have one that tests itself. See Dataform vs dbt.

Cost. Nothing beyond the queries.

6. Operate it

Build. For one of the pipelines above: a budget with a forecast alert, an alert when a run fails and another when no run succeeds, and a one-page note on what to do when each fires. Add a table of what the project cost for a month, from the billing export.

Proves. That you can be trusted with something running. This is the rarest project in a junior portfolio and the one closest to the job. See cost controls and budgets and logging and monitoring on GCP.

Cost. Nothing.

Which to build

Three finished projects that connect are stronger than six separate ones. The pipeline of project 2, turned into code in project 5 and operated in project 6, is one story an interviewer can follow from start to finish.

Keeping it free

• Use public data. BigQuery hosts public datasets and pays for their storage. You pay only for queries, and the first 1 TB a month is free (Google Cloud: BigQuery public datasets).
• Save the trial credit for the billed services. New accounts get USD 300 for 90 days (Google Cloud: free features and trial offer). Spend it on streaming and orchestration, not on things the Free Tier already covers.
• Set a budget alert before creating anything.
• Delete what bills by the hour as soon as you have your results.

See the GCP free tier for the limits.

The write-up

A README in this order works:

1. The question the project answers, in one sentence.
2. A diagram with the services named.
3. The data profile: what was wrong with the source and what you did about it.
4. The numbers: rows in, rows rejected by rule, run time, monthly cost.
5. Failure cases: what you broke on purpose, and what happened.
6. How to run it, from a fresh clone.
7. What you would change with more time.

Keep secrets out of the repository. A service account key committed to a public repository is the one portfolio mistake with consequences beyond the portfolio.

Common mistakes

• Stopping at the dashboard. The pipeline behind it is the project.
• No numbers.
• Only the happy path.
• A repository that cannot be run.
• Describing the tools instead of the decisions.
• Leaving billed resources running and learning about budgets the hard way.

End-to-end pipeline projects are part of the Data Engineering course.

Sources

• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• Data Dictionary: Yellow Taxi Trip Records (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• BigQuery public datasets (Google Cloud Documentation) — Google Cloud
• Try BigQuery using the sandbox (Google Cloud Documentation) — Google Cloud
• Free Google Cloud features and trial offer (Google Cloud Documentation) — Google Cloud