How to Create an Entity Relationship Diagram (ERD) for BigQuery?

From work and personal projects, I’ve found it difficult but important for a team to document the relationships of tables (i.e., what are the foreign keys to join tables?). In this post, I’m going to quickly demonstrate how to create an Entity Relationship Diagram (ERD) for BigQuery on Google Cloud Platform (GCP) using QuickDatabaseDiagrams (QDB).

Step 1. Visit QDB at https://www.quickdatabasediagrams.com/

Try out for free without signing up!

Step 2. Retrieve the schema of BigQuery tables from BigQuery UI.

1. Replace <your-gcp-project-id>, <your-bigquery-dataset-name>, and <your-bigquery-table-name> for your own use case.

SELECT
column_name, data_type
FROM
`<your-gcp-project-id>`.<your-bigquery-dataset-name>.INFORMATION_SCHEMA.COLUMNS
WHERE
table_name="<your-bigquery-table-name>"

2. Run the query above

3. Click “SAVE RESULTS” and then click “Copy to Clipboard”

4. Paste back to QDB, click “EDIT” and then “Untabify”

Step 3. Repeat Step 2 for the rest of your tables to be included in ERD