Preparing Categorical Data for BigQuery ML Linear Regression
Short answer
B Use SQL in BigQuery to transform the state column using a one-hot encoding method, and make each city a column with binary values.
Question
name variable as a key predictive component. In order to train and serve the model, your data must be organized in columns. You want to prepare your data using
the least amount of coding while maintaining the predictable variables. What should you do?
Key Takeaways
One-hot encoding is the standard technique for converting nominal categorical data into binary columns for linear regression, avoiding the false ordinal relationships created by simple numeric mapping.
Learn how to handle categorical variables like city names in BigQuery ML linear regression using one-hot encoding via SQL, ensuring minimal code and accurate model predictions.
Common mistake: Choosing option D (ordinal encoding) is a common trap; assigning sequential numbers to cities implies a magnitude relationship (e.g., City 5 > City 1) that does not exist, leading to skewed model weights.
Expert Analysis
The question asks for the best method to prepare a categorical city variable for a BigQuery ML linear regression model, prioritizing minimal coding while maintaining predictive granularity. Linear regression models require numerical input; they cannot process raw text strings directly. Therefore, the categorical data must be transformed into a numerical format.
Option B is correct because one-hot encoding transforms each unique category (city) into its own binary column (0 or 1). This preserves the nominal nature of the data without imposing an arbitrary order, which is crucial for cities where no inherent ranking exists. BigQuery SQL provides built-in functions and auto-preprocessing capabilities for this, allowing implementation with minimal custom code.
Option A is incorrect because dropping the city variable removes a key predictive component specified in the scenario. Option C involves TensorFlow and external vocabulary files, which adds complexity and coding overhead, violating the "least amount of coding" constraint. Option D uses ordinal encoding (assigning numbers 1-5), which incorrectly implies that City 5 is greater than City 4, introducing false mathematical relationships that can skew the linear regression weights.
Official Reference
This link opens the official vendor documentation in a new tab.
Exam Strategy
When preparing data for machine learning models in exam scenarios, always identify whether the categorical variable is nominal or ordinal. For nominal data (like cities or colors), one-hot encoding is almost always the correct approach to avoid implying false order. Look for keywords like 'linear regression' which requires numerical inputs, and 'minimal coding' which often points to native SQL features rather than external tools like TensorFlow.
Frequently Asked Questions
Why can't I use raw city names in BigQuery ML?
What is the difference between one-hot and ordinal encoding?
Community Votes
68% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.
Discussion 39 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.
From public community threads 30 of 39
-
cajica 👍 10 Answered DIf 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.
-
MaxNRG 👍 7 Answered BOne-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.
-
barnac1es 👍 5 Answered BOne-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.
-
blathul 👍 5 Answered BOne-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.
-
NicolasN 👍 4 Answered DI 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.
Show 25 more comments
-
KC_go_reply 👍 4 Answered B- 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.
-
ovokpus 👍 4 Answered BThe 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
-
cloudmon 👍 4 Answered DThis is one of those examples in which none of the answers are actually the right way to do it, but D is the only one that may make some sense. B is wrong because it talks about transforming the STATE column. The actual correct way would be to transform/one-hot encode the CITY column (thus creating binary columns representing the city).
-
juliobs 👍 4 Answered DD uses the least amount of coding... even if the model is not good. B encodes the "state", not the "city".
-
knith66 👍 4 Answered BOne hot encoding for state and binary values for each city will allow me to choose the B option.
-
leandrors 👍 4 Answered DCloud Datafusion: least amount of coding
-
MaxNRG 👍 3A removes the city information completely, losing a key predictive component. C requires additional coding and infrastructure with TensorFlow and vocabulary files outside of what BigQuery already provides. D transforms the distinct city values into numeric regions, losing granularity of the city data. By using SQL within BigQuery to one-hot encode cities into multiple yes/no columns, the city data is maintained and formatted appropriately for the BigQuery ML linear regression model with minimal additional coding. This aligns with the requirements stated in the question.
-
AWSandeep 👍 3 Answered DD. Use Cloud Data Fusion to assign each city to a region that is labeled as 1, 2, 3, 4, or 5, and then use that number to represent the city in the model.
-
sergiomujica 👍 3I think it should say citiy instead of state... it is a typooi in the transcription of the question
-
Remi2021 👍 3 Answered Bkey is in 'binary value'. It says low code not 'no code'. B is right one.
-
tavva_prudhvi 👍 2I 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.
-
YorelNation 👍 2 Answered BComing for an econometric background i can assure you that for linear regression you can't encode a non ordinal feature as a sequence of number (awnser D). The most appropriate answer is B, this is how to proceed with non-ordinal categorical features in a linear regression context.
-
dconesoko 👍 2 Answered BManually 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.
-
YorelNation 👍 2And i discovered that BQ ML does the one-hot encoding for you: https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-auto-preprocessing
-
baimus 👍 2 Answered BThis is B. It's easier to one hot in bigquery than to do it in datafusion and then import the values back into bigquery.
-
mialll 👍 2 Answered Bhttps://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-auto-preprocessing#one_hot_encoding
-
damaldon 👍 2Ans. B https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-auto-preprocessing
-
MaxNRG 👍 2https://cloud.google.com/bigquery/docs/auto-preprocessing#one_hot_encoding
-
pluiedust 👍 2 Answered BI was also majoring in Econometrics and I think it is B
-
AWSandeep 👍 2Online points to D but not sure why B is incorrect.
-
carbonscave 👍 1 Answered BWhy B is correct The Math of Linear Regression: Linear regression equations calculate weights based on numbers. You cannot feed raw text strings (like "London" or "Tokyo") directly into the math. You must convert categorical text into numerical data. One-Hot Encoding: This is the structurally correct way to handle unordered categories. It pivots the data so that every city becomes its own distinct column containing a binary value (1 if the user is in that city, 0 if they are not). This ensures the model treats each city as an independent factor. Least Amount of Coding: Writing a CASE WHEN statement or using BigQuery ML's native ML.FEATURE_CROSS / automated string encoding directly within BigQuery SQL requires far less overhead than standing up an external processing pipeline.
-
Mohamed_Tareq 👍 1 Answered BB. Use SQL to one-hot encode the city column Converts categorical values into binary columns (0/1) Fully supported using BigQuery SQL No external tools needed Works perfectly with linear regression in BigQuery ML and off course not D since it is using 1,2,3,4,5 for representing the cities which mean different weight for each city based on its number i.e city 5 is greater some how than 4 and 4 than 3 and so on which may be not right in reality
-
tavva_prudhvi 👍 1While it's true that Cloud Data Fusion can simplify data integration tasks with a visual interface, it might not be the best choice in this specific scenario as using Cloud Data Fusion to assign each city to a region might result in a loss of important predictive information due to the grouping of cities
-
AmirNaik204 👍 1D. The Key is "linear regression model". Linear regression model is used if you are predicting something numeric. So option is to write a no code transform that assign each city a number and use the number in the model. Predict the number and translate back to its corresponding city.
-
vaga1 👍 1 Answered BA 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.
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.