Guides
Guides

BigQuery

Suggest Edits

Setting up tables

Data in BigQuery typically resides in tables. When sharing data with Toplyne, the best practice is to create separate tables for event data and profile data partitioned on a time column.

How does Toplyne sync work?

Toplyne requires read permissions on all the tables you want to sync with us. You can grant us read permissions at individual table-level granularity or on the complete dataset in which your tables reside.

We prefer to ingest your data from BigQuery in an incremental fashion. You need to add a monotonically increasing timestamp column in your tables. We will use this column to identify all the new data that has been added to the tables and will only read-only that much data for syncing.

You can add a column called UPDATED_AT with timestamp data type to your table, and this column will highlight when the row in question was added to the table.

If you don't share such a timestamp column, we'll do a daily full scan of your table and sync it entirely.

Event data table

This table will capture a user's action; it will help answer the question, “what has a user done on your product, and when.”

Rows

Each row in this table represents an event triggered by a user.

Columns

  • USER_ID (Required): Key to identify which user has performed the event
  • ACCOUNT_ID (required for account-level analytics): Key to identify which account the particular user belongs to
  • EVENT_NAME (Required): Name of the event
  • TIMESTAMP (Required): Timestamp at which the event has occurred
  • UPDATED_AT (Required): UTC timestamp when the row has been updated or added (not the event timestamp)
  • EVENT_PROPERTIES (Optional): Event properties, typically shared as a JSON
    with key/value pairs

Partition

Partition this table on TIMESTAMP column.

Sample Event Table

USER_IDACCOUNT_IDEVENT_NAMETIMESTAMPUPDATED_ATEVENT_PROPERTIES
129823Payment_Initiated2022-10-26 07:08:342023-01-09 17:08:34{
"payment_Id": 123,
"amount": 200,
}
156777Trial_Ended2022-11-01 20:01:142023-01-09 17:08:34{
"account_Id": 77
}

Profile data table

This table will capture any profile information a user or an account has; it will help answer the question, “who is this user or account?”.

Rows

Each row in this table is a unique entity.

Columns

  • USER_ID/ACCOUNT_ID (Required): Key to identify which entities' properties are listed in the row.
  • USER_CREATION_TIME/ ACCOUNT_CREATION_TIME (Optional): To identify since when the user or account has been active.
  • UPDATED_AT: UTC timestamp when the row has been updated or added (not the event timestamp)
  • USER_PROPERTIES/ACCOUNT_PROPERTIES: Each subsequent column is a profile key; against it, you will have its value. For example, you may include the email address, geographical location, account name, current billing plan they are on, etc.

Partition

Partition this table on UPDATED_AT column.

Sample User Table

USER_IDUSER_CREATION_TIMEUPDATED_ATEmailCountryCurrent Plan
12962022-10-26 07:08:342022-11-26 07:08:34[email protected]UKAnnual Plan
26762022-11-01 20:01:142022-12-01 20:01:14[email protected]UKMonthly Plan

Sample Account Table

ACCOUNT_IDACCOUNT_CREATION_TIMEUPDATED_ATAccountNameCountryCurrent Plan
232022-10-26 07:08:342022-11-26 07:08:34QueenUKAnnual Plan
562022-11-01 20:01:142022-11-26 07:08:34BeetlesUKMonthly Plan

Step-by-step guide to share

Toplyne has a service account [email protected], which will be used as the principal to give access to your data in BigQuery.

You need to ensure that you are consistent with the Project ID on both the IAM and the BigQuery console.

  1. Login into your Google Cloud Platform account.

  2. Go to IAM & Admin -> Click on Roles

  1. You need to grant access to the Toplyne principal as shown here. Access is required for the following two roles: BigQuery Job User and BigQuery Read Session User.
  1. Now let's move to the BigQuery console.
  1. Create a dataset for Toplyne to use:
    In the BigQuery console, select your project and create a dataset named toplyne_shared_views.
    You can do this from the query console as follows:
    create schema toplyne_shared_views;
  2. Select the dataset you created above. Then navigate to Sharing > Permissions.
  1. In the permissions section, select Add Principal.
  1. Grant BigQuery Data Owner on this dataset to Toplyne principal and save.
  1. Share your data:
    1. If all the tables you wish to share are already in a dataset, grant BigQuery Data Viewer access to the whole dataset and click Save.
    2. If you wish to share only specific tables or views with Toplyne, grant BigQuery Data Viewer access to those tables one by one and click Save.
    3. In case you are sharing views with us, please ensure that you have granted us BigQuery Data Viewer access to all the underlying tables for the corresponding view definition.
  1. Once you have completed the outlined steps, share your Project ID with Toplyne, along with the shared dataset & table names.

Updated over 1 year ago