Connecting BigQuery to Elementum - Elementum Documentation

Get your CloudLink connection up and running with Google BigQuery. This guide covers security architecture, service account setup, IAM permissions, and configuration steps.

Secure Direct Access

Elementum provides secure, in-place data access to your BigQuery datasets:

Authentication

Service Account credentials permit Elementum access to defined datasets and tables. You retain full control over the data and can terminate access at any time.

In-Place Access

Data stays in your BigQuery project. No data is copied or moved to external systems.

How It Works

Google Cloud Side

A specific dataset and defined set of tables with IAM permissions (e.g., read/write, read-only) are configured to enable business processes.

Elementum Side

Further access to view the data within Elementum is restricted at the user level through access policies.

Direct Connect Architecture

This connection allows for read/write access and the ability to execute workflows on your data:

Your Organization
Elementum Platform

Your Google Cloud Project:

Contains your data (datasets, tables, views)
Grants access to Elementum Service Account with appropriate IAM roles
Network policies can restrict access to known Elementum Platform IP addresses

Security Architecture

Data Encryption

At Rest:

Service Account JSON encrypted using industry-standard algorithms
Credentials encrypted and never returned outside internal system

In Transit:

All traffic encrypted using TLS
Secure connection via BigQuery API

Access Control

Network Security:

VPC Service Controls support
Authorized networks configuration

Authentication:

Service Account with JSON key
IAM-based role permissions

Whitelist Elementum IP Addresses (Optional)

If you use VPC Service Controls or authorized networks, configure them to allow connections from these IP addresses:

US Region
Europe Region

210.166.136
209.114.114
72.254.246

185.13.42
182.157.140
65.106.188

Note: BigQuery uses Google’s global network and doesn’t require IP whitelisting by default. IP restrictions are only needed if you’ve configured VPC Service Controls or other network-level restrictions.

Setting up Elementum Access in Google Cloud

Prerequisites

Before starting the setup:

Ensure you have Project Owner or IAM Admin access in Google Cloud
Have the BigQuery API enabled in your project
Identify which datasets and tables need Elementum access
Plan your access level (read-only vs read/write)

Setup Steps Overview

Create Service Account

Create a dedicated service account for Elementum access.

Create Platform Dataset

Create an empty dataset for Elementum platform operations. This is NOT where your data lives.

Grant IAM Roles

Assign appropriate BigQuery roles to the service account.

Generate JSON Key

Download the service account key file for authentication.

Run These Steps in Google Cloud Console

Create Service Account

Navigate to IAM & Admin > Service Accounts in Google Cloud Console:

Click Create Service Account
Service account name: elementum-cloudlink
Service account ID: elementum-cloudlink (auto-generated)
Description: Service account for Elementum CloudLink integration
Click Create and Continue

You’ll grant roles in the next steps. Click Done to create the service account first.

Create Platform Dataset for Elementum

In the BigQuery Console:

Select your project
Click Create Dataset
Dataset ID: elementum_platform
Data location: Same region as your data (e.g., US, EU)
Click Create Dataset

Critical: The elementum_platform dataset is for Elementum’s internal operations. Do NOT put your data tables here. Your data tables should remain in your existing datasets.

Grant Service Account Access to Platform Dataset

Grant the service account full access to the platform dataset:

Click on the elementum_platform dataset
Click Sharing > Permissions
Click Add Principal
Principal: elementum-cloudlink@YOUR_PROJECT.iam.gserviceaccount.com
Role: BigQuery Data Owner
Click Save

Or via gcloud CLI:

gcloud projects add-iam-policy-binding YOUR_PROJECT_ID \
  --member="serviceAccount:elementum-cloudlink@YOUR_PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataOwner" \
  --condition="expression=resource.name.startsWith('projects/YOUR_PROJECT_ID/datasets/elementum_platform'),title=elementum-platform-access"

Grant BigQuery Job User Role

The service account needs to run queries:

Go to IAM & Admin > IAM
Find the elementum-cloudlink service account
Click Edit (pencil icon)
Click Add Another Role
Select BigQuery Job User
Click Save

Or via gcloud CLI:

