DEV Community: Jose Torreblanca

Building a Modern Analytics Stack: UI Cube.js dbt Athena Integration

Jose Torreblanca — Thu, 21 Aug 2025 20:19:33 +0000

How we built a seamless data pipeline from AWS Athena to React dashboards using dbt and Cube.js

The Challenge: From Raw Events to Dashboard Insights

Picture this: You have millions of ridepooling trip events flowing into AWS Athena, and you need to serve them up as beautiful, interactive dashboards to your stakeholders. The data needs to be:

Fast to query (sub-second response times)
Always fresh (reflecting recent events)
Flexible (various time ranges and filters)
Scalable (handling growing data volumes)

This is the story of how we built a modern analytics stack that connects all these pieces seamlessly.

The Architecture: Four Layers, One Goal

Our stack consists of four key layers, each solving a specific problem:

UI Layer (React) 
    ↕️ 
Semantic Layer (Cube.js)
    ↕️
Transformation Layer (dbt)
    ↕️
Storage Layer (AWS Athena)

Let's walk through each layer and see how they work together.

Layer 1: AWS Athena - The Foundation

At the bottom, we have AWS Athena storing our raw event data in S3 with a multitenant architecture:

-- Raw trip events table
ridepooling__moia__service__trip_entities.ent__trip
ridepooling__tenant_1__service__trip_entities.ent__trip
ridepooling__tenant_2__service__trip_entities.ent__trip

Each tenant gets isolated data storage, but the schema remains consistent. Events flow in continuously from our ridepooling services.

Athena's role:

Serverless SQL engine over S3 data lakes
Cost-effective for large datasets
Handles complex queries with good performance
Integrates natively with AWS ecosystem

Layer 2: dbt - The Transformation Engine

dbt sits on top of Athena, transforming raw events into analytics-ready models:

-- stop_network_usage.sql
{{ config(materialized="view") }}

select
    stops.stop_id,
    stops.service_area_uuid,
    stops.name_en as stop_name_en,
    stops.name_de as stop_name_de,
    date(trip_usage.occurred_at) as trip_date,
    count(distinct trip_usage.trip_id) as daily_trip_count
from
    {{ multitenancy_source("ridepooling", "service__service_area_entities", "att__stop__details_latest") }} stops
left join (
    select link__stop_id__pickup as stop_id, trip_id, occurred_at
    from {{ multitenancy_source("ridepooling", "service__trip_entities", "ent__trip") }}
    where link__stop_id__pickup is not null
    union
    select link__stop_id__delivery as stop_id, trip_id, occurred_at  
    from {{ multitenancy_source("ridepooling", "service__trip_entities", "ent__trip") }}
    where link__stop_id__delivery is not null
) trip_usage on stops.stop_id = trip_usage.stop_id
group by stops.stop_id, stops.service_area_uuid, stops.name_en, stops.name_de, date(trip_usage.occurred_at)

Key dbt features we leverage:

Multitenancy macros: Dynamic table routing per tenant
Jinja templating: Conditional logic and variables
View materialization: Always fresh data without staleness
Modular design: Reusable transformations across tenants

The multitenant magic:

-- This macro resolves to tenant-specific tables at runtime
{{ multitenancy_source("ridepooling", "service__trip_entities", "ent__trip") }}
-- Becomes: ridepooling__moia__service__trip_entities.ent__trip

Layer 3: Cube.js - The Semantic Layer

Cube.js acts as our semantic layer, defining business metrics and providing a consistent API:

// stop-network-usage.js
cube(`StopNetworkUsage`, {
  sql_table: `ridepooling__${COMPILE_CONTEXT.tenant_name}__service__stop_network_data_products__${COMPILE_CONTEXT.env}.stop_network_usage`,

  dimensions: {
    stopId: {
      sql: `stop_id`,
      type: `string`,
      primary_key: true
    },

    stopNameEn: {
      sql: `stop_name_en`,
      type: `string`
    },

    tripDate: {
      sql: `trip_date`,
      type: `time`
    }
  },

  measures: {
    totalTrips: {
      sql: `daily_trip_count`,
      type: `sum`
    },

    avgDailyTrips: {
      sql: `daily_trip_count`,
      type: `avg`
    }
  }
});

Cube.js superpowers:

Semantic definitions: Business users think "total trips," not SQL aggregations
Automatic SQL generation: Converts REST/GraphQL requests to optimized SQL
Caching layer: Sub-second response times for repeated queries
Multi-tenant aware: Same schema definition works across all tenants
Time dimension intelligence: Handles date ranges, comparisons, and time series

Layer 4: React UI - The Experience

Finally, our React dashboard consumes Cube.js APIs to create beautiful visualizations:

