Skip to main content

BigQuery connection setup guide

Updated

Crisp_and_BigQuery_Logos.png

BigQuery is a scalable, dynamic and powerful enterprise-ready cloud data warehouse that enables robust yet simplified computing features, such as business intelligence. You can connect Crisp with Google’s BigQuery, to sync your distributor and retailer data securely to the cloud, then integrate that data into your internal systems.

Note: To create this connection, you need your Google Cloud Platform account email and Google cloud region. For more information, see Google Cloud Documentation > Regions and Zones.

To connect Crisp to BigQuery:

  1. Log into your Crisp account.
  2. From the main menu on the left side of the screen, select Connectors.
    main_menu_connectors.png
  3. Select the Add connector button.
    connector_setup_add.png
  4. Select the Data Destinations tab, then select the Google BigQuery connector tile.
  5. If you want, update the default name of the connector. If you're going to set up multiple data feeds to this destination, we recommend using something unique.

    bigquery_001.png
  6. Enter the Google Cloud account email you want to receive the data share, choose an account type (User or Group), then select the Add button.

    biquery_002.png

    Note: Crisp does not verify these user accounts. We strongly recommended that you copy and paste the email addresses rather than enter them manually. Also, be sure to select the correct account type or your data will not be shared.

  7. Select your Google Cloud Region (e.g., Northern Virginia). 
    Note: You cannot change this setting once the connector is saved. 

    Google_BigQuery_002.png

  8. In the Connector source data section, select the Select button.
    Google_BigQuery_003.png
    The Select source data screen appears.

  9. Select the retailer(s) for which you want to export data (e.g., UNFI), then choose which data tables and columns to include in the export. For detailed instructions on making your data feed selections, see Selecting Destination Data Sources.
    Hint: You can select the book icon to the right of a table name to see its documentation.
    Excel_Outbound_003.png

  10. To complete your selections, select the Accept button.

    Excel_Outbound_004.png
    The window closes and you return to the connector setup screen.

  11. If you want, choose additional columns to include in your export:
    • To include a column that identifies which source connector the data came from, select the Include Source Connector Configuration ID option.
      Hint: This is useful if you have multiple connectors for the same retailer, such as one for U.S. sales and one for Canadian sales.
    • To include a column that provides the date and time data was last sent from Crisp to the destination, select Include Last Exported Timestamp.
      Note: Crisp retroactively ingests and re-exports data for a set period of time after the initial data capture, known as a lookback period, to account for changes from the source (such as missing register sales added later). You can use the last exported timestamp column to confirm if data was recently exported from Crisp to help troubleshoot issues or reduce costs by automating how often data flows from a destination to other systems at your organization. For more on retroactive data changes, see Data changes for destination connections.

  12. Select the Save button.

    connector_setup_save.png

    The connection is saved. You can check the status of the connection by clicking the new Google BigQuery tile. 

A diagnostic checklist appears on the right side of the screen. Once all the items are complete, green check marks appear next to them and the connection is finalized.

Accessing your Crisp data in BigQuery

Once you setup the connection, you can access your Crisp data in BigQuery.

Note: Crisp initiates a data load into the BigQuery project within 10 minutes to export the first dataset that includes backfill data. The time to initially backfill data depends on the number of tables, but should not take more than a day. Ongoing data availability depends on the source, but Crisp data typically updates within six hours of source data availability. When the connector is deleted, Crisp deletes the connected datasets as well. 

To access your data in BigQuery:

  1. To access your data in BigQuery, follow the instructions in the BigQuery documentation > Star a project article.
  2. Choose to star your project by name and enter crisp-platform-shared as the project name.

The crisp-platform-data project now appears in your BigQuery Explorer and contains the Crisp datasets you specified.