dbt Integration with FlexIt

FlexIt integrates with dbt Cloud or dbt CLI to enable 100% metadata-less BI with the best of dbt

• Data Lineage
• Exposures (Dashboard dependencies)
• Joins (Entity Relationships)
• Metrics
• Data freshness
• Data dictionary (Docs)
• Statistics
• Custom meta for formatting, synonyms, business names, joins, time grain and more

Example dbt Project

The dbt-business-intelligence github repo provides a great dbt project to highlight BI integration.

Getting Started

Check out this video to see how a Business Intelligence integration with dbt can enable 100% metadata-less BI

---

This guide assumes that you already have a dbt environment set up, and a FlexIt Analytics instance running. If not, or if you'd like to learn more about either FlexIt or dbt, then check out the links below.

Check out the dbt docs or get dbt

Check out the FlexIt docs or get FlexIt

Connect to your data warehouse

Required

You need to create a data source connection in FlexIt that will connect to the dbt project databases. dbt holds metadata, but that does not include database connection details.

Connecting to dbt

FlexIt reads your dbt project's artifacts (manifest.json, plus catalog.json, run_results.json, and sources.json when available) in one of three ways:

• dbt Cloud - FlexIt pulls artifacts from a dbt Cloud job run through the dbt Cloud API
• dbt Docs Server - FlexIt pulls artifacts from a URL or folder where your generated dbt docs are hosted
• Upload manifest.json - you upload the files manually

To create a data model from any of these, go to the FlexIt Administration panel, select Data Models on the left, click Create from dbt on the top-right, and select the dbt Location. Read more about data models in the Data Modeling guide.

dbt Cloud

1. In dbt Cloud, create a service token with Read-Only permissions. Add Job Admin if you want to start dbt Cloud jobs from FlexIt.
2. In FlexIt, go to Administration > Configuration > Integrations, click Configure next to dbt, and paste the token into Service Token. If your dbt Cloud account is not at https://cloud.getdbt.com, enter its API URL as well. See the Administration Guide.
3. Click Create from dbt, choose dbt Cloud, and select:
   • Account, Project, and Job - the job whose run artifacts describe your models
   • Run - Most Recent Successful Run, Most Recent Run, or a specific run
   • Step - the job step whose run_results.json FlexIt reads for run status and statistics (default 5). Use the step that runs your models, e.g. dbt build.
4. Configure the following settings:
   • Real-time Connection - FlexIt reads dbt properties live instead of from a local copy. When you update a model and run the job, the changes flow through to this FlexIt data model automatically, and the metadata cannot be edited in FlexIt. If disabled, the data model is seeded from the dbt metadata once and can then be edited in FlexIt, which may let dbt and FlexIt drift out of sync.
   • Only show documented - only show models and columns that have an entry in a YAML file.
   • Only show compiled nodes - hide models and columns that were not compiled in the run.

Docs Server

Use this when you run dbt Core (CLI) and host the output of dbt docs generate somewhere FlexIt can reach. See Hosting dbt Docs for setting one up on the FlexIt server itself.

1. Click Create from dbt and choose dbt Docs Server.
2. Enter the Docs URL: the folder that contains manifest.json. This can be:
   • a full URL, e.g. https://docs.example.com/dbt
   • a path on the FlexIt server, e.g. /webcontent/my_project (the default)
3. Configure Real-time Connection, Only show documented, and Only show compiled nodes as described above. With a real-time connection, FlexIt picks up changes whenever manifest.json is updated.

Upload Manifest

Manually upload the manifest.json and (optionally) catalog.json files from your dbt project target folder. Then configure Only show documented and Only show compiled nodes as described above.

Metadata

Once you've connected to a dbt project, you'll be in the FlexIt editor with all the dbt metadata automatically populated. If you selected Real-time Connection for your dbt config, then you cannot make changes in the editor (see more about this in Connecting to dbt). Explore your metadata model and Save when you are ready to create analyses. After saving, you can give access to the metadata model to specific users, groups, and roles.

