Skip to article
Analytics Analytics platform

CETAS

Create External Table As Select, a pattern for writing query results to external storage.

Source: Microsoft Learn - CREATE EXTERNAL TABLE AS SELECT in Synapse SQL Reviewed 2026-05-12

Exam trap
Running CETAS into a reused folder without a cleanup or versioning convention.
Production check
Confirm owner, scope, resource IDs, region, tags, and environment before accepting the current state.
Practice this topic Microsoft Learn
Article details and learning context
Aliases
Create External Table As Select
Difficulty
intermediate
CLI mappings
8
Last verified
2026-05-12
Compare related concepts Search related resources Browse the glossary

Understand the concept

In plain English

CETAS is a Synapse SQL pattern that writes the result of a SELECT statement into external storage while creating external table metadata for later queries. In everyday Azure work, it helps teams materialize expensive query results, stage transformed lake data, and make serverless or dedicated SQL outputs reusable without repeatedly scanning the same source files. You usually see it around Synapse SQL scripts, serverless SQL pools, dedicated SQL pools, external data sources, external file formats, ADLS Gen2 folders, and pipeline notebooks.

Why it matters

CETAS matters because a poorly designed CETAS job can overwrite assumptions, expose data, create expensive duplicate files, or scan large lake folders every time it runs. The business impact is rarely abstract: users see slower systems, missing data, failed sign-ins, confusing reports, or unexpected cost when the setting is misunderstood. A solid glossary entry gives architects and operators the same language for design reviews, support handoffs, and audit evidence. It also helps teams decide what to check first, which metric or log proves the current state, who owns remediation, and when a change should be rolled back instead of patched live.

Official wording and source

Create External Table As Select, a pattern for writing query results to external storage. Microsoft Learn places it in CREATE EXTERNAL TABLE AS SELECT in Synapse SQL; operators confirm scope, configuration, dependencies, and production impact. Use the linked source for exact Azure behavior.

Open Microsoft Learn

Technical context

Technically, CETAS appears as a Transact-SQL CREATE EXTERNAL TABLE AS SELECT statement using an external data source, external file format, target location, and query body. Engineers verify it through SQL text, output folder, external table metadata, credential, data source, file format, storage ACLs, query duration, bytes scanned, and output file count. Important configuration includes database scoped credentials, managed identity or SAS access, LOCATION values, file format, compression, schema, partitioning expectations, and cleanup policy. It often interacts with Azure Synapse Analytics, Azure Data Lake Storage Gen2, Blob Storage, PolyBase-style external objects, Data Factory, Power BI, and lakehouse pipelines.

Exam context

Compare with

Serverless SQL pool External data source

Where it is used

Where you see it

  1. In the Azure portal, CETAS appears near Synapse SQL scripts, where operators confirm scope, owner, diagnostics, access, and production state before release or incident response.
  2. In CLI, ARM, SDK, REST, or Bicep output, CETAS appears as external table definitions and LOCATION paths, giving teams repeatable release and audit evidence during deployments and incidents.
  3. In logs, metrics, tickets, or reviews, CETAS appears beside exported files, schema drift, row counts, and storage costs, linking symptoms to security, reliability, cost, and performance decisions quickly.

Common situations

  • Plan how data moves from source systems into curated reporting or AI datasets.
  • Troubleshoot failed pipeline runs, permissions, integration runtimes, or data movement bottlenecks.
  • Separate batch, streaming, lake, warehouse, and notebook responsibilities.
  • Document data ownership, lineage, and operational recovery expectations.

Illustrative Azure scenarios

These examples show how the concept can affect design and operations. They are illustrative scenarios, not customer claims.

Scenario 01 Claims data lake extract Scenario, objectives, solution, measured impact, and takeaway.
Scenario

Greenfield Mutual, an insurance carrier, needed repeatable extracts from Synapse serverless SQL into curated ADLS Gen2 folders for actuarial reporting.

Goals
  • Reduce repeated lake scans for monthly reports
  • Create governed external table metadata
  • Keep sensitive claim data in approved storage
  • Cut report preparation below six hours
Approach using CETAS

Data engineers used CETAS to materialize filtered claim results into a controlled ADLS Gen2 folder with an approved external data source and Parquet file format. Managed identity access was limited to the reporting container, and the script wrote to date-stamped locations to avoid overwriting prior extracts. Synapse pipeline activities ran the CETAS statement after source validation, then captured query IDs, bytes processed, row counts, and output paths. Purview lineage and storage lifecycle rules documented retention. Analysts queried the external table instead of scanning raw folders repeatedly. The runbook also captured dashboard links, owner contacts, rollback criteria, and monthly review steps so operators could verify the design without tribal knowledge during incidents or release windows. Evidence from each change was saved with the deployment record for audit, support, and future capacity planning.