// StopUsageDashboard.jsx
import { useCubeQuery } from '@cubejs-client/react';

const StopUsageDashboard = () => {
  const { resultSet, isLoading } = useCubeQuery({
    measures: ['StopNetworkUsage.totalTrips'],
    timeDimensions: [{
      dimension: 'StopNetworkUsage.tripDate',
      granularity: 'day',
      dateRange: 'last 30 days'
    }],
    dimensions: ['StopNetworkUsage.stopNameEn']
  });

  if (isLoading) return <LoadingSpinner />;

  return (
    <div>
      <h2>Stop Usage Over Time</h2>
      <LineChart 
        data={resultSet.chartPivot()} 
        xAxis="time"
        yAxis="StopNetworkUsage.totalTrips"
      />
    </div>
  );
};

UI benefits:

Declarative queries: Describe what you want, not how to get it
Automatic optimization: Cube.js handles SQL generation and caching
Real-time updates: Data stays fresh through view materialization
Rich interactions: Time range pickers, filters, drill-downs work out of the box

The Data Flow: From Event to Insight

Here's how a single trip event becomes a dashboard insight:

Event arrives: Trip booked with pickup stop A and delivery stop B
Athena stores: Raw event in tenant-specific S3 partition
dbt transforms: View aggregates daily usage per stop, handling both pickup and delivery
Cube.js semantics: Converts to business metrics like "total trips per stop"
UI displays: Beautiful chart showing stop usage trends

The magic is that steps 3-5 happen in real-time during user queries, ensuring fresh data.

Key Design Decisions & Trade-offs

✅ Why Views Over Tables?

Always fresh data vs. potential performance hit
Simplicity vs. automation complexity
Perfect for our use case: Daily monitoring with reasonable data volumes

✅ Why Cube.js Over Direct SQL?

Semantic consistency across different front-ends
Automatic optimization and caching
Developer productivity - no more manual SQL writing
Multi-tenant support built-in

✅ Why dbt Over Raw Athena?

Version control for transformations
Testing and documentation built-in
Modularity and reusability across tenants
Software engineering practices for data

✅ Why This Stack?

Serverless: Scales automatically, pay-per-use
Modern: Embraces current best practices
Flexible: Each layer can be swapped independently
Developer-friendly: Code-first approach throughout

Performance Characteristics

Our stack delivers impressive performance:

Query latency: 200-800ms for typical dashboard queries
Data freshness: Real-time (views query source directly)
Scalability: Handles millions of events, hundreds of concurrent users
Cost efficiency: Serverless architecture scales to zero

Performance tips:

Cube.js pre-aggregations for frequently accessed metrics
Athena partition pruning with date filters
Strategic use of dbt incremental models for large fact tables
Smart caching strategies in the UI layer

Operational Considerations

Monitoring & Observability

Cube.js: Built-in query performance metrics
dbt: Model run logs and test results
Athena: CloudWatch metrics for query performance
UI: User experience monitoring and error tracking

Multi-tenant Isolation

Data isolation: Separate databases per tenant
Schema consistency: Same models work across tenants
Runtime flexibility: Switch tenants via variables
Security: Row-level security enforced at storage layer

Development Workflow

dbt development: Local development with sample data
Cube.js iteration: Schema development with dev APIs
UI prototyping: Component development with mock data
Integration testing: End-to-end testing across layers
Deployment: GitOps with environment promotion

Lessons Learned

🎯 Start Simple

We initially over-engineered with complex incremental models and scheduling. Views solved our freshness problem elegantly.

🎯 Semantic Layer is Worth It

Cube.js transformed our development velocity. No more custom SQL endpoints or manual optimization.

🎯 Multi-tenancy From Day One

Planning for multiple tenants upfront saved massive refactoring later.

🎯 Choose Your Materializations Wisely

Not everything needs to be a table. Views, incremental, and tables each have their place.

What's Next?

Our stack continues to evolve:

Real-time streaming: Moving from batch to stream processing
Advanced analytics: ML models integrated into the pipeline
Self-service: Empowering business users to create their own metrics
Global scale: Multi-region deployment strategies

The Bottom Line

Building a modern analytics stack isn't just about picking the right tools—it's about creating a coherent data experience that serves everyone from data engineers to business stakeholders.

Our UI → Cube.js → dbt → Athena stack delivers:

Fast insights for decision makers
Productive development for engineers
Scalable architecture for growing data needs
Maintainable codebase for long-term success

The secret sauce isn't any single technology—it's how they work together to create something greater than the sum of their parts.

What's your experience with modern analytics stacks? Have you found different combinations that work well for your use cases? I'd love to hear about your architecture decisions and trade-offs.

[Boost]

Jose Torreblanca — Thu, 21 Aug 2025 20:06:44 +0000

