A Practical GCP Data Platform Architecture for Small Teams

In this article, let us look at a practical data platform architecture on GCP for a small team. The aim is not to create a platform with every possible feature. We want something that can ingest files and events, transform the data, and make it available in BigQuery without needing a separate platform team to operate it.

This approach is useful when two or three data engineers are supporting several pipelines. In that situation, managed services are usually worth more than having complete control over every component. For our use case, we will use Cloud Storage, Pub/Sub, Cloud Run, BigQuery, Cloud Scheduler, and Terraform.

What we are building

Let us assume that an application sends daily CSV files and also publishes a small number of real-time events. Analysts need cleaned tables in BigQuery, while engineers need a reliable way to replay failed data.

The flow will look like this:

Daily files -> GCS landing bucket -> Pub/Sub notification -> Cloud Run job
                                                        -> BigQuery raw tables
Application events -> Pub/Sub -> Cloud Run service -> BigQuery raw tables
BigQuery raw tables -> scheduled SQL -> curated tables -> BI tool

The landing bucket is important even if BigQuery is the final destination. It gives us an immutable copy of the source file, which makes reprocessing much easier. I would not delete a file immediately after loading it. Instead, I would apply a lifecycle rule to move or delete old files after the agreed retention period.

Why these services

For a small team, each new service creates another thing to monitor and understand. The below choices cover most basic data workloads without introducing Kubernetes, a permanent Spark cluster, or a custom scheduler.

Requirement	GCP service	Reason
File landing	Cloud Storage	Cheap, durable, and easy to replay
Event buffering	Pub/Sub	Separates producers from ingestion
Ingestion code	Cloud Run	Runs containers without managing servers
Warehouse	BigQuery	Minimal administration and good SQL support
Simple transforms	BigQuery scheduled queries	No additional orchestration service
Infrastructure	Terraform	Repeatable environments and reviewed changes

If the platform later has many dependent jobs, scheduled queries may become difficult to manage. At that point, Cloud Composer or another orchestrator can be added. I would not begin with Composer for five independent SQL transformations because its operational cost and complexity are not justified yet.

1. Create separate raw and curated datasets

We will keep ingested data separate from tables used by analysts. This makes permissions and cleanup easier. A simple Terraform configuration could look like this:

resource "google_bigquery_dataset" "raw" {
  dataset_id = "raw"
  location   = var.region

  default_table_expiration_ms = 7776000000
}

resource "google_bigquery_dataset" "curated" {
  dataset_id = "curated"
  location   = var.region
}

resource "google_storage_bucket" "landing" {
  name                        = "${var.project_id}-data-landing"
  location                    = var.region
  uniform_bucket_level_access = true

  lifecycle_rule {
    condition { age = 90 }
    action { type = "Delete" }
  }
}

The 90-day expiration on raw BigQuery tables is only an example. Before adding expiry rules, confirm whether the source can be replayed and whether there are audit requirements. Curated tables normally need a different retention policy.

I would also create separate service accounts for ingestion and transformation. The ingestion service account needs permission to read the landing bucket and write to the raw dataset. It should not have permission to update curated tables or administer the entire project.

2. Ingest files using an event

Cloud Storage can publish an object creation event through Eventarc or Pub/Sub. The handler should record the bucket, object name, generation, load status, and error message in a control table. The object generation is useful because a file name can be reused.

The processing logic can be kept small:

def process_file(event):
    bucket = event["bucket"]
    name = event["name"]
    generation = event["generation"]

    if already_loaded(bucket, name, generation):
        return

    table = table_for_object(name)
    load_to_bigquery(
        uri=f"gs://{bucket}/{name}",
        table=table,
        write_disposition="WRITE_APPEND"
    )
    mark_loaded(bucket, name, generation)

The already_loaded check is not optional in practice. Pub/Sub and Eventarc provide at-least-once delivery, so the same event can arrive more than once. Without an idempotency check, retrying a successful load could duplicate all rows.