Potential outcomes
  • Monthly report preparation dropped from 18 hours to five
  • Serverless query bytes scanned fell by 71 percent
  • No exports landed outside approved containers
  • External table metadata improved analyst self-service
What to learn

CETAS is powerful when query output, storage location, credential, schema, and retention are governed together.

Scenario 02 Retail inventory snapshot Scenario, objectives, solution, measured impact, and takeaway.
Scenario

Contoso Outdoor, a national retailer, needed daily inventory snapshots from semi-structured lake data for regional replenishment dashboards.

Goals
  • Publish daily snapshots by 6 a.m. local time
  • Avoid repeated OPENROWSET scans by analysts
  • Keep snapshot schema stable for Power BI
  • Reduce storage waste from old extracts
Approach using CETAS

The analytics team created a CETAS pattern in Synapse SQL that selected validated inventory records, wrote compressed Parquet output to a date-partitioned ADLS Gen2 path, and created external table metadata for dashboard refreshes. Database scoped credentials used the workspace managed identity, and pipeline parameters generated unique LOCATION values for each business date. Data quality checks compared source row counts with external table counts before notifying regional planners. Lifecycle rules expired obsolete snapshots after 90 days, and query monitoring recorded bytes scanned for cost reviews. The rollout plan included before-and-after measurements, escalation contacts, exception rules, and a control review with finance, security, and application owners. That evidence made the improvement repeatable and gave support teams a clear baseline when later incidents raised similar symptoms.

Potential outcomes
  • Dashboard refresh completed 43 percent faster
  • Analyst ad hoc scans against raw files dropped by 60 percent
  • Inventory snapshot storage stayed within the approved retention budget
  • Regional planners received reports before opening every day
What to learn

CETAS turns expensive lake queries into reusable external tables when storage paths and lifecycle rules are explicit.

Scenario 03 Public sector open data export Scenario, objectives, solution, measured impact, and takeaway.
Scenario

CivicData Works, a public sector analytics team, needed to publish cleaned transit performance data without exposing raw operational files.

Goals
  • Export public-ready data weekly
  • Separate raw and published storage zones
  • Preserve query lineage for transparency
  • Reduce manual file preparation effort
Approach using CETAS

Architects used CETAS in a Synapse serverless SQL pool to select approved columns, aggregate trip metrics, and export results into a public publishing container. The SQL scripts referenced a dedicated external data source and file format, while managed identity permissions prevented access to raw incident folders. Each weekly export wrote to a versioned path with metadata describing the source query, publish date, and reviewer. A Data Factory pipeline triggered the CETAS job, validated file counts, and opened an approval task before website publication. Monitoring alerted if the external table creation failed or row counts changed unexpectedly. Release evidence included configuration snapshots, CLI output, representative metrics, and ticket notes, giving operations a durable baseline for training, audits, and later optimization work. The team also defined when to scale back, rotate credentials, or open a vendor support case.

Potential outcomes
  • Manual export preparation fell from two days to three hours
  • Raw operational files remained inaccessible to the publishing job
  • Public dataset lineage was documented for every weekly release
  • Failed exports were detected before website publication
What to learn

CETAS helps publish governed lake data when the export query, storage boundary, and approval evidence are clear.

Azure CLI

Use CLI, REST, SDK, or scripted queries for CETAS because SQL, storage, identity, and output evidence must be reproducible when validating exported lake data or troubleshooting query cost and to avoid portal-only evidence during reviews.

Useful for

  • Verify that external data sources and credentials exist before running a CETAS script.
  • Capture output folder, row counts, and query IDs after an export job.
  • Compare generated external table metadata with the approved lake schema.

Before you run a command

  • Confirm the active tenant, subscription, resource group, workspace, account, or region before running commands.
  • Use least-privileged access and avoid storing secrets, prompts, certificates, tokens, or personal data in command output.
  • Know whether the command is read-only, mutating, cost-impacting, security-impacting, or destructive before production use.

What the output tells you

  • Output confirms whether the live Azure configuration exists at the expected scope and matches the approved design.
  • Returned IDs, settings, metrics, timestamps, or logs help separate configuration drift from application behavior.
  • Differences between expected and actual state create evidence for rollback, escalation, audit, or owner follow-up.

Mapped commands

Synapse operations

