# AMQL - [What is AMQL?](/docs): AMQL is the analytics-as-code language for an expressive semantic layer. AML defines models and metrics; AQL queries them as composable objects. - [Try AMQL](/docs/try-amql): One model, one dataset, one query. Enough to see how AML and AQL fit together, and a playground to run it in. - [Design Principles](/docs/design-principles): Underlying principles and philosophy behind how AMQL is designed - [AML vs YAML](/docs/aml-vs-yaml): AML is a typed language built for analytics modeling. The structural case against YAML, and how YAML-based analytics-as-code lands in the Jinja trap. - **AML** - [AML Overview](/docs/aml): Why a semantic layer needs a real language, what AML gives you that YAML configs cannot, and the vocabulary you will meet in every AML file. - [Core concepts](/docs/aml/concepts): Models, dimensions, measures, relationships and datasets: the building blocks every AML file is made of, and how they map onto SQL. - Modeling patterns - [Modeling patterns](/docs/aml/modeling-patterns): How to design scalable and maintainable semantic models with proven data modeling patterns, expressed in AML. - [Star schema pattern](/docs/aml/modeling-patterns/star-schema): How to implement a star schema in AML with one fact table and multiple dimensions - [Snowflake schema pattern](/docs/aml/modeling-patterns/snowflake-schema): How to implement a snowflake schema with normalized dimension chains in AML, and when a star schema is the better choice. - [Galaxy schema (fact constellation)](/docs/aml/modeling-patterns/galaxy-schema): Learn how to implement galaxy schema with multiple facts sharing common dimensions - [Role-playing dimensions pattern](/docs/aml/modeling-patterns/role-playing-dimensions): Learn how to use the same dimension multiple times with different meanings - [Composite keys pattern](/docs/aml/modeling-patterns/composite-keys): Learn how to join two models on several columns at once - [Polymorphic associations pattern](/docs/aml/modeling-patterns/polymorphic-associations): Learn how to join a model whose foreign key points into different tables depending on a type column - [Validity windows pattern](/docs/aml/modeling-patterns/validity-windows): Learn how to match each fact row to the dimension row whose validity window contains it, such as price bands and SCD Type 2 history - [Interval overlap pattern](/docs/aml/modeling-patterns/interval-overlap): Learn how to count a record in every period its date range overlaps, such as open orders per month or active headcount per month - Joins - [How joins are generated](/docs/aml/joins/how-joins-work): How the compiler turns relationships into SQL joins, how the join type is chosen, and how fan-out and referential integrity are handled. - [Path ambiguity](/docs/aml/joins/path-ambiguity): What happens when a dataset has more than one join path between two models, how the compiler ranks the candidates, and how to override its choice. - [Filter direction](/docs/aml/joins/filter-direction): Control which way filters and groupings flow across a relationship with the direction property, and override it per metric with with_relationships(). - [Fan-out resolution](/docs/aml/joins/fan-out): The compiler resolves fan-out automatically. How the mechanism works and how it compares to symmetric aggregates. - Reusability - [Reusability](/docs/aml/reusability): AML's reusability features, how Extend works, and a step-by-step process for factoring logic out and reusing it across models and datasets. - [Define & reuse global SQL definitions](/docs/aml/reusability/const-reuse-sql-definitions): Declare a SQL definition once as an AML Constant and reuse it across models, so a change lands in one place. - [Implement Reusable Metric Store](/docs/aml/reusability/extend-metric-store): Define metrics once with AML Extend and reuse them across datasets, so one metric store serves the whole project. - [Expose different data to different teams](/docs/aml/reusability/extend-reuse-modify-expose-data): Reuse one model with AML Extend while hiding fields per audience, so HR and Finance see different cuts of the same data. - [Reuse models but dynamically modify dimensions](/docs/aml/reusability/extend-reuse-modify-values): Reuse a model with AML Extend but redefine its dimensions, so one model can serve several shapes without a copy. - [Create new models from existing dimensions and measures](/docs/aml/reusability/extend-create-new-models-on-the-fly): Chain AML Extend to build a new model from groups of reusable dimensions and measures, instead of declaring it from scratch. - [Create your own types](/docs/aml/reusability/create-your-own-types): Create your own types to give related values a common shape, keep them consistent, and catch mistakes early - **AQL** - [AQL Overview](/docs/aql): AQL is a query language where metrics are first-class composable objects. That is what makes a semantic layer expressive enough to reason from. - Learn - [AQL in 30 minutes](/docs/aql/learn/learn-in-30-minutes): A skim-friendly tour of AQL. Each section is a 30-second preview with a deep link to the full Learn page. - [What AQL is for](/docs/aql/learn/what-aql-is-for): AQL exists to define and query metrics: reusable aggregations that adapt to wherever you drop them. Everything else is the plumbing behind that. - [Tables and rows](/docs/aql/learn/tables-and-rows): AQL operates on tables, one row at a time. Understanding the 'current row' is the foundation for everything else. - [The pipe operator](/docs/aql/learn/pipe): The pipe operator (|) chains AQL expressions left-to-right. It's the single biggest thing that makes AQL read differently from SQL. - [Filtering](/docs/aql/learn/filtering): AQL has two filter functions: where() and filter(). They look similar but solve different problems. - [Grouping and aggregation](/docs/aql/learn/grouping): group() collapses rows by a dimension. Combined with an aggregator like sum() or count(), it produces one number per group. - [Cross-model queries](/docs/aql/learn/cross-model): Reference columns from related models without writing joins. AQL uses the relationships from your dataset to figure out the path. - [Defining a metric](/docs/aql/learn/defining-a-metric): A metric is an aggregation you've named. Once named, you can reuse it across reports and compose it into bigger metrics. - [Metric context](/docs/aql/learn/metric-context): A metric doesn't carry a fixed value. It gets evaluated against the dimensions and filters around it. That surrounding state is the metric's context. - [Level of detail](/docs/aql/learn/level-of-detail): Sometimes a metric needs to be calculated at a different grain than the report it appears in. Level-of-detail functions let you override that grain. - [Nested aggregation](/docs/aql/learn/nested-aggregation): Sometimes you need to aggregate the results of an aggregation. AQL handles this with the group() + select() pattern inside another aggregator. - [Time comparisons](/docs/aql/learn/time-comparisons): Year-over-year, month-over-month, period-to-date: all expressed as 'evaluate this metric in a shifted time context'. - Coming from SQL - [AQL vs SQL](/docs/aql/from-sql/aql-vs-sql): Why SQL, designed in the 1970s for querying tables, is a poor fit for defining and composing business metrics, and what AQL does differently. - [Learn AQL from a SQL background](/docs/aql/from-sql): Learn how to transform your SQL analytics workflow into AQL's metric-centric approach - Concepts - [Basic Concepts](/docs/aql/concepts/basic-concepts): Definitions of core AQL concepts, including expressions, models, iteration, origin models, and cross-model references. - [Origin Model](/docs/aql/concepts/origin-model): Understand how AQL transformations like select and filter retain the origin model, preserving model context that plain SQL queries lose. - Writing AQL - [Where to define AQL expressions](/docs/aql/writing/where-to-define-aql): Decide whether an AQL metric or dimension belongs on a model, on a dataset, as a standalone Metric, or inline in an explore. - [Order of Operations](/docs/aql/writing/order-of-operations): When filters fire, when metrics evaluate, and why an of_all() can make a dimension filter look ignored. - [AQL Best Practices](/docs/aql/writing/best-practices): Advisory guidance for writing AQL: model design, metric design, performance, and the SQL-to-AQL pitfalls that catch people most often. - User-defined functions - [AQL user-defined functions](/docs/aql/user-defined-functions): Define a reusable AQL expression once in AML and call it from any metric or dimension. - [ABC classification](/docs/aql/user-defined-functions/abc-classification): Classify items into A, B and C by their share of a total, such as products by sales, with reusable AQL functions. - [Project profitability](/docs/aql/user-defined-functions/project-profitability): Calculate discount factor, free cash flow, discounted cash flow, NPV and payback year for a project with reusable AQL functions. - Cookbook - [Metrics by Example](/docs/aql/cookbook/metrics-by-example): Find the AQL pattern that fits your question: by intent, by metric shape, or by copy-paste snippet. Built on a shared e-commerce schema. - [Level of Detail Patterns](/docs/aql/cookbook/level-of-detail): Four worked Level of Detail patterns in AQL: nested aggregation, dimensionalize(), customer segmentation flags, and is_at_level() percentages. - [Percent of Total](/docs/aql/cookbook/percent-of-total): A detailed walkthrough on how to calculate the distribution of a metric using AQL. - [Period Comparison](/docs/aql/cookbook/period-comparison): Compare metrics across time periods in code: relative offsets, fixed date ranges, granularity control, and CAGR, each with a worked AQL example. - [Moving Average](/docs/aql/cookbook/moving-average): Smooth a time series with a 3-month moving average: using trailing_period() for calendar-based windows or window_avg() for row-based windows. - [Cumulative Metrics](/docs/aql/cookbook/cumulative-metrics): Build cumulative metrics over time with running_total(), worked through a running revenue total and a cumulative average order value per category. - [Multi-Period KPI Summary](/docs/aql/cookbook/multi-period-kpi-summary): Build report showing key business metrics (like GMV, AOV, and Total Orders) across time windows like Week, MTD, and YTD: all in one summary table. - [Cohort Retention](/docs/aql/cookbook/cohort-retention): This step-by-step tutorial helps you understand and apply AQL for solving the Cohort Retention Analysis use case - [Customer Order Frequency](/docs/aql/cookbook/customer-order-frequency): Build a cumulative order-frequency chart in AQL by chaining dimensionalize, percent-of-total, nested aggregation and running_total on each other. - [Nested Aggregation](/docs/aql/cookbook/nested-aggregation): A worked cookbook example: aggregate an order-level metric up to the user grain, then take the max per country. - [Ranking](/docs/aql/cookbook/rank): Rank items in AQL with rank() and ntile(): globally, within partitions, as a static dimension, or bucketed into tiers such as quartiles and deciles. - [Semi-Additive Calculation](/docs/aql/cookbook/semi-additive-calculation): Some metrics aggregate across every dimension but time. Two AQL approaches to those, worked through a bank balance as the running example. - Troubleshooting - [Troubleshooting](/docs/aql/troubleshooting): Narrow down the source of an AQL error, diagnose unexpected results, and use the AMQL sandbox to isolate and test a fix on a simplified query. - [AQL Validation Rules](/docs/aql/troubleshooting/validation-rules): Hard constraints that AQL expressions must follow. Violating these either fails validation or produces deterministically wrong results. - [Common Relationships Problems](/docs/aql/troubleshooting/relationship-problems): Fix two common data model problems: unconnected models that break filters or duplicate metrics, and ambiguous joins that send AQL down the wrong path. - **Reference** - [Reference](/docs/reference): Lookup material for AML objects and AQL functions. - AML Reference - [AML Reference](/docs/reference/aml): Syntax, types, and object definitions for the Analytics Modeling Language. - [AML Quick Reference](/docs/reference/aml/examples): Quick syntax lookup for commonly used AML patterns - Objects - Model - [AML Model](/docs/reference/aml/objects/model): The AML Model block maps a database table or SQL query to dimensions and measures, giving the semantic layer its structure for exploring data. - [AML Table Model](/docs/reference/aml/objects/model/table-model): AML Table Model reference: every parameter from type and label to data source, dimensions and measures, plus an example mapping a table to a model. - [AML Query Model](/docs/reference/aml/objects/model/query-model): Define an AML model on top of a custom SQL query: reference other models, declare dimensions and measures, and configure query persistence. - [AML Model Fields](/docs/reference/aml/objects/model/field): Syntax for dimensions and measures inside a model. - Dataset - [AML Dataset](/docs/reference/aml/objects/dataset): Dataset definition syntax in AML: metadata, models, relationships, cross-model fields and the view block. - [AML Dataset Fields](/docs/reference/aml/objects/dataset/dataset-field): Introducing concepts about Dimensions and Metrics in a Dataset. - [AML Relationship](/docs/reference/aml/objects/dataset/relationship): Syntax for defining relationships between models: types, join fields, filter direction and nullability. - [AML Metric](/docs/reference/aml/objects/metric): Reference for the AML Metric object — an aggregated business measure that can be defined inside a dataset or as a standalone, reusable definition. - [AML Types](/docs/reference/aml/types): AML's built-in types, how to declare custom ones, and how the type system catches errors as you write. - Data Format - [Date Format](/docs/reference/aml/formats/date-format): How the format property works on AML date fields, with the day, month, year and separator tokens and the patterns each one accepts. - [Number Format](/docs/reference/aml/formats/number-format): Use the format property on number fields to control currency symbols, thousands separators, decimal places, percentages and K/M/B abbreviations. - [Data Format](/docs/reference/aml/formats/data-format): Format AML dimensions and measures for cleaner reports by adding a format property with a date or number pattern to the model definition. - Programmable Constructs - [AML Constant](/docs/reference/aml/language/constant): Declare a value once with AML Constant and reuse it anywhere in the project, so thresholds live in one place. - [AML Function](/docs/reference/aml/language/func): Declare reusable logic with AML Func: typed parameters, defaults, and the limits on what a function can return. - [AML Extend](/docs/reference/aml/language/extend): How to use AML Extend to build on an existing object without redeclaring it. - [AML Partial](/docs/reference/aml/language/partial): Use AML Partial to extract the shared logic into a new object, making it ready for reuse across multiple objects - [AML String Interpolation](/docs/reference/aml/language/string-interpolation): Embed values in AML strings, multi-line strings and heredocs with ${...} interpolation, including expressions such as if-else. - [AML If-else](/docs/reference/aml/language/if-else): Control-flow expression to conditionally evaluate and return values in AML. - [AML Module](/docs/reference/aml/language/module): Use Module to group similar AMQL objects and functions together. Enable better structuring and future code reusability. - AQL Reference - [AQL Reference](/docs/reference/aql): Signatures, grammar, and per-function details for the Analytics Query Language. - [AQL Cheatsheet - Operators](/docs/reference/aql/operators): Quick Reference for AQL Operators - Grammar - [AQL Expression](/docs/reference/aql/grammar/expression): What AQL expressions are, the two kinds (table and metric), how the pipe operator chains functions, and the dataset context each one needs. - [Table Expressions](/docs/reference/aql/grammar/table-expression): Table expressions in AQL are queries returning tabular output. Chain table functions with the pipe operator to select, filter and join data. - [Metric Expressions](/docs/reference/aql/grammar/metric-expression): AQL metric expressions: composing aggregations, setting a source table, and applying metric context (filters, LOD, window functions) with pipes. - [Explore Expressions](/docs/reference/aql/grammar/explore-expression): Explore expressions are AQL's top-level query form: name the dimensions, measures and filters, and leave tables and joins to the compiler. - [Datetime Literals](/docs/reference/aql/grammar/datetime-literal): AQL datetime literals use @ syntax for fixed dates, date ranges and relative expressions like @(last 7 days), for use with datetime operators. - [AQL Condition](/docs/reference/aql/grammar/condition): Leverage AQL Condition for advanced filtering use cases - [AQL Operators](/docs/reference/aql/grammar/operator): How AQL operators work, from the pipe to logical comparisons across text, numbers, datetimes and booleans, with a syntax example for each. - [Macros](/docs/reference/aql/grammar/macros): AQL macros take expressions instead of values: where, keep, exclude and dimensionalize, the building blocks for composing metrics flexibly. - [eval](/docs/reference/aql/grammar/eval): AQL Syntax: create a new context - [where vs. filter](/docs/reference/aql/grammar/where-vs-filter): Compare the differences between 'where' function and the 'filter' function in this documentation - Types - [Types](/docs/reference/aql/types): A reference to every type in AQL: what a type is, how types act as blueprints for objects like models and fields, and the properties each allows. - [Scalar Type](/docs/reference/aql/types/scalar): A reference to AQL scalar types like Date, Datetime, Number, Text, and Truefalse, plus types that exist but are not yet operable. - [Text](/docs/reference/aql/types/text): AQL's text type stores strings of any characters, with operators for equality, pattern matching (like, ilike), list membership and null checks. - [Number](/docs/reference/aql/types/number): AQL's number type holds integers, floats and doubles as a dimension or measure, with its comparison operators and number formatting. - [Date](/docs/reference/aql/types/date): Reference for AQL's Date type: what it represents, the operators for filtering by period or exact date, and how to format it in a dimension. - [Datetime](/docs/reference/aql/types/datetime): AQL's datetime type stores date and time together, handles time zones and daylight saving automatically, and adds period and exact-time operators. - [Truefalse](/docs/reference/aql/types/truefalse): AQL's truefalse type represents boolean values, with the operators for filtering and comparing truefalse dimensions inside a query. - [Table Type](/docs/reference/aql/types/table): The Table type in AQL holds rows and columns from a model. Chained transformations return Tables; an aggregation collapses one into a Scalar. - [Dimension (reference)](/docs/reference/aql/types/dimension): Some AQL functions take only a predefined dimension reference (model.field_name). Why arbitrary expressions are rejected, and what to define in AML. - [Field Type](/docs/reference/aql/types/field): How AQL's Field type works, what field origin means, how a field tracks where it came from, and why new fields from transformations lose it. - [Measure](/docs/reference/aql/types/measure): Define measures in AQL data models: every parameter, the supported aggregation types, the two SQL definition forms, and custom aggregation rules. - Functions - [AQL Functions](/docs/reference/aql/functions): Every AQL function, grouped by category. Each row links to the full reference page with signatures and examples. - Table functions - [Table Functions](/docs/reference/aql/functions/table): Reference for AQL's four table functions (select, group, filter and unique), each transforming an input table expression into an output table. - [select](/docs/reference/aql/functions/table/select): AQL Syntax: Retrieves one or multiple fields - [filter](/docs/reference/aql/functions/table/filter): AQL Syntax: Extract the records that match the conditions - [group](/docs/reference/aql/functions/table/group): AQL Syntax: Group data into one or multiple dimensions - [unique](/docs/reference/aql/functions/table/unique): AQL Syntax: To return unique values - [top](/docs/reference/aql/functions/table/top): AQL Syntax: To return top rows - [bottom](/docs/reference/aql/functions/table/bottom): AQL Syntax: To return bottom rows - [Metric Functions Overview](/docs/reference/aql/functions/metric-functions): Metric functions transform a metric expression by modifying its context: its filters, relationships, level of detail, or time window. - Condition functions - [where](/docs/reference/aql/functions/condition/where): AQL Syntax: Apply filtering conditions to measure - [with_relationships](/docs/reference/aql/functions/with_relationships): Use with_relationships() to activate one join path when two tables connect through several routes, so a measure gets exactly the right context. - LOD functions - [of_all](/docs/reference/aql/functions/lod/of_all): AQL Function: Evaluate expression without certain dimensions or grains. Aliases: of_all, exclude_grains - [keep_grains](/docs/reference/aql/functions/lod/keep): AQL syntax: Evaluate a metric against specific grain(s)/dimension(s), excluding others. - [dimensionalize (previously exact_grains)](/docs/reference/aql/functions/lod/dimensionalize): AQL Syntax: always evaluate an expression using certain grains; Dimensionalized Measure - [percent_of_total](/docs/reference/aql/functions/lod/percent_of_total): Calculates the percentage of a metric relative to different totals (row, column, or grand total). - Time-based functions - [running_total](/docs/reference/aql/functions/time/running_total): AQL Function: Calculate a metric from starting of time (visible data by default) to the current period - [running_total! (deprecated)](/docs/reference/aql/functions/time/running_total_all): AQL Syntax: Evaluate measures along a running dimension with all data - [period_to_date](/docs/reference/aql/functions/time/period_to_date): Calculate a metric from the start of a year, quarter, month or other period up to the current date, as in a Year-to-Date (YTD) total. - [exact_period](/docs/reference/aql/functions/time/exact_period): AQL function: calculate a metric within a custom time period, so you can compare how it performs in that period against another one. - [relative_period](/docs/reference/aql/functions/time/relative_period): AQL Function: A function to calculate a metric in the active time-range shifted by a specified interval - [trailing_period](/docs/reference/aql/functions/time/trailing_period): A function to calculate a metric over a specific number of date periods up to the current period. - Window functions - [Window functions](/docs/reference/aql/functions/window): AQL window functions do secondary calculations across a subset of a table's rows: navigating, ranking and aggregating, as SQL window functions do. - Ranking - [rank](/docs/reference/aql/functions/window/ranking/rank): AQL window function: ranks rows within a partition, giving ties the same rank and then skipping ahead, e.g. 1, 1, 3, 4, 4, 6. - [dense_rank](/docs/reference/aql/functions/window/ranking/dense_rank): AQL window function: ranks rows within a partition, giving ties the same rank and keeping the sequence consecutive, e.g. 1, 1, 2, 3, 3, 4. - [percent_rank](/docs/reference/aql/functions/window/ranking/percent_rank): AQL Window Function: Returns the relative percentile of a row within a partition of a table. The value is between 0 and 1, inclusive. - [ntile](/docs/reference/aql/functions/window/ranking/ntile): AQL window function: divides the rows in a partition into a given number of ranked groups, assigning each row its bucket number. - Navigation - [previous](/docs/reference/aql/functions/window/navigation/previous): AQL Window Function: A window function that returns the value of the previous row at an offset relative to the current row. - [next](/docs/reference/aql/functions/window/navigation/next): AQL Window Function: A window function that returns the value of the next row at an offset relative to the current row. - Position - [first_value](/docs/reference/aql/functions/window/position/first_value): AQL Window Function: Returns the value of an expression from the first row of the window frame. - [last_value](/docs/reference/aql/functions/window/position/last_value): AQL Window Function: Returns the value of an expression from the last row of the window frame. - [nth_value](/docs/reference/aql/functions/window/position/nth_value): AQL Window Function: Returns the value of an expression from the Nth row of the window frame, where N is a positive integer. - Aggregates - [window_sum](/docs/reference/aql/functions/window/aggregates/window_sum): Use window_sum to compute running totals or range-based sums across rows in AQL, with control over ordering, partitioning and axis-aware behavior. - [window_avg](/docs/reference/aql/functions/window/aggregates/window_avg): AQL Window Function: A window aggregation function that returns the average of rows in a range relative to the current row. - [window_min](/docs/reference/aql/functions/window/aggregates/window_min): AQL Window Function: A window aggregation function that returns the min of rows in a range relative to the current row. - [window_max](/docs/reference/aql/functions/window/aggregates/window_max): AQL Window Function: A window aggregation function that returns the max of rows in a range relative to the current row. - [window_count](/docs/reference/aql/functions/window/aggregates/window_count): AQL Window Function: A window aggregation function that returns the count of rows in a range relative to the current row. - [window_stdev](/docs/reference/aql/functions/window/aggregates/window_stdev): AQL Window Function: A window aggregation function that returns the (sample) standard deviation of rows in a range relative to the current row. - [window_stdevp](/docs/reference/aql/functions/window/aggregates/window_stdevp): AQL Window Function: A window aggregation function that returns the (population) standard deviation of rows in a range relative to the current row. - [window_var](/docs/reference/aql/functions/window/aggregates/window_var): AQL Window Function: A window aggregation function that returns the (sample) variance of rows in a range relative to the current row. - [window_varp](/docs/reference/aql/functions/window/aggregates/window_varp): AQL Window Function: A window aggregation function that returns the (sample) variance of rows in a range relative to the current row. - Inline reference - [Aggregate Functions (Aggregators)](/docs/reference/aql/functions/inline/aggregator-functions): Reference for AQL aggregate functions (count, sum, average, percentile and more) with syntax, return types, examples and database support notes. - [Logical Functions](/docs/reference/aql/functions/inline/logical-functions): AQL logical functions (case, and, or, not, is, in) with syntax, return types and examples for writing conditional expressions in a calculation. - [Mathematical Functions](/docs/reference/aql/functions/inline/math-functions): All mathematical functions available in AQL. - [Text Functions](/docs/reference/aql/functions/inline/text-functions): This page provides a comprehensive list of text manipulation functions for string operations. - [Time Intelligence Functions](/docs/reference/aql/functions/inline/time-intelligence-functions): All time intelligence functions available in AQL. - [Null/Zero Functions](/docs/reference/aql/functions/inline/null-and-zero-functions): Reference for AQL's null and zero handling functions (coalesce, nullif, and safe_divide), covering syntax, return types, and worked examples for each. - [SQL Passthrough Functions](/docs/reference/aql/functions/inline/sql-passthrough-functions): SQL passthrough functions allow you to call native database functions directly within AQL queries. - [Miscellaneous Functions](/docs/reference/aql/functions/inline/miscellaneous-functions): This page provides a list of miscellaneous functions that are not categorized into other groups. - [AQL Error Reference](/docs/reference/aql/error-reference): Look up any AQL error code from ERR-0 to WARN-300, each with a plain description, an example that triggers it, and a suggested fix.