Exam Professional Data Engineer topic 1 question 164 discussion

If we're rigorous, as we should because it's a professional exam, I think option B is incorrect because it's one-hot-encoding the "state" column, if the answer was "city" column, then I'd go for B. As this is not the case and I do not accept an spelling error like this in an official question, I would go for D.

One-hot encoding is a common technique used to handle categorical data in machine learning. This approach will transform the city name variable into a series of binary columns, one for each city. Each row will have a "1" in the column corresponding to the city it represents and "0" in all other city columns. This method is effective for linear regression models as it enables the model to use city data as a series of numeric, binary variables. BigQuery supports SQL operations that can easily implement one-hot encoding, thus minimizing the amount of coding required and efficiently preparing the data for the model.

This is B. It's easier to one hot in bigquery than to do it in datafusion and then import the values back into bigquery.

One-Hot Encoding: One-hot encoding is a common technique for handling categorical variables like city names in machine learning models. It transforms categorical data into a binary matrix, where each city becomes a separate column with binary values (0 or 1) indicating the presence or absence of that city. Least Amount of Coding: One-hot encoding in BigQuery is straightforward and can be accomplished with SQL. You can use SQL expressions to pivot the city names into separate columns and assign binary values based on the city's presence in the original data. Predictive Power: One-hot encoding retains the predictive power of city information while making it suitable for linear regression models, which require numerical input.

One hot encoding for state and binary values for each city will allow me to choose the B option.

I guess Option D loses the granularity of the city-level information, as multiple cities will be grouped into the same region and represented by the same number. This can result in a loss of important predictive information for your linear regression model. On the other hand, if we use one-hot encoding to create binary columns for each city. This method preserves the city-level information, allowing the model to capture the unique effects of each city on the likelihood of purchasing your company's products. Additionally, it can be done directly in BigQuery using SQL, which requires less coding and is more efficient.

One-hot encoding is a common technique used to represent categorical variables as binary columns. In this case, you can transform the city variable into multiple binary columns, with each column representing a specific city. This allows you to maintain the predictive city information while organizing the data in columns suitable for training and serving the linear regression model. By using SQL in BigQuery, you can perform the necessary transformations to implement one-hot encoding.

- A is wrong since it drops the city which is a key predictor. - C is wrong since we want to keep it simple, and not use Tensorflow here. - D is wrong since there is no specific reason to use Data Fusion, and also this encoding here is ordinal, which doesn't make sense for something non-quantitative such as cities - we want one-hot coding instead. Therefore, B must be the correct answer.

Cloud Datafusion: least amount of coding

A doesn't include the city column. C is not low code. D is not a one hot encoding, but an ordinal one on the city column. B applies a one hot encoding on the state column and a binary encoding on the city column, which works for me.

https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-auto-preprocessing#one_hot_encoding

D uses the least amount of coding... even if the model is not good. B encodes the "state", not the "city".

Manually bigquery ml does preprocessing for you however if one wants to do a manual processing one can use the ML.ONE_HOT_ENCODER function. It just acts as an analytical funciton.

B is the answer. https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-auto-preprocessing#one_hot_encoding One-hot encoding maps each category that a feature has to its own binary feature where 0 represents the absence of the feature and 1 represents the presence (known as a dummy variable) creating N new feature columns where N is the number of unique categories for the feature across the training table.

The Cloud Data Fusion method will add unecessary weights to categories with higher value labels, which will skew the model. The best practice for encoding nominal categorical data is to one-hot-encode them into binary values. That is conveniently done in BigQuery: https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-auto-preprocessing#one_hot_encoding

D Cloud Data Fusion is a fully managed, code-free data integration service that helps users efficiently build and manage ETL/ELT data pipelines.

I have the same feeling with @cloudmon so I compromised to answer [D]. In more detail, here is my reasoning: The requirement "maintaining the predictable variables" (a.k.a. city) makes: [A] obiously invalid [B] invalid, since it broadens the prediction to be state-dependent (all cities in particular state will be treated as the same variable). Additionally, one-hot encoding is not suitable for linear regression problems, dummy encoding (drop one) is better. Answer [C] doesn't satisfy the "least amount of coding" directive. Other than that (as far I understood by searcing the keyword tf.feature_column.categorical_column_with_vocabulary_list) the TensorFlow vocabulary list is another form of one-hot encoding. So it remains [D] which offers a visual interface but uses ordinal (or label) encoding which is far from ideal for regression problems.