View Full Databricks Certified Data Engineer Professional Exam Dumps and Practice Test Dumps

 

Question 381. Which Spark SQL function returns the position of the first occurrence of a substring within a string?

1) instr()

2) substring()

3) locate()

4) regexp_extract()

Answer: 1) instr()

Explanation:

The instr() function searches for a specified substring within a string and returns the position of its first occurrence. It is useful when a data pipeline needs to determine whether a particular pattern exists at a known character position. For example, it can help identify where a delimiter or keyword begins within a text field. The returned position can then be used with other string functions for further processing. instr() operates directly on string expressions and is commonly useful when working with semi-structured or text-heavy datasets.

Question 382. Which function returns the number of elements in an array or characters in a string?

1) size()

2) length()

3) count()

4) cardinality()

Answer: 1) size()

Explanation:

The size() function is commonly used to determine the number of elements in an array or map. It is particularly useful when processing nested data because it provides a simple way to measure collection size without exploding the structure. For example, a data engineer can use size() to identify records containing unusually large arrays or to create conditions based on the number of nested values. This is different from string-oriented functions that measure characters. Using collection functions appropriately helps maintain efficient transformations when working with complex Spark SQL data types.

Question 383. Which function returns the number of characters in a string?

1) length()

2) size()

3) count()

4) array_size()

Answer: 1) length()

Explanation:

The length() function returns the length of a string expression. It is useful for validating text fields, identifying unexpectedly long values, or applying data-quality rules. For example, a pipeline may use length() to verify that an identifier contains an expected number of characters before loading the record into a curated table. The function is focused on the string value rather than counting rows or array elements. In data engineering workflows, this makes length() useful for profiling and validating textual columns before downstream processing.

Question 384. Which function removes leading and trailing whitespace from a string?

1) trim()

2) replace()

3) translate()

4) regexp_extract()

Answer: 1) trim()

Explanation:

The trim() function removes leading and trailing whitespace from a string. It is frequently used during data cleansing because source systems may contain unwanted spaces around identifiers, names, or categorical values. Removing these spaces can prevent incorrect comparisons and improve consistency during joins and filtering. For example, values such as ” Customer “ can be normalized before being compared with another dataset. trim() changes the surrounding whitespace while leaving the meaningful content inside the string intact, making it useful as an early cleansing step in transformation pipelines.

Question 385. Which function converts a string to lowercase in Spark SQL?

1) lower()

2) upper()

3) initcap()

4) translate()

Answer: 1) lower()

Explanation:

The lower() function converts alphabetic characters in a string to lowercase. It is commonly used to normalize text before comparisons, filtering, deduplication, or joins. For example, customer values such as “ABC@EXAMPLE.COM” and “abc@example.com” can be standardized to the same lowercase representation. Applying consistent casing can reduce mismatches caused solely by capitalization differences. The function operates on the string expression and returns the transformed value, allowing it to be incorporated directly into DataFrame transformations or SQL queries without changing the original source column.

Question 386. Which function converts a string to uppercase?

1) upper()

2) lower()

3) initcap()

4) translate()

Answer: 1) upper()

Explanation:

The upper() function converts alphabetic characters in a string to uppercase. It can be used when a standardized uppercase representation is required for reporting, comparison, or data validation. For example, codes from multiple source systems can be converted to uppercase before being compared. Standardizing case helps prevent logically identical values from being treated as different values solely because one source uses lowercase and another uses uppercase. The function returns a transformed string expression and can be applied within SQL statements or PySpark DataFrame transformations.

Question 387. Which function replaces occurrences of a specified substring with another string?

1) replace()

2) split()

3) translate()

4) regexp_replace()

Answer: 1) replace()

Explanation:

The replace() function substitutes occurrences of a specified substring with another string. It is useful when the required replacement is literal rather than based on a regular-expression pattern. For example, a data pipeline can replace a particular unwanted character sequence or standardize a known textual value. Because the operation directly targets a specified substring, it is straightforward for deterministic cleansing tasks. When more complex pattern-based replacements are required, a regular-expression function may be more appropriate. replace() is therefore useful for simple and predictable string transformations.

Question 388. Which function replaces text using a regular-expression pattern?

1) regexp_replace()

2) replace()

3) regexp_extract()

4) split()

Answer: 1) regexp_replace()

Explanation:

The regexp_replace() function replaces text that matches a regular-expression pattern. It is useful when data contains variable formats that cannot be handled effectively with a simple literal replacement. For example, a pipeline can remove punctuation, normalize repeated whitespace, or replace groups of characters matching a defined pattern. The regular expression determines which portions of the input are modified. This makes regexp_replace() particularly useful for complex cleansing operations involving inconsistent source data. Careful pattern design is important because an overly broad expression can replace unintended content.

Question 389. Which function extracts a substring based on a regular-expression pattern?

1) regexp_extract()

2) regexp_replace()

3) split()

4) substring_index()

Answer: 1) regexp_extract()

Explanation:

The regexp_extract() function extracts a portion of a string that matches a specified regular-expression pattern and capture group. It is useful when structured information is embedded inside free-form text. For example, a pipeline can extract a product code, identifier, or other patterned value from a larger string. The function provides more flexibility than simple substring operations because the desired content can be described through a regular expression. This makes it valuable for parsing semi-structured text before storing the extracted information in structured Delta tables.

Question 390. Which function splits a string into an array using a specified delimiter or regular expression?

1) split()

2) explode()

3) array_join()

4) substring()

Answer: 1) split()

Explanation:

The split() function divides a string into an array using a specified delimiter or regular-expression pattern. It is useful when source data stores multiple values inside one text field. For example, a comma-separated list of product categories can be converted into an array so that each category can later be processed individually. After splitting, other array functions can be applied without requiring the data to remain as a single string. This is a common preparation step when transforming delimited source fields into structured or nested representations.

