PDE practice questions 261–270

1. Question 261 · 340 in bank

   Your company is loading comma-separated values (CSV) files into Google BigQuery. The data is fully imported successfully; however, the imported data is not matching byte-to-byte to the source file. What is the most likely cause of this problem?

   • AThe CSV data loaded in BigQuery is not flagged as CSV.
   • BThe CSV data has invalid rows that were skipped on import.
   • CThe CSV data loaded in BigQuery is not using BigQuery's default encoding.
   • DThe CSV data has not gone through an ETL phase before loading into BigQuery.

   Show answer

   Answer: C

   Explanation

   Answer : C : " If you don't specify an encoding, or if you specify UTF-8 encoding when the CSV file is not UTF-8 encoded, BigQuery attempts to convert the data to UTF-8. Generally, your data will be loaded successfully, but it may not match byte-for-byte what you expect." https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv#details_of_loading_csv_data

   Source: Adapted from community discussion — YAS007

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

2. Question 262 · 340 in bank

   Select 2

   Your company produces 20,000 files every hour. Each data file is formatted as a comma separated values (CSV) file that is less than 4 KB. All files must be ingested on Google Cloud Platform before they can be processed. Your company site has a 200 ms latency to Google Cloud, and your Internet connection bandwidth is limited as 50 Mbps. You currently deploy a secure FTP (SFTP) server on a virtual machine in Google Compute Engine as the data ingestion point. A local SFTP client runs on a dedicated machine to transmit the CSV files as is. The goal is to make reports with data from the previous day available to the executives by 10:00 a.m. each day. This design is barely able to keep up with the current volume, even though the bandwidth utilization is rather low. You are told that due to seasonality, your company expects the number of files to double for the next three months. Which two actions should you take? (Choose two.)

   • AIntroduce data compression for each file to increase the rate file of file transfer.
   • BContact your internet service provider (ISP) to increase your maximum bandwidth to at least 100 Mbps.
   • CRedesign the data ingestion process to use gsutil tool to send the CSV files to a storage bucket in parallel.
   • DAssemble 1,000 files into a tape archive (TAR) file. Transmit the TAR files instead, and disassemble the CSV files in the cloud upon receiving them.
   • ECreate an S3-compatible storage endpoint in your network, and use Google Cloud Storage Transfer Service to transfer on-premises data to the designated storage bucket.

   Show answer

   Answer: CD

   Explanation

   E cannot be: Transfer Service is recommended for 300mbps or faster https://cloud.google.com/storage-transfer/docs/on-prem-overview Bandwidth is not an issue, so B is not an answer Cloud Storage loading gets better throughput the larger the files are. Therefore making them smaller with compression does not seem a solution. -m option to do parallel work is recommended. Therefore A is not and C is an answer. https://medium.com/@duhroach/optimizing-google-cloud-storage-small-file-upload-performance-ad26530201dc That leaves D as the other option. It is true you cannot user tar directly with gsutil, but you can load the tar file to Cloud Storage, move the file to a Compute Engine instance with Linux, use tar to split files and copy them back to Cloud Storage. Batching many files in a larger tar will improve Cloud Storage throughput. So, given the alternatives, I think answer is CD

   Source: Adapted from community discussion — Toto2020

   References

   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation

   Next question

3. Question 263 · 340 in bank

   An external customer provides you with a daily dump of data from their database. The data flows into Google Cloud Storage GCS as comma-separated values (CSV) files. You want to analyze this data in Google BigQuery, but the data could have rows that are formatted incorrectly or corrupted. How should you build this pipeline?

   • AUse federated data sources, and check data in the SQL query.
   • BEnable BigQuery monitoring in Google Stackdriver and create an alert.
   • CImport the data into BigQuery using the gcloud CLI and set max_bad_records to 0.
   • DRun a Google Cloud Dataflow batch pipeline to import the data into BigQuery, and push errors to another dead-letter table for analysis.

   Show answer

   Answer: D

   Explanation

   The answer is D. An ETL pipeline will be implemented for this scenario. Check out handling invalid inputs in cloud data flow https://cloud.google.com/blog/products/gcp/handling-invalid-inputs-in-dataflow ParDos . . . and don’ts: handling invalid inputs in Dataflow using Side Outputs as a “Dead Letter” file

   Source: Adapted from community discussion — Radhika7983

   References

   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation

   Next question

