I85PREP
⌘ K
BIDA study materials
BIDA study resources

BIDA practice questions

One question at a time. Build your BIDA 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 Transformation & Automation

In a relational database, which property of a transaction guarantees that either all of its statements are committed or none are?

  1. Atomicity Correct answer.
  2. Consistency
  3. Durability
  4. Isolation

Explanation

Atomicity treats a transaction as a single indivisible unit, so a failure partway through rolls back every change already applied. The other ACID properties cover different guarantees such as permanent storage, concurrent visibility, and rule enforcement.

Question 2Data Transformation & Automation

A pipeline reads a CSV where the amount column stores values like "1,250.00". Which transformation step is required before the column can be summed numerically?

  1. Remove the thousands separator and cast to a numeric type Correct answer.
  2. Apply a hash function to each value
  3. Concatenate the column with a currency code
  4. Sort the column in descending order

Explanation

Numeric aggregation requires a numeric data type, and embedded thousands separators make the value a string that cannot be summed directly. Stripping the separator and casting converts it to a number the engine can add.

Question 3Data Transformation & Automation

Which SQL clause is used to filter individual rows before any grouping occurs?

  1. HAVING
  2. ORDER BY
  3. GROUP BY
  4. WHERE Correct answer.

Explanation

WHERE evaluates row-level predicates before aggregation, so non-matching rows never reach the grouping step. HAVING filters after grouping and can reference aggregate results.

Question 4Data Transformation & Automation

In a star schema, what is the primary role of a dimension table?

  1. Enforcing referential integrity between fact tables
  2. Storing the numeric measures of each business event
  3. Holding pre-aggregated totals for every query
  4. Providing descriptive attributes used to filter and label facts Correct answer.

Explanation

Dimension tables supply the descriptive context, such as product name or region, that analysts use to slice and label fact rows. The measurable quantities live in the fact table instead.

Question 5Data Transformation & Automation

A scheduled job must run every day at 02:00 UTC. Which orchestration feature is designed to express this requirement?

  1. A column-level encryption key
  2. A foreign key constraint
  3. A row-level security policy
  4. A cron-style schedule defined on the workflow Correct answer.

Explanation

Cron-style schedules express recurring time-based triggers and are the standard way to launch a workflow at a fixed time each day. The other options govern access, integrity, or protection of data rather than timing.

Question 6Data Transformation & Automation

Which file format stores data as columnar blocks with embedded schema and is commonly used for analytical query engines?

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

Explanation

Parquet is a columnar format that embeds schema metadata and stores values by column, which enables efficient compression and column pruning for analytics. Row-oriented text formats lack these properties.

Question 7Data Transformation & Automation

What does an idempotent data load guarantee when the same batch is executed twice?

  1. The target ends in the same state as a single execution Correct answer.
  2. The batch is rejected on the second run
  3. Only the first run writes to the target
  4. Row counts double on the second run

Explanation

Idempotency means repeating an operation produces the same final state as running it once, which makes retries safe. This is usually achieved with upserts or overwrite-by-partition patterns rather than simply blocking reruns.

Question 8Data Transformation & Automation

A column contains the values '2024-01-15', '2024-02-30', and 'not a date'. What is the most appropriate first transformation step?

  1. Validate and quarantine rows that fail date parsing Correct answer.
  2. Convert the whole column to integers
  3. Sort the column alphabetically
  4. Drop the entire table

Explanation

Parsing each value against a date format reveals which entries are invalid, and quarantining them preserves data quality while allowing valid rows through. Casting or sorting does not resolve the malformed values.

Question 9Data Transformation & Automation

A source system sends the same customer record three times with different timestamps. Which approach keeps only the most recent version in the target?

  1. Randomly select one of the three rows
  2. Append all three rows without change
  3. Keep the row with the earliest timestamp
  4. Deduplicate by customer key and keep the row with the latest timestamp Correct answer.

Explanation

Deduplication keyed on the business key with a latest-timestamp rule retains the newest version of each record. Appending everything or choosing arbitrarily leaves stale or duplicated data in the target.

