Free DEA-C01 Exam Braindumps (page: 1)

Page 1 of 33

Streams cannot be created to query change data on which of the following objects? [Select All that Apply]

  1. Standard tables, including shared tables.
  2. Views, including secure views
  3. Directory tables
  4. Query Log Tables
  5. External tables

Answer(s): D

Explanation:

Streams supports all the listed objects except Query Log tables.



Tasks may optionally use table streams to provide a convenient way to continuously process new or changed data. A task can transform new or changed rows that a stream surfaces. Each time a task is scheduled to run, it can verify whether a stream contains change data for a table and either consume the change data or skip the current run if no change data exists. Which System Function can be used by Data engineer to verify whether a stream contains changed data for a table?

  1. SYSTEM$STREAM_HAS_CHANGE_DATA
  2. SYSTEM$STREAM_CDC_DATA
  3. SYSTEM$STREAM_HAS_DATA
  4. SYSTEM$STREAM_DELTA_DATA

Answer(s): C

Explanation:

SYSTEM$STREAM_HAS_DATA
Indicates whether a specified stream contains change data capture (CDC) records.



1. + +
2. | SYSTEM$CLUSTERING_INFORMATION('SF_DATA', '(COL1, COL3)') |
3. | |
4. | { |
5. | "cluster_by_keys" : "(COL1, COL3)", |
6. | "total_partition_count" : 1156, |
7. | "total_constant_partition_count" : 0, |
8. | "average_overlaps" : 117.5484, |
9. | "average_depth" : 64.0701, |
10. | "partition_depth_histogram" : { | 11. | "00000" : 0, |
12. | "00001" : 0, |
13. | "00002" : 3, |
14. | "00003" : 3, |
15. | "00004" : 4, |
16. | "00005" : 6, |
17. | "00006" : 3, |
18. | "00007" : 5, |
19. | "00008" : 10, |
20. | "00009" : 5, |
21. | "00010" : 7, |
22. | "00011" : 6, |
23. | "00012" : 8, |
24. | "00013" : 8, |
25. | "00014" : 9, |
26. | "00015" : 8, |
27. | "00016" : 6, |
28. | "00032" : 98, |
29. | "00064" : 269, |
30. | "00128" : 698 |
31. | } |
32. | } |
33. + +

The Above example indicates that the SF_DATA table is not well-clustered for which of following valid reasons?

  1. Zero (0) constant micro-partitions out of 1156 total micro-partitions.
  2. High average of overlapping micro-partitions.
  3. High average of overlap depth across micro-partitions.
  4. Most of the micro-partitions are grouped at the lower-end of the histogram, with the majority of micro-partitions having an overlap depth between 64 and 128.
  5. ALL of the above

Answer(s): E



Mark a Data Engineer, looking to implement streams on local views & want to use change tracking metadata for one of its Data Loading use case. Please select the incorrect understanding points of Mark with respect to usage of Streams on Views?

  1. For streams on views, change tracking must be enabled explicitly for the view and underlying tables to add the hidden columns to these tables.
  2. The CDC records returned when querying a stream rely on a combination of the offset stored in the stream and the change tracking metadata stored in the table.
  3. Views with GROUP BY & LIMIT Clause are supported by Snowflake.
  4. As an alternative to streams, Snowflake supports querying change tracking metadata for views using the CHANGES clause for SELECT statements.
  5. Enabling change tracking adds a pair of hidden columns to the table and begins storing change tracking metadata. The values in these hidden CDC data columns provide the input for the stream metadata columns. The columns consume a small amount of storage.

Answer(s): C

Explanation:

A stream object records data manipulation language (DML) changes made to tables, including inserts, updates, and deletes, as well as metadata about each change, so that actions can be taken using the changed data. This process is referred to as change data capture (CDC). An individual table stream tracks the changes made to rows in a source table. A table stream (also referred to as simply a “stream”) makes a “change table” available of what changed, at the row level, between two transactional points of time in a table. This allows querying and consuming a sequence of change records in a transactional fashion.

Streams can be created to query change data on the following objects:
-Standard tables, including shared tables.
-Views, including secure views
-Directory tables
-External tables

When created, a stream logically takes an initial snapshot of every row in the source object (e.g. table, external table, or the underlying tables for a view) by initializing a point in time (called an offset) as the current transactional version of the object. The change tracking system utilized by the stream then records information about the DML changes after this snapshot was taken. Change records provide the state of a row before and after the change. Change information mirrors the column structure of the tracked source object and includes additional metadata columns that describe each change event.

Note that a stream itself does not contain any table data. A stream only stores an offset for the source object and returns CDC records by leveraging the versioning history for the source object. When the first stream for a table is created, a pair of hidden columns are added to the source table and begin storing change tracking metadata. These columns consume a small amount of storage. The CDC records returned when querying a stream rely on a combination of the offset stored in the stream and the change tracking metadata stored in the table. Note that for streams on views, change tracking must be enabled explicitly for the view and underlying tables to add the hidden columns to these tables.

Streams on views support both local views and views shared using Snowflake Secure Data Sharing, including secure views. Currently, streams cannot track changes in materialized views.
Views with the following operations are not yet supported:
-GROUP BY clauses
-QUALIFY clauses
-Subqueries not in the FROM clause
-Correlated subqueries
-LIMIT clauses Change Tracking:

Change tracking must be enabled in the underlying tables.
Prior to creating a stream on a view, you must enable change tracking on the underlying tables for the view.
Set the CHANGE_TRACKING parameter when creating a view (using CREATE VIEW) or later (using ALTER VIEW).
As an alternative to streams, Snowflake supports querying change tracking metadata for tables or views using the CHANGES clause for SELECT statements. The CHANGES clause enables query-ing change tracking metadata between two points in time without having to create a stream with an explicit transactional offset.



Page 1 of 33



Post your Comments and Discuss Snowflake DEA-C01 exam with other Community members:

DC 6/16/2023 9:35:26 AM
Where are the questions???
Anonymous
upvote