BigQuery - Challenges & Best Practices

As a cloud computing tool, BigQuery is not without its challenges. In this brief chapter, we have tried to highlight some of the noticeable challenges that BigQuery is facing.

BigQuery from Business Perspective

From a business perspective, one of the biggest hurdles regarding BigQuery is securing leadership buy-in to either test, adapt or scale the platform to meet a given organization's needs.

• Many businesses are content with relying upon on-premise data storage and don't consider cloud storage options.
• Or, worse, a business may not even consider data warehousing in any form to be a viable solution to their big data storage.
• Business leaders may see BigQuery's variable costs as a potential resource drain, especially if their organization has many developers, engineers, architects and end users relying on data stored and queried from BigQuery.

BigQuery from User Perspective

From a user's perspective, BigQuery has a bit of a learning curve.

• The dual versions of SQL available, standard SQL and legacy SQL, mean that those who have worked with other SQL dialects may be confused which mode they need to enable to run a given query or use a particular function.
• Developing with BigQuery can be challenging when trying to integrate a BigQuery-based data warehouse with an external connection like a Google Sheet or the BigQuery API.
• Despite providing visibility into resources consumed and other performance metrics at and after execution time, BigQuery's error logs can be vague, leading to frustration when troubleshootingespecially for newer developers.

BigQuery Best Practices

To avoid or surmount these challenges, it is necessary to understand and implement BigQuery best practices. Getting the most out of BigQuery requires the knowledge of Google Cloud Platform, cloud computing, and SQL in general.

To reduce the possibility of receiving unexpected, costly monthly bills, enable monitoring and frequently review the billing and usage dashboards, filtered on BigQuery.

Instead of continually increasing the slots on an ad hoc basis, compel developers to think about and implement best practices at a code level. This may include −

• Reducing the scope of data processed by avoiding broad queries like "SELECT *"
• Choosing efficient SQL query design patterns to optimize query operation
• Avoid compute-intensive queries like those that utilize wildcard references and excessive metadata reads
• Use available tools like BigQuery's BI Engine to identify problematic operations and provide an increased performance lift if necessary
• Specify query restraints for large tables by only allowing users to query with a WHERE clause

Users armed with both platform and SQL knowledge will be the ones to construct, expand and evangelize the platform, adding the power of BigQuery to their individual skillset and organizational tech stack.