I85PREP
⌘ K
CDESS study materials
CDESS study resources

CDESS practice questions

One question at a time. Build your CDESS understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Data Engineering

In a data engineering context, which characteristic most precisely distinguishes a data lakehouse from a traditional data warehouse?

  1. It enforces schema-on-write before any data can be ingested.
  2. It requires all transformations to occur inside the storage engine itself.
  3. It combines open file storage with a transactional metadata layer supporting ACID operations. Correct answer.
  4. It stores data only in proprietary columnar formats optimized for SQL queries.

Explanation

A lakehouse pairs low-cost open storage formats such as Parquet with a table format and catalog that provide ACID transactions, time travel, and schema enforcement. A warehouse keeps data in proprietary or tightly managed storage and relies on schema-on-write loading. The defining trait of the lakehouse is that transactional layer over open files.

Question 2Data Engineering

A pipeline writes a stream of click events to a partitioned table where the partition key is the event date. Which partitioning choice best supports efficient downstream queries that filter by date?

  1. Partition by the full event payload hash.
  2. Partition by event date so that date filters prune irrelevant files. Correct answer.
  3. Partition by a random hash of the user ID.
  4. Partition by the ingestion timestamp at second granularity.

Explanation

Partition pruning lets the query engine skip files whose partition values cannot match the filter, which cuts scanned bytes and runtime. Partitioning on the same column used in common filters, here the event date, maximizes that benefit. High-cardinality or random keys produce excessive small partitions without pruning value.

Question 3Data Engineering

Which statement correctly describes the role of a schema registry in a streaming data platform?

  1. It replaces the message broker by persisting messages in a relational database.
  2. It centralizes versioned schemas and enforces compatibility rules between producers and consumers. Correct answer.
  3. It stores the raw event payloads so consumers can replay historical data.
  4. It schedules batch jobs that compact topic data on a fixed cadence.

Explanation

A schema registry holds versioned definitions and applies compatibility policies so that producers cannot publish breaking changes unnoticed. Consumers fetch the schema by ID to deserialize records correctly. It does not store payloads or replace the broker.

Question 4Data Engineering

An engineer needs to load only the rows changed since the previous nightly run from a source table that has an updated_at column. Which extraction strategy fits best?

  1. Extracting only rows where the primary key is even.
  2. Full table truncate-and-reload each night.
  3. Random sampling of rows on each run.
  4. Incremental extraction filtered on updated_at greater than the last watermark. Correct answer.

Explanation

Incremental extraction reads only rows newer than the stored high-water mark, reducing load and cost while keeping the target current. The watermark is persisted between runs so no changes are missed. Full reloads and arbitrary filters either waste resources or lose data.

Question 5Data Engineering

Which file format is columnar and typically supports predicate pushdown and efficient compression for analytical scans?

  1. Parquet Correct answer.
  2. CSV
  3. JSON Lines
  4. Plain text logs

Explanation

Parquet stores data by column with embedded statistics, so engines can skip row groups that fail a predicate and read only needed columns. That design yields strong compression and fast analytical scans. Row-oriented text formats lack these pruning and compression advantages.

Question 6Data Engineering

A batch job must be rerun without producing duplicate rows in the target table. Which write pattern achieves this?

  1. Use an idempotent MERGE or overwrite of the affected partition keyed on a business key. Correct answer.
  2. Write to a temporary directory and never reconcile with the target.
  3. Disable primary key constraints on the target before writing.
  4. Append every run's output to the target table.

Explanation

Idempotent writes make reruns safe by matching on a business key with MERGE or by replacing the exact partition the job owns. Repeating the job then converges to the same state instead of adding duplicates. Plain appends and unreconciled temp writes both risk duplication or data loss.

Question 7Data Engineering

Which practice best supports lineage and reproducibility for a transformation pipeline?

  1. Editing production SQL manually without version control.
  2. Overwriting source tables in place during transformation.
  3. Storing transformation code in version control with pinned dependency versions and recorded run metadata. Correct answer.
  4. Running transformations only from an engineer's laptop.

Explanation

Versioned code plus pinned dependencies and logged run metadata lets you reconstruct exactly which logic and inputs produced a given output. That traceability is the basis of lineage and reproducibility. Manual edits, local-only runs, and in-place overwrites destroy that audit trail.

