Join multiply tables within one dataset

[Godson1]

Good Day Community,

Recently completed my Google Analytics certification in September. Working on my capstone project, my business task (How can we get casual riders to become subscription riders?) As a data analysts I'm trying to analyze how subscribers and casual users use the city bike sharing services differently. I'm using the 2019 data broken down into 4 tables representing each quarter. The combined dataset is around 4 million rows. After trying to combine this these tables in excel I realized (I'm a beginner) I needed to use Big Query SQL. I attempted to the join statement, it was processing, it timed out after 6 hours, this was my first time processing this statement, I realized it was large, but I thought Big Query handled large datasets, now I'm scratching my head again because I just want to join this data so it can be a analyzed, can someone please help me.

[ms4446 · Accepted solution]

Hi @Godson1 ,

Congratulations on your recent Google Analytics certification! When working with large datasets in BigQuery, such as your city bike sharing service data, it's essential to structure your queries efficiently to manage performance and costs effectively. Here are some refined strategies and a revised query example:

Table Aliases: Using aliases like t1, t2, t3, and t4 for your tables is a best practice for readability, especially when dealing with multiple tables in a query. It simplifies the SQL and makes it easier to understand and maintain.

WHERE Clause Optimization: The WHERE clause is used to filter the dataset to only include 'casual' customer types in the example provided. Consider adding additional filters to focus on specific time periods, geographic locations, or other relevant criteria for your analysis.

Testing with LIMIT: The LIMIT clause is invaluable for testing your query with a smaller subset of data. This can prevent unnecessary costs and save time during the development phase. However, remember to remove or adjust the LIMIT for the final query execution to analyze the full dataset.

Cost Management: BigQuery provides a cost estimator to forecast query costs, and you can set up cost controls to avoid unexpected charges. Always review the estimated costs before running large queries.

Query Validation: Use BigQuery's query validator to check for potential errors before executing the query. This can help identify issues that could lead to performance degradation or higher costs.

Explicit Column Selection: Instead of using SELECT *, specify the exact columns you need. This not only reduces the amount of data processed but also makes your query more predictable and easier to debug.

Revised Query Example:

-- Replace 'project.dataset.table_name' with your actual project and dataset names.
-- Replace 'ride_date' and other placeholder column names with the actual column names you need for your analysis.
SELECT
  t1.customer_id,
  t1.ride_date AS ride_date_q1,
  t2.ride_date AS ride_date_q2,
  t3.ride_date AS ride_date_q3,
  t4.ride_date AS ride_date_q4
  -- Include other relevant columns here
FROM
  `project.dataset.Q1_2019` AS t1
JOIN
  `project.dataset.Q2_2019` AS t2
ON
  t1.customer_id = t2.customer_id
JOIN
  `project.dataset.Q3_2019` AS t3
ON
  t1.customer_id = t3.customer_id
JOIN
  `project.dataset.Q4_2019` AS t4
ON
  t1.customer_id = t4.customer_id
WHERE
  t1.customer_type = 'casual'
  -- Add additional filters here if necessary, such as date ranges or geographic locations
-- For the final execution, ensure the LIMIT clause is removed or adjusted to include all relevant data
-- LIMIT 1000; -- Uncomment for testing purposes only

[Godson1]

Good insight, thank you

[vipulgcp13]

Hello @ms4446 ,

Great insight for query optimization!

However, I have one question regarding the use of joins and where clause in Bigquery.

In above example of revised query you have used joins first and then where clause.

Since we are only filtering on customer_type = 'casual' from table `project.dataset.Q1_2019`, which of the below approach is better for optimization in Bigquery:

1) Use below code - first using where clause to filter out result (here customer_type = 'casual') and then use joins on top of that.

FROM
(
SELECT * from `project.dataset.Q1_2019` AS t1
WHERE
t1.customer_type = 'casual'
)
JOIN
`project.dataset.Q2_2019` AS t2
ON
t1.customer_id = t2.customer_id
...

2) Use below code - joins followed by where clause as you have used in revised query example

FROM
`project.dataset.Q1_2019` AS t1
JOIN
`project.dataset.Q2_2019` AS t2
ON
t1.customer_id = t2.customer_id
...
WHERE
t1.customer_type = 'casual'

Your inputs are much appreciated! Thank you.

[ms4446]

In most cases, BigQuery's query optimizer is remarkably intelligent. With standard SQL, it will often rearrange your WHERE clauses and JOIN conditions automatically to find the most efficient execution plan. This means that, from a pure performance standpoint, both of your approaches might end up with the same execution time.

However, there are some key reasons why the second approach (joins followed by WHERE) is generally preferred:

1. Readability: It's more natural to think in terms of joining the broader datasets first and then filtering down to your specific criteria. This keeps the structure of the query clear.
2. Hypothetical Changes: If you later need to analyze subscribers too, using the second structure makes that modification trivial – just remove or adjust the WHERE clause.
3. Potential Indexing: If you have an index on the customer_id column, then joins will likely benefit from this index even before filtering by customer_type.

When Filtering Before the Join Might Be Better

There are niche situations where filtering early (like your first approach) could be beneficial:

• Filtering on a Partitioned Column: If one of your tables is partitioned by date and you're only interested in a specific date range, filtering on that date column within a subquery before the join could significantly speed things up.
• Massively Reducing Data Size: If the filter in your first approach eliminates a huge percentage of the data from the initial table, then the joins will naturally be faster due to the smaller intermediate results.

Recommendation

Start with the second approach (joins followed by WHERE). This is the standard best practice. If you encounter performance issues, then consider these points:

• Look at the Query Plan: BigQuery can explain its query execution plan. If the plan shows that filtering earlier might make a big difference, revisit your strategy.
• Experiment: Sometimes the best way to know for sure is to benchmark both approaches with your actual dataset.

[vipulgcp13]

Great, thanks @ms4446