Live Integration Tests¶

Tags contributor guide testing

This document describes BenchBox’s live integration test suite that executes real benchmarks against cloud database platforms using actual credentials.

Overview¶

Live integration tests verify that BenchBox works correctly with real cloud databases by:

Connecting to actual Databricks, Snowflake, and BigQuery instances
Creating test schemas and loading data
Executing queries and verifying results
Cleaning up test resources

Important: These tests are skipped by default and only run when credentials are available.

Prerequisites¶

General Requirements¶

BenchBox installed with cloud platform extras: pip install "benchbox[cloud]"
Active cloud platform accounts with appropriate permissions
Environment variables configured with credentials

Cost Considerations¶

All tests use scale_factor=0.01 (~10MB of data) to minimize costs:

Databricks: <$0.05 per test run (SQL Warehouse usage)
Snowflake: <$0.05 per test run (Warehouse credit usage)
BigQuery: <$0.10 per test run (Storage + query processing)

Total estimated cost: <$0.20 per full test run across all platforms

Setup Instructions¶

1. Create Environment File¶

Copy the template and fill in your credentials:

cp .env.example .env

2. Configure Credentials¶

Edit .env and add credentials for the platforms you want to test:

Databricks¶

DATABRICKS_HOST=your-workspace.cloud.databricks.com
DATABRICKS_HTTP_PATH=/sql/1.0/warehouses/your-warehouse-id
DATABRICKS_TOKEN=your-access-token

How to get Databricks credentials:

Log into your Databricks workspace
Go to SQL Warehouses and select a warehouse
Click “Connection Details”
Copy the Server hostname and HTTP path
Generate a Personal Access Token from User Settings → Access Tokens

Snowflake¶

SNOWFLAKE_ACCOUNT=your-account.snowflakecomputing.com
SNOWFLAKE_USERNAME=your-username
SNOWFLAKE_PASSWORD=your-password
SNOWFLAKE_WAREHOUSE=COMPUTE_WH
SNOWFLAKE_DATABASE=BENCHBOX
SNOWFLAKE_SCHEMA=PUBLIC

How to get Snowflake credentials:

Your account identifier (e.g., xy12345.us-east-1)
Username and password for authentication
Warehouse name (create one if needed: CREATE WAREHOUSE COMPUTE_WH)
Database and schema for testing (will be created if missing)

BigQuery¶

BIGQUERY_PROJECT=your-project-id
BIGQUERY_DATASET=benchbox_test
BIGQUERY_LOCATION=US
GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json

How to get BigQuery credentials:

Create a Google Cloud project
Enable the BigQuery API
Create a service account with BigQuery Admin role
Download the service account JSON key file
Set GOOGLE_APPLICATION_CREDENTIALS to the key file path

3. Verify Setup¶

Test that credentials are configured correctly:

# Check what would run (dry-run)
make test-live-databricks --dry-run

# Run a single platform test
make test-live-databricks

Running Tests¶

Run All Live Tests¶

# Run all platforms (requires all credentials)
make test-live-all

Run Individual Platforms¶

# Databricks only
make test-live-databricks

# Snowflake only
make test-live-snowflake

# BigQuery only
make test-live-bigquery

Run with pytest Directly¶

# All live tests
uv run -- python -m pytest -m live_integration -v

# Specific platform
uv run -- python -m pytest -m live_databricks -v

# Specific test file
uv run -- python -m pytest tests/integration/platforms/test_databricks_live.py -v

Test Coverage¶

Databricks Tests (9 tests)¶

Connection - Verify SQL Warehouse connectivity
Version Info - Get platform metadata
Catalog Access - Verify Unity Catalog permissions
Schema Creation - Create isolated test schemas
Data Loading - Load TPC-H SF=0.01 data
Simple Query - Execute basic SELECT queries
TPC-H Query - Run TPC-H Query 1 on loaded data
COPY INTO - Test COPY INTO functionality
Cleanup - Verify schema deletion works

Snowflake Tests (9 tests)¶

Connection - Verify Snowflake connectivity
Version Info - Get platform metadata
Warehouse Access - Verify warehouse and database access
Schema Creation - Create isolated test schemas
Data Loading - Load TPC-H SF=0.01 data
Simple Query - Execute basic SELECT queries
TPC-H Query - Run TPC-H Query 1 on loaded data
PUT + COPY - Test PUT + COPY INTO workflow
Cleanup - Verify schema deletion works

BigQuery Tests (10 tests)¶