Question 8Data Engineering

In a star schema, which table holds the quantitative measures and foreign keys to the dimension tables?

  1. The fact table Correct answer.
  2. The conformed dimension table
  3. The staging table
  4. The bridge table

Explanation

The fact table stores measurable events such as amounts or counts along with foreign keys referencing dimensions. Dimensions hold descriptive attributes used for filtering and grouping. Staging and bridge tables serve different pipeline and modeling roles.

Question 9Data Engineering

A daily pipeline occasionally fails after partially writing to the target. Which control best prevents downstream consumers from reading incomplete output?

  1. Increasing the job's memory allocation.
  2. Writing directly to the consumer-facing table row by row as data arrives.
  3. Sending an email notification when the job starts.
  4. Publishing results atomically by writing to a staging location and swapping or committing the new version only on success. Correct answer.

Explanation

Atomic publication ensures consumers see either the previous complete version or the new complete version, never a partial write. Staging plus a commit or swap step provides that guarantee. Row-by-row writes expose intermediate states, and notifications or memory changes do not affect visibility.

Question 10Data Engineering

A streaming job aggregates events with a one-hour window but receives many late-arriving events. Which mechanism lets the job still account for events after the window has fired?

  1. Reducing the window to one second.
  2. Disabling watermarks entirely.
  3. Partitioning the stream by event type.
  4. Configuring allowed lateness and updating the window result when late events arrive. Correct answer.

Explanation

Allowed lateness keeps window state alive after the watermark passes so late events can retrigger and update results. This balances correctness against state retention. Shrinking windows or repartitioning does not address lateness semantics.

Question 11Data Engineering

A pipeline must handle a source that sends the same event multiple times due to retries. Which approach yields correct aggregate counts downstream?

  1. Deduplicate on a stable event identifier before aggregation. Correct answer.
  2. Increase consumer parallelism.
  3. Aggregate first, then discard half the results.
  4. Count every incoming record as a new event.

Explanation

At-least-once delivery means duplicates are expected, so deduplication on a stable event key restores exactly-once semantics for downstream counts. Aggregating raw duplicates inflates totals. Parallelism and post-hoc discarding do not identify duplicates.

Question 12Data Engineering

A data quality check should fail the pipeline when the primary key of a dimension table is not unique. Which test expresses this rule?

  1. A uniqueness assertion on the primary key column. Correct answer.
  2. A row-count comparison against yesterday's total.
  3. A null-count assertion on a nullable descriptive column.
  4. A check that the table has at least one column.

Explanation

A uniqueness test on the primary key directly encodes the constraint that no key value repeats. Null checks, volume comparisons, and structural checks test different properties and would not catch duplicate keys. Data quality rules should map one-to-one to the invariant they protect.

Question 13Data Engineering

An engineer must join a very large fact table to a small dimension table in a distributed SQL engine. Which technique usually avoids a costly shuffle of the large table?

  1. Broadcasting the small dimension table to every worker. Correct answer.
  2. Disabling the query planner's statistics.
  3. Converting the fact table to CSV before the join.
  4. Sorting both tables by a random column first.

Explanation

Broadcast joins replicate the small table to each worker so the large table can be scanned locally without being repartitioned. That eliminates the network-heavy shuffle of the fact table. Random sorting, format changes, and disabling statistics do not achieve this.

Question 14Data Engineering

A slow query scans a large table with a filter on a low-cardinality column. Which storage-layout change most directly reduces scanned data?

  1. Adding more columns to the table.
  2. Clustering or sorting the data on the filter column to enable file or row-group pruning. Correct answer.
  3. Increasing the number of partitions to one per row.
  4. Renaming the table to a shorter name.

Explanation

Clustering or sorting on the filter column groups similar values together, so min/max statistics per row group let the engine skip irrelevant data. This reduces bytes scanned without changing query logic. Adding columns or per-row partitions increases overhead.

Question 15Data Engineering

Which orchestration behavior correctly implements a dependency between an upstream extraction task and a downstream transformation task?

  1. Running both tasks concurrently with no ordering.
  2. Triggering the transformation only after the extraction task reports success. Correct answer.
  3. Scheduling the transformation at a fixed time regardless of extraction status.
  4. Retrying the extraction indefinitely while the transformation runs.

