BigQuery Service Account Setup

Overview

Cogny connects to your BigQuery data warehouse using a Google Cloud service account with read-only access. This guide walks through creating and configuring a service account with the minimum required permissions.

Time to complete: 10-15 minutes

Prerequisites:

• Google Cloud Project with BigQuery enabled
• Project Owner or Project IAM Admin role
• GA4 BigQuery export configured (for GA4 analysis)

Why Service Accounts?

Service accounts provide secure, auditable access to BigQuery data:

• Read-only access: Cogny never modifies your data
• Fine-grained permissions: Grant only necessary access
• Audit trail: All queries logged in Cloud Logging
• Key rotation: Easily rotate credentials if needed
• No user credentials: No personal OAuth tokens required

Step 1: Create Service Account

Via Google Cloud Console

1. Navigate to IAM & Admin > Service Accounts
2. Select your project
3. Click Create Service Account
4. Enter service account details:
   • Name: cogny-analytics (or your preferred name)
   • ID: cogny-analytics@your-project.iam.gserviceaccount.com
   • Description: Service account for Cogny growth analytics platform
5. Click Create and Continue

Via gcloud CLI

# Set your project ID
export PROJECT_ID="your-project-id"

# Create service account
gcloud iam service-accounts create cogny-analytics \
    --project=$PROJECT_ID \
    --description="Service account for Cogny growth analytics platform" \
    --display-name="Cogny Analytics"

# Verify creation
gcloud iam service-accounts list --project=$PROJECT_ID

Step 2: Grant IAM Roles

Required Roles

Grant these roles for read-only BigQuery access:

Via Google Cloud Console

1. In the service account list, find cogny-analytics
2. Click the three-dot menu > Manage permissions
3. Click Grant Access
4. Add the service account email as a principal
5. Assign roles:
   • BigQuery Data Viewer
   • BigQuery Job User
   • BigQuery Metadata Viewer
6. Click Save

Via gcloud CLI

# Set service account email
export SA_EMAIL="cogny-analytics@${PROJECT_ID}.iam.gserviceaccount.com"

# Grant BigQuery roles
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.dataViewer"

gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.jobUser"

gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.metadataViewer"

# Verify role bindings
gcloud projects get-iam-policy $PROJECT_ID \
    --flatten="bindings[].members" \
    --filter="bindings.members:serviceAccount:${SA_EMAIL}"

Step 3: Create and Download Key

Via Google Cloud Console

1. Navigate to your service account
2. Go to Keys tab
3. Click Add Key > Create new key
4. Select JSON format
5. Click Create
6. Save the JSON file securely (it contains credentials)

Via gcloud CLI

# Create and download key
gcloud iam service-accounts keys create cogny-credentials.json \
    --iam-account=${SA_EMAIL} \
    --project=$PROJECT_ID

# Verify key creation
ls -lh cogny-credentials.json

The JSON file contains:

{
  "type": "service_account",
  "project_id": "your-project-id",
  "private_key_id": "abc123...",
  "private_key": "-----BEGIN PRIVATE KEY-----\n...",
  "client_email": "cogny-analytics@your-project.iam.gserviceaccount.com",
  "client_id": "123456789",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/..."
}

Step 4: Configure Dataset Permissions (Optional)

For additional security, restrict access to specific datasets:

Via Google Cloud Console

1. Navigate to BigQuery
2. Select your dataset (e.g., analytics_123456789)
3. Click Sharing > Permissions
4. Click Add Principal
5. Enter service account email
6. Select roles:
   • BigQuery Data Viewer
   • BigQuery Metadata Viewer
7. Click Save

Via gcloud CLI

# Set dataset ID
export DATASET_ID="analytics_123456789"

# Grant dataset-level access
bq add-iam-policy-binding \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.dataViewer" \
    ${PROJECT_ID}:${DATASET_ID}

bq add-iam-policy-binding \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.metadataViewer" \
    ${PROJECT_ID}:${DATASET_ID}

# Verify dataset permissions
bq show --format=prettyjson ${PROJECT_ID}:${DATASET_ID}

Step 5: Connect to Cogny

Via Cogny Dashboard

1. Log in to app.cogny.com
2. Navigate to Settings > Data Warehouses
3. Click Connect BigQuery
4. Enter connection details:
   • Project ID: Your GCP project ID
   • Dataset ID: Your GA4 dataset (e.g., analytics_123456789)
   • Service Account Key: Upload the JSON file
5. Click Test Connection
6. If successful, click Save

Via API

curl -X POST https://api.cogny.com/v1/warehouses \
  -H "Authorization: Bearer sk_live_abc123xyz789" \
  -H "Content-Type: application/json" \
  -d '{
    "type": "bigquery",
    "name": "Production Analytics",
    "config": {
      "project_id": "your-project-id",
      "dataset_id": "analytics_123456789",
      "credentials": {
        "type": "service_account",
        "project_id": "your-project-id",
        "private_key_id": "abc123...",
        "private_key": "-----BEGIN PRIVATE KEY-----\n...",
        "client_email": "cogny-analytics@your-project.iam.gserviceaccount.com"
      }
    }
  }'

Connection Test

Cogny runs these validation checks:

1. Authentication: Verify service account credentials
2. Project Access: Confirm project exists and is accessible
3. Dataset Access: Verify dataset read permissions
4. Query Execution: Run sample query to test permissions

-- Test query executed by Cogny
SELECT
  COUNT(*) as row_count,
  COUNT(DISTINCT user_pseudo_id) as unique_users
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
LIMIT 1

Security Best Practices

1. Use Least Privilege

Grant only necessary permissions:

# ✅ Good: Project-level read-only access
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.dataViewer"

# ❌ Bad: Project editor access
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/editor"

2. Rotate Keys Regularly

