Query Architecture

Casabase Cube queries are declarative.

You describe what you want through a Point of View (POV), and the query engine determines how to resolve the requested members, which data to read, how to aggregate it, which formulas apply, and what the current user is permitted to see.

For example:

CALL CUBE.QUERY_CUBE(
    'FINANCE',
    '{
        "ACCOUNT": [{"children": "Revenue"}],
        "PERIOD": [{"children": "Q1"}],
        "ENTITY": ["North America"]
    }',
    NULL,
    NULL
);

The caller does not specify a fact table, write joins, or define the aggregation path. Those details are resolved from the cube model.

The Query Contract

A Casabase Cube query consists primarily of:

Cube
  +
Point of View
  +
Query Options

The POV describes the requested multidimensional coordinates.

Conceptually:

POV
│
├── Dimension
│   └── Member selection
├── Dimension
│   └── Hierarchy expansion
└── Dimension
    └── Member selection
        │
        ▼
Casabase Cube Query Engine
        │
        ▼
Multidimensional Result

This abstraction allows the same query definition to continue working as the underlying model evolves.

For complete POV syntax, see Point of View (POV).

Query Processing Pipeline

Conceptually, Casabase Cube processes a query in five stages:

1. Resolve POV
       │
       ▼
2. Determine Permitted Scope
       │
       ▼
3. Aggregate
       │
       ▼
4. Calculate
       │
       ▼
5. Return, Write, or Refuse

The order matters.

In particular, security scope is established before aggregation and formula evaluation, so all downstream calculation operates only on data available to the executing user.

1. Resolve the Point of View

The first step is to resolve the POV into concrete member selections.

For example:

{
  "ACCOUNT": [{"children": "Revenue"}],
  "PERIOD": [{"children": "Q1"}],
  "ENTITY": ["North America"]
}

can resolve hierarchy operators such as:

children
descendants
idescendants
parent
ancestors
siblings
bottom
uda

into actual member sets.

Cube Variables are also resolved before execution where they appear in the POV.

Omitted Dimensions

A POV does not need to explicitly contain every cube dimension.

Internally, omitted dimensions resolve to an implied member:

Configured default member
        │
        ├── exists ──► use default
        │
        └── absent ──► use dimension top

However, result shape depends on whether that resolution came from a configured default.

Dimensions explicitly selected in the POV appear in the output.

An omitted dimension with a configured default member also appears in the output.

An omitted dimension without a configured default is resolved internally at the dimension top but does not appear as an output column. Result shape therefore consists of the dimensions named in the POV plus dimensions introduced through configured defaults.

For the full default-member behavior, including top-member substitution and operator expansion, see Point of View (POV).

2. Determine the Permitted Scope

After resolving the POV, Casabase Cube determines the leaf-level data the current user is permitted to access.

When row-level security applies, this happens before aggregation and calculation.

Conceptually:

Resolved Member Set
        │
        ▼
Current User
        │
        ▼
Security Rules
        │
        ▼
Permitted Leaf Scope

Everything after this step operates only within the permitted scope.

This ordering is the core security guarantee of the calculation engine.

Opt-In Security

Casabase Cube row-level security is opt-in per user.

Filtering applies when:

1. Security is enabled on at least one dimension of the cube.
2. The user has at least one active security rule on the cube.

A user with no active security rules remains unrestricted by Casabase Cube row-level security.

Once security applies, the resulting permitted scope is used consistently for aggregation and formula evaluation.

See Security Model and Access Control for the complete model.

Partial Rollups

Security does not necessarily remove ancestors of permitted members.

A restricted user can still see an ancestor above their granted branch, but its value is calculated only from the permitted leaves.

For example:

True hierarchy:

Promotions               94,503
├── No Promotion         90,866
├── Coupon                1,247
├── Newspaper Ad          1,171
└── Temporary Price Red.  1,219

A user granted only Coupon can see:

Promotions    1,247
Coupon        1,247

The ancestor remains visible, but the value is a partial rollup over the user’s permitted scope.

This means the same member label can legitimately have different values for different users.

3. Aggregate

Once the permitted leaf set has been established, Casabase Cube performs the requested multidimensional aggregation.

Parent values are derived dynamically from leaf data.

Conceptually:

Permitted Leaf Data
        │
        ▼
Hierarchy Structure
        │
        +
Consolidation Operators
        │
        +
Time-Balance Metadata
        │
        ▼
Aggregated Values

Consolidation operators determine how child members contribute to their parents.

Depending on the model, a member can contribute by:

• Addition
• Subtraction
• Exclusion

This allows hierarchies to represent structures such as netting and eliminations directly.

Dynamic Aggregation

Casabase Cube does not depend on stored aggregate values.

Parent values are derived at query time from the stored leaf data.

As a result:

• There is no aggregation processing window.
• Newly loaded fact data is immediately reflected in queries.
• Fact-data changes do not require a hierarchy rebuild.
• Dimension metadata changes do require the affected structure to be rebuilt.
• Storage does not multiply with hierarchy depth.

