Okay, let me introduce you to your best friend, if you're not already familiar with them yet, is the query validator, and it's a phenomenal tool. I don't know when this was added to BigQuery. But it was one of the things that definitely just made my year, when the team added it in. Query validator, in addition to validating the syntax error query, if you click on it, will also tell you how much data that you're going to process. And since you're paying for this, it is extremely good to know that maybe your query accidentally is pulling in petabytes of data when you only wanted to pull in a certain subset. And you forgot to use your where clause filter or something like that. So you can actually see how much data you're going to process, copy and paste that into your pricing estimator. And then see how much that's actually going to cost. Okay, and that's the query validator. And you're going to be using this in your lab extensively. Okay, so a little bit of topics, as we mentioned the shared resource pool of these slots or workers. This is to protect a BigQuery from somebody abusing all of these resources and just going absolutely nuts, nuts is a relative term. [LAUGH] We're talking about, say, if they wanted to just hammer one particular table because ingesting data into BigQuery is free, and copying tables are free. And these quotas are designed to protect the reasonable user. So you have 50 concurrent queries. So that means 50 queries running at the exact same time is the current quota limit. A query timeout, one query can't run longer than six hours. And look into optimization there. And you have 1,000 updates to a single table per day. And you can have 1,000 tables referenced by a single query. And if you have query with 1,000 tables, a 1,000 tables in a single query, send me an email. That's an amazingly long SQL statement, so definitely break that up. And the maximum query size is 128 megabytes, and that's compressed. And a 125 megabyte text file compressed is a very, very large query, trust me. Okay, so now this is to protect the BigQuery service as a whole. But say you wanted to flip this around and basically say, okay, that's great and use these quotas are really, really high. But what about if I have multiple teams, like a marketing team and analysis team that both use my Google Cloud Platform project? I want to be able to make sure that I don't blow through my entire budget for BigQuery within the first week of the month, what can I do there? Well, you can set up custom quotas by user or by user group. And custom alerts, through your logs, to see how much you've actually gone through for your budget as well. So those custom quotas are your first line of defense, which you can set up. Okay, back to good SQL hygiene and SQL behavior. So query just what you need. So as we mentioned previously, don't SELECT *, especially if you have long string length columns. So say you had reviews of products, right? So you're running an ecommerce analytics warehouse. And you have a column that says, this is a customer's review and it's paragraphs and paragraphs and paragraphs, right? So paragraphs and paragraphs and paragraphs of text translate to bytes and bytes and bytes of data. So if you really just wanted to return the first name, last name and how much they ordered, just include those three columns, and then leave the reviews out of there. So SELECT * can be dangerous. And again, it'll give a sense for how much data that you're processing by clicking on that query validator. But generally, as a best practice, limit your queries just to the columns that you need. And it'll make it easier too, when you actually see the query output to not have a massive horizontal scroll bar. And you're scrolling through and kind of seeing all the columns. And just use that preview button, if you wanted to just get a sense for some of the data that's available. And that first bullet point there is make a liberal use of your where clause. You want to filter often and filter early, that's a great performance consideration too, as we're going to cover in a later course. But keep in mind that, if you're using a where condition and you think that limiting the records, just to get a feel for how many met that condition of column = 123. Limit 10 doesn't preclude you from having to scan through all those bytes of data out on disk and match that WHERE condition as well.
