Architecture

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:

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

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

Security & Access

Operational Data

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

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

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

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:

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.