direct 
az synapse workspace list --resource-group <resource-group>
az synapse workspacediscoverAnalytics
read-only Microsoft Learn 
az synapse workspace show --name <workspace-name> --resource-group <resource-group>
az synapse workspacediscoverAnalytics
read-only Microsoft Learn 
az synapse sql pool list --workspace-name <workspace-name> --resource-group <resource-group>
az synapse sql pooldiscoverAnalytics
read-only Microsoft Learn 
az synapse spark pool list --workspace-name <workspace-name> --resource-group <resource-group>
az synapse spark pooldiscoverAnalytics
read-only Microsoft Learn 
az synapse workspace show --name <workspace> --resource-group <resource-group>
az synapse workspacediscoverAnalytics
read-only Microsoft Learn 
az synapse workspace create --name <workspace> --resource-group <resource-group> --storage-account <storage-account> --file-system <filesystem> --sql-admin-login-user <user> --sql-admin-login-password <password>
az synapse workspaceprovisionAnalytics
cost-impacting Microsoft Learn 
az synapse sql pool list --workspace-name <workspace> --resource-group <resource-group>
az synapse sql pooldiscoverAnalytics
read-only Microsoft Learn 
az synapse spark pool list --workspace-name <workspace> --resource-group <resource-group>
az synapse spark pooldiscoverAnalytics
read-only Microsoft Learn

Architecture context

CETAS sits in the analytics architecture as a bridge between SQL query processing and lake storage. In Synapse SQL, CREATE EXTERNAL TABLE AS SELECT materializes a query result into external files while registering table metadata for later reads. Architects use it to stage transformed data, reduce repeated scans, support downstream reporting, or create reusable lake outputs from serverless or dedicated SQL patterns. The design review must include external data source, database scoped credential, managed identity or SAS access, file format, folder naming, overwrite assumptions, schema, partition strategy, and cleanup policy. Poorly governed CETAS jobs create duplicate data, insecure storage paths, expensive scans, and confusing lineage for Data Factory, Power BI, or lakehouse consumers.

Security
Security for CETAS starts with understanding which identities, secrets, certificates, endpoints, data stores, or management-plane permissions it touches. Review who can view, change, or use it, and confirm that production access follows least privilege. Check whether private networking, firewall rules, RBAC, key vault storage, managed identity, audit logs, and data classification apply. Operators should avoid exposing tokens, connection strings, prompts, certificate material, or cost-sensitive business metadata in troubleshooting output. A secure design also documents emergency access, rotation responsibilities, and evidence retention so an incident response team can prove the current configuration without inventing access during an outage. Security reviewers should confirm least privilege, private access paths, and audit retention before approving production use.
Cost
Cost for CETAS comes from the resources, transactions, data movement, retention, compute, capacity, tokens, or operational labor it influences. Some costs are direct meters, while others appear as extra storage, higher throughput, duplicate processing, export jobs, monitoring ingestion, or engineering time. Review budgets, cost allocation, tags, usage metrics, and SKU limits before scaling or enabling new behavior. The safest approach is to define the owner, expected usage pattern, and alert thresholds up front. That way finance conversations use evidence instead of opinions after the bill arrives. Finance and engineering teams should agree which metric proves usage and which scope owns remediation.
Reliability
Reliability for CETAS depends on whether the design behaves predictably during scale events, regional incidents, expired credentials, throttling, schema changes, or downstream failures. Identify the dependency chain, expected failure mode, and recovery target before production use. Monitor the signals that show backlog, lag, retries, health state, capacity saturation, authentication failures, or stale data. Test restore, rotation, failover, replay, or rollback paths where they apply. Operators need a runbook that separates platform configuration problems from application defects and says which evidence is required before escalating to networking, identity, database, or product teams. Runbooks should state the first observable symptom, safe rollback path, and owner escalation route.
Performance
Performance for CETAS is about how quickly and consistently the related workload can complete useful work. Measure the right signals: latency, throughput, backlog, request units, token volume, CPU, memory, bytes scanned, file counts, retries, or throttled operations depending on the service. Avoid tuning one setting in isolation when partitions, replicas, keys, network paths, identity calls, downstream services, or client behavior may be the real bottleneck. Performance reviews should compare expected workload shape with live metrics and include a safe test plan before increasing capacity or changing production configuration. Load tests should compare expected throughput, latency, queue depth, and saturation signals against live limits.
Operations
Operationally, CETAS needs ownership, naming, tagging, change records, and repeatable verification. Teams should know where it appears in the portal, which commands or queries prove state, which dashboards show health, and which settings are safe to change during business hours. Keep examples, approvals, and rollback notes with the service runbook rather than in personal notes. For production changes, capture current configuration before and after the work, including resource IDs, region, owner, timestamp, and related deployment. Good operations turn the term into a checklist that first responders can follow under pressure. Operational evidence should include timestamps, resource IDs, owner names, and links to the approved change record.

Common mistakes

  • Running CETAS into a reused folder without a cleanup or versioning convention.
  • Ignoring storage permissions until the SQL query succeeds but export fails.
  • Treating CETAS output as governed data without retention, lineage, or schema review.