You have a Fabric tenant that contains a lakehouse.
You plan to query sales data files by using the SQL endpoint. The files will be in an Amazon Simple Storage Service (Amazon S3) storage bucket.
You need to recommend which file format to use and where to create a shortcut.
Which two actions should you include in the recommendation? Each correct answer presents part of the solution.
NOTE: Each correct answer is worth one point.
To efficiently query sales data files in Amazon S3 using the SQL endpoint in a Fabric tenant's lakehouse, you should use the Parquet format and create a shortcut in the Tables section. Parquet is a columnar storage file format that is highly efficient for complex queries due to its data compression and encoding schemes. Creating a shortcut in the Tables section allows the data to be recognized as a table, making it accessible through the SQL endpoint for structured queries.
Answer is DE. To be able to use the SQL Endpoint you need to create the shortcut in the Tables section. The file also needs to be in the delta format to be recognised as a managed table. If you try to add a parquet file to the tables section, it will not be recognised as a table object and you won't be able to query it.
A parquet file shortcut is recognized as a managed table. Delta format not supported by S3. The answer cannot be E.
B,D are the correct answers, since the Parquet file format within S3, and the pointing to it via a lakehouse Table shortcut, allows the shorcut to be recognized by Fabric as a Delta table and hence provide access to the SQL endpoint. Explanation: As per the provided links: "If the target of the shortcut contains data in the Delta\Parquet format, the lakehouse automatically synchronizes the metadata and recognizes the folder as a table". It is stated that S3 contains sales data files, that is files not tables. Besides, delta tables are not supported in S3. So the file format needs to be either CSV or Parquet, leading us to choose the latter for all the reasons stated in other comments. So creating a shortcut in Table section that points to S3 Parquet files, will allow Fabric to recognise it as a Delta table, which in turn enables the SQL endpoint.
S3 does support delta parquet: https://aws.amazon.com/blogs/big-data/introducing-native-delta-lake-table-support-with-aws-glue-crawlers/ Answer: DE
I just tested this. Have a S3 bucket with a CSV and a parquet file. Created a shortcut, and tried it to place it in tables. That was not successful. It asked me to move it to Files. So answer to the first part must me A. Create a shortcut in the Files section Both csv and parquet can be loaded as table. That is part of the job. The result is that both will result in a delta table, and it is delta tables that can be viewed in SQL endpoints. So the second answer must be E. Use the delta format.
If you need just query, as they mentioned. Parquet (read in parallel) and shortcut in the table solve this problem. you don't need delta.
why not 'A'? The shortcut will be reading files from the S3 instead of tables.
D and E. Tested. You can turn delta files available in your AWS Service and create a shortcut here.
- E and D E. Use the delta format - Choosing Delta format is most suitable for complex environments that benefit from features like ACID transactions, schema enforcement, and historical data tracking. This choice is particularly effective in lakehouse architectures where maintaining data integrity and supporting complex queries are paramount. D. Create a shortcut in the Tables section - Given the structured nature of Delta format files and the need to perform SQL queries, creating a shortcut in the Tables section is most beneficial. This approach allows the SQL endpoint to efficiently query the data using table semantics, leveraging the optimizations provided by the lakehouse.
For querying sales data files using the SQL endpoint with files stored in an Amazon S3 storage bucket, I recommend the following two actions: File Format: Choose the Apache Parquet file format for the sales data files. Parquet is a columnar storage file format that is optimized for complex queries and is efficient for use with SQL queries due to its data compression and encoding schemes. It supports complex data types and is ideal for large datasets1. Shortcut Creation: Create a shortcut in the Lakehouse Explorer under the Tables section. When you create a shortcut to a Delta formatted table under Tables in Lakehouse Explorer, it will automatically register it as a table, enabling data access through Spark, SQL endpoint, and the default semantic model2. These actions will help ensure efficient querying and easy accessibility of your sales data within the Fabric tenant’s lakehouse environment.
IMHO, D & E Because of that: Shortcuts aren't supported in other subdirectories of the Tables folder. If the target of the shortcut contains data in the Delta\Parquet format, the lakehouse automatically synchronizes the metadata and recognizes the folder as a table. https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts#lakehouse
To effectively query sales data files stored in an Amazon S3 bucket using the SQL endpoint in your Fabric tenant's lakehouse, you should: B. Use the Parquet format D. Create a shortcut in the Tables section **Creating a shortcut in the Files section would treat the data more like raw files, which might not leverage the full potential of SQL querying capabilities. Creating the shortcut in the Tables section aligns more closely with the structured query requirements and provides a better-integrated experience.
To effectively query sales data files stored in an Amazon S3 bucket using the SQL endpoint in your Fabric tenant's lakehouse, you should: B. Use the Parquet format D. Create a shortcut in the Tables section **Creating a shortcut in the Files section would treat the data more like raw files, which might not leverage the full potential of SQL querying capabilities. Creating the shortcut in the Tables section aligns more closely with the structured query requirements and provides a better-integrated experience.
I guess MS documentation says Delta rather than Parquet? https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-sql-analytics-endpoint
B- Because Parquet format is supported in Amazon S3 https://learn.microsoft.com/en-us/azure/data-factory/format-parquet D- Because you need to use the Shortcuts as managed portion of the lakehouse.
ChatGPT is saying amazon S3 supports both Delta and Parquet files
There is no mention of how big the data is , using CSV will be better if the data is small
CSV is best if the data is not that large