Exam Professional Data Engineer topic 1 question 243 discussion

Option A is the correct choice because it retains all the original columns and specifically addresses the issue of null values in ‘feature1’ by replacing them with zeros, without altering any other columns or performing unnecessary calculations. This makes the data ready for use in BigQueryML without losing any important information. Option C is not the best choice because it includes the EXCEPT clause for the price and square_feet columns, which would exclude these columns from the results. This is not desirable since you need these columns for the machine learning model to predict the price per square foot

Correct answer is C. It both replace NULL with 0 and pass price per square foot of real estate.

It's the only one that: - computes the price per square foot of real estate. Note that "the training data has a column for the price and a column for the number of square feet" only. - fills NAs with zeros

Not worded well but the best answer I would think would be C since it has price per square foot but I understand the argument for A.

Let's step away from GCP for a minute. If price & square feet are already features, then there is no need for BigQuery ML to do any prediction. You'd want to predict price per square foot based on other fields like location, weather, etc. The first sentence in the question indicates that a machine learning team is preparing the data for model training. Therefore, C's query a fantastic preparation step. If this query for any other use case, then A would've been the answer.

To people saying you need price and square footage to predict price/sq. feet: you do not need an ML model then, you need a calculator. C is the correct answer because you want to predict price/sq. feet from the features EXCLUDING price and sq. footage.

Gemini told me C Here's why it's the best of the limited choices: Calculates price_per_sqft: It includes the calculation for the target variable your model needs. Handles Nulls: It uses IFNULL(feature1, 0) to replace nulls in feature1 with 0, similar to COALESCE. Most Comprehensive: While it excludes the original price, square_feet, and feature1 columns, it still retains any other columns that might be present in the training_data table.

it should be C.

This must be C, though the wording isn't great. If price and square foot are included in the data, they are either intended to be the target, in which case you need to create that target as per C, or if they are genuinely features, you DO NOT need a machine learning model. If you already know price and square feet, price per square foot is just price/ft2. You don't need ML to predict that, it's just a division. The only context this makes sense in is if they mean "price and square foot are the target, and feature1 is the predictive feature", which means C is correct. The removing nulls from feature1 and the creation of price per square foot is C.

Font Cloude 3.5 and GPT 4o, in theoy is better to keep the less amount of features, then price_per_sqft and feature1 cleaned is the best option

EXCEPT means it won't select that column.

C is not a valid answer. You are introducing a redundant variable, that could be valid, but removing from the dataset 2 variables that exactly influence in the predictions you are trying to make.

C is not a valid answer. You are introducing a redundant variable, that could be valid, but removing from the dataset 2 variables that exactly influence in the predictions you are trying to make.

Option C not only handles the null values in feature1 by replacing them with zeros (using IFNULL(feature1, 0) as feature1_cleaned), but it also creates a new feature price_per_sqft by dividing the price by the number of square feet (price/square_feet as price_per_sqft). This new feature directly corresponds to what your team wants to predict (the price per square foot of real estate), and could therefore be very useful for the machine learning model.

It should be C. "They want to predict the price per square foot of real estate. The training data has a column for the price and a column for the number of square feet." You need to create the column the model is going to predict.

Option A

I didn't get why they mentioned in the task price and square feet columns. Just to irritate us? Do we need to do something with these columns or just with column feature1?