Architecture

Estimated reading: 8 minutes

2.1 Platform Overview

Casabase Cube is a Snowflake Native Application. It runs entirely inside your Snowflake account as a first-class database object. There are no external servers, APIs, or cloud services involved.

The application is built on three foundational Snowflake capabilities:

CapabilityHow Casabase Cube Uses It
Native App FrameworkPackages all application logic into a single installable unit distributed via the Snowflake Marketplace or private listings.
Stored ProceduresAll aggregation, hierarchy traversal, formula evaluation, write-back, and security logic executes within your account.
Streamlit in SnowflakeThe entire user interface (Query Builder, Hierarchy Editor, Data Forms, Admin, Audit Log, Help Center) runs natively in Snowsight.

Design Principles

  • Zero data movement: Casabase Cube never copies, replicates, or stages your source data. All queries execute directly against your tables.
  • Metadata-driven: Cube definitions, hierarchies, formulas, security, and saved queries are all stored as configuration metadata, interpreted at query time.
  • Upgrade-safe: Your configuration data persists across application upgrades. Application logic is updated independently without affecting your data or settings.
  • Secure by default: Your data is only accessible when you explicitly grant SELECT to the application. The application operates within its own security context.

2.2 High-Level Architecture

┌─────────────────────────────────────────────────────────────────┐ │ YOUR SNOWFLAKE ACCOUNT │ │ │ │ ┌───────────────────────────────────────────────────────────┐ │ │ │ CASABASE_CUBE (Native App) │ │ │ │ │ │ │ │ ┌───────────────┐ ┌──────────────┐ ┌──────────────┐ │ │ │ │ │ Configuration │ │ Application │ │ Shared Views │ │ │ │ │ │ │ │ Logic │ │ │ │ │ │ │ │ • Cubes │ │ • Procedures │ │ • Hierarchy │ │ │ │ │ │ • Formulas │ │ • Functions │ │ views for │ │ │ │ │ │ • Saved │ │ • Streamlit │ │ external │ │ │ │ │ │ Queries │ │ App │ │ access │ │ │ │ │ │ • Security │ │ │ │ │ │ │ │ │ │ • Audit Log │ │ │ │ │ │ │ │ │ │ • Variables │ │ │ │ │ │ │ │ │ │ • Locks │ │ │ │ │ │ │ │ │ │ • Data Forms │ │ │ │ │ │ │ │ │ └───────────────┘ └──────────────┘ └──────────────┘ │ │ │ └───────────────────────────────────────────────────────────┘ │ │ │ │ │ SELECT (granted) │ │ │ │ │ ▼ │ │ ┌───────────────────────────────────────────────────────────┐ │ │ │ YOUR DATA (your tables) │ │ │ │ │ │ │ │ ┌──────────────┐ ┌───────────────┐ ┌──────────────┐ │ │ │ │ │ Fact Tables │ │ Dimension │ │ Views │ │ │ │ │ │ (measures) │ │ Tables │ │ │ │ │ │ │ │ │ │ (parent-child)│ │ │ │ │ │ │ └──────────────┘ └───────────────┘ └──────────────┘ │ │ │ └───────────────────────────────────────────────────────────┘ │ │ │ │ ┌───────────────────────────────────────────────────────────┐ │ │ │ YOUR VIRTUAL WAREHOUSE │ │ │ │ (all compute runs here) │ │ │ └───────────────────────────────────────────────────────────┘ │ └─────────────────────────────────────────────────────────────────┘

The key takeaway: everything runs inside your Snowflake account. Casabase Cube reads your data where it lives, computes aggregations using your virtual warehouse, and stores only configuration metadata within the application.

2.3 What Casabase Cube Stores

Casabase Cube stores only configuration and operational metadata, never your source data. This metadata falls into three categories:

Cube Configuration

WhatDescription
Cube definitionsWhich dimensions and hierarchies are associated with each cube, and how they map to your source tables.
FormulasCalculated member definitions: expressions, dependencies, and target hierarchies.
Saved queriesReusable query definitions that can be shared across users.
Cube variablesSubstitution variables (e.g., current_year = FY24) for dynamic member references.
Data form layoutsRow/column/POV configurations for structured data entry grids.

Security & Access

WhatDescription
Row-level security grantsWhich users can access which members within specific dimensions.
Write locksWhich dimension members are locked to prevent data entry (e.g., closed periods).

Operational Data

WhatDescription
Audit logA record of all operations (queries, writes, hierarchy changes, admin actions) with timestamps, users, and execution details.
Hierarchy snapshotsPoint-in-time captures of hierarchy state for comparison and review.
Cell commentsUser annotations attached to specific data intersections in write-back grids.
Write-back dataUser-entered values from data forms and write operations. Stored separately from source data and merged at query time.

2.4 How Queries Work

When you run a query in Casabase Cube, whether through the Query Builder UI or via SQL, the application follows this general flow:

  1. You define what you want to see: Select your dimensions, members, and measures using the Query Builder or a SQL procedure call.
  2. The cube resolves your request: Member selections are expanded, formulas are prepared, and the query is built against your source tables.
  3. Your data is queried directly: The application reads from your source tables using your virtual warehouse. No data is copied or staged.
  4. Results are assembled: Aggregations, calculated members, and any security filtering are applied, and the final result set is returned.

Your source data is never modified. No intermediate tables are created.

How Write-Back Works

When you enter data through Data Forms or the WRITE_CUBE procedure:

  1. Validation: The target members are validated, and lock restrictions are checked.
  2. Storage: Values are written to a dedicated write-back table, separate from your source data. Your source data is never modified.
  3. Spreading: If writing to a parent member, values can be distributed across bottom-level members using methods like even distribution or proportional allocation.
  4. Merge at query time: When you query the cube, user-entered values are automatically merged with source data, with user inputs taking priority.
  5. Audit: Every write operation is logged with full details.

