Breakout: BigQuery

Connect Ascend to BigQuery to run your data pipelines on Google Cloud.

In this session, you'll set up a BigQuery trial (if needed), grant Ascend access to your GCP project, and configure a BigQuery Data Plane Connection to run your pipelines. You'll also leverage BigQuery's AI capabilities to improve your existing flow.

Prerequisites

Before you begin, ensure you have:

• Completed Hands-On Session #1
• If you'd like to use your own GCP project, you'll need a Google Cloud Platform (GCP) account with Admin permissions to a project
• Credit card or bank account to sign up for a free trial of BigQuery

Don't have a GCP project?

You can create a free GCP account which includes $300 in credits and access to BigQuery's free tier.

Admin permissions required

You need Admin permissions to your GCP project to grant access to Ascend's service account.

Step 1: Sign up for a BigQuery trial

If you don't already have a GCP account, follow these steps to get started with $300 in free credits.

Already have a GCP account with admin permissions?

Skip ahead to Step 2: Get your Ascend Environment's service account.

Create a Google Cloud account

1. Go to cloud.google.com/free

2. Click Get started for free
3. Sign in with your Google account (or create one)
4. Fill out the required information:
   • Country
   • Account type (Individual is fine for the bootcamp)
   • Payment method (Required for verification, but you won't be charged)
5. Accept the terms and click Start my free trial

tip

Your free trial includes $300 in credits valid for 90 days. BigQuery also has a permanent free tier with 1 TB of queries per month and 10 GB of storage.

(Optional) Create a GCP project

1. Once signed in, go to the Google Cloud Console
2. Click the project dropdown at the top of the page
3. Click New Project
4. Enter a project name (e.g., ascend-bootcamp)
5. For Organization, select No organization (if available)
6. Click Create
7. Wait for the project to be created, then select it from the dropdown

Permission error?

If you see "You do not have the required resourcemanager.projects.create permission", this means your Google account is part of an organization that restricts project creation. Try one of these options:

1. Use a personal Gmail account - Sign up with a personal @gmail.com account instead of a work/school account
2. Select "No organization" - When creating the project, make sure the Organization field is set to "No organization"
3. Ask your admin - If using a work account, ask your GCP admin to grant you project creation permissions or create a project for you

Enable BigQuery

1. With your project selected, use the search bar at the top to search for BigQuery
2. Click on BigQuery in the results
3. Select your project to view the BigQuery console

Step 2: Get your Ascend Environment's service account

Ascend uses a service account to connect to your GCP project. You'll need to grant this service account access to BigQuery.

1. In Ascend, go to Settings (top right menu)
2. Navigate to Environments
3. Click on the Default environment
4. Copy the Service Account Email (it looks like ascend-env-xxxxx@ascend-io-xxxx.iam.gserviceaccount.com)

Step 3: Grant BigQuery permissions in GCP

1. Navigate to IAM & Admin > IAM in the GCP Console
2. Select your GCP project (top left dropdown)
3. Click Grant Access
4. Paste the Ascend service account email in the New principals field
5. Click Select a role and choose BigQuery Admin
6. Click Save

Step 4: Create a BigQuery Connection in Ascend

Now let's connect Ascend to your BigQuery project. To start, navigate to your Workspace in Ascend and choose your preferred method:

Ask Otto

Open Otto and ask:

Connect to my GCP project with project ID YOUR_GCP_PROJECT_ID and dataset ASCEND_BOOTCAMP (create if not exists)

Otto will create the Connection configuration for you.

Use the UI

1. Navigate to your Workspace in Ascend
2. From the Super Graph, click the + button next to Connections
3. Select BigQuery as the connection type
4. Fill in your GCP Project ID
5. Fill in the Dataset as ASCEND_BOOTCAMP and select Create dataset if doesn't exist yet
6. Click Test Read Connection to verify it works
7. Save the connection

tip

If you don't see the Connections section, you may be in a Flow Graph. Use Cmd+K to search for "Super Graph" and navigate there.

Create a file

Create a file connections/data_plane_bigquery.yaml in your Project:

connection:
  bigquery:
    project: YOUR_GCP_PROJECT_ID
    dataset: ascend_bootcamp
    create_dataset_if_not_exists: true

Save the file and Ascend will automatically detect the new Connection.

Step 5: Set BigQuery as the default Data Plane

To use BigQuery for all your Flows, update your project defaults.

You can ask Otto to do this for you:

Set BigQuery as the default Data Plane for all Flows

or do this manually:

1. Open the ascend_project.yaml file in your Project
2. Add or update the defaults section:

project:
  name: my-project
  defaults:
    - kind: Flow
      name:
        regex: .*
      spec:
        data_plane:
          connection_name: data_plane_bigquery

This tells Ascend to use BigQuery for all Flows by default.

Step 6: Run a pipeline on BigQuery

Let's verify everything is working by running the demo pipeline on BigQuery.

1. Navigate to the sales Flow in your Workspace
2. Click Run Flow or use Otto to trigger a run
3. Watch the pipeline execute on BigQuery

You notice you a syntax error here, because this pipeline was originally written for DuckDB. Let's ask Otto to fix it.

Fix the syntax error in the sales flow and rerun the flow to ensure it works

4. Check the data in the BigQuery Console to confirm it was written

tip

You can also ask Otto: "Run the sales flow" or "Show me the data in the customers table"

Step 7: Leverage BigQuery's AI capabilities

BigQuery has a built-in AI capabilities that can help you with your data. Let's ask Otto for ideas on how to use BigQuery ML in this flow

How can I leverage BigQuery ML in this flow?

Then ask Otto to implement the ideas you are most excited about

Checkpoint

By the end of this session, you should have:

• A GCP project with BigQuery enabled
• Granted Ascend's service account access to BigQuery
• Created a BigQuery Data Plane Connection in Ascend
• Set BigQuery as the default Data Plane
• Successfully run a pipeline on BigQuery
• Leveraged BigQuery's AI capabilities to improve the flow

Need help?

Ask a bootcamp instructor or reach out in the Ascend Community Slack.

Next steps

Return to the Bootcamp Overview to continue with Day 2 sessions, or explore more BigQuery features with Otto!