Question 10Data Transformation & Automation

A table has 10,000 rows where the region column holds only four distinct values. Which index type is generally most efficient for filtering on region?

  1. A unique B-tree index
  2. A bitmap index Correct answer.
  3. A hash index on every column
  4. A full-text index

Explanation

Low-cardinality columns benefit from bitmap indexes because each distinct value maps to a compact bit vector that supports fast combination of predicates. Unique indexes and full-text indexes target different access patterns.

Question 11Data Transformation & Automation

In a slowly changing dimension of type 2, what happens when an attribute of a tracked entity changes?

  1. A new column is added for each change
  2. The attribute change is discarded
  3. A new row is inserted with a new surrogate key and validity dates Correct answer.
  4. The existing row is overwritten in place

Explanation

Type 2 preserves history by inserting a new versioned row with its own surrogate key and effective date range while closing the prior row. Overwriting in place is type 1 behavior and loses history.

Question 12Data Transformation & Automation

A workflow task fails because an upstream API returns a 429 status. Which handling strategy is most appropriate?

  1. Permanently disable the task
  2. Delete the downstream tables
  3. Retry with exponential backoff Correct answer.
  4. Immediately rerun in a tight loop

Explanation

A 429 indicates rate limiting, which is transient; exponential backoff spaces out retries so the client stops overwhelming the API. Tight loops worsen throttling and permanent disablement abandons valid work.

Question 13Data Transformation & Automation

What is the main purpose of a surrogate key in a dimension table?

  1. To provide a stable, system-generated identifier independent of source values Correct answer.
  2. To encrypt personally identifiable information
  3. To store the natural business key from the source system
  4. To enforce uniqueness across all fact tables

Explanation

A surrogate key is generated by the warehouse and stays stable even if source business keys change or are reused. Natural keys come from the source and are stored separately as attributes.

Question 14Data Transformation & Automation

A pipeline joins two tables on a string column where one side has trailing spaces. What is the most reliable fix to avoid missed matches?

  1. Trim whitespace on both join keys before joining Correct answer.
  2. Increase the join timeout
  3. Cast both keys to floating point
  4. Join on row number instead

Explanation

Trailing spaces make logically equal strings compare as different values, so normalizing both sides with a trim produces consistent keys. Casting to numbers or joining on position does not address the mismatch.

Question 15Data Transformation & Automation

Which transformation pattern best handles a source that delivers only changed records plus an operation flag of insert, update, or delete?

  1. Apply change data capture merges to the target Correct answer.
  2. Full reload of the target on every run
  3. Convert all operations to inserts
  4. Ignore the operation flag and append all rows

Explanation

Change data capture delivers deltas with operation codes, and a merge applies inserts, updates, and deletes to keep the target synchronized. Full reloads are costly, and ignoring the flag corrupts the target state.

Question 16Data Transformation & Automation

A nightly job processes a partition keyed by date. Which write mode makes rerunning the same date safe?

  1. Insert with a random surrogate key
  2. Overwrite the partition for that date Correct answer.
  3. Truncate the whole table first
  4. Append to the partition

Explanation

Overwriting only the target partition replaces the prior run's output for that date, so reruns converge on the same result. Appending duplicates rows, and truncating the whole table destroys unrelated partitions.

Question 17Data Transformation & Automation

Which practice best supports data lineage tracking across a multi-step pipeline?

  1. Storing only the final output table
  2. Recording input and output datasets and the transformation version for each step Correct answer.
  3. Relying on developer memory of the pipeline
  4. Deleting intermediate datasets immediately

Explanation

Lineage requires capturing which datasets feed each step, which datasets it produces, and which code version performed the work. Keeping only final outputs or deleting intermediates breaks the chain of provenance.

Question 18Data Transformation & Automation

A column declared NOT NULL in the target rejects a batch containing nulls. Which remediation preserves the most data while satisfying the constraint?

  1. Load the nulls and let the constraint fail
  2. Silently drop the NOT NULL constraint
  3. Define and apply an explicit default or imputation rule for nulls Correct answer.
  4. Skip the entire batch permanently

