The End of Manual Rebalancing: How to Build an Agentic Lakehouse in 15 Minutes

(This blog covers this example; you can just create a fresh Dremio account and run this SQL to see this example in action, also see the end result in the 1 minute video below)

The Data Burden: Moving Beyond the "Stale Data" Paradigm

The traditional portfolio rebalancing workflow is broken. We are currently witnessing a structural collapse of the wall between data engineering and real-time decision-making. The "old way", characterized by manual spreadsheets, brittle ETL pipelines, and disconnected data silos, is no longer sustainable. When market conditions shift in minutes, relying on data that takes hours to aggregate leaves firms reactive and exposed to risk.

The solution isn't just better dashboards; it’s the Agentic Lakehouse. As defined by Dremio, this is the first data platform built for and managed by AI agents. It unifies data access and delivers autonomous performance, creating an environment where AI not only displays data but also acts upon it. Today, I’ll show you how to transcend the "data burden" and go from a fresh sign-up to AI-powered autonomous insights in just 15 minutes using Dremio’s Semantic Layer and SQL Engine.

2. Step 1: Claim Your Stake in the Cloud

The journey to an Agentic Lakehouse begins with a "Zero-ETL" philosophy. We are moving away from the era of moving and copying data into expensive, proprietary warehouses.

1. Sign Up: Visit the Dremio Cloud page to start your Free Trial.
2. Choose Your Cloud: Dremio is provider-agnostic, available on AWS (Azure Coming Soon to Dremio Cloud), and any environment is available on Dremio Enterprise Self-Managed Deployments.
3. Connect Your Data: Point Dremio to your S3 or Azure Data Lake Storage (ADLS).

The speed here is the selling point. Because Dremio uses a "Zero-effort" query engine, you aren't waiting for a data migration project to finish; you are querying your raw files immediately.

3. The Architecture: Why Medallion Matters for Portfolios

To turn raw data into financial intelligence, we utilize the Medallion Architecture. This isn't just a naming convention; it’s a framework for data integrity.

• Bronze (Raw): The landing zone where data arrives in its native state.
• Silver (Conformed): This is the "Architect’s Layer." In a portfolio context, this is where we normalize disparate ticker symbols, handle currency conversions from global exchanges, and calculate the math needed for analysis.
• Gold (Curated): The high-value layer where business logic resides. This is the "AI-Ready" zone.

As the source context highlights, Dremio “unifies data access, delivers autonomous performance, and enables organizations to accelerate AI and analytics at unprecedented speed and efficiency, without the complexity of traditional data warehouses.”

4. Step 2: Setting the Foundation (The Bronze Layer)

In the Bronze layer, we land our raw asset values. We aren't worried about formatting yet, we just want the data "in."

FULL SQL HERE

Raw Landing Zone

/*
 * Asset Management Portfolio Rebalancing
 * 
 * Scenario:
 * Comparing actual portfolio allocations against target weights to identify rebalancing needs.
 * 
 * Architecture: Medallion (Bronze -> Silver -> Gold)
 */

-------------------------------------------------------------------------------
-- 0. SETUP
-------------------------------------------------------------------------------
CREATE FOLDER IF NOT EXISTS RetailDB;
CREATE FOLDER IF NOT EXISTS RetailDB.AssetMgmt;
CREATE FOLDER IF NOT EXISTS RetailDB.AssetMgmt.Bronze;
CREATE FOLDER IF NOT EXISTS RetailDB.AssetMgmt.Silver;
CREATE FOLDER IF NOT EXISTS RetailDB.AssetMgmt.Gold;

-------------------------------------------------------------------------------
-- 1. BRONZE LAYER
-------------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS RetailDB.AssetMgmt.Bronze.Portfolios (
    PortfolioID INT,
    AssetClass VARCHAR,
    CurrentValue DOUBLE,
    TargetWeight DOUBLE -- Percentage (e.g., 0.40 for 40%)
);

INSERT INTO RetailDB.AssetMgmt.Bronze.Portfolios VALUES
-- Portfolio 1: Aggressive
(1, 'US Equity', 550000, 0.50),
(1, 'Intl Equity', 320000, 0.30),
(1, 'Bonds', 100000, 0.15),
(1, 'Cash', 30000, 0.05),
-- Portfolio 2: Conservative
(2, 'US Equity', 200000, 0.20),
(2, 'Intl Equity', 100000, 0.10),
(2, 'Bonds', 650000, 0.60),
(2, 'Cash', 150000, 0.10),
-- Portfolio 3: Balanced
(3, 'US Equity', 420000, 0.40),
(3, 'Intl Equity', 200000, 0.20),
(3, 'Bonds', 350000, 0.35),
(3, 'Cash', 50000, 0.05);
-- (12 rows total)

