Azure Synapse SQL Serverless Virtual Data Warehouse

Question

Hi,
I have many GBs of data in a data lake in partitioned parque files. I am using Azure Synapse SQL Serverless to expose this data lake to Power BI authors via virtual data warehouse defined in T-SQL for the data exploration phase of a data analytics project.

What is the best means of exposing this data lake data within SQL Serverless is it External Tables or Views?

Initially I thought of using External Tables. However basically I have to use varchar for the vast majority of the columns since data types such as Date are not available.

So now I am creating views and Casting the relevant data types when exposing these views to Power BI.

Does anyone have any thoughts on this?

Kieran Wood , Microsoft Certified Data Engineer and Solutions Expert in Data Analytics

http://uk.linkedin.com/in/kieranpatrickwood

Answer 1

After further investigation if when creating the parquet file using Synapse Pipelines I use the mapping type column for example as follows ...

I can use most of the data types when defining and External table using parquet files as a data source ....

DROP EXTERNAL TABLE [dbo].[NYT_Fact]  
GO  
CREATE EXTERNAL TABLE [dbo].[NYT_Fact] (  
 VendorID tinyint,  
 tpep_pickup_datetime DATE,  
 tpep_dropoff_datetime DATE,  
 RatecodeID  tinyint,  
 PULocationID  smallint,  
 DOLocationID  smallint,  
 payment_type  tinyint,  
 passenger_count tinyint,  
 trip_distance  DECIMAL (8,2),  
 fare_amount  DECIMAL (8,2),  
 extra DECIMAL (8,2),  
 mta_tax  DECIMAL (8,2),  
 tip_amount  DECIMAL (8,2),  
 tolls_amount  DECIMAL (8,2),  
 improvement_surcharge  DECIMAL (8,2),  
 total_amount  DECIMAL (10,2),  
 congestion_surcharge  DECIMAL (8,2)  
 )  
 WITH (  
 LOCATION = 'Parquet/NYT/**',  
 DATA_SOURCE = [synfilesys_woodk_dfs_core_windows_net],  
 FILE_FORMAT = [SynapseParquetFormat]  
 )  
GO  
SELECT * FROM  [dbo].[NYT_Fact]  
GO  
  
  
  

The main exception I have found so far is Boolean.

Kieran Wood , Microsoft Certified Data Engineer and Solutions Expert in Data Analytics

http://uk.linkedin.com/in/kieranpatrickwood
www.dataplatformservices.com