Skip to main content

Database connection settings

After you successfully connect a database to Omni, you can further configure settings for schema refreshes, default connection permissions, environments, and more.

General​

The Settings tab of a connection's page contains general settings for the database, such as those for:

  • Connecting to the database, which are specific to each type of database
  • Schema restriction and refreshes
  • Timezones

Connection details​

Settings such as Host, Port, Username, and Password are used to connect to the database. Each database type has its own set of settings. Refer to your database's setup guide for more information about these fields.

Timezones​

Most database types will have a handful of timezone-related settings, such as Database or Query Timezone. Refer to the Converting timezone data guide for more information about these settings and how to configure them.

Schema restriction & refreshes​

Schema restriction​

To restrict the schemas Omni includes in the model, use the Include Schemas field. This allows you to specify only the schemas you want to pull into Omni (also accepts wildcard syntax). For example:

sales_prod                  # Include only the sales schema
sales_prod,engineering      # Include the sales and engineering schemas
sales_*, engineering        # Include all schemas starting with sales_ and engineering schema

Limiting the number of schemas in a model can improve performance by reducing model size. Note: Omni will model all schemas if this field is left blank.

Offloaded schemas​

The Offloaded schemas setting allows you to defer the loading of schema metadata until specifically requested, also known as "lazy loading." The specified schemas will be accessible when needed without unecessarily adding to the size of your Omni model. This feature is useful when using dbt virutal schemas or managing the load of large schemas.

Values entered into this field:

  • Must be in a comma-separated list, like one, two, three
  • Are case-sensitive. This means that SCHEMA_1 must exactly match SCHEMA_1 in the database.
  • Can use wildcard syntax, like dbt_*.
  • Must specify a database if the schema is in a database other than the default, like OTHER_DB.SCHEMA. The non-default database must also be included in the Other databases setting - note that the name of this field will vary depending on the type of connection.

These schemas will be included in the model in addition to the schemas set in the connection's Include schemas setting, if any.

To apply the changes, save and then trigger a schema refresh. While you may not see any visible changes to the connection's shared models, you can check that the setup was successful by:

  1. Opening one of the models in the model IDE.
  2. In the Shared layer of the model, click the link next to Parent at the bottom of the page. This will open the schema model.
  3. In this view, change the Browse mode to Model by using the dropdown near the top left corner of the page.

At this point, the schemas you specified as offloaded shouldn't be visible.

Schema refreshes​

The Schema section on the right side of the connection page contains settings that allow you to define how and when the connection's schema is refreshed. A refresh is when Omni checks the database for schema changes and merges (but doesn't overwrite) them with the existing Omni model.

On-demand & scheduled refreshes​

To trigger an immediate schema refresh, click the Refresh now button.

Refreshing specific schemas & tables

Navigate to Model > Refresh schema in the model IDE to invoke refreshes for specific schemas and tables.

Alternatively, you can use the Schedule button to define a cron schedule to refresh the schema. For example, if you know your ETL pipeline runs every day at the same time, you could define a schedule that refreshes the schema after the process completes to ensure your data is always up-to-date.

Omni uses Amazon Web Services (AWS) syntax for cron expressions. Refer to the AWS documentation for more information and examples.

Branch-based schema refreshes​

By default, schema refreshes will immediately impact the shared model. If you prefer to review schema changes before they're promoted, enable the Branch based schema refresh setting. This will require schema refreshes to be performed in an Omni branch before they can be promoted to the shared model.

When this setting is enabled:

  • On-demand and scheduled refreshes will be disabled
  • Schema refreshes can only be triggered from the IDE. If you’re not in an existing branch, selecting Model > Refresh schema will prompt you to create a branch first.

Note: This setting can’t be enabled if a connection has multiple models or if connection environments (dynamic connections) are currently in use.

Permissions​

The Permissions tab allows you to manage user access and permissions for the connection.

Base access​

The Base Access section of this tab allows you to establish the minimum level of access users will have for the models in the connection. Note: The base role will override less permissive roles specified using the Model access settings.

Model access​

The Model access section allows you to apply more granular connection permissions for individual users or groups at the model level. Refer to the User permissions documentation for information about different configuration types.

Environments​

The Environments tab allows you to define additional environments for the connection, which can changes the connection you're querying against while in an Omni branch. For example, you could set up environments that allow you to test and validate changes in a dev connection, but run workbook queries against a production connection. Refer to the Dynamically switching database environments guide for more information and setup steps.

dbt​

If your organization leverages dbt, this will be where you manage that connection. Read more about our dbt integration here.

Archive​

The Archive tab allows you to delete a connection from your organization. When a connection is deleted, the connection and its associated models and content will be moved to the Trash.

Sensitive information, such as passwords and keys, will be fully deleted.