DP-203 Practice Questions

:

Scenario: Implement a surrogate key to account for changes to the retail store addresses.

A surrogate key on a table is a column with a unique identifier for each row. The key is not generated

from the table data. Data modelers like to create surrogate keys on their tables when they design

data warehouse models. You can use the IDENTITY property to achieve this goal simply and

effectively without affecting load performance.

Reference:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehousetables-identity

https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-203/page_85_img_1.jpg

Box 1: DATEDIFF

DATEDIFF function returns the count (as a signed integer value) of the specified datepart boundaries

crossed between the specified startdate and enddate.

Syntax: DATEDIFF ( datepart , startdate, enddate )

Box 2: LAST

The LAST function can be used to retrieve the last event within a specific condition. In this example,

the condition is an event of type Start, partitioning the search by PARTITION BY user and feature. This

way, every user and feature is treated independently when searching for the Start event. LIMIT

DURATION limits the search back in time to 1 hour between the End and Start events.

Example:

SELECT

[user],

feature,

DATEDIFF(

second,

LAST(Time) OVER (PARTITION BY [user], feature LIMIT DURATION(hour,

1) WHEN Event = 'start'),

Time) as duration

FROM input TIMESTAMP BY Time

WHERE

Event = 'end'

Reference:

https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-stream-analytics-querypatterns

:

Scenario: Purge Twitter feed data records that are older than two years.

Data sets have unique lifecycles. Early in the lifecycle, people access some data often. But the need

for access often drops drastically as the data ages. Some data remains idle in the cloud and is rarely

accessed once stored. Some data sets expire days or months after creation, while other data sets are

actively read and modified throughout their lifetimes. Azure Storage lifecycle management offers a

rule-based policy that you can use to transition blob data to the appropriate access tiers or to expire

data at the end of the data lifecycle.

Reference:

https://docs.microsoft.com/en-us/azure/storage/blobs/lifecycle-management-overview

https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-203/page_95_img_1.jpg

Reference:

https://docs.microsoft.com/en-us/azure/data-factory/concepts-integration-runtime

Avro supports batch and is very relevant for streaming.

Note: Avro is framework developed within Apache’s Hadoop project. It is a row-based storage format

which is widely used as a serialization process. AVRO stores its schema in JSON format making it easy

to read and interpret by any program. The data itself is stored in binary format by doing it compact

and efficient.

Reference:

https://www.adaltas.com/en/2020/07/23/benchmark-study-of-different-file-format/

Apache Spark Structured Streaming is a fast, scalable, and fault-tolerant stream processing API. You

can use it to perform analytics on your streaming data in near real-time.

With Structured Streaming, you can use SQL queries to process streaming data in the same way that

you would process static data.

Azure Event Hubs is a scalable real-time data ingestion service that processes millions of data in a

matter of seconds. It can receive large amounts of data from multiple sources and stream the

prepared data to Azure Data Lake or Azure Blob storage.

Azure Event Hubs can be integrated with Spark Structured Streaming to perform the processing of

messages in near real-time. You can query and analyze the processed data as it comes by using a

Structured Streaming query and Spark SQL.

Reference: https://k21academy.com/microsoft-azure/data-engineer/structured-streaming-withazure-event-hubs/

Type 2 - Creating a new additional record. In this methodology all history of dimension changes is

kept in the database. You capture attribute change by adding a new row with a new surrogate key to

the dimension table. Both the prior and new rows contain as attributes the natural key(or other

durable identifier). Also 'effective date' and 'current indicator' columns are used in this method.

There could be only one record with current indicator set to 'Y'. For 'effective date' columns, i.e.

start_date and end_date, the end_date for current record usually is set to value 9999-12-31.

Introducing changes to the dimensional model in type 2 could be very expensive database operation

so it is not recommended to use it in dimensions where a new attribute could be added in the future.

https://www.datawarehouse4u.info/SCD-Slowly-Changing-Dimensions.html

https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-203/page_151_img_1.jpg

Range Left or Right, both are creating similar partition but there is difference in comparison

For example: in this scenario, when you use LEFT and 20100101,20110101,20120101

Partition will be, datecol20100101 and datecol20110101

and datecol20120101

But if you use range RIGHT and 20100101,20110101,20120101

Partition will be, datecol=20100101 and datecol=20110101

and datecol=20120101

In this example, Range RIGHT will be suitable for calendar comparison Jan 1st to Dec 31st

Reference:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transactsql?view=sql-server-ver15