Wayfair Writes its Success Story with BigQuery for Internal Analytics
4922
Of your peers have already read this article.
4:00 Minutes
The most insightful time you'll spend today!
Editor’s note: Home-goods and furniture giant Wayfair first partnered up with Google Cloud to transform and scale its storefront—work that proved its value many times over during the unprecedented surge in ecommerce traffic during 2020. Today, we hear how BigQuery’s performance and cost optimization have transformed the company’s internal analytics to create an environment of “yes”.
At Wayfair, we have several unique challenges relating to the scale of our product catalog, our global delivery network, and our position in a multi-sided marketplace that supports both our customers and suppliers. To give you a sense of our scale we have a team of more than 3,000 engineers with tens of millions of customers. We supply more than 20 Million items using more than 16,000 supplier partners.
Serving those constituents generates a lot of analytics work. Wayfair runs over a billion ‘analytic’ database queries a year, from both humans and systems, against multi-petabyte datasets. Scaling our previous environment to meet these challenges required us to maintain dozens of copies of data for different use cases and manage complex data synchronization routines to move trillions of records each day, resulting in long development times and high support costs for our analytics projects.
Centralizing our data using Cloud Storage and BigQuery enabled us to break down existing silos and build unified pipelines for our batch and real-time operations. BigQuery shines by letting us decouple our compute and storage resources and flexibly ingest structured data in streaming and batch modes. We also benefit from the same decoupling for more complicated machine learning (ML) workflows in Dataproc and Cloud Storage. In particular, BigQuery is extremely low maintenance. From ML and easy data integrations to the integrated query cache, many out-of-the-box features have proven valuable for multiple use cases.
Beyond storage and compute, Google Cloud offers multiple tools to maximize the value of our data. For example, we can easily connect Data Studio to BigQuery for lightning-fast dashboarding of enterprise KPI reporting. When we need to dive deeper into a metric, Looker provides an interface and semantic model that empowers more business users to answer important questions — without understanding SQL or our vast dataset and table structure.
The combination of all of Google Cloud’s tools enables every Wayfairian the opportunity to self-serve their data visualization and analysis needs. We are able to support all of the requirements of our internal and external users, from descriptive analytics to alerting and ML, in a platform that blends Google’s proprietary technology with open-source standards.we have seen a greater than 90% reduction in the number of analytic queries in production that take more than one minute to run
As a result, we have seen a greater than 90% reduction in the number of analytic queries in production that take more than one minute to run, which delights our users and increases adoption of our analytics tools across the business.
Higher performance means saying “Yes” more often
We went into the BigQuery experience expecting very strong performance on large, aggregate queries and excellent scalability — the kind of performance that could enable consistent 5-15 minute processing queries and 5-10 second response times for large analytics queries.
We weren’t disappointed — we saw slightly better than linear performance profiles as record counts went up from millions to trillions for a flat resource allocation. BigQuery had the expected high performance for aggregate queries. This made our large data processing pipelines predictable and straightforward to maintain and optimize.
Below is a plot of gigabytes processed by a query against query run time in seconds; outliers are filtered and the axes are pruned to ranges where we had a large sample size. This focuses on longer-duration queries. As an example of the linear performance, a 2 terabyte query averages 5 minutes — 2.5min/terabyte — and an 18 terabyte query (9 times as much data) averages 25s — only 1.4min/terabyte. This is despite the fact that complex queries [sorting and analytic functions are particularly problematic] are more common with large data sizes. We do see increasing variability at large sizes as slot limits and other resource constraints come into play, but these factors are controllable and ultimately a business optimization decision.
Though individual values vary greatly depending on query complexity [analytic functions/sorting can be computationally expensive], the overall trend line is below 1-1 for bytes versus duration, meaning our BigQuery query runtimes are increasing slower than our data volumes. We are still seeing sub 1:1 threshold runtimes, and we’re already at 100 PB of total data with single queries running on 50PB or more. Going strong!
One unexpected and pleasant surprise has been high performance for smaller queries, such as those required for development, interactive analytics, and internal applications we use for forecasting, segmentation, and other latency-sensitive data workflows. BigQuery’s resource management systems have provided smoother degradation and parallelism profiles than we initially expected for a multi-tenant platform.
We regularly see sub-second p50 query performance for interactive use cases, with p90 performance coming in under 10 seconds for Looker, Data Studio, and AtScale — meeting the threshold we set to avoid analysts losing focus during a business investigation. The chart below shows p50, p90, and p95 timing for core workloads, as well as the number of queries in each bucket and the number of unique users. [Looker and Atscale will use small numbers of service accounts for a large number of users].
This has enabled two important shifts in the way our customers experience analytics services:
- Delivering better user productivity. People no longer have long wait times to get results for a basic query delivering an improved overall user experience.
- Scaling our data transformation. BigQuery has proven, effective data transformation that allows us to embrace the industry shift away towards ELT (extract, load, transform) to transform raw data right within the database.
We no longer have to ask ourselves, “Can we meet the SLA for this data processing task?” The new conversation is, “Of course, we can meet it. What’s the associated cost based on how many resources we assign?” We can make cost and resource trade-offs clear to our users and say “yes” more often to requirements that have measurable business value.
Supporting cost vs. performance decisions
Democratized access to internal analytics is a powerful decision-making tool. With BigQuery’s robust tooling, the decision about how to best allocate computational resources is in the hands of the analytics groups closest to the business decision being made. If they want to spend a sprint optimizing a dashboard, they can show exactly how much money it will save. If they choose not to optimize a report, they can offset the decision with concrete cost savings and other realized value.
Wayfair teams also benefit from visibility into their committed costs by abstracting away the details of the underlying services, including BigQuery slots, the virtual CPUs used to execute SQL queries.
Take a look at this example view consumed by our teams (the metrics for these dashboards are from the BigQuery INFORMATION_SCHEMA (jobs and reservations tables):
Suppose an organization wants to purchase capacity for a period of time. In that case, we provide an average cost for a slot during that time and charge them based on their slot usage for queries, multiplied by the average price.
Teams can see whether their consumption matches their reservations with a consistent, high-level number. We apply the same approach to tools like Dataproc by aggregating up the costs of component services and providing an organizational cost for those services.
How we use performance signals to improve user efficiency
Instead of overcorrecting for occasional performance fluctuations by allocating excessive resources, we try to align incentives so that people make the right choices for the business. Showback doesn’t work in a vacuum — it requires context and integrated insights. Teams need to show the cost of running a dashboard as well as the ROI is in terms of end user engagement and what peer dashboards with equivalent source datasets might cost.
We want to give users the freedom to do things the way they want and need to while also educating them on achieving their goals and using best practices to reduce their costs and improve their performance. The goal is never for all dashboards to load “less than x seconds” — we believe that any dashboard can load in x seconds when it is properly designed and resourced.
For instance, BigQuery allocates slots for a new workload quickly and efficiently, and queries that are optimized and have dedicated resources consistently complete quickly. When users experience a slowdown, it’s typically because they are sharing resources or inefficiently using their resources. Variable performance is a useful signal to help users discover opportunities to optimize queries and work more productively. We value these signals as we like to focus on the aggregate experience of all users interacting with the tool, not individual interaction.
A natural way to improve user efficiency is to reward ideal behaviors and discourage those that do not align with our analytics strategy. Some examples include splitting out high-demand reporting from ad-hoc exploration to reduce concurrency errors or encouraging people to use materialized tables instead of writing complex custom SQL. For instance, a dedicated reporting project might only include an organization’s Data Studio dashboard if it directly references a table or meets other optimization requirements. Using this approach, we give our users a high degree of freedom and experimentation at the entry-level while providing a well-documented path to scale.
Visibility into query performance improves business performance
The detailed tracking information we get from Google Cloud products is critical — Google’s robust analytics performance has enabled us to scale Data Studio to over 15,000 Wayfarians. Of course, a larger user base always comes with concerns that less sophisticated users may not appropriately optimize their queries or make the best use of the infrastructure.
Evaluating the different Data Studio dashboards helps us identify opportunities to use better query optimization practices, such as reducing custom queries, connecting directly to permanent tables, or using the BI Engine to improve dashboard performance. Recently, our team built new billing projects specific for Data Studio reporting. We can scale up a reservation dedicated to reporting about a major sales event in minutes.
Beyond everyday reporting, holidays and sales events may require higher, guaranteed performance. BigQuery’s flexible capacity commitments allow us to decide at a business level whether an urgent need requires us to reprioritize resources immediately or whether we have the time to optimize queries and rebuild dashboards to be performant. These new billing projects let us evaluate if teams need to scale up resourcing, what areas they can optimize in BigQuery, or if a new reservation is needed to resource that team separately.
Recommendations for achieving high performance at low cost
Are you wondering how to allocate BigQuery slots? Teams must see direct, immediate return on their investment in performance and optimization.
Our first rule of thumb is to align resource utilization with ownership as closely as possible. This makes it easier for teams to secure dedicated resources, even if they start out small. We’re comfortable doing this because BigQuery dynamically reassigns capacity between the targeted reservation and other consumers in milliseconds. We always get the total usage of our slots — there is no wasted capacity even when we break assignments down granularly.
Once a team has a reservation, we provide reporting that consumes various BigQuery information schema views, such as query logs and reservation information, and processes it into aggregated reporting. We look for metrics like the average slots available to a query at a point in time, overall query performance, and how long queries took to execute to pinpoint the overall health of a reservation against our business objectives. Flex slots also let us experiment easily with additional capacity, and BigQuery’s performance is so consistent that we can use straightforward models to estimate return on additional slot investment.
Other key cost optimization tips we have found helpful at Wayfair include:
- Optimize for worst-case performance. Given BigQuery’s ability to share slots, we expect teams will often see much better performance, but we find that thinking it’s more effective when teams think about the most pessimistic case when resourcing their jobs.
- Take advantage of BI Engine reservations. We like to think critically about the data and workload. BI Engine reservations are excellent for workloads that query lots of small dimension tables or a few larger tables loaded into memory. In cases like that, such as with OLAP tools using pre-computed aggregates, we’ve seen BI engine investments drive a 25% reduction in average query time—excellent ROI.
- Leverage Looker for large, longitudinal datasets. Since Looker supports a wide range of use cases, we see a broader range of performance — p50 is still under a second, but p95 can get as high as a minute. For these cases, we recommend modeling user patterns [first of month, first day of week] and dynamically allocating more slots for these periods while deprioritizing batch workloads in the key windows.
- Lean in heavily on the BQ cache for broad-based reporting. To avoid a Monday morning reporting lag, We use the BigQuery cache to avoid Monday morning report loading lags. Up to 30% of our Data Studio queries hit the BQ cache, ensuring our Data Studio p95 consistently stays under 10 seconds.
- Use slots-per-job to improve experience. In our reporting, we find that the most valuable view is slots-per-job. We look for dips in slots available for jobs as this situation often correlates strongly with poor user experiences. For example, our reporting showed us that Looker has the highest volumes of queries and users on Mondays and during each month’s first two business days. We used this information to scale up slots to support these periods of highest demand, delivering on performance SLAs to support business users.
- Get a global picture of usage. We manage slots in a central tool. Teams input their requirements into a calendar to indicate when they want higher or lower capacity to inform our flex slot purchases and annual commitments. It might seem like dozens of relatively inconsistent workloads at the team level, but the picture changes when you look at demand across a week (or our entire business). Understanding global usage makes it easy for us to model baseline capacity recommendations for serving year slots versus flexible demands.
Reaping the rewards of BigQuery
From our first initial undertaking with Google Cloud to transform our storefront, we had a lot of confidence in the underlying technology offerings and the teams that ran them and their ability to meet our unique requirements. But our experience using BigQuery for internal analytics at Wayfair has exceeded our already high expectations.
BigQuery’s fast, dynamic allocation of resources and ability to transform data in place are important to our mission of maintaining consistently high performance for our users while closely managing costs. Moreover, our visibility into query and dashboard performance through BigQuery’s DSP views and other performance metrics have helped us make clearer connections between performance goals and the costs required to meet them — and guide our analysts across the company towards attaining high performance on every project.
Groww’s Google Cloud-Powered Platform: The Key to Secure and Successful Investing
2921
Of your peers have already read this article.
4:00 Minutes
The most insightful time you'll spend today!
Groww makes investments simple and accessible, using Google Kubernetes Engine to ensure a reliable platform for customers, and makes data-backed decisions to grow its business with BigQuery.
About Groww
Headquartered in Bangalore, Groww is India’s fast-growing online investment platform that offers a simple and easy way to invest in stocks, direct mutual funds, IPOs, ETFs, and digital gold. Its mission is to make investing as intuitive and accessible as ecommerce.
Google Cloud results
- Reduces hardware costs with Preemptable Virtual Machines
- Enables a lean DevOps team with Google Cloud
- Analyzes data effectively and quickly for agile business growth
Investing is one way to ensure financial security. However, the thought of it can be a daunting one, especially for people without any prior experience. With a mission to make investment simple for digital natives in India, Groww was launched in 2016.
“We noticed that many people were on social media, booking cabs, and ordering food online, but the same people were not investing, despite having the means to do so,” says Singh. This observation led to a lightbulb moment for the team, and they realized that in order to appeal to the millennial, mobile-savvy generation, they had to create an investment platform that was as easy to use as an ecommerce platform.
Managing unpredictable spikes with Google Kubernetes Engine
As with any platform, there are bound to be peak and non-peak hours when it comes to traffic. For Groww, regular spikes take place in the early mornings, or in the evenings when people are more relaxed having come home from work. But the nature of the fintech industry is a volatile one. Investors are only human, and their investment decisions can be swayed quickly by the news. As such, spikes in traffic can happen at the most unpredictable times. To cope with this unpredictability, Groww uses Google Kubernetes Engine to scale up and down automatically to meet the required capacity around the clock. This also helps the company save costs, as it pays only for what is needed.
“No matter how much of an expert you are, you can never predict when traffic on the platform will be heavy,” says Singh. “Google Kubernetes Engine helps ensure that we never run out of capacity, without overspending on infrastructure cost.”
More recently, the investment company started using Preemptible Virtual Machines, which run at one third of its hardware cost. It also leverages Anthos to monitor and manage its backend infrastructure and to have better workload visibility. Singh shares, “We are very open to adopting new technologies, and our team is always eager to learn if we can do things better. We believe that technology is always evolving and it’s our responsibility to learn and make use of what’s available out there.”
Despite having so much running in the background, Singh explains that the company keeps a very lean DevOps team. “We’ve only got four or five people in DevOps, and that’s only possible because Google Cloud products are already able to run on their own.”
Making swift, data-backed business decisions
Infrastructure is only part of the equation for a successful business. Outside of operations, the ability to analyze data effectively is arguably the most important component for a startup to thrive. Groww leverages BigQuery to make decisions quickly and efficiently. “With BigQuery, we have a place where we can put all data, fire queries, and build dashboards almost instantly, allowing us to make business decisions quickly,” explains Singh.
The team also uses Looker Studio to clearly visualize the information generated through charts and graphs. The best part? Groww doesn’t need to spend additional time and resources setting up a large data team, since BigQuery does most of the work and in a shorter period of time. The resources saved also enables the team to focus on addressing functional requirements, rather than managing and sizing the data platform.
“From a startup perspective, BigQuery is really helpful because often setting up your own data lake can be very costly, and a distraction when the team is also busy focusing on setting up an infrastructure,” adds Singh.
Ensuring security and compliance with Google Cloud
As a fintech company, security and compliance continue to be top priorities for Groww. It chose Google Cloud as its preferred cloud provider because there are three data center replication zones in Mumbai, which means it adheres to financial regulations for keeping its user data within borders.
Moving forward, Groww plans to evolve its platform alongside its users. Singh says, “As we gain more users with different wants and needs, it will be a natural progression that the company evolves. I believe that with Google Cloud, we are well equipped to pave the way to the future.”
Creating and Using Storage Buckets for Your Data Needs
1381
Of your peers have already read this article.
2:30 Minutes
The most insightful time you'll spend today!
So, you want to store objects on the cloud? But you’re really new to Google Cloud or Cloud Storage and would like someone to walk you through the process step by step?
Today is your lucky day!
I will help you understand the big steps involved in setting up and using Cloud Storage. For a walkthrough of all the steps involved that even includes highlighting of things to select so you don’t spend all your time playing “Where’s Waldo” on the page looking for that one button, click try the tutorial!
Creating buckets, storing objects in them, and adjusting permissions may feel challenging at first, but after you’ve walked through these steps, you’ll be able to create and use the Cloud Storage your project needs.
Resource Hierarchy
Projects contain all of the related parts of your application. Inside projects, you may have buckets, the top level containers in Cloud Storage. Files and folders are stored in buckets. You can grant access to a bucket, folder, or file using Identity and Access Management.
You can do all of that in this interactive tutorial. So let’s get started. I suggest you follow along in the tutorial so you can see the specific keys and screens as I describe them.
Creating a project
Everything in your application will be in a project, even if the only service you are using is storage. Projects need to be associated with a billing account, so if you don’t have one, consider signing up for the Free Trial. You’ll start in the Cloud Console.
Creating a bucket
Once you have a project, you can use storage by navigating to the Cloud Storage page by using either the Navigation menu at the top left of the console or by searching for “Cloud Storage” using the search box at the top of the console.
At the Buckets page of Cloud Storage, you’ll be able to create a bucket. The name of your bucket needs to be globally unique; that is no other bucket in Google Cloud Storage can have the same name. This is because if you or your organization allow your bucket to be accessible on the Internet, it will be at the URL https://storage.googleapis.com/<bucket name>. You can also consider implementing a security feature to prevent data exfiltration, organization restriction headers when you’re finished creating your bucket.. Pick a region close to you for your bucket. In some cases it may make sense to use multi-region or dual-region, but the details are more than I want to get into here. Don’t worry; you can find lots more information on them! Additionally, I won’t go into the details about selecting the appropriate storage class based on access frequency and longevity, but more information can be found here as well our recently introduced Autoclass tiering feature..
Since buckets can be accessed from the Internet, you need to be careful to only make the things you want to be public, public. The default value is to keep your data off of the Internet, so when you create the bucket, there’s an option to “Enforce public access prevention on this bucket” that is already selected. Since we’ll be making items in this bucket public later on, clear the option before confirming the bucket information. Don’t worry; you’ll see how to set that option back later.
Take a look at the Bucket detail page. There are tabs to see the objects in the bucket, the permissions on the bucket, and much more. There are commands to create folders and to upload files and folders. We’ll do some of this in the next section. You can even find out much more from the Learn option in the upper right.
Adding items to the bucket
Find a file you’d like to upload to this bucket, remembering it will be made public to the Internet later. If you don’t have anything available, there’s a picture of a cat (of course, it’s a cat picture, this is the Internet!) in the interactive tutorial.
Go to the bucket details for your bucket. Using the “Upload files” button, you’ll just upload the file to the bucket by selecting the file from your computer. We won’t get into service classes here, but when you’re ready, there are links in the references.
Of course, if you have a lot of files in a bucket, you probably will want some sort of organization. You can do this by creating folders in your bucket with the “Create Folder” option. Once you’ve done that, you can go to the overflow menu (three vertical dots at the end line with the object information) and select Move. Remember, if you need help finding this, the interactive tutorial will point it out.
Making a bucket public
No surprise, the Bucket details hold lots of information about your bucket. You’ll change the bucket’s permissions in the Permission tab. (I hope that’s not a surprise.) You can then give access to individuals, groups, or all users. For this tutorial, follow the steps to grant access to allUsers. There are a variety of different levels of access you can grant to a user. In this case, just grant the ability to view the object using Storage Object Viewer.
Then go into the Objects tab to find the URL for the object you uploaded. Copy that URL and ensure you can indeed access the object from another tab or incognito window.
Now that you’ve done that, go back to the Permission tab for the bucket and select Prevent Public Access to secure the bucket again.
So, in a nutshell, to make all files in a bucket public, in the Permissions tab:
- Uncheck the option “Remove Public Access Prevention”
- Use Grant Access to give allUsers the Storage Object Viewer role in Cloud Storage.
To make all files in a bucket private, in the Permissions tab select Prevent Public Access.
Cleaning up
There is a charge to keep items in Cloud Storage, so you’ll probably want to delete the object you uploaded by selecting it and pressing Delete. You can delete the bucket in a similar way. The tutorial has details, of course.
While there are lots more details about Cloud Storage, you’ve got the big picture now. Go do something creative and useful with it!
So what now?
If you haven’t walked through the interactive tutorial, give it a try!
Serverless for Startups, the Best Way to Succeed: Expert Says
7023
Of your peers have already read this article.
1:30 Minutes
The most insightful time you'll spend today!
As Google Cloud has become a choice for more startups, I’ve experienced an increase in founders asking how they should think about cloud services. Though each startup is different and requirements may vary across industries and regions, I’ve seen a few core best practices that help startups to succeed—as well as several traps to avoid.
For example, If you go with a public cloud provider, you’re ideally not starting from ground zero like you would running your own data center, but it’s important not to introduce similar complexity in a virtualized environment. Just because you’re using public cloud infrastructure doesn’t mean you want to manage it.
Instead, you want to leverage platforms that abstract away complexity so your team can focus on delivering value to customers. That’s where serverless comes in. Serverless platforms are fully managed by the provider, offering automatic scaling for workloads, as well as provisioning, configuring, patching, and management of servers and clusters. Freed from these resource-intensive tasks, your technical talent can focus on the things that differentiate your business, not on IT curation.
This applies to not only running stateless applications, but also managing and analyzing data. You should be collecting data points about which features are most popular on your platform, what people are buying, the types of activities that help your customers get their jobs done, and so on. This information is essential to building and executing on a product roadmap that will serve your customers. However, it’s not enough to collect this data, you need to make your data accessible, secure, and easy for your team to analyze—so where do you run and host it?
On Google Cloud, this is where options like Spanner and Cloud SQL can play a large role, as can BigQuery for analysis. You won’t have to worry about standing up infrastructure or patching servers—you can just stream your data to our data management platforms, where it’s available whenever someone needs to run a query. By leveraging a serverless architecture, your startup can be data-driven without having to invest in the traditional complexity of database administration and management—and that can significantly change your playing field.
Serverless is just one of the factors you should consider as you build out your tech stack. To hear my thoughts on a range of other topics relevant to startups — such as security, cloud credits, and the differences among managed services — check out the below video or visit our Build and Grow page.
Google Cloud Announces General Availability of BigQuery Row-level Security
6587
Of your peers have already read this article.
3:00 Minutes
The most insightful time you'll spend today!
Data security is an ongoing concern for anyone managing a data warehouse. Organizations need to control access to data, down to the granular level, for secure access to data both internally and externally. With the complexity of data platforms increasing day by day, it’s become even more critical to identify and monitor access to sensitive data. In many cases, sensitive data is co-mingled with non-sensitive data, and access restrictions to sensitive data need to be enabled based on factors like data location or presence of financial information. There may also be nuances where data is sensitive for some groups of users, while for others, it is not.
Today, we’re pleased to announce the general availability of BigQuery row-level security, which gives customers a way to control access to subsets of data in the same table for different groups of users. Row-level security (RLS) extends the principle of least privilege access and enables fine-grained access control policies in BigQuery tables. BigQuery currently supports access controls at the project-, dataset-, table- and column-level. Adding RLS to the portfolio of access controls now enables customers to filter and define access to specific rows in a table based on qualifying user conditions—providing much needed peace of mind for data professionals.
“Our digital transformation and migration of data to the cloud magnifies the business value we can extract from our information assets. However, granular data access control is essential to comply with international regulatory and contractual requirements. BigQuery row-level security helps us comply with data residency and export restrictions,” says Jarrett Garcia, Iron Mountain’s Enterprise Data Platform Senior Director. “It enables us to manage fine-grained access controls without replicating data. What used to take months for approval and access provisioning can now be done more efficiently and effectively. We are looking forward to implementing additional data security capabilities on the BigQuery roadmap to address other critical business use cases.”
How BigQuery row-level security works
Row-level security in BigQuery enables different user personas access to subsets of data in the same table. Customers who are currently using authorized views to enable these use cases can leverage RLS for ease of management. To express the concept of RLS, we have introduced a new entity in BigQuery called row access policy. Row access policies map a group of user principals to the rows that they can see, defined by a SQL filter predicate.
Secure logic rules created by data owners and administrators determines which user can see which rows through the creation of a row-level access policy. The row-level access policies created on a target table by administrators or data owners are applied when a query is run on the table. One table can have multiple policies applied to it.
Below is an example, where row-level access policies have been created to filter data based on users’ “region”.
In the illustrated scenario above, row-level access policies have been created to verify a querying user’s region and to give them access only to the subset of data relevant to that region. Access policies are granted to a grantee list which support all types of IAM principles such as individual users, groups, domains or service accounts. In this example, when a user queries the table, row-level access policies are evaluated to assess which, if any, policies are applicable to that user. The group ‘sales-apac’ is granted access to view a subset of rows where region = ‘APAC’ whereas the group ‘sales-us’ is granted access to view a subset of rows where the region = ’US’. Likewise, users in both groups will see rows in both regions, and users in neither group will not see any rows.
Row-level access policies can also be created using the SESSION_USER() function to restrict access only to rows that belong to the user running the query. If none of the row access policies are applicable to the querying user, the user will have no access to the data in the table.
When a user queries a table with a row-level access policy, BigQuery displays a banner notice indicating that their results may be filtered by a row-level access policy. This notice displays even if the user is a member of the `grantee_list`.
When to put BigQuery row-level security to work
Row-level access policies are useful when you have a need to limit access to data based on filter conditions. The row-access policies’ filter predicate supports arbitrary SQL, and is conceptually similar to the WHERE clause of a SQL query. Filter predicates support the SESSION_USER() function to restrict access only to rows that belong to the user running the query. If none of the row access policies are applicable to the querying user, the user will have no access to the data in the table. Currently, the column used for filtering must be in the table, but we anticipate adding support for subqueries in the filter expression, opening up access to use cases where data is filtered based on lookup tables and calculated values. Row-level access policies can be created, updated and dropped using DDL statements. You will be able to see the list of row-level access policies applied to a table using the BigQuery schema pane in the Cloud Console, which simplifies the management of policies per table, or by using the bq command-line tool.
Row-level security is compatible with other BigQuery security features, and can be used along with column-level security for further granularity. Since row-level access policies are applied on the source tables, any actions performed on the table will inherit the table’s associated access policies, to ensure access to secure data is protected. Row-level access policies are applicable to every method used to access BigQuery data (API, Views, etc).
Try it out
We’re always working to enhance BigQuery’s (and Google Cloud’s) data governance capabilities, to provide more controls around managing your data. With row-level security, we are adding deeper protections for your data. You can learn more about BigQuery row-level security in our documentation and best practices.
Harnessing the Power of Data and AI to Transform Life Science Supply Chains
3963
Of your peers have already read this article.
3:30 Minutes
The most insightful time you'll spend today!
Global life science supply chains are lengthy and complex with many moving parts. One small disruption can create serious delays and affect your ability to deliver therapeutics for patients.
Supply chain disruptors
Over the last few years, healthcare organizations have encountered a range of obstacles, from both internal and external factors, that have resulted in supply networks failing to get drugs and medical devices to where they need to be on time. These obstacles include:
- Labor and supply shortages
- Rising material costs
- Raw material constraints
- Geo-political events
- Unpredictable weather
How do you overcome supply chain disruptors that are out of your control?
The intelligent healthcare supply chain
While many organizations have already implemented data-driven supply chains, organizations are still faced with the challenges of static, siloed, and different functional supply chain applications; limited data exchange with key trading partners across upstream and downstream operations; and the inability to effectively leverage relevant external data.
At Google Cloud, we believe the key to meaningful and effective change is a data-driven supply chain that allows you to achieve visibility, flexibility, and innovation.
Our solutions help you prepare for the unpredictable and enhance the value of your data. By unlocking AI-driven insights, you can strengthen distribution networks and optimize your workflows and supply chains to become more reliable, intelligent, and sustainable. Some of the business challenges we address include:
- Predicting demand with Vertex AI Forecast
- Visual inspection for quality and predictive maintenance with pre-built ML models
- Automating and optimizing pickup and delivery operations with Cloud Fleet Routing API
- Real time and holistic inventory visibility with Supply Chain Twin
Make sure you’re prepared for the unpredictable with real-time visibility over your distribution networks. Learn how you can harness the power of AI and analytics and gain actionable insights that enhance your supply chain.
More Relevant Stories for Your Company
What is BigQuery?
BigQuery is Google Cloud's enterprise data warehouse designed to help you ingest, store, analyze, and visualize big data with ease. Organizations rely on data warehouses to aggregate data from disparate sources, process it, and make it readily available for data analysis that supports their strategic decision-making. You can ingest data
Data Warehouse Migration Challenges and How to Meet Them
In the last blog post, we discussed why legacy data warehouses are not cutting it any more and why organizations are moving their data warehouses to cloud. At GCP, we often hear that customers feel that migration is an uphill battle because the migration strategy was not deliberately considered. Migrating to
Analytics Hub for Secure Data Sharing and Analytics Unlocks True Data Value and Insights
Customers tell us that sharing and exchanging data with other organizations is a critical element of their analytics strategy, but it’s hamstrung by unreliable data and processes, and only getting harder with security threats and privacy regulations on the rise. Furthermore, traditional data sharing techniques use batch data pipelines that are
How Intelligent and Trusted Data Cloud is Pivotal for Digital Disruption
Businesses that are dependent on legacy systems find it real hard to derive desired business results and unlock value from their ever-growing volume of siloed data, as a major chunk of their efforts and resources are invested in its extensive maintenance and management. Even with the adoption of latest tools