Explanation

An explicit default or imputation rule documents how missing values are handled and lets valid rows load while satisfying the constraint. Removing the constraint or failing the load sacrifices data integrity or availability.

Question 19Data Transformation & Automation

A query joins a 50-million-row fact table to a small 200-row lookup table. Which join strategy is typically most efficient?

  1. Broadcast the fact table to each worker
  2. Shuffle both tables across the network
  3. Broadcast the small table to each worker Correct answer.
  4. Perform a nested loop without indexes

Explanation

Broadcasting the tiny lookup table avoids shuffling the large fact table, so each worker joins locally with a small in-memory copy. Shuffling the fact table or broadcasting it would move enormous volumes of data.

Question 20Data Transformation & Automation

What distinguishes a data lakehouse from a traditional data warehouse?

  1. It adds warehouse-style transactions and schema management over open file storage Correct answer.
  2. It stores only unstructured documents
  3. It forbids SQL access to the data
  4. It removes the need for any schema

Explanation

A lakehouse layers table formats that provide ACID transactions, schema enforcement, and cataloging on top of cheap open file storage. Warehouses use proprietary storage, while lakehouses still support SQL and structured data.

Question 21Data Transformation & Automation

A transformation must be applied to every row and depends only on that row's columns, with no cross-row state. Which execution model fits best?

  1. A global sort followed by a scan
  2. A cross join with a constant table
  3. A parallel map operation partitioned across workers Correct answer.
  4. A single-threaded sequential loop

Explanation

Row-independent logic is embarrassingly parallel, so a map partitioned across workers scales linearly. Sequential looping wastes parallelism, and sorting or cross joins add cost without benefit.

Question 22Data Transformation & Automation

A pipeline computes daily revenue by summing transactions, but late-arriving events arrive up to three days after their event date. Which design handles this correctly?

  1. Use the ingestion timestamp as the event date
  2. Only process events that arrive on the same day
  3. Reprocess the affected date partitions within a defined lateness window Correct answer.
  4. Discard all late events permanently

Explanation

Reprocessing partitions for recent dates within a bounded lateness window incorporates late events without recomputing all history. Ignoring late events or mislabeling their dates produces understated or misplaced revenue.

Question 23Data Transformation & Automation

Two concurrent transactions each read a balance and then write an updated value derived from that read, causing a lost update. Which isolation mechanism prevents this outcome?

  1. Increasing the statement timeout
  2. Row-level locking or optimistic concurrency with version checks Correct answer.
  3. Read uncommitted isolation
  4. Disabling foreign keys

Explanation

A lost update occurs when two transactions overwrite each other based on stale reads; locking the row or checking a version on write forces one to fail or wait. Weaker isolation or unrelated settings do not serialize the writes.

Question 24Data Transformation & Automation

A streaming job must aggregate events by key with exactly-once semantics into a sink that supports transactions. Which combination is required?

  1. Checkpointed state plus transactional or idempotent sink writes Correct answer.
  2. Periodic full reloads of the sink
  3. At-least-once delivery without deduplication
  4. Stateless processing plus append-only writes

Explanation

Exactly-once end to end requires recoverable operator state from checkpoints combined with a sink that commits transactionally or deduplicates idempotently. Stateless or non-deduplicated writes can duplicate results after failures.

Question 25Data Transformation & Automation

A team needs to evolve a Parquet dataset by adding a non-nullable column with no default. What is the safest migration approach?

  1. Overwrite all historical files without the new column
  2. Add the column as nullable, backfill values, then enforce non-null Correct answer.
  3. Rename the dataset and start fresh
  4. Enforce non-null immediately on existing files

Explanation

Existing files contain no value for the new column, so enforcing non-null immediately breaks readers. Adding it as nullable, backfilling historical rows, and then tightening the constraint preserves compatibility.

Question 26Data Transformation & Automation

Which statement best describes the role of a schema-on-write target table in a transformation pipeline?

  1. It stores raw text and infers types only when queried
  2. It validates and enforces column types before data is stored Correct answer.
  3. It requires no declared columns and accepts any structure
  4. It defers all type checking to the reporting layer