The numbered markers in the screenshot below point to dbt-specific features in the data model editor:

1. dbt Config - the dbt connection for this data model
2. Relationships tab - joins from dbt relationships tests and meta.joins
3. Source Info - statistics from catalog.json
4. Column Lineage - column-level lineage
5. Convert to Yaml - generate dbt YAML from the FlexIt metadata
6. View Definition - model lineage
7. Dependencies & Migration - reports and dashboards that use a model or column

Reports and Dashboards

Once you've saved your Metadata, you can create new reports and dashboards, or load samples.

Create New

To create a new analysis from your dbt metadata, click on New in the top-left and select New Analysis. Select the data model that you created in Metadata above, and then you'll see a blank report ready to drag-and-drop fields from the right.

Samples

If you used the Example dbt Project from above, then you can load pre-built samples from FlexIt:

1. Navigate to "Administration > Configuration > Backup and Restore"

2. Right-click and "Restore" the samples_sales.zip item

3. Check the "Samples (Sales)" content to restore, deploy content to "Shared Content", select the data model that you created in Metadata above, and click Restore

4. Navigate to the "Shared Content > Samples (Sales)" folder and take a look at the reports and dashboards

Concepts and Standards

Now that you've tried everything out with the sample project and reports, let's dive into the concepts

Data Lineage

FlexIt leverages the lineage feature of dbt to show upstream model, source, and seed dependencies, along with their refresh times and tooltip descriptions. Right-click a model and select View Definition in the data model editor or a report's field list, or View Lineage in a report's data panel.

Column-level lineage

When you host dbt docs with a dbt Job, FlexIt also generates column-level lineage. Right-click a field and select Column Lineage in the data model editor, or View Model Lineage / View Column Lineage in a report's field list. The lineage tab has its own URL, so you can bookmark it or share the link with others.

dbt Explore

For dbt Cloud connections, right-click a model and select Open in dbt Explore to open it in dbt Cloud.

Exposures

To see which FlexIt reports and dashboards depend on a model or column, right-click it in the data model editor and select Dependencies & Migration. This lists dependent content along with any issues, such as broken links when a model column no longer exists, and lets you Migrate reports to another data model.

Joins (Entity Relationships)

Create relationships (joins) between models one of two ways in the dbt schema.yml model definition:

1. Using the relationships test on dbt model columns, which also checks integrity. This always creates an inner join.
2. Using the meta.joins property in the dbt model, for other join types or more complex conditions

models/schema.yml

models:
  - name: fact_sales
    meta:
      joins:
        - to: dim_customer
          type: left # inner, left, right, full; default 'inner'
          join_on:
            - from_field: customer_key
              exp: '=' # optional; default '='; also <, >, <=, >=, <>, !=
              to_field: customer_key
            - from_value: 'Retail' # compare to a literal value instead of a field
              to_field: customer_type
              operator: and # and, or; joins this condition to the previous ones
    columns:
      - name: customer_key
        tests:
          - not_null
          - relationships: # JOINS the dbt integrity way
              to: ref('dim_customer')
              field: customer_key

If a relationships test and meta.joins define a join between the same two models, the relationships test is used.

Relationships defined in the model YAML will appear in FlexIt as shown below. Joins you delete in the FlexIt data model are not re-created when the dbt metadata is reloaded. Re-creating a deleted join in FlexIt brings it back.

Deleted joins are saved as overrides on the data model. When you click dbt Config in the data model editor and choose your models again in Select Models, FlexIt asks what to do with any overrides on the selected models:

• Keep Overrides - deleted joins stay deleted
• Remove Overrides - joins are reset to what the dbt project defines

Metrics

Metrics (aka measures or facts) are numeric values summarized by dimensions. There are three ways to define them.

dbt Semantic Layer measures

Each measure in a dbt semantic_model becomes a metric on the semantic model's underlying model, using the measure's expr (or name) column and agg:

models/semantic_models.yml

semantic_models:
  - name: sales
    model: ref('fact_sales')
    measures:
      - name: quantity
        agg: sum # sum, average, min, max, count, count_distinct
        expr: quantity

