How to Maintain Column Descriptions in Google BigQuery's WRITE_TRUNCATE Scheduled Queries

Описание: Discover how to keep your column descriptions intact during nightly scheduled queries in Google BigQuery. Explore solutions to avoid losing metadata like descriptions with actionable steps.
---
This video is based on the question https://stackoverflow.com/q/63851993/ asked by the user 'Jason Stinnett' ( https://stackoverflow.com/u/13635502/ ) and on the answer https://stackoverflow.com/a/63853968/ provided by the user 'Yun Zhang' ( https://stackoverflow.com/u/11206202/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Maintain / Set Column Descriptions for Write Truncate Scheduled Query

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Maintain Column Descriptions in Google BigQuery's WRITE_TRUNCATE Scheduled Queries

Managing data in Google BigQuery can be quite the challenge, especially when it comes to scheduled queries that use WRITE_TRUNCATE mode. While this mode allows you to overwrite existing data with new data, it unfortunately comes with the drawback of stripping away important metadata, such as column descriptions. If you’ve found yourself in a situation where your column descriptions are being deleted nightly, don’t worry! This guide will guide you through some effective solutions to keep your metadata safe and intact.

The Scenario

In many organizations, scheduled queries are restored or refreshed nightly to keep the data current. These queries are commonly configured in BigQuery to use the WRITE_TRUNCATE option. However, when using this method, users often notice that while the table's overall description remains, any descriptions associated with individual columns are lost in the process. This can pose significant problems, as detailed descriptions are crucial for understanding the context and purpose of each column in your dataset.

The Problem

You’re not alone in facing this issue. As you've discovered, there's no direct method to insert column descriptions into scheduled queries configured to WRITE_TRUNCATE. After running a scheduled query, you’ll notice:

Column Descriptions Are Lost: Despite having made efforts to document your schema, the automated process erases your descriptions, making it harder for users to interpret the data accurately.

No Clear Documentation: Attempts to find guidance on how to preserve column descriptions in BigQuery scheduled queries from Google’s resources or forums like Stack Overflow may not yield conclusive results.

This expands the question: Is it possible to keep column descriptions through a scheduled query in Google BigQuery, or is this simply a limitation we have to accept?

The Solution

Fortunately, there are several workarounds you can employ to maintain column descriptions even when using regular scheduled queries. Below are the most notable techniques:

1. Use CREATE OR REPLACE TABLE

Instead of employing the WRITE_TRUNCATE directive, one effective method is utilizing the CREATE OR REPLACE TABLE syntax with a description for each column. The syntax would look like this:

[[See Video to Reveal this Text or Code Snippet]]

This approach allows you to effectively redefine your table, including retaining column descriptions every time you run your scheduled query.

2. Use INSERT instead of WRITE_TRUNCATE

If you'd prefer not to repeat descriptions in every scheduled query, you can modify your queries to perform a DELETE followed by an INSERT. For example, you can execute:

[[See Video to Reveal this Text or Code Snippet]]

This way, the table structure, including column descriptions, remains unchanged while the data is updated.

3. Consider Using a MERGE Statement

For organizations that require atomic updates (where either all changes are made, or none at all), you might choose to encapsulate everything with a MERGE statement. Here’s what this could look like:

[[See Video to Reveal this Text or Code Snippet]]

This statement allows for a clean execution in a single transaction while preserving all your metadata and data integrity throughout the updates.

Conclusion

Dealing with metadata can be tricky, but these methods ensure that your column descriptions stay intact during scheduled queries in BigQuery. Using CREATE OR REPLACE TABLE, employing a DELETE-INSERT sequence, or leveraging MERGE statements are all effective techniques to navigate this common issue. It’s essential t