Designing a Real‑Time Data Warehouse Backend with Flink and Paimon for SMBs

Overall Architecture

The system follows a simple front‑end/back‑end separation; the computation engine (Flink) and the management layer (Spring Boot) are isolated, each handling its own responsibilities. No micro‑service decomposition or message‑queue decoupling is used— a monolithic Spring Boot application suffices for this scale.

Backend Service Layer Refactoring

Early versions injected save() / findAll() directly in Controllers, leading to scattered data‑access code, manual timestamp handling, and no transaction management. The solution was to move all data operations to dedicated Service classes, one per business module: SyncTaskService: task CRUD and lifecycle management FlinkClusterService: encapsulates all Flink REST API and SQL Gateway interactions DwhMetaService: Paimon metadata sync and table maintenance QueryService: ad‑hoc SQL execution with whitelist validation QualityCheckService: quality‑check execution and alarm linkage AlertNotifyService: DingTalk / WeChat / Email notifications ReportService: report templates and data queries DatasourceService: datasource management, connection testing, password encryption

After the refactor, Controllers only handle request routing, while @Transactional is applied uniformly in Services, SQL‑injection protection and password encryption become cross‑cutting concerns, and Service logic can be unit‑tested without starting a web container.

Core CDC Task Flow

Register Data Source

In the "Data Source Management" UI, add MySQL (source) and Paimon (target) connections. Passwords are encrypted with AES‑256‑GCM before storage; the encryption key is injected via environment variables.

Create Task

Select task type (CDC sync), source and target datasources, and define table mapping, e.g. shop.orders → ods.ods_orders. Set parallelism and checkpoint interval, then insert a record into the sync_task table with initial status draft.

Start Task

If the CDC connector JAR is not yet uploaded, call Flink REST /jars/upload to upload it.

Submit the job via POST /jars/{jarId}/run with task configuration.

Flink returns a jobId; the backend updates the task status to submitting.

A Quartz job polls the Flink cluster every 30 seconds until the job reaches running, then updates the management database.

Note: /jobs/overview may not list the newly submitted job immediately, so a short wait‑and‑retry loop is required.

Runtime Monitoring

Periodically pull job status, checkpoint info, source lag, and throughput from Flink REST API and write them to the sync_task record.

The front‑end dashboard displays these metrics in real time.

If the job status becomes FAILED, the AlertNotifyService automatically sends an alarm.

Pause & Resume

When a user clicks "Pause":

Call POST /jobs/{jobId}/stop to trigger a savepoint.

The task status changes to saving_point.

Poll the savepoint until it completes (may take seconds to minutes for large state).

Once completed, set status to paused and store the savepoint path.

Resuming reads the saved path and re‑submits the Flink job, guaranteeing exactly‑once processing.

Dual Flink Submission Modes

JAR Mode

Used for Flink 1.x and CDC scenarios. The backend uploads the JAR to the Flink cluster and runs it via /jars/{jarId}/run. CDC connectors currently lack a pure‑SQL submission path.

SQL Gateway Mode

For Flink 2.x pure‑SQL use cases. The backend connects to the SQL Gateway via the HiveServer2 protocol, creates a session, submits SQL, and polls for a jobId. No JAR upload is needed; this mode is ideal for ETL transformations and materialized views.

Both modes converge on FlinkClusterService, which provides a unified interface to SyncTaskService, abstracting away the underlying submission differences.

Metadata Synchronization Mechanism

Paimon stores its catalog metadata in an external Metastore (MySQL in this implementation). The synchronization process:

JDBC connects to the Paimon Metastore instance.

Read system tables paimon_catalog_{key}_database, paimon_catalog_{key}_table, and paimon_catalog_{key}_table_column to extract all database, table, and column information.

Infer layer prefixes (e.g., ods_* → ODS, dwd_* → DWD) to categorize tables.

Write the extracted metadata into management tables dwh_table_meta / dwh_column_meta.

Apply an upsert using the composite key (paimonDb, paimonTable) to ensure idempotent writes; updates modify existing rows, inserts add new ones, and tables removed from Paimon are cleaned up.

This keeps a fresh index of all Paimon tables in the management database, allowing the front‑end to query metadata without hitting the Paimon Metastore, thus improving response speed.

Note: Paimon Metastore table names follow the pattern paimon_catalog_{catalogKey}_table, where catalogKey is the catalog name defined in the Paimon configuration (default rtdwh). Changing the catalog name requires updating the sync logic accordingly.

Security System

SQL Injection Protection

Statement whitelist: only SELECT, SHOW, DESCRIBE, EXPLAIN, WITH are allowed.

Keyword blacklist: DROP, DELETE, TRUNCATE, ALTER, GRANT are rejected outright.

Table and column names in quality checks are validated against a regex allowing only letters, digits, underscores, and dots.

Absolute safety is unattainable, but layered defenses dramatically lower accidental misuse and raise the attack barrier for internal tools.

Credential Security

Datasource passwords are stored encrypted with AES‑256‑GCM; the key is supplied via environment variables.

Stateless JWT authentication with configurable token expiry.

Actuator health‑check endpoints do not expose database connection details.

Authentication & Authorization

All APIs require an Authorization: Bearer <token> header.

Roles: ADMIN, DEVELOPER, VISITOR, with fine‑grained permissions such as task:create and task:view.

Front‑end route guards filter menus and pages based on the user’s role.

The permission model is simple yet sufficient for small‑to‑medium teams; it can be extended for project‑level isolation if needed.

The next article will discuss deployment, practical scenarios, and daily operational procedures.