4. Question 264 · 340 in bank

   Select 3

   You are choosing a NoSQL database to handle telemetry data submitted from millions of Internet-of-Things (IoT) devices. The volume of data is growing at 100 TB per year, and each data entry has about 100 attributes. The data processing pipeline does not require atomicity, consistency, isolation, and durability (ACID). However, high availability and low latency are required. You need to analyze the data by querying against individual fields. Which three databases meet your requirements? (Choose three.)

   • ARedis
   • BHBase
   • CMySQL
   • DMongoDB
   • ECassandra
   • FHDFS with Hive

   Show answer

   Answer: BDE

   Explanation

   Answer is BDE - A. Redis - Redis is an in-memory non-relational key-value store. Redis is a great choice for implementing a highly available in-memory cache to decrease data access latency, increase throughput, and ease the load off your relational or NoSQL database and application. Since the question does not ask cache, A is discarded. B. HBase - Meets reqs C. MySQL - they do not need ACID, so not needed. D. MongoDB - Meets reqs E. Cassandra - Apache Cassandra is an open source NoSQL distributed database trusted by thousands of companies for scalability and high availability without compromising performance. Linear scalability and proven fault-tolerance on commodity hardware or cloud infrastructure make it the perfect platform for mission-critical data. F. HDFS with Hive - Hive allows users to read, write, and manage petabytes of data using SQL. Hive is built on top of Apache Hadoop, which is an open-source framework used to efficiently store and process large datasets. As a result, Hive is closely integrated with Hadoop, and is designed to work quickly on petabytes of data. HIVE IS NOT A DATABSE.

   Source: Adapted from community discussion — awssp12345

   References

   • Google Cloud documentation

   Next question

5. Question 265 · 340 in bank

   Select 3

   You are training a spam classifier. You notice that you are overfitting the training data. Which three actions can you take to resolve this problem? (Choose three.)

   • AGet more training examples
   • BReduce the number of training examples
   • CUse a smaller set of features
   • DUse a larger set of features
   • EIncrease the regularization parameters
   • FDecrease the regularization parameters

   Show answer

   Answer: ACE

   Explanation

   To address the problem of overfitting in training a spam classifier, you should consider the following three actions: A. Get more training examples: Why: More training examples can help the model generalize better to unseen data. A larger dataset typically reduces the chance of overfitting, as the model has more varied examples to learn from. C. Use a smaller set of features: Why: Reducing the number of features can help prevent the model from learning noise in the data. Overfitting often occurs when the model is too complex for the amount of data available, and having too many features can contribute to this complexity. E. Increase the regularization parameters: Why: Regularization techniques (like L1 or L2 regularization) add a penalty to the model for complexity. Increasing the regularization parameter will strengthen this penalty, encouraging the model to be simpler and thus reducing overfitting.

   Source: Adapted from community discussion — TVH_Data_Engineer

   References

   • Google Cloud documentation

   Next question

6. Question 266 · 340 in bank

   You are implementing security best practices on your data pipeline. Currently, you are manually executing jobs as the Project Owner. You want to automate these jobs by taking nightly batch files containing non-public information from Google Cloud Storage, processing them with a Spark Scala job on a Google Cloud Dataproc cluster, and depositing the results into Google BigQuery. How should you securely run this workload?

   • ARestrict the Google Cloud Storage bucket so only you can see the files
   • BGrant the Project Owner role to a service account, and run the job with it
   • CUse a service account with the ability to read the batch files and to write to BigQuery
   • DUse a user account with the Project Viewer role on the Cloud Dataproc cluster to read the batch files and write to BigQuery

   Show answer

   Answer: C

   Explanation

   A is wrong, if only I can see the bucket no automation is possible, besides, also needs launch the dataproc job B is too much, does not follow the security best practices C has one point missing…you need to submit dataproc jobs. In D viewer role will not be able to submit dataproc jobs, the rest is ok Thus….the only one that would work is B! BUT this service account has too many permissions. Should have dataproc editor, write big query and read from bucket

   Source: Adapted from community discussion — digvijay

   References

   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation

   Next question

