Dremio Blog

26 minute read · February 27, 2026

Analyze Financial Services Data with Dremio Cloud

Alex Merced Alex Merced Head of DevRel, Dremio
Start For Free
Analyze Financial Services Data with Dremio Cloud
Copied to clipboard

Table of Contents

Analyze Financial Services Data with Dremio Cloud

What You'll Build
Step 1: Sign Up for Dremio Cloud
Step 2: Create Folders and Seed Data
Accounts Table (Core Banking)
Transactions Table
KYC Records Table (Compliance)
Why Apache Iceberg Matters for Financial Data
Iceberg vs. Federated: When to Use Which
Step 3: Build Bronze Views
Step 4: Build Silver Views
Account Activity
Account Compliance View
Step 5: Build Gold Views
Account Risk Profile
Transaction Anomalies
Step 6: Enable AI-Generated Wikis and Tags
Step 7: Ask Questions with the AI Agent
Next Steps

Financial institutions deal with data spread across core banking systems, market data feeds, and compliance databases. A risk analyst checking whether an account shows suspicious activity needs data from all three. Building ETL pipelines to consolidate everything into one warehouse takes months and introduces data staleness that regulators won't accept.

This tutorial shows you how to build a financial analytics pipeline on Dremio Cloud in 30 minutes. You'll seed sample banking, market, and compliance data, model it into a medallion architecture, and use the AI Agent to detect transaction anomalies and assess account risk through natural language questions.

What You'll Build

  • Source tables simulating a core banking system (Oracle), market data store (S3), and compliance database (PostgreSQL)
  • Bronze views that standardize types and naming across all sources
  • Silver views that join account activity with compliance records
  • Gold views for account risk profiling and transaction anomaly detection
  • AI-generated wikis and tags for semantic context
  • Natural language financial analytics using Dremio's AI Agent

Try Dremio’s Interactive Demo

Explore this interactive demo and see how Dremio's Intelligent Lakehouse enables Agentic AI

Step 1: Sign Up for Dremio Cloud

Go to dremio.com/get-started and create a free trial account. You get 30 days and $400 in compute credits.

Your trial includes a project, an Open Catalog built on Apache Polaris, and Dremio-managed storage. Navigate to the SQL Runner from the left sidebar to start executing SQL.

Step 2: Create Folders and Seed Data

Create a folder structure that represents the different systems a financial institution typically operates:

CREATE FOLDER IF NOT EXISTS finservexp;
CREATE FOLDER IF NOT EXISTS finservexp.oracle;
CREATE FOLDER IF NOT EXISTS finservexp.s3;
CREATE FOLDER IF NOT EXISTS finservexp.postgres;

Note: Tables in the built-in Open Catalog use folder.subfolder.table_name without a catalog prefix. External sources use source_name.schema.table_name.

Accounts Table (Core Banking)

CREATE TABLE finservexp.oracle.accounts (
  account_id INT,
  customer_name VARCHAR,
  account_type VARCHAR,
  balance DECIMAL(12,2),
  currency VARCHAR,
  branch VARCHAR,
  opened_date DATE,
  status VARCHAR
);

