Consider a scenario where you are loading data from an external stage (AWS S3) into a Snowflake table. The data contains date fields in various formats. You want to ensure that all dates are…

Question

Options

• ADATE FORMAT = 'YYYY-MM-DD'
• BTIMESTAMP FORMAT = 'YYYY-MM-DD'
• CCONVERT TIMEZONE = FALSE
• DON ERROR = 'SKIP FILE'
• EDATE INPUT FORMAT = 'AUTO', DATE OUTPUT FORMAT = 'YYYY-MM-DD'

• B
  10% (3)
• C
  3% (1)
• D
  3% (1)
• E
  83% (25)

Explanation

Option E is correct because the data contains dates in various formats, so DATE INPUT FORMAT = 'AUTO' lets Snowflake automatically detect and parse whatever date formats appear in the source file, while DATE OUTPUT FORMAT = 'YYYY-MM-DD' enforces a consistent format upon loading - addressing both requirements in the scenario simultaneously. Option A is a trap: specifying a single fixed input format like 'YYYY-MM-DD' would fail on any row whose date doesn't already match that exact format, which is the opposite of handling "various formats" (the syntax is also wrong - Snowflake uses underscores: DATE_FORMAT). Option B targets TIMESTAMP values, not DATE fields - a different data type entirely. Option C (CONVERT TIMEZONE) controls timezone offset behavior and has no bearing on date format standardization. Option D (ON ERROR = 'SKIP FILE') is an error-handling directive that silently drops bad files rather than transforming any data.

Memory tip: Think "AUTO in, formatted out" - when the source has mixed formats, AUTO handles the chaos coming in, and the output format parameter locks down what lands in your table.

No community discussion yet for this question.