Explanation

Dependency-aware orchestration starts a downstream task only when its upstream dependencies succeed, preventing transforms from reading missing or partial data. Fixed-time scheduling and concurrent execution ignore actual upstream state. Unbounded retries without gating can compound failures.

Question 16Data Engineering

A team must retain the full change history of a customer record for auditing while also exposing the current state. Which modeling approach satisfies both needs?

  1. Use a slowly changing dimension type 2 with validity dates plus a current-state view. Correct answer.
  2. Overwrite the row on every change, keeping only the latest state.
  3. Store only the creation timestamp of each record.
  4. Delete historical rows after each update.

Explanation

SCD type 2 keeps one row per version with effective and expiration dates, preserving history, while a current-state view filters to the active row. Overwriting or deleting history loses the audit trail. A creation timestamp alone cannot reconstruct prior attribute values.

Question 17Data Engineering

Which access-control approach most directly enforces column-level restrictions for a shared analytical table?

  1. Relying on users to avoid selecting sensitive columns.
  2. Storing the table in a public bucket.
  3. Granting every user the table owner role.
  4. Applying column-level grants or masked views so sensitive columns are hidden from unauthorized roles. Correct answer.

Explanation

Column-level grants or masking views restrict which fields a role can read even when it can query the table. That enforces least privilege at the data layer. Owner roles and public storage remove protection, and policy-by-convention is not enforcement.

Question 18Data Engineering

A pipeline ingests nested JSON with variable fields. Which approach best preserves flexibility while enabling reliable downstream queries?

  1. Reject all records containing any unexpected field.
  2. Store only the first record of each batch.
  3. Land the raw JSON and parse it into typed columns with schema evolution handled explicitly. Correct answer.
  4. Flatten every field into a single string column.

Explanation

Landing raw JSON preserves fidelity, while explicit parsing into typed columns with managed schema evolution gives downstream users stable, queryable structures. Rejecting variants, stringifying everything, or sampling records sacrifices data or usability. The pattern separates ingestion from modeling.

Question 19Data Engineering

Which metric best indicates that a streaming consumer is falling behind its producer?

  1. The number of columns in the output table.
  2. The number of registered schema versions.
  3. The size of the consumer's compiled binary.
  4. The consumer lag measured as the gap between the latest offset and the committed offset. Correct answer.

Explanation

Consumer lag quantifies how far behind the committed offset is from the latest produced offset, directly signaling processing shortfall. Schema counts, binary size, and output width do not measure consumption progress. Rising lag over time is the classic alert signal.

Question 20Data Engineering

A table is updated by merges that rewrite many small files each hour. Which maintenance action most improves read performance over time?

  1. Removing partitioning entirely.
  2. Increasing the frequency of tiny appends.
  3. Running compaction to merge small files and reorganize them. Correct answer.
  4. Disabling file statistics collection.

Explanation

Compaction combines small files into larger ones, reducing metadata overhead and per-file scan costs. Without it, frequent small merges degrade read performance. Disabling statistics or adding more small writes worsens the problem.

Question 21Data Engineering

A data engineer is designing a pipeline that must tolerate a source system outage without losing already-processed progress. Which design element is most important?

  1. Persisting checkpoints and watermarks durably so processing can resume from the last committed position. Correct answer.
  2. Restarting from the beginning of the source on every failure.
  3. Removing retry logic to simplify the code.
  4. Storing all state only in memory on a single node.

Explanation

Durable checkpoints record committed progress so a restart resumes from the last known good position instead of reprocessing or losing data. In-memory-only state vanishes on failure, and full restarts are costly. Retry logic is essential for transient outages.

Question 22Data Engineering

Which scenario most clearly calls for change data capture rather than nightly full extracts?

  1. A high-volume transactional table where only a small fraction of rows change daily and low latency is required. Correct answer.
  2. A static lookup file that never changes.
  3. A small reference table that changes a few times per year.
  4. A table that is dropped and recreated every hour.

Explanation

CDC reads the change log to capture only modified rows with low latency, which suits large, high-churn tables where full extracts are expensive. Static or rarely changing tables gain little, and tables recreated hourly have no stable change history. Matching the technique to change volume and latency needs is the key.

Question 23Data Engineering