Question 391. Which function returns a portion of a string based on a starting position and length?

1) substring()

2) split()

3) slice()

4) regexp_extract()

Answer: 1) substring()

Explanation:

The substring() function extracts a portion of a string based on a starting position and a specified length. It is useful when the desired value occupies a predictable location within a source field. For example, a fixed-format identifier may contain a region code in its first few characters and a transaction code afterward. substring() can isolate those components without using regular expressions. This function is most appropriate when the input follows a stable positional structure. For variable patterns, regular-expression functions may provide greater flexibility.

Question 392. Which function returns the portion of a string before or after a specified delimiter based on an occurrence count?

1) substring_index()

2) substring()

3) split()

4) locate()

Answer: 1) substring_index()

Explanation:

The substring_index() function returns a portion of a string based on a specified delimiter and occurrence count. It is useful when values contain predictable delimiters, such as domain names, file paths, or hierarchical identifiers. For example, a string containing multiple segments separated by periods can be processed to retrieve the required portion without manually splitting the entire value. The occurrence parameter determines how many delimiter occurrences are considered. This makes substring_index() convenient for extracting portions of structured strings while keeping the transformation relatively simple.

Question 393. Which function returns the first element of an array when an index is supplied?

1) element_at()

2) array_position()

3) slice()

4) get_json_object()

Answer: 1) element_at()

Explanation:

The element_at() function retrieves an element from an array using an index or retrieves a value from a map using a key. It is useful when nested data has a known position or key that must be accessed directly. For arrays, Spark SQL uses its documented indexing semantics, so data engineers should understand how positive and negative indexes are interpreted. For maps, the function provides direct key-based access. This avoids unnecessarily exploding an entire collection when only one specific nested value is required for downstream processing.

Question 394. Which function returns the position of a specified element within an array?

1) array_position()

2) element_at()

3) arrays_overlap()

4) array_contains()

Answer: 1) array_position()

Explanation:

The array_position() function returns the position of the first occurrence of a specified value within an array. It is useful when the order of nested values has meaning and a pipeline needs to determine where a particular element occurs. For example, it can identify the position of a selected category in an ordered list. If the value is not found, the function returns the documented no-match result. This differs from array_contains(), which only determines whether a value exists and does not provide its position.

Question 395. Which function determines whether an array contains a specific value?

1) array_contains()

2) array_position()

3) arrays_overlap()

4) element_at()

Answer: 1) array_contains()

Explanation:

The array_contains() function checks whether a specified value exists within an array. It returns a Boolean result that can be used in filtering conditions, data-quality checks, or conditional transformations. For example, a pipeline can determine whether an order contains a particular product category or whether a record includes a required tag. Unlike array_position(), it does not identify where the matching value occurs. This makes array_contains() appropriate when the requirement is simply to determine whether a particular element is present in the collection.

Question 396. Which function returns a portion of an array using a starting position and length?

1) slice()

2) element_at()

3) array_position()

4) array_except()

**Answer: 1) slice()

Explanation:

The slice() function returns a subset of an array based on a starting position and the number of elements to include. It is useful when only part of a collection is required for processing or analysis. For example, a pipeline may retain only the first several values from an ordered array rather than processing every element. This can simplify transformations involving nested data and reduce unnecessary downstream processing. slice() operates on the array structure itself, so the resulting value remains an array rather than becoming multiple DataFrame rows.

Question 397. Which function removes duplicate elements from an array while preserving the array structure?

1) array_distinct()

2) array_union()

3) array_except()

4) array_intersect()

**Answer: 1) array_distinct()

Explanation:

The array_distinct() function removes duplicate values from an array and returns an array containing distinct elements. It is useful when source systems produce repeated categories, tags, identifiers, or other nested values. Applying this function can simplify later comparisons and reduce redundant data inside each row. Unlike array_union(), which combines two arrays, array_distinct() works on a single array and focuses on eliminating repeated values. This makes it appropriate for normalizing nested collections before applying additional transformations, aggregations, or business rules.

Question 398. Which function returns the common values shared by two arrays while removing duplicates?

1) array_intersect()

2) array_union()

3) array_distinct()

4) array_except()

Answer: 1) array_intersect()

Explanation:

The array_intersect() function compares two arrays and returns the distinct values that occur in both. It is useful when data engineers need to identify shared attributes between two nested collections. For example, customer preference categories can be compared with product categories to identify common values. The function operates directly on arrays, avoiding the need to explode both collections and perform a separate relational operation. Because the result represents common values rather than all values from both inputs, it is appropriate for intersection-style comparisons within nested data.

Question 399. Which function combines the elements of multiple arrays positionally into structs?

1) arrays_zip()

2) zip_with()

3) array_union()

4) flatten()

Answer: 1) arrays_zip()

Explanation:

The arrays_zip() function combines multiple arrays by their positions and produces an array containing structs with the corresponding values. This is useful when separate arrays represent related attributes that must remain aligned. For example, an array of employee IDs can be paired positionally with an array of employee names. The resulting structures preserve the relationship between values at the same positions. This differs from array_union(), which combines values into one distinct collection, and from zip_with(), which applies a custom lambda expression to corresponding elements.

Question 400. Which Spark SQL function can remove null values from an array while applying a condition to each element?

1) filter()

2) transform()

3) exists()

4) forall()

**Answer: 1) filter()

Explanation:

The higher-order filter() function can be used to retain only array elements that satisfy a condition, including a condition that excludes null values. For example, a lambda expression can test whether each element is not null and return an array containing only valid values. This is useful for cleaning nested collections without exploding them into separate rows. The key distinction is that filter() returns a modified array, while exists() and forall() return Boolean results. transform() instead changes elements rather than deciding which elements should remain.