The Evolution of Data Analytics: From Batch to Real-time

In today’s fast-paced digital economy, businesses demand insights not just quickly, but in real time. Traditional Extract, Load, Transform (ELT) processes, often relying on batch processing, are struggling to keep up. The rise of the data lakehouse architecture – combining the flexibility of data lakes with the ACID properties and performance of data warehouses – has opened new avenues for real-time analytics. At SoftCrafter, we specialize in building robust data solutions, and we’ve seen firsthand how crucial real-time capabilities are for modern businesses, especially in areas like e-commerce and web development where immediate user feedback and operational insights are paramount.

This article explores a powerful combination for achieving real-time ELT within a lakehouse environment: orchestrating dbt (data build tool) for data transformation and Spark Structured Streaming for continuous data ingestion and processing.

Understanding the Core Components: dbt and Spark Structured Streaming

dbt: The Transformation Powerhouse

dbt has revolutionized the ‘T’ in ELT by empowering data analysts and engineers to transform data in their warehouse using SQL. It brings software engineering best practices – version control, testing, documentation, and modularity – to data transformation. With dbt, you define your data models as SQL queries, and dbt compiles and executes them, managing dependencies and materializations. This declarative approach simplifies complex data pipelines and promotes collaboration, which is a cornerstone of our corporate services at SoftCrafter.

Spark Structured Streaming: Real-time Data Ingestion and Processing

Apache Spark Structured Streaming is a scalable and fault-tolerant stream processing engine built on the Spark SQL engine. It allows you to express continuous computations on streaming data in the same way you would express batch computations on static data. This unified API simplifies development and reduces the cognitive load associated with managing separate batch and streaming codebases. Structured Streaming can ingest data from various sources (Kafka, Kinesis, files, etc.) and write to different sinks (Delta Lake, Parquet, RDBMS, etc.), making it ideal for continuous data loading into a lakehouse.

Architecting the Real-time ELT Pipeline

The synergy between dbt and Spark Structured Streaming lies in their respective strengths. Spark Structured Streaming handles the continuous ingestion and initial transformations (E and L, and potentially some light T), landing data into raw or bronze layers of your lakehouse. dbt then takes over for complex, curated transformations (the heavy T), building out silver and gold layers for analytics. Here’s a conceptual architecture:

  1. Data Sources: Operational databases, event streams (Kafka), APIs, logs.
  2. Ingestion (Spark Structured Streaming): Continuously read data from sources. Perform schema inference, basic cleansing, and append to the raw/bronze layer of the lakehouse (e.g., Delta Lake tables).
  3. Real-time Transformations (Spark Structured Streaming): For immediate insights, Spark Structured Streaming can perform incremental transformations on the bronze layer, creating real-time materialized views or aggregated tables in a silver layer. This can involve joins, aggregations, and filtering.
  4. Batch/Scheduled Transformations (dbt): dbt models are run on a schedule (e.g., hourly, daily) to refine data from the bronze/silver layers into curated gold-layer tables. These transformations can be more complex, involving historical data, deduplication, and business logic that doesn’t require immediate real-time updates. dbt can also manage incremental models to efficiently process only new or changed data.
  5. Data Lakehouse (Delta Lake): Acts as the unified storage layer for all data stages (bronze, silver, gold), providing ACID transactions, schema enforcement, and time travel capabilities.
  6. Analytics & BI: Tools like Tableau, Power BI, or custom applications consume data from the gold layer for dashboards, reports, and machine learning models.

Implementing the Integration: Practical Considerations

Spark Structured Streaming for Ingestion and Initial Processing

Let’s consider a simple example of ingesting data into a Delta Lake table using Spark Structured Streaming:

from pyspark.sql import SparkSession
from pyspark.sql.functions import current_timestamp

spark = SparkSession.builder.appName("StreamToDelta").getOrCreate()

# Read from a Kafka topic
kf_df = spark.readStream 
    .format("kafka") 
    .option("kafka.bootstrap.servers", "localhost:9092") 
    .option("subscribe", "raw_events") 
    .load()

# Deserialize value and add a processing timestamp
parsed_df = kf_df.selectExpr("CAST(value AS STRING)", "timestamp") 
    .withColumn("processing_time", current_timestamp())

# Write to Delta Lake table in append mode
query = parsed_df.writeStream 
    .format("delta") 
    .outputMode("append") 
    .option("checkpointLocation", "/tmp/delta/checkpoint/raw_events") 
    .trigger(processingTime="10 seconds") 
    .toTable("raw_events_delta")

query.awaitTermination()

dbt for Curated Transformations

Once data lands in the raw_events_delta table, dbt can define models to transform it. Here’s a simple dbt model (models/silver/stg_events.sql) that selects and cleans raw events:

SELECT
    CAST(value AS JSON) as event_payload,
    timestamp as event_timestamp,
    processing_time
FROM {{ source('my_lakehouse', 'raw_events_delta') }}
WHERE value IS NOT NULL

And a subsequent gold layer model (models/gold/agg_daily_events.sql) aggregating events:

-- models/gold/agg_daily_events.sql
{{ config(
    materialized='incremental',
    unique_key='event_date'
) }}

SELECT
    CAST(event_timestamp AS DATE) as event_date,
    COUNT(*) as total_events
FROM {{ ref('stg_events') }}
{% if is_incremental() %}
  WHERE event_timestamp > (SELECT MAX(event_date) FROM {{ this }})
{% endif %}
GROUP BY 1
ORDER BY 1

This incremental dbt model efficiently processes only new data since the last run, complementing the continuous streaming ingestion. Orchestrating dbt runs can be done using tools like Apache Airflow or Prefect, ensuring timely updates to your analytical layers. SoftCrafter often leverages these orchestration tools to deliver comprehensive software services.

Benefits of This Integrated Approach

  • Real-time Insights: Achieve near real-time analytics by continuously processing data and making it available for reporting.
  • Data Quality and Governance: dbt’s testing and documentation capabilities, combined with Delta Lake’s schema enforcement, ensure high-quality, trustworthy data.
  • Scalability: Both Spark Structured Streaming and dbt leverage Spark’s distributed processing power, allowing for horizontal scalability to handle massive data volumes.
  • Developer Productivity: SQL-centric dbt and unified streaming/batch APIs in Spark reduce complexity and accelerate development cycles.
  • Cost Efficiency: By processing data incrementally and using open-source technologies, businesses can optimize infrastructure costs.
  • Future-Proof: This architecture is adaptable to evolving data sources and analytical requirements, providing a solid foundation for future growth.

At SoftCrafter, we believe in empowering our clients with cutting-edge solutions that drive business value. Integrating dbt and Spark Structured Streaming into a lakehouse architecture is a prime example of how modern data engineering can deliver tangible benefits, from real-time operational dashboards to advanced machine learning models. If you’re looking to enhance your data capabilities, don’t hesitate to contact us.

#dbt #Spark #StructuredStreaming #DataLakehouse #ELT #RealtimeAnalytics #DataEngineering #DeltaLake

Categorized in:

Data Engineering,

Last Update: October 25, 2026

Tagged in:

, ,