Skip to content

BigQuery

BigQuery is our data warehouse hosted on Google Cloud Platform. Prod | Dev

Datasets

Datasets in dw-prod-gwiiag:

Dataset Description
ai Tables used for LLMs and ML models
analytics_196617415 Google Analytics 4 daily event export
bulk_load One-time tables, dumps and ad-hoc tables
dbt Models run by dbt
dbt_cable KYC and user tables for Cable
dbt_finance Finance dbt models that require permissions control
dbt_ml Tables generated by dbt for ML models
dbt_rebtel Rebtel dbt models that require permissions control
dbt_reporting Tables for periodic, file-based data products
dbt_reverse_etl Tables supporting the reverse ETL process that rely on change data capture
dbt_semantic Semantic-layer models built by dbt
dbt_stage Processed events from the backend event dump, created by dbt
dbt_themis Risk configuration for the events
dbt_utils Utility models run by dbt
event_hub Data from Pub/Sub topics
firebase Data from Firebase
funnel Tables coming from Funnel
gcs Files ingested from GCS buckets, triggered by Pub/Sub and Cloud Functions
google_sheets Tables imported from Google Sheets
intercom Intercom tables coming from Stitch
kinesis Raw backend events from Kinesis
quickbooks, quickbooks_<entity> QuickBooks tables coming from Stitch, one dataset per Majority legal entity
reverse_etl Tables used for reverse ETL in tools like Airflow
s3 Files ingested from S3
searchconsole Tables coming from Google Search Console
seed_data Seed data loaded by dbt. See Seeds

Finding Raw Events

All backend events are sent through Kinesis to one table in our BigQuery data warehouse:

For Prod: dw-prod-gwiiag.kinesis.backend_events

For Stage: dw-dev-s5dlij.kinesis.backend_events_stage

For Dev: dw-dev-s5dlij.kinesis.backend_events

Best Practices

  • Use CTEs to filter data before joining with other tables. This will improve performance and readability.
  • List the columns (avoid using *) to improve readability and performance.
  • Always use the date filter. Extra points if you use the partition column to filter the data ⭐️. You can find it in table details.
  • If the table is clustered, use the cluster column to filter the data and it must be the first filter. You can also find it in table details.
  • All names should be in snake_case.
  • If you need any help please feel free to ask us! 💬

Querying backend_events table

select *
from dw-prod-gwiiag.kinesis.backend_events
where event_ingested_at >= 'yyyy-mm-dd'
  and event_name = 'EventName'
limit 100;

Warning

  • Always filter by event_ingested_at first - The table is partitioned by ingestion day for performance
  • Use event_name as secondary filter - The table is clustered by this field for faster queries
  • Add additional conditions after these two filters as needed
  • If you need to check data in the event, try querying the dbt_stage.event_name_as_snake_case table.

Performance Monitoring

Importing ad-hoc data

Sometimes we might receive some data that we need to import into BigQuery for ad-hoc analysis or backfill purposes. Before the import the file should be placed in the majority-bulk-upload in the dw-prod project. It should follow the naming convention: <date_of_export>_filename. It can then be imported into dw-prod-gwiiag.bulk_load dataset. You can use UI to import the data, for CLI use the instructions below. Table name should be the same as the filename (snake_case).

JSON Import

BigQuery only supports newline-delimited JSON (ndJSON) format. See Loading JSON data from Cloud Storage.

Convert JSON List to ndJSON

If you have a JSON list file, convert it to ndJSON:

jq -c '.[]' json_list.json > result.ndjson

Import with CLI: 

bq load \
  --autodetect \
  --null_marker='' \
  --source_format=NEWLINE_DELIMITED_JSON \
  dw-prod-gwiiag.bulk_load.20241114_table_name \
  gs://majority-bulk-upload/20241114_table_name.json

Time Travel

BigQuery's time travel feature allows you to query historical data.

BigQuery retains historical data for: - 7 days by default - Check your table's time travel window before attempting recovery

select *
from dataset_name.table_name
for system_time as of timestamp_sub(current_timestamp(), interval 1 hour);

Common Use Cases

  • Recover accidentally deleted data
  • Compare current vs. historical states
  • Audit data changes
  • Restore tables to a previous state