Oracle FDI- Fusion Data Intelligence: Setting up Data Build Tool (dbt)

As Oracle customers increasingly modernise their analytics stack, combining Fusion Data Intelligence (FDI) with transformation frameworks like dbt (Data Build Tool) provides a powerful, scalable, and governed analytics architecture.

This blog outlines how to set up dbt to work with Oracle Fusion Data Intelligence, enabling teams to manage transformations using modular SQL, version control, CI/CD, and best-practice data engineering principles.

Why Use dbt with Fusion Data Intelligence?

Oracle Fusion Data Intelligence provides curated data pipelines, semantic models, and analytics for Oracle Fusion applications. However, organisations often require:

·    Additional transformations

·    Custom business logic

·    Cross-source data modelling

·    Dev/Test/Prod promotion controls

·    Version-controlled SQL transformations

dbt complements FDI by enabling:

·    Modular SQL-based transformations

·    Data lineage and documentation

·    Testing and data quality checks

·    CI/CD integration

·    Git-based version control

Reference Architecture

Typical integration pattern:

1.    FDI loads and curates Fusion application data into Oracle Autonomous Database.

2.    dbt connects to the same database (or replicated schema).

3.    dbt builds transformation layers:

o    Staging

o    Intermediate

o    Mart/Presentation

4.    BI tools consume dbt-generated data models.

In most cases, the backend database is:

Oracle Autonomous Database

Prerequisites

Before setup, ensure:

·    Access to FDI environment

·    Access to Autonomous Database (wallet or TLS connection)

·    Database user with appropriate privileges

·    Python 3.8+

·    Access to Git repository

·    Network connectivity (Private Endpoint or Public based on architecture)

Step 1: Install dbt

Install dbt with Oracle adapter:

pip install dbt-oracle

Verify installation:

dbt --version

Step 2: Configure dbt Profile

Create or update the profiles.yml file:

fdi_project:

  target: dev

  outputs:

    dev:

      type: oracle

      host: <adb-hostname>

      user: <username>

      password: <password>

      port: 1522

      service: <service_name>

      schema: <target_schema>

      threads: 4

If using mTLS, configure wallet-based connection with appropriate parameters.

Step 3: Create dbt Project

Initialize project:

dbt init fdi_transformations

cd fdi_transformations

Project structure:

models/

  staging/

  intermediate/

  marts/

tests/

macros/

Step 4: Build Staging Models

Start with FDI-curated tables.

Example staging model:

-- models/staging/stg_gl_balances.sql

select

    ledger_id,

    period_name,

    currency_code,

    accounted_dr,

    accounted_cr

from fdi_gl_balances

Run:

dbt run

Step 5: Implement Business Transformations

Create mart layer:

-- models/marts/mart_gl_summary.sql

select

    ledger_id,

    period_name,

    sum(accounted_dr - accounted_cr) as net_balance

from {{ ref('stg_gl_balances') }}

group by ledger_id, period_name

Use dbt’s ref() function to manage dependencies and lineage.

Step 6: Add Data Quality Tests

Example:

version: 2

models:

  - name: stg_gl_balances

    columns:

      - name: ledger_id

        tests:

          - not_null

Run tests:

dbt test

Step 7: Documentation & Lineage

Generate documentation:

dbt docs generate

dbt docs serve

This provides:

·    Data lineage graph

·    Column-level documentation

Dependency visibility

Deployment Strategy (Recommended)

For enterprise-grade implementation:

Environment Separation

·    DEV – Developer testing

·    TEST – Business validation

·    PROD – Production workload

CI/CD Integration

Use:

·    GitHub Actions / Azure DevOps

·    Automated dbt run + test on PR merge

Schema Strategy

·    FDI schema (read-only)

·    dbt transformation schema

BI consumption schema

Security & Governance Best Practices

·    Use separate database roles

·    Apply least privilege principle

·    Avoid direct transformation in FDI-managed schemas

·    Enable auditing on transformation schema

·    Use private endpoint connectivity if possible

Common Use Cases

Organisations using dbt with FDI typically:

·    Extend Financial reporting models

·    Enrich Procurement and SCM analytics

·    Combine Fusion ERP + external SaaS data

·    Build custom executive dashboards

·    Create reusable enterprise data marts

Benefits to Oracle Customers

By integrating dbt with Fusion Data Intelligence:

✔ Faster development cycles✔ Improved collaboration between IT and Analytics teams✔ Transparent transformation logic✔ Version-controlled SQL✔ Stronger data quality governance

Final Thoughts

Fusion Data Intelligence provides the foundation. dbt adds agility, governance, and engineering discipline to your transformation layer.

For NZOUG members exploring modern data engineering practices within Oracle Cloud, this combination offers a future-ready analytics architecture aligned with industry standards.

If your organisation is implementing this setup, consider starting in a non-production environment and documenting naming standards, transformation layers, and promotion processes early.