7. Question 267 · 340 in bank

   You are using Google BigQuery as your data warehouse. Your users report that the following simple query is running very slowly, no matter when they run the query: SELECT country, state, city FROM [myproject:mydataset.mytable] GROUP BY country You check the query plan for the query and see the following output in the Read section of Stage:1:

   

   What is the most likely cause of the delay for this query?

   • AUsers are running too many concurrent queries in the system
   • BThe [myproject:mydataset.mytable] table has too many partitions
   • CEither the state or the city columns in the [myproject:mydataset.mytable] table have too many NULL values
   • DMost rows in the [myproject:mydataset.mytable] table have the same value in the country column, causing data skew

   Show answer

   Answer: D

   Explanation

   BTW, how is the query even syntactically valid? It has non aggregated columns in the SELECT part of the query. That query will not run in the first place, unless I'm missing something.

   Source: Adapted from community discussion — Paul_Oprea

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

8. Question 268 · 340 in bank

   Your globally distributed auction application allows users to bid on items. Occasionally, users place identical bids at nearly identical times, and different application servers process those bids. Each bid event contains the item, amount, user, and timestamp. You want to collate those bid events into a single location in real time to determine which user bid first. What should you do?

   • ACreate a file on a shared file and have the application servers write all bid events to that file. Process the file with Apache Hadoop to identify which user bid first.
   • BHave each application server write the bid events to Cloud Pub/Sub as they occur. Push the events from Cloud Pub/Sub to a custom endpoint that writes the bid event information into Cloud SQL.
   • CSet up a MySQL database for each application server to write bid events into. Periodically query each of those distributed MySQL databases and update a master MySQL database with bid event information.
   • DHave each application server write the bid events to Google Cloud Pub/Sub as they occur. Use a pull subscription to pull the bid events using Google Cloud Dataflow. Give the bid for each item to the user in the bid event that is processed first.

   Show answer

   Answer: D

   Explanation

   I'd go with B: real-time is requested, and the only scenario for real time (in the 4 presented) is the use of pub/sub with push.

   Source: Adapted from community discussion — jvg637

   References

   • Google Cloud documentation

   Next question

9. Question 269 · 340 in bank

   Select 2

   Your organization has been collecting and analyzing data in Google BigQuery for 6 months. The majority of the data analyzed is placed in a time-partitioned table named events_partitioned. To reduce the cost of queries, your organization created a view called events, which queries only the last 14 days of data. The view is described in legacy SQL. Next month, existing applications will be connecting to BigQuery to read the events data via an ODBC connection. You need to ensure the applications can connect. Which two actions should you take? (Choose two.)

   • ACreate a new view over events using standard SQL
   • BCreate a new partitioned table using a standard SQL query
   • CCreate a new view over events_partitioned using standard SQL
   • DCreate a service account for the ODBC connection to use for authentication
   • ECreate a Google Cloud Identity and Access Management (Cloud IAM) role for the ODBC connection and shared ג€eventsג€

   Show answer

   Answer: CD

   Explanation

   C = A standard SQL query cannot reference a view defined using legacy SQL syntax. D = For the ODBC drivers is needed a service account which will get a standard Bigquery role.

   Source: Adapted from community discussion — jvg637

   References

   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation

   Next question

10. Question 270 · 340 in bank

    You have enabled the free integration between Firebase Analytics and Google BigQuery. Firebase now automatically creates a new table daily in BigQuery in the format app_events_YYYYMMDD. You want to query all of the tables for the past 30 days in legacy SQL. What should you do?

    • AUse the TABLE_DATE_RANGE function
    • BUse the WHERE_PARTITIONTIME pseudo column
    • CUse WHERE date BETWEEN YYYY-MM-DD AND YYYY-MM-DD
    • DUse SELECT IF.(date >= YYYY-MM-DD AND date <= YYYY-MM-DD

    Show answer

    Answer: A

    Explanation

    The TABLE_DATE_RANGE function in BigQuery is a table wildcard function that can be used to query a range of daily tables. It takes two arguments: a table prefix and a date range. The table prefix is the beginning of the table names, and the date range is the start and end dates of the tables to be queried. The TABLE_DATE_RANGE function will expand to cover all tables in the dataset that match the table prefix and are within the date range. For example, if you have a dataset that contains daily tables named my_table_20230804, my_table_20230805, and my_table_20230806, you could use the TABLE_DATE_RANGE function to query all of the tables in the dataset between August 4, 2023 and August 6, 2023 as follows: SELECT * FROM TABLE_DATE_RANGE('my_table_', '2023-08-04', '2023-08-06');

    Source: Adapted from community discussion — Nirca

    References

    • Google Cloud documentation
    • Google Cloud documentation

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

PDE practice, question 261. 340 questions available.