Top Google BigQuery frequently asked interview questions

In this post we will look at Google BigQuery Interview questions. Examples are provided with explanation.

1. What is Google BigQuery?
2. What are the Components of Google BigQuery?
3. Explain Technical Architecture of Google BigQuery?
4. What is Google Cloud?
5. What are the components of the Google Cloud Platform?
6. What are the products supported by GCP?
7. What is GCP App Engine?
8. What are Window Functions in BigQuery?
9. What is BigQuery Cloud Data Warehouse?
10. What are the ways for accessing the BigQuery Cloud Datawarehouse?
11. How can we load data from Cloud Storage?
12. How to delete duplicate rows from a BigQuery table?
13. What are the Database services supported by GCP?

What is Google BigQuery?

Google Bigquery is used for Storing and Querying datasets which are time consuming and expensive.It is an Enterprise Data Warehouse which solves problems by enabling super fast SQL Queries using the processing power of google infrastructure.BigQuery is fully managed, we dont need for deploying any resources like Disks and Virtual Machines.As Bigquery has high level architecture and is built on top of Dremel Technology which has been in production on Google internally.

What are the Components of Google BigQuery?

Google BigQuery consists of 12 components:

• Serverless Service Model.
• Opinionated Storage Engine.
• Dremel Execution Engine & Standard SQL.
• Enterprise-grade Data Sharing.
• Public, Commercial, Marketing Datasets and the Free Pricing Tier.
• Streaming Ingest.
• Batch Ingest.
• Federated Query Engine.
• UX, CLI, SDK, ODBC/JDBC, API.
• Pay-Per-Query AND Flat Rate Pricing.
• IAM, Authentication & Audit Logs.

Explain Technical Architecture of Google BigQuery?

• Snowplow BigQuery Loader used in transforming data from Google Pub/Sub and loads it into BigQuery.
• Snowplow BigQuery Mutator used in reading and performing table mutations for adding new columns as per required.
• Snowplow BigQuery Repeater is an app used for reading failedInserts and tries in re-inserting them into BigQuery.
• Snowplow BigQuery Forwarder used in implementing repeater Apache Beam job.

What is Google Cloud?

Google CLoud Platform(GCP) contains a set of physical assets like computer, hard disks, virtual resources such as virtual machines that are contained in Google Data Centers.It helps in allowing software and hardware products for at scaling and co existing remotely in all data centers.It helps in accessing, managing and using tools through web interfaces.

What are the components of the Google Cloud Platform?

Components of GCP are as follows:
Google Compute Engine
Google Cloud Container Engine
Google Cloud App Engine
Google Cloud Storage
Google Cloud Dataflow
Google BigQuery Service
Google Cloud Job Discovery
Google Cloud Endpoints
Google Cloud Test Lab
Google Cloud Machine Learning Engine

What the products supported by GCP?

• BigQuery used as Data Warehouse for insights.
• Cloud CDN helps in content delivery network in images, audios, videos, etc.
• Cloud Functions used in writing codes and handling infrastructure for running that.
• Cloud Run is a managed environment used in running Containerized Applications.
• Cloud SDKused in providing CLT and other libraries for Google CLoud.
• Cloud Storage helps in storing format that are durable and insecure.
• Compute Engine used in running Google's data centers and providing virtual machines.
• Data Flow helps in streaming analytics in batch processing and streams.
• Operators used in monitoring, logging and performing other services.
• Google Kubernetes Engine helps in managing environment for running containerized applications.

What is GCP App Engine?

Google Cloud Platform App Engine is used in providing and ensuring the capability in running our and the availability of our application.It manages the servers and infrastructures for us, and is responsible for providing built in services and APIs as traffic to our sites.GCP App Engine also provides integration to different development tools such as Jenkins, Eclipse, GIT, Maven, etc in building our application without changing our workflow.

What is Window Functions in BigQuery?

Window Functions are used in computing values over a group of rows and can return single value for each row, it aggregates functions that returns a single value for a group of rows.
It consists of 3 types of function such as:
Navigation Function used in returning value given a specific location criteria.
Numbering Functionsused in assigning numbers to each rows which are based on its position in the specified window.
Analytic Function used in performing calculation on the set of values.

What is BigQuery Cloud Data Warehouse?

Google BigQuery Cloud Data Warehouse is cloud native which is built for the cloud and also provides features such as: Auto Scaling, Unlimited Storage, Rebusting Data, Encryption and security, etc.

What are the ways for accessing the BigQuery Cloud Data Warehouse?

Ways for accessing BigQuery Cloud Datawarehouse are as follows:
Web User Interface
ODBC Drivers
JDBC Drivers
BQ Command line Client
Python Libraries

How can we load data from Cloud Storage?

We can load data from Cloud Storage by using the following source code:

• vro
• Comma-separated values
• JSON
• ORC
• Parquet
• Firestore exports stored in Cloud Storage

How to delete duplicate rows from a BigQuery table?

We can delete duplicate rows by running the query which rewrites our table.We can use the following command:

SELECT *
FROM (
  SELECT
      *,
      ROW_NUMBER()
          OVER (PARTITION BY INDEX)
          row_number
  FROM Accidents.FILLEDCOMBINE
)
WHERE row_number = 2

What are the Database services supported by GCP?

Database Services supported by GCP are as follows:

• Firestore
• Firebase Realtime Database
• Memorystore
• Google Cloud Partner Services
• Bare Metal
• Cloud SQL
• Cloud Spanner
• Cloud Bigtable

See Also
Spring Batch Interview Questions
Apache Camel Interview Questions
JBoss Fuse Interview Questions
Drools Interview Questions
Java 8 Interview Questions