gcloud projects add-iam-policy-binding YOUR_PROJECT_ID \
  --member="serviceAccount:elementum-cloudlink@YOUR_PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.jobUser"

Grant Access to Your Data Datasets

For each dataset containing data you want to access:

Read/Write Access
Read-Only Access
Table-Level Access

Navigate to your data dataset in BigQuery
Click Sharing > Permissions
Click Add Principal
Principal: elementum-cloudlink@YOUR_PROJECT.iam.gserviceaccount.com
Role: BigQuery Data Editor
Click Save

# Via gcloud CLI
bq add-iam-policy-binding \
  --member="serviceAccount:elementum-cloudlink@YOUR_PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataEditor" \
  YOUR_PROJECT_ID:your_dataset

Navigate to your data dataset in BigQuery
Click Sharing > Permissions
Click Add Principal
Principal: elementum-cloudlink@YOUR_PROJECT.iam.gserviceaccount.com
Role: BigQuery Data Viewer
Click Save

# Via gcloud CLI
bq add-iam-policy-binding \
  --member="serviceAccount:elementum-cloudlink@YOUR_PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataViewer" \
  YOUR_PROJECT_ID:your_dataset

For fine-grained control, grant access to specific tables:

# Grant access to specific table
bq add-iam-policy-binding \
  --member="serviceAccount:elementum-cloudlink@YOUR_PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataViewer" \
  YOUR_PROJECT_ID:your_dataset.your_table

IAM Roles Reference:

Role	Permissions
BigQuery Data Viewer	Read tables, views, and metadata
BigQuery Data Editor	Read + Insert, update, delete data
BigQuery Data Owner	Full control including delete tables
BigQuery Job User	Required to run queries

Generate Service Account JSON Key

Download the authentication credentials:

Go to IAM & Admin > Service Accounts
Click on elementum-cloudlink service account
Go to Keys tab
Click Add Key > Create New Key
Select JSON format
Click Create
Save the downloaded JSON file securely

Security: Store this JSON key securely. It provides full access to the permissions you’ve granted. Never commit it to version control or share it publicly.

Configuring CloudLink in Elementum

After completing the Google Cloud setup, configure the connection in Elementum:

Navigate to CloudLink Settings

Go to Settings > Cloud Links > Add Connection and select BigQuery.

Enter Connection Details

Fill in the connection form:

Field	Value	Description
Name	`Production BigQuery`	Descriptive name for your connection
Project ID	`your-gcp-project-id`	Your Google Cloud project ID
Dataset ID	`elementum_platform`	The empty platform dataset (NOT your data dataset)
Location	`US` or `EU`	Dataset region (must match platform dataset location)
Service Account JSON	`{...}`	Paste entire contents of the downloaded JSON key file
Read Only	Toggle	Enable for read-only access

Critical - Dataset ID Field: Enter the empty platform dataset you created (e.g., elementum_platform), NOT your data dataset. If you enter your data dataset here, it will be hidden from workflow building and you won’t be able to access your data.

Configure Resource Scheduler

Set how frequently Elementum syncs with BigQuery:

Setting	Default	Description
Interval	24	Number of time units between syncs
Time Unit	Hour	Minutes, Hours, or Days

Cost consideration: More frequent syncs consume more BigQuery query slots. For large datasets, consider longer intervals unless real-time data is required.

Test and Save

Click Save to test the connection and save if successful:

Validates Service Account JSON format
Tests authentication with BigQuery API
Verifies dataset access

If saved successfully, your connection is properly configured.

Configure Data Access

After saving the connection:

Select Datasets: Choose which datasets to expose in Elementum
Select Tables: Pick specific tables from each dataset
Configure Field Mapping: Map columns to Elementum field types
Set Primary Key: Identify the unique identifier column for each table

Verification and Testing

After completing the setup, verify everything is working correctly:

Test Service Account Access

In Google Cloud Console, verify the service account has the correct permissions:

Go to IAM & Admin > IAM
Find elementum-cloudlink service account
Verify it has:
- BigQuery Job User at project level
- BigQuery Data Owner on elementum_platform dataset
- BigQuery Data Viewer or BigQuery Data Editor on your data datasets

Test Query Access

