The third course in this course series is Achieving Advanced Insights with BigQuery. Here we will build on your growing knowledge of SQL as we dive into advanced functions and how to break apart a complex query into manageable steps.
This course is part of the From Data to Insights with Google Cloud Specialization
Achieving Advanced Insights with BigQuery
About this Course
What you will learn
Discuss the more advanced functions of SQL on BigQuery.
Compare how BigQuery was developed to address scaling limitations in the context of how traditional databases have evolved to handle dataset scale.
Identify the fundamental pieces of work that impact BigQuery performance and how to optimize your queries for speed.
Identify what Google Cloud and BigQuery tools are available to you to permission-control and share your data.
Skills you will gain
- Google Cloud Platform
- Cloud Computing
We help millions of organizations empower their employees, serve their customers, and build what’s next for their businesses with innovative technology created in—and for—the cloud. Our products are engineered for security, reliability, and scalability, running the full stack from infrastructure to applications to devices and hardware. Our teams are dedicated to helping customers apply our technologies to create success.
Syllabus - What you will learn from this course
Overview of what you will learn in this course
Advanced Functions and Clauses
Deepen your knowledge of SQL on BigQuery by learning about more advanced functions like statistical approximations, analytical window queries, user-defined functions, and WITH clauses.
Schema Design and Nested Data Structures
Walkthrough the evolution of how traditional databases handle dataset scale and compare how BigQuery was developed to address scaling limitations. Deep dive into nested and repeated fields which are a key part of denormalized BigQuery data structures.
Optimizing for Performance
Learn the fundamental pieces of work that impact BigQuery performance and how to optimize your queries for speed.
Advanced Insights with Vertex AI Workbench
Introducing Vertex AI Workbench -- a key tool in the Data Scientist toolkit -- which enables analysts to collaborate through the use of scalable cloud notebooks.
Securing and sharing your BigQuery datasets is critical for any organization. Learn what Google Cloud and BigQuery tools are available to you to permission control and share your data.
Summary of the course ley learning points
- 5 stars75.30%
- 4 stars20.98%
- 3 stars3.08%
- 2 stars0.46%
- 1 star0.15%
TOP REVIEWS FROM ACHIEVING ADVANCED INSIGHTS WITH BIGQUERY
Excellent course; I only wish there were a much longer and more robust lab on optimizing query performance, after all the tips we learned in the corresponding lessons.
Arrays and Structs could have been explained better and more practice on these topics could have been helpful. Otherwise the content is very good and informative.
I learned lots about the structs and arrays and how it could be used to improve performance by creating parent child relationships instead of doing expensive joins
Everything was very good except for the sound problem. Your sound volume was so poor. You need to talk more loudly.
About the From Data to Insights with Google Cloud Specialization
Want to know how to query and process petabytes of data in seconds? Curious about data analysis that scales automatically as your data grows? Welcome to the Data Insights course!
This four-course accelerated online specialization teaches course participants how to derive insights through data analysis and visualization using the Google Cloud Platform. The courses feature interactive scenarios and hands-on labs where participants explore, mine, load, visualize, and extract insights from diverse Google BigQuery datasets. The courses also cover data loading, querying, schema modeling, optimizing performance, query pricing, and data visualization. This specialization is intended for the following participants: ● Data Analysts, Business Analysts, Business Intelligence professionals ● Cloud Data Engineers who will be partnering with Data Analysts to build scalable data solutions on Google Cloud Platform To get the most out of this specialization, we recommend participants have some proficiency with ANSI SQL. >>> By enrolling in this specialization you agree to the Qwiklabs Terms of Service as set out in the FAQ and located at: https://qwiklabs.com/terms_of_service <<<
Frequently Asked Questions
Can I preview a course before enrolling?
Yes, you can preview the first video and view the syllabus before you enroll. You must purchase the course to access content not included in the preview.
What will I get when I enroll?
Once you enroll and your session begins, you will have access to all videos and other resources, including reading items and the course discussion forum. You’ll be able to view and submit practice assessments, and complete required graded assignments to earn a grade and a Course Certificate.
When will I receive my Course Certificate?
If you complete the course successfully, your electronic Course Certificate will be added to your Accomplishments page - from there, you can print your Course Certificate or add it to your LinkedIn profile.
Why can’t I audit this course?
This course is one of a few offered on Coursera that are currently available only to learners who have paid or received financial aid, when available.
Is financial aid available?
Yes. In select learning programs, you can apply for financial aid or a scholarship if you can’t afford the enrollment fee. If fin aid or scholarship is available for your learning program selection, you’ll find a link to apply on the description page.
Qwiklabs Terms of Service
Definitions“Service” means the Lab Service and the Lab Creation Service, collectively, along with the Qwiklab Site.“Lab Service” means the educational, training, and learning services provided to you through the Qwiklabs Site, or any related website provided by Cloud vLab, in concert with your respective Lab Sponsor.“Creator role” means the designation of your account as a creator to access the “Lab Creation Service”. If you have the Creator role, all sections of this agreement apply to you including sections that reference the Lab Service and the Lab Creation Service.“Lab Creation Service” means the services and functionality hosted by Cloud vLab and made available to you only if you have a “Creator role” on or through the Qwiklabs Site through which you may deploy, configure, customize, manage, administer, and control a virtual server for implementing and testing software as a part of your training through the Lab Service.“Lab Sponsor” means the company or other organization with whom you are employed or otherwise associated in connection with the Lab Service."Content" means any content or work of authorship created, owned or licensed by you only if you have a “Creator role’, submitted to the Lab Creation Service, and that is transmitted, rendered, displayed or executed on or through the Service, including without limitation any text, postings, audio, sounds, video, photos, images, messages, software, and materials.“Sponsor Content” means any content or work of authorship created, owned, or licensed by your Lab Sponsor and utilized in the Service.“Qwiklabs Technology” means all of Cloud vLab’s proprietary technology (including, but not limited to, software, hardware, products, processes, algorithms, user interfaces, know-how, techniques, designs, and other tangible or intangible technical material or information) made available to you by Cloud vLab in providing the Service, excluding Sponsor Content.“Qwiklabs Site” means the web site located at Qwiklab.com, and/or any related or successor URLs operated or controlled by Cloud vLab.“Resources” means any virtual or physical infrastructure provided to you by the Service.Use of the ServiceOverview of Rights
This Agreement applies to all use of the Service. Subject to the terms and conditions of this Agreement and your registration with us through the Qwiklabs user registration process, Cloud vLab hereby grants you the right to use the Lab Service under the terms of this Agreement. Furthermore, if you have a Creator role, Cloud vLab hereby grants you the right to use the Lab Creation Service under the terms of this Agreement. Use of Resources may be performed only in accordance with the terms and conditions of this Agreement and such other specifications as may be communicated by Cloud vLab from time to time.
Restrictions and Limitations
(a) You may not access the Service if you are a competitor of Cloud vLab, unless you have our prior written consent. In addition, you may not access and/or use the Service for purposes of monitoring its availability, performance, or functionality, or for any other benchmarking or competitive purposes.
(b) You shall not (i) license, sublicense, sell, resell, transfer, assign, distribute, or otherwise commercially exploit or make available to any third party the Service in any way, except as expressly authorized in this Agreement; (ii) modify (except as permitted through the Lab Creation Service (if you have a Creator role) or make derivative works based upon the Service; (iii) reverse engineer the Service and/or any component thereof; (iv) access the Service in order to build a competitive product or service; (v) build a product using similar ideas, features, functions, or graphics of the Service, or (vi) copy any ideas, features, functions, or graphics of the Service.
(c) You shall not utilize any part of the Service to: (i) send spam or otherwise duplicative or unsolicited messages in violation of applicable Laws (as defined below); (ii) send or store infringing, obscene, threatening, libelous, defamatory, pornographic, online gambling, or otherwise unlawful or tortious material, including material harmful to children or that violates third party privacy rights or inconsistent with the generally accepted practices of the Internet community as reasonably determined by Cloud vLab; (iii) send or store material containing software viruses, worms, Trojan horses or other harmful computer code, files, scripts, agents, or programs; (iv) interfere with or disrupt the integrity or performance of the Service or the data contained therein; (v) attempt to gain unauthorized access to the Service or its related systems or networks; or (vi) enable, further, or participate in any unlawful activity. You may not use any part of the Service in connection with providing any website or service that is aimed at, directed to, or marketed to children under the age of 13. You acknowledge and agree that if Cloud vLab or any Lab Sponsor becomes aware or has reason to believe that you are engaging in any such prohibited activity, both have the right to immediately suspend and/or terminate your use of the Service.
(d) If you have a Creator role any use of the Lab Creation Service and the Resources must be limited to use for the sole purpose of completing or participating in Lab Services provided by your Lab Sponsor. The Resources may not be made available to or accessed by any third party other than your Lab Sponsor and/or any individuals acting on behalf of your Lab Sponsor. All software or other Content stored on the Resources may be deleted at any time by Cloud vLab. Cloud vLab makes no warranties or representations with respect to the performance, reliability, or functionality of the Lab Creation Service. All Content or other data stored on the Resources should be non-confidential and no warranty or representation is made with respect to the confidentiality or security of any Content stored on the Resources.
(e) The Lab Service and if you have a Creator role, Lab Creation Service right to use is non-transferable. Any Lab tokens you buy or any promotional tokens you’re given are for your individual use and cannot be resold or distributed.
(f) All rights not expressly granted to you are reserved by Cloud vLab and its licensors.
You are responsible for all activity occurring through the use of the Service. You represent that you shall abide by all applicable local, state, national, and foreign laws and regulations in connection with your use of the Service, including, without limitation, those related to intellectual property and privacy (collectively, "Laws").
You will not obscure or contravene or attempt to obscure or contravene any notices of or attribution to Cloud vLab displayed within the Service that relate to Cloud vLab’s role as a service provider.
You will select and use a secure user password for your account and you agree not to share your password with any other party.
Commercial Activities Prohibited
The Resources may not be used for commercial advertising purposes or related promotional or commercial activities. If you have a Creator role, use of the Lab Creation Service is limited to the creation and testing of Content and related materials in connection with the Lab Service.
This section is applicable to you only if you have a Creator role on Qwiklabs site or any other site provided by Cloud vLab in concert with your Lab Sponsor.
As between you and Cloud vLab, Content shall be the property of you. By posting, uploading, inputting, providing or submitting Content, y ou hereby grant to Cloud vLab and its affiliated companies, Agents and necessary sublicensees a worldwide, perpetual, royalty-free license to (i) copy, reproduce, edit, translate, reformat, store, display, distribute, and perform Content on or through the Service in order to provide the Service; (ii) use and analyze the Content in furtherance of Cloud vLab’s internal business purposes or otherwise for the purpose of providing the Service; (iii) disclose metrics regarding Content on an aggregated basis for marketing and business development purposes; (iv) publish your name in connection with your Content; and (v) sublicense such rights to any supplier or third party in relation to the operation of the Qwiklabs business including the Service. "Agents" include (i) service providers and related third parties that CloudvLab may hire to perform certain business-related functions and (ii) business partners and related third parties with which CloudvLab may have a contractual relationship with respect to the Service.
Demonstration Accounts and Use
Cloud vLab may grant to certain persons or entities a limited-time demonstration account (“Demo Account”) to use the Service for the limited purpose of evaluating the Service for purchase. Any such Demo Account granted to you may be used only for the limited time period specified by Cloud vLab (the “Demo Period”) upon provision of the Demo Account login details to you. Any Demo Account may be revoked at any time and for any reason. All Content submitted by any user of a Demo Account will be deleted upon termination of the Demo Period. In addition to the terms and conditions of this Section 7, all terms and conditions of this Agreement shall apply to any use of the Service in connection with a Demo Account.
Intellectual PropertyCloud vLab Intellectual Property
Cloud vLab and its licensors, partners, or affiliates, where applicable, shall own all right, title, and interest, including, without limitation, all intellectual property rights in and to the Cloud vLab Technology. This Agreement is not a sale and does not convey to you any rights of ownership in or related to the Service, the Cloud vLab Technology or the intellectual property rights owned by Cloud vLab. The Cloud vLab name, the Qwiklabs trademark, and the other product names associated with the Service are trademarks of Cloud vLab, and no right or license is granted to use them.
You hereby assign and agree to assign to Cloud vLab all right, title, and interest in and to any enhancement requests, recommendations, suggestions, comments, evaluations, ideas, or other information relating to the Service (“Feedback”) provided by you to Cloud vLab, including, but not limited to, all intellectual property rights embodied in such Feedback.
Modification of Terms
Cloud vLab reserves the right to modify this Agreement or its policies relating to the Service and other Applicable Terms, at any time, effective upon posting of an updated version of this Agreement, policies and/or other Applicable Terms on the Service. You are responsible for regularly reviewing this Agreement and such policies, the current version of which shall be made available as set forth herein through the Qwiklabs Site. If any change to this Agreement is not acceptable to you, your sole remedy is to terminate your use of the Service and any other rights under this Agreement. Any use of the Service after such publication shall constitute acceptance by you of such revised Agreement.
Term and TerminationTerm
This Agreement commences upon your acceptance of this Agreement by clicking “I Accept” in the sign-up process for the Service and shall continue until terminated (the “Term”). You acknowledge and agree that Cloud vLab or your Lab Sponsor may terminate and/or suspend your access to any portion of the Service for any reason or for no reason at all, in Cloud vLab’s sole discretion, without prior notice. You may terminate this Agreement at any time by discontinuing your use of the Service. For users of Demo Accounts, this Agreement shall terminate upon the expiration of the corresponding Demo Period. All other user accounts shall terminate upon the conclusion or withdrawal of the Lab Service by the Lab Sponsor.
Effects of Termination
Upon termination or expiration, your right to access or use Content shall immediately cease, and Cloud vLab shall have no obligation to retain copies of any Content or related data. Upon termination or expiration of this Agreement, the following provisions will survive in full force and effect: 6, 8, 10.2, 11, 12, 13 and 15, and any other clause or portion of a clause which, by its nature, is intended to survive termination or expiration of this Agreement.
You shall indemnify and hold Cloud vLab, its licensors, partners and each such party’s parent organizations, subsidiaries, affiliates, officers, directors, employees, attorneys, and agents harmless from and against any and all claims, demands, costs, damages, losses, liabilities, and expenses (including attorneys’ fees and costs) arising out of or in connection with: (i) any Content, including without limitation any claim alleging that use of any Content infringes or misappropriates the rights of, or has caused harm to, a third party; (ii) a breach or violation by you of any responsibilities, representations, covenants, or warranties under this Agreement and/or other Applicable Terms; or (iii) your use of the Resources. You agree that Cloud vLab’s licensors and partners shall be third party beneficiaries of your indemnification obligations hereunder.
Disclaimer of Warranties
You acknowledge and agree that by using the Service, you may be exposed to Sponsor Content that is offensive, indecent, or objectionable. You further acknowledge and agree that the Service and the Sponsor Content may contain errors or omissions. You acknowledge and agree that Cloud vLab does not screen or review published Sponsor Content on the Service to determine whether it contains false or defamatory material or material which is offensive, indecent, objectionable, or which contains errors or omissions. Under no circumstances will Cloud vLab be liable in any way for Sponsor Content, including, but not limited to, for any defamation, falsehoods, errors, or omissions in any such content, or for any loss or damage of any kind incurred as a result of the use or publication of any such Sponsor Content posted, emailed, or otherwise transmitted via the Service. Cloud vLab does not guarantee that any Sponsor Content will be to your satisfaction.
CLOUD VLAB AND ITS LICENSORS MAKE NO REPRESENTATION, WARRANTY, OR GUARANTY AS TO THE RELIABILITY, TIMELINESS, QUALITY, SUITABILITY, TRUTH, AVAILABILITY, ACCURACY, OR COMPLETENESS OF THE SERVICE OR ANY SPONSOR CONTENT. CLOUD VLAB AND ITS LICENSORS DO NOT REPRESENT OR WARRANT THAT (A) THE USE OF THE SERVICE WILL BE SECURE, TIMELY, UNINTERRUPTED, OR ERROR-FREE OR OPERATE IN COMBINATION WITH ANY OTHER HARDWARE, SOFTWARE, SYSTEM, OR DATA, (B) THE SERVICE WILL MEET YOUR REQUIREMENTS OR EXPECTATIONS, (C) ANY STORED DATA WILL BE ACCURATE OR RELIABLE, (D) THE QUALITY OF ANY PRODUCTS, SERVICES, INFORMATION, OR OTHER MATERIAL PURCHASED OR OBTAINED BY YOU THROUGH THE SERVICE WILL MEET YOUR REQUIREMENTS OR EXPECTATIONS, (E) ERRORS OR DEFECTS WILL BE CORRECTED, OR (F) THE SERVICE OR THE SERVER(S) THAT MAKE THE SERVICE AVAILABLE ARE FREE OF VIRUSES OR OTHER HARMFUL COMPONENTS. THE SERVICE AND ALL SPONSOR CONTENT ARE PROVIDED TO YOU STRICTLY ON AN "AS IS" BASIS. CLOUD VLAB AND ITS LICENSORS HEREBY DISCLAIM (TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW) ALL CONDITIONS, REPRESENTATIONS AND WARRANTIES, WHETHER EXPRESS, IMPLIED, STATUTORY OR OTHERWISE, INCLUDING, WITHOUT LIMITATION, ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT OF THIRD PARTY RIGHTS.
CLOUD VLAB’S SERVICES MAY BE SUBJECT TO LIMITATIONS, DELAYS, AND OTHER PROBLEMS INHERENT IN THE USE OF THE INTERNET AND ELECTRONIC COMMUNICATIONS. CLOUD VLAB IS NOT RESPONSIBLE FOR ANY DELAYS, DELIVERY FAILURES, OR OTHER DAMAGE RESULTING FROM SUCH PROBLEMS.
Limitation of Liability
IN NO EVENT SHALL CLOUD VLAB’S AGGREGATE LIABILITY ARISING WITH RESPECT TO OR IN CONNECTION WITH THIS AGREEMENT EXCEED THE AMOUNTS RECEIVED BY CLOUD VLAB AND ATTRIBUTABLE TO YOUR LAB SPONSOR’S RELATIONSHIP WITH US, IF ANY, IN THE THREE (3) MONTH PERIOD IMMEDIATELY PRECEDING THE EVENT UPON WHICH CLAIMS ARE BASED. IN NO EVENT SHALL CLOUD VLAB AND/OR ITS LICENSORS BE LIABLE TO ANYONE FOR ANY INDIRECT, PUNITIVE, SPECIAL, EXEMPLARY, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES OF ANY TYPE OR KIND (INCLUDING LOSS OF DATA, REVENUE, PROFITS, USE, OR OTHER ECONOMIC ADVANTAGE) ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE SERVICE, INCLUDING BUT NOT LIMITED TO THE USE OF OR INABILITY TO USE THE SERVICE, OR FOR ANY CONTENT OBTAINED FROM OR THROUGH THE SERVICE, ANY INTERRUPTION, INACCURACY, ERROR, OR OMISSION, REGARDLESS OF CAUSE, IN THE CONTENT, EVEN IF CLOUD VLAB OR ITS LICENSORS HAVE BEEN PREVIOUSLY ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
In no event shall Cloud vLab incur any liability to you or any End Users on account of any loss or damage resulting from any delay or failure to perform all or any part of this Agreement to the extent such delay or failure is caused by events, occurrences, or causes beyond the control and without negligence of Cloud vLab, including by not limited to acts of God, strikes, riots, acts of war, lockouts, earthquakes, fires, and explosions.
Last Updated: September 1, 2015
More questions? Visit the Learner Help Center.