Jose Torreblanca

Aug 21 '25

When Simple Beats Complex: Solving Stale Data with dbt Views

#dbt #dataengineering #sql #analytics

Comments

3 min read

When Simple Beats Complex: Solving Stale Data with dbt Views

Jose Torreblanca — Thu, 21 Aug 2025 20:05:20 +0000

A story about discovering the right materialization for the right use case

The Problem: "Will My Data Be Stale in 90 Days?"

Picture this: You're building a daily monitoring dashboard for ridepooling stop usage. Your stakeholder asks a simple question: "What happens if I query this data in 90 days? Will I get stale data?"

That question led me down a rabbit hole that taught me more about dbt materializations than any tutorial ever could.

The Discovery: Everything Goes Stale

My initial stop_network_usage model was materialized as a table with a hard-coded 30-day lookback window:

{{ config(materialized="table") }}

select
    stops.stop_id,
    date_trunc('day', trips.occurred_at) as usage_date,
    count(trips.trip_id) as daily_usage
from stops
inner join trips on [...]
where trips.occurred_at >= current_date - interval '30' day

The harsh reality hit: without regular dbt runs, every table in our data warehouse goes stale.

Fresh events flow into source systems continuously
dbt tables only update when manually executed
Queries read stale data until the next dbt run

If I didn't run dbt for 90 days, my dashboard would show data from 120 days ago (90 days since last run + 30-day lookback window). Ouch.

The "Complex" Solution Path

My first instinct was to over-engineer:

Convert to incremental materialization ✓
Add scheduling infrastructure (cron jobs, Airflow, AWS EventBridge)
Implement monitoring and alerting
Add sliding window logic for data retention
Set up error handling and recovery procedures

I started down this path, converting my model to incremental:

{{ config(materialized="incremental", unique_key=["stop_id", "usage_date"]) }}

-- Complex incremental logic with is_incremental() conditions
{% if is_incremental() %}
  and date_trunc('day', trips.occurred_at) > (select max(usage_date) from {{ this }})
{% else %}
  and trips.occurred_at >= current_date - interval '90' day
{% endif %}

