Preparing and using data for analysis Study Guide — Professional Data Engineer

Preparing and Using Data for Analysis

This domain covers how a Professional Data Engineer transforms raw data into analyst-ready assets, enforces access controls, enables business intelligence tooling, and applies machine learning within the Google Cloud data stack. It accounts for 15% of the exam, meaning roughly 9–11 questions. Questions test practical decision-making: choosing the right SQL construct, picking the correct security mechanism, selecting the appropriate BigQuery ML model type, or wiring together a transformation workflow.

BigQuery SQL: Arrays, Structs, Window Functions, and UDFs

Arrays and Structs

ARRAY and STRUCT are BigQuery's semi-structured data types that allow nested and repeated fields without denormalizing into separate tables.

Key SQL mechanics:

• UNNEST(array_col) flattens an array into rows; used in FROM or JOIN.
• STRUCT(val1 AS field1, val2 AS field2) constructs a struct inline.
• ARRAY_AGG(expr) aggregates values into an array.
• Querying nested fields uses dot notation: order.customer.name.
• Querying repeated fields requires UNNEST or CROSS JOIN UNNEST.

SELECT order_id, item.product_name, item.quantity
FROM orders
CROSS JOIN UNNEST(line_items) AS item;

Common traps

• Forgetting that UNNEST in a FROM clause with a comma is shorthand for CROSS JOIN UNNEST—results multiply rows; analysts misread this as a bug.
• Using ARRAY_AGG without ORDER BY or LIMIT produces non-deterministic ordering—exam may ask about consistency.
• A STRUCT column is not queryable as a whole in WHERE; you must reference individual sub-fields.
• ARRAY<ARRAY<...>> is not allowed in BigQuery—arrays cannot be nested inside arrays directly (wrap in a STRUCT first).

Window Functions

Window functions compute values across a set of rows related to the current row without collapsing them into a single output row.

Syntax: function() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...)

Frame clauses: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running total) vs. RANGE BETWEEN (value-based range).

Common traps

• LAST_VALUE requires ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the actual last value; default frame cuts off at current row.
• ROW_NUMBER() vs RANK() on tie-breaking: exam questions ask which produces sequential numbers regardless of ties—answer is ROW_NUMBER().
• Window functions execute after WHERE and GROUP BY but before ORDER BY and LIMIT; you cannot filter on a window function result in the same WHERE clause—wrap in a subquery or CTE.

User-Defined Functions (UDFs)

UDFs extend BigQuery SQL with custom logic in SQL or JavaScript.

Persistent UDFs are stored in a dataset and reusable across queries. Temporary UDFs are defined in the query and scoped to that session.

Common traps

• JavaScript UDFs cannot use console.log or make network calls—sandboxed environment.
• UDFs that reference external GCS libraries must have the GCS bucket in the same region as the BigQuery dataset, or use multi-region.
• A SQL UDF calling another UDF counts against the nesting limit (max 6 levels of UDF chaining).

Authorized Views, Row/Column-Level Security, and Policy Tags

Authorized Views and Authorized Datasets

An authorized view allows a view in one dataset to query tables in another dataset without granting the view's users direct access to the source tables. You grant the view identity (serviceAccount or dataset) access at the source dataset level.

An authorized dataset (newer pattern) grants an entire dataset access to query tables in another dataset—useful when you have many views.

Decision flow for sharing data securely:

How to pick the right BigQuery data-sharing security mechanism

Row-Level Security

Row access policies filter rows returned to a user based on their identity. Created with:

CREATE ROW ACCESS POLICY region_filter
ON dataset.table
GRANT TO ("user:alice@example.com", "group:apac-analysts@example.com")
FILTER USING (region = 'APAC');

Key facts:

• Multiple policies are OR-combined for a user (they see the union of all matching policies).
• A user with no matching policy sees zero rows (default deny).
• Bypass: users with bigquery.rowAccessPolicies.create or the roles/bigquery.admin role see all rows.
• Row access policies apply at query time, not storage time.

Column-Level Security and Policy Tags

Policy tags (part of BigQuery Data Catalog / Dataplex) enforce column-level access. Workflow:

1. Create a taxonomy in Dataplex (formerly Data Catalog).
2. Add policy tags to the taxonomy (e.g., PII/SSN, PII/Email).
3. Tag columns in BigQuery schema with the policy tag.
4. Grant roles/datacatalog.categoryFineGrainedReader to users who may see the column.
5. Enable enforcement on the taxonomy.

Without the fine-grained reader role, queries that reference the tagged column return an error.

Common traps

• Policy tags require enforcement to be turned on on the taxonomy—just tagging a column without enabling enforcement does nothing.
• roles/bigquery.dataViewer alone does NOT bypass policy tags; you separately need categoryFineGrainedReader.
• An authorized view does not inherit the permissions of its creator at runtime; it runs as the view identity. If the source table has row access policies, they still apply unless the view is granted bypass.
• Row access policies are not supported on views—only on base tables.