A team observes that a distributed aggregation produces correct results only when run with a single reducer. Which underlying issue most likely explains this?

  1. The cluster has too much memory allocated.
  2. The output table has too many indexes.
  3. The input files are compressed.
  4. The aggregation logic is non-associative or depends on input order, so parallel partial aggregation changes results. Correct answer.

Explanation

Parallel aggregation relies on the combine operation being associative and commutative so partial results can merge safely. If the logic depends on order or is non-associative, parallel execution yields different totals than a single reducer. Memory, indexes, and compression do not alter aggregation semantics.

Question 24Data Engineering

An engineer needs exactly-once semantics for a stream that writes to an external sink that supports transactions. Which combination is required?

  1. Checkpointed source offsets plus transactional or idempotent sink commits tied to those checkpoints. Correct answer.
  2. At-most-once source reads with no checkpointing.
  3. Buffering all records indefinitely in memory.
  4. Random partitioning of the source stream.

Explanation

Exactly-once end-to-end requires that source progress and sink writes commit together, typically via checkpoints coordinated with transactional or idempotent sink commits. Without that coordination, failures cause duplicates or loss. At-most-once reads and in-memory buffering cannot provide the guarantee.

Question 25Data Engineering

A pipeline joins two large tables on a skewed key where a few values dominate. Which strategy best mitigates the resulting hot partitions?

  1. Increasing the number of distinct join keys artificially for all rows equally.
  2. Salting the skewed keys and replicating the matching small-side rows to spread work across partitions. Correct answer.
  3. Sorting both tables by the skewed key before joining.
  4. Disabling shuffle entirely for the join.

Explanation

Salting appends a random suffix to hot keys so their rows distribute across many partitions, while the other side is replicated to match each salted variant. This relieves the straggler partition caused by skew. Uniform key changes or pre-sorting do not remove the concentration.

Question 26Data Engineering

A data engineer is choosing a storage format for an analytical table that will be queried with selective filters on a few columns. Which property of columnar formats most directly reduces the bytes read for such queries?

  1. Row-level locking allows concurrent writers to avoid reading uncommitted blocks
  2. Column pruning combined with per-column block statistics allows the engine to skip irrelevant data Correct answer.
  3. Compression dictionaries allow the engine to decode only the rows that match the filter
  4. Schema evolution lets the engine drop columns that are not referenced at query time

Explanation

Columnar files store each column separately with metadata such as min/max statistics per block, so a query that references few columns reads only those columns and can skip blocks that cannot match the predicate. This reduces I/O far more than row-oriented layouts, where whole rows must be read even when one field is needed.

Question 27Data Engineering

A nightly load writes a partition of fact records, and a downstream report reads that partition at the same time. Which approach most reliably prevents the report from reading a half-written partition?

  1. Enable compression on the target partition so partial files are unreadable
  2. Add a retry loop around the report query so it re-reads until row counts match
  3. Increase the report's read timeout so it waits for the load to finish
  4. Write to a staging location and atomically publish the partition only after the full load succeeds Correct answer.

Explanation

Atomic partition publication means consumers see either the previous complete version or the new complete version, never a mixture. Staging the data and swapping or committing the partition in one step provides this isolation regardless of how long the load takes.

Question 28Data Engineering

Which characteristic distinguishes a slowly changing dimension of Type 2 from Type 1?

  1. Type 2 applies only to fact tables, while Type 1 applies only to dimension tables
  2. Type 2 keeps a new row per change with validity dates, while Type 1 overwrites the attribute in place Correct answer.
  3. Type 2 stores only the most recent value, while Type 1 archives every historical value
  4. Type 2 requires a surrogate key, while Type 1 always uses the natural business key

Explanation

A Type 2 dimension preserves history by inserting a new version of the record with effective and expiration dates (and often a current flag), so past states remain queryable. Type 1 simply overwrites the changed attribute, losing prior values.

Question 29Data Engineering

A data engineer needs to store raw ingestion payloads exactly as received so they can be reprocessed later with new logic. Which storage choice best fits this requirement?

  1. A curated reporting table that stores only validated and transformed fields
  2. An append-only raw zone that retains the original payloads with ingestion timestamps Correct answer.
  3. A cache layer that holds the most recent payload per source key
  4. A materialized view that pre-aggregates payloads for dashboards