For CSV files, define the schema instead of relying on schema autodetection. Autodetection is convenient for a demo, but a value such as 00123 can unexpectedly become an integer in one file and a string in another. Store schemas with the ingestion code and review schema changes in Git.

3. Handle application events separately

Small JSON events can arrive through Pub/Sub and be processed by a Cloud Run service. I prefer to write the original payload to a raw table along with metadata before extracting every field. For example:

CREATE TABLE raw.application_events (
  event_id STRING,
  event_time TIMESTAMP,
  received_at TIMESTAMP,
  event_type STRING,
  payload JSON
)
PARTITION BY DATE(event_time)
CLUSTER BY event_type;

The producer should send a stable event_id. The consumer can stage messages and use a MERGE statement to avoid inserting an event twice:

MERGE raw.application_events T
USING raw.application_events_stage S
ON T.event_id = S.event_id
WHEN NOT MATCHED THEN
  INSERT (event_id, event_time, received_at, event_type, payload)
  VALUES (S.event_id, S.event_time, CURRENT_TIMESTAMP(), S.event_type, S.payload);

For low volume, inserting small batches is fine. If traffic grows, avoid sending one BigQuery request per event. Batch the messages or use the BigQuery Storage Write API. Also configure a dead-letter topic so a malformed message does not retry forever.

4. Transform data with scheduled SQL

For independent transformations, BigQuery scheduled queries are enough. A curated orders table could be refreshed incrementally as follows:

MERGE curated.orders T
USING (
  SELECT
    JSON_VALUE(payload, '$.order_id') AS order_id,
    TIMESTAMP(JSON_VALUE(payload, '$.updated_at')) AS updated_at,
    CAST(JSON_VALUE(payload, '$.amount') AS NUMERIC) AS amount
  FROM raw.application_events
  WHERE event_type = 'order_updated'
    AND event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY)
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY JSON_VALUE(payload, '$.order_id')
    ORDER BY event_time DESC
  ) = 1
) S
ON T.order_id = S.order_id
WHEN MATCHED AND S.updated_at > T.updated_at THEN
  UPDATE SET updated_at = S.updated_at, amount = S.amount
WHEN NOT MATCHED THEN
  INSERT (order_id, updated_at, amount)
  VALUES (S.order_id, S.updated_at, S.amount);

Notice that the query reads two days of data even if it runs every hour. This overlap handles late events. The MERGE keeps the operation repeatable. The exact lookback should be based on actual source delays rather than a random fixed value.

5. Add the minimum useful monitoring

A pipeline is not finished just because data reached BigQuery once. At minimum, I would add alerts for Cloud Run errors, dead-letter topic messages, failed BigQuery jobs, and missing daily files. A control table can make this visible with a basic query:

SELECT source_name, expected_date
FROM metadata.expected_files
WHERE expected_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
EXCEPT DISTINCT
SELECT source_name, source_date
FROM metadata.file_loads
WHERE status = 'SUCCESS';

Cloud Scheduler can run this check and a small Cloud Run job can send the result to the team’s normal alerting channel. Keep alert ownership clear. An alert that nobody is expected to respond to is only extra noise.

Production considerations

For a demo, all resources can live in one project. In production, I would use separate projects for development and production, separate service accounts, remote Terraform state, and CI/CD with workload identity federation instead of service account keys. BigQuery datasets and GCS buckets should use matching regions to avoid unnecessary transfer problems.

Cost controls also matter. Set BigQuery query byte limits where possible, partition large tables, require partition filters, and create budget alerts. Cloud Run maximum instances should be capped so a sudden Pub/Sub backlog does not create an unexpected number of concurrent BigQuery jobs.

This architecture will not fit every workload. Spark is still useful for large or complex distributed processing, and a proper orchestrator becomes helpful when jobs have many dependencies, backfills, and SLAs. But for a small team starting a GCP data platform, GCS, Pub/Sub, Cloud Run, and BigQuery provide a useful base. Start with the simple flow, make retries and replay work properly, and add services only when the workload gives you a clear reason.