ETL Architecture: asapBI, Trino, Spark, Airflow for data engineers

ETL Architecture: How asapBI Unifies Trino, Spark, and Airflow in One Interface

The asapBI platform offers a streamlined approach to designing ETL processes through a single interface that orchestrates tools like Trino, Spark, and Airflow—without locking you into any vendor. It auto-generates code for routine tasks while allowing manual tweaks for complex ones, and supports local development with seamless syncing.

How asapBI's Unified Interface Works

The core idea behind asapBI is a single control point for assets spread across systems. This ties together data models and ETL processes that would otherwise be siloed. The interface is optional: remove it, and things still run, but setup gets clunkier. The platform's architecture revolves around orchestration, with asapBI as the central hub interacting with external clusters and databases.

Key requirements the system addresses:

• For straightforward processes (like moving data between tables), it uses a drag-and-drop field mapping that handles about 90% of everyday tasks.
• Complex workflows need hand-written code in SQL, Python, Java, Scala, or R.
• Heavy computations run on external clusters like Trino, Spark, or Impala, keeping storage separate from compute.
• All loads are managed from one spot, simplifying monitoring.
• Offline development is supported: work locally without internet, then sync changes.
• Only open-source components are used to avoid vendor lock-in and ease maintenance.

Creating and Configuring Data Transfer Processes (DTPs)

The basic building block of ETL in asapBI is the DTP (Data Transfer Process)—an algorithm that populates one table from others, possibly across different databases. You start by creating a DTP and setting up field mappings via the graphical interface. It handles data movement between DBMS like Greenplum to ClickHouse, using Trino or Spark as the execution engine.

Once configured, deploy the DTP to Airflow orchestrator. asapBI connects to Airflow, which can pass tasks to a Spark cluster. Pick your implementation language: SQL or Python for Spark. Generated code is viewable and editable on the Code tab for full transparency.

Example of auto-generated Python code for Spark:

# Example generated code for DTP in Spark
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("DTP_Example").getOrCreate()
df_source = spark.read.format("jdbc").option("url", "jdbc:postgresql://host/db").option("dbtable", "source_table").load()
df_target = df_source.selectExpr("col1", "col2", "col3")
df_target.write.format("jdbc").option("url", "jdbc:clickhouse://host/db").option("dbtable", "target_table").mode("overwrite").save()

Saving a DTP to Airflow auto-creates a ready-to-run DAG. Launch and debug these DAGs via Airflow's UI or asapBI, which also tracks run logs.

Orchestrating Load Chains and Managing Workflows

Chain DTPs together to form workflows, which become DAGs in Airflow terms. Simple chains are built by adding DTPs to a task tree. For conditionals, loops, or advanced logic, edit the auto-generated DAG code manually.

Airflow's strength lies in running multiple DAGs in parallel, enabling:

• Faster loads by parallelizing independent steps.
• Auto or manual retries for failed tasks without halting the whole chain.
• True process independence.

No orchestrator like Airflow or Dagster? asapBI deploys to a database of your choice, auto-generating a function to run the DTP. Edit that code too for flexibility.

Key Takeaways

• asapBI provides a user-friendly interface for orchestrating ETL with open-source tools, dodging vendor lock-in.
• It auto-generates code for routine tasks but allows manual edits for tricky scenarios.
• Offline development and local testing sync effortlessly.
• Monitor and manage everything from one dashboard.
• No black boxes: always view and tweak generated code.

The Future of ETL and AI's Role in Data Engineering

As AI advances, questions arise about traditional ETL and data models. In theory, store everything in a data lakehouse and let AI analyze directly for insights—no dashboards needed. But asapBI's current focus is automating grunt work like field passthroughs, leaving complex logic to engineers. Until AI handles data processing autonomously, tools like asapBI remain essential for structuring and orchestrating ETL.

— Editorial Team