INSERT INTO finservexp.oracle.accounts VALUES
(1001,'Alice Chen','checking',45200.50,'USD','New York','2021-03-15','active'),
(1002,'Alice Chen','savings',120000.00,'USD','New York','2021-03-15','active'),
(1003,'Bob Martinez','checking',8750.25,'USD','Miami','2022-06-20','active'),
(1004,'Carol Smith','checking',67300.00,'GBP','London','2020-11-01','active'),
(1005,'Carol Smith','investment',350000.00,'GBP','London','2020-11-01','active'),
(1006,'David Kim','checking',12400.75,'USD','San Francisco','2023-01-10','active'),
(1007,'Eva Johnson','savings',3200.00,'USD','Chicago','2023-05-22','active'),
(1008,'Frank Liu','checking',89500.00,'USD','New York','2019-08-14','active'),
(1009,'Grace Patel','checking',23100.50,'GBP','London','2022-03-01','active'),
(1010,'Henry Brown','savings',5600.00,'USD','Miami','2023-06-18','active'),
(1011,'Iris Wang','checking',145000.00,'USD','San Francisco','2020-12-05','active'),
(1012,'Jack Taylor','investment',210000.00,'GBP','London','2021-07-09','active'),
(1013,'Karen Lee','checking',78200.00,'USD','New York','2019-09-25','active'),
(1014,'Leo Garcia','savings',4100.00,'USD','Miami','2023-08-14','active'),
(1015,'Mia Nguyen','checking',31500.75,'USD','Chicago','2022-01-30','active'),
(1016,'Nathan Wilson','checking',92000.00,'GBP','London','2020-10-12','active'),
(1017,'Olivia Thomas','savings',7800.00,'USD','Miami','2023-09-05','active'),
(1018,'Paul Anderson','investment',480000.00,'USD','New York','2018-07-20','active'),
(1019,'Quinn Davis','checking',15600.50,'USD','Chicago','2022-02-14','active'),
(1020,'Rachel Moore','savings',9200.00,'GBP','London','2023-10-01','active'),
(1021,'Sam Jackson','checking',56700.00,'USD','New York','2019-06-30','active'),
(1022,'Tina White','checking',18900.25,'USD','San Francisco','2022-04-25','active'),
(1023,'Uma Harris','investment',290000.00,'GBP','London','2021-11-11','active'),
(1024,'Victor Clark','savings',6500.00,'USD','Miami','2023-03-17','active'),
(1025,'Wendy Lewis','checking',42000.00,'USD','Chicago','2022-05-08','active'),
(1026,'Xavier Walker','checking',110000.00,'USD','New York','2019-08-22','active'),
(1027,'Yara Hall','savings',3800.00,'GBP','London','2023-06-30','active'),
(1028,'Zach Allen','checking',73500.00,'USD','San Francisco','2020-11-15','active'),
(1029,'Amy Young','investment',175000.00,'USD','Chicago','2021-07-20','active'),
(1030,'Brian King','savings',8100.00,'GBP','London','2023-08-25','active'),
(1031,'Chloe Wright','checking',95000.00,'USD','New York','2020-12-10','active'),
(1032,'Derek Scott','checking',21300.50,'USD','Miami','2022-09-15','active'),
(1033,'Elena Green','investment',320000.00,'GBP','London','2021-01-05','active'),
(1034,'Felix Adams','savings',4500.00,'USD','San Francisco','2023-10-20','active'),
(1035,'Gina Baker','checking',28700.75,'USD','Chicago','2022-02-28','active'),
(1036,'Hugo Nelson','checking',88000.00,'GBP','London','2019-09-10','active'),
(1037,'Ivy Carter','savings',5200.00,'USD','Miami','2023-11-05','active'),
(1038,'Jake Mitchell','investment',410000.00,'USD','New York','2018-07-15','active'),
(1039,'Kira Perez','checking',14200.50,'USD','Chicago','2022-04-12','active'),
(1040,'Liam Roberts','savings',6800.00,'USD','Miami','2023-05-30','active'),
(1041,'Maya Turner','checking',67500.00,'USD','San Francisco','2020-10-25','active'),
(1042,'Noah Phillips','checking',19500.25,'GBP','London','2022-06-14','active'),
(1043,'Olga Campbell','investment',260000.00,'USD','New York','2019-08-05','active'),
(1044,'Pete Parker','savings',3900.00,'USD','Miami','2023-07-28','active'),
(1045,'Rosa Evans','checking',33200.00,'USD','Chicago','2022-08-10','active'),
(1046,'Sean Edwards','checking',105000.00,'GBP','London','2020-11-20','active'),
(1047,'Tara Collins','savings',7100.00,'USD','Miami','2023-09-22','active'),
(1048,'Uri Stewart','investment',390000.00,'USD','San Francisco','2019-12-30','active'),
(1049,'Vera Sanchez','checking',16800.50,'GBP','London','2022-01-18','active'),
(1050,'Will Morris','savings',5400.00,'USD','Chicago','2023-10-15','active');

Transactions Table

CREATE TABLE finservexp.oracle.transactions (
  txn_id INT,
  account_id INT,
  txn_date DATE,
  txn_type VARCHAR,
  amount DECIMAL(12,2),
  merchant VARCHAR,
  channel VARCHAR
);

