Blog Post

Azure Synapse Analytics Blog
6 MIN READ

How to use CETAS on serverless SQL pool to improve performance and automatically recreate it

Liliam_C_Leme's avatar
Liliam_C_Leme
Icon for Microsoft rankMicrosoft
Jul 19, 2022
Serverless SQL pool  has a very cool way to handle CREATE EXTERNAL TABLE AS SELECT (CETAS).  I mean, you can use a serverless SQL pool to create a CETAS which will materialize the query results. It m...
Updated Jul 06, 2022
Version 1.0
Synapse SQL
TePeBox's avatar
TePeBox
Copper Contributor
Jul 25, 2022

Liliam_C_Leme MrAndyCutler Maybe I wasn't very clear on my issue...
So just imagine you have a Power BI dataset which is used to visualize the result of the CETAS. So you need to go and refresh the Power BI dataset i.e. daily our hourly. Lets imagine we use another process to kick of the refresh of the query we use with CETAS. It should create always the same file (so that Power BI can consume it) and it should be always there (so Power BI can refresh its data at any time with the latest data the CETAS created, so i.e. if a user presses the "refresh" button outside of the regular reprocess schedule).
So any solution which is renaming the table wouldn't work and if the data isn't available at all time (so as in the original solution in the timeframe the old data was deleted and the CETAS is not done, yet) the Power BI would have empty data.

There's a plentitude of similar scenarios, you might somehow get around those cases by syncing or parametrizing processes but it would be way easier if there's a simple "refresh" option for the CETAS