SnowPro Advanced: Data Engineer — Question 74

Data is loaded into a staging table every day. From there, several departments perform transformations on the data and load it into different production tables.

How should the staging table be created and used to MINIMIZE storage costs and MAXIMIZE performance?

Answer options

• A. Create it as an external table, which is not covered by Time Travel.
• B. Create it as a transient table with a retention time of 0 days.
• C. Create it as a temporary table with a retention time of 0 days.
• D. Create it as a permanent table with a retention time of 0 days.

Correct answer: B

Explanation

Creating the staging table as a transient table with a retention time of 0 days minimizes storage costs because transient tables do not incur costs for historical data retention, while still allowing for efficient data transformation processes. Options A and D involve external and permanent tables, respectively, which would incur additional storage costs due to retention policies. Option C, while also having a retention time of 0 days, is less optimal as temporary tables are session-specific and may not be suitable for daily data loads across departments.