How to use CETAS (Synapse Serverless Pool) in dbt?

user_9907980 0
Viewed 407

In Synapse Serverless Pool, I can use CETAS to create external table and export the results to the Azure Data Lake Storage.

CREATE EXTERNAL TABLE external_table
WITH (
    LOCATION = 'location/',
    DATA_SOURCE = staging_zone,  
    FILE_FORMAT = SynapseParquetFormat
)  
AS
SELECT * FROM table

It will create an external table name external_table in Synapse and write a parquet file to my staging zone in Azure Data Lake.

How can I do this in dbt?

azure-synapse dbt
2 Answers

I was trying to do something very similar and run my dbt project with Synapse Serverless Pool, but ran into several issues. Ultimately I was mislead by CETAS. When you create the external table it creates a folder hierarchy, in which it places the parquet file. If you were to run the same script like the one you have as an example it fails because you cannot overwrite with CETAS. So dbt would be able to run it like any other model, but it wouldn't be easy to overwrite. Maybe if you dynamically made a new parquet every time the script is run and deleted the old one, but that seems like putting a small bandage on the hemorrhaging wound that is the synapse and severless pool interaction. I had to switch up my architecture for this reason.

I was trying to export as a parquet to maintain the column datatypes and descriptions so I didn't have to re-schematize. Also so I could create tables based of incremental points in my pipeline. I ended up finding a way to pull from a database that already had the datatype schemas, using the dbt-synapse adapter. Then if I needed an incremental table, I could materialize it as a table via dbt and dbt-synapse and access it that way.

What is your goal with the exported parquet file?

Maybe we can find another solution?

Here's the dbt-synapse-serverless adapter github where it lists caveats for serverless pools.

user_114010730
user_99079800
Related