=XAMTOPICS - Expert Verified, Online, Free. ‘ custom View Setings Topic 1 - Question Set 1You ave table in an Azure Synapse Analytics dedicated SQL poo, The table was created by sing the following Transact-SQL statement, CREATE TABLE [dbo] . [DimEmployee] ( [SmployeeKey] [int] IDENTITY (1,1) NOT NULL, (EmployeeTD] [int] NOT NULL, [FirstName] [varchar] (100) NOT NULL, [LastName] [varchar] (100) NOT NULL, (JobTitle] [varchar] (100) NULL, [LastHireDate] [date] NULL, (Streetaddress] [varchar] (500) NOT NULL, [City] [varchar] (200) NOP NULL, [StateProvince] [varchar] (50) NOT NULL, (Portalcode] [varchar] (10) NOT NULL ) You ned to alter the table to meet he following requiemens: > Ensure that users can identify the current manager of employees. c» Support creating an employee reporting hierarchy for your entre company. -» Provide fast lookup ofthe managers’ attributes such as name and job tte Which column should you add tothe table? {A [ManagerEmployee] [small] NULL 8. [ManagerEmployeekey|[smalint] NUL 6. [Manager mployeekey] int] NULL . [Managerttame][vrchar(200) NULL Correct Answer: © ‘We need an extra column to identity the Manager. Use the data type asthe EmployeeKey column, an int column, Reference: hnepsffdoes microsoft com/en-us/analysisservces/tabular-models/hierarchies-ssastabular Community vote dtrbuton © i Jakibick (aT ITS) 10 months 90 swerC, Smallnt eliminates A and . But would name the feld [ManagerEmployeelD] int] NULL since it should reference EmployeelD, not Employeekey since this one i IDENTITY. pote 14 tines DB Chandra2099 5 wecks, 2 days ago IDENTITY keyword suggests that its a surrogate key and it's 2 good practice to have a separate field forthe surrogate key which isnot 2 business fil like emp no or emp name puted Times © & anures0dtest 5 months 999 Why is smalint not possible. would sill support more than enough range to list all the employees inthe company and save space + speed up the lookup, puted 7 mes © & anures0otest 5 months 290 Salt i upto 32,767: he vers ‘pote I nee © & alexleonvatencia TTT) 11 months, 3 weeks 299 Lieespuesta es correcta puted 11 tines © & Mansoorsen9s (T=) day, shou a Selected Answer € s/)docsimicrosott.com/en-us/s/t-sq/data-types/nt-bigin-smalint-and-tinyit-transact-sviepte T ines © amipanah 3 weeks, 24ays 299 Guys i passed the exam today and i would say more than 90% ofthe questions was exactly the same as this! Good job Examtopic and community. upted tines 2 & hobeyor 2 months, 2 weeks 990 iknkis pte Times © A examtopieseap 3 months, 3 weeks ago correct, pte Times 1D & erazybobs 3 months, 3 weeks ago Soundsright pte Times © A Hansen 5 montis go Answer Cis pote Times © & Nikes015 montis ago “Answer. Looking atthe table itis bul for OLAP where Employeekey wll be serrogate key so basing on thatthe ManagerEmployeeKey Looks 4204, fype shoule match and NULL constraint shoul be apalied (big boss hae ne Bose) ‘puted 2ines © Mem $ months, 1 week ago correct ans iC pte Times © & mithomas 6 montis a90 €: ManagerEmployeeKey INT NULL Remember, his should be NUL since there are employees that may not have a manager Ge, the boss). This design is usually seen with recursive CTE proof of concep, pte Tver © & Dotty 6 months, 3 weeks ago Selected Answer C pte Times BA Egocentie 7 months, 2 weeks ago Cis te correct answer ‘puted times © & boggy011 7 months, 3 weeks ago Selected Answer € puted Times BW temace & months, 1 week ago Comect answers ‘puted T nes © & Guincimund & mons, 2 weeks ago answer's ‘puted T ines © & NeerajKumar 8 months ago Comect Ans iC puted 2 inesYou have an Azute Synapse workspace named MyWorkspace thal contain an Apache Spark database named mytestdb You run the following command in an Azute Synapse Analytics Spark pool in MyWorkspace ‘CREATE TABLE mytestdb.myParquetTabl ployeelD in, EmployeeName sting, EmployeeStartDate date) USING Parquet You then use Spark to insert row into mytestéb myParquetTable.Therow contains the folowing data EmployeeName | EmployeeID | EmployeeStartDate Alice 24 2020-01-25 (ne minte later you execute the following query rom a serverless SQL poolin MyWorkspace SELECT EmployeeiO- FROM mytestb. dbo myParquetTable WHERE EmployeeName = Alice’; What will be returned bythe query? Am B.anerror null value Correct Answer: A ‘Once a database has been created by a Spatk jo, you can create tables in t with Spark that use Parquet as the storage format. Table names will be converted to lower case and need tobe queried using th lowercase name, These tables wll mediately become availabe for querying by any of the Azure Synapse workspace Spark pools. They can also be used from any ofthe Spark jobs subject to permissions Note: For extemal tables, since they are synchronized to serveress SQL pool asynchronously there will be a delay util they appear. Reference: upsifdocs.mierosoft.com/en.us/azure/synapse-analytics/metadata/table Community vote dstrbution © & deatrypo [in weedd) 1 mort, 1 week ago ‘Answer i 8, but not because of the lowercase, The case has nothing to do withthe error. Ifyou look attentively, you wll notice that we create table mytestdb myParquetTable, but the select statement contains the reference to table mytestdb doo myParquetTable(!!- dbo) Here isthe eror message | got Error spark catalog requires a single-part namespace, but got [mytestdb, dbo] ‘puted tines © & gerie1979 (aS) month ago Ici test waited for one minute and tied the query ina serverless sal pool and received 24 asthe result, 50 I don't understand that B has been voted so much because the answer i A) 24 without a doubt pote tines © & maximilianogarcias 2 weeks, 6 ays 90 Did you tried the same query that is presented here? with “mytesta.clbo.myParquetTse"?? puted 3 mas © & Billybobo604 [Terie days, 21 nous a90 ‘there's another consideration, as to know the way the record is inserted. when this is dane specifying the columns it will succeed but witha select * itwon' hence the € would be the answer, But let's assume the racer i inserted correct. puted T nes © gabriethuka 1 weak, 1 day ag0 | vie this in Azure Synapse an it returns an error Removing the "dbo" part works So answer i 8. pote Times 1 & abrar8H0 4 days, sous agoDid you mentioned table name in Lowercase inthe query? ‘ted T nes BA aad 1 week 4 days 299 dash is just a typo, simply ignore it puted Times © & withrocky 2 weeks, 2 days ago “abo in the select statement wil sultan error. If this SQL statement i given to check presence of mind, then answer is B orf the dbo it Statement i 9 typo then answer is A ‘pted2trmes sal © A ashwin Joshias 4 wesks, 1 day ago ‘Answer is shown a8 8 here. 'ied to recreate the scenario and when we execute the select query t's throwing an errar because of hyphen symbol only but not because of upper & lowercase So lam not sure f hyphen is typo error. yes then answer should be A else Suppasein select query if dbo object mising then error will occur ‘pted 7 ines © & ted0809 1 mont ago We are not asking ower or upper case here Look atthe bigger picture, Returning 28 pote Times © igormmpinto 1 month, 2 weeks ago Answer is But think thats because in CREATE TABLE statement we have “myjtestdlsmyParquetTablo" and in SELECT statement we have mmytestdb doo myParquetTable puted? mes © Winwan33 2 montis apo Answer is 8 it will return an errr. table name wil be converted to lowercase Iitpsd earn microsoft comfen-us/szure/synapse-anaytis/metadata/able pve tnes © Jawidkaderi 2 mons 990 ‘The query is correc, The spelling is correct. | am not sure why the upper an ower casei of any concern her; it has to do with the collation S, if ignore the dash aftr the end of SELECT line the answer would be 24 pte 2 tines © & Man01997 2 months ago itiethe answer puted T tines BM anks84 2 months, 3 weeks 290 Once you create has been created, the table names willbe converted to lower case and need to be queried using the lowercase name, Hence, the answer is B (Fron, puted inas 2M anks84 2 months, 3 weeks 290 ‘Once the table has been created pote Ties Ignore the Type above © & debarun 2 months, 2 weeks 290 Error. Table names wll be converted to lower case and need to be queried using the lower case name ‘pte T nas © & debarun 2 months, 2 weeks 290 Eror. Table names willbe converted to lower case and need to be queried using the lower case name pte Times © A Remedios79 3 mortis 290 since you wrote the table name in mixed awer and uppercase it should give you error because it expects lowercase table name FROM mytestdi.dbo myParquetTable pte tines© & rafeek00007 2 months ago asters B.A eor will occur pte TimesRAG DROP. You have a table named SalesFact in an enterprise data warehouse in Azure Synapse Analyt. SalesFact contains sales data fromthe past 36 ‘months and has the following characteristics: = Is partitioned by month “» Contains one bilion rows “> Has clustered columnstore index [tthe beginning of each month, you need to remove data ftom SalesFact that Is older than 36 months as quickly as possible, Which three actions should you perform in sequence ina stored procedure? To answer, move the appropriate actions from the Ist of actions to the answer tea and arrange them inthe correct order. Select and Pace: Actions Switch the partition containing the stale data from SalesFact to SalesFact_ Work. Truncate the partition containing the stale data Drop the SalesFact_ Work table. Create an empty table named SalesFact Work that has the same schema as SalesFact. Execute a DELETE statement where the value in the Date column is more than 36 months ago. Copy the data to a new table by using CREATE TABLE AS SELECT (CTAS), Correct Answer: Actions Answer Area Answer Area ‘Switch the partition containing the stale data from SalesFact to SalesFact_ Work. Create an empty table named SalesFact Work that has the same schema as SalesFact. Truncate the partition containing the stale data. ‘Switch the partition containing the stale data from SalesFact to SalesFact Work. Drop the Salestact Work table. Drop the SalesFact Work table. Create an empty table named SalesFact Work that has the same schema as SalesFact. Execute a DELETE statement where the value in the Date column is more than 36 months ago. Copy the data to a new table by using CREATE TABLE AS SELECT (CTAS). ‘Step 1: Create an empty table named SalesFact. ork that has the same schema as SalesFac. Step 2: Switch the partition containing the stale data from SalesFact to SalesFact Mork. SQL. Data Warehouse supports partition spliting, merging, and switching. To switch partitions between two tables, you must ensure that the patitions algn on thir spective Boundaries and that the tale defitions match Loading data into partitions wih patton switching isa convenient way stage new data ina table that snot visible to users the switch nthe new data Step 3: Drop the SalesFact_ Work table Reference: hitps:ifdoes microsoft com/enus/azure/sal-data.warehouse/sqldats-warehousetabes-patition © & seta (RTOS) year, 2th 99Given answer D A Cis coreect pte 33 tines BB sik year 2monits ago Yes. Once the partion poted2times 1 & Jawidkaderi ox)? months ap0 very interesting questions Every partion has a name, which indicated by the mmY¥YY perhaps. So, if we know the name ofthe partion, we can drop that parction dirty: DROP PARTITION SCHEME parton scheme name [> However, if there i an index onthe table DOPR Patton will not work So, the iis correct. Dac. ‘puted T ines 1 with an empty partition itis equivalent to truncating the partition from the original table © & pmeos 3 months, 1 weck ago Answer is F-A- https//docs miroso posted times comes-es/achive/blogs/apsblag/azuresel-dw-performance-ctasparttion-switching-vs-updatedelete © & supriyako 1 month, 1 week 290 F seems wrong ast says CTAS to copy the data ted Tee © & pme08 3 moni, 1 week go Dis incorrect because we also need to copy the data onto the new table voted tines © & Deekshat234 3 months, 2 weeks ago correct ans ‘puted T nes 2A mithoma3 6 months ago DAC ‘Azure Synapse does not support truncating partitions. Currently, that pote Times feature is only tied to MS SOL Server. © & Dathy 6 months, 2 weeks 290 Stop 1: Create an empty table named SalesFact work that has the same schema as SalesFact. Step 2: Switch the partion containing the stale data from SalsFact to SalesFact Work Step 3: Drop the SalesFact Work able puted Times 2B dudewit7 months, 3 weeks 250 DAC isthe right option For more information this doc discusses exactly this example: https/does microsoft com/en-us/azure/synapse-analytics/sql dat data-warehouse-tablesparttion pte Times © & theezins months ago Why net included deb puted Times 9 sales data older than 36 months whichis mentioned in question? © & RamGhase 9 months, 3 weeks 290 i could not understand how answer handled to remave data before 36 month puted T nas rard 9 months, 3 weeks 290 you have to move the partitions that contains the date before 36 months voted 3 nas | 0 onthe, 7 week 290 DACiscom pote tines © indomanish 11 mons ago Patton switching help us in loading large dataset quickly. Nat sure iit will help in purging data ae well puted 2imes © & Sabadamal2010atGmall 11 montis, 1 week 99 Given answers correct ‘puted ines© & covllmalls yea, 1 month ag0 DAC is ort pte nee © A uit 1 yea: s months ag0 truncate partitions even quicker, why isnt that the answer, ifthe data is dropped anyway? ‘puted 3 mes © & BlackMal year 3 months ago This think it should be the answer ted nse © & yolapa1172 1 year, 1 month ago There i no way to truncate partitions in Synapse. Partitions don’ even have names and you can't reference them by valve, ‘pote tines © & poomipy 1 yea 4 months ape what is ‘poted times 1 correct answer for this? © & AnonzuredataEngineer + yea, 4 months 290 Seems like it should be: pte T nes 1 & dragos.dragos62000 1 year Smontis 290 Coneet ted | neeYou have files and folders in Azure Data Lake Storage Gen? for an Azure Synapse workspace as shawn inthe following exhibit ‘Mtoptolder/ | Filer.csv Htoldert/ older2/_| Filed csv I | File2.csv Fileacsv | You create an exteral table named ExtTable that has LOGATION= /topflder When you query ExtTableby using an Azure Synapse Analytics serverless SQL pool, which ile are returned? ‘A. Filed and File8.esv only 8. Fle.csv and Flees only Fle. cs File? Fle, and Fled.esv Filet .esv only Correct Answer: C Toruna TSQL query over set of les within a folder or st of folders while treating them as a single entity or rows, provide a path oa folder orapattem (using wildcards) over @ set of files or folders. Reference: hntpsffdacs microsoft com/en-us/azute/synapseanalytes/sql/query-datestorage¥query-multiple-les-orfolders Community vote dstrbution © & Chilem1900 ama) 1 yea, 7 months ago believe the answer should be 8, In case ofa serveress pool a wildcard should be added to the location. https://decs microsoft com/en-us/azure/synapse-analytics/sq/davelop-tables-exteral-ables?tabs=hadoop targuments-ceate-external-table puted 91 times © & captainpike 1 yas, 1 month 90 | tested and proove you right, the answer is 8. Remind the question is refering to serverless SOL and not dedicated SQL pool. “Unlike Hadoop fevteral tables, native external tables don't return subfolders unless you specify atthe end of path In this example, if LOCATION="/websdata/, a servriess SQL pool query, ill retun cows from mydata at. It won't return mydata? it and mydata3ut because theyre located in a subfolder Hadoop tables wil return ll files within any subfolder” voted 20 tines © & alana GTS 1 year, months ago “Servrless SQL pool can recursively traverse folders only if you specty/* atthe end ofp hitps//decs microsoft com/en-us/azure/synapse-analyis/sql/query-folders-multiple-sefles upeted 21 times 2 & Preben year Smonths ago |when you are quoting fom Microsoft documentation, do not ADD in words tothe sentence Only"is not used ted 12 times © & captaipike 1 year, 1 month go ‘The answer is B however. Icould not make pote tines © & brohanyu (mea) 1 day 1Shous 399 Bis correct ‘pte T nas ta work somebody? © rohitbinman 1 woe, 4 days 290 “native external tables don’t return subfolders unless you specify /* atthe end of path In this example, f LOCATION=""webdata/’, a serverless SOL pool query, wil return routs fram mya xt. It ont return mycata2. and mycsta8 tt because they're located in a subfolder, Hadoop tables will Fetuanal files within any sub-folder https://learn microsof.com/en-us/saure/synapse-analtics/sql/develop-tables-exernal-tablesabs=hadooparguments-create-exteral-tablepte T ines © gerret979 1 monn, 1 week 290 tested the solution out in Azure Synapse with ll the necessary steps connected tothe Data Lake Gen2 storage account and answer B is correct the subdirectories are not taker into account pote Times DB dawidkaderi 2 months 290 kind of agree with captanpike, However, | have my reason ase: lam reading the questions as such the table created inside te /topfolder/ hich means the new table isin the same linea: Flees, folderl/ flder2, Filedesv So, think A] isthe correct answer. FleZesv and File3.csv ‘puted 7 nes © & smsme323 2 months, 1 week ago ned wildcard to acces al fie, ‘puted T ines 2 gerret979 monn ago correct, only with location ‘puted T ves ‘opfolder/™ it ill hve access to all the subfolders 2M anks84 2 months, 3 wecks 290 Given answer C is Correct ‘puted Times © & yyphinh 2 months, 1 week ago Tagree with @temace, C ‘pted 2 ines © & examtopieseap 3 months, 3 weks ago correct 8 ‘puted? ines 2 & Razat2 «months ago Unlike Hadoop externa tables, native externa tables don’ return subfolders unless you specify /* at the end of path. I this example, if LOCATION="/webdata, a serverless SQL pool query ill return rows from mydata txt. I won etum mydata2 tx and mydata8 txt because they're located in subfolder. Hadoop tables wll return al les within any sub-older, upvted 3 tines DB danieys «months, 1 week ago Bis the correct Same example in: ttps:/locs microsoft camyen-us/sqlt-sqU/statements/create-estemaltabl Intestlipreservesview=truesitabe-serverest pote Times sact-svlew=szure-sald BA Razat2 4 months, 2 weeks 290 (Seems from tis document that Answer is ’C"jln SQL Server, the CREATE EXTERNAL TABLE statement creates the path and folderft doesn't Slready exist. You can then use INSERT INTO ta export data from local SQL Server table o the extemal datasource. For more information, £66 PolyBase Queries you specty LOCATION to be 8 folder,» PolyBase query that selects from the exteral table wil retrieve files from the folder and lofts subfolders. Just lke Hadoop, PolBase doesnt return hen folders. also doesn return files for which the fie name begins wth an undetline C) ora period () In this example if LOCATION="/webdata, a PolyBase query wil return rows from mydata kt and mydat2.xt.K wont return mydatad it because its fein iden folder. And it won return hidden tat because #3 hidden fle Recursive data for extemal tables htosflocs mirosofe com/en-us/sqlt-sql/statements/creste-extern-tabls ‘puted Times © Nishikag 5 months, 1 week 200 ‘Answer Bn serveress SQL pools must be specified /* atthe end of the location path, In Dedicated pool the folders are alwasy scanned recursively, https//decs microsoft com/en-us/azure/synapse-analytis/sq/dvelop-tables-exteral-tables7abs=hadoop pted times ransact-sol'view=sal-server-verlS&tabs=dedicated BA temace 5 months, 1 week 3g0hutps//docs microsoft com/en-us/sqlt-sqistatements/create-extema-table-transac-sqlview=sql-server-verISB&tabs=dedicatedtlocation-- Folder-or flepath pote 5 ines BB amira404 sons, 1 neck 90 Cis comect as mentionned in the official documentation which showcase a similar example :tps//does mierosoftcony/en-us/sqVt= sal statements/erete-external-table-transact-sqlview=sql-serverover Sétabs-decietedvlocation--flder_orflepath puted tines B & Backy 6 months, 3 weeks e90 ‘The question does not show the actual query so this sa problem pte TimesHoTsPor You ate planning the deployment of Azure Data Lake Storage Gen2. You have the following two reports that will access the data lake: ce» Report: Reads thre columns from ale that contains $0 column, “+ Report: Queries a single record based on a timestamp. You need to recommend in which format to store the data inthe data lake to support the reports, The solution must minimize red times, \What should you recommend fot exch report? To answer, select the appropriate options inthe answer ata, NOTE: Each correct selections worth one point Hot Aes: Answer Area Report: v | Avro csv Parquet TV v Avro csv Parquet TSV Answer Area Report: v Avro csv Parquet Correct Answer: TSV Report2: v Avto csv Parquet TSV Report: o8V- 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: hitps/streamsets.com/dacumentaton/datacollector/latesthelp/datacollector/UserGulde/Destinations/ADLS-G2-D.html © & alana GTI yea, 6 months ago 1: Parquet -colummorinted binary fie format 2: AVRO - Row based format, and has logical type timestamp hitpsyfveutu be/trWthxeT3UY pote 137 times 2 A nteox 1 week. 9 doys go‘Thanks forthe video share, this relly helps, Cheers, ‘voted T nes © @ terajuana* yew: 5 months ag0 the web is fl of eld information. timestamp support has been added to parquet ‘voted 5 nas 1D & vadege 1 yea: 5 months ago ‘Ok butin 1st case we need only 3 of $0 columns. Parquet i columnar format In 2nd Avro because ideal for red fll ow ited IStines © anurestudent1498 7 months, 2 weeks ago thisis caret. ited nes © & infor (HEaTIaEES) 1 yer, months 90 Shouldn't the answer fr Report 1 be Parquet? Because Parquet format is Columnar and should be best for reading a few columns only, puted 71 tines © & Deekahat 234 [eai==515) 2 months, 2 weeks ago 1. Parquet 2. AVRO pted tines © & ROLLINGROCKS 5 montis ego Parquet - Avro makes more sense given the defintios given by Microsoft pted2simes © & temace Smosh, 7 week ape 1. Parquet 3 of 50 columns 2. Parquet. In case with single row work faster, than AVRO, ‘pote Times BM main616 6 months, 3 weaks ago 1. sv (arson swjson support query accelerate by select specified rows hitps//docs microsoft com/en-us/azure/storage/blobs/data-lake storage-query-accelerationtoverview 2awo ‘pte nee © & Doth 6 months, 2 weeks ag0 ‘Parquet 2: AVRO ‘puted 3 mes © & Ralphtiang 8 months ago Consider Parquet and ORC fle formats when the /O pattems are more read heawy or when the query patterns are focused on a subset of columns inthe records the Avro format works well with 9 message bus sich as Event Hub or Kafka that write multiple events/messages in succession poted 3 times © A ragp.87 10 months ago 1. Parquet 2.Awro hitps//docs microsoft com/en-us/azure/storage/blobs/datalake-storage-best-pratices "Consider using the Avo file format in eases where your VO patterns are mare write heavy, or the query pattems favor retrieving multiple rows of recordsin teir entirety. Consider Parquet and ORC file formats wien the V/O pattems afe more read heavy or when the query patterns are focused on a subset of columns in the records pte 8 tines BM SebK 8 months, 2 weeks apo ‘Thank you ated Ines © & Mohammadkhubeb 10 months, 1 week 90 Why NOT csv in report ? puted 2simes 2 & dauqueto 3 days, 18 hous ago (CSVis not optimized for column reads ‘ote T nas © & Sandipau 10 months, 3 weeks 290‘This has to be parquet and AVRO, got the answer from Udemy ‘pte 5 nas © Mahesh.mm 11 months, week 290 1. Parquet 2.AVRO ‘poted 3 times © A marcint212 11 months, 3 weeks ag0 ‘The goals: The sol tion must minimize read times. | made small test on Databrck pls Datalake, ‘The same file saved as Parquet and Avro 9 min of records Parquet -150 MB ‘Avro ~TOOMB. Reading Parquet always 10 times faster that Avro, I checked = forall data or small range of data with condition “aller enly one columa So wil select option Parquet parquet pte 2 ines 2 & devaer 11 sorts ag0 how can be foster ead is same as number of ends? voted | nes © & Oazypoppe 1 year 200 Solution says parquet isnot supported for als gen 2 but it actually is: httos//docs microsoft. comven-us/azure/data-factory/fermat-parquet puted 3 times © & noranathae yea, * month 390 An interesting ane complete sricle that explain the dfferent uses between parquet/avra/esv and gives answers ta the question https://mecium coryssense-tech/esv-vs-parquet-vs-avto-cnoosing-the-ight-tal-fr-the-ightjob-79c3/5691 a8 pve ines BB elimey + yeas, 4 months ago hitps/luminousmen com/postbig-data-fle-formats ‘pte T nas BB etimey 1 yeas, 4 months ago Report 1 definitely Parquet pote TimesYou ate designing the folder structure for an Azure Data Lake Storage Gen? contane. Users wll query data by using a variety of services including Azure Databrcks and Azute Synapse Analytics serverless SQL pools. The data will be secured by subject area, Most queries wll include data from the current year or current month, Which folder structure should you recommend to support fast queties and simplified folder security? ‘A HSubjectAvea) DataSource) (OD) (MM)/(YYYY)/(FleData)VYYY) {MM} {0D}. csv 8, (ODY{MM)AYYYY) (SubjectArea} (DataSource) (FleData) VY) (MM) {00}.c8¥ AYYYYN(MM)/{OD)/{SubjectArea) DataSource) (FleData)VYYY) {MM} {00}. csv 1. SubjectArea) (DataSource) (¥Y¥Y}/(MM)/{00)/ (leat) {YYVY) {4M} (00}.cs¥ Correct Answer: D ‘There's an important reason to put the date atthe end of the directory structure, I you want o lock down certain regions or subject matters to users/groups then you can easily da so wih the POSIX permissions. Otherwise, if there was a need to restrict a certain security group to viewing jst the UK data or certain planes, with the date structure in font a separate permission would be required for numerous directories under every hour directory, Additionally having the date structure infront would exponentially increase the numberof directories as time went Note: In oT workloads, there can be a great deal of data being landed inthe data stove that spans across numerous products, devices, organizations, and customer. I's important to pe-plan the diectory layout for organization, secu, and efficient processing ofthe data for doworstream consumers. A general template to consider migit be the following layout Region) (SubjectMatters)\py mm) edith Community vote dstrbuton © & sagga TERTIETS) ye, months ago tp docs microsoft conen-u/szure/storage/blob/ dat ake-strage-best-practicestbatchobs-structure pte 51 tines © & Deekshat234(Toxtoani5) 3 months, 2 weeks ego correct, pte Times 1B & examtopicseap 3 months, 3 weeks ago Discorrect ‘pte T nas 2 & StudentFromAus § months 250 Comect ‘puted T nes BM Doth 6 months, 3 weeks ago Dis correct pte Times © Olukunmi 7 months, 1 week ag Dis correct ‘pte Times © & Egocentie 7 months, 2 weeks ago Dis correc pte T ines © & SebK 8 months, 2 weeks 260 Dis cone pote tines© & Ralphtiang 9 months ago Discorrect ‘pte T nes © NeeralKumar 9 months 290 Comect ‘pote T nes © & PallaiPatel 10 montis, 1 week 290 Comect ‘puted Times 2 & Skyrocket 10 months, 2 weeks 290 ‘pte T tines 2B Verodon 11 months, 1 week ago Thats comet ‘puted 7 tnes ©. Mahesh.mm 1 months," week age pte T nee © & slexleonvaleneia 1 Respuesta correcta D. pte Times 2M rashjan 11 months, 4 weoks 299 Dis corre pte Times © & ohana yer, 1 month sg0 Took the exam today, this question came out Ans: D ‘pve neeHoTsPor You need to output fles from Azure Data Factory. Which file format should you use fo each type of output? To answer, select the appropiate options inthe answer area, NOTE: Each correct selection I worth one pont Hot ea: Answer Area Columnar format: v Avro Gzip Parquet TT JSON with a timestamp: v Avro Gzip Parquet TXxT Answer Area Columnar format: Avro GZip Parquet Correct Answer: TT JSON with a timestamp: Avro Gzip Parquet TxT Box: Parquet Parquet stores datain columns, while Avro stores data in a row-based format. By their very nature, colummorented date stores are optimized for read-heavy analytical workloads, while row-based databases are best for write heavy transactional workloads Box2: Avro ‘an Avo schema is crested using JSON format. AVRO supports timestamps. Note: Azure Data Factory supports te following fle formats (not GZip or TXT). Avro format ~ Binary format = Delimited text format > Excel format JSON format => ORC format “> Parque format> XML format Reference: btps:rwww.éatanami.com/2018/05/6/bigdatefile-formats-demystiied © & Matesh, mm ([GETEEE) 1 mons, 1 week a90 Parquet and AVRO i corect option peed 26 tines © & sppd 2 months, 3 weeks 290 Parquet and AVRO is correct pte T ives © A Deckshat234 3 months, weeks 259 ‘pte Times 2M Razat2 4 months, 2 weeks 290 Parquet and AVRO is correct option upvted ines BA Doth 6 months, 3 weeks 90 agree withthe answer ‘puted 3 mas © & Ratphtiang 8 months ago Parquet and AVRO is correct option pvted Zines © a Pala conrect, pte Times tel 10 months, 1 eek 290 1B & Shyrocket 10 months, 2 weeks ago Parquet and AVRO is right. puted? ines 2 & dba 11 months, 1 week ago CGP fle format is one of supported Binary format by ADF https/docs microsoft com/en-us/azure/data-factory/cannector-fle-systemttabs = data-factorytfle-system-35-sink puted Tinas 2d bad atitude 21 months, 3 weeks ago agree withthe answer upted tines © & alextoonvalencia 11 manhs, 3 weoks 290 Respuesta correcta PARQUET & AVRO. pte TimesHoTsPor You use Azure Data Factory to prepare data to be queried by Azure Synapse Analytics serverless SQL pools. Files are iniallyngeste into an Azure Data Lake Storage Gen? account as 10 small SON files. Each fle contains the same data attributes and data from a subsidiary of your company. You need to move the files toa diferent folder and transform the data to meet the following requirements * Provide te fastest possible query times. "Automatically infer the schema from the undetying les. How should you configure the Data Factory copy activity? To answer, select the apotootate options inthe answer area NOTE: Each correct selections worth one pont, Hot Aven Answer Area Copy behavior: wi Flatten hierarchy Merge files Preserve hierarchy Sinkfiletype: | SSsi csv JSON Parquet TXT. Answer Area Copy behavior: wi Flatten hierarchy Merge files Sink file type S csv JSON Parquet TXT. Box 1: Preserver hierarchy ‘Compared to the at namespace on Blab storage, the hierarchical namespace greatly improves the performance af directory management operations, which improves overall job performance Box2: Parquet ‘Azute Data Factory parquet format is supported for Azure Data Lake Storage Ger2 Parquet supports the schema propery Reference: hitpsffdocs microsoft. com/en-us/azure/storage/blobs/ data lake storage introduction htps://docs micrasoft.com/en-us/szure/data factory/format parquet © & alana GTTTTS1 yeay, 6 months ago {Merge Files 2. Parquet Iitps//docs microsoft con/en-us/azure/storage/blobs/data-ake-storage-performance-tuning-guidance ‘poted 17 tes© & Ameenymous 1 yes: 6 months age ‘The smaller the files, the negative the performance so Merge and Parquet seems tobe the right answer. upoted 16 times 2 A edba 11 months, 1 wesk ago just want to add a bit more reference regarding copyBehavior in ADF pls info mentioned in Best Practice doc. soit shall be Mergfil first Ittps/docs mieosot com/en-us/azure/data-factory/cannector-fle-systemttabs= data-factoryefle-system-35-sink ted mee 1B & lowd 1 year, 5 month ago Larger fies lead to better performance and recuced costs Typically, analytics engines such as HDInsight have a per-file overhead tht involves tasks such 3 litng, checking access, and performing various metadata operations. you store your data 3s many small les, this can negatively affect performance. In general, organize your data into larger sized files for beter performance (256 MB to 100 GB in size). S poted times © & captanbee (GETS)! yew, Smorts ago Its frustrating just how many questions Examples get wrong Cant be helpful pveed tines © & gssdascoder* year, 1 month ago Trying ta understand ifn answer iscorect will hep learn more puted 7 ines © & RywHayabusa 1 yea 4 mors ago ‘At east helps in learning, as you have te research and think for yourself Another big topics having tis questions inthe fist place is immensely helpful voted «2ines © & Maviodiagps S months, 3 weeks 200 itis missleading, they should not give any answer at al \pvted2imet © & Sebk 8 months, 1 week ago Agree, ‘ted 2 ines © & Selmas7 TE) weeks, 1 ay ogo | think "Atomatcallynfrthe schema from the underlying fle” means we should keep the same hierarchy and not merge all the data into 2 single fe. So I would say thatthe frst one is Preserve Hearchy. ‘pte T nes PA Oldschool 2 weeks eso ‘Ql are intially ingested into an Azure Data Lake Storage Genz account as 10 small JSON files. Each file contains the same data attributes and data from a subsidiary of your compary. Since all have same data and attnbutes we ean Merge them in one file and automaticaly infer the schema from the underbing 10 smal fies. Merge Parquet ited nee © & Azure Kraken 3 months, 1 wee 390 ‘So many answer are misleading, totally fed-up by checking the comment section far each and every question ‘puted 7 tines 2 W tomas 1 month, 2 weeks ago This sit isn't a brain dump, is 9 study tool and it very often has some insights that are actually useful in my job beyond the tests I all you want is answers im sure that you can find something else tat fits the bil ‘voted T nes © A tomras 1 month, 2 weeks ago It's more along the lines of being 2 Stackoverlow for the Microsoft exams. ‘ted net © & Deckshat234 3 months, 2 weeks ago 1. Merge Files 2 Parquet ‘puted T nas © id months, 1 wack ago | would say Merge might not be possible, ecause we do nat know i the Json contains enough information about the subsidiary, which we would need ifwe merge pte Times BB Atha months 992 The frst answer should be Merge Files‘pte inas 2 & azures0otest 5 mons 999 1. Merge Files 2. JSON Decompression requires compute and thus costs time, Since the ls are small eavng them uncompressed is ok since it will ll faster query. ‘Also they are JSON ies so compressing them in colurmar format does not make much sens, puted ines 2 & Dathy 6 months, 3 weeks 290 1. Merge Files 2 Parquet ‘puted 2 mes © & KashRaynardMorse ? mots, 3 weeks 990 ‘Atequirement was "Automatically infer the schema from the underlying fles", meaning Preserve hiratchy is needed ‘pvted 3 nas oa Tonite 290 is possible that all or some schemas may be ifferent in that case we cannot merge voted I nee 1B & Aditya049 5 mans, 2 weeks 290 gabdu why are you tying to create a diferent use cas? it's clearly mentioned thatthe data attributes are same in all the files. Answers merge, don't canfuse others looking for answers nere ‘pote tines © & imomins& months ago another hat key is: You need to move the files toa diferent folder 0 answer should be preserve hierarchy. ‘puted? tnes BM adizra54 2 mons, 1 week 250 But the data attributes ave same inal the ies pote Tes 2 & Eyepateh99a e mont, 1 week ago 1. Preserve elrarchy - ADF is used only for processing and Synapse isthe sink. Since synapse has parallel processing power, it can process thefls in diferent flder and thus improve performance. 2 Parquet ‘puted Times © & uzairah 5 montis, week ago ‘The question says "Provide the fastest posible query times." not the wite times an there is na in rectory there are just 10 json files so combining them into one file would be most approprite a large fl size increase performance rea iy link https//docs microsoft com/en-us/azure/storage/olobs/data lae-storage-best-praclcestfile-size voted T nes 2 & kami 2 months, 2 weeks 290 ‘Ae these answers the actual correct answers or guesses? Who highlights the correct answers? pte nes BM sraken 10 months, 1 week go “In general, we recommend that your system have some sort of process to aggregate small les into larger ones for use by downstream applications. https//docs microsoft com/en-us/azurefstorage/olabs/datalake-storage-best-practices ‘Thetefore I believe the answer should be "Merge fles'and Parquet’ ‘pte tines 1B & Sandipau 10 months, 3 weeks 290 Merge ané parquet willbe the right option, also taken reference from Udemy pted2simes © Mahesh sm 11 months,“ week 390 “LAs hierarchical namespace greatly improves the performance of directory management operations, which improves overall job performance, Preserverherarchy looks correc. Also there is overhead for merging les, 2 Parquet ‘puted 3imas © & Boompiee s months, 3 weeks apo ‘The overhead for merging happens once, after that it’ faster every time to query the files if they are merges. pote times© & urairahm S montis, week 250 ‘The question says "Prove the fastest posible query times." not the ite times and there is no in dlvectory there are just 10js0n files so combining them int one file would be most appropriate as large fl size increase performance read in link https//decs microsoft com/en-us/azure/storage/olobs/data lae-storage-best-practcestfle-size ted nee © & mashines 11 months, 3 weeks ago Merge Files and Parquet ‘puted tinesHoTsPor You have a data model that you plan to implement in data warehouse in Azure Synapse Analytics as shown inthe following exhibit. Fact_DailyBookings Dim Customer PERE iDailyBookingsID iCustomerlD iEmployeelD. iCustomerlD >———#_ vcCustomerName vcEmployeeLastName iTimelD vcCustomerAddress1 vcEmployeeMName iEmployeetD veCustomerCity veEmployeeFirstName fp ——oF ttemID atEmployeetireDate iQuantityOrdered = dtémployeetevel dxchangeRate Dim_Time dtEmployeeLastPromotion iCountryofOrigin iTimelD - mUnitPrice + icalendarday iCalendarWeek iCalendarMonth veDayofWeek veDayofMonth veDayofYear Azure Synapse Analytics iHolidayindicator ‘Allthe dimension tales willbe less than 2 GB after compression and the fact table willbe approximately 6 TB. The dimension tables willbe relatively static with very few data inserts and updates. Which typeof table should you use fr each table? To answer, select the appropriate options inthe answer area NOTE: Each correct selection I worth one pont. Hot Area: Answer Area Dim_Customer: v. Hash distributed Round-robin Replicated Dim_Employee: Vv Hash distributed Round-robin Repiicated Dim_Time: v Hash distributed Round-robin Replicated Fact_DailyBookings v Hash distributed Round-robin ReplicatedAnswer Area Dim_Customer: v Hash distributed Round-robin Replicated Dim_Employee: v Hash distributed Round-robin: Correct Aner: Epica! Dim_Time vy Hash distributed Round-robin Replicated Fact_DailyBookings: v Hash distributed Round-robin Replicated Box 1: Replicated Replicated tables are ideal for small starschema dimension tables, because the fact table is often distributed ona column that's not compatible with the connected dimension tables. this case applies to your schema, consider changing small dimension tables currently Implemented as round:obin to replicated Box 2: Replicated Box 3: Replicated Box 4: Hash-distbuted For Fact tables use hash-distribution wit clustered columnstoe index. Performance improves when two hash tables are joined onthe same Aistibution column Reference: hitpsfazure microsoft. conv/en-us/updates/reduce datz movement-and-make your queries more-fclent withthe general-avallablty of replicated tabes/htips:azure microsoft com/er-us/blogireplicatedtables-now-generall-avalablein-azure-sqkdta.warehouse/ © Bin viana TET) yea, 2 monte ogo ‘The answer's correc. ‘The Dime are under 2ob so n0 point in use hah, ‘Common distribution methods for tables: ‘The table category often determines which option to choose for distributing the table Table category Recommended distribution option Fact -Use hash-distibution with clustered columnstore index, Performance improves when two hash tables are joined on the same distribution colar Dimension - Use replicate for smaller tables. fables are too large to store on each Compute node, use hash-dstrbuted Staging - Use round-robin forthe staging table. The load with CIAS i fast. Once the data i inthe staging table, use INSERT. SELECT to move the data to production tables. htps//docs microsoft com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overviewtcommon-distribution- methods-for-ables puted 11 tines © virendrapsingh § montis, 3 weeks 290 This is a wonderful explanation, Worth giving ike ‘voted 3 ines © Gametie yea, 1 month ago Thanks, but where in the question does it indicate about Fact table has chisteredcolurmnstore index?‘puted 3 ines 8 rserksap 1 yea, month 299 Normally for big tables we use casted colurmnstore index for optimal performance and compression, Since the table mentioned heres in TBs we can safely assume using this index is the best choice https//docs microsoft comy/en-us/azure/synapse-analytics/sol-dat-warehouse/sel-data-warehouse-tables-index pated 2imet © & berserksap year, 1 month ago https//docs microsoft com/en-us/azure/synapse-analytics/sq/develop-tables-overview ‘ted nee © & ohana (GETS) 1 ye, 1 mam 290 Took the exam today, this question came out ‘Ans: All the Dim tables > Replicated Fact Tables -> Hash Distributed pred 3 tines © & Deekshat234 (Terme) 3 moths, 2 weeks 290 contect poted 1 tines B & bjecto 5 months ago Just a better link that explains the decisions. Also watch te video, its cool https/docs mirosote com/en-us/azure/synapse-analytics/sql-data-warehouse/massivly-parallel-processing-mpp-architecure puted 2times © & Dothy 6 months, 3 wesks 290 ‘The answers correct, puted Times © & Pallavipatl 10 montis, 1 week 290 upted times © & Pritamts 10 months, 1 week aso Got this question on 2312/2021. answer is core pte ines © A Mahesh sm 11 months," week 390 Ansis correct upted ines © alfonsedisatve 1 yea, 1 month apo Dimension are Replicated “Since the table nas multiple copies, replicated tables work best when the table sze sles than 2 G8 compressed, “Replicate tables may not yiela the best query performance when ‘The table has frequent insert update, and delete operations" = We recommend using replicated tables instead of round-robin tables in most cases" https//docs microsoft com/en-us/azure/symapse-analyis/sql-data-warehouse/design-guidance-for-replcated-tables posted Times BA gssdéscoder * year 1 morth ego Comect:httos//decs microsoft com/en-us/azure/synapse-analytis/sq/develop-tables-overview#common-distrbution-methodsfor-tables pute timesHoTsPor You have an Azure Data Lake Storage Gen? container. Data ingested into the container, and then transformed by a data integration aplication. The data is NOT modified after that. Users can read Sle nthe container bu cannot modify the files, You need to design a data archiving solution that meets the following requirements: = New datas accessed frequently and must be available as quickly as possible, ~» Data that solder than ve years Is accessed infrequently but must be avallable within one second when requested. ® Data tats older than seven yeas is NOT accessed. After seven years, the data mustbe persisted at the lowest cost possible. "> Costs must be minimized while maintalning the required availabilty How should you manage the data? To answer, select the appropriate options inthe answer ara, NOTE: Each correct selections worth one point Hot Area Answer Area Five-year-old data: Delete the blob, Move to archive storage. Move to cool storage. Move to hot storage. Seven-year-old data: Delete the blob, Move to archive storage Move to coal storage. Move to hot storage, Answer Area Five-year-old data: Delete the blob, Move to archive storage. Move to cool storage. Correct Answer: | Move to hot storage. Seven-year-old data: Delete the blob, Move to archive storage. Move to coal storage. Move to hot storage, Box 1: Move to cool storage Box 2: Move to archive storage Archive - Optimized for storing data that i rarely accessed and stored for at least 180 days with Mlexible latency cequirements, onthe order of hours. Te following table shows a compatison of premium performance block blob storage, andthe hot, cool, and archive acess ters,Availability Availablity (RA-GRS reads) Usage charges Minimum storage duration Latency Time to first byte) Reference: hitpsifdocs microsoft. com/enus/azure/storage/blobsstorage-blobstoragetiers Premium performance oe9% NA Higher storage costs, lower access, and transaction cost NA Single-ciait riliseconds Hot ter pag 92998 Higher storage costs lower access, and transaction costs NA rmiliseconds © & yobhip Gaara) year, 6 months ago Answer should be 17 Coal 2 Archive oo! tier 208% 29.0% Tower storage costs, higher access, and transaction costs 30days? ‘Comparison table shown access time for coal tert is milseconds Archive tier oftine oftine Lowest storage costs highest access, and transaction costs 190 days hutpsi/docs mirosof com/en-us/azure/storage/blabs/storage-blob-storage-tersttcomparing-black-blob-storage-options puted 68 tines 2 & r00s 6 months, 1 week 290 Right #1 is Cool because its clearly mentioned in the dacumentation that “Older datasets th available for immediate access https/does mieroso% com/en-us/azure/storage/blobs/access-ters-overiew#comparing-blockcblob-storage-options ted nee © & sustbu (GETTEETS)1 yen, 2 months 290 “Tricky question, t says data that is OLDER THAN (> 5 years), must be available within one second wen requested not used frequently, but are expected to be But the frst question ass for Five-year-old data, which s=5, 50 It can also be hot storage Similarly forthe seven-year-old Not sure, please puted 1 times sonfiem? BB joshtey 4 months ago “Costs must be minimized while maintaining the required availabilty, pote times © & PugazhManohar[Sa=a==1) 3 months, 3 weeks ag0 cod, 2.Aschive puted Times © examtopicseap 3 months, 3 weeks ago ‘Theres @ question with ‘data over 5/7 yeas old so it's very tricky 1-Hot 2-cold pte times 2 & Dothy 6 months, 3 weeks 390 So cold and archive ate the correct answers,pte T ines 2 & Pallavipatel 10 montis, 1 week e90 pte Ties BB ANath 11 ments ago 1. Cool Storage 2. Archive Storage pte Times © & Mahesh.ymm 1 months“ week 290 Answer is corect puted Times 2M sith year montis ago [Answer should be thot 2srehive https//mmwbme com/blogs/cold-vschot-datastorage/ Cold storage data retrieval can take much longer than hot storage. can take minutes to hours to access cold storage dats puted times © & syamumar 1 year S months 350 [also doubt fits hot storage and archive. because its mentioned S-year-old has to be retieved within seconds which snot possible via cold storageli ited Tee © & savin yar $ months ago but the cost factar i also there. keeping the data in hotter for S years vs cod tie for 5 years would add signfiant amount ‘pote T nes © & captainbee + yea, § months ago old storage takes aillseconds to retrieve pte tines © & mareint212 1 monthe,2 weeks ag0 httpsi/wnwbmecom/blags/eold-vs-hot-data-storage/ Riso about Azure! ted 2 inet 2 & Dre 1 year 6 months 90 ‘Answer is corect pted inesQuestion #11 opie 1 RAG DROP. You need to create a partioned table in an Azure Synapse Analytics dedicated SQL pool How should you complete the TrensactSQL statement? To answer, crag the appropriate values tothe corect targets, Each value may be used ‘once, more than once, o nat at all You may need to drag the spit bar between panes ot sral to view content, NOTE: Each correct selections worth one pont Select and Place: Values ‘Answer Area CLUSTERED INDEX CREATE TABLE tablet = ID INTEGER, DISTRIBUTION colt VARCHAR (10), PARTITION col? VARCHAR (10) PARTITION FUNCTION ; ae Pesci ees =f ") = HASH(ID), (a sacs uber FoR VALNES (1, 1900000, 2000000) ¥ Corest Answer: Values ‘Answer Area CREATE TABLE tablet “Gg seca, [Dremerporron | col VARCHAR(20), [parrerron—] __coll2. varcxar(io) EARTITION FONCTION)» ®T7H DISTRIBUTION HASH (TD) (20 RANGE LEET FoR VALUES (2, 1000000, 2000000)) b Bo 1: osTaBUTION ‘Table distribution options include DISTRIBUTION = HASH { distribution column_name ), assigns each row to one distribution by hashing the vali stored in dition cou nae. Box 2; PARTITION Table partition options. Syntax: PARTITION (patton column same RANGE [LEFT | RIGHT] FOR VALUES (| boundary.value [1] }) Reference: htpsildocs.microsoftcom/enus/sqlt-sq/statements/createtable-azure-sqhdata-warehouse © & Sunny TE poise © & Sasha in San Francisco (STS) 1 yea ago Correct answer by haw to remember? Distribution option before the Partition option because.."D" comes before” or because the syste needs ‘to know the algorithm (hash, round-rbin, replicate) before it can stat to Partition o segment the data, (seem reasonable?) puted $0 times © & Deekshat 224 EATS) 3 months, 2 weeks ago correct preted nes x 6 months ago © & gursimran_s § months, 4 weeks ago 6o with a logical explanation guys. what i this D before P.if take i ike that then C comes before Das well. Try to grasp the logics. answers conrect. ‘pted times BA Dotty & months, 3 weeks ago Answer is corect puted times© A Egocentic 7 months, 2 weeks ago provided answer is corect ‘pte Times © A vineeti234 8 montns ago D comes before Pas in DP-203 ‘puted 7 mes © & PallaviPatl 10 montis, 1 week 290 puted times 2 & Jaws1990 11 montis 290 ‘Wouldt VALUES(, 1000000, 200000) create a partition for records with 1D pte Times BM ploer 9 months, 4 weeks ago Having three boundaries wil lead to four pattions 1. Parttion for values <1 2. Partition for values from 7 to 999999 3. Partition for values from 1000000 to 199999 4 Parton for values »= 2000000 puted 7 tines © nastyana 9 montis, 2 weeks 290 but only <= and >. tis range let for values right pote tines © & Mahesh.mm 11 months," week 290 Answer is corect pte T ines © & hugoborda 1 yer 2 months ag0 Answer is corect pte Times © & hsetn 1 yes, 3 months 90 Indeed! Answaris correct pte Times which would mean 1 row?You need to design an Azure Synapse Analytics dedicated SQL poo that meets the following requirements: ‘> Can return an employee record from a given point in time. -* Maintains the latest employee information, Minimizes query complexity. ow should you model the employee data? ‘Aas atemporal table B.as SQL graph table (as a degenerate dimension table 1. as a Type? slowly changing dimension (SCO) table Correct Answer: D ‘AType2 SCD supports versioning of dimension members. Often the source system doesnt store versions, so the data warehouse load process detects and manages changes in a dimension table. In this case, the dimension table must use surogate key to provide a unique reference to aversion ofthe dimension member, als includes columns that define the date range validity ofthe version (fr example, StartDate and EndDate) and possibly a fag column (for example, IsCutent) to easly filter by current dimension members. Reference: tpsifdocs. microsoft. com/en-usylearn/modules/populate-slowly-changingdimensions-azure-synapse-analytis pipelines/3-choose-between- dimension-ypes. Community vote dstrbuton © & pesaceses [Ham Ia=za) yeu, mons ago ‘Answer D: Temporal tbl is better than SCD2, but ts no supported in Sympase yet preted tines © & Preben + yess, Smonths ago Hore’s the documentation for how to implement temporal tables in Synapse from 2018, https//docs microsoft com/en-us/azure/synapse-analytis/sq-data-warehouse/sql-data-warehouse-tables-temporary ted nee © & mbravo 1 year,S montis 290 ‘Temporal tables and Temporary tables are two very distinct concepts, Your link has absolutely nothing to co with thi question, voted Tatines © Vaishnav 1 year 4 montns ago \psdocsmicrosof.comven-us/azure/azure-sq/temporal-tables Answer :A Temporal Tables ‘voted ines © berserksap 1 year, 1 month ago | think synapse doesnt sup ‘voted nee temporal tables, Pease check the below comment by hsetin © & sparkohus mons, 3 week ago though this not something relative to this question. temproal tables lock ale to deta table coveted tines © & rashan(HERTTERTS) 1 months, 4 weks ago Dis correct (voting comment that people dont have to open discussion always, please upvote to help others) puted 67 times © & oldschool (ATED)? woos, 5 days 99 scb2-D pote Times © A Deekshat234 3 months, 2 weeks apoconreet ‘pted 2 nas © & PugazhManohar 3 months, 3 weeks 200, Maintains the latest employee informa pte Times = SCO-Type (Ans-D) 1B & Dathy 6 months, 3 weeks 290 Answer is corect posted 2simes © & Martn_Nbg 7 mons, 7 week 290 “Temporal tables ae not supported in Synapse so is correct puted 7 ines 2 & sparkchu 8 months, 1 week ago overall you should use delta table ‘ted T nes ‘puted T ines © & Adelina 10 months, 3 weeks ago Discarract ‘puted 2 nes © & devadey 10 mors, 4 weeks 290 Confusing high voted comment. Ds SCD? but comments talking about temporal table Fier way SCD? right answer which is Choice D ‘puted Times 2B Veropon 11 months, 1 week ago Dedicated SOL Pools is the key ‘puted 3 ines © Maesh.mm 17 months," week age Answer is D pte Ties © M hesetin 1 yea, 3 months age ‘Answer i D, Microsoft seems to have confirmed ths. https//docs microsoft. com/en-us/answers/questions/13056 /temporal-table-n-azur synapse htmit~text= Unfortunately22C200we%20do%20nctX20supportsubmitted’k20by'20another'é20Arure%20customer. ‘pte nee © & dav722 5 year 3 months ago ‘Answer D is correct Temporal tables mentioned inthe lnk below are supported in Azure SQL Database(Paas) and Azure Managed Instance, where 2 in this question Dedicated SQ1 Pools are mentioned so no temparal tables can be used, SCD Type 2 the answer hpsi/docs mirosof comven-us/azure/azure-sql/temporal-tables puted 5 tines (© @ exeoins 1 year, Smonths ago Definitely answer D pted 3 times © & [Removed] ea, s montis a2 ‘The answer is A = Temporal tables "Temporal ‘ables enable you to restore row versions from any point in time https//decs microsoft com/en-us/azure/azure-sql/database/business-contiuity-high-aallabilty-isaster-recover-hadr-overview puted Timesauestion #13 opie 1 You have an enterprise wide Azure Data Lake Storage Gen2 account. The data lake is accessible ony through an Azure virtual network named wert You are building @ SQL poo in Azure Synapse that will se data from the data lake, Your company ha a sales team, Allthe members of the sales team are in an Azure Active Directry group named Sales, POSIX controls are used to assign the Sales group access tothe files inthe data lake, You plan to lad data to the SQL pool every hour You need to ensure that the SQL pool can load the sales data from the data lake. Which three actions should you perform? Each correct answer presents part ofthe solution NOTE: Each area selection is worth one point. ‘A. Add the managed det tothe Sale group. 8. Use the manage dently a5 the credential forthe data load process C. Create a shared access sgnatute (SAS). ©. Ad your Azure Active Directory (Azure AD) account tothe Sales group. Use the shared acces signature (SAS) as the credemals forthe data load process. F Create a managed identity Correct Answer: BF ‘The managed identity grants permissions tothe dedicated SQL pools inthe workspace Note: Managed identity for Azure resources isa feature of Azure Active Directory. The feature provides Azute services with an automatically managed identity in ‘Azure AD- Reference: hitpsffdocs microsoft com/en.us/azure/synapse-analytcs/securty/synapse-workspace-managed-dentity Community vote dsmbuton © & diane (GETEI)1 ye 6 mons 290 correct answer is ABFhttps/www examtopiescom/dscussions/microsof/vew/1207-exam-dp-200-ropic-1-question-S6-discussion/ upoted 721s © vith year smonths 290 Yes but the order is clffeent itis FAB ted 35times © A Kinglo 1 yea:, 3 months 290 ‘The question did't speciy erder or sequence ted 10ines A gesdscoder * yoo, Bracth, agree with you pated nee © A ikl (aT) 1 you 4 months ago Comect Answer should be F Create a managed identity. {A dd the managed identity to the Sales group, B Use the manages identity a5 the credentials forthe data load process pvted 30 tines © & ituox (ERT) week, 3 doy ago month 390, Fist create a managed ID, Add the managed ID, use the managed ID pte Times © & mahantal | month weske ag0 Can anyone explain why we cannot useshared access signature inthis case?‘pte inas © & dmitrypo 1 ont, 1 week ago “Managed Identity authentication is required when your storage account i attached to 8 VNet. https: leotn microsoftcom/en-us/saute/synapse-analics/sq)-data-warehouse/quickstart-bulk-load-copy-tsal-examples ‘voted T nas BB eval 3 months, 2 weeks ago AGF isthe corract ane pote Times © A Deekshat234 3 months, weeks ago ABE is conrect pted times © & Dethy 6 months, 2 weeks 290 correct answer is ABP puted times 2 & Egocentie 7 months, 2 weeks ago ABFis correct ‘puted T ines © @ praticewizards 8 months," ek ago FAB - create, add to group, use to load data ‘puted T tines © & wacky 11 montis a90 Is answer A properly worded? “Aad the managed tent to the Sales group” should be “Ad the Sales group to managed identity" preted ines © & lukeoatine 11 montis 90 FAB should be correct pte ines BM Verodon 11 months, 1 week ago FAB ip correct sequence upvted ines © Sabadamal2010atGmall 11 months, 1 wesk ago 1. Create a managed identity 2, Add the managed identity tothe Sales group. 3. Use the managed identity as the credentials forthe data load process pted times 1B & Maheshymm 1 months,“ week 290 FAB ie correct sequence puted Times © & Lewistrck 11 mons, 2 weeks ag0 ‘Would it even be a good ides to have the data load process be part ofthe Sales team? They have separate responsibilites so should be part of snather group. | know that’s not possible in the answer st, ut" tying to think best practices here puted tines 2 & Astam208 1 yeo:392 Comect answer's FA, 8 puted tines 2 & FredNo 1 year a90 use managed identty puted SinesQuestion #14 HoTsPoT ‘You have an Azure Sypapse Anabytics dedicated SOL poo! that contains the users shown inthe folowing table Name Role Usert___| Server admin User2 | db_datereader seri executes a query on the database, andthe query retuns the results showa inthe following exhibit. ebl.name a2 table_nam typ-nane as datatype, 4 cris masked, FROM sys-maskod_columns AS ¢ INNER JOIN ays.tables AS tbl O¥ ¢. (ob: INNER JOIN sys.types typ ON ¢.user_typ WHERE ie masked = 07 Ad = typ.user_type_ia Results Messages name table_name datatype —_is_masked —_ masking_function 1 | BirthDate | DimCustomer date 1 default () 2 | Gender DimCustomer | nvarchar | 1 default () 3 | EmailAddress |DimCustomer nvarchar | 1 email () 4 | YearlyIncome | DimCustomer | money 1 default () Usert isthe only user who has access to the unmasked data, Use the drop-down menus to select the answer choice that completes each statement based on the information presented inthe graphic. NOTE: Each correct selections worth one point Hot ares: Answer Area When User2 queries the Yearlyincome column, the values returned will be [answer choicel. v a random number the values stored in the database XXXX 0 When Userl queries the BirthDate column, the values returned will be [answer choice] a random date the values stored in the database XXX 1900-01-01Correct Answer: Answer Area ‘When User? queries the Yearlyincome column, the values returned will be [answer choice] v a random number the values stored in the database XXX 0 When Userl queries the BirthDate column, the values returned will be [answer choice]. v a random date ‘the values stored in the database 00K 1900-01-01 Box 1:0 The Yearlyincame column sof the money datatype ‘The Default masking function: Ful masking according to the datatypes ofthe designated fields » Use a zero valve fr numeric datatypes (isin, bit, decimal int, money, numeric, small, smallmoney,tnyn, loa, eal), Box 2: the values stored nthe database Users wth administrator privileges ae always excluded from masking, and see the orginal data without any mask. Reference: btpsf/docs.mierosoft.com/en-us/azure/azure'sal/database/dynamic-date-masking overview © & hseta (ERT TOE)1 yea, 3morths ago User Tis admin, so he wl see the value stored in dbms 10 2. Value in database puted 65 times © & anurearmy year, 1 month 290 Bis wrong voted? nes 2 ‘Aaltya0891 Sons, 2weoks ago zureammy read the question properly anl then answer, nd is queried by user, masking doesnt apply to user? ‘pote tines © & ile Gama year, 3 months ago + Use a zero value fr numeric dats types (bignt. bit, decimal, nt, money, numeric small. smallmaney,tinyint, fot rea + Use 01-01-1900 for datetime data types (date, datetime2, datetime, datetimeofiset,smalldatetime, time). prted T7imes © & berserkeap 1 yea: 1 month ago ‘The second question is queried by User 1 who is the acmin ted 7 times © & ovésehool (Sn) wees, days ag0 User is reader 0 he wl 00 querying Yearhincame with defauk mask; User is admin and ony he wil seal stored values preted nes © A Deckshat234:3 months, 2 weeks 250 ‘pvted 3 nas © & azures0otest 5 months 299 User 1 The value User 2.100% see https//docs microsoft com/en-us/szure/szure-sal/database/dynamic-data-masking-overview?view=32uresqlAND https/wwselshack com/ynamie-data-masking-n-sol-server pte 2 ines BW objecto 5 months, 3 weeks ago According to https//docs microsoft comven-us/sl/elational-databases/security/dynamic-data-maskngview=sol-server-ver16 “For date and time datatypes use 01.01.1900 0:00:00.0000000 (dat, datetime2, datetime, datetimeottset,smalldatetime, time), Data masking as defau puted Times 1a date dateime smalldate should be 1900-01-01. Strangely here is no such option Any ideas anyone? © & objecto 5 months, 3 weeks 290 Damn, User 1 only reads Yearlylncome (not date, 50 yes Os the corect answer poted 7 ines 2 & Doth & months, 3 weeks ago 10 2, Value in database ‘pote Times BB Egocentie 7 months, 2 weeks ago con this question its just about paying attention to detail ‘puted Sines © & manant6 7 montis, 4 weeks ago How user? can access data as itis masked? puted times © & manant6 7 months, « weaks ago Can Someone explain frst option asin doc it says 0 ‘puted T nes © Mahesh.mm 1 months," week age 1.0 (Default values for money datatype for masked function will writen when queried by user2) 2. Value in database (As it is queried by user! whois admin) ‘pted 8 times 2 Milan correct ‘poted times 1 yeat,1 month ago BB gssdéscoder year 1 month apo ‘Agree with answer, but | 38 a typo in the question db_datereader MUST be db_ datareader, puted 3 mes 2 & du 1 yea, 1 month ago 0 for money and 1/1/1900 for dates https//docs microsoft com/en-us/sql/elationa-databases/security/dynamic-data-masking?view=sel-server-ver1S pote 5 tines © & ditya049r 5 mons, 4 wesks ago attention to detail, What you mentioned is the default used for date incase of masking but here the query is executed by user 1 who has ‘access othe table and no masking rues will be applied for user 1 so that answer is value present in the database ted 3 nes © & GervasioMontaNelas 1 year, 2 montis 290 Its carect puted? ines Bolles year, 3 months ago upvted tines © Mazazing 1 yer, month 290 yes, its correct ted 2imetYou have an enterprise data warehouse in Azure Synapse Analytics. Using PolyBase, you create an external table named [Ext Items] to query Paquet files stored in Azure Data Lake Storage Gen? without importing the data to the data warehouse. ‘The extemal table has three columns, You discover thatthe Parquet files have a fourth column named ItemD, Which command should you run to ad the ItemD column to the extemal able? A ALTER ETERNAL TABLE [Fat] . (Ttems] ‘ADD [ItemzD] ints 3 DROP EXTESNAL FILE FORMAD parquevfilel; CREATE EXTERNAL FILE FORMAT parquetfilel wrTE ( FORMAT_2YPE = PARQUET, DATA_COMPRESSION = ‘org-apachs.hadcop.io.ccmpress.snappycodac’ io « DROP EXTERNAL TABLE [Ext]. [Ttems] CREATE EXTERNAL TABLE [Ext]. [ztems] ({rtemrD] [int] woct, [rtemvane} nvarchar (50) uz, [itemtype]_nvarchar (20) NULL, [Trempeseription] nvarchar(250)) wim ci LocaPION= ‘/rtems/", DATA SOURCE = AzureDataLakestore, FILE_FORMAT = PARQUER, REJECT_TYPE = VALUE, REGECT_VALUE = 0 ” 0. ALTER TABLE [Ext]. [Teens] ADD [ItemzD] ints Correct Answer: C Incorrect Answers ‘A.D: Only these Data Definition Language (ODL) statements are allowed on exteral tables: “> OREATE TABLE and DROP TABLE > CREATE STATISTICS and DROP STATISTICS “> OREATE VIEW and DROP VIEW Reference: bupsfdocs.mlerosoft.com/en-us/sql-sl/statements/createextemaltabletransact-sl © & chien_Nguyen.Van TITIES) yor 3 months ago Ciscorect tps fmatexamtopice com dlscussonsmicrosofvew/19469-eram-dp-200-opic vee tines, © & oésehool (aaa) weeks, Says ag0 Cie Comect pated nes question 27-dscussion/ © & Selmas7 3 weoks, 1 ¢ay 290 [stil can understand why it's not pte tines © A Deckshat234 3 months, weeks ag0 correct, pte T ines 2 & sp17 4 months, 3 weeks agoCis comrect. ven f you are confuse with other options. The cue here i Keyword Location while creating extemal able, LOCATION = “older_or-flepath’ Species the folder or the ile path an file name forthe actual data poted 2 tne BW Oxren8 months, 2 weeks ago Good thing the detail re shown here: “The external table has three columns." And the solution yet reveals the column details This doesnt make ‘any sense to me. If Cis the correct answer (only one that seems acceptable, then the question isl flawed ‘puted 2imes BB dduquer 3 ys, 18 hours 90 ‘The external table has 3 columins but the fie it references has 4 columns a the external table has to be altered pte times © & PallaniPatel 10 montis, 1 week a90 ciscomect ‘puted Tinas © & hugoborda 1 yea, 2 mont ago Answer is corect pte tinesHoTsPor You have two Azure Storage accounts named Storaget and Storage2. Each account holds one container and has the hierarchical namespace ‘enabled, The system has files that contain data stored inthe Apache Parquet format You need to copy folders and files from Storage to Storage? by using a Data Factory copy activity The solution must meet the following requirements: “> No transformations must be performed. .» The rignal folder structure must be retained - Minimize time required to perform the copy activity How should you configure the copy activity? To answer, select the appropiate options inthe answer are, NOTE: Each correct selections worth one pont. Hot Aes: Answer Area Source dataset type: lv [Binary Parquet [Delimited text Copy activity copy behavior: lv [FlattenFierarchy MergeFiles PreserveHierarchy Answer Area Source dataset type: lv Binary Correct Answer: [Delimited text Copy activity copy behavior: lv [FlattenFierarchy MergeFiles jerarchy Box 1: Parquet For Parquet datasets, the type property of the copy acivy source must beset to ParquetSource Box2 Preservelirarchy PreserveHierrchy (default: Preserves the file hierarchy in the target folder. The relative path ofthe source fete the source folder is identical to the relative path of he target fle to the target folder Incoreect Answers -> FlattenHierarchy ll files from the source folder arin the fist level ofthe target folder, The target files have autogenerated names, - Merges: Merges all les frm the source folder to one file Ith fle name is specie, the merged file name I the specified name, Otherwise isan autogenerated fle name. Reference: hntpsffdoes.mierosoft.com/en-us/azute/dat-facory/format parquet htis:/éocs.mictosoft.com/envus/azure/dateTactory/eomector-azure- datalake storage © & eddyRoboto (Hanan) yor 9 monte ago This could be binary a source and sink, since there are no transformations on files, tend to believe that would be binary the correct anwer.pvted 60 tines BA 100) 9 months, 2 weeks ago ‘Agree, Ive checked it. With binay source and sink datasets it works ‘ted 2ines © Gametite yer, 1 month ago But the doc says “When using Binary dataset in copy activity, you can only copy rom Binary dataset to Binary dataset” So | guess it's parquet then? ted Stinet © A captainpike 1 year, 1 month ago ‘This note is referting tothe fact that, inthe template, you have to specify "BnarySink” a the type forthe target Sink and that exactly what ‘the Copy data tool does. (you can check tis by eating the created copy pipeline and see the code) Choosing Slnary and PreserveHierarchy copy al fil as they are perfectly. poted 3 tines 2 Jed.ethak 1 year, 1 month ago no it must be parquet because The type property of the dataset must beset to Binary and its parquet hear so answer are correct ‘voted T nas © A michas 1 yet, 2 months ago agree. If its just copying then binary is fine and would probably be faster ‘ated tines © & AbhiGate Gara) ye, 2 mons ago “Answer seems corect as data is store is parquet already and requirement isto do no transformation so answers right protest tnes © & NintyFour 6 months, 2 weeks 290 ’As question hss mentioned, Minimize time required to perform the copy activi. ‘And binary faster than Parquet. Hence, inay is answer pvted 3 mas © & poko7 (TEER) days, hous ago Answers comect, Upveted tines A temace 2 weeks, 2 daye 050 Binary - copy fles sis in fastest ay. PreserveHicrarchy - for saving folder structure pte Times BA Oldschool 2 weeks, s days 290 “Answer i correct, No transformation and preserve hierarchy puted times © & RBKasemodel2 weeks, 5 days 399 [believe the answer shouldbe Binary, since its stated that no transformations must be done. “You can use Binary dataset in Copy activity, GetMetadata activity, or Delete activity. When using Binary dataset, the service doesnot parse fle content but eat asi https//learn microsoft comyen-us/szure/data-factory/format-binary | could’ found any information saying that parquet won't be parsed if the source and sink are parques fs. Sothink it will parse, and we can Uungerstan that fis transformation puted times 2 & allagowt 2 montis 290 Answer seems cortec, advice don’ overthink the source is parquet and it's one ofthe options so iis parquet. pote 3 tines 1D A Deekshat234 3 months, 2 weeks a50 siven ansis correct ‘pte Times BA AKCI1 Amon, 2 weeks 250 Binary would work But since the question refer to the source filetype as Parquet and thats one ofthe option provided, | wauld go with Parquet puted Times © & RIKOT 6 months, 1 week 290 Agree with answer puted times © & NintyFour & months, 2 weeks 250{As question has mentioned, Minimize time required to perform the copy activi. ‘And binary i faster than Parquet. Hence, inary is answer pte 2 tines © & AnureRan 6 months, 2 weeks 90 Isit binary or parquet? \pvted net © & DingDongsingSong 8 montis, 2 weeks ap9 So what isthe answer to this question? Binary or Parquet? The files a Parquet. f youre simply copying afl, you just need to define the right source type (ie. Parquet inthis instance. Why would you even consider Binary when te file isn* Binary type puted 3 times 2 & kami 8 months, 3 weeks 290 Ive just tested iin Azure, created two Gen? storage accounts, used Binaty as source and destination, placed two parquet files in account one, Created pipeline in ADF, added copy data activity and then define fst binary as source with wldeard path parquet) and the sink 3 binary, with Inked service for account 2, selected Preservehierarchy it worked puted 1 tines © & Anshulsuryawanshi 9 montis 99 ‘When using Binary dataset in copy activity, you can only copy from Binary datast to Binary dataset upted times © & Sandipau 10 mons, 8 weeks ago this snouts be binary puted tines BM Verobon 11 montis, 1 week ago ‘The type property of the dataset must beset to Parquet hitps//docs mirosof com/en-us/azure/data-actory/format-parquetiparquet-as-source ‘poted 2 nesYou have an Azure Data Lake Storage Gen? container that contains 100 TB of data You need to ensure tha the data inthe containers available for read workloads in a secondary region ifn outage occurs inthe primary region. ‘The solution must minimize costs, Which ype of data redundancy should you use? ‘A. geoedundant storage (GRS) 8. readsaccess georedundant storage (RA-GRS) . zone-redundant storage (288) © ocaly redundant storage (URS) Correct Answer: 8 ‘Geoedundant storage (wth GRS or G2RS) replicates your data to another physical location inthe secondary region to protect against regional outages. However that data is avaabe to be read only if he customer or Microsoft intiates a fallover from the primar to secondary region. When you enable read access tothe secondary region, your data is avallable tobe read at al times, including ina situation where the primary region becomes unavailable Incorrect Answers ‘A While Georedundant storage (GRS) is cheaper than Read-Acoess Geo-Redundant Storage (RA-GRS), GRS does NOT inate automatic tallover ©,0: Locally redundant storage (LRS) and Zoneedundant storage (ZRS) provides redundancy within a single region Reference: btpsifdacs. microsoft. com/en-us/azute/storage/common/storageredundancy Community vote dtrbution © & meet GETTATS)1 year, months aga Bisright Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location inthe secondary region to protect against regional cutages. However, that data is available to be read only if the customer or Microsof intites a failover fom the primary to secondary region. ‘When you enable read access tothe secondary region, your data is avaiable to be read a all times, including ina stuation where the primary region becomes unavailable, puted 76 times 2 & devader 10 moths, 4 weeks 350 ‘looks corect answer, RA-GRS s always avalible because its auto failover Since this not asked inthe question but mere importantly the question is about reducing cost which GRS. ‘voted 20 ines © kenmexam 1 month 290 ‘The question clearly says “savallable for read workloads in a secondary region’. Thisis only available when choosing RA-GRS." With GRS, when a ciaster happens in the primary region the user hasta imitate 3 fever so thatthe secondary region becomes the primary region". [Ate point you are reading from your secondary regian with GRS. Hence i believe the answers should be B “https/ear microsoft. com/en-us/azure/storage/common/storage-redundancy#geo-redundant-storage “https: /learn microsoft com/en-us/azure/storage/comman/storage-disater-recovery-guidance ote Times BB BRIO 9 months, 3 weeks 390 It should be A because of two reasons: 1. Minimize cost 2, When primaty is unavailable Hence No need for RA.GRS. ted tines © & illyob0604 3 dys, 18 hours spo Exactly. This is the point It clearly states" case of an outage! RA-GRS --> secondary region can be read also notin a case of outage pated ines © & Sashain San Francisco (GIES) 1 yo" 399 Imrmy opinion, | believe the and answers A and this why. Inthe question they state *. available for read workloads in a secondary region IF AN OUTAGE OCCURES inthe primary.” Wel, answer B (RA-GRS) states in Microsoft dacumentation that RA-GRS i for when". your datas avalable tobe read AT ALL TIMES, including in situation where theprimary region becomes unavailable” ‘To me, the nature ofthe qu nis what isthe cheapest solution which allows for failover to read workload, when there isan outage. Answer (8) ‘Common sense would be A’ too because that is probably the most often reab-ife use case upvted $8 ines © & Sabadamal20r0atGmall 11 months, 1 week ago It’s not about common sense rather about technology. With GRS, data remains available even if an entire data center becomes unavailable orf ‘theres a widespread regional failure. There would be a down time when a region becomes unavailable. Alternately, you could implement read access geo-redundant storage (RA-GRS), which provides read-access tothe data in altemate locations. puted nae © & Rrko7 (Henna) 5 days, 19 hours ago Read-access geo-redundant storage (RA-GRS) is correct answer, pote T tines PB OléSehool 2 weeks, s day 250 RA-GRS is always ON if selected no matter regional outage, GRS activates only in case of regional outage and thst wae the question, alsa it's puted T ines © & Mvia1029 1 month ago ‘With GRS or GZRS, the data in the secondary region int available fr read or write access unless there isa fllover tothe secondary region. For read access tothe secondary region, configure your storage account to use Read access geo redundant storage (RA GRS) >Read access geo zone redundant storage (RA GZRS), pte Times 2 & prosery 2 months 290 Bis for sure wrong Answer Comect Answeris A Because question ask minimum cost. which i A, Furthermore, RA-GRS is always readable in 2nd region even if you don't have any availabilty issue in primary region. So Correct ansuer is A puted 7s © yyphnh 3 months, 1 week ago GGRS does NOT intste automatic falover, So Bis correct ‘puted T ines © kevint1210 3 months, 2 weeks ago Should be A ‘puted 2 ines 2 bagustte 3 months, 3 weeks ago ‘is cheaper pted 3 times © & aurorafang 3 months 3 weeks 290 also vote for A after reading the comments puted Times BB uty 3 months, 3 weeks ago read-access geo-redundant storage (RA-GRS) isthe answer asin the question it's mentioned, Only read access to be available upvted Ttines © & dainh225 «months, 1 week age RS ws Ra GRS The diference between GRS and RA GRS is faity simple, GAS ony allows to be ead inthe secondary zone int primary o secondity while RA GRS allaws the option to read in the secondary whenever. 1 even of» filover from the pte tines 2 & Jainh225 4 months, 1 week age SRS vs a GRS ‘The diference between GRS and RA GAS is fairly simple, GRS only allows to be read in the secondary zone inthe even of 2 failover from the primary to secondary while RA GRS allows the option to read in the secondary whenever.pte tines 2 & prathamesh1996 6 months, 2 weeks 290 ‘Ais Cotrect for Minimize cost § When primary is unavailable, pte tines © & Andushi7 months, 1 week age A because of costs aspect ‘pte nes © muove 7 mnths, 3 weeks ago Als correct because of cost, RA-GRS will cost $5,910.73, GRS wil cost 4586.12 pte 3 tines BA Dicer months, 1 week 90 I you go to open a Azure storage account and choose GRS/ZRS/LRS/RA-GRS, there wll bea notice reminding you which options ae cheaper. From cheapest to most expensive: LAS The (complete) DataCenter, not the region and not components inside a DataCenter. So, what helps us inthis situation? RS: "copies your data synchronously thre times within a single physical location in the primary region.” importants here the SINGLE PHYSICAL LOCATION (meaning inside the same Data Center Sin our scenaia all copies woulkn’ work anymore) => Cis wrong. ZRS:" copies your data synchronously across three Azure availability Zones inthe primary region” (meaning, in different Data Centers. ln our Scenario this would meet the requirements) => Dis right {GRS/GZAS: are lke LRS/ZRS but withthe Data Centers in diferent azure regions. Ths works too but is more expensive than ZS. So ZRS isthe right hitps/docs microsoft com/en-us/azure/storage/common/storage-redundancy pres 49 tines 8 & Detar 11 months ago I agree. Please give ths comment medal {ora cookie, ‘puted 3 nes BM Orrens months, 2 weeks ago Yes, well sad, that's the correct answer ted I nee© Narasimhap 9 months, 3 weaks ago Well explained! pote ines © & greentever (ETT) 1 month, 3 wesks 290 Microsoft recommends using ZRS in the primary region for Azure Data Lake Storage Gen2 workloads pte Times © A Deekshat234 3 months, 2 weeks ago Dis correct pote Times © & RUKOT months, 1 weak ago Dis corract ast talks abo, pte Times Gata center” means we cannot use the LRS (LOCAL) © i olavrabs 6 months. 3 weeks 90 [D> Data is replicated synchronously poted tines © & Egocenttle 7 months, 2 weeks ago Discorrect puted 2simes 2 & ravi2931 7 montis, 3 weeks ago it snoule be 0 ‘puted Tinas © & ravi2931 7 montis, 3 neeks ago See ths explained leary LAS isthe lowest-cost redundancy option and offers the least durability compared to other options. LRS protects your data against server rack and drive falures. However ifa disaster such a fire or flooding occurs within te data center al replicas ofa storage account using LRS may be lost or unrecoverable. To mitigate this risk, Microsoft recommends using zone-redundant storage (285), geo-redundant storage (GRS), oF geo- zone-redindant storage (GZRS) ted | nee © & AS61208 8 mons 290 ‘Answer shouldbe D, as LRS won't be helpfull incase of whole datacenter flr pote Times BA Andy91 8 months, 1 weak ego Thisis the corect answer indeed ‘pted times © & bhanuprasad9331 9 months, 1 week ago ‘Answer is LRS, From microsoft docs: RS replicates data ina single AZ. An AZ can contain one of more data centers. So, even fone dats center fils, dats can be accessed through other datacenters inthe same AZ, hitps//docs microsoft com/en-us/azure/avalabilty-zones/az-overvewtavaiabilty-20Nes hitps/docs mirosof com/en-us/azure/storage/common/storage-redundancy#redundancyin-the-primary-region puted times © & PallaiPatel 10 montis, 1 weak 290 Dis correct puted 3 mes © & vimalnits 10 monts, 1 week 290 Comect answer's. ‘pte 2 times © & Tinsaaaasa 10 months, week ago LAS helps to replicate data in the same data center while ZS replicates data synchronously actoss three storage clusters in one regionpte T ines © Shatheesh 10 months, 2 weoks ago Dis the corect answer, In question its clealy mentioned if datacenter alt shouldbe available, LRS stores everything in sane data center so it's not the correct answer, nest cheapest option fx ZRS. pte T ines 1B & Jaws1990 11 months age Mentions data centre Availability Zone) filre, not rack fale, so should be Zone Redundant Storage, ‘pvted 3 nas BB Detar 11 months ago note thatthe "lata centre fal pvted 7 nasHoTsPor You have a SQL pool in Azure Synapse You plant load data from Azure Blob storage toa staging table, Approximately 1 milion rows of data willbe loaded daily. The tble will be ‘wuncated before each dally loa, You need to create the staging table. The solution must minimize how long it takes to load the data to the staging table How should you configure the table? To answer, select the appropriate options inthe answer area NOTE: Each correct selection I worth one pont. Hot aren Answer Area Distribution: v [Hash Replicated [Round-robin Indexing: Partitioning: Answer Area Distribution: mA Replicated [Round-robin comet Waexing = [Clustered 7 |Clustered columnstore_| [Heap Partitioning: Vv |None Box: Hash Hashstrbuted tables improve query psfrmanceonlarge fact bles, They can have vr ge numbers of ms and sil achieve high perfomance Incorrect Answers Round-obin tables ae useful for improving loading speed. Box 2; Clustered columnstore- ‘When creating partitions on clustered columnstore tables, itis important to consider how many rows belong to each partion For optimal compression and performance of clustered columnstore tables, @ minimum of 1 milion cows per distribution an partition is needed. Box: Date- ‘Table partons enable you to divide your data into smaller groups of data, In most cases, table partitions are created ona date column, Pertti switching canbe used to quickly remove or replace a section ofa tableReference: tpsifdocs.mierosoft.com/enus/azute/synapseanalyics/sql-data-warehouse/safdata warehouse tables partion bttpsi/does.microsoftcom/en-us/azute/synapse-analytcs/sqtdate warehouse/sob-data warehouse tables distribute © & av000 (EETaTa) yor, mnths 50 Round-Robin Heap None puted 298 tines BM anto69 10 months, 2 weeks a0 agree too ‘voted 3 nee 1B & Narasimhap 9 months, 5 weaks ago Round Rebin Heap None. No brainer fortis question puted 7 nas © & gssdéscoder 1 yea 1 month ago Agree 100%, Allin paragraphs under this: htps//docs microsoftcomyen-us/azure/synapse-analytics/sa/develop-tables-overview. ‘poted Sines BB rtaz 11 month ago Also agree 100% ‘pated 2imes © A Deepshikhat228 4 months, 7 week ago agree, Round Robin, Heap and None i the correct option puted imas © & laszek (HETTECTS] yo, 9 months ago Round-robin - this iste simplest clstribution model, not great for querying but fast to process Heap -no brainer when cresting staging tables No partitions this isa staging table, why add effort to patton, when truncated daly? ‘pvted 40 tines © berserkeap 1 yea: 1 month 290 Had doubts regarding why there is no need fora partion. While what you suggested is true wart it be better if there isa date partition to truncate the table ? pote Ives BM andy.g 9 months, 3 weeks ago ‘Ther sno fiker on 3 truncate statement so na benefit in having » partion ‘ted ines © & Vardhan_Brahmanapally 1 yea 292 ‘Can you explain me why should we use heap? ‘voted T nes 8 & Detar 11 months ago The term heap basicaly refers toa table without a clustered index. Adding a clustered index to a temp table makes absolutely no sense and ita waste of compute resources fora table that would be entirely truncated daly no clstered index = heap. pote tines © & saLDev0000 8 rons, 1 Week 90 Drtazis right in ation, when you populate an indexed table, you are also wrting tothe index, so this adds an adltional overhead in the write process poted 7 ines © & peko7 (ETAT) days, rho ago ‘Ast only about stage table soit should be Round-Robin| Heap None pte | ines © A rohitbinnant 1 wee, &43ys 350