7. Infrastructure (Final Presentation)

Final Presentation rubric (40 points) — IaC (5) + Data Lake Ingestion (15) + DWH Transformation (15) + Repository (5). This page maps each requirement to the code and infrastructure that satisfies it.

All cloud resources are provisioned by Terraform under terraform/. Snippets below are excerpts; see the full files for the unedited version.

---

1. Infrastructure as Code (5 points)

A Data Lake on Google Cloud (GCS bucket) + a Data Warehouse dataset on BigQuery, reproducible via Terraform, with a variables.tf and no hardcoded secrets.

Data lake — GCS bucket (terraform/main.tf):

resource "google_storage_bucket" "data_lake" {
  name          = "${var.project_id}-data-lake"
  location      = var.location
  force_destroy = true

  uniform_bucket_level_access = true
  storage_class               = "STANDARD"

  versioning { enabled = true }

  lifecycle_rule {
    condition { age = 30 }
    action    { type = "AbortIncompleteMultipartUpload" }
  }
}

Data warehouse — BigQuery datasets:

resource "google_bigquery_dataset" "rush" {
  dataset_id = var.bq_dataset_name
  location   = var.location
  delete_contents_on_destroy = true
}

resource "google_bigquery_dataset" "traffic_raw" {
  dataset_id = "traffic_raw"
  location   = var.location
  delete_contents_on_destroy = true
}

resource "google_bigquery_dataset" "weather_raw" {
  dataset_id = "weather_raw"
  location   = var.location
  delete_contents_on_destroy = true
}

Variables (terraform/variables.tf):

variable "project_id"      { type = string }                             # required, no default
variable "region"          { type = string  default = "europe-west6" }
variable "location"        { type = string  default = "europe-west6" }
variable "bq_dataset_name" { type = string  default = "rush" }
variable "image_tag"       { type = string  default = "latest" }
variable "ui_public"       { type = bool    default = true }

Secrets discipline:

• terraform.tfvars is .gitignored (*.tfvars in .gitignore)
• gcp_config.json and keys/*.json are .gitignored
• All cloud auth in containers uses Application Default Credentials — no JSON key is baked into any image

Reproduce:

cd terraform
cat > terraform.tfvars <<EOF
project_id = "your-gcp-project-id"
EOF
terraform init
terraform apply

---

2. Data Lake Ingestion Pipeline (15 points)

Implement a pipeline that ingests data from the source into your cloud data lake. Orchestrated, schedulable, runnable on a regular basis.

The ingestion scripts in pipelines/backfill/ and pipelines/ingestion/ use dlt with a filesystem staging destination pointed at the GCS bucket above. Raw rows land as parquet in gs://<project_id>-data-lake/<dataset>/ before BigQuery loads them. The switch is in config.py:

def make_staging():
    if get_target() != "bigquery":
        return None
    bucket = os.environ.get("GCP_BUCKET_NAME")
    if not bucket:
        return None
    return dlt.destinations.filesystem(bucket_url=f"gs://{bucket}")

# in each pipeline:
pipeline = dlt.pipeline(
    pipeline_name="traffic_backfill",
    destination=make_destination(),   # bigquery
    staging=make_staging(),           # gs://...-data-lake
    dataset_name="traffic_raw",
)

Orchestration — Cloud Run Job + Cloud Scheduler (terraform/cloud_run.tf):

resource "google_cloud_run_v2_job" "ingest" {
  name     = "rush-ingest"
  location = var.region
  template {
    template {
      service_account = google_service_account.runner.email
      timeout         = "3600s"
      containers {
        image = "${local.registry}/ingest:${var.image_tag}"
        env { name = "GCP_PROJECT_ID"   value = var.project_id }
        env { name = "GCP_BUCKET_NAME"  value = google_storage_bucket.data_lake.name }
      }
    }
  }
}

resource "google_cloud_scheduler_job" "ingest" {
  name      = "rush-ingest-daily"
  schedule  = "0 2 * * *"
  time_zone = "Etc/UTC"
  http_target {
    http_method = "POST"
    uri = "https://${var.region}-run.googleapis.com/apis/run.googleapis.com/v1/namespaces/${var.project_id}/jobs/${google_cloud_run_v2_job.ingest.name}:run"
    oauth_token { service_account_email = google_service_account.scheduler.email }
  }
}

The runner service account holds storage.objectAdmin on the bucket and bigquery.{dataEditor,jobUser} on the project:

resource "google_storage_bucket_iam_member" "runner_bucket" {
  bucket = google_storage_bucket.data_lake.name
  role   = "roles/storage.objectAdmin"
  member = "serviceAccount:${google_service_account.runner.email}"
}

Locally the same Python runs in Airflow (rush_backfill, rush_daily) — see Orchestration.

---

3. Data Warehouse Transformation Pipeline (15 points)

Reads data from the data lake, transforms it, loads it into BigQuery. The resulting table must be partitioned and clustered in a way that makes sense for the upstream queries (with explanation).

The dbt project in pipelines/transformation/dbt/ reads the BigQuery raw datasets that dlt populated from the lake, then produces two analytics-ready marts. Run by the rush-dbt Cloud Run Job on the 0 3 * * * schedule (one hour after ingest).

resource "google_cloud_run_v2_job" "dbt" {
  name     = "rush-dbt"
  location = var.region
  template {
    template {
      service_account = google_service_account.runner.email
      containers {
        image = "${local.registry}/dbt:${var.image_tag}"
        env { name = "GCP_PROJECT_ID" value = var.project_id }
      }
    }
  }
}

resource "google_cloud_scheduler_job" "dbt" {
  name     = "rush-dbt-daily"
  schedule = "0 3 * * *"
  ...
}

Partitioning + clustering

Both marts are lookup tables that the recommender hits with the same keys every time. The configs match that access shape:

mart_traffic_baseline — partitioned on month_of_year, clustered on (zsid, day_of_week, hour_of_day):

{{ config(
    materialized='table',
    partition_by={
      'field': 'month_of_year',
      'data_type': 'int64',
      'range': {'start': 1, 'end': 13, 'interval': 1}
    },
    cluster_by=['zsid', 'day_of_week', 'hour_of_day']
) }}

Recommender query (one per counter on the route):

select avg_vehicle_count
from mart_traffic_baseline
where zsid = @zsid
  and month_of_year = @month
  and day_of_week = @dow
  and hour_of_day = @hour;

Partition pruning drops 11/12 of the table; clustering narrows the surviving partition to the specific counter + time-of-day block. Typical lookup scans tens of KB instead of the full 423k rows.

mart_weather_effect — partitioned on day_of_week, clustered on (zsid, hour_of_day, precip_bucket):

{{ config(
    materialized='table',
    partition_by={
      'field': 'day_of_week',
      'data_type': 'int64',
      'range': {'start': 1, 'end': 8, 'interval': 1}
    },
    cluster_by=['zsid', 'hour_of_day', 'precip_bucket']
) }}

Same access shape (zsid, day_of_week, hour_of_day, precip_bucket): partition pruning isolates one weekday, cluster pruning narrows to the counter, hour, and bucket.

Why integer-range partitioning?

The marts have no timestamp; they're keyed by season-aware integer profile (month, dow, hour). BigQuery's range integer partitioning is the right fit — date_trunc partitioning would force us to fabricate a date column. Postgres ignores the config silently, so the same SQL runs on both engines.

---

4. Repository Requirements (5 points)

All components available in Git, documented setup steps, SQL queries, etc.

Asset	Location
Terraform	terraform/ + terraform/README.md
Ingestion pipelines	pipelines/backfill/ + pipelines/ingestion/, each with a README
dbt project	pipelines/transformation/dbt/ — top-level README plus one in models/staging/, models/marts/, macros/
Airflow DAGs	dags/rush_pipeline.py
Cloud Run entrypoints	scripts/run-ingest.sh, scripts/run-dbt.sh
One-command bootstrap	install.sh, setup.sh
This peer-review manual	docs/ → published to javihslu.github.io/rush