ARA-C01 Question 163
Single answerStorage directory structureA data platform team is building an external stage on Amazon S3 for raw event ingestion. Files arrive under date-based paths such as s3://acme-raw/events/2026/04/20/ and multiple Snowpipe definitions are planned for different event domains. The architects want a storage directory structure that minimizes unnecessary file scanning, simplifies object lifecycle management, and supports independent loading pipelines for each domain. Which design should the team choose?
- A
Use a single top-level prefix such as s3://acme-raw/all_files/ and rely on PATTERN clauses in Snowpipe and COPY INTO to separate domains and dates
- B
Organize objects into meaningful hierarchical prefixes such as s3://acme-raw/
/year= /month= /day= - / and create stages or Snowpipe definitions that target the relevant prefix
- C
Store all domains in the same prefix and create one directory table to physically reorganize the underlying S3 objects for better pruning
- D
Flatten the structure so files are written directly under the bucket root, because Snowflake metadata tracking performs the same regardless of object path depth
Show answer and explanation
Correct answer: B
Explanation
The best answer is to organize external stage data using meaningful prefixes, typically by business domain and time components, then point stages, Snowpipe definitions, or COPY commands at the narrowest practical path. In cloud object storage, the "directory structure" is represented by object key prefixes, and those prefixes matter operationally even though the storage is object-based rather than a true filesystem. For Snowflake architectures, this supports better pipeline isolation, more efficient file discovery, easier lifecycle policy application, and clearer ownership boundaries. Snowflake documentation for external stages, Snowpipe, and directory tables emphasizes that stages can reference subpaths and that directory tables expose file metadata rather than reorganize storage. Therefore, storage layout decisions should be made in the cloud storage design, not delegated to Snowflake metadata features.
- A. Incorrect.
Incorrect. Although PATTERN can filter files, placing all files under one broad prefix forces Snowflake integrations and loading processes to evaluate a larger file set than necessary. This increases operational complexity and can reduce efficiency, especially when separate domains have independent ingestion and retention requirements. A broad shared prefix is a common anti-pattern when multiple pipelines need isolation.
- B. Correct.
Correct. Using domain-specific and date-based prefixes is a best-practice storage directory structure for external stages. It reduces the scope of file discovery, allows separate Snowpipe or COPY processes to target only the relevant path, and aligns well with cloud lifecycle policies and operational ownership. Prefix-based organization is especially important when different data domains have independent pipelines, security boundaries, or retention needs.
- C. Incorrect.
Incorrect. A Snowflake directory table does not physically reorganize files in S3. It stores metadata about staged files to support querying and automation use cases. The misconception is treating a directory table like a storage optimization feature; actual object organization must be designed in the cloud storage path structure itself.
- D. Incorrect.
Incorrect. Flattening all files into the bucket root makes prefix-based targeting, governance, and lifecycle management harder. While cloud object storage is not a traditional hierarchical filesystem, object key prefixes are still operationally significant. Snowflake can target subpaths in stages, and well-designed prefixes help reduce unnecessary scanning and simplify pipeline separation.