Data Engineering Career Roadmap

Diagram and justify a GCP architecture connecting Cloud Storage, BigQuery, dbt, Airflow, FastAPI on Cloud Run, Redis, and optional Dataflow, including its ETL-versus-ELT and batch-versus-streaming boundaries.

Configure the capstone GCP project, required APIs, application credentials, service accounts, and initial least-privilege IAM roles.

Define the grain of each fact and dimension table in the connectivity-intelligence warehouse.

Design a star schema for connectivity events, customers, devices, locations, and service plans.

Choose between normalized, denormalized, and nested BigQuery structures for a stated access pattern.

Specify Type 2 slowly changing dimension logic, including effective dates, current-record flags, and late-arriving changes.

Select partition keys and clustering columns for the connectivity tables based on grain, volume, and query patterns.

Validate the warehouse model against representative analytical questions and operational API access patterns.

Load newline-delimited JSON and Parquet datasets from Cloud Storage into correctly typed BigQuery tables.

Query nested and repeated BigQuery fields using STRUCT, ARRAY, and UNNEST operations.

Interpret a BigQuery execution plan to locate expensive scans, shuffles, and joins.

Reduce the bytes scanned and execution time of a representative analytical query and apply an appropriate query-cost guardrail.

Build a resumable Python extractor that paginates through a simulated connectivity-data API and records a safe extraction checkpoint.

Persist immutable raw extracts in Cloud Storage using deterministic, auditable object paths.

Implement an idempotent staged BigQuery MERGE that is safe to retry and deduplicates repeated source deliveries.

Detect and quarantine records that violate the source schema, preserving machine-readable rejection reasons.

Handle an additive source-schema change without corrupting or silently truncating existing warehouse data.

Initialize a dbt project connected to BigQuery with isolated development and production targets.

Declare raw BigQuery tables as dbt sources and configure source-freshness expectations.

Build staging models that standardize names, types, timestamps, and null handling.

Build intermediate dbt models that encode reusable connectivity business logic.

Build fact and dimension marts using ref-based dependencies.

Generate dbt documentation and inspect the resulting model lineage graph.

Configure an incremental dbt model with a valid unique key and update strategy.

Prove that the incremental model produces the same result as a full refresh on a controlled test dataset.

Create a dbt snapshot that implements the planned history-tracking strategy for a mutable source entity.

Write a singular dbt test for a connectivity-specific referential or business-rule constraint.

Add dbt unit tests for transformation logic that handles duplicate, null, and boundary-case records.

Use Jinja and a dbt macro to remove repeated transformation logic without obscuring model behavior.

Author an Airflow DAG that orders ingestion, loading, dbt transformation, and validation tasks.

Configure retries, timeouts, and trigger rules for a recoverable data workflow.

Execute a parameterized historical backfill without duplicating warehouse records.

Implement automated SQL checks for freshness, completeness, uniqueness, and validity in a critical connectivity dataset.

Route records that fail post-load business validation to a quarantine table with machine-readable failure reasons.

Configure workflow observability that records run metadata and alerts on failed or stale pipeline runs.

Specify a versioned API contract for connectivity summaries and time-series insights.

Implement and integration-test a FastAPI endpoint that executes parameterized BigQuery queries.

Add cursor-based pagination and bounded filters to protect a large-result endpoint.

Implement Redis caching with an explicit TTL and invalidation policy, then measure its effect on latency and BigQuery cost.

Deploy the FastAPI service to Cloud Run with authenticated invocation, a least-privilege service identity, and explicit concurrency or autoscaling settings.

Provision the capstone's BigQuery datasets, Cloud Storage buckets, service accounts, and Cloud Run dependencies with Terraform.

Parameterize reusable Terraform configuration to create isolated development and production data resources.

Apply an appropriate BigQuery row-level or column-level access control to sensitive connectivity data.

Store and consume application and pipeline secrets without committing credentials to source control.

Build a pull-request workflow that runs SQL linting, dbt checks, and Python tests.

Build a delivery workflow that deploys versioned API and data-workflow artifacts to GCP.

Explain how PCollections, transforms, runners, and pipeline options interact in the Apache Beam execution model.

Implement a Beam batch pipeline that cleans and aggregates connectivity events.

Test a Beam transform using deterministic input and expected output.

Implement event-time windows with an explicit watermark, allowed-lateness, and trigger strategy for simulated streaming events.

Run a Beam pipeline on Dataflow and diagnose its behavior using worker, throughput, lag, and error metrics.

Run the complete capstone from raw connectivity-event ingestion to API-served warehouse insight.

Demonstrate that rerunning a failed date partition does not duplicate or omit warehouse data.

Simulate an upstream schema change and verify downstream compatibility across ingestion, dbt, and FastAPI layers.

Verify least-privilege access boundaries for raw, transformed, sensitive, and API-visible data.

Measure selected pipeline and API service-level indicators against the capstone's latency and reliability targets.

Write and validate a runbook for diagnosing and recovering from an ingestion or transformation failure.

Publish a deployment-ready GitHub repository and README explaining the business problem, architecture, setup, tests, trade-offs, costs, and measured results.

Record a concise, structured screencast demonstrating the working capstone and its failure-recovery behavior.

Rewrite résumé bullets to quantify relevant backend experience and newly demonstrated data-engineering impact.

Solve a timed BigQuery exercise using window functions and QUALIFY to deduplicate event data.

Solve a timed SQL exercise that derives sessions and calculates a funnel metric from event data.

Present a system-design solution for a scalable batch-and-streaming connectivity-data platform and defend its reliability, cost, and modelling trade-offs.

Prepare concise STAR stories covering reliability, scaling, technical leadership, and production incidents.

Build a targeted application tracker that maps each job requirement to portfolio or résumé evidence and records tailoring, outreach, status, and follow-up.