INSERT INTO finservexp.oracle.transactions VALUES
(1,1001,'2024-06-01','debit',1200.00,'Office Supplies Inc','online'),
(2,1001,'2024-06-01','debit',8500.00,'Wire Transfer','branch'),
(3,1001,'2024-06-02','credit',15000.00,'Payroll Deposit','ach'),
(4,1001,'2024-06-02','debit',950.00,'Restaurant Group','pos'),
(5,1001,'2024-06-03','debit',12000.00,'Wire Transfer','online'),
(6,1003,'2024-06-01','debit',250.00,'Grocery Store','pos'),
(7,1003,'2024-06-02','credit',3200.00,'Payroll','ach'),
(8,1003,'2024-06-03','debit',180.00,'Gas Station','pos'),
(9,1004,'2024-06-01','debit',3500.00,'IT Services Ltd','online'),
(10,1004,'2024-06-02','debit',4200.00,'Wire Transfer','branch'),
(11,1004,'2024-06-02','credit',22000.00,'Client Payment','ach'),
(12,1004,'2024-06-03','debit',890.00,'Restaurant','pos'),
(13,1006,'2024-06-01','debit',150.00,'Coffee Shop','pos'),
(14,1006,'2024-06-02','credit',4500.00,'Payroll','ach'),
(15,1006,'2024-06-03','debit',2100.00,'Rent Payment','ach'),
(16,1008,'2024-06-01','debit',5600.00,'Investment Transfer','online'),
(17,1008,'2024-06-01','debit',3200.00,'Wire Transfer','branch'),
(18,1008,'2024-06-02','credit',28000.00,'Client Payment','ach'),
(19,1008,'2024-06-02','debit',7800.00,'Wire Transfer','online'),
(20,1008,'2024-06-03','debit',4100.00,'Wire Transfer','branch'),
(21,1011,'2024-06-01','debit',2300.00,'SaaS Subscription','online'),
(22,1011,'2024-06-02','credit',35000.00,'Revenue','ach'),
(23,1011,'2024-06-03','debit',18000.00,'Vendor Payment','ach'),
(24,1013,'2024-06-01','debit',1800.00,'Office Lease','ach'),
(25,1013,'2024-06-02','credit',12000.00,'Client Payment','ach'),
(26,1013,'2024-06-03','debit',6500.00,'Equipment Purchase','online'),
(27,1016,'2024-06-01','debit',4200.00,'Legal Services','online'),
(28,1016,'2024-06-02','debit',9500.00,'Wire Transfer','branch'),
(29,1016,'2024-06-02','credit',45000.00,'Revenue','ach'),
(30,1016,'2024-06-03','debit',7200.00,'Contractor Payment','ach'),
(31,1018,'2024-06-01','debit',15000.00,'Securities Purchase','online'),
(32,1018,'2024-06-02','debit',22000.00,'Wire Transfer','online'),
(33,1018,'2024-06-02','credit',80000.00,'Dividend Income','ach'),
(34,1018,'2024-06-03','debit',35000.00,'Wire Transfer','branch'),
(35,1021,'2024-06-01','debit',890.00,'Restaurant','pos'),
(36,1021,'2024-06-02','credit',8500.00,'Payroll','ach'),
(37,1021,'2024-06-03','debit',2200.00,'Auto Payment','ach'),
(38,1026,'2024-06-01','debit',6700.00,'Wire Transfer','online'),
(39,1026,'2024-06-01','debit',4300.00,'Vendor Payment','ach'),
(40,1026,'2024-06-02','credit',42000.00,'Revenue','ach'),
(41,1026,'2024-06-02','debit',8900.00,'Wire Transfer','branch'),
(42,1026,'2024-06-03','debit',5100.00,'Wire Transfer','online'),
(43,1028,'2024-06-01','debit',3400.00,'SaaS Tools','online'),
(44,1028,'2024-06-02','credit',18000.00,'Client Payment','ach'),
(45,1028,'2024-06-03','debit',7600.00,'Payroll','ach'),
(46,1031,'2024-06-01','debit',4800.00,'Marketing Agency','online'),
(47,1031,'2024-06-02','credit',32000.00,'Revenue','ach'),
(48,1031,'2024-06-03','debit',11000.00,'Office Renovation','branch'),
(49,1038,'2024-06-01','debit',25000.00,'Securities Purchase','online'),
(50,1038,'2024-06-02','credit',95000.00,'Portfolio Returns','ach');