Explanation

Reprocessing requires the original, untransformed data to remain available, so an append-only raw or landing zone that preserves payloads and ingestion metadata is appropriate. Curated and aggregated layers have already applied logic that would need to be redone.

Question 30Data Engineering

Which statement correctly describes the role of a surrogate key in a dimensional model?

  1. It is the natural key copied from the source system to guarantee uniqueness
  2. It is a composite of all dimension attributes used to detect changes
  3. It is a system-generated identifier that is stable and independent of the source business key Correct answer.
  4. It is a foreign key that references the fact table from the dimension

Explanation

A surrogate key is generated by the warehouse and assigned to each dimension row, decoupling the warehouse from source key changes and enabling multiple versions in Type 2 dimensions. Business or natural keys remain as separate attributes for traceability.

Question 31Data Engineering

A data engineer is asked to explain why partitioning a large event table by ingestion date improves query performance. Which explanation is most accurate?

  1. Partition pruning lets the engine read only the date ranges a query needs instead of scanning the whole table Correct answer.
  2. Partitioning normalizes the table into third normal form to reduce redundancy
  3. Partitioning compresses each row individually so scans decode fewer bytes
  4. Partitioning automatically creates indexes on every column used in filters

Explanation

When a query filters on the partition column, the engine can eliminate partitions outside the predicate range and read only the relevant files. This reduces the amount of data scanned, which is the primary benefit of date partitioning for event tables.

Question 32Data Engineering

Which practice best ensures that a transformation job produces the same output when rerun on the same input?

  1. Store intermediate results in a temporary directory that is cleared after each run
  2. Assign the job a higher priority in the scheduler queue
  3. Run the job only during off-peak hours when the cluster is less loaded
  4. Make the job deterministic by avoiding reliance on current timestamps and random values in its logic Correct answer.

Explanation

Determinism means the same inputs always yield the same outputs, which requires removing hidden dependencies such as wall-clock time, random generators, or unordered iteration. Scheduling, temporary storage, and queue priority do not affect the logical result.

Question 33Data Engineering

A team wants a single place to record the schema, owner, and description of each dataset so analysts can discover trustworthy data. Which component fills this role?

  1. A connection pool that reuses database sessions across queries
  2. A scheduler that triggers jobs at defined intervals
  3. A data catalog that stores technical and business metadata about datasets Correct answer.
  4. A message queue that buffers events between producers and consumers

Explanation

A data catalog maintains metadata such as schemas, ownership, descriptions, and tags, enabling discovery and governance across datasets. Message queues, schedulers, and connection pools serve runtime concerns rather than metadata management.

Question 34Data Engineering

A pipeline reads from an operational database and must avoid placing long read locks on production tables. Which extraction approach best meets this constraint?

  1. Pause replication and query the primary directly during the extraction window
  2. Issue SELECT statements with table-level locks to guarantee a stable read
  3. Read consistent snapshots using a transaction isolation level that does not block writers Correct answer.
  4. Extract with a series of uncommitted dirty reads to reduce lock duration

Explanation

Snapshot-based reads at an isolation level such as repeatable read or snapshot isolation give the extractor a consistent view without holding locks that block production writers. This keeps source latency low while preserving consistency.

Question 35Data Engineering

A batch aggregation groups records by a key, but results vary between runs even though the input is unchanged. Which cause is most likely?

  1. The job was submitted by a different user account
  2. The cluster has more worker nodes than the previous run
  3. The aggregation depends on non-deterministic ordering or on floating-point sums accumulated in a different order Correct answer.
  4. The output table was written with a different compression codec

Explanation

When results depend on the order in which records are processed, or on floating-point addition whose result varies with order, reruns can differ. Removing order dependence or using deterministic, order-insensitive aggregation restores stable results.

Question 36Data Engineering

A data engineer must load a dimension that receives frequent small updates during the day while analysts query it continuously. Which design most directly balances freshness and query stability?

  1. Block analyst queries while any change is pending so the table stays consistent
  2. Land changes in a staging table and merge them into the dimension in scheduled micro-batches Correct answer.
  3. Apply each change immediately with row-by-row updates to the live dimension
  4. Rebuild the entire dimension from scratch after every single change

Explanation