Connection - Verify BigQuery connectivity
Version Info - Get platform metadata
Project Access - Verify project and dataset access
Dataset Creation - Create isolated test datasets
Data Loading - Load TPC-H SF=0.01 data
Simple Query - Execute basic SELECT queries
TPC-H Query - Run TPC-H Query 1 on loaded data
GCS Load - Test GCS staging workflow
Query Cost - Verify query cost estimation
Cleanup - Verify dataset deletion works

Total: 28 live integration tests across 3 cloud platforms

Security Best Practices¶

Never Commit Credentials¶

.env files are in .gitignore - never commit them
Use .env.example as a template with placeholder values
Rotate tokens/passwords regularly

Use Least Privilege¶

Create dedicated service accounts with minimal permissions:

Databricks:

SQL Warehouse usage permission
Schema create/drop permission
No admin/cluster create permissions needed

Snowflake:

CREATE SCHEMA privilege
USAGE on warehouse and database
No ACCOUNTADMIN role needed

BigQuery:

BigQuery Data Editor role
BigQuery Job User role
Storage Object Admin (for GCS staging)

Isolate Test Resources¶

Tests use unique schema names with timestamps
Cleanup fixtures automatically remove test data
No production data is touched

Troubleshooting¶

Tests are Skipped¶

Problem: All tests show SKIPPED

Solution: Check that required environment variables are set:

# Databricks
echo $DATABRICKS_TOKEN

# Snowflake
echo $SNOWFLAKE_PASSWORD

# BigQuery
echo $GOOGLE_APPLICATION_CREDENTIALS

Authentication Errors¶

Databricks: “Invalid access token”

Verify token hasn’t expired
Check workspace URL is correct
Ensure SQL Warehouse is running

Snowflake: “Incorrect username or password”

Verify credentials in Snowflake UI
Check account identifier format
Try connecting with snowsql CLI first

BigQuery: “Could not load credentials”

Verify JSON key file path exists
Check service account has required roles
Try gcloud auth application-default login

Data Loading Failures¶

Problem: Tests fail during data loading

Solutions:

Verify sufficient warehouse/compute resources
Check network connectivity
Ensure sufficient storage quota
Review platform-specific logs

Schema Cleanup Failures¶

Problem: Warning messages about failed schema deletion

Impact: Usually benign - schemas with timestamps avoid conflicts

Solution: Manually drop test schemas if needed:

-- Databricks
DROP SCHEMA IF EXISTS benchbox_test_1234567890 CASCADE;

-- Snowflake
DROP SCHEMA IF EXISTS benchbox_test_1234567890 CASCADE;

-- BigQuery
DROP SCHEMA IF EXISTS benchbox_test_1234567890 CASCADE;

CI/CD Integration (Optional)¶

GitHub Actions¶

Tests can run in CI with secrets:

name: Live Integration Tests

on:
  workflow_dispatch:  # Manual trigger only
  schedule:
    - cron: '0 0 * * 0'  # Weekly on Sunday

jobs:
  test-databricks:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.11'
      - name: Install dependencies
        run: pip install ".[cloud]"
      - name: Run Databricks live tests
        env:
          DATABRICKS_HOST: ${{ secrets.DATABRICKS_HOST }}
          DATABRICKS_HTTP_PATH: ${{ secrets.DATABRICKS_HTTP_PATH }}
          DATABRICKS_TOKEN: ${{ secrets.DATABRICKS_TOKEN }}
        run: make test-live-databricks

Note: Store credentials as GitHub Secrets, never in workflow files.

Cost Control in CI¶

Run on manual trigger or schedule (not on every commit)
Use smallest possible scale factors
Set test timeouts to prevent runaway costs
Monitor cloud spend regularly

Development Workflow¶

Adding New Tests¶

Follow the existing test pattern in test_databricks_live.py
Use fixtures from conftest.py
Always include cleanup logic
Document expected cost in docstring

Testing Without Credentials¶

Regular smoke tests don’t require credentials:

# Run smoke tests (use stubs)
make test-smoke
# or
uv run -- python -m pytest -m platform_smoke -v

Debugging Live Tests¶

Enable verbose output:

# Verbose pytest output
uv run -- python -m pytest -m live_databricks -vv -s

# Capture logs
uv run -- python -m pytest -m live_databricks -v --log-cli-level=DEBUG

Additional Resources¶

Support¶

For issues or questions:

GitHub Issues
Review test output for specific error messages
Check platform-specific documentation

Remember: Always protect your credentials and be mindful of cloud costs when running live integration tests.