Task 2.7: Ingest semi-structured and unstructured data (for example, JSON and nested data) via Lakeflow Connect and other managed connectors into Unity Catalog–governed Delta tables.

• Afrom_json(payload, schema)

  Incorrect

  from_json parses the string into a STRUCT that matches a schema you supply, which is the fixed schema the team wants to avoid.

• Bto_json(payload)

  Incorrect

  to_json goes the other way: it turns a struct or VARIANT value into a JSON string.

• Cschema_of_json(payload)

  Incorrect

  schema_of_json returns a DDL schema string describing the JSON; it does not convert the data.

• Dparse_json(payload)

  Correct

  parse_json takes a JSON string and returns the equivalent VARIANT value, which keeps the full document and its nesting.

VARIANT stores semi-structured data flexibly, and parse_json is the function that produces it from a JSON string. from_json is for parsing into a known struct schema.

• Amode set to DROPMALFORMED

  Incorrect

  DROPMALFORMED discards corrupted records, so the data would be dropped instead of read.

• BmultiLine set to true

  Correct

  By default each line must be a complete JSON record; multiLine lets one record span multiple lines.

• CprimitivesAsString set to true

  Incorrect

  This infers all primitive values as strings; it does not change how records are split.

• DallowComments set to true

  Incorrect

  This ignores Java and C++ style comments in JSON; the problem here is records spanning lines.

The JSON reader expects JSON Lines by default. Files with one object across many lines need multiLine.

• Araw.store.bicycle.color

  Incorrect

  Dot notation navigates STRUCT columns; raw is a string, so there are no fields to navigate.

• Bvariant_get(raw, '$.store.bicycle.color', 'string')

  Incorrect

  variant_get takes a VARIANT expression; raw is a STRING column.

• Cexplode(raw)

  Incorrect

  explode turns an array or map into rows; it does not extract a field from a JSON string.

• Draw:store.bicycle.color

  Correct

  For JSON strings, the column:path syntax extracts a field, and dot notation after the colon reaches nested fields.

Databricks SQL can query JSON strings directly with column:path syntax; the results are strings. For better read performance, extract frequently used fields into typed columns.

• A.option("singleVariantColumn", "data")

  Correct

  This loads each record as a single VARIANT column; because the whole record is one value, no schema evolution occurs during ingestion.

• B.option("cloudFiles.schemaEvolutionMode", "rescue")

  Incorrect

  Rescue mode keeps a fixed column schema and puts unexpected fields in the rescued data column; the record is still split into columns.

• C.option("cloudFiles.inferColumnTypes", "true")

  Incorrect

  This makes inference choose types for each column; records are still split into columns that can evolve.

• D.option("cloudFiles.format", "binaryFile")

  Incorrect

  binaryFile loads raw file bytes and metadata, so the JSON fields would not be queryable.

Landing whole records as VARIANT makes ingestion robust to schema drift. Malformed records and records over 16 MB cannot be stored as VARIANT.

• ASELECT * FROM read_files('/Volumes/main/landing/books', format => 'xml', rowTag => 'catalog')

  Incorrect

  The rowTag element becomes the row, so with catalog as the row tag the whole file is one row that holds an array of book structs.

• BSELECT * FROM read_files('/Volumes/main/landing/books', format => 'xml', rootTag => 'book')

  Incorrect

  rootTag is a writer option for the root element that wraps the row elements in XML output; reading XML needs rowTag.

• CSELECT * FROM read_files('/Volumes/main/landing/books', format => 'xml', rowTag => 'book')

  Correct

  rowTag names the XML element that maps to a row; with book as the row tag, each book becomes a row and its child elements and attributes become its fields.

• DSELECT * FROM read_files('/Volumes/main/landing/books', format => 'xml')

  Incorrect

  rowTag is a required option for reading XML; without it the reader does not know which element maps to a row.

XML does not map to rows by itself, so the XML reader needs the rowTag option: the element it names becomes the top-level struct of each row, and its child elements become the fields. Databricks reads XML natively with read_files, Auto Loader and COPY INTO.