Extract. Transform. Read.

A newsletter from Pipeline: Your Data Engineering Resource​

Presented by Basejump AI

Use natural language prompts to chat with your database in Basejump’s intuitive interface, or embed it directly in your application. Book your demo here.

Hi past, present or future data professional!

One thing that makes my work day easier is when I’m Google-ing (as all software developers do) a problem and I come across the holy grail of solutions: A one-line implementation.

Like anything, however, a one-liner that is too complex can become a bad thing. Think: Chained Pandas expressions that become unreadable. Or cramming a multi-line query inside of a BigQuery client method.

My favorite one line (at least in recent memory) is a clause used with SQL’s ALTER TABLE statement: RENAME TO. You may find renaming a table as compelling as schema creation. But this simple clause can be especially useful in lieu of a more dangerous phrase: CREATE OR REPLACE.

The RENAME command allows you to rename a table without having to completely recreate its contents–and risk a SQL statement failing and losing some or all of your data.

Specifically, I use RENAME TO when I want to convert a copy table with some change, like an updated schema, to a production table. I do so using these steps:

• Create/backfill a staging table I’ll ultimately convert to prod
• Use ALTER TABLE `dataset.production_table` RENAME TO `production_table_original`
• Use ALTER TABLE `dataset.staging_table` RENAME TO `production_table`
• Double-check all partitions, clustering specifications and metadata descriptions are identical between the tables

The best part is that this is a true one-liner. No chains–or headaches–involved.

To save you a headache, here are this week’s links:

• ​How Data Engineers Can Google Better (Yeah, Seriously)​
• ​How To Design A BigQuery SQL Schema​
• ​Backfill Your SQL Tables Without Breakage Before Anyone Finds Out You Were Wrong​

If you want to read more about this method, I cover the process in more detail here.

Questions? You know where to find me: zach@pipelinetode.com.

Until next time–thanks for ingesting,

-Zach Quinn