Free Data Engineering on Microsoft Azure: Design & Implement Data Storage Questions and Answers — Questions and Answers
Question 1: You have been given the task of creating an enterprise data lake on Azure and carrying out big data analytics while working for a cloud company. Which Azure service from the list below would you choose in this situation?
- Azure Disks
- Azure Files
- Azure Queues
- Azure Blobs (Correct answer)
Correct answer: Azure Blobs
Azure Blob Storage is ideal for building an enterprise data lake and performing big data analytics. It is a massively scalable and highly available object storage solution designed to store large amounts of unstructured data, such as text or binary data. This makes it perfect for raw data ingestion and processing in analytics workloads, supporting various big data frameworks.
Question 2: In Azure Data Explorer, you have established an external table with the name ExtTable. The KQL (Kusto Query Language) query must now be executed on this external table by a database user. Which of the subsequent functions ought he to use to make a reference to this table?
- access_table()
- Only the table administrator can query the table
- external_table() (Correct answer)
- refer_table()
Correct answer: external_table()
In Azure Data Explorer (Kusto Query Language or KQL), to query an external table, you must explicitly reference it using the `external_table()` function. This function takes the name of the external table as an argument and allows users to query data stored in external locations like Azure Blob Storage or Azure Data Lake Storage. It's essential for interacting with data outside the native database.
Question 3: Your current project is a Columnstore table. Even though columnstore tables and indexes are always saved using columnstore compression, you want to further reduce the size of the columnstore data. You choose to configure archival compression, an add-on compression, for this reason. Which of the following techniques would you employ in order to archive compression on the data?
- COLUMNSTORE_ARCHIVE (Correct answer)
- COLUMNSTORE_COMPRESS
- COLUMNSTORE_ARCHIVECOMPRESS
- COLUMNSTORE
Correct answer: COLUMNSTORE_ARCHIVE
To apply additional archival compression to a Columnstore table in SQL Server or Azure SQL Data Warehouse, you use the `COLUMNSTORE_ARCHIVE` compression option. This option provides a deeper level of compression than the default `COLUMNSTORE` compression, which is beneficial for data that is accessed less frequently but needs to be stored efficiently. It helps reduce storage costs for cold data.
Question 4: You are setting up a storage account for Azure Data Lake Gen2. The availability of data must be guaranteed for both write and read activities, even if a whole data center (zonal or non-zonal) goes down. Which replication technique would you apply to the storage account? (Select the least expensive solution)
- Geo-zone-redundant storage (GZRS)
- Geo-redundant storage (GRS)
- Locally-redundant storage (LRS)
- Zone-redundant storage (ZRS) (Correct answer)
Correct answer: Zone-redundant storage (ZRS)
Zone-redundant storage (ZRS) replicates data synchronously across three Azure availability zones within a single region. This ensures data availability and durability even if an entire data center (zonal) goes down, covering both write and read activities. ZRS is the least expensive option among the choices that guarantees availability across zones.
Question 5: With Azure Data Lake Store Gen1, you are engaged. You now realize how important it is to understand the external data's schema. Which plug-in from the list below would you use to learn the external data schema?
- mysql_request
- Pivot
- Narrow
- infer_storage_schema (Correct answer)
Correct answer: infer_storage_schema
The `infer_storage_schema` plug-in in Azure Data Explorer (KQL) is used to automatically detect and understand the schema of external data stored in Azure Data Lake Store Gen1 (or other external storage). This plug-in helps in quickly creating external tables by suggesting column names and data types based on the data's structure. It simplifies the process of querying external data without manual schema definition.
Question 6: In SQL Server Analysis Services, data semantic models must be created. One should adhere to a few best practices for data modeling that are advised. Which of the following procedures would you believe to be the ideal procedures to follow when developing data semantic models?
- Create a dimension model snowflake or/and star, even if you need to ingest data from various sources.
- Only include the integer surrogate keys or value encoding in the model and exclude all the natural keys from the dimension tables.
- Decrease cardinality to reduce the uniqueness of the values and allow much better compression.
- All of the above (Correct answer)
Correct answer: All of the above
Adhering to best practices for data semantic models is crucial for performance and usability. Creating star or snowflake schemas (A) is fundamental for analytical models, simplifying queries and improving performance. Including only integer surrogate keys (B) reduces storage, optimizes joins, and isolates the model from changes in natural keys. Decreasing cardinality (C) by reducing unique values leads to better data compression and faster query execution, making 'All of the above' the correct approach.
Question 7: A history table is automatically created in the same database when you establish a temporal table in Azure SQL Database in order to store the historical records. Which of the following claims regarding the history table and temporal table is accurate?
- A temporal table must have 1 primary key.
- To create a temporal table, System Versioning needs to be set to On.
- If you don't specify the name for the history table, the default naming convention is used for the history table.
- All of the above (Correct answer)
Correct answer: All of the above
All the statements regarding temporal tables in Azure SQL Database are accurate. A temporal table requires a primary key (A) to uniquely identify rows and track changes effectively. System Versioning must be explicitly set to 'ON' (B) for the table to function as a temporal table and for historical data to be managed automatically. If a history table name isn't specified, the system uses a default naming convention (C), ensuring the history table is always created. Therefore, 'All of the above' is the correct answer.
