Learn AQL from SQL background

This guide helps SQL practitioners understand AQL's metric-centric paradigm and migrate their analytics workflows to leverage Holistics' semantic layer capabilities.

Target Audience

• Data analysts comfortable with SQL looking to adopt semantic modeling
• Analytics engineers migrating from SQL-based pipelines
• Teams evaluating AQL for scalable analytics infrastructure

What is AQL?

AQL (Analytics Query Language) is Holistics' semantic modeling language that sits between your data warehouse and business users. Instead of writing SQL queries repeatedly, you define metrics, dimensions, and relationships once, creating a reusable semantic layer.

Traditional SQL workflow:

Data Warehouse → SQL Queries → Reports

AQL workflow:

Data Warehouse → Semantic Layer (AQL) → Self-Service Analytics

Fundamental Shift in Thinking

When transitioning from SQL to AQL, the most significant change is how you approach building analytics. While SQL encourages thinking in terms of data transformations, AQL promotes thinking in terms of business metrics.

SQL starts with raw tables and transforms them step by step:

1. Transform the raw orders data to calculate daily sales
2. Transform those daily totals to get monthly aggregates
3. Transform the monthly data to calculate what users asked for - growth rates

Each query builds a pipeline where output feeds into the next transformation.

AQL starts with what users need and works backwards:

1. What metrics do users want? -> Month-over-month growth rate
2. What components can be used to build it? -> Current month sales and previous month sales
3. What existing metrics can we reuse? -> sales metric
4. What data is needed? → The orders table with amount field

This approach creates a library of reusable metrics that combine flexibly for different analyses, rather than single-purpose SQL queries that solve one specific problem.

Practical Example: Period-over-Period Growth

SQL's Sequential Build-up:

-- Layer 1: Daily aggregation
WITH daily_sales AS (
  SELECT DATE(order_date) as date, SUM(amount) as sales
  FROM orders
  GROUP BY DATE(order_date)
),
-- Layer 2: Roll up to monthly
monthly_sales AS (
  SELECT
    DATE_TRUNC('month', date) as month,
    SUM(sales) as total_sales
  FROM daily_sales
  GROUP BY DATE_TRUNC('month', date)
),
-- Layer 3: Compute growth metrics
monthly_growth AS (
  SELECT
    month,
    total_sales,
    LAG(total_sales) OVER (ORDER BY month) as prev_month_sales,
    (total_sales - LAG(total_sales) OVER (ORDER BY month)) /
      LAG(total_sales) OVER (ORDER BY month) * 100 as growth_rate
  FROM monthly_sales
)
SELECT * FROM monthly_growth;

AQL's Goal-First Definition:

Dataset ecommerce {
  // Business need: growth rate
  metric monthly_sales_growth {
    definition: @aql
      safe_divide((sales - sales_last_month) * 100, sales_last_month)
    ;;
  }

  metric sales {
    definition: @aql sum(orders.amount) ;;
  }

  metric sales_last_month {
    definition: @aql
      sales
      | relative_period(orders.created_at, interval(-1 month))
    ;;
  }
}

Notice how AQL starts with the desired outcome and works backward to identify dependencies, while SQL builds forward through transformation layers.

From SQL to AQL Components

SELECT -> explore

The equivalent of a SELECT statement in Holistics is an explore expression. Unlike SQL, you don't write explore blocks directly - Holistics generates them through the UI when users build reports.

Instead of writing queries, you focus on building reusable components:

• Models - Business entities (users, orders, products)
• Relationships - How models connect (orders -> users)
• Metrics - Reusable aggregations (revenue, user count)
• Dimensions - Attributes and calculated fields

These components combine dynamically based on user selections in the UI.

SQL Query:

SELECT
  DATE_TRUNC('month', created_at) as month,
  status,
  COUNT(*) as order_count,
  SUM(amount) as revenue
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY 1, 2

AQL Components (you define):

Model orders {
  dimension created_at { type: 'datetime' }
  dimension status { type: 'text' }
  dimension amount { type: 'number' }
}

Dataset ecommerce {
  metric order_count {
    definition: @aql count(orders.id) ;;
  }

  metric revenue {
    definition: @aql sum(orders.amount) ;;
  }
}