To give a measure a label, description, or meta, define a simple dbt metric named after the measure. Other metric types (derived, ratio, cumulative, conversion) are not used by FlexIt.

Column meta.metrics

Define one or more metrics on a model column:

models/schema.yml

models:
  - name: fact_sales
    columns:
      - name: quantity
        meta:
          metrics:
            quantity_total:         # identifier for the metric
              type: sum             # sum, average, count, count_distinct, min, max; default 'sum'
              label: Total Quantity # display label
            quantity_average:
              type: average
            avg_price:
              sql: "sales / quantity" # math using other columns: + - * / ( )

Metrics can also use the meta attributes below, such as format, synonyms, and hidden.

Column meta.aggregate

For a simple metric, set the default aggregation directly on a numeric column:

models/schema.yml

models:
  - name: fact_sales
    columns:
      - name: quantity
        meta:
          aggregate: sum # sum, mean, min, max, count, distinct, none

Data freshness

In reports or dashboards, users can be aware of data freshness for models and sources throughout the full lineage. Field tooltips show the model run status and time, and list any sources whose freshness check warns or errors. If a model or source in the lineage has an error, the report shows a warning icon.

Data dictionary (Docs)

All dbt descriptions in models, columns, metrics, sources, seeds, and more are displayed in FlexIt tooltips. Use markdown in descriptions or Docs Blocks to enable rich html tooltips in FlexIt.

models/schema.yml

models:
  - name: fact_sales
    description: "{{ doc('fact_sales') }}"
    columns:
      - name: sales
        description: "Sum of total sales revenue"

models/schema.md

{% docs fact_sales %}

| Metric         | Definition                                       |
|----------------|--------------------------------------------------|
| quantity       | Sum of quantity sold                             |
| price          | Average price for an ordered product             |
| sales          | Sum of total sales revenue                       |
| target         | Sum of target sales revenue                      |


{% enddocs %}

The model description that references the docs block markdown will enable the following tooltip in FlexIt:

Statistics

Information such as Number of rows or Size of a table/model can be very informative and build trust in the data. If the underlying data source supports these statistics, then FlexIt makes them available by right-clicking on any model in an analysis and selecting View Stats, and in the data model editor.

Tags

dbt tags on models and columns, including tags inherited through the lineage, can show messages and icons in field tooltips. Configure which tags do what under Tag Actions in the data model Settings.

Custom meta

Many dbt features inherently support the needs of business intelligence, for example:

1. relationships test for integrity also enable joins
2. Semantic Layer measures define facts for aggregation
3. Descriptions and docs blocks support data literacy through explanatory tooltips
4. Lineage enables data literacy and trust
5. Freshness enables trust in letting the users know when there are issues with the underlying data

However, there are many features that are specific to business intelligence, and thus not inherently supported through standard dbt features. This is where the meta tag comes in, allowing us to define custom attributes for models, columns, and metrics. FlexIt reads meta or config.meta.

Attribute	Applies to	Description
label	models, columns	Business name
hidden	models, columns, metrics	Hide from users
display_index	models, columns	Display order
joins	models	Joins
format	columns, metrics	Formatting
synonyms	columns, metrics	Synonyms for search and NLQ
grain	date columns	Default time grain
group_label	columns, metrics	Put the field in a folder with this name
sort	columns	Default sort
certification	columns, metrics	Certification status
aggregate	numeric columns	Default aggregation
metrics	columns	Metrics defined on the column
datatype	columns	Data type, when dbt does not provide one
nlq	columns	Custom Natural Language Query filters

Formatting

Define column formatting for numbers, currency, percents, dates, and text

models/schema.yml

models:
  - name: monthly_sales
    columns:
      - name: revenue
        meta:
          format:
            type: currency
            decimalPlaces: 2
            displayUnits: auto # none, auto, thousands, millions, billions, trillions
      - name: quantity
        meta:
          format:
            type: number
            suffix: " units sold"
      - name: date_day
        meta:
          format:
            type: date
            pattern: 'MMM D, YYYY'
      - name: mrr_change
        meta:
          format:
            type: percent
            divideBy100: true
      - name: descr
        meta:
          format:
            type: text
            showNullAs: 'No Value Entered'

