Yes, You Can Edit Schemas In BigQuery–But Not How You Think

Creating Derivative Table Schemas In BigQuery SQL

I don’t keep data on the percentage of my pipelines, both personal and professional, that fail on their first deployment. But if I did, I have a pretty good guess what one of the biggest culprits would be: An incorrect schema.

From unreliable auto-detection to mismatched types to omitted fields, a schema is one of the easiest bits of the pipeline to create.

It’s also the easiest to mess up.

So, as a data engineer one of my (somewhat futile) quests has been to find ways to streamline schema design.

My latest trick is the ability to recover and edit a schema in BigQuery using the JSON sourced from a table’s metadata.

The goal is to write a simple query to end up with a JSON schema like the following that can be used to construct a new table using the BigQuery UI’s edit as text option in the create table window.

[{ 
  "name": "domain", 
  "type": "STRING", 
  "mode": "NULLABLE" 
}, { 
  "name": "num_comments", 
  "type": "FLOAT64", 
  "mode": "NULLABLE" 
}, { 
  "name": "score", 
  "type": "FLOAT64", 
  "mode": "NULLABLE" 
}, { 
  "name": "title", 
  "type": "STRING", 
  "mode": "NULLABLE" 
}, { 
  "name": "ups", 
  "type": "FLOAT64", 
  "mode": "NULLABLE" 
}, { 
  "name": "upvote_ratio", 
  "type": "FLOAT64", 
  "mode": "NULLABLE" 
}, { 
  "name": "dt_updated", 
  "type": "TIMESTAMP", 
  "mode": "NULLABLE" 
}]

I’ll show you how to generate a SQL query that follows the required naming conventions of BigQuery so we can copy/paste in the UI instead of having to manually create our schema–all in under 5 minutes.

Note that this is different from simply creating a copy of a table. When you copy a table you also copy all its records. Our goal is to only copy the schema so you can have a fresh start.

My hope is to spare you unnecessary brain power and keep your index finger strain-free.

Build Your Pipeline To A Data Engineering Career

You’ve reached the limit of the public preview. The full version of this post includes the implementation details: The code, the edge cases, and the "why" behind the architecture.

When you join PipelineToDE, you get:

• The DA → DE Pathway Course: A structured roadmap to bridge the gap between analysis and engineering.
• Weekly Senior Deep Dives: Fresh, tactical insights on Python, Cloud (GCP/AWS), and modern orchestration delivered every week.
• Production-Ready Blueprints: Access to 80+ protected stories and code repos from my time in the trenches as a Senior DE
• The DE Job Board (Coming Soon): Exclusive access to a curated board of high-agency Data Engineering roles.