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…

Question

Options

• ACreate it as an external table, which is not covered by Time Travel.
• BCreate it as a transient table with a retention time of 0 days.
• CCreate it as a temporary table with a retention time of 0 days.
• DCreate it as a permanent table with a retention time of 0 days.

• A
  5% (3)
• B
  78% (47)
• C
  3% (2)
• D
  13% (8)

Explanation

Transient tables with DATA_RETENTION_TIME_IN_DAYS = 0 are the correct choice because they persist across sessions (allowing multiple departments to access them), incur zero Time Travel storage overhead, and - unlike permanent tables - have no Fail-safe period, eliminating the 7-day Fail-safe storage cost that makes permanent tables expensive for throwaway staging data.

Why the distractors are wrong:

• A (external table): External tables store data outside Snowflake and query it via cloud storage, which significantly hurts performance due to lack of micro-partition optimization, clustering, and caching - it does not maximize performance.
• C (temporary table): Temporary tables are session-scoped; they are dropped when the session ends. Since multiple departments need to access the same staging table (likely across different sessions and users), a temporary table simply won't work here.
• D (permanent table, retention 0): Setting retention to 0 removes Time Travel overhead, but permanent tables always carry a 7-day Fail-safe (non-configurable), adding unnecessary storage cost for data that's replaced daily anyway.

Memory tip: Distinguish B vs. C with this rule - "Temp = gone when you leave, Transient = stays for others." For staging data that multiple teams share, you need it to outlive the session that created it, so transient wins. Then remember that transient skips Fail-safe entirely, while permanent cannot - making transient the cost-minimizing champion for disposable staging tables.

No community discussion yet for this question.