ARA-C01 Data Engineering Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  Use COPY INTO with MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE and rely on the target table column order to map the fields.

  Why it's wrong here

  MATCH_BY_COLUMN_NAME is intended for semi-structured formats such as JSON or Parquet, not for delimited CSV files where field position determines the mapping. It does not handle the pipe delimiter, header row, or date format issues. The load would either fail or misalign columns, and rejected rows would not be captured for review as required.

• ✗

  Create an external table over the stage and query the CSV files directly, converting the date with TO_DATE in the SELECT statement.

  Why it's wrong here

  External tables are designed for reading data in place, typically for semi-structured or partitioned files, and they do not load rows into a staging table. They also do not provide the COPY INTO error capture the team needs. While a query could cast dates, this approach does not fulfill the requirement to load the batch and capture rejected rows.

• ✓

  Define a file format with TYPE = CSV, FIELD_DELIMITER = '|', SKIP_HEADER = 1, and a DATE_FORMAT matching the source, then use COPY INTO with ON_ERROR = 'CONTINUE' and a validation_mode run first.

  Why this is correct

  This configuration addresses every stated requirement: the pipe delimiter and header skip match the files, the date format matches the source data, and ON_ERROR = CONTINUE preserves load progress while capturing rejected rows. Running VALIDATION_MODE first reports errors without loading, so the team can inspect problems before committing data. This is the standard controlled CSV ingestion pattern.

• ✗

  Load the files with COPY INTO using ON_ERROR = 'ABORT_STATEMENT' and inspect the query history afterward to identify which rows failed.

  Why it's wrong here

  ABORT_STATEMENT stops the entire load on the first error, so a single malformed row prevents the rest of the batch from loading. Query history shows the failure but does not preserve the offending rows for review, and the team would need to fix and rerun repeatedly. This is the opposite of the fault-tolerant capture the scenario requires.