DEA-C02 Question #78: Real Exam Question with Answer & Explanation

Question

How should a Data Engineer build a pipeline that executes a stored procedure to parse and load unstructured data files as the files arrive in a stage?

Options

• ACreate a Snowpipe with the parameter TRUE.
• BCreate a Snowpipe that includes the INTEGRATION = '<procedure name>' parameter.
• CCreate a stage with parameter ENCODING = '<procedure name>' to call the procedure when
• DCreate a stage with a directory table, a stream on the directory table, and a task referencing the

Explanation

Option D is the correct architectural pattern because Snowflake's native mechanism for detecting new files in a stage relies on a directory table (which catalogs file metadata), a stream on that directory table (which captures change events when new files arrive), and a task that polls the stream and triggers the stored procedure to parse and load each new file. This chain - stage → directory table → stream → task → stored procedure - is the designed Snowflake solution for unstructured file processing with custom logic.

Why the distractors are wrong:

• A is incorrect because Snowpipe automates COPY INTO for structured/semi-structured data; it has no TRUE parameter that invokes a stored procedure.
• B is incorrect because the INTEGRATION parameter in Snowpipe refers to a notification integration (e.g., SQS for S3 events), not a stored procedure name - it's real syntax repurposed as a misleading distractor.
• C is incorrect because ENCODING is a file format property specifying character encoding (e.g., UTF-8); stages have no mechanism to invoke procedures via an encoding parameter.

Memory tip: Think of the pattern as a watchdog chain - Directory table Detects, Stream Signals, Task Triggers, Procedure Processes. All four links are needed; Snowpipe alone can't call custom procedures.

No community discussion yet for this question.