Querying External Files With PolyBase

The file is in object storage, but the analyst wants to query it beside local tables. PolyBase and external tables can provide that view when file layout and query cost are understood.

Know What a PolyBase External Table Provides

An external table gives SQL Server a schema over data stored elsewhere. The rows remain in files or a remote source. A SELECT can join those rows with local tables, but the storage path, format, and credentials still matter. An external table is not a free copy of the file into the buffer pool.

SQL Server 2025 can query supported CSV, Parquet, and Delta files through native engine capabilities in supported storage, without installing PolyBase services for file queries. PolyBase remains relevant to other external connectors and data virtualization. Check the precise source type and instance configuration before planning installation.

I start with a narrow question: is this an occasional exploration, or a repeated production report? A repeated report can benefit from loading curated data locally. External access is useful, but network and file scans still have to happen somewhere.

SELECT et.name AS external_table_name,
       ds.name AS data_source_name,
       ff.name AS file_format_name
FROM sys.external_tables AS et
JOIN sys.external_data_sources AS ds
  ON ds.data_source_id = et.data_source_id
LEFT JOIN sys.external_file_formats AS ff
  ON ff.file_format_id = et.file_format_id;

Define the Storage and File Format

An external data source identifies the storage location and any required credential. An external file format describes how to interpret the file. CSV requires attention to delimiters, quotes, encoding, and headers. Parquet carries column types, yet you still need a matching table definition.

Keep secrets out of scripts and blog examples. Use a supported credential method for the chosen storage. Give the SQL Server identity only the access needed for the relevant path. A broad storage key copied into a deployment script is a poor shortcut.

I check a small known file before mapping an entire folder. If one column shifts or a date format is wrong, the larger query can fail or misread rows. Test the schema with source documentation and sample values, then version it when the file contract changes.

Declare Columns Deliberately

External table columns should reflect the file’s order and types where required by the format. Do not use a wide nvarchar type for every field just to get a first query running. A deliberate schema lets SQL Server apply filters and conversions predictably, and it makes bad input visible.

A folder can contain files from multiple schema versions. If a query reads them together, the table definition must account for that reality. Separate paths by version or normalize files before querying. One new column in a later file is not a reason to let old reports return confusing values.

I prefer to project only the columns the report needs. SELECT * makes file and network work harder to understand. It also ties consumers to every column in a source that can change without warning. Name the contract in a view when several reports share it.

SELECT OrderId, OrderDate, Amount
FROM dbo.ExternalOrders
WHERE OrderDate >= CONVERT(date, '2025-01-01')
  AND OrderDate < CONVERT(date, '2025-02-01');

Check Whether PolyBase Filters Travel to the Source

A filter in T-SQL does not guarantee that every remote system executes it. Pushdown depends on connector, data type, expression, and plan. Simple comparisons on external columns are easier to push than wrappers around those columns. Keep predicates direct and inspect the actual plan or external work details.

For external database connectors, sys.dm_exec_external_work can show the read command sent outward. File query behavior differs by format and engine capability, so evaluate what was scanned or transferred for that source. Do not claim pushdown from query text alone.

I compare a broad query with a selective one on a safe sample and inspect the work, not just elapsed time. A selective filter that still reads the whole storage path has a different cost profile from one that prunes files or rows. That difference matters before a dashboard runs it every few minutes.

SELECT execution_id, step_index, status, read_command
FROM sys.dm_exec_external_work
WHERE read_command IS NOT NULL;

Organize Files for Pruning

A useful storage layout groups files by a partition key such as date or source region. Keep files large enough for efficient scans but not so large that every selective query must read an enormous object. The right layout follows query filters and load cadence. It should be documented as part of the data contract.

Avoid mixing historical and current schema in one path. New files should arrive through a completion convention so queries do not read partial uploads. A manifest or controlled folder handoff can help when producers write several files for one batch.

I ask which query the file layout is meant to support. If nobody can answer, the storage path is an archive layout, not a reporting design. That can be fine, but expectations should match what the source can deliver.

Decide When to Load Locally Instead of Using PolyBase

External queries are useful for exploration, infrequent joins, and data that is too broad to load indiscriminately. A frequently used, tightly filtered report can justify local staging or a curated table. Local indexes and statistics give you more control over repeated workloads.

Watch remote availability and credentials. A local report can fail because storage access changes, even when SQL Server itself is healthy. Log the source path and file set behind important runs. Reproducibility matters when a file can be replaced after a query.

Do not build a daily production dashboard on an external scan without testing concurrent readers. The first single-user query can look fine while the shared workload strains storage and network. Measure with your own environment before choosing the architecture.

Validate Rows and Costs

Compare row counts and control totals with the producer’s manifest where one exists. Check nulls and conversions in columns used for joins and filters. An external table can expose a file successfully while still interpreting a column incorrectly. Physical access is only the first validation step.

Review the execution plan, external work, storage reads, and SQL Server resource use for representative queries. Keep the filter shape stable while comparing designs. I also check how a missing or malformed file appears to an operator, because that is the problem that arrives outside the demo.

PolyBase and an external table can make a file feel like a table. It does not remove the need to manage file contracts, permissions, and query work. Use the abstraction, then verify what the engine actually reads.

Related reading on this blog: PolyBase Error Msg 46530: External Data Sources Are Not Supported With Type GENERIC and Moving Data Out of SQL Server in Open Formats.

An external table is not local storage, it is a query contract over data that remains elsewhere.

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