PAD Cost Reduction and Efficiency Tips

PAD leverages BigQuery, which is a highly cost-efficient platform for analyzing large (“big”) amounts of data. In addition, there are steps you can take to optimize its efficiency further. Below are some tips:

• Avoid SELECT *: Using SELECT * retrieves all columns from a table, which can lead to scanning unnecessary data and incurring higher costs. Instead, specify only the columns you need. This practice not only reduces the amount of data processed but also improves query performance, helping you maintain better control over your expenses.
  
  
• Avoid LIMIT Statements: PAD differs from other databases in that it scans the entire dataset even when a LIMIT statement is used. While LIMIT can show you a subset of results, it doesn’t help with cost control. It’s better to avoid LIMIT statements on large datasets for cost management.
  
  
• Check the Green Check Mark: In the PAD SQL console, a green check mark (✅) appears before running a query. Hovering over this mark will show an estimate of the data to be processed. It roughly costs $5 per terabyte (TB) of data processed, but this can vary depending on the size and complexity of your data. Referencing the check mark and the amount of data it will process can help you gauge costs.
  
  
• Optimize your queries: To minimize the amount of data being processed in PAD, you should try to write efficient queries where possible. Make sure to use the WHERE clause to filter out unnecessary data. Also, be aware that JOINs and GROUP BYs can cause compute time and cost to increase exponentially and should be used carefully when working with large and complex datasets.
  
  
• Use partitioning and clustering: If you are working with large datasets in PAD (i.e. those that contain several terabytes or millions of rows), consider partitioning your tables by date or another logical grouping. This can significantly reduce the amount of data that needs to be scanned in each query. Clustering is another technique to reduce the amount of data that needs to be processed. It involves organizing the data in a table based on one or more columns to improve query performance. You can read more about partitioning and clustering here.
  
  
• Use smaller tables: If you have a large table, consider breaking it up into smaller tables. This can help reduce query costs by minimizing the amount of data scanned.

FAQ:

• What happens if I run an inefficient workflow or am close to my billing limit?
  
  No worries, we've got you covered! At CTA, we’ve set up internal systems to track and alert us to any heavy or unusual usage from a PAD account. If our team detects any unusual activity, we'll reach out to you to discuss the inefficiencies and provide recommendations to avoid them in the future. In addition, our team has developed similar tools for billing in your PAD project. If you're nearing your allotted monthly usage in PAD, we'll get in touch with you to discuss the next steps and ensure that your work is not interrupted. If you have any questions about your billing and monthly usage limits, please contact help@techallies.org. 

Have questions? Please contact help@techallies.org.