KYC Records Table (Compliance)

CREATE TABLE finservexp.postgres.kyc_records (
  kyc_id INT,
  account_id INT,
  verification_status VARCHAR,
  risk_rating VARCHAR,
  last_verified DATE,
  next_review DATE,
  flagged_reason VARCHAR
);

INSERT INTO finservexp.postgres.kyc_records VALUES
(1,1001,'verified','medium','2024-01-15','2025-01-15',NULL),
(2,1002,'verified','low','2024-01-15','2025-01-15',NULL),
(3,1003,'verified','low','2024-03-20','2025-03-20',NULL),
(4,1004,'verified','medium','2024-02-10','2025-02-10',NULL),
(5,1005,'verified','medium','2024-02-10','2025-02-10',NULL),
(6,1006,'verified','low','2024-04-01','2025-04-01',NULL),
(7,1007,'verified','low','2024-05-15','2025-05-15',NULL),
(8,1008,'under_review','high','2023-08-14','2024-08-14','high_volume_wire_transfers'),
(9,1009,'verified','low','2024-06-01','2025-06-01',NULL),
(10,1010,'verified','low','2024-06-18','2025-06-18',NULL),
(11,1011,'verified','medium','2024-01-05','2025-01-05',NULL),
(12,1012,'verified','medium','2024-07-09','2025-07-09',NULL),
(13,1013,'verified','medium','2024-02-25','2025-02-25',NULL),
(14,1014,'verified','low','2024-08-14','2025-08-14',NULL),
(15,1015,'verified','low','2024-03-30','2025-03-30',NULL),
(16,1016,'verified','high','2024-01-12','2025-01-12','large_international_transfers'),
(17,1017,'verified','low','2024-09-05','2025-09-05',NULL),
(18,1018,'under_review','high','2023-07-20','2024-07-20','unusual_investment_patterns'),
(19,1019,'verified','low','2024-04-14','2025-04-14',NULL),
(20,1020,'verified','low','2024-10-01','2025-10-01',NULL),
(21,1021,'verified','medium','2024-02-28','2025-02-28',NULL),
(22,1022,'verified','low','2024-04-25','2025-04-25',NULL),
(23,1023,'verified','medium','2024-11-11','2025-11-11',NULL),
(24,1024,'verified','low','2024-03-17','2025-03-17',NULL),
(25,1025,'verified','low','2024-05-08','2025-05-08',NULL),
(26,1026,'under_review','high','2024-01-22','2025-01-22','rapid_wire_transfer_pattern'),
(27,1027,'verified','low','2024-06-30','2025-06-30',NULL),
(28,1028,'verified','medium','2024-02-15','2025-02-15',NULL),
(29,1029,'verified','medium','2024-07-20','2025-07-20',NULL),
(30,1030,'verified','low','2024-08-25','2025-08-25',NULL),
(31,1031,'verified','medium','2024-01-10','2025-01-10',NULL),
(32,1032,'verified','low','2024-09-15','2025-09-15',NULL),
(33,1033,'verified','medium','2024-03-05','2025-03-05',NULL),
(34,1034,'verified','low','2024-10-20','2025-10-20',NULL),
(35,1035,'verified','low','2024-04-28','2025-04-28',NULL),
(36,1036,'verified','high','2024-02-10','2025-02-10','cross_border_high_value'),
(37,1037,'verified','low','2024-11-05','2025-11-05',NULL),
(38,1038,'under_review','high','2023-07-15','2024-07-15','high_frequency_securities_trading'),
(39,1039,'verified','low','2024-04-12','2025-04-12',NULL),
(40,1040,'verified','low','2024-05-30','2025-05-30',NULL),
(41,1041,'verified','medium','2024-01-25','2025-01-25',NULL),
(42,1042,'verified','low','2024-06-14','2025-06-14',NULL),
(43,1043,'verified','medium','2024-02-05','2025-02-05',NULL),
(44,1044,'verified','low','2024-07-28','2025-07-28',NULL),
(45,1045,'verified','low','2024-08-10','2025-08-10',NULL),
(46,1046,'verified','high','2024-03-20','2025-03-20','large_international_transfers'),
(47,1047,'verified','low','2024-09-22','2025-09-22',NULL),
(48,1048,'verified','medium','2024-01-30','2025-01-30',NULL),
(49,1049,'verified','low','2024-04-18','2025-04-18',NULL),
(50,1050,'verified','low','2024-10-15','2025-10-15',NULL);