Explanation

Schema-on-write means the structure, including column names and data types, is declared and enforced at load time. Records that violate the declared structure are rejected or coerced before they ever reach storage, which protects downstream consumers from surprises.

Question 27Data Transformation & Automation

A pipeline must convert a text column of mixed formats such as '1,234.50' and '987' into numeric values. Which step should come first?

  1. Cast the raw string directly to a decimal type
  2. Strip grouping separators, then cast to numeric Correct answer.
  3. Concatenate all values into one string, then split
  4. Replace every digit with a zero before casting

Explanation

Grouping separators like commas are not valid in most numeric literal parsers, so they must be removed before casting. Normalizing the string first ensures the cast succeeds consistently across all rows.

Question 28Data Transformation & Automation

Which practice most directly reduces the risk of a transformation silently producing wrong results after a source schema changes?

  1. Renaming output tables each release
  2. Adding automated schema-drift checks with alerts Correct answer.
  3. Increasing the job's memory allocation
  4. Running the job more frequently

Explanation

Automated schema-drift detection compares the observed source structure against an expected contract and raises an alert when they diverge. This catches renamed, dropped, or retyped columns before bad data propagates.

Question 29Data Transformation & Automation

What is the primary benefit of storing transformation logic in version-controlled code rather than hand-edited scripts on a server?

  1. It eliminates the need for testing
  2. It makes changes reviewable and reproducible Correct answer.
  3. It guarantees faster query execution
  4. It removes the need for data validation

Explanation

Version control records every change with authorship and history, enabling peer review and exact reproduction of any prior state. This traceability is the core reason teams move transformation logic into repositories.

Question 30Data Transformation & Automation

A column holds values like ' Alice ', 'Bob ', and ' Carol'. Which transformation produces a consistent join key?

  1. Trimming leading and trailing whitespace Correct answer.
  2. Converting all text to uppercase only
  3. Padding every value to a fixed length
  4. Reversing the character order

Explanation

Trimming removes the surrounding whitespace that would otherwise cause equal names to compare as unequal. Applying it before joining or matching yields consistent keys.

Question 31Data Transformation & Automation

Which statement correctly distinguishes a full load from an incremental load?

  1. An incremental load ignores change timestamps entirely
  2. An incremental load always truncates the target first
  3. A full load only moves rows changed since the last run
  4. A full load replaces the entire target dataset each run Correct answer.

Explanation

A full load refreshes the whole target from the source, typically by truncating and reloading. An incremental load moves only new or changed records, which is cheaper but requires reliable change detection.

Question 32Data Transformation & Automation

Which data type is generally most appropriate for storing a monetary amount where exact rounding matters?

  1. Fixed-precision decimal Correct answer.
  2. Floating-point double
  3. Variable-length text
  4. Single-precision float

Explanation

Fixed-precision decimal types store an exact number of digits and scale, avoiding the binary rounding artifacts of floating point. This makes them the standard choice for currency values.

Question 33Data Transformation & Automation

A team wants each transformation step to be independently rerunnable without reprocessing the entire pipeline. Which design supports this best?

  1. Writing every step's output to durable, partitioned storage Correct answer.
  2. One monolithic script with no intermediate storage
  3. Requiring a full reload whenever any step fails
  4. Chaining all steps in a single in-memory process

Explanation

Persisting each step's output to durable storage lets downstream steps restart from the last good checkpoint. This modularity limits the blast radius of a failure and avoids redundant work.

Question 34Data Transformation & Automation

A transformation must combine rows from two tables where every row from the left table should appear even when no match exists on the right. Which join type achieves this?

  1. Inner join
  2. Cross join
  3. Semi join
  4. Left outer join Correct answer.

Explanation

A left outer join preserves all rows from the left input and fills right-side columns with nulls when no match exists. Inner and semi joins drop unmatched left rows, and a cross join pairs every row with every row.

Question 35Data Transformation & Automation

