BigQuery Information Schema: DDL to ER Diagram
![](https://framerusercontent.com/images/4PkpusPVZfn8e5MYmeVCztQgviQ.jpeg)
Norapath Arjanurak
data-modeling
Part 1: BigQuery Information Schema: Primary Key and Foreign Key
Part 3: A Guide to Transformational Modeling with A BigQuery Dataset
From BigQuery DDL to ER Diagram
In Part 1, we discussed how to extract metadata and constraints from BigQuery datasets using Information Schema. Now, in Part 2, we will focus on how to use this metadata to create an ER diagram. This diagram will help to clearly describe the structure and relationships within the dataset.
Creating Tables and Adding Constraints
From Part 1, we extracted the DDL and the constraints (PK and FK) from BigQuery information schema which will be used to describe the relationships between tables at the column level. The following commands show the DDL that integrates these relationships as primary and foreign keys.
This command is the DDL retrieved from the result of the query given in Part1 with the added constraints. While BigQuery does not support direct ERD generation, we can use the Datascale platform to easily visualize the DDL of the above query, as shown below.
![Thelook e-commerce ER diagram](https://framerusercontent.com/images/DGYbRC5F22vx97bTxT5GvsaDGc.png)
Datascale provides capabilities to visualize SQL schemas and ERDs. You can use the DDL from the above queries to create an ERD. Following these steps to use the Datascale platform.
Login to Datascale: Login or sign up with a Google account.
Navigate to the SQL models: Go to the SQL models section on the left panel and create a project.
Input DDL Statements: Add a new SQL and paste your SQL code into the workspace. It will automatically save.
Generate ERD: Click on the “Diagram” and then “ER Diagram” on the right panel. It will show the column-level diagram.
Alternatively, you can see the visualization in action from this link: https://datascale.cloud/sqls/bq-schemaddl-2ZHeQ0wfUw4xSE3Xg5BDdguHc8RCewhSaXW1VtWs
![DDL to ER diagram using Datascale](https://framerusercontent.com/images/1sVB9joZhgMgyzXdzJqp1QCVYQ.png)
In this part, we demonstrated how to define tables and constraints using BigQuery DDL, and how to visualize the resulting schema using the Datascale platform. This visualization describes the column-level relationships, which will be useful during the transformation of data.
What can we continue with this ER diagram?
In Part 3, we will build on the ER diagram from Part 2 to dive into the transformation of data by focusing on the various modeling layers: base, stage, intermediate, and fact/dimension tables.
We will demonstrate how data can be transformed into business insights and highlight the importance of ER diagrams in this process.