Sample Questions and Answers
How does BigQuery integrate with Cloud Identity and Access Management (IAM) to secure data?
A) IAM provides granular permissions on datasets, tables, and views, allowing control over who can view, query, or administer data.
B) IAM is not supported.
C) IAM controls only storage access.
D) Permissions cannot be set by user.
Answer: A
Explanation: IAM is the fundamental access control layer in Google Cloud.
What is a Data Catalog in Google Cloud, and how does it complement BigQuery?
A) Data Catalog is a metadata management service that helps discover, manage, and govern data assets, integrating with BigQuery to provide data lineage, tagging, and search.
B) It stores data permanently.
C) Data Catalog is unrelated to BigQuery.
D) It replaces BigQuery.
Answer: A
Explanation: Data Catalog improves data governance and discoverability.
Describe the process and benefits of using BigQuery ML’s AutoML Tables feature.
A) AutoML Tables automates feature engineering and model selection from tabular data within BigQuery, allowing non-experts to build high-quality models without writing code.
B) AutoML requires external tools.
C) AutoML Tables only supports image data.
D) Models cannot be deployed in BigQuery.
Answer: A
Explanation: AutoML Tables democratizes ML with automated workflows inside BigQuery.
How do quotas and limits affect BigQuery usage, and how can you manage them?
A) BigQuery imposes quotas on query usage, API requests, and storage. Monitor quota usage via Cloud Console and request increases or optimize workloads to stay within limits.
B) Quotas are unlimited.
C) Limits cannot be adjusted.
D) Quotas apply only to storage.
Answer: A
Explanation: Quotas prevent abuse and ensure fair use, requiring management in large deployments.
What is the difference between a “logical” and “physical” schema in BigQuery, and why is this distinction important?
A) Logical schema defines the structure (fields, types, nested fields) from the user perspective. Physical schema refers to how data is stored physically in columnar format for performance optimization.
B) Both terms mean the same.
C) BigQuery does not have physical schema.
D) Logical schema controls storage allocation.
Answer: A
Explanation: Understanding this helps optimize schema design and query performance.
When should you use approximate aggregation functions in BigQuery, and what are their trade-offs?
A) Use approximate functions like APPROX_QUANTILES or APPROX_COUNT_DISTINCT on large datasets to speed up queries and reduce cost, at the expense of some precision.
B) Approximate functions always produce exact results.
C) They are slower than exact functions.
D) Approximate functions do not work with nested data.
Answer: A
Explanation: Approximate functions offer trade-offs between accuracy and performance.
How do you configure BigQuery to comply with data residency and regulatory requirements?
A) Use location settings to store datasets in specific geographic regions and apply IAM policies and encryption controls consistent with regulatory policies.
B) Data location is random.
C) BigQuery only stores data in US.
D) No configuration needed.
Answer: A
Explanation: Location control is critical for compliance with regulations like GDPR.
What is the significance of BigQuery’s audit logs, and how can they be used?
A) Audit logs record user activity, such as data access and changes, enabling security monitoring, compliance audits, and troubleshooting.
B) Logs are not available.
C) Logs only record failed queries.
D) Logs are deleted immediately.
Answer: A
Explanation: Logs provide accountability and traceability in data operations.
Explain the concept of “schema on read” and “schema on write.” Which approach does BigQuery use?
A) BigQuery uses “schema on write,” requiring data schema defined when loading or streaming data, which enables optimized storage and query performance.
B) Schema on read means schema is applied at query time.
C) BigQuery uses schema on read.
D) Both are identical.
Answer: A
Explanation: Schema on write enforces structure upfront for efficient querying.
How does BigQuery handle missing or null data in nested and repeated fields?
A) Null values can appear in nested fields; repeated fields can be empty arrays. BigQuery supports SQL functions to handle such data cleanly.
B) Nulls are not supported.
C) Missing data causes errors.
D) Nested fields cannot be null.
Answer: A
Explanation: Handling nulls and missing data is essential for robust queries.
What is the role of BigQuery ML model export, and what formats are supported?
A) Models built in BigQuery ML can be exported to TensorFlow SavedModel format for deployment in other environments.
B) Models cannot be exported.
C) Exported models are in CSV format.
D) Export only supports JSON.
Answer: A
Explanation: Exporting models enables integration with other ML platforms.
How can you automate BigQuery data pipeline deployments and schema changes?
A) Use Infrastructure-as-Code tools like Terraform or Deployment Manager to define BigQuery resources and manage changes systematically.
B) BigQuery cannot be automated.
C) Only manual deployments allowed.
D) Schema changes are automatic.
Answer: A
Explanation: Automation ensures repeatability and reduces errors.
Describe the key components of a typical ELT pipeline involving BigQuery.
A) Extract data from sources, Load raw data into BigQuery, Transform data within BigQuery using SQL or BigQuery ML, then serve results for analytics.
B) BigQuery does not support ELT.
C) Transform before loading.
D) Extract is not necessary.
Answer: A
Explanation: ELT leverages BigQuery’s scalable processing for transformations.
What are the benefits of using Data Studio with BigQuery?
A) Data Studio offers easy, interactive visualization of BigQuery datasets with live query capability and rich dashboarding, enabling business users to explore data without SQL.
B) Data Studio only supports static files.
C) BigQuery data cannot be visualized.
D) Data Studio requires exporting data.
Answer: A
Explanation: Data Studio empowers users with accessible BI tools on top of BigQuery.
How does BigQuery handle schema changes when querying external data sources via federated queries?
A) Schema changes in external sources must be managed separately; BigQuery does not enforce schema evolution on external data, requiring careful source management.
B) Schema updates propagate automatically.
C) BigQuery caches external schemas.
D) Schema is ignored in federated queries.
Answer: A
Explanation: External data management requires coordination for consistency.
Explain how BigQuery ensures high availability and disaster recovery.
A) BigQuery stores data redundantly across multiple zones in a region, provides automatic failover, and offers point-in-time recovery features.
B) Availability is manual.
C) Data is stored on a single server.
D) Recovery requires manual backups.
Answer: A
Explanation: BigQuery’s cloud-native architecture ensures resilience.
What are the limitations of BigQuery’s user-defined functions (UDFs) and scripting capabilities?
A) UDFs have execution time limits and do not support external calls. Scripting supports procedural logic but lacks complex control flow features found in traditional databases.
B) UDFs can run indefinitely.
C) Scripting replaces all SQL.
D) No limits on UDFs.
Answer: A
Explanation: Limitations ensure predictable resource usage.
How do partitioned tables affect query cost in BigQuery?
A) Queries on partitioned tables scan only relevant partitions, reducing data scanned and query cost compared to scanning entire tables.
B) Partitioning increases cost.
C) Partitioning is irrelevant to cost.
D) Partitioning disables querying.
Answer: A
Explanation: Partition pruning is a key cost optimization.
What is the difference between BigQuery’s on-demand pricing and flat-rate pricing models?
A) On-demand charges per query data scanned. Flat-rate charges a fixed price for dedicated slots, offering predictable costs for heavy workloads.
B) On-demand is cheaper for all use cases.
C) Flat-rate is pay per query.
D) Both models are identical.
Answer: A
Explanation: Pricing choice depends on workload patterns.
How can BigQuery ML models be evaluated for accuracy and fairness?
A) Use built-in evaluation metrics (e.g., precision, recall, AUC) and fairness assessments, and monitor model bias via audit datasets.
B) Evaluation is not supported.
C) Accuracy is always 100%.
D) Fairness is irrelevant.
Answer: A
Explanation: Responsible ML requires thorough evaluation.
Explain the role of BigQuery Omni and its benefits for multi-cloud data analysis.
A) BigQuery Omni enables querying data across multiple clouds (AWS, Azure) without moving data, providing unified analytics.
B) Omni is limited to GCP.
C) Data must be copied before querying.
D) Omni replaces BigQuery.
Answer: A
Explanation: Omni supports hybrid and multi-cloud strategies.
How do you monitor BigQuery query performance and diagnose slow queries?
A) Use Query Execution Details in Cloud Console, Query Plan explanation, and Stackdriver Logging to analyze performance bottlenecks and optimize queries.
B) No monitoring tools are available.
C) Performance is always optimal.
D) Diagnosis requires external tools.
Answer: A
Explanation: Monitoring tools are crucial for tuning.
Reviews
There are no reviews yet.