ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam DP-203 All Questions

View all questions & answers for the DP-203 exam
Go to Exam

Exam DP-203 topic 2 question 54 discussion

Actual exam question from Microsoft's DP-203
Question #: 54
Topic #: 2
[All DP-203 Questions]

HOTSPOT -
You are implementing an Azure Stream Analytics solution to process event data from devices.
The devices output events when there is a fault and emit a repeat of the event every five seconds until the fault is resolved. The devices output a heartbeat event every five seconds after a previous event if there are no faults present.
A sample of the events is shown in the following table.

You need to calculate the uptime between the faults.
How should you complete the Stream Analytics SQL query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer Hide Answer
Suggested Answer:
Box 1: WHERE EventType='HeartBeat'
Box 2: ,TumblingWindow(Second, 5)
Tumbling windows are a series of fixed-sized, non-overlapping and contiguous time intervals.
The following diagram illustrates a stream with a series of events and how they are mapped into 10-second tumbling windows.

Incorrect Answers:
,SessionWindow.. : Session windows group events that arrive at similar times, filtering out periods of time where there is no data.
Reference:
https://docs.microsoft.com/en-us/stream-analytics-query/session-window-azure-stream-analytics https://docs.microsoft.com/en-us/stream-analytics-query/tumbling-window-azure-stream-analytics
by Fer079 at Dec. 15, 2021, 8:02 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Fer079
Highly Voted 3 years, 5 months ago
I think the right answers should be WHERE EventType='HeartBeat' and Session window. If we want to calculate the uptime between the faults, we must use session window for each device, we know that will be receiving events for each 5 seconds if there is no error, so when an error occurs (or if we reach the maximum size of the window) then a new event will not be received within the next 5 seconds and the window will close, calculating the uptime. However if We use Tumbling window, it´s not possible to calculate the uptime beyond 5 seconds
upvoted 96 times
Pian12345
6 months, 4 weeks ago
totally agree with this
upvoted 1 times
...
ovokpus
3 years, 3 months ago
I concur!
upvoted 1 times
...
onyerleft
3 years, 5 months ago
Yes this sounds right
upvoted 3 times
...
Davico93
2 years, 11 months ago
what happen if the event continues and the 50,000 second finishes? you cannot count that as a fault event
upvoted 2 times
Davico93
2 years, 11 months ago
Sorry, you are right @Fer079!
upvoted 2 times
...
...
Load full discussion...
...
Canary_2021
Highly Voted 3 years, 5 months ago
My answer is: Question 1: B. Use LAG function as a filter to only filter out the events that switch from 'HeartBeat' to fault or witch from fault to 'HeartBeat'. Question 2: C. No matter if there is a fault, device always sends message every 5min. Calculate the uptime between the faults don't need any window here. Any duration > 5s should between fault line and heartbeat line should be part of items that need to count into to calculate duration.
upvoted 24 times
Fer079
3 years, 4 months ago
You cannot use the LAG function here because the "partition by" by deviceId is not included here, so the change between the status could be between different devices. This LAG function is evaluated before the "group by" clause of the query. If you see the Microsoft documentation: https://docs.microsoft.com/en-us/stream-analytics-query/lag-azure-stream-analytics It says clearly that "LAG isn't affected by predicates in the WHERE clause, join conditions in the JOIN clause, or grouping expressions in the GROUP BY clause of the current query because it's evaluated before those clauses."
upvoted 8 times
ubaldo1002
3 years, 1 month ago
LAG does not require the PARTITION BY this is optional..
upvoted 2 times
...
mamahani
2 years, 1 month ago
you do not need partition by with LAG function; its an optional parameter; however in this scenario this is not the reason why we should not be using this function; with LAG we will receive in the query result only the "transition" events i.e. the device works correctly (eventype'heartbeat;) and then there is fault ('fault')-> we would receive only the record with "faul" (as its different then previous line event i.e. heartbeat; by this one record we will not know how long the device was operational correctly, because we dont have these records anymore; we need to have 'startting ' record for correctly operating device with heartbeat event , and this for every singe "re-start' after the fault; LAG function would be good to calculate e.g. the increasing heartbeat by comparing the heartbeat of previous records with current one; but not in this user case;
upvoted 1 times
...
...
...
8ac3742
Most Recent 8 months ago
SELECT DeviceID, MIN(EventTime) AS StartTime, MAX(EventTime) AS EndTime, DATEDIFF(second, MIN(EventTime), MAX(EventTime)) AS duration_in_seconds FROM input TIMESTAMP BY EventTime WHERE EventType = 'HeartBeat' GROUP BY DeviceID, TumblingWindow(second, 5) HAVING DATEDIFF(second, MIN(EventTime), MAX(EventTime)) > 5
upvoted 1 times
...
renan_ineu
8 months, 2 weeks ago
1. You want "between faults" and between faults there are heartbeats. So EventType must be heartbeat. 2. Usually I'd go with Session (check link), but this session timing is bugging me. Why 50k seconds for a window limit? I will probably have a global uptime, not uptimes between faults... So I'll go with tumbling. https://learn.microsoft.com/en-us/stream-analytics-query/session-window-azure-stream-analytics
upvoted 1 times
...
RG_123
9 months, 2 weeks ago
Chat GPT - EventType = 'Heartbeat', Session Window To calculate the uptime between faults in the Azure Stream Analytics SQL query, you should complete the query by using the SessionWindow function, which groups events based on a period of inactivity, allowing you to calculate durations of uptime between faults. Here's how to complete the query: Filter the HeartBeat events: You should filter only for HeartBeat events because these indicate the system is running without faults. Use SessionWindow: This function groups events into sessions based on gaps of inactivity. In this scenario, a session is defined by the HeartBeat events, which will end when a fault occurs. Calculate the duration: The DATEDIFF function is used to calculate the time difference between the start and end of each session, giving you the uptime between faults.
upvoted 2 times
...
kkk5566
1 year, 9 months ago
WHERE EventType='HeartBeat' and Session window.
upvoted 2 times
...
rocky48
2 years ago
1. Where EventType = 'HeartBeat' 2. SessionWindow
upvoted 4 times
...
SinSS
2 years, 1 month ago
1. Where EventType = 'HeartBeat' 2. SessionWindow
upvoted 1 times
...
dom271219
2 years, 9 months ago
The where clause must be EvenType != 'HearBeat' otherwise you're not counting the uptime between the fault
upvoted 1 times
dom271219
2 years, 9 months ago
Sorry ignore it
upvoted 1 times
...
...
Deeksha1234
2 years, 10 months ago
Agree with Fer079 , EventType='HeartBeat' and Session window is correct
upvoted 1 times
...
uzairahm
2 years, 11 months ago
WHERE EventType='HeartBeat' is definitely correct as you would need to filter out other events to calculate the uptime. If you look at the example in link https://docs.microsoft.com/en-us/stream-analytics-query/session-window-azure-stream-analytics it would be crystal clear that sessionwindow is the right answer and @iooj (a lot of thanks) has already tested it
upvoted 1 times
...
sparkchu
3 years, 2 months ago
tricky but instructive question.
upvoted 2 times
...
iooj
3 years, 4 months ago
I created a Stream Analytics job and tested all combinations and here is my answer. With a tumbling window, you will never be able to accumulate the correct interval. The session is suitable here, but if the session closes earlier (by timeout) than the event occurs, then it will also fail to accumulate. So please note that in the timeout should be 6, not 5. A working version: EventType='HeartBeat' and SessionWindow(second, 6, 50000). But... P.S. In the data example on the screenshot, the difference is generally indicated in minutes, in this situation, none of the answers will work, you will need to change seconds to minutes.
upvoted 13 times
MadEgg
3 years ago
Thanks for testing it, but I think your conclusion is wrong. We should calculate the difference (without any limitation). If you use SessionWindow with a timeout of 6 you limit this functionality. You get the right answer for the data in the table but what happens if you have a failure after >6 seconds? I think Canary_2021 is right -> B, C P.S. :-D didn't recognized it... but would say that this is a typo in the table.
upvoted 1 times
...
...
romanzdk
3 years, 4 months ago
B and A?
upvoted 1 times
...
engrbrain
3 years, 5 months ago
The answer is BC. Every T_SQL Group by Query that needs to calculate max based on certain criteria should use the HAVING function to group that criteria
upvoted 5 times
...
MFR
3 years, 5 months ago
For me the session window suits for the given scenario. Also no device ID has been considered in the given answer, which is essential for calculating the uptime period per device
upvoted 4 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel