You are working with a Snowflake external stage that contains a mix of structured and unstructured data. Specifically, you have a directory in your S3 bucket that contains both CSV files and JSON…

Question

Options

• ACreate two separate views, one for the CSV files and one for the JSON files, using the
• BCreate a single view that uses the 'METADATA$FILENAME column in the directory table to
• CCreate a single external table that automatically detects the file format and schema based on the
• DIt's not possible to combine data from different file formats (CSV and JSON) directly using directory
• ECreate a stored procedure that reads the directory table metadata, determines the file type for

• A
  74% (26)
• B
  3% (1)
• C
  3% (1)
• D
  14% (5)
• E
  6% (2)

Explanation

Option A is correct because CSV and JSON files have fundamentally incompatible parsing logic and schemas - you must handle them separately using two views (or external tables), each configured with the appropriate FILE_FORMAT and filtered by file extension via METADATA$FILENAME (e.g., WHERE METADATA$FILENAME LIKE '%.csv'). Once each view correctly parses its respective format, you can then UNION ALL them if a combined result is needed.

Why the distractors fail:

• B is tempting but wrong: METADATA$FILENAME alone can't resolve the parsing incompatibility within a single view - you'd still need different read logic per format, which a single view query can't cleanly express.
• C is wrong because Snowflake external tables don't auto-detect mixed file formats; you must explicitly specify FILE_FORMAT, and one external table maps to one format.
• D is wrong because combining the data is possible - just not in a single step without separation first.
• E is wrong because while a stored procedure could work, it's the wrong tool for creating a reusable view; it's overcomplicated and not how Snowflake recommends handling this pattern.

Memory tip: Think "parse first, combine second." Different file formats need different parsers, so always create one view per format, then unite the results - just like you'd sort mail before reading it.

No community discussion yet for this question.