Prepare and test your skills
Prepare and test your skills
Free sample question, answered and explained below. The Associate Data Practitioner practice bank has 1,156 more like it. Try 10 free: instant score, no account.
A data analyst is using a BigQuery notebook (Colab Enterprise) to analyze sales performance across retail regions. In an earlier Python code cell, the analyst defined a numerical threshold:
sales_threshold = 10000
The analyst now wants to execute a query in a dedicated SQL cell to retrieve all records from sales_dataset.orders where order_total exceeds this value.
How should the analyst reference the Python variable inside the SQL cell query?
WHERE order_total > %sales_threshold%Wrapping variables in percent symbols (%var%) is the standard syntax for referencing environment variables in Windows command prompt scripts and batch files. It is also used in certain legacy reporting and business intelligence tools for parameter substitution.
%) is a wildcard operator used with the LIKE clause for string pattern matching (e.g., WHERE name LIKE 'A%').%sales_threshold% as a Python variable identifier. Attempting to use this syntax results in a GoogleSQL syntax validation error.% matches zero or more characters in string comparisons.% and %% for line and cell magics (e.g., %%bigquery), but individual variable references within modern Colab Enterprise SQL cells use { }.This answer would be appropriate when defining environment variables in a Windows batch script or using specific legacy data extraction utilities that require %variable% string interpolation.
While IPython SQL extensions historically used % for line magics, native SQL cells in Colab Enterprise interact with the Python namespace specifically via curly braces ({ }).
WHERE order_total > $sales_thresholdThe dollar sign prefix ($var or ${var}) is a syntax convention commonly used in Unix shell scripting, bash environments, Makefile configurations, and some template engines (such as Terraform or Cloud Data Fusion runtime macros) to denote environment variables or runtime macro substitutions.
$sales_threshold as a Python variable reference. BigQuery SQL will evaluate $sales_threshold as invalid SQL syntax, causing the query to fail with a parsing error.{ }) to interpolate Python variables and BigQuery DataFrames into SQL statements.!echo $sales_threshold in Jupyter notebook magic cells), but not in GoogleSQL cells.@param_name or ?, the dollar sign is not used for notebook variable binding in SQL cells.This syntax would be relevant if executing a terminal command in a notebook bash cell (e.g., using !echo $sales_threshold after setting an environment variable) or configuring runtime macro arguments in Cloud Data Fusion pipelines.
Students often confuse shell variable substitution ($VARIABLE) with notebook SQL variable substitution ({variable}). In Jupyter/Colab SQL cells, Python variables are always injected using single curly braces.
WHERE order_total > {sales_threshold}In BigQuery notebooks and Colab Enterprise, SQL cells provide native integration with the Python notebook environment. By enclosing a Python variable or a BigQuery DataFrame name in curly braces ({ }), users can dynamically inject Python state directly into GoogleSQL query statements.
{sales_threshold} in the SQL cell query evaluates the Python variable defined in the earlier cell and substitutes its numerical value (10000) into the WHERE clause before query dispatch.SELECT * FROM {df}) to iteratively refine analytical pipelines.This is the built-in, native GoogleSQL parameterization syntax supported by Colab Enterprise in Google Cloud console. It eliminates the need to instantiate a bigquery.Client() or construct formatted SQL strings in Python code cells.
WHERE order_total > <>Double angle brackets (<< >>) are typically used in template engines, documentation placeholders, or specific macro replacement tools to denote placeholders that must be replaced prior to code compilation or execution.
<< as a variable substitution marker. In SQL, angle brackets are either comparison operators (<, >, <>) or bitwise shift operators in certain dialects.<> into a SQL cell causes an immediate syntax error prior to query execution.This choice would be appropriate only if working with a custom template engine or text-replacement system configured to replace angle-bracket delimited tokens before submitting raw text to an API.
When working in integrated cloud notebooks like BigQuery Studio / Colab Enterprise, Google standardizes on Python-like format string syntax ({var}) to reference variables across different cell types.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated