Search⌘ K
Home
Courses
Data Engineering Foundations in Python

Solution: BigQuery SQL

Explore how to create tables, insert data, and design report tables in BigQuery SQL. Understand key data modeling concepts and practice querying for analytical insights like monthly sales and member segmentation.

We'll cover the following...

Solution

Let's check the solution to this challenge. Keep in mind that there isn't only one solution to this problem. We will go through a few alternatives.

Create source tables

Here is the standard way of using DDL to create tables in BigQuery. We can also create a table using many other programming languages, such as Python, Java, Ruby, etc. Read BigQuery documentation for more information.

Python 3.8
query_job = client.query(
    """
    CREATE OR REPLACE TABLE sql_challenge.product (
        product_id STRING,
        product_name STRING,
        unit_price FLOAT64,
        category STRING
    );
    CREATE OR REPLACE TABLE sql_challenge.customer (
        customer_id STRING,
        customer_name STRING,
        zipcode STRING,
        is_member BOOLEAN
    );
    CREATE OR REPLACE TABLE sql_challenge.transaction (
        transaction_id STRING,
        product_id STRING,
        customer_id STRING,
        date DATE,
        quantity FLOAT64
    );
    """
)
results = query_job.result()
print("product, customer and transaction table have been created.")

Insert data into tables

Here is the standard way of using DML to insert data into BigQuery tables. However, when we ...