Quickly create BigQuery demo/dev environments using SQL

My colleague Polong Lin did a brilliant thing recently. He wanted to do a demo, and rather than ask us to follow a checklist of instructions, he simply had us make a single BigQuery call:

'MY-PROJECT-NAME', 'demods')

This call provisioned everything in our BigQuery project that we needed for the demo.

Try it!

You will see that a dataset named demods will get created in your project, and the dataset will contain stuff that is needed for the demo:

How does this magic work?

Stored Procedure with a Script

The idea is to create a Stored Procedure that contains a script. The script creates the dataset, and makes other SQL calls. In this case, I’m using EXECUTE IMMEDIATE to create the query dynamically:

CREATE OR REPLACE PROCEDURE publicdata.setup_flights_demo (
projectname STRING, datasetname STRING)
""", projectname, datasetname);

CREATE OR REPLACE TABLE `%s`.%s.num_flights_by_airport AS
airline, departure_airport, COUNT(*) AS num_flights
GROUP BY 1, 2;
""", projectname, datasetname);

The first query in the script creates the dataset:


and the second query creates a table and populates it with data by selecting the necessary rows:


Obviously, this trick is useful beyond creating demo environments. You can also schedule the CALL, although a Materialized View/Table is a better solution for that.

It’s fun to work with brilliant people! Enjoy!




Operating Executive at a technology investment firm; articles are personal observations and not investment advice.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Do a Simple Web Scrapping with Python — Part 5: Second step

Plug your existing domain models into NServiceBus Sagas

Vulcan Forged to integrate Binance Smart Chain (BSC)

Where does Blockchain fit into the web stack?

FreeCourseSite — Download All Paid Courses For Free Now

Passing Crystal callbacks to C structs with safe user interfaces

Road to Game Dev: Animating Sprites in Unity

Building Advance Blog on Laravel 5.5 with TDD mode #5 User can create Post

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Lak Lakshmanan

Lak Lakshmanan

Operating Executive at a technology investment firm; articles are personal observations and not investment advice.

More from Medium

Event driven workflows in BigQuery +Cloud function

Working with JSON data in BigQuery

How to use Backfill: the Time Machine for Scheduled Queries in BigQuery

BigQuery: Soundex function and UTMs reducing hardcoding on spelling errors