Rotate service account keys every 90 days:

# Create new key
gcloud iam service-accounts keys create cogny-credentials-new.json \
    --iam-account=${SA_EMAIL}

# Update Cogny with new credentials
# Then delete old key

# List keys
gcloud iam service-accounts keys list \
    --iam-account=${SA_EMAIL}

# Delete old key
gcloud iam service-accounts keys delete KEY_ID \
    --iam-account=${SA_EMAIL}

3. Monitor Service Account Activity

Enable Cloud Audit Logs:

# View recent BigQuery queries from service account
gcloud logging read "protoPayload.authenticationInfo.principalEmail=${SA_EMAIL}" \
    --limit 50 \
    --format json

4. Store Credentials Securely

Never commit credentials to version control:

# Add to .gitignore
echo "cogny-credentials.json" >> .gitignore
echo "*-credentials.json" >> .gitignore

# Use environment variables or secret managers
export COGNY_CREDENTIALS_PATH="/secure/path/cogny-credentials.json"

5. Restrict Network Access (Optional)

For enterprise deployments, use VPC Service Controls:

# Create access policy
gcloud access-context-manager policies create \
    --title="Cogny BigQuery Access"

# Create service perimeter
gcloud access-context-manager perimeters create cogny-perimeter \
    --resources=projects/PROJECT_NUMBER \
    --restricted-services=bigquery.googleapis.com

Troubleshooting

Error: "Permission Denied"

Cause: Service account lacks required roles

Solution:

# Verify current roles
gcloud projects get-iam-policy $PROJECT_ID \
    --flatten="bindings[].members" \
    --filter="bindings.members:serviceAccount:${SA_EMAIL}"

# Re-grant required roles
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.dataViewer"

Error: "Dataset Not Found"

Cause: Service account cannot access dataset

Solution:

# Check dataset permissions
bq show --format=prettyjson ${PROJECT_ID}:${DATASET_ID}

# Grant dataset access
bq add-iam-policy-binding \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.dataViewer" \
    ${PROJECT_ID}:${DATASET_ID}

Error: "Invalid Credentials"

Cause: Service account key is invalid or expired

Solution:

# Create new key
gcloud iam service-accounts keys create cogny-credentials-new.json \
    --iam-account=${SA_EMAIL}

# Update credentials in Cogny

Error: "Query Execution Failed"

Cause: Service account lacks bigquery.jobUser role

Solution:

# Grant job execution permission
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.jobUser"

Advanced Configuration

Multi-Project Access

Grant access to multiple projects:

# Grant access to additional projects
for project in project-1 project-2 project-3; do
    gcloud projects add-iam-policy-binding $project \
        --member="serviceAccount:${SA_EMAIL}" \
        --role="roles/bigquery.dataViewer"

    gcloud projects add-iam-policy-binding $project \
        --member="serviceAccount:${SA_EMAIL}" \
        --role="roles/bigquery.jobUser"
done

Cross-Project Queries

Enable cross-project queries:

# Grant data viewer on source project
gcloud projects add-iam-policy-binding source-project \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.dataViewer"

# Grant job user on destination project
gcloud projects add-iam-policy-binding destination-project \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.jobUser"

Table-Level Access

Restrict to specific tables:

# Grant access to specific table
bq add-iam-policy-binding \
    --member="serviceAccount:${SA_EMAIL}" \
    --role="roles/bigquery.dataViewer" \
    ${PROJECT_ID}:${DATASET_ID}.events_*

Cost Optimization

Query Cost Monitoring

Track query costs for the service account:

# View query costs
bq ls --jobs \
    --project_id=$PROJECT_ID \
    --filter="configuration.query.user_email:${SA_EMAIL}"

Set Query Cost Limits

Configure BigQuery quota:

# Set daily query limit (TB)
gcloud alpha services quota update \
    --service=bigquery.googleapis.com \
    --consumer=projects/$PROJECT_ID \
    --metric=bigquery.googleapis.com/quota/query/usage \
    --value=10

Enable Query Cache

Cogny automatically uses BigQuery query cache to reduce costs:

-- Cached queries don't incur processing costs
SELECT *
FROM `project.dataset.table`
WHERE DATE(event_timestamp) = '2025-02-09'
-- Subsequent identical queries use cache

Terraform Configuration

Automate service account creation with Terraform:

# Service account
resource "google_service_account" "cogny" {
  account_id   = "cogny-analytics"
  display_name = "Cogny Analytics"
  description  = "Service account for Cogny growth analytics platform"
  project      = var.project_id
}

# IAM roles
resource "google_project_iam_member" "cogny_data_viewer" {
  project = var.project_id
  role    = "roles/bigquery.dataViewer"
  member  = "serviceAccount:${google_service_account.cogny.email}"
}

resource "google_project_iam_member" "cogny_job_user" {
  project = var.project_id
  role    = "roles/bigquery.jobUser"
  member  = "serviceAccount:${google_service_account.cogny.email}"
}

resource "google_project_iam_member" "cogny_metadata_viewer" {
  project = var.project_id
  role    = "roles/bigquery.metadataViewer"
  member  = "serviceAccount:${google_service_account.cogny.email}"
}

# Service account key
resource "google_service_account_key" "cogny" {
  service_account_id = google_service_account.cogny.name
}

# Output credentials (store securely)
output "service_account_key" {
  value     = base64decode(google_service_account_key.cogny.private_key)
  sensitive = true
}

Next Steps

• GA4 BigQuery Schema - Understand GA4 data structure
• Security Best Practices - Additional security guidance
• API Authentication - Connect via API

Support

• Documentation: docs.cogny.com/bigquery
• GCP IAM Guide: cloud.google.com/iam/docs/service-accounts
• Support: support@cogny.com