Parquet Exports From SQL Server 2022 With CETAS

A CSV export can turn one table into a debate about quoting, dates, and types before it reaches a lake or Snowflake. Parquet exports preserve a typed column structure and support efficient downstream reads. SQL Server 2022 can write them through PolyBase and CREATE EXTERNAL TABLE AS SELECT.

Confirm the Platform for Parquet Exports

CETAS export to Parquet is supported on SQL Server 2022 and later, with a configured external destination. Use Azure Blob Storage, ADLS Gen2, or S3-compatible object storage. The example uses an existing S3-compatible bucket and a dedicated export prefix. Install and configure the required PolyBase components, and confirm network reachability from the SQL Server host. Grant only the storage permissions needed to write and read the destination.

I plan the downstream contract before running the export: columns, types, date interpretation, null handling, and partition or folder naming. A successful write is not enough if the consumer reads an amount as a string. Which source columns need an explicit CAST so the file schema is stable across releases?

Enable Export Deliberately

SQL Server 2022 keeps PolyBase export disabled by default. Enable it at the instance level after the storage and security review. This is an administrative change, so run it in a controlled deployment and record the previous value. The setting takes effect after RECONFIGURE; it does not require every query to specify a separate export flag.

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'allow polybase export', 1;
RECONFIGURE;
EXEC sys.sp_configure N'allow polybase export';

The last call displays the configured and running values. Without this setting, the CETAS statement below stops with error 46553, which says Create External Table as Select is disabled. Parquet exports still need permissions on database objects and the external location. Turning on the instance switch does not create credentials or give a user access to a bucket.

Create a Scoped Storage Credential

Use a database master key to protect a database-scoped credential. The S3 example expects an access key and secret key supplied through a secure deployment process. The placeholders below are not working credentials. Do not put real secrets in a blog, ticket, or checked-in script. Rotate them under the storage team's policy and restrict who can alter external data sources.

-- In the export database; supply a strong secret securely.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ReplaceWith-A-Strong-Passw0rd';
GO
CREATE DATABASE SCOPED CREDENTIAL ExportS3Credential
WITH IDENTITY = 'S3 Access Key',
     SECRET = '<access-key-id>:<secret-access-key>';
GO
CREATE EXTERNAL DATA SOURCE ExportS3
WITH (LOCATION = 's3://storage.example.internal:9000',
      CREDENTIAL = ExportS3Credential);
GO

If the database already has a master key, do not create another. The external source LOCATION names the service endpoint; the CETAS LOCATION names the bucket and export path. Azure storage uses a different credential pattern. Choose one supported provider and test a small write before scheduling a full table.

Define Parquet and Export a Query

Create an external file format with FORMAT_TYPE = PARQUET, then use CETAS. The source query explicitly selects columns and casts the amount to a stable decimal type. Give Parquet exports an empty, unique location for each run. CETAS writes files and creates an external table pointing to them; it is not a regular local table copy.

CREATE EXTERNAL FILE FORMAT ExportParquet
WITH (FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE dbo.OrderExport2026
WITH
(
    LOCATION = '/exports/orders_2026_09_24/',
    DATA_SOURCE = ExportS3,
    FILE_FORMAT = ExportParquet
)
AS
SELECT OrderID, CustomerID,
       CAST(OrderTotal AS decimal(19,4)) AS OrderTotal,
       OrderDate
FROM dbo.Orders
WHERE OrderDate >= '2026-09-01'
  AND OrderDate < '2026-10-01';
GO

Replace the source table and dates with the approved slice. A file set can contain several Parquet files because export is parallel. Do not promise a single file named after the external table. Check current documentation for supported data types and characters before assuming every SQL Server column can be written directly to Parquet.

Verify Rows and Types in Parquet Exports

Count the source slice and the external table after CETAS completes. Query a few boundary rows, nulls, large amounts, and date values from the external table. Inspect the Parquet file metadata with the downstream reader or a trusted local inspection tool to confirm physical and logical types. This matters when a consumer interprets timestamps or decimal precision differently.

SELECT COUNT_BIG(*) AS source_rows
FROM dbo.Orders
WHERE OrderDate >= '2026-09-01'
  AND OrderDate < '2026-10-01';
SELECT COUNT_BIG(*) AS exported_rows
FROM dbo.OrderExport2026;
SELECT TOP (10) OrderID, OrderTotal, OrderDate
FROM dbo.OrderExport2026
ORDER BY OrderID;

Counts can match while values are wrong, so compare aggregates and samples by key. Check that the object-storage folder contains only the files for this run and that the downstream service reads the same count and schema. A canceled export can leave partial files; treat that folder as failed and use a new destination for a retry.

Make Parquet Exports a Repeatable Handoff

Record the export query, source snapshot or extraction window, row count, file count, schema, and destination path in a manifest. If source rows change during the run, a later count of the live table is not a valid reconciliation target. Use a consistent snapshot or a stable cutoff for a strict comparison. A target system such as Snowflake should load from the manifest's exact folder, not a wildcard across old and new exports.

I start with a small date range, validate it end to end, then scale. If an unsupported type needs conversion, make that conversion explicit in the SELECT and document the target meaning. Parquet removes CSV quoting trouble, but it does not replace a data contract. The file is ready when the receiving team can verify both its rows and its types.

Account for File Layout and Types

CETAS can write more than one Parquet file, and file count can vary with parallelism. A consumer should treat the destination folder as a dataset, not look for a single fixed filename. Inspect the files after the first export: count, size, row groups, and schema. Many tiny files can slow downstream discovery and reads; a very large file can make retries expensive. If file layout is important, tune the export design or compact downstream under an explicit process.

Parquet types do not map one-to-one with every SQL Server type. Character collation, decimal precision, datetime offsets, and binary values deserve test cases. I include minimum and maximum dates, nulls, non-ASCII text, and boundary decimals in a small validation slice. A row count alone cannot tell you whether the receiving system preserved the intended meaning.

Plan a Failed Run

CETAS is not an overwrite command for a published folder. Give each run a new path and publish it only after validation. If the statement fails, inspect and clean up the failed destination under an approved storage procedure before retrying. Keep the old good export available until the new manifest is accepted. This prevents a reader from mixing partial new files with yesterday's complete set.

Related reading on this blog: Troubleshooting Common CSV Import Issues and PolyBase Error Msg 46530: External Data Sources Are Not Supported With Type GENERIC.

A Parquet file is not a completed export, it is complete when schema and rows match the source slice.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.