Busting 12 myths about BigQuery

Not long ago, Forrester Research named Google Cloud the leader in their report, The Forrester Wave™: Insight Platforms-As-A-Service, Q3 2017 — and we couldn’t have been more proud. Without question, Google BigQuery has played a large role in this achievement.

Every day, we work with a wide variety of customers, from rising startups to large enterprises, who use Google BigQuery to address their data warehousing and analytics challenges. Our customers frequently tell us they love its functionality and flexibility. But along the way, we’ve also come across a few myths about BigQuery. Sometimes these myths are based on experience with very early versions of BigQuery, but often they’re due to misconceptions about BigQuery’s architecture. In this blog post, we present a few truths to help bust 12 of BigQuery’s most common myths.

The topics discussed in this post are universally applicable, but some were originated by our enterprise customers. Aspects such as query prioritization, complex project topology, automated project administration, and fine-grained access control are essential to enterprises, but may not be immediately apparent in product documentation.

Truth 1: With BigQuery, you can achieve near real-time analysis.

When it comes to BigQuery, it’s true that quota policies limit free load jobs, but you can stream data into BigQuery. By default, quota policies enforce some limits on streaming inserts, but when you have a use case that requires it, your quota can be increased. If you work with a Google Cloud representative, reach out to find out how.

Furthermore, if your data is stored in Cloud Bigtable, Cloud Storage, or Google Drive, you can analyze it via BigQuery without storing the data in BigQuery. Refer to truth 3 for more details.

Truth 2: BigQuery supports more than SQL.

Truth 3: BigQuery can analyze data external to its storage.

This article discusses how the above-mentioned feature can be used to make a “Silver” grade of data available to Data Analysts and Data Engineers. “Silver” grade compares to “Gold” grade, which refers to data that natively resides in BigQuery. While data outside the data warehouse is of higher velocity and variety, quality and interactivity are somewhat sacrificed. The “Silver” grade data is ideal for Data Analysts who need to perform custom studies involving data that is yet to make it into the data warehouse. Also, Data Engineers commonly use this type of data when designing ETL jobs, in order to expand the variety of data in the warehouse.

Truth 4: With BigQuery, you can easily retrieve a point-in-time snapshot of your data.

Note that when a table is explicitly deleted, its history is flushed after two days.

Truth 5: BigQuery supports query prioritization.

You might simply prefer a fixed monthly bill, or encounter workloads that are extremely sensitive to query latency, and thus have predictability and control requirements that cannot be met by the on-demand service. For such situations, you can use the flat-rate service. In this model, a certain number of slots are dedicated to your project(s), and you can establish a hierarchical priority model amongst the projects. The flat-rate model is especially suitable for large enterprises with multiple business units and workloads with varying priorities and budgets. For instance, the arrangement illustrated below gives priority to queries that are issued from the “Dashboarding” project over the queries from the other two projects. But even with prioritization, slots won’t be wasted. If the prioritized “Dashboarding” project does not use all its dedicated slots, they’ll be distributed among the remaining projects. Even data stored in the “Data Science” project can be queried from the “Dashboarding” project with a higher priority than when it’s queried from within the “Data Science” project itself.

Truth 6: BigQuery project topology can mimic your organizational model.    

The following diagram illustrates IAM’s intrinsic hierarchy. You can grant permissions to roles at the organization, folder, project, or dataset level. Permissions are inherited down the hierarchy. This blog post details how you can map your organization with Google Cloud Platform (GCP) resource hierarchy.

If you’re dealing with onboarding/offboarding a large number of users, you shouldn’t assign roles at the user level. Instead, you should take advantage of nested groups of users. These groups can represent different entities within your organization, such as departments, workflows, campaigns, studies, etc., or simply be named after projects. Some of the typical naming conventions for groups are: gcp-{team}-{role} or gcp-{project}-{role}.

The number of GCP projects, user groups, and their level of access to each project depends on many factors within your organization. While it’s impractical to lay out every possible topology, the remainder of this section presents some of the common approaches that you can use as-is, in combination or as a starting point to build what suits you.

The following arrangement is based on data lifecycle, where:

• Users of Data Engineer group only work within the PRE_PROD project, where they build and test ETL pipelines.
• In PROD environment, only service accounts perform ETL processes and produce “clean” data for Data Analysts to publish visualizations and derivative datasets for broader usage and Data Scientists to train new machine learning models.  
• Functional users such as Marketing, Product Management, etc., use data visualizations and derivative datasets created by Data Analysts.

The topology below places data—and the users of that data—into different projects. This arrangement could be employed to address different requirements, most commonly to establish separate query prioritization and budgets among teams, or to separate other GCP resources from the data warehouse.

In this arrangement, users of the Data Engineer group manage a single project per environment, which stores all of the data. Users of the Data Analysis and Data Science groups build reports and machine learning models in projects separate from the data lake.

In organizations with strict data security requirements, users do not have access to all the data, but rather a subset, based on different security domains. Using datasets, you can separate sensitive data from data that should be commonly available, and control fine-grained access at the dataset level. In the example below, Data Analysts and Data Scientists both have access to the Common and Sales dataset, but only Data Scientists are able to access Employee data.

Expanding on the least-privilege principle, you may also consider splitting your ETL processing into an additional project. This might be necessary when separate teams are responsible for the management of data pipelines and data storage. The separation of concerns also aids in easy cost tracking since billing data can be tallied by project.

Truth 7: BigQuery project configuration management can be automated.

Truth 8: BigQuery supports normalized schemas and JOINs.

As illustrated below, the majority of normalized data structures naturally map to nested and repeated rows in BigQuery. This will simplify loading data from Avro and JSON files. You can access these fields using intuitive dot notation, for example: cityLived[0].name.

When the relationship between two tables cannot be represented using nested and repeated rows, we find that a traditional normalized schema works best more often than you might think. Normalization typically works better when a related table is smaller than 1 TB or undergoes frequent updates and deletes. For more information about schema design, take a look at this solution.

Truth 9: BigQuery supports fine-grained access control down to row and column level.

Truth 10: BigQuery is widely integrated, both with GCP and third-party products.  

To connect your custom application to BigQuery, you can use client libraries, which are available for most common programming languages, or use BigQuery's REST API directly.

To connect to BigQuery from a legacy application or an application that cannot be easily modified, you can use BigQuery JDBC and ODBC drivers.

Truth 11: You can control the encryption keys.

Truth 12: You can monitor and audit BigQuery usage.

BigQuery automatically creates audit logs of user actions. You can export audit logs to another BigQuery dataset in batch or as a data stream, then use your preferred tool to visualize the logs. For details, see Analyzing Audit Logs Using BigQuery. The section called “Analyzing BigQuery Usage” in this article shows how you can visualize BigQuery audit logs to answer billing questions. You can expand on this technique and extract different kinds of insights from audit logs.

Next steps