While learning about incremental models was valuable (they're genuinely powerful for large datasets), I realized I was building a complex solution for a simple problem.

The Simple Solution: Views to the Rescue

Then it hit me. For my use case, I had a much simpler option: views.

{{ config(materialized="view") }}

select
    stops.stop_id,
    stops.service_area_uuid,
    stops.name_en as stop_name_en,
    date(trip_usage.occurred_at) as trip_date,
    count(distinct trip_usage.trip_id) as daily_trip_count
from stops
left join (
    select link__stop_id__pickup as stop_id, trip_id, occurred_at
    from trips where link__stop_id__pickup is not null
    union
    select link__stop_id__delivery as stop_id, trip_id, occurred_at  
    from trips where link__stop_id__delivery is not null
) trip_usage on stops.stop_id = trip_usage.stop_id
group by [...]

Problem solved. Views query the source data directly every time, so they're always fresh—no automation required.

Views vs. Tables vs. Incremental: Choose Your Fighter

This experience taught me that dbt materializations aren't just technical choices—they're architectural decisions with real business implications:

📊 Views: Always Fresh, Simple

Best for: Small-medium datasets, infrequent queries, freshness > performance
Pros: Always fresh data, no scheduling complexity
Cons: Slower queries, higher compute costs
My use case: ✅ Perfect for daily dashboard monitoring

🗄️ Tables: Fast Performance, Can Go Stale

Best for: Large datasets, frequent queries, acceptable staleness
Pros: Pre-computed results, fast queries
Cons: Requires regular refresh, can go stale
My use case: ❌ Would need complex automation

⚡ Incremental: Best of Both Worlds (When You Need It)

Best for: Very large datasets, regular updates, historical accumulation
Pros: Efficient processing, fast subsequent runs, accumulates history
Cons: Complex logic, still needs scheduling, overkill for simple use cases
My use case: ❌ Over-engineered for my requirements

The Key Insight: Match the Tool to the Problem

The temptation in data engineering is to always reach for the most sophisticated solution. Incremental models are powerful, automated pipelines are impressive, but sometimes the simple answer is the right answer.

My requirements:

Daily UI monitoring ✓
Always fresh data ✓
Reasonable data volumes ✓
Simple aggregations ✓

Views delivered exactly what I needed without any of the operational overhead.

Lessons Learned

Question your assumptions: Not every data model needs to be a table or incremental
Understand the trade-offs: Performance vs. freshness vs. complexity
Size your solution to your problem: Views are underrated for the right use cases
Think about operations: Who will maintain your complex automation?

The Bottom Line

Sometimes the best engineering decision is choosing the simplest solution that meets your requirements. Views might not be glamorous, but they solved my stale data problem elegantly.

The next time someone asks "Will my data be stale in 90 days?", I'll confidently answer: "Not if you use views."

What materialization strategies have you found work best for your use cases? I'd love to hear about times when simple solutions beat complex ones in your data projects.

Understanding Big O Notation: A Beginner's Reflection on Writing Better Programs

Jose Torreblanca — Tue, 17 Dec 2024 09:09:22 +0000

When I started learning about programming, I focused on writing code that simply worked. But as I continued to grow as a developer, I realized that writing efficient programs is just as important as making them functional. One of the tools that helped me gain this understanding is Big O Notation.

In this article, I'll share what I have learned about Big O Notation, why it matters, and how to measure it. I'll focus on the three most common time complexities: O(1), O(n), and O(n²).

What is Big O Notation?

Big O Notation is a way to measure how the performance of an algorithm changes as the size of the input grows. It tells us about the time complexity (how long it takes to run) or space complexity (how much memory it uses).

By understanding Big O, developers can:

Compare the efficiency of different solutions.
Identify bottlenecks in code.
Write scalable programs that perform well even with large inputs.

Think of Big O Notation as a measure of how an algorithm "scales" rather than how fast it runs on your specific machine. It gives a general idea of performance trends.

Key Time Complexities: O(1), O(n), O(n²)

Let’s dive into the three time complexities I have learned so far.

1. O(1) - Constant Time

An algorithm has O(1) time complexity if it always takes the same amount of time to complete, regardless of the input size.

Example:

function getFirstElement(array) {
    return array[0];
}

Here, the function retrieves the first element of an array. No matter how large the array is, this operation happens in constant time.

Why it matters: O(1) operations are the most efficient because their execution time does not increase with the size of the input.

2. O(n) - Linear Time

An algorithm has O(n) time complexity if the execution time grows linearly with the input size.

Example:

function printAllElements(array) {
    for (let i = 0; i < array.length; i++) {
        console.log(array[i]);
    }
}

Here, the function iterates through every element of the array. If the array has 10 elements, the loop runs 10 times. If the array has 1,000 elements, it runs 1,000 times.

Why it matters: O(n) operations scale reasonably well but can become slower with very large inputs.

3. O(n²) - Quadratic Time

An algorithm has O(n²) time complexity if its execution time grows quadratically with the input size. Typically, this happens when we have nested loops.

Example:

function printPairs(array) {
    for (let i = 0; i < array.length; i++) {
        for (let j = 0; j < array.length; j++) {
            console.log(array[i], array[j]);
        }
    }
}

Here, for each element in the outer loop, the inner loop runs through the entire array. If the array has 10 elements, this results in 100 operations (10 * 10).

Why it matters: O(n²) operations can quickly become inefficient as input size grows. For example, with 1,000 elements, this would require 1,000,000 operations!

How to Measure Big O Notation

To determine the Big O of a function:

Count the basic operations in your code, like loops, function calls, and calculations.
Focus on the dominant term — the part of the code that grows the fastest as input size increases.
Ignore constants and smaller terms because they become negligible as the input size grows.

Let’s analyze an example:

function exampleFunction(array) {
    console.log(array[0]);       // O(1)

    for (let i = 0; i < array.length; i++) {   // O(n)
        console.log(array[i]);
    }

    for (let i = 0; i < array.length; i++) {   // O(n)
        for (let j = 0; j < array.length; j++) {   // O(n)
            console.log(array[i], array[j]);
        }
    }
}

The first operation console.log(array[0]) runs in O(1) time.
The first loop runs in O(n) time.
The nested loop runs in O(n²) time.

To find the overall time complexity, we combine these:

O(1) + O(n) + O(n²)
The dominant term is O(n²), so the time complexity of this function is O(n²).

Final Thoughts

Learning about Big O Notation has been a game-changer for me. It helps me think critically about the efficiency of my code and make better decisions when choosing data structures and algorithms. By understanding time complexities like O(1), O(n), and O(n²), I can now:

Identify the strengths and weaknesses of my solutions.
Optimize my code for better performance.
Avoid writing programs that work but scale poorly.

As I continue learning, I'll explore more complex time complexities and data structures. For now, this reflection gives me a solid foundation to write better and more efficient programs.

Happy coding!

Key Takeaways:

Big O Notation measures how algorithms scale with input size.
O(1): Constant time - execution time remains the same.
O(n): Linear time - execution time grows proportionally with input size.
O(n²): Quadratic time - execution time grows quadratically due to nested loops.
Always focus on the dominant term when analysing code.

I hope this reflection helps other beginners like me! Understanding these basics will pave the way for solving more challenging problems efficiently.