ADA-C01 Question 343
Single answerCreate external tablesA data engineering team stores daily JSON files in an Amazon S3 bucket. The bucket already has a named external stage in Snowflake called RAW_STAGE that points to the correct S3 location and uses a file format named JSON_FF. The team needs to create an external table so analysts can query two attributes from the JSON payload: customerId as VARCHAR and orderTotal as NUMBER. They also want to retain the source filename for troubleshooting. Which statement should the Snowflake administrator use?
- A
CREATE EXTERNAL TABLE ext_orders AS SELECT $1:customerId::VARCHAR AS customerId, $1:orderTotal::NUMBER AS orderTotal, METADATA$FILENAME AS src_file FROM @RAW_STAGE FILE_FORMAT = (FORMAT_NAME = JSON_FF);
- B
CREATE EXTERNAL TABLE ext_orders (customerId VARCHAR AS (VALUE:customerId::VARCHAR), orderTotal NUMBER AS (VALUE:orderTotal::NUMBER), src_file VARCHAR AS (METADATA$FILENAME)) LOCATION = @RAW_STAGE FILE_FORMAT = (FORMAT_NAME = JSON_FF) AUTO_REFRESH = TRUE;
- C
CREATE TABLE ext_orders (customerId VARCHAR, orderTotal NUMBER, src_file VARCHAR) STAGE = @RAW_STAGE FILE_FORMAT = (FORMAT_NAME = JSON_FF) EXTERNAL = TRUE;
- D
CREATE EXTERNAL TABLE ext_orders LOCATION = @RAW_STAGE PATTERN = '.*\.json' REFRESH_ON_CREATE = TRUE AS (customerId VARCHAR, orderTotal NUMBER, src_file VARCHAR);
Show answer and explanation
Correct answer: B
Explanation
To create an external table in Snowflake over files in cloud storage, the administrator uses CREATE EXTERNAL TABLE with a LOCATION that points to an external stage and defines columns in the table DDL. For semi-structured data such as JSON, external table columns are typically virtual columns derived from the VALUE pseudo-column, for example VALUE:customerId::VARCHAR. Metadata columns such as METADATA$FILENAME can also be exposed as virtual columns for operational troubleshooting and auditability. A common mistake is trying to use CTAS syntax or treating an external table like a regular table with an EXTERNAL flag. Snowflake documentation for CREATE EXTERNAL TABLE and querying staged/semi-structured data describes these patterns and emphasizes defining virtual columns from VALUE along with optional refresh behavior such as AUTO_REFRESH when properly configured.
- A. Incorrect.
Incorrect. External tables are not created with a CTAS-style SELECT from a stage. Instead, Snowflake requires a CREATE EXTERNAL TABLE statement that defines columns, typically with virtual column expressions based on VALUE for semi-structured data. This option resembles creating a regular table from queried staged files, not defining an external table over files in cloud storage.
- B. Correct.
Correct. This is the valid pattern for creating an external table over semi-structured files in an external stage. For JSON files, external table columns are usually defined as expressions over VALUE, such as VALUE:customerId::VARCHAR. Including a virtual column for METADATA$FILENAME is also supported for lineage and troubleshooting. LOCATION references the named stage, FILE_FORMAT points to the JSON file format, and AUTO_REFRESH can be enabled when supported and configured for the cloud provider integration.
- C. Incorrect.
Incorrect. Snowflake does not create external tables by adding EXTERNAL = TRUE to a regular CREATE TABLE statement, and STAGE is not a valid clause for CREATE TABLE in this manner. This option reflects a common misconception that external tables are just regular tables with a flag, when in reality they are a distinct object type with specific syntax.
- D. Incorrect.
Incorrect. The syntax is invalid. External tables do not use an AS clause to declare standard typed columns in this format. Column definitions for external tables must be included directly in the CREATE EXTERNAL TABLE statement, and for JSON they should generally be virtual columns derived from VALUE. Although PATTERN and REFRESH_ON_CREATE are valid concepts in some external table definitions, the statement structure here is not valid Snowflake SQL.