This is one of the fundamental architectural differences from traditional pre-aggregated OLAP systems.

Time-Balance Processing

Aggregation can also incorporate configured time-balance behavior.

Time-balance settings are part of the multidimensional model rather than something every query must restate.

Depending on the member configuration, time aggregation can represent behavior such as:

Flow measure
    │
    └── Sum periods


Closing balance
    │
    └── Use closing period


Opening balance
    │
    └── Use opening period


Average balance
    │
    └── Average periods

This behavior belongs to the calculation engine and is not reproduced by simply running SUM(AMT) over the secure fact view.

4. Calculate

After the permitted scope and required aggregations have been established, member formulas are evaluated.

Calculated members do not contain stored fact values. Their formulas are resolved dynamically when those members participate in a query.

Conceptually:

Aggregated / Base Values
        │
        ▼
Formula Dependencies
        │
        ▼
Formula Solve Order
        │
        ▼
Cross-Dimension Solve Order
        │
        ▼
Calculated Result

Formulas can reference stored members, other calculated members, members in other dimensions, and hierarchy-aware functions.

Formula Dependencies

Calculated members can depend on other calculated members.

Casabase Cube resolves those dependencies automatically.

Circular dependencies are rejected rather than being allowed to loop indefinitely at query time.

Detailed dependency and solve-order behavior is documented in Solve Order & Dependencies.

Security and Formula Evaluation

A formula evaluates against the same permitted data scope as a direct query.

A formula cannot be used to bypass row-level security.

For example, if a formula explicitly references a member the user cannot access, the formula does not return that member’s real value.

The authoritative architecture guide documents a tested case in which a restricted user’s formula reference to a denied member returned no restricted data, while the same formula returned the correct value when pointed at a granted member.

Conceptually:

Formula Reference
       │
       ▼
Permitted Scope
       │
       ├── Member permitted ──► value available
       │
       └── Member denied ─────► restricted value unavailable

Formulas are therefore security-scoped, not disabled.

Formulas and Partial Scope

Security-safe does not mean that every user receives the same calculated value.

A formula operating on an aggregate can use a partial rollup for a restricted user.

For example, a ratio or share-of-total calculation can produce a different result because the restricted user’s numerator or denominator is calculated only from permitted leaves.

The result is mathematically consistent with that user’s visible scope, but it can differ from the value seen by a fully scoped user.

This is an important architectural distinction:

Security leak
    │
    └── No


Scope-dependent calculation
    │
    └── Yes

5. Return, Write, or Refuse

After resolution, security, aggregation, and calculation, the engine does one of three things:

Return Result

Write Result

or:

Refuse Query

Casabase Cube does not intentionally substitute an approximate result when the requested calculation cannot be performed correctly.

Returning Results

When output_table is NULL, QUERY_CUBE returns the result directly.

For example:

CALL CUBE.QUERY_CUBE(
    'ASOSAMP',
    '{
        "MEASURES": ["Units"],
        "TIME": [{"children": "Qtr1"}],
        "YEARS": ["Curr Year"]
    }',
    NULL,
    NULL
);

can return:

MEASURES   TIME   YEARS       AMT
---------  -----  ----------  -----
Units      Jan    Curr Year   42228
Units      Feb    Curr Year   20841
Units      Mar    Curr Year   31434

Result Shape

The result is relational.

The returned columns consist of the resolved output dimensions plus:

AMT

For example:

MEASURES
TIME
YEARS
AMT

A narrower POV generally results in a narrower relational result.

Again, configured default members can cause an omitted dimension to participate in and appear in the output.

Writing Results

QUERY_CUBE can also materialize a result into a reporting table.

Conceptually:

POV
 │
 ▼
Calculation Engine
 │
 ▼
Complete Result
 │
 ▼
Replace Target Table

The operation is full replacement rather than incremental append.

The architecture guide establishes a useful guarantee:

A materializing query either replaces the table completely or fails. Consumers do not read a half-written result.

Materialization Changes the Security Boundary

During execution, the materialized result is calculated under the security scope of the identity running the query.

After the result is written, however, the target is an ordinary Snowflake table.

At that point:

Cube security

no longer dynamically re-evaluates for every reader.

Instead:

Snowflake table grants

control access to the materialized result.

This means a single materialized table should not normally be used to serve audiences that require different cube-level security scopes.

See Data Storage & Boundaries.

Refusal Over Approximation

Some query requests cannot be resolved correctly under the supported calculation model.

Examples from the authoritative architecture include:

• Unsupported formula or calculation constructs
• A calculated-member selection beyond supported scale
• Missing dimensional context required for time-balance behavior

In those cases, the engine returns an error describing the condition rather than silently producing an approximate result.

Applications should therefore surface query errors to users rather than converting them into blank or misleading results.

Always Current

Dynamic aggregation means there is no stored aggregation state to refresh after new fact data arrives.