Micro-batch merges consolidate many small changes into fewer write operations, reducing contention and giving analysts a stable table between merges. Immediate row-by-row updates and full rebuilds are far more expensive and disruptive.

Question 37Data Engineering

An engineer must reconcile row counts between a source table and its replicated copy. Which check most directly detects rows that were dropped during replication?

  1. Compare per-partition counts and checksums between source and target for the same key ranges Correct answer.
  2. Compare the storage size of the source and target tables
  3. Compare the number of columns in the source and target schemas
  4. Compare the query execution plans of the source and target engines

Explanation

Partition-level count and checksum comparisons localize missing or altered rows to specific key ranges, making discrepancies actionable. Schema, plan, and size comparisons can hint at differences but do not directly confirm row-level completeness.

Question 38Data Engineering

A pipeline must join a fact stream with a reference table that changes occasionally. Which approach keeps the join correct without reprocessing the entire stream?

  1. Cache the reference table once at job start and never refresh it
  2. Maintain the reference table as versioned state and join each event against the version valid at its event time Correct answer.
  3. Broadcast the reference table to workers and reload it only on job restart
  4. Join each event against the latest reference table regardless of event time

Explanation

Versioned reference data with effective timestamps lets each event be enriched with the attribute values that were true when the event occurred, which is essential for accurate historical reporting. Joining against the latest version silently rewrites history.

Question 39Data Engineering

A data engineer is designing error handling for a pipeline that loads thousands of files per run. Which strategy best prevents one malformed file from failing the entire run while preserving visibility?

  1. Quarantine failed files with error details, continue processing the rest, and report the failures Correct answer.
  2. Abort the run on the first bad file so no partial results are produced
  3. Silently skip malformed files without recording which ones failed
  4. Retry the whole run repeatedly until every file parses successfully

Explanation

Quarantining bad records or files isolates them for investigation while allowing the bulk of valid data to load, and the recorded errors provide the audit trail needed to fix the source. This balances throughput with data quality visibility.

Question 40Data Engineering

A team needs to compute a running seven-day total of daily sales in a warehouse. Which SQL construct is appropriate?

  1. A self-join that pairs each day with every other day in the table
  2. A GROUP BY on date with a HAVING clause filtering to seven days
  3. A window function that sums sales over a preceding seven-day range ordered by date Correct answer.
  4. A DISTINCT count of sales dates within each month

Explanation

A window function with a RANGE or ROWS frame covering the preceding seven days computes a moving total while preserving one row per date. GROUP BY collapses rows, and self-joins or distinct counts do not produce a running total.

Question 41Data Engineering

A data engineer observes that a nightly job's runtime grows steadily over months while input volume stays flat. Which cause is most likely?

  1. Accumulating small files and unpruned old partitions increase the metadata and scan overhead Correct answer.
  2. The SQL dialect changes automatically as the engine is upgraded
  3. The job's user permissions are being reduced over time
  4. The cluster hardware is silently degrading each month

Explanation

Without compaction and retention, tables accumulate many small files and obsolete partitions, so planning and scanning take longer even with constant input. Routine compaction and partition pruning restore stable runtimes.

Question 42Data Engineering

A pipeline must publish a daily aggregate that downstream teams consume, and the aggregate must be recomputable from source if a bug is found. Which practice best supports this?

  1. Store only the final aggregate and delete intermediate data after publication
  2. Overwrite the aggregate nightly without retaining the logic version used
  3. Version the transformation logic and store the input snapshot used for each published aggregate Correct answer.
  4. Allow analysts to edit the aggregate table directly to correct errors

Explanation

Retaining the logic version and the exact input snapshot makes each published aggregate reproducible and auditable, so a defect can be fixed and the result recomputed. Deleting inputs or allowing manual edits destroys that ability.

Question 43Data Engineering

A data engineer must choose between a normalized operational schema and a denormalized analytical schema for a reporting layer. Which trade-off is most accurate?

  1. Normalization guarantees that reports never require aggregation
  2. Denormalization removes the need for any data quality checks
  3. Denormalization reduces joins at query time at the cost of redundant storage and update complexity Correct answer.
  4. Normalization always improves analytical query speed by eliminating redundancy

Explanation

Analytical schemas often denormalize to minimize joins and speed up scans, accepting duplicated attributes and more complex load logic. Normalization reduces redundancy for transactional integrity but typically increases join work for analytics.

