Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Federated queries in Fabric HorizonDB use pg_fabric_fdw to read supported warehouses and SQL analytics endpoints with PostgreSQL SQL. This article describes the foreign-table metadata, query behavior, and session controls for this Federated Analytics capability.
Important
Fabric HorizonDB is in public preview. Use the service-managed extension and connection configuration supplied for your database. Instructions that install the extension, require PostgreSQL superuser access, or replace a managed foreign server don't apply to the managed service.
For a procedure, see Query Fabric data from a HorizonDB database. For errors and recovery steps, see Troubleshoot federated queries.
Connection and metadata options
The managed foreign server has two connection options:
| Option | Meaning |
|---|---|
endpoint |
The bare Fabric host name, without a port or tcp: prefix. |
database |
The default warehouse or SQL analytics endpoint database. |
The wrapper uses a native Tabular Data Stream (TDS) connection, not an Open Database Connectivity (ODBC) data source name (DSN) or connection string. Connect as the database creator and confirm Microsoft Entra access to the source. You don't pass a token in SQL. Don't change service-managed servers or user mappings.
The default fabric_dw server targets the database's own mirrored SQL analytics endpoint. For a separate warehouse, create a customer-owned server with CREATE SERVER ... FOREIGN DATA WRAPPER pg_fabric_fdw OPTIONS (endpoint '...', database '...'). Copy the host and SQL database name from the warehouse's Settings > Connection strings. The SQL database name can differ from the Fabric item display name. See Register a separate warehouse source.
Import options
IMPORT FOREIGN SCHEMA reads the source catalog and creates local metadata. The local schema must already exist. Use LIMIT TO to select tables or EXCEPT to exclude them.
| Option | Meaning |
|---|---|
database_name |
Selects another queryable SQL database reachable through the selected Fabric connection instead of its default database. It doesn't discover arbitrary workspaces. |
mirror_schema |
Associates each imported table with a same-named local statistics source by setting its mirror_of option. The source must meet the requirements in Local planner statistics. |
For example, import one table from another database in the workspace:
CREATE SCHEMA IF NOT EXISTS fabric_sales;
IMPORT FOREIGN SCHEMA analytics
LIMIT TO (customer_signals)
FROM SERVER fabric_dw
INTO fabric_sales
OPTIONS (database_name 'sales_analytics');
Replace the names with your source and managed server. The option doesn't grant access to the other database or discover arbitrary workspaces. A different endpoint can require a separate customer-owned server.
Import filters match exact names. Quote a mixed-case name, such as "CustomerSignals". A missing schema, insufficient catalog visibility, an empty selection, or an unsupported remote type can fail the import.
Foreign-table and column options
Use a manual definition when you need a subset of columns or a different local name:
CREATE FOREIGN TABLE fabric_sales.customer_signal_scores (
customer_id bigint,
score numeric(5, 2) OPTIONS (column_name 'risk_score')
)
SERVER fabric_dw
OPTIONS (
database_name 'sales_analytics',
schema_name 'analytics',
table_name 'customer_signals'
);
This example assumes the source has those columns and matching types. Verify the source definition instead of inferring a type from sample values.
| Option | Scope | Meaning |
|---|---|---|
schema_name |
Foreign table; required | The remote schema. |
table_name |
Foreign table; required | The remote table. |
database_name |
Foreign table; optional | Another queryable database reachable through the selected Fabric connection. |
mirror_of |
Foreign table; optional | A schema-qualified local table or materialized view used only for statistics. |
column_name |
Column; optional | The exact remote column name. Without it, the local name is used. |
Imported columns retain their remote names through column_name. Set this option before renaming a manually defined column that doesn't already have it. Otherwise, a local rename changes which remote column the wrapper requests. A difference between local and remote column names prevents full-query pushdown, although eligible parts can still run remotely.
Changing or dropping customer-owned foreign-table metadata doesn't change the remote table or its rows.
Data type mappings
Prefer IMPORT FOREIGN SCHEMA to obtain the source's type mapping.
| Fabric column type | PostgreSQL column type |
|---|---|
bit |
boolean |
tinyint, smallint |
smallint |
int |
integer |
bigint |
bigint |
real |
real |
float |
double precision |
decimal(p,s), numeric(p,s) |
numeric(p,s) |
date |
date |
time(p) |
time |
datetime2(p) |
timestamp |
datetimeoffset(p) |
timestamptz |
char(n), nchar(n) |
char(n) |
varchar(n), nvarchar(n) |
varchar(n) |
varchar(max) |
text |
varbinary(n), varbinary(max) |
bytea |
The wrapper also supports UUID columns. Use the imported mapping or verify the source representation before declaring a uuid column. A binary column isn't necessarily a UUID.
These boundaries affect imports and expressions:
- Remote
time,datetime2, anddatetimeoffsetcolumns support at most six fractional-second digits. Import rejects a remote precision of seven. - Fabric exact numeric calculations support up to 38 digits. Wider PostgreSQL numeric definitions can't participate in pushed-down expressions.
- Unmapped remote column types fail import. Fetch JSON stored as string data as
text, and apply PostgreSQL JSON functions locally. - UUID casts run in PostgreSQL because the engines accept different string representations. UUID equality can still push down.
- Timezone-dependent expressions, such as
date::timestamptz, run locally to preserve PostgreSQL timezone rules. - Out-of-range date literals, BC dates, infinity, and
TIME '24:00:00'stay local. Dates constructed remotely can still fail if their calculated value falls outside Fabric's supported range.
Query pushdown
The wrapper translates eligible PostgreSQL operations into T-SQL. Fabric evaluates those operations and returns the result rows. Other operations can run in PostgreSQL over the returned rows, or the query can fail if it can't be translated safely.
| Query shape | Behavior |
|---|---|
Projection, filters, ordering, LIMIT, and OFFSET |
Can run in Fabric when the expressions are supported. |
| Joins between eligible foreign tables on one server | Can run as one remote join, including across reachable databases when supported by the connection. |
| A join between a local table and a foreign table | Uses local planning and eligible remote scans. A selective local key can parameterize a remote scan; broader inputs can require a local join. |
count, sum, avg, min, and max |
Can run in Fabric. count uses COUNT_BIG; integer inputs to sum are widened where required. |
Supported window functions, such as row_number, rank, lag, and lead |
Push down when the entire query runs in Fabric. In a mixed query, they run locally. |
array_agg, json_agg, and jsonb_agg |
Run locally over a remote scan because Fabric has no corresponding result type. |
| PostgreSQL-only functions or unsupported date expressions | Can run locally or produce an unsupported-construct error. Local fallback isn't guaranteed for every expression. |
| Regular-expression predicates and recursive common table expressions | Aren't supported. |
Writes, COPY into a foreign table, FOR UPDATE, and FOR SHARE |
Aren't supported. Foreign tables are read-only. |
Run EXPLAIN (VERBOSE) on the actual query. The Fabric SQL line shows the SQL planned for Fabric. Local aggregate, join, or sort nodes show where PostgreSQL completes the work. A cursor's Materialize node alone doesn't imply local query computation.
By default, EXPLAIN without ANALYZE doesn't contact Fabric. EXPLAIN ANALYZE executes the query.
Remote estimated plans
Enable the remote plan for your session:
SET fabric_fdw.remote_explain = ON;
EXPLAIN now contacts Fabric to obtain an estimated execution plan without executing the remote SQL. The remote identity needs permission to execute the statement and SHOWPLAN permission in every referenced database. Without those permissions, EXPLAIN fails.
The output includes a Fabric Plan tree. Fabric costs use different units from PostgreSQL costs. Even with EXPLAIN ANALYZE, the nested Fabric plan remains estimated rather than providing actual per-operator counters. A parameterized scan can report that its remote plan is skipped because the runtime parameter values aren't available yet.
Restore the default when you finish:
RESET fabric_fdw.remote_explain;
Cross-engine behavior differences
Pushdown doesn't guarantee PostgreSQL-equivalent results for every expression. The engine that performs a comparison or calculation applies its own rules.
| Operation | Difference and guidance |
|---|---|
| Text comparison, ordering, grouping, and joins | Remote operations use the warehouse collation. A local COLLATE declaration doesn't change results already computed by Fabric. Validate case, accents, and sort order. |
| UUID ordering and range predicates | Fabric and PostgreSQL rank UUIDs differently. Equality, IN, and grouping agree. See UUID comparison rules. |
| Average of integers | A pushed integer average can discard the fraction. Use avg(column_name::numeric) when the fractional result is required. |
| Text length with trailing spaces | The remote length function omits trailing spaces. Use length(column_name \|\| '\|') - 1 to count them. |
| Fixed-length character padding | Fabric can retain padding where PostgreSQL trims it during conversion. Use rtrim when padding must be removed. |
Numeric-to-integer casts at .5 |
The rounding rules differ. Use round(value)::integer for half-away-from-zero behavior, and check the target integer range before casting. |
LIKE patterns |
T-SQL treats brackets as pattern syntax and doesn't use PostgreSQL's implicit backslash escape. Use an explicit escape character, such as LIKE 'a#%b' ESCAPE '#' for a literal percent sign. |
| Week and day-of-week calculations | Fabric calendar settings can differ from PostgreSQL ISO-week rules. Validate week, dow, and week truncation against the required business calendar. |
| High-precision calculations | Large exact results can exceed 38 digits. Some power, exponent, and division results use floating-point precision. Don't assume exact PostgreSQL numeric results. |
| An empty grouping set over empty input | Fabric can return no rows where PostgreSQL returns one grand-total row. Test empty inputs explicitly. |
Import can warn that a Fabric text collation isn't verified compatible with the PostgreSQL database collation. The warning appears at most once per import and reports counts rather than affected column names. It isn't proof that the import finished or that a specific comparison will differ. Inspect the source collation and test representative values.
UUID comparison rules
fabric_fdw.uuid_fabric_semantics defaults to ON. It allows Fabric UUID comparisons where the query can push them down. A mixed plan can still sort UUIDs locally, so the resulting order can depend on the plan.
Use PostgreSQL ordering when your application requires consistent PostgreSQL UUID range and sort behavior:
SET fabric_fdw.uuid_fabric_semantics = OFF;
The relevant sort or filter now runs locally. This can increase the number of rows transferred. The setting affects UUIDs only, not text collation.
Session controls
Use the controls exposed by your released extension version and permissions.
| Setting | Default | Effect |
|---|---|---|
fabric_fdw.max_retrieved_rows |
0 |
No cap by default. A positive value rejects a foreign scan if its estimated retrieved rows exceed the limit. This isn't an exact runtime row limit. |
fabric_fdw.max_batch_memory |
256 kB |
A positive value enables result batching. 0 fetches one row at a time. The allowed range is 0 through 1024 kB; the effective memory limit is also bounded by work_mem. Changing this setting requires the applicable parameter SET privilege. |
fabric_fdw.remote_explain |
OFF |
Adds Fabric's estimated execution plan to EXPLAIN and requires remote plan permissions. |
fabric_fdw.enable_query_labels |
OFF |
Adds a unique label to each remote query and reports it in a PostgreSQL NOTICE. |
fabric_fdw.uuid_fabric_semantics |
ON |
Allows Fabric UUID comparison semantics where those operations can push down. |
Set statement_timeout before starting a scan. The wrapper captures it for remote reads when the scan starts; changing it during a scan doesn't change that captured value. Query cleanup can continue after cancellation. For details, see Cancellation and timeouts.
Correlate a query with Fabric history
Enable labels before running a query:
SET fabric_fdw.enable_query_labels = ON;
SELECT count(*)
FROM fabric_mirror.orders;
Record the label from the NOTICE. One PostgreSQL query can issue multiple remote queries, each with a separate label.
Run the following query against the source warehouse, not the HorizonDB database's PostgreSQL endpoint:
SELECT
distributed_statement_id,
label,
start_time
FROM queryinsights.exec_requests_history
WHERE label = '<query-label>';
The label and distributed_statement_id are different identifiers. History can take up to 15 minutes to appear. Access depends on your warehouse permissions. For more information, see Query insights.
Restore the default after collecting the labels:
RESET fabric_fdw.enable_query_labels;
Local planner statistics
The optional mirror_of foreign-table option identifies a permanent local table or materialized view that PostgreSQL samples during ANALYZE. The wrapper doesn't create or refresh this statistics source, and queries still read rows from Fabric.
This option isn't Fabric database mirroring and doesn't synchronize data. Maintain representative local rows through your own approved load process.
The statistics source must:
- Be a permanent table or materialized view, not a temporary table or ordinary view.
- Use a schema-qualified name in
mirror_of. - Match the foreign table's non-dropped columns by count, order, and PostgreSQL type.
- Match declared type modifiers, such as character length or numeric precision and scale.
Column names can differ because matching is positional. IMPORT FOREIGN SCHEMA ... OPTIONS (mirror_schema 'analytics_stats') sets mirror_of for each imported table using the same table name in analytics_stats.
After you populate or refresh a matching statistics source, run ANALYZE on the customer-owned foreign table. Changing the local source doesn't refresh foreign-table metadata or remote data.