Why Apache Iceberg Matters for Financial Data

Financial data has specific requirements that Apache Iceberg addresses directly.

ACID Transactions. Financial records cannot tolerate partial writes. If a batch of transactions fails midway through an insert, Iceberg ensures no partial data is committed. The table either has all the new records or none of them.

Time Travel for Regulatory Audits. When a regulator asks "What did this account's transaction history look like on March 15th?", you can query the exact state of the table at that timestamp using Iceberg's snapshot history. No backup restoration required.

Automated Performance Management. Dremio handles the operational overhead of maintaining Iceberg tables automatically. It compacts small files, rewrites manifests, clusters data by frequently filtered columns, and vacuums expired snapshots. For transaction tables that grow by millions of rows per month, this prevents the gradual performance degradation that plagues manually maintained data lakes.

Autonomous Reflections. Risk queries against transaction tables tend to follow patterns: same time windows, same account filters, same aggregations. Dremio detects these patterns and automatically creates pre-computed Reflections stored as Iceberg tables. The query optimizer substitutes them transparently, turning multi-second scans into sub-second lookups without any manual intervention.

Iceberg vs. Federated: When to Use Which

In this tutorial, all tables are Iceberg because they're in the Open Catalog. In production, the decision depends on how fresh the data needs to be.

Keep data federated when real-time accuracy matters. A core banking system processes transactions throughout the day. If you replicate transaction data to Iceberg on a nightly ETL schedule, your risk analysts spend the morning working with yesterday's numbers. By querying Oracle directly through Dremio's federation, every query sees the latest committed transactions. You can still create manual Reflections on those federated views and choose a refresh cadence (hourly, every 15 minutes) that balances performance against freshness.

Migrate data to Iceberg when Dremio's automated performance management provides clear value. Historical transaction archives, aggregated compliance reports, and end-of-day snapshots are strong candidates. These datasets don't need sub-minute freshness, and Iceberg gives them automatic compaction, manifest optimization, clustering, vacuuming, and eligibility for Autonomous Reflections.

The practical approach for financial services: federate your operational systems for real-time visibility, then materialize analytical and historical datasets into Iceberg for automated optimization.

Step 3: Build Bronze Views

Bronze views create a consistent interface over the raw source tables. In financial services, consistency matters because audit queries fail when column names or date formats vary between systems. A single Bronze layer means every downstream query references the same schema regardless of whether the underlying data comes from Oracle, S3, or PostgreSQL.

Create the layer folders:

CREATE FOLDER IF NOT EXISTS finservexp.bronze;
CREATE FOLDER IF NOT EXISTS finservexp.silver;
CREATE FOLDER IF NOT EXISTS finservexp.gold;

This view standardizes the account data from core banking. The date cast ensures consistent timestamp handling across all downstream joins.

CREATE VIEW finservexp.bronze.bank_accounts AS
SELECT
  account_id,
  customer_name,
  account_type,
  balance,
  currency,
  branch,
  CAST(opened_date AS TIMESTAMP) AS opened_timestamp,
  status
FROM finservexp.oracle.accounts;

The transactions view casts the date and keeps all fields. The merchant and channel fields will be critical for anomaly detection in the Gold layer, where patterns like "multiple wire transfers via online channel" signal potential compliance issues.

CREATE VIEW finservexp.bronze.bank_transactions AS
SELECT
  txn_id,
  account_id,
  CAST(txn_date AS TIMESTAMP) AS txn_timestamp,
  txn_type,
  amount,
  merchant,
  channel
FROM finservexp.oracle.transactions;

The KYC view casts both date fields to timestamps. The next_review date is especially important: we'll compute a review_overdue flag in the Silver layer to catch accounts that haven't been recertified on schedule.

CREATE VIEW finservexp.bronze.compliance_kyc AS
SELECT
  kyc_id,
  account_id,
  verification_status,
  risk_rating,
  CAST(last_verified AS TIMESTAMP) AS last_verified_timestamp,
  CAST(next_review AS TIMESTAMP) AS next_review_timestamp,
  flagged_reason