Architect’s Note: This SQL lands the raw market values (in absolute dollars) and the target percentages for each asset class (US Equities, Bonds, etc.). By landing the raw dollar value here, we maintain a single source of truth for downstream calculations beyond just rebalancing.

5. Step 3: From Raw Data to Analysis (The Silver Layer)

In the Silver layer, we transform these raw dollars into a holistic business concept. We need to know the Actual Weight of each asset class relative to the total portfolio value.

-------------------------------------------------------------------------------
-- 2. SILVER LAYER
-------------------------------------------------------------------------------

CREATE OR REPLACE VIEW RetailDB.AssetMgmt.Silver.AllocationAnalysis AS
SELECT 
    PortfolioID,
    AssetClass,
    CurrentValue,
    TargetWeight,
    SUM(CurrentValue) OVER (PARTITION BY PortfolioID) AS TotalPortfolioValue
FROM RetailDB.AssetMgmt.Bronze.Portfolios;

Architect’s Note: Notice the use of the SUM(...) OVER (PARTITION BY PortfolioID) window function. This is where the magic happens. It allows us to calculate the total portfolio value (the denominator) across individual asset rows simultaneously. We are converting raw financial data into a "conformed" view that standardizes how we measure "Actual Weight" across a diverse global portfolio.

6. Step 4: Automating the Trigger (The Gold Layer)

The Gold layer is where we embed the business rules that trigger action. We aren't just looking at data; we are defining "Risk."

-------------------------------------------------------------------------------
-- 3. GOLD LAYER
-------------------------------------------------------------------------------

CREATE OR REPLACE VIEW RetailDB.AssetMgmt.Gold.RebalanceTriggers AS
SELECT 
    PortfolioID,
    AssetClass,
    CurrentValue,
    (CurrentValue / TotalPortfolioValue) AS ActualWeight,
    TargetWeight,
    ((CurrentValue / TotalPortfolioValue) - TargetWeight) AS Drift,
    CASE 
        WHEN ABS((CurrentValue / TotalPortfolioValue) - TargetWeight) > 0.05 THEN 'REBALANCE REQUIRED'
        ELSE 'On Track'
    END AS Status
FROM RetailDB.AssetMgmt.Silver.AllocationAnalysis;

Architect’s Note: This layer calculates "Drift." By flagging any deviation greater than 5% as REBALANCE REQUIRED, we have made the data "AI-ready." While we use a CASE statement here, an advanced implementation could use Dremio’s AI_CLASSIFY function to categorize the reason for the drift (e.g., market volatility vs. a specific sector crash) based on unstructured news feeds, making the signal even more actionable for an agent.

7. Step 5: Unleashing the AI Agent

Now we reach the "Agentic" phase. Dremio's AI Semantic Layer provides a governed, business-friendly view of this data. AI agents don't need to understand your table joins; they just need to ask a question.

The Prompt:

"List all asset classes where Status is 'REBALANCE REQUIRED'."

Visionary Insight: This is a game-changer. An AI agent, utilizing Dremio’s AI Agent, can interpret this governed view from the semantic layer and autonomously surface risk. Instead of an analyst spending hours digging through reports, the agent can scan thousands of portfolios in seconds, identify outliers, and even draft rebalancing trades. This is the "Semantic" advantage: translating natural language into high-performance SQL. This SQL can even be created with the help of Dremio AI Agent, so it doesn't just take advantage of the Semantic Layer but can help you create it, creating a lakehouse built for AI agents by AI agents.

8. The Performance Secret: Why It’s So Fast

AI agents have a "latency intolerance." If a query takes 30 seconds, the agentic loop breaks, and the autonomous workflow fails. The Agentic Lakehouse solves this with three core technologies:

• Apache Iceberg: The high-performance, open-source table format that provides the foundation for our analytical data.
• Reflections: This is Dremio’s unique query acceleration. Reflections provide the sub-second response times needed for Autonomous Rebalancing at the speed of the market.
• Apache Polaris: In financial services, security is non-negotiable. Polaris provides the RBAC (Role-Based Access Control) and unified metadata management. It ensures that an AI agent sees only the portfolios it is authorized to view, providing a secure, governed perimeter for your AI initiatives.

9. Conclusion: The Future is Agentic

We have traveled from a raw cloud sign-up to a fully functional, AI-driven risk alert in mere minutes. By shifting the burden of data preparation and normalization to the Medallion-structured Lakehouse, we have liberated the analyst.

Data is no longer a static resource for human eyes; the Agentic Lakehouse is about building an ecosystem where AI can act on data as effectively as we do. The question is no longer whether you can store your data, but whether your data is ready for use. Final Thought: If your data could talk to an AI agent today, would it be ready to answer the most critical questions about your business's health?