Cloud Infrastructure for Data Pipelines

Service Selection Guide

Compute (query engines)

The biggest practical difference: BigQuery and Athena are serverless (no cluster to manage); Snowflake and Redshift require you to think about concurrency and warehouse sizing.

Storage

All three are object stores — they look like key-value stores, not filesystems. The "folder" structure in the key name is just a naming convention.

Storage Layout and Partitioning

S3 / GCS bucket layout

s3://my-data-lake/
  raw/
    source=salesforce/
      year=2024/month=01/day=15/
        events_20240115_001.parquet
  processed/
    domain=churn/
      year=2024/month=01/
        churn_features_20240101.parquet
  archive/
    ...

Separate raw and processed data in the key hierarchy so you can apply different retention policies and IAM permissions to each layer.

Partition strategy

Partition on the columns most commonly used in WHERE clauses. For time-series data, year/month/day is standard. Avoid over-partitioning — having millions of tiny files is worse than having a few large ones.

# PySpark — write with partitioning
df.write \
    .partitionBy("year", "month", "day") \
    .mode("overwrite") \
    .parquet("s3://my-bucket/processed/events/")

Partition column types matter: BigQuery and Athena push partition filters down efficiently. Use date/timestamp columns for time partitioning, not string representations — 2024-01-15 as a DATE, not "20240115" as a STRING.

File size and format

Target 128MB–1GB per file after compression. Files smaller than ~10MB create metadata overhead that slows queries on all columnar engines.

BigQuery

Cost control

BigQuery on-demand charges per TB scanned. The biggest lever is how much data your queries touch.

-- Always filter on partition column to prune scans
SELECT user_id, event_type
FROM `project.dataset.events`
WHERE DATE(created_at) BETWEEN '2024-01-01' AND '2024-01-31'  -- partition pruning
  AND event_type = 'purchase';

-- Check bytes scanned before running expensive queries
-- In BigQuery UI: the validator shows estimated bytes in the top right

Cluster your tables on the columns most used in WHERE and JOIN after the partition column — clustering reduces bytes scanned within a partition.

CREATE TABLE `project.dataset.events`
PARTITION BY DATE(created_at)
CLUSTER BY user_id, event_type
AS SELECT ...;

Slot reservations vs. on-demand

On-demand is cheaper for infrequent/bursty workloads. Reservations (flat-rate pricing) make sense when you're spending > ~$2,000/month on on-demand, or when you need predictable query concurrency for dashboards.

BigQuery Storage API

Use the Storage Read API for fast data export to Pandas/Spark — much faster than exporting to CSV first.

from google.cloud import bigquery_storage

client = bigquery_storage.BigQueryReadClient()
# Reads directly into Arrow/Pandas without intermediate storage

Redshift

RA3 node types (recommended)

RA3 separates compute from storage — you pay for storage on S3 and scale compute independently. This is almost always the right choice for new Redshift clusters.

Distribution and sort keys

-- Distribute fact tables by the join key to minimize data movement
CREATE TABLE orders (
    order_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(10,2)
)
DISTSTYLE KEY DISTKEY (user_id)  -- co-locate with users table
SORTKEY (created_at);            -- skip scans on date range queries

Mismatched distribution keys between joined tables cause expensive data redistribution across nodes. Check SVL_QUERY_SUMMARY for DS_DIST_BOTH steps — those are the expensive ones.

Redshift Spectrum

Query Parquet/ORC files directly on S3 without loading into Redshift:

CREATE EXTERNAL SCHEMA raw_lake
FROM DATA CATALOG DATABASE 'my_glue_db'
IAM_ROLE 'arn:aws:iam::123456:role/redshift-spectrum-role'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

SELECT * FROM raw_lake.events WHERE event_date = '2024-01-15';

Use Spectrum to query raw/archive data without paying for Redshift storage on cold data.

IAM Patterns for Data Pipelines

Principle of least privilege

Each pipeline component should have only the permissions it needs, scoped to the specific resources it touches.

// S3 policy for a pipeline that reads raw and writes processed
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:ListBucket"],
      "Resource": [
        "arn:aws:s3:::my-bucket/raw/*",
        "arn:aws:s3:::my-bucket"
      ]
    },
    {
      "Effect": "Allow",
      "Action": ["s3:PutObject", "s3:DeleteObject"],
      "Resource": "arn:aws:s3:::my-bucket/processed/*"
    }
  ]
}

Cross-account access

When pipelines need to read from another team's account, use IAM roles + resource-based policies rather than sharing credentials.

// Bucket policy in Account A — allows Role in Account B to read
{
  "Principal": {"AWS": "arn:aws:iam::ACCOUNT_B:role/pipeline-role"},
  "Action": ["s3:GetObject"],
  "Resource": "arn:aws:s3:::account-a-bucket/shared/*"
}

GCP service accounts

# Create a service account per pipeline
gcloud iam service-accounts create churn-pipeline-sa

# Grant BigQuery read on the source dataset
gcloud projects add-iam-policy-binding my-project \
  --member="serviceAccount:[email protected]" \
  --role="roles/bigquery.dataViewer"

# Grant GCS write on the output bucket only
gsutil iam ch serviceAccount:[email protected]:objectCreator gs://my-output-bucket/

Cost Optimization

Infrastructure Checklist

Before going to production:

• [ ] Storage layout uses partition columns that match query patterns
• [ ] File sizes are 128MB–1GB after compression
• [ ] IAM roles follow least-privilege (no s3:* or bigquery.admin for pipelines)
• [ ] Credentials are in Secrets Manager / Secret Manager, not environment variables or code
• [ ] BigQuery tables are partitioned and clustered on the right columns
• [ ] Redshift DISTKEY matches the primary join key for fact tables
• [ ] Cost alerts configured on the cloud account
• [ ] Data lifecycle policy set — raw data retention, archive after N days