OpenAI dbt Package

This dbt package transforms data from Fivetran's OpenAI Platform/Enterprise connector into analytics-ready tables.

Resources

• Number of materialized models¹: 49
• Connector documentation
  • OpenAI connector documentation
• dbt package documentation
  • GitHub repository
  • dbt Docs
  • DAG
  • Changelog
• dbt Core™ supported versions
  • >=1.3.0, <3.0.0

What does this dbt package do?

This package enables you to analyze OpenAI Platform spend, usage, and Codex Enterprise productivity across your organization. It creates enriched models with metrics focused on daily cost by project and model, per-user activity across every OpenAI product, Codex Enterprise coding productivity, and per-user usage summaries.

This package is also designed to roll up alongside Fivetran's Claude dbt package into the AI Reporting package, which combines Claude and OpenAI data into unified, cross-vendor AI usage and cost reporting models.

Output schema

Final output tables are generated in the following target schema:

<your_database>.<connector/schema_name>_openai_reports

Final output tables

By default, this package materializes the following final tables:

Table	Description
openai__cost_usage_report	Daily OpenAI Platform spend and token usage by project and model. One row per source relation, day, project, and model, plus one org-level row per source relation/day for cost that can't be attributed to a project (cost_type = 'other', e.g. aggregate feature charges like "Assistants API", or token-type cost with no matching completion volume). Cost is inferred by applying an implied per-token rate (Costs endpoint spend ÷ completion token volume) to each project's token share, since the Costs endpoint doesn't report a project breakdown directly. Still useful with only openai__using_cost or only openai__using_completion enabled — see Additional configurations. Example Analytics Questions: Which projects or models are driving the most spend day over day? How does token usage compare across projects and models over time? How much cost can't be attributed to a specific project or model?
openai__enterprise_user_report	Daily OpenAI Platform activity by user, project, model, and product (completions, embeddings, audio transcription, audio speech, image generation, moderation, web search, and file search). Cost is not available at this grain — the Costs endpoint doesn't report per-user attribution. Example Analytics Questions: Which users are the heaviest consumers of a given product or model? How is usage distributed across products (completions, embeddings, image, etc.) day to day? Which projects have the most active users?
openai__code_report	Daily Codex Enterprise productivity (lines of code added/removed, threads, turns) plus credit and token consumption, one row per source relation, day, and user. Per-model and per-speed detail is rolled up onto this grain rather than fanned out into separate rows. Example Analytics Questions: Who are the most active Codex users by lines of code or threads per day? How much credit and token volume is Codex consuming per person over time? Is Codex usage trending up or down across the organization?
openai__user_summary	One row per source relation and user, with organization role, project membership, project-level custom roles, API key ownership, invite status, and all-time/month-to-date usage totals. Example Analytics Questions: Which users belong to the most projects or hold the most custom roles? Who has been most active this month versus all time? Which users haven't been active recently? Which users own the most API keys, or haven't accepted their invite yet?
openai__compliance_cost_report	Daily ChatGPT Enterprise (Compliance Platform) spend by user, product, surface, model, and SKU. Only its codex-product rows cover the same activity as openai__code_report, and from a cost angle rather than that report's productivity angle — see Opinionated Modelling Decisions for the double-counting caution if you query both. Example Analytics Questions: Which users or products are driving the most ChatGPT Enterprise spend? How does spend break down by surface (web, desktop, API) or client? Which SKUs or service tiers make up the bulk of Compliance Platform cost?

¹ Each Quickstart transformation job run materializes these models if all components of this data model are enabled. This count includes all staging, intermediate, and final models materialized as view, table, or incremental.

---

Prerequisites

To use this dbt package, you must have the following:

• At least one Fivetran OpenAI Platform/Enterprise connection syncing data into your destination.
• A BigQuery, Snowflake, Redshift, Databricks, PostgreSQL, or DuckDB destination.

How do I use the dbt package?

You can either add this dbt package in the Fivetran dashboard or import it into your dbt project:

• To add the package in the Fivetran dashboard, follow our Quickstart guide.
• To add the package to your dbt project, follow the setup instructions below.

Install the package (skip if also using the ai_reporting combo package)

Include the following openai package version in your packages.yml file if you are not also using the upstream AI Reporting combination package:

TIP: Check dbt Hub for the latest installation instructions or read the dbt docs for more information on installing packages.

packages:
  - package: fivetran/openai
    version: [">=0.1.0", "<0.2.0"]

Define database and schema variables

Option A: Single connection

By default, this package runs using your destination and the openai schema. If this is not where your OpenAI data is (for example, if your OpenAI schema is named openai_fivetran), add the following configuration to your root dbt_project.yml file:

vars:
    openai_database: your_destination_name
    openai_schema: your_schema_name

Option B: Union multiple connections

If you have multiple OpenAI connections in Fivetran and would like to use this package on all of them simultaneously, we have provided functionality to do so. For each source table, the package will union all of the data together and pass the unioned table into the transformations. The source_relation column in each model indicates the origin of each record.

To use this functionality, you will need to set the openai_sources variable in your root dbt_project.yml file:

# dbt_project.yml

vars:
  openai:
    openai_sources:
      - database: connection_1_destination_name # Required
        schema: connection_1_schema_name # Required
        name: connection_1_source_name # Required only if following the step in the following subsection

      - database: connection_2_destination_name
        schema: connection_2_schema_name
        name: connection_2_source_name

Optional: Incorporate unioned sources into DAG

If you use Fivetran Transformations for dbt Core™ and are unioning multiple OpenAI connections, you can define your sources in a property .yml file, using this as a template. Set the variable has_defined_sources: true under the OpenAI namespace in your dbt_project.yml. Otherwise, your OpenAI connections won't appear in your DAG. See the union_connections macro documentation for full configuration details.

Disable models for non-existent sources

This step is optional if you are unioning multiple connections together in the previous step. The union_connections macro will create empty staging models for sources that are not found in any of your OpenAI schemas/databases. However, you can still leverage the below variables if you would like to avoid this behavior.

This package takes into consideration that not every OpenAI Platform/Enterprise account syncs every source table, and allows you to disable the corresponding functionality for all tables except users: cost, completion, embedding, audio_transcription, audio_speech, image, moderation, web_search_call, file_search_call, codex_usage, codex_usage_model, project, project_api_key, project_user, project_user_role, users_role, invite, compliance_cost (which covers the compliance_costs_organization_log, compliance_costs_organization_log_billing, and compliance_costs_log_file tables together), and compliance_users. users is the spine of openai__user_summary with no partial-value alternative, so stg_openai__users and openai__user_summary always build.

By default, all of these variables are assumed to be true. Add variables for only the tables you want to disable:

vars:
    openai__using_cost:                  False   # Disable if you are not syncing the cost table
    openai__using_completion:            False   # Disable if you are not syncing the completion table
    openai__using_embedding:             False   # Disable if you are not syncing the embedding table
    openai__using_audio_transcription:   False   # Disable if you are not syncing the audio_transcription table
    openai__using_audio_speech:          False   # Disable if you are not syncing the audio_speech table
    openai__using_image:                 False   # Disable if you are not syncing the image table
    openai__using_moderation:            False   # Disable if you are not syncing the moderation table
    openai__using_web_search_call:       False   # Disable if you are not syncing the web_search_call table
    openai__using_file_search_call:      False   # Disable if you are not syncing the file_search_call table
    openai__using_codex_usage:           False   # Disable if you are not syncing the codex_usage table
    openai__using_codex_usage_model:     False   # Disable if you are not syncing the codex_usage_model table
    openai__using_project:               False   # Disable if you are not syncing the project table
    openai__using_project_api_key:       False   # Disable if you are not syncing the project_api_key table
    openai__using_project_user:          False   # Disable if you are not syncing the project_user table
    openai__using_project_user_role:     False   # Disable if you are not syncing the project_user_role table
    openai__using_users_role:            False   # Disable if you are not syncing the users_role table
    openai__using_invite:                False   # Disable if you are not syncing the invite table
    openai__using_compliance_cost:       False   # Disable if you are not syncing Compliance Platform cost data
    openai__using_compliance_users:      False   # Disable if you are not syncing the Compliance Platform users table

(Optional) Additional configurations

Expand/Collapse details

Partial cost and code reporting

