Data Engineering Foundations in Python/

...

/

Solution: BigQuery SQL

Solution: BigQuery SQL

Check out the solution to the previous challenge.

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 ...