type is one of none, number, currency, percent, date, or text. Depending on type, possible attributes are:

• decimalPlaces
• prefix
• suffix
• displayUnits
• separatorStyle - '1' for 1,000.00, '2' for 1 000,00, or '3' for 1.000,00 (quoted)
• negativeValues - '2' to show negatives as -100 instead of (100) (quoted)
• showNullAs
• staticValue
• pattern (date only)
• divideBy100 (percent only)

Synonyms (Aliases)

Use meta synonyms when you want to assign multiple potential names to a single column. For example, sales might be called revenue, receipts, proceeds, or other names depending on who you ask. Synonyms allow you to define multiple aliases for a column that can be used for both search and Natural Language Query (NLQ)

models/schema.yml

models:
  - name: fact_sales
    columns:
      - name: sales
        meta:
          synonyms: # for business searching and natural language processing
            - Revenue
            - Proceeds
            - Receipts

Defining synonyms in your model schema.yml allows them to be search and used for Natural Language Query, as shown below:

Business (Friendly) Name

Use meta label on models and model columns to give them business friendly names.

models/schema.yml

models:
  - name: fact_sales
    meta:
      label: Sales Fact # friendly name
    columns:
      - name: rev
        meta:
          label: Revenue # friendly name

dbt metrics have a label attribute, so do not use meta.label for metrics

Model Order

Use the display_index meta tag to place models and columns in a specific order

models/schema.yml

models:
  - name: fact_sales
    meta:
      label: Sales Fact
      display_index: 1 # display first

Hidden

Use the meta.hidden attribute on models, model columns, and metrics to hide it from being displayed to end users in the BI tool.

models/schema.yml

models:
  - name: stg_dummy_table
    meta:
      hidden: true

Joins

For inner joins where all rows have values for the joining columns, it is recommended that you use the column relationships tests so that you get the benefit of testing integrity and automated joins. However, when this is not possible, or you have a more complex join need, then you can use the model meta tag to define joins.

See the Joins (Entity Relationships) section.

Certification

Mark a column or metric as certified so users know it can be trusted. FlexIt shows an icon and the note in the field tooltip.

models/schema.yml

models:
  - name: fact_sales
    columns:
      - name: sales
        meta:
          certification:
            status: certified # certified, ongoing, invalid, none
            note: Reconciled with the general ledger
            user: Finance Team
            date: 2026-01-15

Time Interval (Grain)

Use the meta.grain model column attribute to define default time granularity for a field. Set it to off to show only the original value of a field without the ability to have specific time grains.

models/schema.yml

models:
  - name: fact_sales
    columns:
      - name: prior_year_date_day
        meta:
          grain: off # time interval grain; values 'off', 'day', 'week', 'month', 'quarter', 'year'

When grain is not off, users will see the following grain options in the FlexIt metadata:

Grain only applies to date columns

Hosting dbt Docs

FlexIt can host your dbt docs and serve them as a Docs Server.

With a dbt Job

The dbt Job step in Data Transformation can publish docs for you:

1. Add a dbt Job step pointing at your project's repository (see Git Repositories).
2. Enable Generate Docs to run dbt docs generate after your commands.
3. Set Local Directory to a folder name, e.g. my_project. After each run, FlexIt copies manifest.json, catalog.json, run_results.json, sources.json, and index.html to webcontent/my_project.
4. Enable Refresh Cache to refresh the data models built from those docs once the job finishes.
5. Create your data model from dbt Docs Server with the Docs URL /webcontent/my_project.

The docs site itself is available at http://flexitserver/webcontent/my_project.

Manually

Copy the dbt target folder created by dbt docs generate to the FlexIt webcontent folder. The docs are available at http://flexitserver/webcontent/target, and the Docs URL is /webcontent/target. You can rename the target folder to anything you want, changing the URL as well.