/
Use data that exists in BigQuery

Use data that exists in BigQuery

If data already resides in BigQuery, there is a simple way to integrate it into the Wondaris platform. The below article steps out that process.

This assumes you are using Wondaris' Centralise - should you have access to Wondaris On Your Data, there are other ways to integrate with your data warehouse directly - contact support@wondaris.com for more information.

Prerequisites

The following outlines the prerequisites for this process.

  1. Data exists in BigQuery

  2. BigQuery region where the data resides is the same as the defined region in your Wondaris infrastructure

  3. If using a view to expose underlying data, you can either create an authorized view or add the service accounts directly to the underlying data:

    1. Using BigQuery Authorised Views

    2. Direct permissions on the source data: the following Wondaris service accounts must have permissions to read that data:
      (Note: if data resides in a dataset within the Wondaris MDP project, these permissions will already be associated)

      1. mdp-cloud-run-service@wondaris-193901.iam.gserviceaccount.com

      2. mdp-ingestion-exec-worker@wondaris-193901.iam.gserviceaccount.com

      3. wndrs-cdp-worker@wondaris-193901.iam.gserviceaccount.com

  4. The required permissions for the above are as follows:

    1. BigQuery Resource Viewer

    2. BigQuery Data Viewer

    3. BigQuery User

  5. Know what data is important and how it will be used (this is important to improve performance, reduce cost & ensure the right data is available for use in the CDP.

Instructions

  1. Define a mechanism for exposing the data in BigQuery:

    1. Data View (simplest, but may be slower and cause increased cost, depending on underlying data)

    2. Materialised View (simple, powerful, could reduce cost & improve performance through partitioning & clustering - may not be possible to use, depending on query)

    3. Materialised table via BQ Schedule (powerful & cost effective, but limited by rate of “ingestion” - ie: when the schedule is set)

    4. Materialised table via some other mechanism (most powerful & flexible, but requires externally setup data pipelines)

  2. Create the table or view in BigQuery within the Wondaris_MDS dataset in your Wondaris MDP project - copy the table or view slug from the BigQuery user interface (at the top or in the “View ID” / “Table ID” in the details tab - eg: website_visits_view in the below image):

     

  3. Log into the Wondaris Centralise Platform

  4. Navigate to “MDS Tables” & Click “Add MDS Table”

     

  5. Fill out the form to define the table in Wondaris:

    1. Centralise - choose the appropriate Centralise account to associate the data to (this is likely to only have one choice if your business has only one licence)

    2. Tick the “From Existing” checkbox

    3. GCP Table / view dropdown - find the table or view from the list from BigQuery (eg: website_visits_view above)

    4. Name - a friendly name to identify the table in Wondaris' user interface

    5. Description - optionally add a description to further explain what data is contained within this table and/or the use cases for it.

    6. Centralise Table Types - choose either events or customer, depending on the MAIN data within this table

       

  6. Click “Create”

  7. Once created, click the Edit Metadata button (it should appear next to where the create button was, alternatively you can go back to MDS tables in the left nav and find the table to click the Metadata button)

  8. If you imported the table directly using the “From existing” checkbox, you can import the metadata using the Import from BQ button on the top right

    Note: Before you import table metadata, please be aware that existing metadata fields may be overwritten.

  9. Check each column in the imported metadata, filling in the following:

    1. Include a friendly name & description to help identify the data that your users will know

    2. Add in any business tags, taking note of:

      1. if a column does not have a tag, it will NOT be included in the CDP ingestion

      2. you must specify a customer identifier column or it will not be included in the CDP ingestion (we need to know how to link data to customers) - note: if the data is from a marketing or social platform, use the generic “Customer ID > Customer Marketing ID or Customer Social ID” in the available metadata tags.

    3. Flag any sensitive or pre-hashed values using the flags in the row

    4. Click “Update Metadata” to save your work

       

  10. WIN!

Related articles