A pipeline groups transactions by customer and needs the total and the count per customer in one pass. Which operation produces this result?

  1. A distinct filter on customer
  2. A self-join on the customer column
  3. A window function partitioned by date
  4. An aggregation grouping by customer Correct answer.

Explanation

Grouping by customer and applying SUM and COUNT aggregates collapses each customer's rows into one summary record. This computes both metrics in a single pass over the data.

Question 36Data Transformation & Automation

A source provides a change timestamp but occasionally delivers records with a timestamp earlier than one already processed. Which design handles these late updates correctly?

  1. Reject the batch and alert the source team
  2. Append every record and never reconcile
  3. Overwrite the target row when the incoming timestamp is newer Correct answer.
  4. Ignore any record older than the current watermark

Explanation

Comparing timestamps and overwriting only when the incoming record is newer ensures the target always reflects the most recent known state. Late arrivals with older timestamps are safely discarded by this rule.

Question 37Data Transformation & Automation

Which approach best handles a source field that contains both a date and a time in a single string when the target needs separate date and timestamp columns?

  1. Parse the string once into a timestamp, then derive the date Correct answer.
  2. Truncate the string to its first ten characters only
  3. Hash the string and store the digest
  4. Store the raw string in both target columns

Explanation

Parsing the combined string into a proper timestamp type preserves all the information, and the date part can then be derived from it. This avoids lossy string slicing and keeps both columns consistent.

Question 38Data Transformation & Automation

Two source tables use different codes for the same status, such as 'A' and 'Active'. Which transformation resolves this before combining the tables?

  1. A filter that removes all coded rows
  2. A sort that places 'A' before 'Active'
  3. A lookup mapping that standardizes codes to one vocabulary Correct answer.
  4. A union that keeps both original code values

Explanation

A mapping table translates each source code into a shared canonical value, so downstream logic sees one consistent vocabulary. This is the standard approach for harmonizing reference data across sources.

Question 39Data Transformation & Automation

A pipeline reads a CSV where a quoted field legitimately contains a comma, for example "Smith, John". Which parsing approach handles this correctly?

  1. Reading the file as fixed-width columns
  2. Replacing all commas with semicolons first
  3. Using a CSV parser that honors quote characters Correct answer.
  4. Splitting each line on every comma

Explanation

A proper CSV parser treats text inside quotes as a single field even when it contains the delimiter. Naive splitting on commas would incorrectly break the quoted value into two fields.

Question 40Data Transformation & Automation

Which technique best reduces the volume of data shuffled across the network during a large group-by aggregation?

  1. Sorting the input by an unrelated column
  2. Increasing the number of output partitions
  3. Disabling compression on intermediate data
  4. Pre-aggregating partial results before the shuffle Correct answer.

Explanation

Computing partial aggregates locally before the shuffle means far fewer records travel between nodes. The final aggregation then combines the smaller partial results, cutting network cost substantially.

Question 41Data Transformation & Automation

A transformation needs a running total of order amounts per customer ordered by date. Which construct computes this?

  1. A window function with a cumulative frame Correct answer.
  2. A distinct count on order id
  3. A left join to the same table
  4. A group-by with a sum aggregate

Explanation

A window function partitioned by customer and ordered by date, using a cumulative frame, produces a running total while retaining each row. A plain group-by would collapse the rows and lose the per-order detail.

Question 42Data Transformation & Automation

A nightly pipeline reads from a source that is still being written to by an operational system. Which practice most reduces the chance of reading a partially written record?

  1. Reading at maximum concurrency
  2. Reading the same table twice and merging
  3. Reading only committed snapshots or stable partitions Correct answer.
  4. Skipping all validation to move faster

Explanation

Reading committed snapshots or only partitions that are no longer being written avoids capturing in-flight changes. This gives the pipeline a consistent view of the data.

Question 43Data Transformation & Automation

Which transformation correctly deduplicates records that share a business key but differ in a quality score, keeping the highest-scoring version?

  1. Remove all rows whose score is below average
  2. Concatenate duplicate rows into one string
  3. Rank rows per key by score and keep rank one Correct answer.
  4. Group by the key and take the maximum score only