Conceptually:

Load New Fact Data
       │
       ▼
Next Query
       │
       ▼
New Data Included

There is no separate:

Load Data
   │
   ▼
Process Cube
   │
   ▼
Rebuild Aggregates

step.

Only changes to dimension metadata require the relevant dimension structure to be rebuilt.

Two Query Architectures

Casabase Cube provides two fundamentally different ways to read cube information.

Calculation Engine

Use:

QUERY_CUBE
QUERY_CUBE_PIVOT_JSON

when the result depends on the multidimensional model.

The engine provides:

• Hierarchy-aware aggregation
• Member formulas
• Time intelligence
• POV member-selection semantics
• Row-level security

Secure Views

Use:

<CUBE>_FACT_SECURE
<CUBE>_DIMENSIONS

when the consumer needs relational access.

The secure views provide:

• Stored leaf-level fact data
• Dimension and hierarchy metadata
• Standard SQL access
• Security filtering

They do not evaluate member formulas or apply the calculation engine’s hierarchy and time-balance logic.

Calculation Engine vs. Secure Fact View

The distinction is important.

Suppose a hierarchy contains:

Total Product
├── Product A
└── Product B

Using:

CALL CUBE.QUERY_CUBE(...);

to request:

Total Product

asks the multidimensional engine to calculate that member according to the hierarchy and its model metadata.

Using:

SELECT SUM(AMT)
FROM <CUBE>_FACT_SECURE;

asks Snowflake SQL to sum the stored rows selected by the SQL statement.

These can represent different calculation semantics.

Use the engine when you need the cube’s number.

Use the secure fact view when you need the stored facts and intend to provide the relational calculation logic yourself.

Pivoted Query Architecture

QUERY_CUBE_PIVOT_JSON uses the same underlying multidimensional query semantics as QUERY_CUBE.

The difference is result shape.

Conceptually:

POV
 │
 ▼
Same Cube Query Engine
 │
 ▼
Calculated Multidimensional Result
 │
 ▼
Pivot Selected Dimensions
 │
 ▼
Structured JSON

Security, hierarchy resolution, aggregation, and formulas are not bypassed simply because the result is pivoted.

See Querying with SQL for usage.

Saved Queries

A Saved Query stores a reusable POV.

It does not introduce a different calculation engine.

Conceptually:

Saved Query
     │
     ▼
Stored POV
     │
     ▼
QUERY_CUBE

The same query architecture therefore applies to:

• Interactive queries
• Saved Queries
• Scheduled Saved Queries

For interactive execution, security reflects the executing user.

For scheduled materialization, security reflects the identity under which the scheduled query executes.

Query Behavior You Can Rely On

Several behaviors are part of the query contract.

Results Reflect Current Fact Data

There is no stored aggregate to refresh.

Result Shape Follows the Resolved POV

Output contains explicitly selected dimensions plus those introduced by configured defaults.

Security Is User-Specific

The same query can return different permitted results for users with different security scopes.

Calculations Respect Security

Formula evaluation cannot retrieve data outside the executing user’s permitted scope.

Unsupported Requests Fail Explicitly

The engine refuses requests it cannot calculate correctly.

Materialized Writes Are Complete

A failed materialization does not intentionally expose a partially replaced result table.

Architectural Flow

Putting the entire process together:

              QUERY REQUEST
                   │
                   ▼
         Resolve POV Selections
                   │
                   ▼
       Resolve Default Dimensions
                   │
                   ▼
        Determine User Security
                   │
                   ▼
         Permitted Leaf Scope
                   │
                   ▼
        Dynamic Aggregation
                   │
          ┌────────┴────────┐
          │                 │
          ▼                 ▼
  Hierarchy Rules     Time-Balance Rules
          │                 │
          └────────┬────────┘
                   ▼
         Evaluate Formulas
                   │
                   ▼
            Final Result
                   │
        ┌──────────┼──────────┐
        │          │          │
        ▼          ▼          ▼
     Return      Materialize  Refuse

That sequence is the core Casabase Cube query architecture.

Key Principles

• A query describes the desired multidimensional POV, not the physical execution plan.
• The cube configuration determines the underlying data and aggregation behavior.
• POV selections are resolved before query calculation.
• Default members can introduce omitted dimensions into the result.
• Security scope is established before aggregation and formulas.
• Restricted users can receive partial ancestor rollups.
• Parent values are dynamically aggregated from stored leaf data.
• Member formulas are evaluated dynamically.
• Formula calculations operate only on the user’s permitted scope.
• Results can be returned directly or materialized.
• Materialized tables become normal Snowflake security objects after they are written.
• Secure views provide relational access, not full cube calculation semantics.
• Unsupported requests are refused rather than approximated.

Related Topics

• High-Level Architecture
• Data Storage & Boundaries
• Hierarchy Capabilities
• Formula Capabilities
• Security Model
• Integration Points
• Point of View (POV)
• Querying with SQL