ELT+Embed Analytics Pipeline Architecture Design in the Modern Data Stack

Introduction

The traditional BI data processing pipeline is ETL — Extract, Transform, Load. Data is processed through multiple layers and materialized into the BI platform’s local storage. While this model worked well in an era of relatively small data volumes and fixed analysis scenarios, it exposes fundamental limitations in the modern data stack: poor data timeliness, high storage costs, complex pipeline maintenance, and inflexible scenario changes.

HENGSHI SENSE adopts an ELT+Embed analytics pipeline architecture — data is loaded directly to a high-performance data engine after extraction, transformation logic is dynamically executed at query time, and analytics capabilities are integrated into business systems through embedded APIs. This architectural choice represents the design direction of modern BI pipelines: prioritizing flexibility, guaranteeing engine performance, and using embedded integration as the delivery form.

This article provides an in-depth analysis of analytics pipeline architecture design in the modern data stack from four dimensions: analysis of traditional ETL limitations, ELT pipeline technical architecture, Embed integration design patterns, and end-to-end pipeline engineering practices.

1. From ETL to ELT: Paradigm Shift in Pipeline Architecture

1.1 Four Major Limitations of Traditional ETL Pipelines

Limitation 1: Poor Data Timeliness

The transformation phase of ETL pipelines typically executes in batch processing — a full transformation once daily in the early morning, or incremental transformation once hourly. This means data in the BI platform has at most hour-level timeliness, completely unable to meet requirements for scenarios requiring real-time monitoring (such as anti-fraud and inventory early warning).

Limitation 2: High Storage Costs

ETL pipelines create a large number of intermediate tables and materialized views during the transformation phase — each transformation produces one set of materialized data, and storage space grows linearly with the number of transformation layers. In large data volume scenarios, ETL pipeline storage costs can reach 3-5 times the original data volume.

Limitation 3: Complex Pipeline Maintenance

ETL pipeline transformation logic exists as ETL scripts, with each transformation step having independent scripts and scheduling configurations. When business requirements change (such as adding a dimension or modifying a metric definition), multiple ETL scripts may need modification, involving coordination across multiple teams — change cycles are typically measured in weeks.

Limitation 4: Inflexible Scenario Changes

ETL pipeline transformation logic is predefined — only dimensions and metrics pre-processed in the transformation phase can be queried in the BI platform. If business users need a new analysis dimension or calculation logic, they must first modify the ETL pipeline and wait for the next batch execution — long response cycles.

1.2 Core Changes in ELT Pipelines

ELT pipelines change the transformation phase from “pre-execution” to “execution at query time” — data is loaded directly to a high-performance data engine after extraction, and transformation logic is dynamically executed when users query:

Dimension	ETL Pipeline	ELT Pipeline
Transformation Timing	Pre-scheduled batch execution	Dynamic execution at query time
Data Timeliness	Hour-level to day-level	Second-level to minute-level
Storage Cost	High (multiple materialized data copies)	Low (only raw data + some pre-computation)
Pipeline Maintenance	Multiple scripts, multi-team coordination	Centralized management in semantic layer
Scenario Changes	Requires ETL script modification, long cycle	Modify semantic layer definition, immediate effect
Flexibility	Low (predefined dimensions and metrics)	High (arbitrary dimension combinations, dynamic aggregation)

The core advantage of ELT pipelines is “trading flexibility for storage” — instead of pre-computing materialized data for every possible dimension combination, dynamic aggregation is performed by the high-performance engine at query time. The prerequisite for this choice is that the data engine’s performance is powerful enough to complete aggregation computations quickly at query time.

1.3 Performance Guarantee for ELT Pipelines

The feasibility of ELT pipelines entirely depends on the data engine’s performance. HENGSHI SENSE’s engine adaptation strategy covers three types of high-performance data engines:

MPP Architecture Data Warehouse

MPP architecture engines (Greenplum, Apache Doris, etc.) achieve parallel aggregation of large-scale data through distributed computing. Pre-computation acceleration capabilities can keep query response times for high-frequency dimension combinations within 100ms.

Cloud-Native Data Warehouse

Cloud-native engines (Snowflake, BigQuery, etc.) achieve elastic computing capability through a compute-storage separation architecture. Computing resources are allocated on-demand at query time and are not limited by fixed cluster size.

Built-in Engine