Explanation

Ranking rows within each key by descending score and keeping the top rank retains the full record with the best score. Taking only the maximum score would discard the other columns of that record.

Question 44Data Transformation & Automation

A pipeline must enrich events with a slowly changing lookup table where historical attribute values matter for matching the event date. Which join condition is required?

  1. Join on the key and an effective-date range Correct answer.
  2. Join on the key and the current flag only
  3. Join only on the business key
  4. Join on row position in both tables

Explanation

An as-of or range join matches each event to the lookup version whose validity window contains the event date. Joining only on the key would attach an arbitrary or current version instead of the historically correct one.

Question 45Data Transformation & Automation

A transformation produces a count that is lower than expected, and investigation shows rows with null join keys were dropped. Which fix preserves those rows while flagging them?

  1. Convert the null keys to zero before joining
  2. Filter out the null-key rows earlier in the pipeline
  3. Replace the inner join with a left join and label unmatched rows Correct answer.
  4. Aggregate before the join to hide the loss

Explanation

A left join keeps every left row, and a label column marks those without a match so they can be reported rather than silently lost. This makes the unmatched population visible and auditable.

Question 46Data Transformation & Automation

A pipeline must process a very large fact table joined to a small dimension, but the dimension is too large to broadcast. Which strategy is generally most appropriate?

  1. Repartitioning both inputs on the join key before joining Correct answer.
  2. Converting the join to a cross join
  3. Forcing a broadcast join regardless of size
  4. Collecting the dimension to the driver

Explanation

When broadcast is not feasible, co-partitioning both inputs on the join key lets each partition join locally without a large shuffle. This is the standard sort-merge style approach for sizeable joins.

Question 47Data Transformation & Automation

A transformation must be exactly reproducible for audit, including the code, parameters, and input snapshot used. Which combination best satisfies this requirement?

  1. Keeping the developer's local notebook
  2. Recording the job start time alone
  3. Storing only the final output tables
  4. Logging the code version, parameters, and immutable input references Correct answer.

Explanation

Reproducibility requires capturing the code version, the runtime parameters, and a reference to the exact immutable input snapshot. Together these let an auditor rerun the job and obtain the same result.

Question 48Data Transformation & Automation

A streaming aggregation must emit correct totals even when the same event is delivered twice. Which design achieves this?

  1. Reset the aggregate state every minute
  2. Deduplicate on a stable event key before aggregating Correct answer.
  3. Sum every arriving event without checks
  4. Rely on the source to never resend events

Explanation

Deduplicating on a stable event identifier before aggregation ensures each event contributes exactly once to the totals. This removes the effect of duplicate deliveries regardless of source behavior.

Question 49Data Transformation & Automation

A transformation needs to compare each row's value to the previous row's value for the same sensor. Which construct is appropriate?

  1. A cross join of the table with itself
  2. A window function using lag ordered by time Correct answer.
  3. A distinct filter on sensor id
  4. A group-by with a max aggregate

Explanation

The lag window function returns the prior row's value within each sensor partition ordered by time, enabling direct comparison. This avoids the cost and ambiguity of self-joins.

Question 50Data Transformation & Automation

A transformation must convert a numeric column stored as text, but some rows contain an empty string and others contain a literal 'NULL' marker. Which approach most reliably produces a clean numeric column while preserving the distinction between missing and invalid values?

  1. Replace empty strings and 'NULL' markers with a null before casting, then validate that the remaining values convert cleanly Correct answer.
  2. Concatenate a zero onto each value so blanks and markers become numeric before casting
  3. Filter out every row that contains an empty string or 'NULL' marker before the cast
  4. Cast the column directly to a numeric type and let the database raise an error on any non-numeric row

Explanation

Empty strings and sentinel markers like 'NULL' are not numeric, so they must be normalized to a true null before a cast; otherwise the cast either fails or silently coerces them to unexpected values such as zero. Normalizing first and then validating the remaining values keeps missing data distinct from genuinely invalid data that should be flagged.