portfoliq-dbt — Star Schema dbt Package

Install the portfolIQ dbt package to access crypto market data, DeFi TVL, and AI analysis via a direct PostgreSQL connection. Not financial advice.

portfoliq-dbt

portfoliq-dbt is a dbt package (coming soon) that will materialize the portfolIQ Star Schema — 7 dimensions, 10 facts, and 4 satellites — directly in your warehouse. The Star Schema is already queryable today via direct SQL read-only access (Growth+). Once the dbt package is published, you will be able to connect any BI tool that supports PostgreSQL or ODBC — including Power BI, Tableau, Metabase, Lightdash, or Cube.dev — without writing transformations from scratch.

Note: portfolIQ does not ship certified native connectors for Power BI or Tableau. Connection is via standard PostgreSQL (ODBC/JDBC). See SQL Direct Access for step-by-step instructions per BI tool.

Available on Growth plan and above. SQL read-only credentials are required. See SQL Direct Access to provision your credentials.

Not financial advice. Not a fatwa. Methodology disclosed. All data is factual and descriptive. No price targets, no investment recommendations, no buy/sell signals.

What's included

Quickstart (5 minutes)

1. Get your SQL read-only credentials

Subscribe to the Growth plan, then provision credentials via the API:

curl -X POST https://api.portfoliq.io/v1/billing/sql-credentials \
  -H "X-API-Key: YOUR_API_KEY"

Save the returned password immediately — it cannot be retrieved again. Full instructions: SQL Direct Access.

2. Install the package

Add to your packages.yml:

packages:
  - package: portfoliq/portfoliq-dbt
    version: 0.1.0

Then run:

dbt deps

3. Configure the source connection

Copy profiles.yml.example from the package and fill in your credentials:

# profiles.yml
portfoliq:
  outputs:
    dev:
      type: postgres
      host: db.portfoliq.io
      port: 5433
      database: portfoliq
      schema: star_public
      user: sql_user_xxxxxxxx
      password: <from POST /v1/billing/sql-credentials>
      sslmode: require
  target: dev

4. Enable Star Schema and run

# dbt_project.yml (your project)
vars:
  portfoliq_enable_star: true

dbt run
dbt test

5. Query as usual

-- Top 10 assets by market cap (BTC and ETH have full coverage in v1)
SELECT
  a.ticker,
  a.name,
  m.price_consensus_usd,
  m.market_cap_derived_usd,
  m.snapshot_date
FROM {{ ref('fact_market_snapshot') }} m
JOIN {{ ref('dim_asset') }} a ON a.asset_sk = m.asset_sk AND a.is_current
WHERE m.snapshot_date = CURRENT_DATE - 1
ORDER BY m.market_cap_derived_usd DESC NULLS LAST
LIMIT 10;

Available models

Dimensions

Facts

Satellites (public pass-through views)

Feature flag: portfoliq_enable_star

The package ships with a feature flag. When set to false, all models compile but return empty result sets — useful for dry-runs, CI pipelines, or restricted environments:

# dbt_project.yml
vars:
  portfoliq_enable_star: false  # models compile, nothing materializes

Reset to true to resume normal operation.

BI tools compatibility

The Star Schema in star_public is exposed as a read-only PostgreSQL endpoint (db.portfoliq.io:5433). Any BI tool with a PostgreSQL connector or ODBC/JDBC support can connect. portfolIQ does not ship certified native connectors — connection is via the standard PostgreSQL protocol.

Validated via standard PostgreSQL connection:

• Power BI Desktop — PostgreSQL connector (Get Data), Import or DirectQuery mode
• Tableau Desktop — Connect → To a Server → PostgreSQL
• Metabase — Add database → PostgreSQL, select star_public schema
• Lightdash — dbt-native, point at this package's profiles.yml
• Cube.dev — PostgreSQL data source, use as semantic layer

See SQL Direct Access for step-by-step connection instructions per tool.

From REST to BI in 10 minutes

If you're starting from the REST API and want to move to direct SQL + dbt:

From REST to BI — step-by-step guide covering credentials, first dbt run, and your first Power BI report.

Prerequisite: Growth+ subscription

SQL access and the portfoliq-dbt package require a Growth or Enterprise plan (scope star:agg).

• View pricing
• Provision SQL credentials

On the free tier, all REST endpoints remain accessible — the dbt package requires a direct PostgreSQL connection.

Performance & pre-aggregates

The portfolIQ Star Schema models in v0.1.0 are exposed as views pass-through on the star_public schema — they do not materialize continuous aggregates (caggs) client-side. The continuous aggregates referenced in the Terms of Service are maintained by portfolIQ upstream (TimescaleDB caggs on sat_asset_vwap_consensus 1h/1d), so queries against fact_vwap_consensus already benefit from them via the underlying source.

If your BI tool requires materialized tables for performance (Power BI Desktop without DirectQuery, Tableau extract mode), use the dbt feature flag and your own materialization config:

# Your client dbt_project.yml
vars:
  portfoliq_enable_star: true
models:
  portfoliq:
    +materialized: table  # instead of view (default)

Future versions of this package will ship native continuous aggregates as incremental models for the high-volume facts (fact_market_snapshot, fact_vwap_consensus).

Roadmap

License

ELv2 (Elastic License v2). See NOTICE.md and LICENSE in the package.

Commercial use within your own infrastructure is permitted. Redistribution of the package itself requires written consent.

Need help?

• Status page: status.portfoliq.io
• Email: hello@portfoliq.io
• GitHub: github.com/portfoliq/portfoliq-dbt

Disclaimer

Not financial advice. Not a fatwa. Methodology disclosed. Data provided for informational purposes only. Sources: DeFiLlama (MIT licence), public RPC endpoints (Blockstream, Ethereum public nodes), BigQuery public data (MIT), Anthropic Claude API. No CoinGecko data redistributed as-is. No Yahoo Finance. No TradingView.