Federated Postgres join hammering the OLTP primary, plus a partner share with PII

Diagnosis Federation pushes filters and projections down to PostgreSQL only when they are applied directly to the foreign table. Once the foreign table is joined to a 600 GB Delta relation, the optimizer has little ability to derive a Delta-side predicate and push it across the join, so the foreign scan is not constrained to the month in question. Foreign reads also run through a bounded JDBC path, so parallelism is capped and a large scan serialises. The result is a long-running query against an OLTP primary that was never sized for analytics.

Redesign

• Keep every foreign query small and point-shaped: filter on the foreign table itself in a CTE (for example one region at a time), select only the columns needed, and never join the whole foreign table to a large Delta table.
• Materialise the customer dimension nightly into a small Delta table in a governed schema, and join Delta-to-Delta. The dimension is a few thousand rows per region; a full refresh is trivial and removes the OLTP dependency from the analyst path.
• If live reads are genuinely required, point the connection at a read replica with a least-privilege account, set a statement timeout, and serialise concurrent runs.
• Confirm the behaviour in the query profile: if the foreign scan node shows no pushed-down filter, the join is the reason.

Share a curated view, not the raw table. A view gives you row filtering, column projection and derived masking in one object, and for an open-sharing recipient (no Databricks account) a view is the portable way to guarantee the partner never sees the underlying columns.

CREATE VIEW prod.finance.claims_eu_partner AS
SELECT claim_id, claim_month, region, provider_id, amount,
       'XXX-XX-' || right(member_ssn, 4) AS member_ssn_masked
FROM prod.finance.claims
WHERE region = 'EU'
  AND claim_month >= add_months(trunc(current_date(), 'MM'), -12);

The rolling 12-month predicate is evaluated on your side at query time, so the partner cannot widen it. If you prefer to share the table itself, Delta Sharing supports restricting the shared table to a static set of partitions - but that is fixed at share-definition time and won't roll forward, so the view is usually the better fit.

Add the view to a share and grant the recipient access to the share only; no UC grants on the base table are needed. Verify the recipient's connector can read shared views; if it cannot, materialise the same query into a separate curated schema on a schedule and share that table instead.

For legal evidence: column-level grants on the underlying objects, PII tags on member_ssn and patient_name, the share definition under version control, and system.access.audit entries filtered to Delta Sharing to show who queried what and when.

Protecting the primary Enforce a rule that no federated query runs without a filter applied to the foreign table, review connections with the billing team, use a read replica, and add a job-level timeout so a runaway query cannot hold a connection for 47 minutes again. Materialising the dimension is the structural fix; the timeout is the guardrail for when someone forgets.

Detecting misuse

• Monitor system.access.audit for the Delta Sharing service: query counts, bytes read and the recipient identity. Alert on daily volume deviating from the partner's established baseline, or on queries that scan the full shared object repeatedly.
• Confirm the rolling window really is enforced by testing as the recipient: attempt an explicit wide date range and check it returns only in-window rows.
• Set an expiry on the recipient token and rotate it on a schedule, so a leaked credential has a bounded lifetime.
• Combine with system.access.table_lineage when legal asks which downstream objects depend on the PII columns.