Download Data Engineering on Microsoft Azure (beta).dump4pass.DP-203.2022-04-07.5e.199q.vcex

Box 1: Type 2 
A Type 2 SCD supports versioning of dimension members. Often the source system doesn't store versions, so the data warehouse load process detects and manages changes in a dimension table. In this case, the dimension table must use a surrogate key to provide a unique reference to a version of the dimension member. It also includes columns that define the date range validity of the version (for example, StartDate and EndDate) and possibly a flag column (for example, IsCurrent) to easily filter by current dimension members.  
  
Incorrect Answers: 
A Type 1 SCD always reflects the latest values, and when changes in source data are detected, the dimension table data is overwritten.  
  
Box 2: a business key 
A business key or natural key is an index which identifies uniqueness of a row based on columns that exist naturally in a table according to business rules. For example business keys are customer code in a customer table, composite of sales order header number and sales order item line number within a sales order details table.  
  
Reference: 
https://docs.microsoft.com/en-us/learn/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types  

Hash-distributed tables improve query performance on large fact tables, and are the focus of this article.  
Round-robin tables are useful for improving loading speed.  
  
Incorrect: 
Not D: Do not use a date column. . All data for the same date lands in the same distribution. If several users are all filtering on the same date, then only 1 of the 60 distributions do all the processing work.  
  
Reference: 
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute  

Use the same definition as the EmployeeID column.  
  
Reference: 
https://docs.microsoft.com/en-us/analysis-services/tabular-models/hierarchies-ssas-tabular 

Once a database has been created by a Spark job, you can create tables in it with Spark that use Parquet as the storage format. Table names will be converted to lower case and need to be queried using the lower case name. These tables will immediately become available for querying by any of the Azure Synapse workspace Spark pools. They can also be used from any of the Spark jobs subject to permissions.  
Note: For external tables, since they are synchronized to serverless SQL pool asynchronously, there will be a delay until they appear.  
Reference: 
https://docs.microsoft.com/en-us/azure/synapse-analytics/metadata/table 

Step 1: Create an empty table named SalesFact_work that has the same schema as SalesFact. 
Step 2: Switch the partition containing the stale data from SalesFact to SalesFact_Work. 
SQL Data Warehouse supports partition splitting, merging, and switching. To switch partitions between two tables, you must ensure that the partitions align on their respective boundaries and that the table definitions match.  
  
Loading data into partitions with partition switching is a convenient way stage new data in a table that is not visible to users the switch in the new data.  
Step 3: Drop the SalesFact_Work table. 
Reference: 
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition 

To run a T-SQL query over a set of files within a folder or set of folders while treating them as a single entity or rowset, provide a path to a folder or a pattern (using wildcards) over a set of files or folders.  
  
Reference: 
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-data-storage#query-multiple-files-or-folders  

Report1: CSV 
CSV: The destination writes records as delimited data. 
  
Report2: AVRO 
AVRO supports timestamps.  
Not Parquet, TSV: Not options for Azure Data Lake Storage Gen2. 
Reference: 
https://streamsets.com/documentation/datacollector/latest/help/datacollector/UserGuide/Destinations/ADLS-G2-D.html

There's an important reason to put the date at the end of the directory structure. If you want to lock down certain regions or subject matters to users/groups, then you can easily do so with the POSIX permissions.  
Otherwise, if there was a need to restrict a certain security group to viewing just the UK data or certain planes, with the date structure in front a separate permission would be required for numerous directories under every hour directory. Additionally, having the date structure in front would exponentially increase the number of directories as time went on.  
  
Note: In IoT workloads, there can be a great deal of data being landed in the data store that spans acrossnumerous products, devices, organizations, and customers. It’s important to pre-plan the directory layout for organization, security, and efficient processing of the data for down-stream consumers. A general template to consider might be the following layout:   
{Region}/{SubjectMatter(s)}/{yyyy}/{mm}/{dd}/{hh}/  

Box 1: Parquet 
Parquet stores data in columns, while Avro stores data in a row-based format. By their very nature, column-oriented data stores are optimized for read-heavy analytical workloads, while row-based databases are best for write-heavy transactional workloads.  
  
Box 2: Avro 
An Avro schema is created using JSON format.  
AVRO supports timestamps.  
  
Note: Azure Data Factory supports the following file formats (not GZip or TXT). 
Avro format  
Binary format  
Delimited text format  
Excel format  
JSON format  
ORC format  
Parquet format  
XML format  
  
Reference: 
https://www.datanami.com/2018/05/16/big-data-file-formats-demystified 

Box 1: Preserver herarchy 
Compared to the flat namespace on Blob storage, the hierarchical namespace greatly improves the performance of directory management operations, which improves overall job performance.  
  
Box 2: Parquet 
Azure Data Factory parquet format is supported for Azure Data Lake Storage Gen2.  
Parquet supports the schema property.  
  
Reference: 
https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-introduction   
https://docs.microsoft.com/en-us/azure/data-factory/format-parquet