How to Create External Tables with CETAS in Azure Synapse

João Barros 05 de July de 2026 4 min read

The CETAS command (CREATE EXTERNAL TABLE AS SELECT) in Azure Synapse reads raw data from the Data Lake, transforms it with SQL and writes the optimized result as Parquet, creating a ready-to-query External Table at the same time. It is the simplest way to build a "curated" layer in serverless SQL, without copying data out of the lake or paying for dedicated infrastructure. The example below starts from sales CSV files and produces a table aggregated by country.

Prerequisites

An Azure Synapse Analytics workspace with the serverless SQL pool (Built-in).
An ADLS Gen2 account with CSV files in a folder (for example raw/vendas/).
The Storage Blob Data Contributor role on the storage account — CETAS needs to write files to the lake.
Synapse Studio open, with a new SQL script connected to the Built-in pool.

Step 1: Create the database and master key

Because an External Table is a database object, start by creating a dedicated database (do not use master). The master key is required to protect the credential you will create next.

CREATE DATABASE VendasDW;
GO
USE VendasDW;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Uma-Password-Forte-2026!';

Step 2: Connect to the Data Lake

Tell Synapse where the data lives and how to authenticate. The safest option is to use the workspace Managed Identity, avoiding stored keys. Replace aconta with your real storage account name.

CREATE DATABASE SCOPED CREDENTIAL cred_managed_id
WITH IDENTITY = 'Managed Identity';
GO
CREATE EXTERNAL DATA SOURCE lake_dados
WITH (
    LOCATION = 'https://aconta.dfs.core.windows.net/dados',
    CREDENTIAL = cred_managed_id
);

Make sure the workspace managed identity has the Storage Blob Data Contributor role on the dados container; otherwise the next step fails with a permissions error.

Step 3: Define the output format

CETAS writes its results to files. Here you choose Parquet with Snappy compression — the best option for fast, cheap queries because it is a columnar, compressed format.

CREATE EXTERNAL FILE FORMAT parquet_snappy
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

Step 4: Run CETAS

Now you put it all together. The SELECT clause reads the raw CSVs with OPENROWSET and aggregates sales by country; the result is written as Parquet to the curated/vendas_por_pais/ folder and exposed as the dbo.VendasPorPais External Table.

CREATE EXTERNAL TABLE dbo.VendasPorPais
WITH (
    LOCATION = 'curated/vendas_por_pais/',
    DATA_SOURCE = lake_dados,
    FILE_FORMAT = parquet_snappy
)
AS
SELECT
    pais,
    COUNT(*)   AS num_vendas,
    SUM(valor)  AS total_valor
FROM OPENROWSET(
    BULK 'raw/vendas/*.csv',
    DATA_SOURCE = 'lake_dados',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) AS origem
GROUP BY pais;

Note that the target folder in LOCATION must be empty. If it already exists, CETAS returns an error — a safeguard so you don't overwrite data by mistake.

Tip: always give the target folder a descriptive name (for example, the table name). That makes it easy to find and clean up the files when you need to refresh the data.

Verify the result

Query the new table as if it were a regular table:

SELECT TOP 10 *
FROM dbo.VendasPorPais
ORDER BY total_valor DESC;

Then, in the Data tab of Synapse Studio, open the container and confirm that the curated/vendas_por_pais/ folder contains one or more .parquet files. If you see rows in the query and files in the folder, CETAS ran successfully.

Conclusion

With just a few lines of SQL you created a curated Parquet layer and an External Table ready for Power BI, all without leaving the Data Lake. One important point: CETAS does not update existing data. To refresh, drop the External Table with DROP EXTERNAL TABLE, remove the files from the folder and run it again — or write each load to a date-stamped folder. Which table in your lake makes the most sense to materialize this way first?