BigQuery integration makes it easy to load Insightful’s data into a BigQuery data warehouse. When you integrate BigQuery with Insightful, you get a fully managed data pipeline loaded into a powerful and cost-effective data warehouse.
Insightful runs a periodic process to pull data related to your organization, teams, and employees from our database and load them into your BigQuery cluster.
❗ Note: This integration is a Premium add-on.
Getting Started
To store your Insightful’s data in BigQuery, a couple of steps should be taken:
Create a project and enable BigQuery.
Create a service account for Insightful.
Create the connection in Insightful.
Create a Project and Enable BigQuery
If you don’t have a project/enabled BigQuery, you need to create/enable a project and enable BigQuery:
Navigate to the Google Developers Console.
Configure the Google Cloud Platform:
If you don’t have a project already, create one.
If you have an existing project, enable the BigQuery API. Once you’ve done so, you should see BigQuery in the Resources section of Cloud Platform.
When you create your project, you must enable billing so data can be written into the cluster.
Create a service account for Insightful
In order to create a service account for Insightful, you need to follow these steps:
From the Navigation panel on the left, select IAM & admin → Service accounts
Click Create Service Account.
Enter a name for the service account (for example, Insightful-warehouses) and click Create.
Assign the service account the following roles:
BigQuery Data Owner
BigQuery Job User
Create a JSON key. The downloaded file will be used to create your data warehouse connection in the Insightful app.
If you have trouble creating a new service account, refer to Google Cloud’s documentation about service accounts for more information.
Create the integration in Insightful
To create the integration in Insightful, please follow these instructions:
Once you log in to your admin’s dashboard, select Settings → Integrations and search for BigQuery.
Data warehouse feature is disabled by default. Click on Request access and allow our team up to one working day to enable it for your organization.
Once the feature is enabled, click on Configure Integration.
Upload JSON key file and details will be automatically extracted. You can manually enter or edit integration details from your JSON key file (Project ID, Client Email, Client ID, Type and Private Key). Dataset field should be provided separately, and if it doesn’t exist, it will be created automatically. Enter a region name in the Location field (the default is multi-region US). Please find the detailed list here.
You can double-check if all entered details are correct by clicking on Test Connection.
Once you’ve entered all details, click Next. Enter connection name, choose the time period for syncing the data, how often syncs should occur, scope of employees and teams that will be included and finally data types that should be synced.
Click Save.
BigQuery Integration FAQ
How does BigQuery pricing work?
BigQuery offers both a scalable, pay-as-you-go pricing plan based on the amount of data scanned, or a flat-rate monthly cost. You can learn more about BigQuery pricing on Google Cloud’s BigQuery pricing page.
BigQuery allows you to set up Cost Controls and Alerts to help control and monitor costs. If you want to learn more about the costs associated with BigQuery, Google Cloud provides a calculator to estimate your costs.
How do I query my data in BigQuery?
You can connect a BI tool like Mode or Looker to BigQuery, or query directly from the BigQuery console. BigQuery supports standard SQL, which you can enable using Google Cloud’s query UI.
Can I customize my sync schedule?
We have a few sync schedules that you can choose from:
Hourly (every hour at 00:00)
Daily (every day starting at 00:00 UTC)
Weekly (every Sunday at 00:00 UTC)
In case of a failed sync, new attempt will be made every 15 minutes, until a successful sync has happened. You are not able to define your own schedule, other than the three options mentioned above. Please note that each type of sync may have up to 15 minutes delay of sync start.
What is a staging table?
Staging tables are temporary tables marked with _staging suffix. Each staging table contains the data related to the ongoing sync. After the sync is completed, it will be merged with the original table and deleted automatically.
Can I edit an existing connection?
Editing an existing connection will not possible. Admins need to delete existing connections and create new ones from scratch.