What Is Data Federation? How It Works, When It Breaks

This post details the technical aspects of data federation, a pattern for querying across disparate data sources (PostgreSQL, BigQuery, Snowflake) without prior data movement. It explains the middleware layer, schema translation, source-specific connectors (Trino, Athena, BigQuery federated queries, Databricks Lakehouse Federation), and the query execution lifecycle including pushdown optimization. It contrasts federation with ETL/ELT/replication, highlighting trade-offs between freshness and performance. Practical use cases like cross-database analytics, regulated environments, multi-cloud setups, and exploratory work are discussed. Potential failure points such as latency compounding, cross-source join limitations, schema drift, and security coordination challenges are also detailed.