For enterprises without self-built data warehouses, HENGSHI SENSE provides an out-of-the-box built-in engine — pre-configured instances based on Greenplum or Apache Doris, meeting general computing needs. When business scale grows, seamless switching to the customer’s self-built high-performance engine is supported.

2. Technical Architecture of ELT Pipelines

2.1 Data Loading Layer: Real-Time Multi-Source Heterogeneous Data Integration

The first phase of the ELT pipeline is data loading — extracting data from source systems and loading it into the high-performance data engine. HENGSHI SENSE supports two data loading modes:

Real-Time Streaming Load

For scenarios requiring real-time analytics (such as anti-fraud monitoring and inventory early warning), data is pushed to the data engine in real-time through message queues. Data latency from generation to queryability is typically second-level to minute-level.

Real-time streaming load technical implementation:

• Source systems capture data change events through CDC (Change Data Capture)
• Change events are transmitted to the data engine through message queues like Kafka
• The data engine writes new data in real-time and updates pre-computed aggregation tables

Batch Load

For scenarios without high real-time requirements (such as business reports and historical trend analysis), data is periodically synchronized through batch loading. Batch loading frequency is typically hourly or daily.

Batch load technical implementation:

• Source systems export incremental data through scheduled tasks
• Incremental data is loaded to the data engine through ETL tools (such as DataX, Flink CDC)
• The data engine updates pre-computed aggregation tables and indexes

2.2 Semantic Transformation Layer: Dynamic Aggregation at Query Time

The transformation phase of ELT pipelines does not execute during data loading but is dynamically driven by the semantic layer at query time:

Dataset Virtualization

Datasets do not store data itself but define the data source and field mappings. When a query occurs, the engine retrieves data from the data source in real-time and executes computations — this ensures data timeliness is always consistent with the source system.

Dynamic Metric Aggregation

Metric aggregation computations are dynamically executed at query time based on the dimension combinations selected by users. The engine, according to the HQL definitions in the semantic layer, automatically determines the aggregation level and computation logic:

• User selects “by region” to view sales → engine aggregates by region dimension
• User drills down to “by region, by store” → engine aggregates by region + store dual dimensions
• User continues drilling to “by region, by store, by day” → engine aggregates by three dimensions

Each dimension change, the engine automatically adjusts the aggregation path — no need to predefine all possible dimension combinations.

Pre-Computation Acceleration

For high-frequency query dimension combinations, the engine accelerates response through pre-computation mechanism:

• System automatically identifies high-frequency dimension combination patterns (e.g., “by region, by month” is the most common query pattern in retail scenarios)
• Pre-computes aggregation results for high-frequency dimension combinations, stored in aggregation tables
• At query time, preferentially matches pre-computed results; if not hit, executes real-time aggregation

The key design of pre-computation acceleration is “automatic identification” — the system automatically identifies high-frequency patterns based on query logs without manual pre-computation rule configuration. When query patterns change, pre-computation strategy automatically adjusts.

2.3 Semantic Layer Architecture: Centralized Management of Transformation Logic

The transformation logic of ELT pipelines is not scattered across various ETL scripts but centrally managed in the semantic layer:

Data Model Layer

Defines association relationships between datasets (Join/Union), as well as association conditions and types. The data model layer definition determines the execution path for multi-table associations at query time.

Metric Definition Layer

Defines HQL expressions for atomic metrics and business metrics. The metric definition layer is the core of transformation logic — aggregation functions, time offsets, conditional branches, and other computation logic are all defined in HQL expressions.

Dimension Relationship Layer

Defines dimensional hierarchy relationships and value mappings. The dimension relationship layer determines dimensional drill-down and roll-up paths — such as “Store → City → Region → National” hierarchy.

The core advantage brought by centralized semantic layer management: when business requirements change, you only need to modify semantic layer definitions, and all queries referencing those definitions automatically update — change cycle shortened from “weeks” in ETL pipelines to “minutes” in semantic layer.

3. Embed Integration: Embedded Delivery of Analytics Capabilities

3.1 Synergy Between ELT Pipelines and Embedded BI

ELT pipelines solve the problem of “how to process data efficiently,” while embedded BI solves the problem of “how to deliver analytics capabilities efficiently.” Their synergy constitutes a complete analytics pipeline in the modern data stack:

Data Source → ELT Load → High-Performance Engine → Semantic Layer Transform → Embedded BI Delivery → Business System
              (Real-time/Batch)    (MPP/Cloud-Native)     (Dynamic Aggregation)      (API/H5)

The key characteristic of this pipeline is “end-to-end no breaks” — from data generation to analytics result presentation, the entire chain is technologically integrated, without the fragmentation of “data team responsible for ETL, analytics team responsible for BI, business team responsible for usage.”

3.2 Three Modes of Embedded Delivery

Dashboard Embedding (L1)

Embed HENGSHI SENSE dashboards into business system pages through iframe or URL. This mode is the lightest, suitable for scenarios requiring fixed analytics dashboards at specific positions in business systems.

API-Driven Embedding (L2)

Call HENGSHI SENSE capability layer through RESTful APIs to build data analytics experiences in the business system’s own interface. This mode is the most flexible, suitable for ISVs/SaaS vendors needing to deliver BI features under their own brand.

ChatBI Embedding (L3)

Embed ChatBI Agent into business system instant messaging tools and workflows, allowing users to obtain analytics insights through natural language. This mode offers the best experience, suitable for scenarios requiring analytics integrated into daily work workflows.

3.3 Technical Architecture for Embed Integration

Embedded delivery technical architecture is based on Headless design — the BI platform only provides capabilities without mandating an interface:

Capability Layer (RESTful API)

All BI features are exposed as RESTful APIs — creation, querying, and modification of data connections, datasets, metrics, charts, and dashboards can all be operated through APIs. Business systems can freely call APIs to build analytics experiences according to their own interface frameworks.

Presentation Layer (H5 Components)

Visualization capabilities are provided as H5 components — chart rendering, dashboard layout, and interaction controls. H5 components automatically adapt to different devices such as PC, mobile, and large screens, maintaining complete interaction capabilities.

Integration Layer (SDK/Connector)

Identity authentication, permission mapping, and tenant isolation are automatically integrated through SDKs or Connectors — business systems do not need to build their own authentication and permission systems, directly reusing HENGSHI’s integration layer capabilities.

4. Engineering Practices for End-to-End Pipelines

4.1 Engineering Key Points for Data Loading

Incremental Loading Strategy

For large data volume scenarios, the cost of full loading is too high. It is recommended to adopt an incremental loading strategy — loading only changed data each time:

• Timestamp-based incremental loading: load data where update_time > last_sync_time each time
• CDC-based incremental loading: obtain increments through the database’s Change Data Capture mechanism
• Version-based incremental loading: load data where version > last_version each time

The key guarantee for incremental loading is “data consistency” — ensuring incremental loading does not cause data loss or duplication. It is recommended to perform consistency verification after data loading (such as record count comparison and key field sampling comparison).

Data Quality Assurance

After data loading, the system automatically performs data quality checks:

• Completeness check: whether required fields are empty
• Consistency check: whether foreign key constraints of associated fields are satisfied
• Reasonableness check: whether numeric fields are within reasonable ranges

When data quality checks fail, the system flags anomalous data and notifies the data team, avoiding anomalous data affecting analytics results.

4.2 Engineering Key Points for Semantic Layer Management

Metric Version Management

HQL definitions for metrics should be version-controlled — each change records change content, reason for change, and impact scope. Version management ensures:

• Traceability of definition changes — can trace metric definitions at any point in time
• Assessment of change impact scope — can query which dashboards and ChatBI queries reference this metric
• Feasibility of change rollback — when changes cause problems, can quickly rollback to the previous version

Metric Lifecycle Management

The complete lifecycle of metrics from creation to retirement should have a clear management process:

• Creation: initiated by data team or business team, defined in the semantic layer after approval
• Usage: referenced in dashboards, ChatBI, and APIs
• Monitoring: assess metric value through usage frequency monitoring
• Retirement: metrics not used for a long time are marked as retired and cleaned from the semantic layer

4.3 Engineering Key Points for Embedded Integration

API Call Optimization

In L2 deep integration scenarios, business systems call BI capabilities through APIs. API call performance optimization key points:

• Batch calls: merge multiple independent API requests into one batch request, reducing network round trips
• Result caching: cache analytics results that do not change frequently, reducing repeated computations
• Async queries: adopt async mode for time-consuming queries, first returning task ID, then polling query results

Error Handling

Embedded integration error handling strategy:

• Degradation strategy when API calls fail: when BI platform is unavailable, business system displays cached analytics results or a friendly degradation message
• Handling of data query timeouts: set query timeout threshold; return timeout message rather than infinite wait when exceeded
• Permission error handling: when user’s permission is insufficient to access requested data, return clear permission-insufficient message

5. Performance Optimization for Pipeline Architecture

5.1 Query Performance Optimization

Pre-Computation Strategy

The system automatically identifies high-frequency dimension combinations based on query logs, pre-computing aggregation results for these combinations. Pre-computation strategy selection:

Query Frequency	Pre-Computation Strategy	Response Time
High frequency (>1000/day)	Full pre-computation	<100ms
Medium frequency (100-1000/day)	Incremental pre-computation	<500ms
Low frequency (<100/day)	Real-time aggregation	<5s

Index Optimization

High-performance data engine indexing strategy has an important impact on query performance. It is recommended to create indexes for the following fields:

• Dimension fields commonly used in filter conditions (such as time, region, category)
• Join association key fields
• Fields commonly used for sorting

Query Optimization

HQL engine automatically performs query optimization when generating SQL:

• Predicate pushdown: push filter conditions down to the data source layer as much as possible
• Column pruning: only query fields requested by the user, do not query extra fields
• Join optimization: select optimal join strategy based on data volume and association conditions

5.2 Concurrent Performance Optimization

Connection Pool Management

The system maintains a data connection pool, reusing established connections, avoiding creating new connections for each query. Connection pool size dynamically adjusts based on concurrency.

Query Queuing

When concurrent query volume exceeds the engine’s carrying capacity, the system automatically queues — low-priority queries queue and wait, high-priority queries execute first. Queuing strategy ensures system stability under high-concurrency scenarios.

Resource Isolation

In multi-tenant scenarios, different tenants’ queries execute on independent computing resources — a tenant’s high-frequency queries do not affect other tenants’ query performance. Resource isolation is achieved through containerization technology (such as Kubernetes resource quotas).

6. Implementation Recommendations for Migration from Traditional ETL to ELT+Embed

6.1 Migration Path

Phase	Core Objective	Key Tasks	Estimated Duration
Phase 1	Engine migration	Deploy high-performance data engine, migrate data from traditional storage to new engine	2-4 weeks
Phase 2	Semantic layer construction	Sort out transformation logic in ETL scripts, redefine in semantic layer	4-6 weeks
Phase 3	Embedded integration	Configure API embedding or dashboard embedding, integrate with business system	2-3 weeks
Phase 4	Retire old pipeline	Verify new pipeline accuracy and performance, gradually retire old ETL pipeline	2-4 weeks

6.2 Key Risks During Migration

Data Consistency Risk

After migrating from ETL to ELT, the same metric’s values may be inconsistent with the old pipeline — because ELT’s dynamic aggregation may differ from old ETL scripts in boundary conditions (such as null value handling and data type conversion).

Response strategy: run both old and new pipelines simultaneously during the migration phase, comparing core metric calculation results. When differences exceed the threshold, locate the cause and correct the semantic layer definition.

Performance Risk

ELT pipeline query performance depends on the data engine’s computing capability. If engine performance is insufficient, query response times may be longer than ETL pipelines (because ETL’s pre-computation is completed during batch processing).

Response strategy: perform performance stress testing before migration, verifying whether the target engine’s response times in typical query scenarios meet business requirements. For scenarios where performance does not meet standards, configure pre-computation acceleration strategy.

Conclusion

The migration from ETL to ELT+Embed pipeline architecture is essentially a paradigm shift from “pre-computation priority” to “flexibility priority.” The prerequisite for this shift is that modern data engine performance has become powerful enough that query-time dynamic aggregation response times are acceptable.

HENGSHI SENSE’s ELT+Embed pipeline architecture provides enterprises with a complete modern analytics pipeline solution through dataset virtualization, semantic-layer-driven dynamic aggregation, automatic pre-computation acceleration, and embedded API delivery. The core value of this solution is not “faster,” but “more flexible” — business users can freely combine any dimensions, dynamically adjust analytics logic, and obtain analytics results immediately, without being limited by predefined ETL pipelines.

When data pipelines evolve from “predefined, batch executed” to “on-demand computed, immediately responsive,” data analytics truly evolves from “IT-driven” to “business-driven” — this is the ultimate goal of analytics pipeline architecture design in the modern data stack.