2.5 Hierarchy Capabilities

The hierarchy engine transforms your flat parent-child dimension tables into traversable structures that support dynamic roll-up at query time.

Supported Features

FeatureDescription
Ragged hierarchiesBranches of unequal depth. Members can exist at any generation.
Unbalanced hierarchiesSome branches may skip generations.
Shared membersA single member rolls up under multiple parents (e.g., a product in two categories).
Aggregation operators+ (add), (subtract), ~ (no aggregation), set per member.
AliasesDisplay-friendly names shown instead of member keys.
Time BalanceSUM, FIRST, LAST, AVG. Controls how time-series data aggregates across periods.
User Defined Attributes (UDAs)Custom tags attached to members, queryable in formulas via HASUDA().
Sort orderCustom sort ordering for hierarchy display.
Auto-rebuildHierarchies automatically refresh when the source dimension table changes.
Hierarchy snapshotsPoint-in-time captures for comparison and review.

2.6 Formula Capabilities

The formula engine evaluates calculated members at query time. Formulas are defined once and applied dynamically during any query.

Formula Types

TypeExampleDescription
Simple[Revenue] - [Cost of Sales]References other members in the same dimension.
Cross-dimensional[PERIOD].[Jan]References a specific member in another dimension.
Chained cross-dim[YEARS].[FY24]->[PERIOD].[Jan]References an intersection across multiple dimensions.
Hierarchy-aware (IS*)CASE WHEN ISLEAF(PERIOD) THEN {ROW} * 12 ELSE {ROW} ENDAdapts calculation based on a member’s position in the hierarchy.
Row references{ROW}, {TOTAL}, {TOTAL:DIM}Placeholder for the current row value, grand total, or dimensional total.
InlinePassed in POV JSON at query timeAd-hoc formulas that are not persisted.

Formulas support dependencies. A formula can reference other formulas, and Casabase Cube resolves the evaluation order automatically.

2.7 Security Model

Casabase Cube implements security at two levels:

Application Roles

RoleCapabilities
CUBE_ADMINFull access: create/modify cubes, hierarchies, formulas, security grants, cube variables, data forms. Can write data and view audit logs. Inherits CUBE_WRITER and CUBE_PUBLIC.
CUBE_WRITERData entry: use data forms, write cell values, add cell comments. Inherits CUBE_PUBLIC.
CUBE_PUBLICRead-only: run queries via the Query Builder, view saved queries, access Help Center, view audit logs.

Application roles are granted to Snowflake account roles or individual users by the Snowflake administrator (see Section 1.4).

Row-Level Security

For cubes that require fine-grained data access, Casabase Cube supports row-level security per dimension:

  • An administrator defines which members a user can access within a specific dimension.
  • When row-level security is enabled for a hierarchy, queries are automatically filtered to show only authorized members.
  • Users only see data for members they are explicitly granted access to.
  • Security is evaluated per user at query time (requires the READ SESSION privilege).

2.8 Audit & Compliance

Every significant operation in Casabase Cube is logged to an internal audit trail. Each log entry captures:

FieldDescription
Event typeCategory of event (query, write, hierarchy change, admin action, security change, etc.)
UserThe Snowflake user who performed the action
ActionThe specific operation performed
CubeWhich cube was affected
DetailsFull operation details (parameters, values, context)
Execution timeHow long the operation took
StatusWhether the operation succeeded or failed
TimestampWhen the event occurred

Administrators can export audit records for compliance review, purge records based on retention policies, and generate activity summaries.

2.9 Integration Points

Casabase Cube is a Snowflake Native Application, so its entire interface is exposed as stored procedures. Any tool, language, or platform that can connect to Snowflake and execute SQL can access the Casabase Cube engine. There is no proprietary connector or SDK required.

SQL-First Interface

All Casabase Cube operations (querying, write-back, hierarchy management, administration) are callable as standard SQL stored procedure calls:

CALL CASABASE_CUBE.CUBE.QUERY_CUBE('MY_CUBE', 'DB.SCHEMA.TABLE', 'AMT', '{...}', NULL);
CALL CASABASE_CUBE.CUBE.WRITE_CUBE('MY_CUBE', '{...}', 1000.00, 'REPLACE');
CALL CASABASE_CUBE.CUBE.LIST_CUBES();

This means Casabase Cube works natively with:

  • BI, reporting & ETL tools: Any tool with a Snowflake connector (Power BI, Tableau, Looker, Sigma, Alteryx, KNIME, etc.) can call cube procedures and consume results.
  • Spreadsheets: Excel, Google Sheets, or any spreadsheet with ODBC/JDBC connectivity to Snowflake.
  • Programming languages: Python, R, Java, Node.js, .NET, or any language with a Snowflake driver or SDK.
  • Orchestration & automation: Snowflake Tasks, dbt, Airflow, Prefect, or any scheduler that can execute Snowflake SQL.
  • Custom applications: Web apps, internal tools, or microservices that connect to Snowflake via the SQL API, REST API, or any supported driver.

There is no dependency on the Streamlit UI. The UI is a convenience layer; every action available in the interface is also available as a procedure call.

Essbase / Cloud EPM Import

Casabase Cube can import cube metadata (hierarchies and formulas) from Oracle Essbase or Cloud EPM export files staged in Snowflake, enabling migration from legacy EPM platforms.

AI-Powered Help Center

The built-in Help Center uses Snowflake Cortex to provide natural language answers to user questions, powered by a built-in knowledge base.

Share this Doc

Architecture

Or copy link

CONTENTS