You can test the service account by impersonating it:

# Using gcloud CLI
gcloud auth activate-service-account \
  --key-file=path/to/your-key-file.json

# Test query
bq query --use_legacy_sql=false \
  'SELECT COUNT(*) FROM `your_project.your_dataset.your_table`'

Test in Elementum

Verify the connection shows as Connected in CloudLink settings
Browse to the integrated table in Elementum
Verify data loads correctly
Test creating/updating a record (if write access was granted)

Troubleshooting

Connection Issues

Cannot Connect from Elementum:

Verify the Service Account JSON is complete and correctly formatted
Confirm the project ID matches your GCP project
Check that BigQuery API is enabled in the project
Verify VPC Service Controls aren’t blocking access (if applicable)

“Permission denied” Error:

Ensure BigQuery Job User role is granted at project level
Verify the service account email is correct in IAM policies

Cannot See Data Tables

Tables not visible in Elementum:

Most common cause: You entered your data dataset in the Dataset ID field instead of the platform dataset
Verify IAM roles are granted on the correct datasets
Check that the service account has BigQuery Data Viewer or BigQuery Data Editor on your data datasets
Confirm tables exist in the specified datasets

Permission Errors

“Access Denied: Table not found” Errors:

Verify dataset-level permissions are granted
Check that the service account has access to the specific table
Ensure the Location in Elementum matches the dataset location

“User does not have permission to run query” Error:

Grant BigQuery Job User role at the project level
This role is required to execute any queries

Performance Issues

Slow Query Performance:

Consider using partitioned tables for large datasets
Review and optimize query patterns
Check BigQuery slot availability
Consider reserved slots for consistent performance

High Costs:

Increase sync interval to reduce query frequency
Use table partitioning and clustering
Consider using BigQuery BI Engine for frequently accessed data

Security Best Practices

Principle of Least Privilege

Grant only necessary IAM roles to the service account
Use BigQuery Data Viewer when write access isn’t required
Grant access at dataset level, not project level
Regularly audit IAM policies

Network Security

Consider VPC Service Controls for sensitive data
Use authorized networks if applicable
Monitor BigQuery audit logs
Set up alerts for unusual access patterns

Key Management

Rotate service account keys periodically (recommended: every 90 days)
Store keys securely (use Secret Manager in production)
Delete unused keys
Consider workload identity federation for enhanced security

Monitoring

Enable BigQuery audit logs
Monitor query costs and usage
Set up billing alerts
Review access patterns via Cloud Logging

Next Steps

Configure Apps

Set up your first app in Elementum using your connected data

Create Automations

Build workflows that act on your BigQuery data

Setup AI Features

Enable AI-powered search, automations, and insights

Data Best Practices

Optimize your data models for Elementum

Additional Resources

CloudLink Overview

Learn more about CloudLink architecture

BigQuery Documentation

Official Google BigQuery documentation

IAM Best Practices

Google Cloud IAM security guide

Get Support

Contact our team for setup assistance

This guide reflects the latest BigQuery and Elementum best practices. For additional assistance, contact support@elementum.io.

​Secure Direct Access

Authentication

In-Place Access

​How It Works

​Direct Connect Architecture

​Security Architecture

Data Encryption

Access Control

​Whitelist Elementum IP Addresses (Optional)

​Setting up Elementum Access in Google Cloud

​Prerequisites

​Setup Steps Overview

​Run These Steps in Google Cloud Console

​Configuring CloudLink in Elementum

​Verification and Testing

​Troubleshooting

​Security Best Practices

Principle of Least Privilege

Network Security

Key Management

Monitoring

​Next Steps

Configure Apps

Create Automations

Setup AI Features

Data Best Practices

​Additional Resources

CloudLink Overview

BigQuery Documentation

IAM Best Practices

Get Support

Secure Direct Access

How It Works

Direct Connect Architecture

Security Architecture

Whitelist Elementum IP Addresses (Optional)

Setting up Elementum Access in Google Cloud

Prerequisites

Setup Steps Overview

Run These Steps in Google Cloud Console

Configuring CloudLink in Elementum

Verification and Testing

Troubleshooting

Security Best Practices

Next Steps

Additional Resources