openai__cost_usage_report and openai__code_report are each built from two optional source-table pairs, and each model still produces useful (differently-shaped) output when only one side of its pair is enabled:

• openai__cost_usage_report uses cost and completion. With only openai__using_cost enabled, the model falls back to cost by day, project, model, and token_unit_type (token_quantity and num_model_requests are null, since only completion reports token counts). With only openai__using_completion enabled, the cost, currency, and cost_attribution_method columns drop entirely and only usage remains.
• openai__code_report uses codex_usage and codex_usage_model. codex_usage reports its own day-level credit/token totals, so those columns are present whenever either table is enabled — only count_models_used requires openai__using_codex_usage_model specifically. With openai__using_codex_usage disabled, actor_email and the productivity columns (lines of code, threads, turns) drop entirely, since only codex_usage resolves the email or reports that activity.

Disabling one table in a pair doesn't disable the whole model — it just drops the columns that table alone can supply. See the column descriptions in models/openai.yml for the full breakdown of which columns depend on which variable.

Estimated Codex cost

openai__code_report reports Codex credits, but the source data has no universal credits-to-currency conversion rate. If you know your organization's rate, set it to get an estimated estimated_cost_amount column, in whatever currency your rate is denominated in:

vars:
    openai__code_report_credit_rate: 0.01   # currency amount per credit

This column is omitted entirely (not nulled) when the variable isn't set.

Inferred cost attribution

The OpenAI Costs API doesn't report a project-level cost breakdown. To still provide per-project spend, openai_cost in openai__cost_usage_report is inferred: this package derives an implied per-token rate from the Costs endpoint's daily spend per model and unit type, then applies that rate to each project's share of that day's token volume (from completion). Cost that can't be tied back to a matching model/day of completion volume — or that isn't token-based to begin with (e.g. aggregate feature charges like "Assistants API") — lands in a cost_type = 'other' row so that sum(openai_cost) still ties out to the total in the source cost table.

Model family and variant

The cost and enterprise user reports retain the original model and add two parsed columns:

model	model_family	model_variant
gpt-4o-mini-2024-07-18	gpt-4o	mini
gpt-5.2-codex	gpt-5.2	codex
gpt-6-astra	gpt-6	astra
o4-mini	o4	mini
o3-deep-research	o3	deep-research

Parsing trims and lowercases names, unwraps fine-tuned ft: names, and removes a trailing YYYY-MM-DD-shaped snapshot suffix. Numeric GPT and o-series families are structural, so future versions following those conventions work automatically. Known named prefixes such as codex and text-embedding also form families; their remaining suffix stays intact as the variant. Unrecognized names retain the original model as the family and have a null variant. Names without a variant also have a null variant. These fields describe names, not architecture, capabilities, or billing rates.

Model family overrides

model_family (see Model family and variant above) is parsed automatically for known naming patterns, but a new or unrecognized model name falls back to using the full model name as its own family. If OpenAI releases a model whose name doesn't fit those patterns, or you want a specific model to report under a different family than it parses to, set openai_model_family_overrides in your root dbt_project.yml. Each entry's model is the exact model name (as it appears in your data, including any dated snapshot suffix) and family is what you want it to report as:

vars:
  openai_model_family_overrides:
    - model: gpt-4o-mini        # report gpt-4o-mini's own snapshots under their own family, not folded into gpt-4o
      family: gpt-4o-mini
    - model: codex-mini-latest  # rename a specific model's family
      family: codex-mini

model values are trimmed and lowercased, and matched after removing the ft: fine-tune wrapper. Overrides take precedence over the built-in parsing rules and don't change how model_variant is parsed.

Change the source table references

If an individual source table has a different name than the package expects, add the table name as it appears in your destination to the respective variable:

IMPORTANT: See this project's dbt_project.yml variable declarations to see the expected names.

vars:
    openai_<default_source_table_name>_identifier: your_table_name

Changing the Build Schema

By default this package will build the OpenAI staging models within a schema titled (<target_schema> + _openai_staging), and the OpenAI final models within a schema titled (<target_schema> + _openai_reports) in your target database. If this is not where you would like your modeled OpenAI data to be written to, add the following configuration to your root dbt_project.yml file:

models:
    openai:
      +schema: my_new_schema_name # Leave +schema: blank to use the default target_schema.
      staging:
        +schema: my_new_schema_name # Leave +schema: blank to use the default target_schema.

Passthrough metrics

This package includes all source columns defined in the macros folder by default. However, if you have data unique to your OpenAI account that isn't already included in the staging models, you can bring it in with a passthrough metric variable:

vars:
    openai__cost_passthrough_metrics: []
    openai__completion_passthrough_metrics: []
    openai__codex_usage_passthrough_metrics: []
    openai__codex_usage_model_passthrough_metrics: []
    openai__compliance_cost_passthrough_metrics: []
    openai__compliance_cost_billing_passthrough_metrics: []

Each variable brings in columns from one source table and feeds them into a specific end model, aggregated as needed to reach that model's grain:

Variable	Source table	End model	Aggregation
openai__cost_passthrough_metrics	cost	openai__cost_usage_report	Summed
openai__completion_passthrough_metrics	completion	openai__cost_usage_report	Summed
openai__codex_usage_passthrough_metrics	codex_usage	openai__code_report	None (already at report grain)
openai__codex_usage_model_passthrough_metrics	codex_usage_model	openai__code_report	Summed
openai__compliance_cost_passthrough_metrics	compliance_costs_organization_log	openai__compliance_cost_report	None (constant per event; read with max)
openai__compliance_cost_billing_passthrough_metrics	compliance_costs_organization_log_billing	openai__compliance_cost_report	Summed

They all accept the same format, supporting datatype casting, aliasing, and custom transformations:

vars:
  openai__completion_passthrough_metrics:
    - name: "field_id"
      alias: "field_name"
      transform_sql: "cast(field_name as int64)"
    - name: "another_field_name"

name is required and is the column name as it appears in the raw source table. alias and transform_sql are optional — alias renames the output column, and transform_sql provides a custom SQL expression instead of a plain passthrough. If both alias and transform_sql are set, transform_sql should reference the alias, not the raw name — the column has already been renamed to its alias by the time transform_sql runs.

Please create an issue if you'd like to see passthrough column support for other tables in the OpenAI schema.

Source casing for case-sensitive destinations

By default, the package applies case-insensitive comparisons when resolving source_relation values. If your destination is case-sensitive and you want downstream transformations to respect the exact casing of your source database and schema names, set the following variable:

vars:
    fivetran_using_source_casing: true

(Optional) Orchestrate your models with Fivetran Transformations for dbt Core™

Expand for details

Fivetran offers the ability for you to orchestrate your dbt project through Fivetran Transformations for dbt Core™. Learn how to set up your project for orchestration through Fivetran in our Transformations for dbt Core setup guides.

Does this package have dependencies?

This dbt package is dependent on the following dbt packages. These dependencies are installed by default within this package. For more information on the following packages, refer to the dbt hub site.

IMPORTANT: If you have any of these dependent packages in your own packages.yml file, we highly recommend that you remove them from your root packages.yml to avoid package version conflicts.

packages:
    - package: fivetran/fivetran_utils
      version: [">=0.4.12", "<0.5.0"]

    - package: dbt-labs/dbt_utils
      version: [">=1.0.0", "<2.0.0"]

How is this package maintained and can I contribute?

Package Maintenance

The Fivetran team maintaining this package only maintains the latest version of the package. We highly recommend you stay consistent with the latest version of the package and refer to the CHANGELOG and release notes for more information on changes across versions.

Contributions

A small team of analytics engineers at Fivetran develops these dbt packages. However, the packages are made better by community contributions.

We highly encourage and welcome contributions to this package. Learn how to contribute to a package in dbt's Contributing to an external dbt package article.

Opinionated Modelling Decisions

This dbt package takes an opinionated stance on a few points worth knowing before you build on top of it — most notably, openai__compliance_cost_report and openai__code_report both cover Codex activity from different angles (spend versus productivity), and summing spend/credits across the two double-counts Codex. See the DECISIONLOG for this and the rest of our opinionated modeling choices, along with the reasoning behind them.

Are there any resources available?

• If you have questions or want to reach out for help, see the GitHub Issue section to find the right avenue of support for you.
• If you would like to provide feedback to the dbt package team at Fivetran or would like to request a new dbt package, fill out our Feedback Form.