FROM finservexp.postgres.kyc_records;

Step 4: Build Silver Views

Silver views join data across systems to create business-level entities. In financial services, this means combining what an account looks like (banking system) with what it's doing (transactions) and whether it's compliant (KYC).

Account Activity

This view answers: "What is each account doing?" It joins accounts with transactions and computes aggregate metrics like total debits, total credits, wire transfer count, and online debit count. These metrics are the raw signals that the Gold layer uses for risk scoring. The wire_transfer_count is particularly important because frequent wire transfers are a common AML (Anti-Money Laundering) indicator.

CREATE VIEW finservexp.silver.account_activity AS
SELECT
  a.account_id,
  a.customer_name,
  a.account_type,
  a.balance,
  a.currency,
  a.branch,
  COUNT(t.txn_id) AS txn_count,
  COALESCE(SUM(CASE WHEN t.txn_type = 'debit' THEN t.amount ELSE 0 END), 0) AS total_debits,
  COALESCE(SUM(CASE WHEN t.txn_type = 'credit' THEN t.amount ELSE 0 END), 0) AS total_credits,
  SUM(CASE WHEN t.channel = 'online' AND t.txn_type = 'debit' THEN 1 ELSE 0 END) AS online_debit_count,
  SUM(CASE WHEN t.merchant LIKE '%Wire Transfer%' THEN 1 ELSE 0 END) AS wire_transfer_count
FROM finservexp.bronze.bank_accounts a
LEFT JOIN finservexp.bronze.bank_transactions t ON a.account_id = t.account_id
GROUP BY a.account_id, a.customer_name, a.account_type, a.balance, a.currency, a.branch;

Account Compliance View

This view answers: "Is each account compliant?" It joins accounts with KYC records and computes a review_overdue flag. An account with an overdue KYC review is a regulatory risk even if its transaction patterns are normal. By computing this in the Silver layer, the Gold view can combine both behavioral and compliance signals into a single risk score.

CREATE VIEW finservexp.silver.account_compliance AS
SELECT
  a.account_id,
  a.customer_name,
  a.account_type,
  a.balance,
  a.branch,
  k.verification_status,
  k.risk_rating,
  k.last_verified_timestamp,
  k.next_review_timestamp,
  k.flagged_reason,
  CASE
    WHEN k.next_review_timestamp < CURRENT_TIMESTAMP THEN true
    ELSE false
  END AS review_overdue
FROM finservexp.bronze.bank_accounts a
LEFT JOIN finservexp.bronze.compliance_kyc k ON a.account_id = k.account_id;

Step 5: Build Gold Views

Gold views are the final analytical layer that compliance officers, risk managers, and the AI Agent query directly. Each Gold view pre-computes a specific business decision so the end user doesn't need to know which tables to join or what thresholds to apply.

Account Risk Profile

This is the primary view for compliance teams. It combines transaction behavior from account_activity with KYC status from account_compliance to produce a composite_risk_level. The classification logic uses both data sources: an account rated "high" by KYC with 3+ wire transfers becomes "Critical," while a "medium" KYC rating with 2+ wire transfers is "Elevated." This layered approach catches risks that neither system would flag independently.

CREATE VIEW finservexp.gold.account_risk_profile AS
SELECT
  act.account_id,
  act.customer_name,
  act.account_type,
  act.balance,
  act.branch,
  act.txn_count,
  act.total_debits,
  act.total_credits,
  act.wire_transfer_count,
  comp.risk_rating AS kyc_risk_rating,
  comp.verification_status,
  comp.review_overdue,
  comp.flagged_reason,
  CASE
    WHEN comp.risk_rating = 'high' AND act.wire_transfer_count >= 3 THEN 'Critical'
    WHEN comp.risk_rating = 'high' OR comp.review_overdue = true THEN 'High'
    WHEN comp.risk_rating = 'medium' AND act.wire_transfer_count >= 2 THEN 'Elevated'
    WHEN comp.risk_rating = 'medium' THEN 'Moderate'
    ELSE 'Low'
  END AS composite_risk_level
FROM finservexp.silver.account_activity act
JOIN finservexp.silver.account_compliance comp ON act.account_id = comp.account_id;

