SlateoAPI Reference

BigQuery Setup

Connect Slateo to your Google BigQuery data warehouse using a service account. This guide walks you through read-only setup for analytics queries and optional write access for Slateo user-uploaded tables.

Overview

BigQuery supports service account authentication, allowing Slateo to securely access your datasets without exposing your data warehouse to the public internet. Service accounts use JSON key files for authentication and can be granted granular permissions at the project, dataset, or table level.

Best practice: Grant the minimum permissions necessary. Start with dataset-level read access and expand only if needed.

Prerequisites

Before setting up the connection, ensure you have:

  • An active Google Cloud project with BigQuery enabled
  • Permissions to create service accounts (requires iam.serviceAccounts.create permission)
  • Permissions to grant BigQuery dataset access (requires bigquery.datasets.update permission)
  • At least one dataset with tables you want Slateo to access

Setup steps

Step 1: Create a service account

Create a dedicated service account for Slateo with read-only access to your BigQuery datasets.

  1. Navigate to the Google Cloud Console
  2. Select your project from the project dropdown
  3. Go to IAM & Admin → Service Accounts
  4. Click Create Service Account
  5. Enter the service account details:
    • Name: slateo-readonly
    • Description: Read-only access for Slateo data analytics
  6. Click Create and Continue
  7. Skip the optional "Grant this service account access to project" step (we'll grant dataset-level permissions instead)
  8. Click Done

Google Cloud service account documentation →

Project-level vs dataset-level permissions: For better security, grant permissions at the dataset level rather than the project level. This ensures Slateo can only access the specific datasets you authorize.

Step 2: Grant dataset access

Grant the service account read-only access to your BigQuery datasets.

  1. In the Google Cloud Console, navigate to BigQuery
  2. In the Explorer panel, locate the dataset you want to share
  3. Click the three-dot menu next to the dataset name and select Share
  4. Click Add Principal
  5. Enter the service account email (format: slateo-readonly@your-project-id.iam.gserviceaccount.com)
  6. Select the following role:
    • BigQuery Data Viewer (roles/bigquery.dataViewer) - Read access to tables, views, and metadata
  7. Click Save

Repeat this process for each dataset you want Slateo to access.

Additionally, grant the service account permission to run queries at the project level:

  1. Go to IAM & Admin → IAM
  2. Click Grant Access
  3. Enter the service account email
  4. Select BigQuery Job User (roles/bigquery.jobUser)
  5. Click Save

BigQuery access control documentation →

Multiple datasets: If you have multiple datasets, you can grant access to each one individually using the same service account. Slateo will automatically discover all accessible datasets.

Alternative: Using the bq command-line tool

If you prefer using the command line, you can grant dataset access with the bq tool:

# Grant BigQuery Data Viewer role
bq add-iam-policy-binding \
  --member='serviceAccount:slateo-readonly@your-project-id.iam.gserviceaccount.com' \
  --role='roles/bigquery.dataViewer' \
  your-project-id:your_dataset

# Grant BigQuery Job User role (project-level, required to run queries)
gcloud projects add-iam-policy-binding your-project-id \
  --member='serviceAccount:slateo-readonly@your-project-id.iam.gserviceaccount.com' \
  --role='roles/bigquery.jobUser'

Replace:

  • your-project-id: Your Google Cloud project ID
  • your_dataset: Your BigQuery dataset name

Step 2b (Optional): Enable user table uploads (usertables write access)

If you want Slateo users to upload CSVs/files and create tables from them, grant write access to a dedicated dataset named usertables.

  1. In BigQuery, create a dataset named usertables in the same project used by your Slateo connection
  2. Open dataset Share settings and add the Slateo service account
  3. Grant dataset role:
    • BigQuery Data Editor (roles/bigquery.dataEditor) - create/update/delete tables in usertables

Alternative: Using CLI

# Create usertables dataset (adjust location as needed)
bq mk --dataset --location=US your-project-id:usertables

# Allow Slateo to write tables into usertables
bq add-iam-policy-binding \
  --member='serviceAccount:slateo-readonly@your-project-id.iam.gserviceaccount.com' \
  --role='roles/bigquery.dataEditor' \
  your-project-id:usertables

Recommended isolation: Keep write access scoped only to usertables. Continue granting read access to your source datasets with viewer roles from Step 2.

Dataset naming: Use usertables as the dataset name unless your Slateo team has configured a different schema name for your workspace.

Step 3: Create and download a service account key

Generate a JSON key file for the service account to use for authentication.

  1. In the Google Cloud Console, go to IAM & Admin → Service Accounts
  2. Find the slateo-readonly service account and click on it
  3. Go to the Keys tab
  4. Click Add Key → Create new key
  5. Select JSON as the key type
  6. Click Create

The JSON key file will be automatically downloaded to your computer. Store it securely—this file contains credentials that grant access to your BigQuery data.

Service account key management documentation →

Security warning: Treat the JSON key file like a password. Never commit it to version control, share it publicly, or store it in unsecured locations. If the key is compromised, delete it immediately in the Google Cloud Console and create a new one.

Step 4: Configure the connection in Slateo

Add your BigQuery connection in the Slateo admin panel.

  1. Log in to your Slateo workspace
  2. Navigate to Admin → Data Sources → Add Data Source
  3. Select BigQuery
  4. Enter the connection details:
    • Name: A friendly name for this connection (e.g., Production Analytics)
    • Project ID: Your Google Cloud project ID (found in the JSON key file under project_id)
    • Service Account Key: Paste the entire contents of the JSON key file you downloaded in Step 3
  5. Click Test Connection to verify connectivity
  6. Click Save to complete the setup

Next steps

After you complete the setup:

  1. Schema discovery: Slateo automatically scans your accessible datasets to discover available tables and views (typically within 5 minutes)
  2. Verify access: Check that all expected datasets and tables appear in your Slateo workspace
  3. Start querying: Navigate to your workspace to start analyzing your BigQuery data

Security considerations

Service account key management

  • Store keys securely: Use a password manager or secure vault to store the JSON key file
  • Rotate keys periodically: Create new keys and delete old ones every 90 days
  • Monitor key usage: Enable audit logging to track service account activity
  • Limit key distribution: Only share keys with authorized personnel

To rotate a key:

  1. Create a new key following Step 3
  2. Update the key in Slateo admin panel
  3. Test the connection
  4. Delete the old key in the Google Cloud Console

Access control

  • Principle of least privilege: Only grant access to datasets that Slateo needs
  • Use dataset-level permissions: Avoid granting project-level permissions when possible
  • Review permissions regularly: Audit service account permissions quarterly
  • Enable audit logging: Monitor BigQuery access patterns

To enable audit logging:

  1. Go to IAM & Admin → Audit Logs in the Google Cloud Console
  2. Find BigQuery API
  3. Enable Admin Read, Data Read, and Data Write logs
  4. Click Save

BigQuery audit logging documentation →

Network security

  • Private Google Access: If your BigQuery project is in a VPC, enable Private Google Access to keep traffic within Google's network
  • VPC Service Controls: For additional security, use VPC Service Controls to create a security perimeter around your BigQuery resources

VPC Service Controls documentation →

Troubleshooting

Authentication errors

If Slateo reports authentication failures:

  1. Verify the JSON key file is valid and complete:
    • The file should be valid JSON
    • It should contain fields like type, project_id, private_key_id, private_key, and client_email
  2. Check that the service account still exists: 
    gcloud iam service-accounts list --project=your-project-id
  3. Verify the service account key hasn't been deleted: 
    gcloud iam service-accounts keys list \
  --iam-account=slateo-readonly@your-project-id.iam.gserviceaccount.com

Permission errors

If Slateo can connect but cannot query certain datasets or tables:

  1. Verify the service account has the correct roles on the dataset: 
    bq show --format=prettyjson your-project-id:your_dataset | grep -A 20 access
  2. Ensure both required roles are granted:
    • roles/bigquery.dataViewer - for reading table data and metadata
    • roles/bigquery.jobUser - for running queries (project-level)
  3. If user table uploads fail, verify write permissions on usertables:
    • roles/bigquery.dataEditor on your-project-id:usertables
    • roles/bigquery.jobUser on your-project-id
  4. Check for organization policies that might restrict access: 
    gcloud resource-manager org-policies list --project=your-project-id

Dataset not appearing in Slateo

If a dataset doesn't appear in Slateo after granting access:

  1. Verify the dataset exists and contains tables: 
    bq ls your-project-id:your_dataset
  2. Check that the service account has access: 
    bq show --format=prettyjson your-project-id:your_dataset
  3. Trigger a manual schema refresh in Slateo:
    • Go to Admin → Data Sources
    • Find your BigQuery connection
    • Click Refresh Schema

Query timeout errors

If queries are timing out:

  1. Check your BigQuery quotas and limits:
    • Go to IAM & Admin → Quotas in the Google Cloud Console
    • Search for "BigQuery API"
    • Verify you haven't exceeded query limits
  2. Review query complexity and optimize if needed
  3. Consider increasing BigQuery slot reservations for better performance

BigQuery quotas documentation →

Advanced configuration

Using a customer-managed encryption key (CMEK)

If your BigQuery datasets use customer-managed encryption keys, grant the service account access to the key:

gcloud kms keys add-iam-policy-binding your-key \
  --location=your-location \
  --keyring=your-keyring \
  --member='serviceAccount:slateo-readonly@your-project-id.iam.gserviceaccount.com' \
  --role='roles/cloudkms.cryptoKeyDecrypter'

CMEK documentation →

Cross-project access

To grant access to datasets in a different Google Cloud project:

  1. Create the service account in your primary project
  2. Grant dataset access in the secondary project using the service account email
  3. Ensure the BigQuery API is enabled in both projects

Workload Identity Federation (advanced)

For enhanced security without service account keys, consider using Workload Identity Federation. Contact your Slateo account manager for setup guidance.

Workload Identity Federation documentation →

Cost control

AI-assisted data analysis can execute queries faster than manual workflows, which may increase BigQuery utilization. If you need to control costs, we recommend using project isolation to set hard limits on Slateo's BigQuery usage.

Understanding cost dynamics

  • Increased query velocity: AI agents can execute queries in sequence faster than humans writing them manually
  • Compute offloading: Slateo's in-memory engine handles some analysis that would otherwise run in BigQuery, which can reduce BigQuery costs for certain workloads (though Slateo charges for this compute usage)
  • Service account control: Since Slateo doesn't manage your BigQuery infrastructure, cost controls should be implemented on the service account and project level through Google Cloud Console

Recommended approach: Project isolation

The best way to control costs is to isolate the Slateo service account in a dedicated GCP project with its own quota. This gives you a hard cap on Slateo's BigQuery usage without affecting your other workloads.

Setup steps:

  1. Create a separate GCP project for the Slateo service account (e.g., slateo-bigquery-access)
  2. Create the service account in the new project (follow the setup steps in this guide)
  3. Grant the service account access to datasets in your main project(s)
  4. Set a project-level quota on the dedicated project:
    • Go to GCP Console → IAM & Admin → Quotas & System Limits
    • Filter for BigQuery API
    • Find Query usage per day (project-level quota)
    • Click Edit → set your desired TiB limit → Submit
  5. Configure Slateo to use the dedicated project as the billing/query project (contact your Slateo account manager for setup assistance)

How cross-project access works:

  • Project A (main project): Your BigQuery datasets live here
  • Project B (Slateo project): Service account lives here, query jobs run here, quota applies here
  • The service account in Project B can query Project A's tables via: 
    SELECT * FROM `project-a.dataset.table`
  • All bytes scanned are counted against Project B's quota

Required permissions:

  • Service account needs bigquery.jobs.create in Project B (where queries run)
  • Service account needs bigquery.dataViewer in Project A (where data lives)

Why this works: Query costs are charged to whichever project the query job runs in, not the project where the data lives. By running all Slateo queries in the dedicated project, you can set a quota that only affects Slateo's usage.

Additional cost controls

Beyond project isolation, you can implement these supplementary controls:

  • Per-query byte limits: Configure maximum_bytes_billed to limit individual query costs. BigQuery estimates bytes before executing—if the estimate exceeds the limit, the query fails without incurring a charge. Contact your Slateo account manager if you need assistance configuring per-query limits.
  • Budget alerts: Set up billing budgets as a backstop for cost awareness. Note that budgets provide alerts but do not enforce hard limits—use quotas for hard caps.

BigQuery cost controls documentation →

Pricing models

On-demand pricing:

  • Pay per query based on bytes processed
  • Best suited for variable or unpredictable workloads
  • Use quotas and per-query limits to control costs

Flat-rate pricing (reservations):

  • Pay for dedicated query processing capacity (slots)
  • Predictable monthly costs regardless of usage
  • Better for high-volume, consistent workloads
  • Quotas provide approximate limits (BigQuery may occasionally exceed them)

BigQuery pricing documentation →

Monitoring usage

Track BigQuery usage and costs through:

  1. BigQuery audit logs: Monitor query patterns and bytes processed
    • Go to IAM & Admin → Audit Logs
    • Enable Data Read logs for BigQuery API
  2. Cloud Monitoring: Set up dashboards for bytes billed and query counts
  3. Billing reports: Review detailed cost breakdowns by service account

BigQuery monitoring documentation →

Additional resources

For additional support or questions, contact your Slateo account manager.

Was this page helpful?

Was this page helpful?