Integration Of Operational Databases To Data Lake

Andi Lamprecht ·2026-05-11· 7 min read· Accepted

Metadata

Context and Problem Statement

The UTM platform manages operational (OLTP) data, including airspaces, flight plans, and operational intents within transactional GCP Cloud SQL databases. This data is currently siloed, preventing its use for critical regulatory reporting requirements.

The primary driver is ASTM F3548-21 SYS-910 (ACM0005), which mandates monthly aggregate operator conformance monitoring.

Failure to automate this data flow results in:

• Reliance on limited, incomplete APIs
• Frequent “break-glass” production database requests
• Inability to perform the complex, multi-source joins required for aviation safety analytics

We need a solution that enforces regulatory thresholds while protecting the safety-critical performance of the production environment.

Decision Drivers

Decision Outcome

Considered Options

Option 1: No Data Movement - BigQuery Federated Queries (External Connection) - Choosen

Enabling query source data directly from BigQuery without physically moving or duplicating data.

Pros

1. Zero Data Latency - Queries reflect the current state of Cloud SQL in real-time—no synchronization delays or stale data concerns. Analysts always work with the most up-to-date information.
2. No Storage Duplication - Eliminates the need to replicate and store data in BigQuery, reducing storage costs and avoiding data drift between source and analytical environments.
3. Data Governance - Limits the data retention requirements to Cloud SQL.
4. Simplified Architecture - No ETL pipelines to build, maintain, monitor, or troubleshoot. Reduces operational burden and accelerates time-to-insight for analysts.
5. Reduced Development Effort - No DAG development, schema mapping, or incremental load logic required. Analysts can begin querying immediately once the external connection is established.
6. Single Source of Truth - Data is never duplicated, eliminating reconciliation issues and ensuring consistency between operational and analytical views.
7. Flexible Query Patterns - Supports ad-hoc exploratory analysis without pre-defining extraction scope. Analysts can access any table or column as business questions evolve.
8. Lower Total Cost of Ownership - No Cloud Composer compute costs for extraction jobs, no BigQuery storage costs for replicated data, and minimal engineering maintenance overhead.
9. Security - No need to save DB credentials, instead we create a BigQuery connection, which creates a service agent, providing Cloud SQL access to this service agent and sharing this agent to end users ensure highest security. Since its managed service, it takes care of secure networking. By configuring connection timeout, statement timeout, worker memory etc to safe guard the database against the resource consumption issues.

Cons

1. Source DB Load - Schedule heavy analytical workloads during off-peak hours; implement query complexity guidelines for analysts
2. Data Governance - Need to monitor the queries ran by the external connections.
3. Limitation - There are known limitations with this approach
4. Though there is no data copy, BQ can maintain the transformed copy of cloud sql data.

Option 2: Scheduled Batching using Cloud Composer

GCP Cloud Composer, a fully managed Apache Airflow based workflow orchestration service, allows to batch load Cloud SQL to BigQuery.

Pros

1. Low Operational Complexity - Data team already has production ready Cloud Composer Environment.
2. BigQuery Compute Power - As data presents in Big Query, we can leverage on BigQuery’s massively parallel, serverless architecture to do complex analytical work, without strain Cloud SQL.
3. Governance & Data Quality Gates - Pipeline orchestration enables enforcement of data quality checks, PII masking, schema validation, and access controls before data reaches analysts. Ensures compliance and data integrity at every stage.
4. Cost Effective at Scale - BigQuery’s storage costs are minimal compared to compute-heavy alternatives. Batch processing is significantly more cost-efficient than streaming for current data volumes and refresh requirements.

Cons

1. Introduces schema coupling - changes to source schema require corresponding DAG updates.
2. Requires monitoring to prevent query overlap with peak operational hours
3. Upstream Database Support - Upstream Databases should support incremental data loading by providing information on what got changes during the batch executions.
4. Data Staleness - Because Composer is batch-based, data in BigQuery is only as fresh as the last successful DAG run.

When using Cloud Composer to move data to BigQuery, we can choose between a Direct DB approach and a API based approach.

Option 3: Streaming Data from UTM Cloud SQL to BigQuery

Options Considered

1. GCP Datastream – GCP managed change data capture (CDC) service, which is scalable, managed and handles schema changes (non breaking)
2. GCP Dataflow - GCP managed Unified bath and stream processing services, Full transformation control, handles business logic
3. GCP Dataproc - GCP managed Cluster for Data Processing (Apache Flink)
4. Open-source CDC – Debezium with Kafka-based implementation for change data capture

Given the lack of compelling use cases requiring streaming data, the operational burden, and the disproportionate costs relative to business value, batch-based data replication remains the preferred approach. This decision should be revisited if:

• Near-real-time analytics requirements emerge
• Data freshness SLAs tighten to sub-hourly thresholds
• Downstream systems require event-driven data availability

Safety Impact

1. Safety-Critical Data Flow: No. Ingestion is strictly one-way (Production → Lake).
2. Operational Risk: High. Direct Primary DB access could cause resource exhaustion. Mitigated by strict off-peak scheduling and CCI-controlled throttled queries.
3. Fault Tolerance: Ingestion failure delays reporting but does not compromise active flight safety.

Assumptions & Constraints

• UTM Currently has airspace data, this can be better loaded to Data Lake by subscribing to Atlas Pipelines.
• If UTM data grows Storing all historic data is Big Query is not required, Data will be moved to tiered storage.
• Assumption: The Data Team will maintain SQL views that mirror application-level governance and FAA requirements.
• Constraint [Operational]: No batch jobs or heavy analytical extractions are permitted during known high-density flight windows to protect the Primary DB.
• Constraint [Technical]: Direct database access is limited to read-only permissions; no DML (Data Manipulation) or DDL (Data Definition) operations are permitted on the source Production DB.

Related Requirements

• ASTM F3548-21 SYS-910: Mandatory Conformance Monitoring (ACM0005).
• ASTM F3548-21 GEN0200: 1080-hour retention limit for external data.
• FAA NTAP / USP Agreement Section 5(a): 45-day minimum retention for cross-provider data.

Implications

• PE Action: Provision required IAM permissions and set up database performance alerts.

  • If a new DB credentail is generated, it should be created with

    • connection timeout, statement timeout, worker memory etc to safe guard the database against the resource consumption issues.

• Technical Debt: A follow-on ADR is required to evaluate

  • either stream loading or batch loading data, based on the additional reporting requirements
  • adding a Read Replica as flight volume scales.

• Monitoring: Establish “Freshness Alerts” for regulatory reporting timelines.

• Release Gate: App and Data teams must implement a Synchronized Release Gate for all schema changes.

Notes

References

1. BigQuery
2. Datastream
3. Cloud Composer
4. Federated Queries