Transaction Anomalies

This view surfaces individual transactions that warrant review. It flags transactions that are large relative to the account balance (over 25%), high-value wire transfers (over $5,000), or large online transactions (over $10,000). The WHERE clause filters to only include transactions above $5,000 or wire transfers above $3,000, so the view stays focused on actionable items rather than listing every transaction.

CREATE VIEW finservexp.gold.transaction_anomalies AS
SELECT
  t.txn_id,
  t.account_id,
  a.customer_name,
  t.txn_timestamp,
  t.txn_type,
  t.amount,
  t.merchant,
  t.channel,
  a.balance,
  CASE
    WHEN t.amount > a.balance * 0.25 THEN 'large_relative_to_balance'
    WHEN t.merchant LIKE '%Wire Transfer%' AND t.amount > 5000 THEN 'high_value_wire'
    WHEN t.channel = 'online' AND t.amount > 10000 THEN 'large_online_transaction'
    ELSE 'normal'
  END AS anomaly_flag
FROM finservexp.bronze.bank_transactions t
JOIN finservexp.bronze.bank_accounts a ON t.account_id = a.account_id
WHERE t.amount > 5000
  OR (t.merchant LIKE '%Wire Transfer%' AND t.amount > 3000);

Step 6: Enable AI-Generated Wikis and Tags

  1. Click Admin in the left sidebar, then go to Project Settings.
  2. Select the Preferences tab.
  3. Scroll to the AI section and enable Generate Wikis and Labels.
  4. Go to the Catalog and navigate to your Gold views under finservexp.gold.
  5. Click the Edit button (pencil icon) next to the desired view.
  6. In the Details tab, find the Wiki section and click Generate Wiki. Do the same for the Tags section by clicking Generate Tags.
  7. Repeat for each Gold view.

Dremio's AI will produce descriptions like: "account_risk_profile: One row per account combining transaction activity metrics with KYC compliance status. The composite_risk_level field classifies accounts from Low to Critical based on KYC risk rating, wire transfer frequency, and review compliance status."

To enhance the generated wiki, copy its output into the AI Agent on the homepage and ask it to produce an improved version in a markdown code block with additional context. For example, you might add that "Critical accounts require same-day review by the compliance team." Copy the Agent's refined output and paste it back into the wiki editor.

This context is what enables the AI Agent to understand that when you ask about "high-risk accounts," you're referring to the composite_risk_level column, not just the kyc_risk_rating.

Step 7: Ask Questions with the AI Agent

Navigate to the Homepage and use the AI Agent:

"Which accounts have a Critical risk level?"

The Agent queries finservexp.gold.account_risk_profile and returns accounts where high KYC risk overlaps with frequent wire transfers.

"Show me a chart of total debits vs. credits by branch."

The Agent generates a grouped bar chart from the account activity data. This immediately highlights branches with unusual debit-to-credit ratios.

"List all transaction anomalies flagged as high_value_wire, ordered by amount."

The Agent queries finservexp.gold.transaction_anomalies, filters by the flag, and returns a sorted table. In a production environment, this query would help compliance analysts prioritize their reviews.

"Create a pie chart showing the distribution of accounts by composite risk level."

The Agent generates a visualization showing how many accounts fall into each risk category, giving compliance leadership a quick overview of the portfolio's risk profile.

Next Steps

  • Connect real banking data. Replace the simulated folders with federated connections to your actual Oracle, PostgreSQL, or S3 sources.
  • Add FGAC policies. Mask account balances and customer names for non-compliance users using Fine-Grained Access Control.
  • Use Iceberg time travel. Query historical snapshots for regulatory reporting and audit trail requirements.
  • Connect BI tools. Push the Gold views to Tableau or Power BI via Arrow Flight SQL for formal dashboards.

The financial analytics pipeline you built here runs entirely on open standards. Apache Iceberg stores the data. Apache Arrow powers the engine. Apache Polaris underpins the catalog. Your data is portable, governed, and ready for production.

Try Dremio Cloud free for 30 days and build financial analytics on your own data.

Try Dremio Cloud free for 30 days

Deploy agentic analytics directly on Apache Iceberg data with no pipelines and no added overhead.

Start For Free

Article Topics

Product Insights from the Dremio Blog