AML Quick Reference

Dataset raw_ecommerce {
  label: 'Raw Ecommerce'
  description: "This Dataset is about Ecommerce data"
  owner: '[email protected]'
  data_source_name: 'demodb'

  models: [
    users,
    orders
  ]

  relationships: [
  // define relationship between orders and users is many to one
    relationship(orders.user_id > users.id, true)
  ]
}

Model users {
  type: 'table'
  label: "Users"
  data_source_name: 'bigquery_dw'
  table_name: 'users'

  dimension id {
    label: 'ID'
    type: 'number'
    hidden: false // optional
    definition: @sql {{#SOURCE.id}};; // optional
  }

  dimension email {
    label: 'Email'
    type: 'number'
    // without "definition", it automatically uses the same column name as the dimension name ('email')
  }

  measure user_count {
    type: 'number'
    label: 'Count Users'
    definition: @aql count(users.id) ;;
  }
}

Model location {
  type: 'query'
  label: 'Location'
  data_source_name: 'demodb'
  models: [cities, countries]

  query: @sql
    select {{ #ci.id }} as city_id
        , {{ #ci.name }} as city_name
        , {{ #co.code }} as country_code
        , {{ #co.name }} as country_name
        , {{ #co.continent_name }}
    from {{ #cities as ci }}
      left join {{ #countries as co }} on {{ #co.code }} = {{ #ci.country_code }};;

  dimension city_id {
    label: 'City Id'
    type: 'number'
  }

  dimension city_name {
    label: 'City Name'
    type: 'text'
  }

  dimension country_code {
    label: 'Country Code'
    type: 'text'
  }

  dimension country_name {
    label: 'Country Name'
    type: 'text'
  }

  dimension continent_name {
    label: 'Continent Name'
    type: 'text'
  }
}

measure total_orders {
  label: 'Total Orders'
  type: 'number'
  definition: @aql count(orders.id) ;;
}

measure total_users {
  label: 'Total Users'
  type: 'number'
  definition: @aql count(users.id) ;;
}

// This is a simple dimension. The compiler assumes there is a `created_at` column in your
// underlying table
dimension created_at {
  label: 'Created At'
  type: 'datetime'
}

// This is a derived dimension based on the dimension above, using AQL.
dimension created_at_year {
  label: 'Created At Year'
  type: 'number'
  definition: @aql date_part('year', orders.created_at) ;;
}

// This is a simple dimension, with explicitly defined column name.
dimension status {
  label: 'Status'
  type: 'text'
  // If you don't include this line, the compiler uses the table's column that bears the
  // same name as the dimension (i.e. status).
  definition: @sql {{ #SOURCE.status }};;
}

// This is a dimension which uses AQL case-when syntax.
dimension age_group {
  label: 'User Age Group'
  description: "'Under 18', '18 - 22', '23 - 30', '31 - 40', 'Over 40'"
  type: 'text'
  hidden: false
  definition: @aql case(
    when: users.age < 18, then: 'Under 18',
    when: users.age >= 18 and users.age < 23, then: '18 - 22',
    when: users.age >= 23 and users.age < 31, then: '23 - 30',
    when: users.age >= 31 and users.age < 41, then: '31 - 40',
    else: 'Over 40'
  ) ;;
}

// This is a dimension to extract month's name from the base dimension `created_at`.
dimension month_name {
  label: 'Month Name'
  type: 'text'
  definition: @aql date_format(orders.created_at, '%m - %B') ;; // example output: '12 - December'
}

// Relationship full form defined in dataset file
RelationshipConfig {
  rel: Relationship {
    type: 'many_to_one'
    from: r(products.merchant_id)
    to: r(merchants.id)
  }
  active: true
},

// Relationship defined in model file
Relationship relationship_name {
  type: 'many_to_one'
  from: r(orders.user_id)
  to:  r(users.id)
}

// Relationship short form defined in dataset file, relationship type > is many_to_one
relationship(ecommerce_cities.country_code > ecommerce_countries.code, true, 'one_way')

// Relationship short form defined in dataset file, relationship type - is one_to_one
relationship(ecommerce_merchants.admin_id - ecommerce_users.id, true)

Dataset e_commerce {
  // ...

  dimension full_name {
    model: users
    type: 'text'
    label: 'Full Name'
    definition: @aql concat(users.first_name, ' ', users.last_name);;
  }
}

Dataset ecommerce {
  // ...

  // Simple aggregation
  metric count_orders {
    label: 'Count Orders'
    type: 'number'
    definition: @aql count(orders.id) ;;
  }

  // Cross-model aggregation
  metric sum_order_value {
    label: 'Sum Order Values'
    type: 'number'
    definition: @aql sum(order_items, order_items.quantity * products.price) ;;
  }
}

const signup_threshold = 0.6
const greeting = 'Hello'

// Explicit type
Number tax_rate = 0.08

// Used in a dataset metric via string interpolation
Dataset my_dataset {
  metric active_users {
    definition: @aql count(*)
      | where users.signup_rate > ${signup_threshold}
    ;;
  }
}

// Simple function
Func double(x: Number) {
  x * 2
}

// Function that returns a VizBlock
Func revenue_chart(dataset: String) {
  VizBlock {
    label: 'Revenue'
    viz: BarChart {
      dataset: dataset
      series {
        field { ref: r(orders.revenue), aggregation: 'sum' }
      }
    }
  }
}

// Call the function
revenue_chart('ecommerce')

|-- datasets/
|    |-- ecommerce.dataset.aml
|-- modules/
|    |-- cohort/
|    |    |-- retention.aml
|    |-- utils/
|         |-- date_helpers.aml

// datasets/ecommerce.dataset.aml
Dataset ecommerce {
  models: [
    orders,
    cohort.retention_table // model from the cohort submodule
  ]
}

use cohort { retention_table }
use utils { date_helpers: dates } // alias date_helpers to dates

Model users {
  dimension id { type: 'number' }
  dimension email { type: 'text' }
  dimension salary { type: 'number', hidden: false }
}

// Add a new dimension
Model users_with_activation = users.extend({
  dimension activated_at { type: 'datetime' }
})

// Override a property without re-declaring everything
Model users_anonymized = users.extend({
  dimension salary { hidden: true }
})

PartialModel timestamp_fields {
  dimension created_at {
    type: 'datetime'
    definition: @sql {{ #SOURCE.created_at }};;
  }
  dimension updated_at {
    type: 'datetime'
    definition: @sql {{ #SOURCE.updated_at }};;
  }
}

Model users_base {
  type: 'table'
  table_name: 'public.users'
  data_source_name: 'demodb'
  dimension id { type: 'number' }
}

Model users = users_base.extend(timestamp_fields)

PartialDataset revenue_metrics {
  metric gmv { definition: @aql sum(orders.gmv);; }
  metric mrr { definition: @aql sum(subscriptions.mrr);; }
}

Dataset company = company_base.extend(revenue_metrics)
Dataset store = store_base.extend(revenue_metrics)

const score = 85

const grade = if (score >= 90) {
  'A'
} else if (score >= 75) {
  'B'
} else {
  'C'
}

// grade is 'B'

Func kpi_block(metric: 'revenue' | 'orders') {
  const measure_ref = if (metric == 'revenue') {
    r(orders.revenue)
  } else {
    r(orders.id)
  }

  VizBlock {
    label: "KPI -- ${metric}"
    viz: SingleValue {
      dataset: 'ecommerce'
      series {
        field {
          ref: measure_ref
          aggregation: if (metric == 'revenue') { 'sum' } else { 'count' }
        }
      }
    }
  }
}

const env = 'production'
const schema = 'analytics'

// In a string
const table_path = '${schema}.orders' // 'analytics.orders'

// In a heredoc (SQL definition)
@sql select * from ${schema}.orders ;;

// In a function label
Func chart(title: String) {
  VizBlock {
    label: 'Chart -- ${title}'
  }
}

// Combined with if-else
Func greet(name: 'John' | 'Alice') {
  "Hello, ${if (name == 'John') { 'sir' } else { 'madam' }}!"
}