A data engineer wants a target table to stay automatically up to date from a join of two base tables, declaring the desired result with a query and a freshness target while letting Snowflake schedule and run the incremental refreshes. The engineer prefers not to wire up streams and tasks by hand. Which object best fits this requirement?
- AA dynamic table defined by the join query with a target lag, so Snowflake automatically performs incremental refreshes to keep results within the declared freshness window Correct
- BA materialized view defined over the two base tables, so Snowflake transparently maintains the joined result and refreshes it automatically as the underlying data changes
- CA standard view over the join, because Snowflake caches and incrementally refreshes the view results in the background to meet a freshness target the engineer sets
- DA scheduled task that rebuilds the target table from the join every few minutes, because the recurring schedule keeps the joined result current without manual streams
Why A is correct: A dynamic table is declarative: the engineer specifies the query and a target lag, and Snowflake orchestrates incremental refreshes to keep it within that freshness window, removing the need to build streams and tasks manually.
Why B is wrong: A materialized view is tempting because it auto-maintains, but Snowflake materialized views cannot be defined on joins of multiple tables, so they cannot express this multi-table requirement.
Why C is wrong: A standard view stores no data and computes results at query time, so it has no stored result to keep fresh and offers no target-lag or incremental-refresh mechanism.
Why D is wrong: A scheduled task can refresh the table but the engineer must author the DML and schedule, it fully rebuilds rather than incrementally refreshing, and it is imperative rather than the declarative approach asked for.