Explore (generated by UI):

explore {
  dimensions {
    orders.created_at | month(),
    orders.status
  }
  measures {
    order_count,
    revenue
  }
  filters {
    orders.created_at >= @2024
  }
}

FROM -> source table

When aggregating data in SQL, you must choose a FROM table. This table determines the granularity of your data (assuming you don't cause fan-out with joins):

SELECT SUM(amount) FROM orders  -- Aggregates at order level

In AQL, all aggregation functions take the first parameter as the source table:

// Explicit: aggregate amount from orders table
sum(orders, orders.amount)

Most of the time, you don't need to specify the source table because AQL can auto-infer it from the expression inside the aggregate:

sum(orders.amount)  // Auto-inferred: clearly from orders table

This works because when you only reference fields from a single model, AQL knows to use that model as the source table.

Only specify the source table when:

1. Cross-model calculations - When combining fields from multiple models:

// Must specify order_items as base table for revenue calculation
sum(order_items, order_items.quantity * products.price)

2. Complex aggregations - When nesting aggregations:

// Average number of items per order
orders
| group(orders.id)
| select(sum(order_items.quantity))
| avg()

Knowledge Checkpoint

The pipe operator (|) chains operations on expressions. In this example, orders | group(...) takes orders and applies a group on orders.id to it. Learn more about pipe operators in AQL.

When choosing the source table, always choose the one that makes logical sense for your aggregation. For example, count(order_items, orders.id) would count order items instead of orders.

JOIN ON -> Relationships

SQL requires you to join tables explicitly in every query where you need data from multiple tables.

In Holistics, you don't define joins - you define the relationships between tables once in the dataset. For example:

• one user has many orders
• one order has many order_items
• many order_items have one product

From these relationship definitions, AQL will choose the appropriate join in the generated SQL.

-- SQL: Explicit joins in every query
SELECT
  o.id,
  u.name,
  SUM(oi.quantity * p.price) as revenue
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY o.id, u.name

-- AQL: Define relationships once
Dataset ecommerce {
  relationships [
    // many orders to one user
    relationship(orders.user_id > users.id, true),
    // many items to one order
    relationship(order_items.order_id > orders.id, true),
    // many items to one product
    relationship(order_items.product_id > products.id, true),
  ]
}

Dimensions vs Columns

In SQL, you work with physical columns and create calculated fields in your SELECT statements. In AQL, dimensions represent both physical columns and reusable calculated fields that become part of your semantic model.

Table column -> dimension

In Holistics, you can define calculated dimensions alongside physical columns in any model. These dimensions act like real columns and can reference other columns in their calculations.

Model users {
  // Physical columns from database
  dimension id { type: 'number' }
  dimension created_at { type: 'datetime' }
  dimension first_name { type: 'text' }
  dimension last_name { type: 'text' }
  
  // Calculated dimensions
  dimension full_name {
    label: 'Full Name'
    type: 'text'
    definition: @aql concat(users.first_name, ' ', users.last_name) ;;
  }
  
  dimension account_age_days {
    label: 'Account Age (Days)'
    type: 'number'
    definition: @aql date_diff('day', users.created_at, @now) ;;
  }
  
  dimension user_segment {
    label: 'User Segment'
    type: 'text'
    definition: @aql
      case(
        when: users.account_age_days < 30, then: 'New User',
        when: users.account_age_days < 90, then: 'Active User',
        else: 'Established User'
      ) ;;
  }
}

You can also use window functions in dimensions.

Example: Customer order history analysis

Model orders {
  dimension previous_order_date {
    label: 'Previous Order Date'
    type: 'datetime'
    definition: @aql previous(orders.created_at | day(), order: orders.created_at | day()) ;;
  }
}

Window functions operate on the complete underlying table.

The mental model for dimensions looks like this:

WITH holistics_model AS (
  SELECT
    *,  -- all underlying real columns
    dimension_expression AS dimension_name,
    window_function_expression AS window_dimension_name
  FROM underlying_table
)
-- Use holistics_model as if it's the underlying table
SELECT * FROM holistics_model WHERE ...

Cross-model dimension

Dimensions can directly access fields from related models when there's a many-to-one or one-to-one relationship.

If many order items have one product, then it's guaranteed that one order item row only corresponds to one product. This means rows in order_items can access columns in products as if they are in the same table.

Model order_items {
  dimension quantity { type: 'number' }
  
  dimension line_item_revenue {
    label: 'Line Item Revenue'
    type: 'number'
    definition: @aql order_items.quantity * products.price ;;
  }

  dimension product_category {
    label: 'Product Category'
    type: 'text'
    definition: @aql categories.name ;;
  }
}

This works because:

• order_items -> products (many:1)
• products -> categories (many:1)

So each order_item can "see" its product's price and category name directly.

Dimensionalized Metrics

One of AQL's most powerful features is turning metrics into dimensions using dimensionalize(). This enables analyses that would require complex subqueries in SQL:

SQL - Customer lifetime value as a dimension:

WITH customer_ltv AS (
  SELECT
    user_id,
    SUM(amount) as lifetime_value
  FROM orders
  GROUP BY user_id
)
SELECT
  CASE
    WHEN c.lifetime_value >= 10000 THEN 'VIP'
    WHEN c.lifetime_value >= 5000 THEN 'High Value'
    WHEN c.lifetime_value >= 1000 THEN 'Regular'
    ELSE 'New'
  END as customer_tier,
  COUNT(DISTINCT o.user_id) as customer_count,
  SUM(o.amount) as revenue
FROM orders o
JOIN customer_ltv c ON o.user_id = c.user_id
WHERE o.created_at >= '2024-01-01'
GROUP BY 1

AQL - Same logic as reusable dimensions:

// Define lifetime value as a dimension
dimension customer_lifetime_value {
  model: users
  type: 'number'
  definition: @aql sum(orders.amount) | dimensionalize(users.id) ;;
}

// Create customer tier based on LTV
dimension customer_tier {
  model: users
  type: 'text'
  definition: @aql
    case(
      when: customer_lifetime_value >= 10000, then: 'VIP',
      when: customer_lifetime_value >= 5000, then: 'High Value',
      when: customer_lifetime_value >= 1000, then: 'Regular',
      else: 'New'
    )
  ;;
}

// Now use as normal dimension in explore
explore {
  dimensions { users.customer_tier }
  measures {
    customer_count: count(users.id),
    revenue: sum(orders.amount)
  }
  filters { orders.created_at >= @2024 }
}

The dimensionalized metric calculates each user's lifetime value once and makes it available as a dimension for grouping, filtering, or further calculations.

Metric

In AQL, metrics are building blocks that encapsulate business logic - from simple sums to complex calculations involving multiple tables, filters, and time intelligence.

Basic Aggregation

In SQL, aggregations are always tied to a specific query context. Aggregations in SQL always operate in a specific context (of a GROUP BY or a WHERE).

In AQL, a metric is a first-class citizen - a reusable custom aggregation function. A basic metric is built like this:

metric sales {
  definition: @aql sum(orders.amount) ;;
}

Notice that you don't specify a specific GROUP BY or anything. This is one pitfall of people coming from SQL - they think that the grouping should be part of the metric. But in AQL, you should only introduce grouping if absolutely necessary. The grouping should be chosen dynamically as part of the explore for end-users, not in the metric definition itself.

Multi-model metrics

When creating metrics that aggregate across multiple models, you must specify the base table for aggregation. This is especially important for cross-model calculations.

Since metrics in AQL are standalone, they must bring their own FROM table. When working with fields from a single model, AQL can infer the source table. But when combining fields from multiple models, AQL can't infer which table should be the base for aggregation - you must specify the correct FROM table explicitly.

The first parameter tells AQL which table to use as the "FROM" for aggregation:

// Basic revenue metric aggregating at order_items level
metric revenue {
  label: 'Revenue'
  type: 'number'
  definition: @aql sum(order_items, order_items.quantity * products.price) ;;
}

Composed metrics

Metrics can be built from other metrics, creating a composable library of business logic:

// Base metrics
metric total_revenue {
  definition: @aql sum(order_items, order_items.quantity * products.price) ;;
}

metric total_orders {
  definition: @aql count(orders.id) ;;
}

// Composed metric
metric revenue_per_order {
  label: 'Revenue per Order'
  type: 'number'
  definition: @aql safe_divide(total_revenue, total_orders) ;;
}

SUM(CASE WHEN) -> Metrics with where()

SQL uses CASE WHEN for conditional aggregation:

SELECT
  SUM(amount) as total_revenue,
  SUM(CASE WHEN status = 'delivered' THEN amount END) as delivered_revenue,
  SUM(CASE WHEN created_at >= '2024-01-01' THEN amount END) as ytd_revenue
FROM orders

AQL uses the where() function to filter any metric:

// Base metric
metric revenue {
  definition: @aql sum(orders.amount) ;;
}

// Filtered variations
metric delivered_revenue {
  definition: @aql revenue | where(orders.status == 'delivered') ;;
}

metric ytd_revenue {
  definition: @aql revenue | where(orders.created_at >= @2024) ;;
}

// Or apply filters dynamically in explore
explore {
  measures {
    total: revenue,
    delivered: revenue | where(orders.status == 'delivered'),
    cancelled: revenue | where(orders.status == 'cancelled')
  }
}

Important difference: Unlike SQL, AQL returns null (not 0) when no data matches the filter:

// Returns null if no electronics products exist
electronics_revenue: revenue | where(categories.name == 'Electronics')

GROUP BY -> Level of Detail

Metrics in AQL can override the default grouping behavior using of_all() to create Level of Detail calculations:

// Calculate percent of total - ignoring product dimension
metric product_percent_of_total {
  label: 'Product % of Total Revenue'
  type: 'number'
  definition: @aql
    safe_divide(revenue * 100.0, revenue | of_all(products))
  ;;
}

// Calculate percent within category
metric percent_of_category {
  label: '% of Category Revenue'
  type: 'number'
  definition: @aql
    safe_divide(revenue * 100.0, revenue | of_all(products.name))
  ;;
}

The of_all() function tells AQL to calculate the metric without grouping by the specified dimensions, enabling calculations like "percent of total" that would require subqueries in SQL.

Nested (Multi-Level) Aggregation

AQL supports nested aggregations through the group() and select() pattern. This is equivalent to SQL's subqueries or CTEs but more composable:

// Average monthly customer acquisition
metric avg_monthly_new_customers {
  label: 'Avg Monthly New Customers'
  type: 'number'
  definition: @aql
    users
    | group(users.created_at | month())
    | select(monthly_count: count(users.id))
    | avg(monthly_count)
  ;;
}

// Max daily revenue
metric max_daily_revenue {
  label: 'Max Daily Revenue'
  type: 'number'
  definition: @aql
    order_items
    | group(order_items.created_at | day())
    | select(
        daily_revenue: sum(order_items, order_items.quantity * products.price)
      )
    | max(daily_revenue)
  ;;
}

This pattern:

1. Groups data by specific dimensions
2. Calculates metrics for each group
3. Applies a second aggregation on the results

It's more flexible than SQL because the same metric works in any context - by year, by country, etc.

WITH … (CTEs)

AQL doesn't require CTEs because its composable nature handles complex logic through:

1. Reusable dimensions and metrics - Define once, use everywhere
2. Pipe operators - Chain operations naturally
3. Table functions - group(), select(), filter() replace CTE patterns

SQL with CTEs:

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', order_date) as month,
    SUM(amount) as sales
  FROM orders
  GROUP BY 1
),
avg_monthly AS (
  SELECT AVG(sales) as avg_sales
  FROM monthly_sales
)
SELECT
  month,
  sales,
  sales - (SELECT avg_sales FROM avg_monthly) as variance
FROM monthly_sales;

AQL equivalent:

// Define the metrics
metric sales {
  definition: @aql sum(orders.amount) ;;
}

metric avg_monthly_sales {
  definition: @aql
    orders
    | group(orders.created_at | month())
    | select(sales)
    | avg(sales)
  ;;
}

metric sales_variance {
  definition: @aql sales - avg_monthly_sales ;;
}

// Use in exploration
explore {
  dimensions {
    orders.created_at | month()
  }
  measures {
    sales,
    sales_variance
  }
}

The AQL approach creates reusable components instead of query-specific CTEs.