To disable partitioning<\/span><\/p>\nAnswer: 1) To perform calculations across related rows without collapsing them into one row<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Window functions perform calculations across a set of related rows while retaining the individual rows in the result. They are useful for analytical operations such as ranking, running totals, comparisons with previous or next records, and calculating values within groups. Unlike a standard GROUP BY operation, a window function does not reduce all rows in a group to a single output row. A window specification can define partitioning and ordering rules that determine which records participate in each calculation. This makes window functions particularly valuable for detailed analytical transformations.<\/span><\/p>\nQuestion 222. Which window function can return the previous row’s value within an ordered group?<\/b><\/h3>\n
1)<\/b> lag()<\/span>
\n<\/span>2)<\/b> countAll()<\/span>
\n<\/span>3)<\/b> previousRowOnly()<\/span>
\n<\/span>4)<\/b> prior()<\/span><\/p>\nAnswer: 1) lag()<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The lag() window function allows Spark SQL to access a value from a preceding row within an ordered window. It is useful for comparing the current record with an earlier record, such as determining changes in account balances, comparing daily metrics, or identifying status transitions. The window specification normally defines how records are grouped and ordered before the previous value is retrieved. Unlike an aggregation that combines multiple rows into one result, lag() preserves the individual rows and adds the requested previous-row value alongside the current record.<\/span><\/p>\nQuestion 223. Which window function assigns a sequential row number within each window partition?<\/b><\/h3>\n
1)<\/b> row_number()<\/span>
\n<\/span>2)<\/b> sequence_id()<\/span>
\n<\/span>3)<\/b> record_number()<\/span>
\n<\/span>4)<\/b> partition_count()<\/span><\/p>\nAnswer: 2) row_number()<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The row_number() window function assigns a unique sequential number to each row within a defined window partition according to the specified ordering. It is commonly used for tasks such as selecting the latest record per customer, identifying the first occurrence of an event, or creating ordered rankings without ties receiving the same number. The numbering restarts for each partition. Because the ordering determines which row receives each number, the window specification should be designed carefully when deterministic results are required. row_number() is frequently useful in deduplication and record-selection workflows.<\/span><\/p>\nQuestion 224. What is the purpose of a common table expression (CTE) in SQL?<\/b><\/h3>\n
1)<\/b> To define a temporary named result that can be referenced within a query<\/span>
\n<\/span>2)<\/b> To permanently store a Delta table<\/span>
\n<\/span>3)<\/b> To create a compute cluster<\/span>
\n<\/span>4)<\/b> To encrypt source files<\/span><\/p>\nAnswer: 1) To define a temporary named result that can be referenced within a query<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A common table expression, or CTE, is created using the WITH clause and provides a temporary named query result that can be referenced later in the same SQL statement. CTEs can make complex transformations easier to read by separating them into logical stages. For example, an engineer might first filter and aggregate source data in one CTE and then join that result with another dataset. A CTE does not normally create a persistent table. It is primarily a query-structuring technique that improves readability and can simplify complicated SQL transformations.<\/span><\/p>\nQuestion 225. What is the purpose of the <\/b>DISTINCT<\/b> keyword in SQL?<\/b><\/h3>\n
1)<\/b> To remove duplicate result rows<\/span>
\n<\/span>2)<\/b> To increase table partitions<\/span>
\n<\/span>3)<\/b> To create a primary key automatically<\/span>
\n<\/span>4)<\/b> To sort records by timestamp<\/span><\/p>\nAnswer: 1) To remove duplicate result rows<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The DISTINCT keyword instructs SQL to return unique combinations of the selected columns rather than repeated identical rows. It is useful when duplicate values exist in a query result and only unique combinations are required. For example, selecting DISTINCT customer_id can produce one result for each unique customer identifier. DISTINCT may require additional processing because Spark must determine which records represent duplicate combinations. Engineers should therefore use it intentionally and understand the amount of data being processed. DISTINCT does not automatically create a constraint preventing duplicates from being inserted into the underlying table.<\/span><\/p>\nQuestion 226. What does the <\/b>explode()<\/b> function commonly do in Spark SQL?<\/b><\/h3>\n
1)<\/b> It creates separate rows from elements of an array or entries of a map<\/span>
\n<\/span>2)<\/b> It permanently expands a cluster<\/span>
\n<\/span>3)<\/b> It increases executor memory<\/span>
\n<\/span>4)<\/b> It removes nested columns<\/span><\/p>\nAnswer: 1) It creates separate rows from elements of an array or entries of a map<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The explode() function is commonly used to transform nested collection data into a more relational structure. When applied to an array, each element can become a separate output row while the other columns are repeated for each generated record. This is particularly useful when source data contains arrays of transactions, products, events, or other nested objects. Exploding a large collection can significantly increase the number of output rows, so engineers should consider the resulting data volume. The function is therefore useful for flattening nested structures before additional filtering or analytical processing.<\/span><\/p>\nQuestion 227. Which operation can remove duplicate records from a Spark DataFrame?<\/b><\/h3>\n
1)<\/b> dropDuplicates()<\/span>
\n<\/span>2)<\/b> removeCopies()<\/span>
\n<\/span>3)<\/b> uniqueRowsOnly()<\/span>
\n<\/span>4)<\/b> eliminateDuplicatesSQL()<\/span><\/p>\nAnswer: 1) dropDuplicates()<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The dropDuplicates() operation removes duplicate records from a DataFrame based on all columns or a specified subset of columns. When a subset is provided, Spark identifies records that have the same values for those selected fields and retains one record from each duplicate group. This can be useful in ingestion pipelines where repeated source events may occur. Engineers should define the duplicate criteria carefully because removing records based on inappropriate columns could discard legitimate data. For streaming workloads, deduplication can also involve state management and watermark considerations to control how long duplicate information is retained.<\/span><\/p>\nQuestion 228. Why are explicit schemas often preferred over automatic schema inference for production pipelines?<\/b><\/h3>\n
1)<\/b> They provide predictable data types and reduce unexpected schema interpretation<\/span>
\n<\/span>2)<\/b> They guarantee that source data never changes<\/span>
\n<\/span>3)<\/b> They eliminate the need for validation<\/span>
\n<\/span>4)<\/b> They prevent all malformed records<\/span><\/p>\nAnswer: 1) They provide predictable data types and reduce unexpected schema interpretation<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Explicit schemas define the expected structure and data types of incoming data instead of relying entirely on automatic inference. This provides greater predictability in production pipelines because the pipeline knows what types and fields it expects. Automatic inference can sometimes interpret values differently depending on the input files, sampling, or changes in source data. An explicit schema also makes data contracts easier to document and validate. It does not prevent source systems from changing, but it allows unexpected changes to be detected more consistently and handled according to the pipeline’s design.<\/span><\/p>\nQuestion 229. What is a common risk of relying entirely on schema inference for frequently changing source files?<\/b><\/h3>\n
1)<\/b> Different input files may result in inconsistent inferred schemas<\/span>
\n<\/span>2)<\/b> Spark cannot read any JSON data<\/span>
\n<\/span>3)<\/b> Delta tables become automatically corrupted<\/span>
\n<\/span>4)<\/b> All columns are always converted to integers<\/span><\/p>\nAnswer: 1) Different input files may result in inconsistent inferred schemas<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Schema inference determines data types and structure from available input data. When source files change over time, inferred results can potentially differ from earlier expectations. For example, a field that appears numeric in one batch might contain values that cause a different interpretation in another batch. This can create downstream compatibility or transformation problems. Production pipelines therefore often use controlled schemas or explicit schema evolution strategies. Schema inference can still be convenient during development and exploratory work, but production ingestion should account for changing source structures and data-quality requirements.<\/span><\/p>\nQuestion 230. What is the main purpose of a data quality expectation in a declarative pipeline?<\/b><\/h3>\n
1)<\/b> To define conditions that incoming or transformed data should satisfy<\/span>
\n<\/span>2)<\/b> To increase cluster size automatically<\/span>
\n<\/span>3)<\/b> To create cloud storage credentials<\/span>
\n<\/span>4)<\/b> To rename every target table<\/span><\/p>\nAnswer: 1) To define conditions that incoming or transformed data should satisfy<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Data quality expectations define rules that records are expected to satisfy during pipeline processing. Examples include requiring an identifier to be present, ensuring a numeric value is nonnegative, or validating that a date falls within an acceptable range. Expectations provide a structured mechanism for identifying records that do not meet defined quality requirements. Depending on the configured behavior, invalid records may be retained, dropped, or cause processing to fail. This makes expectations useful for embedding quality controls directly into data pipelines rather than relying entirely on separate validation processes.<\/span><\/p>\nQuestion 231. What is the main purpose of quarantining invalid records in a data pipeline?<\/b><\/h3>\n
1)<\/b> To isolate records that fail quality checks for later investigation<\/span>
\n<\/span>2)<\/b> To permanently hide all source data<\/span>
\n<\/span>3)<\/b> To increase the number of Spark workers<\/span>
\n<\/span>4)<\/b> To disable future validation<\/span><\/p>\nAnswer: 4) To isolate records that fail quality checks for later investigation<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Quarantining invalid records separates data that fails validation from records considered suitable for normal downstream processing. Instead of simply discarding problematic records, a pipeline can place them into a dedicated dataset where engineers or data owners can investigate the reason for failure. This can preserve potentially valuable information while preventing poor-quality records from contaminating analytical outputs. A quarantine process can also support monitoring and remediation workflows. The exact implementation varies, but the core concept is to isolate problematic records while maintaining visibility into what was rejected and why.<\/span><\/p>\nQuestion 232. Which file format is commonly used for semi-structured data and supports nested objects?<\/b><\/h3>\n
1)<\/b> JSON<\/span>
\n<\/span>2)<\/b> Plain CSV<\/span>
\n<\/span>3)<\/b> Fixed-width text only<\/span>
\n<\/span>4)<\/b> Basic bitmap<\/span><\/p>\nAnswer: 1) JSON<\/b><\/p>\n
Explanation:<\/b><\/p>\n
JSON is a commonly used semi-structured format capable of representing nested objects and arrays. This makes it useful for APIs, event streams, application logs, and other sources where records do not always fit a simple flat table structure. Spark can read JSON data and represent nested objects using complex data types such as structs, arrays, and maps. Engineers may then flatten or transform those structures using functions such as explode and field selection. Because JSON is flexible, production pipelines should still apply appropriate schema management and data-quality validation.<\/span><\/p>\nQuestion 233. What is a major advantage of Parquet over plain CSV for analytical workloads?<\/b><\/h3>\n
1)<\/b> Parquet supports efficient columnar access and compression<\/span>
\n<\/span>2)<\/b> CSV always requires a transaction log<\/span>
\n<\/span>3)<\/b> Parquet cannot store numeric values<\/span>
\n<\/span>4)<\/b> CSV automatically provides ACID transactions<\/span><\/p>\nAnswer: 1) Parquet supports efficient columnar access and compression<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Parquet is designed for analytical workloads and stores data in a columnar structure. This allows engines to read only the columns needed by a query rather than processing every field in every record. Parquet also supports efficient compression and encoding techniques, which can reduce storage and I\/O requirements. CSV is a simple text-based format that is widely supported but does not provide the same columnar storage characteristics. Delta Lake can use Parquet data files while adding transactional and table-management capabilities. Choosing an appropriate storage format is therefore an important part of data lake design.<\/span><\/p>\nQuestion 234. What does <\/b>append<\/b> mode generally do when writing data to a Delta table?<\/b><\/h3>\n
1)<\/b> Adds new records to the existing table<\/span>
\n<\/span>2)<\/b> Removes all existing records before writing<\/span>
\n<\/span>3)<\/b> Deletes the transaction log<\/span>
\n<\/span>4)<\/b> Changes the table into a view<\/span><\/p>\nAnswer: 1) Adds new records to the existing table<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Append mode adds incoming records to an existing target dataset instead of replacing the current contents. It is commonly used when processing new batches of events, transactions, or source files that should be added to previously ingested data. Append mode should be used when the incoming data is intended to represent additional records. If the same source batch can be processed multiple times, engineers should also consider idempotency and duplicate prevention. Append differs from overwrite mode, which replaces existing data according to the scope and semantics of the write operation.<\/span><\/p>\nQuestion 235. What is the purpose of the <\/b>overwrite<\/b> write mode?<\/b><\/h3>\n
1)<\/b> To replace existing target data according to the operation’s scope<\/span>
\n<\/span>2)<\/b> To add records without modifying existing data<\/span>
\n<\/span>3)<\/b> To create a Spark UDF<\/span>
\n<\/span>4)<\/b> To read only historical table versions<\/span><\/p>\nAnswer: 1) To replace existing target data according to the operation’s scope<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Overwrite mode is used when the incoming dataset should replace existing target data within the scope of the write operation. This can be useful for rebuilding a complete table or replacing selected partitions when the pipeline is intentionally designed for that behavior. Because overwrite can remove existing data, engineers should use it carefully and understand exactly what portion of the target will be replaced. It is fundamentally different from append mode, which preserves existing data and adds new records. Appropriate overwrite strategies can be useful for deterministic batch processing and controlled data refresh operations.<\/span><\/p>\nQuestion 236. Why can repartitioning before writing data be useful?<\/b><\/h3>\n
1)<\/b> It can help control the distribution and number of output partitions<\/span>
\n<\/span>2)<\/b> It automatically validates every business rule<\/span>
\n<\/span>3)<\/b> It permanently disables Spark optimization<\/span>
\n<\/span>4)<\/b> It guarantees zero shuffle activity<\/span><\/p>\nAnswer: 1) It can help control the distribution and number of output partitions<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Repartitioning before writing can influence how records are distributed among output partitions and therefore how many data files are generated. This can be useful when the current partition distribution would create poorly sized files or an undesirable physical layout. However, repartitioning generally involves a shuffle, which can be expensive for large datasets. Engineers should therefore use it when the resulting layout provides a meaningful benefit. The objective is not simply to maximize the number of partitions, but to achieve a practical distribution that supports efficient storage and downstream query processing.<\/span><\/p>\nQuestion 237. What is predicate pushdown most useful for when querying Parquet data?<\/b><\/h3>\n
1)<\/b> Reducing unnecessary data reads based on filter conditions<\/span>
\n<\/span>2)<\/b> Increasing the number of output columns<\/span>
\n<\/span>3)<\/b> Disabling compression<\/span>
\n<\/span>4)<\/b> Converting Parquet into XML<\/span><\/p>\nAnswer: 1) Reducing unnecessary data reads based on filter conditions<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Predicate pushdown allows filtering conditions to be applied as close to the storage layer as possible. With Parquet, the engine can use metadata and column-level information to avoid reading data that cannot satisfy the query predicate. This can reduce I\/O and improve query performance, particularly when only a small portion of a large dataset meets the filter. Predicate pushdown is different from partition pruning, although both can reduce unnecessary data access. Partition pruning eliminates entire partitions, while predicate pushdown can further reduce the data read within the partitions that remain relevant.<\/span><\/p>\nQuestion 238. What is the purpose of a broadcast threshold in Spark?<\/b><\/h3>\n
1)<\/b> It helps determine when a dataset may be small enough for broadcast-based joining<\/span>
\n<\/span>2)<\/b> It defines the maximum number of Delta columns<\/span>
\n<\/span>3)<\/b> It controls table retention periods<\/span>
\n<\/span>4)<\/b> It determines how many notebooks a user can create<\/span><\/p>\n