Question 44Data Engineering

A streaming pipeline must enrich events with a small lookup table that is updated by a separate batch job. Which mechanism best keeps the enrichment current without restarting the stream?

  1. Query the lookup table synchronously for every event
  2. Embed the lookup values directly in the job's source code
  3. Join against a static file read once at startup
  4. Periodically reload the lookup table into broadcast state and swap it atomically Correct answer.

Explanation

Periodic reloads into broadcast state let the streaming job pick up updated reference data while it continues running, and an atomic swap avoids serving a partially loaded table. Hardcoding or one-time reads go stale, and per-event queries add latency and load.

Question 45Data Engineering

An engineer must ensure that a merge into a target table is idempotent when the job is retried after a partial failure. Which design achieves this?

  1. Append every retry's rows to the target so no data is lost
  2. Generate a new random surrogate key on each retry to distinguish attempts
  3. Key the merge on a deterministic business key and use upsert semantics so repeated runs converge Correct answer.
  4. Truncate the target at the start of each retry and reload from scratch

Explanation

An upsert keyed on a deterministic business key makes repeated executions produce the same final state, which is the definition of idempotence. Appending retries or generating new keys creates duplicates.

Question 46Data Engineering

A data platform must enforce that personally identifiable fields are masked for most analysts but visible to a small compliance group. Which approach most directly implements this?

  1. Create a separate full copy of the table for each analyst team
  2. Apply column-level masking policies that vary by role on the shared table Correct answer.
  3. Rely on analysts to filter sensitive columns in their queries
  4. Restrict access to the entire database for everyone except administrators

Explanation

Role-based masking policies attached to specific columns let most users see masked values while privileged roles see the raw data, all from the same governed table. Copying tables or relying on user discipline does not scale and is error-prone.

Question 47Data Engineering

A distributed sort of a billion rows runs out of memory on the reduce side. Which change most directly addresses the root cause?

  1. Disable compression so records are easier to compare
  2. Increase the number of reduce partitions so each handles a smaller sorted chunk Correct answer.
  3. Reduce the number of mappers to lower input parallelism
  4. Add more columns to the sort key to spread the data

Explanation

Reduce-side memory pressure usually comes from too few partitions receiving too much data. Repartitioning into more, smaller groups lowers per-task memory while preserving the sort result.

Question 48Data Engineering

A pipeline joins a large fact table to a dimension where a handful of dimension keys are duplicated due to a data defect. Which outcome should the engineer expect before the defect is fixed?

  1. Aggregates remain correct because the join is on a key column
  2. The join silently drops all rows with duplicated keys
  3. Fact rows matching duplicated keys are multiplied, inflating aggregates for those keys Correct answer.
  4. The engine automatically deduplicates the dimension before joining

Explanation

A join emits one output row per matching pair, so a duplicated dimension key produces multiple fact rows for each matching fact record. Aggregates for the affected keys are then overstated until the dimension keys are made unique.

Question 49Data Engineering

An engineer must backfill two years of history into a partitioned table while a live pipeline continues writing recent partitions. Which strategy minimizes interference and cost?

  1. Backfill older partitions in bounded ranges while the live job writes only current partitions, with partition-level isolation Correct answer.
  2. Disable partitioning so the backfill and live writes share one file set
  3. Rewrite the whole table in a single job that includes both history and live data
  4. Pause the live pipeline for the entire backfill duration

Explanation

Writing disjoint partition ranges lets the backfill proceed in parallel with live ingestion without conflicts, and bounded batches control cost and allow resumability. Pausing ingestion or rewriting everything increases risk and duration.

Question 50Data Engineering

A team notices that a query filtering on a high-cardinality column still scans the entire table. Which explanation is most likely?

  1. The table uses columnar storage, which prevents predicate pushdown
  2. The column has too few distinct values for statistics to be collected
  3. The query engine lacks support for equality predicates
  4. The filter column is not a partition or clustering key, so no pruning metadata applies to it Correct answer.

Explanation

Pruning relies on the filter column aligning with partitioning or clustering, or on per-file statistics that the engine can use. A high-cardinality column that is not part of the layout gives the engine no way to skip data blocks, so a full scan occurs.