Overview

You can connect as many private databases to your Mode Workspace as you like. Mode supports connecting to most popular types of relational databases. When you connect a database to your Mode Workspace, by default all members of your Workspace will be able to run queries against that data connection.

All Workspaces are also automatically connected to the Mode Public Warehouse.

Private data connections

Connect a new database

To connect your database to your Mode organization:

1. Navigate to your Mode homepage and sign in.
2. Click on your name in the upper left corner of the screen.
3. Click Connect a Database.
4. Follow the instructions to connect your database.

Learn more about how Mode connects to private databases.

Modify or disconnect a connected database

You delete or modify the settings and credentials for a database connection only if you're an admin or if you were the member who connected it to the Workspace.

1. Navigate to the Mode home page and sign in.
2. Click on your name in the upper left, and click Workspace Settings
3. In the Data section, click Connections.
4. All the database connections for your Workspace will be listed on the right.
5. Click on the connection and then go to the Connection Properties tab to adjust the database credentials and configuration. Click Delete to disconnect this database from Mode.

Update preferred JDBC driver

To update the driver Mode uses to connect to one of your data sources, you must have the Manage Data Source permission or higher.

1. Open the menu beneath your name in the upper left corner and select Workspace Settings.

2. Scroll down to the Data section and select Manage Connections. In the main panel, select the connection you’d like to update from the list.

3. Review & update the selected driver. Details on all driver versions and known issues are documented for each database technology below.

4. Re-enter the password of the Mode database user or otherwise re-authenticate the credentials, then Save changes.

5. You should see a blue banner at the top of the screen indicating the connection properties have been updated.

6. An error like this indicates the user name & password (or equivalent) must be re-entered before saving the driver changes.

Disable or Enable Automatic Schema Refresh

For Admins and users with "manage" permission:

1. In the Connections section, select a database connection to navigate to the settings.
2. Toggle off or on Automatic Schema Refresh.
3. Confirm and save changes to the connection properties. Schema refresh will now be disabled or enabled.
4. Manually refresh the schema with the button at the top of the page.

Trigger a Schema Refresh

Mode’s schema update frequency is daily at 3:05pm UTC (7:05am PST / 8:05am PDT based on daylight savings). However, you can trigger a schema refresh at any time.

1. In the SQL editor, click on the kebab menu next to your data source and click on Refresh.
2. You can also go to Workspace Settings in the menu beneath your name.

Then go to Manage Connections under the Data section to select the data source. Click on the Refresh schema button on the top right corner.

Controlling access

Database user permissions

Most databases provide robust permission systems, which let you manage user roles to set detailed schema- and table-level permissions. In Mode, your Workspace will access each data connection through a single user, which we recommend you or your database admin create expressly for use with Mode.

Members of your Workspace who use this data connection will be able to access the data source according to the permissions granted to this user in your database. You can connect Mode to the same database multiple times via different database users, with varying levels of permissions.

There are various ways you can control the data that members of your Workspace can query in Mode. One way is to leverage the permissions granted to the single user your Workspace relies upon to connect to a given database. Limiting that user’s permissions on the database side will, in turn, restrict the access of all members of your Mode Workspace for that data connection.

Limit access to a data connection in Mode

Admins in a paid plan Workspace can also limit access to a data connection to specific non-admin users or user groups directly in Mode. If a non-admin user does not have access to a connected database, that user will not be able to:

• Query the database or create, duplicate, edit or schedule any reports that query that database.
• Write or view any of that data connection's Definitions.

Connection admins can set the default connection access policy for everyone in their Workspace to have View or Query access to the connection, or choose to set up access to None.

• The access type View allows all members to view content built on this connection.
• The access type Query allows all members to view and query this connection.

Granting Access Type to Groups and Individuals

In addition you can grant Query, View or Manage access to groups and individuals.

To set up Permissions to a connected database in Mode:

1. Navigate to the Mode home page and sign in.
2. Click on your name in the upper left, and click Workspace Settings
3. In the Data section, click Manage Connections.
4. Click on the data connection you want to limit.
5. Click the Permissions tab.
6. Click on Connection Access Policy, to set the default access type to None, View, Or Query.
7. To manually add users and user groups, click Add members. When in the pop-up box, click on each user or user group that you want to grant access to this connection.
8. Once you click on the user or user group, you will have the option to grant Query, View, or Manage access.
9. If you change your mind and want to remove that member, click on the user or user group, then click on the Access Type and choose Remove Access and then confirm Remove Access.

How database permissions and Mode permissions interact

Here's an example of how this setup might look for a company with a single database, connected to Mode via two different database user roles.

Note that these permissions only determine query access. Users in your Workspace will be able to see a report created with one of these data connections unless the report is in a private Collection.

1. In this simplified example, the company's database contains two tables: a marketing table and a finance table containing sensitive information.

2. There are two user roles (managed at the database level):

   • User Role #1 only has access to the marketing table.
   • User Role #2 has access to both the marketing table and the finance table.

3. When you connect a database to Mode, you do so using database user roles. Each connection will appear as a different database in the schema browser. Note that the default name will be the name of the database (e.g. Redshift), but you can also name it based on a description of the access level or user role.

4. Paid plan Workspaces can also specify which Mode users have access to each connection.

   • A user with query access to “Marketing” will ONLY be able to query the marketing table. They will not have access to any sensitive financial information.
   • A user with query access to “All” will be able to query the marketing and finance tables.

Query Headers & Footers

Mode enables customers to annotate queries in two ways:

• Mode allows customers to define, using Liquid parameters and SQL, metadata attributes to be sent about each query. This metadata can include Mode user or query attributes (e.g. username, query runner group, or timestamp of query run. Mode calls these Query Headers.
• Mode automatically appends a SQL comment to each query with the following static Mode user attributes: email, timestamp of query run, Mode query run URL, and a boolean indicating whether the query was executed manually or scheduled. Mode calls these Query Footers.

Query headers

Custom query headers allow admins to prepend queries with code that executes every time a query is run against the data connection. Using custom query headers you can:

• Manage database load by giving services downstream of Mode context about who is running the query or why it is being run.
• Automatically set environment variables, such as default schemas and time zones.
• Add custom logging to each query that will appear in your database's logs.

Modify a data connection's query header

You must be the creator of a data connection or a Workspace admin to modify its query header.

1. Navigate to the Mode home page and sign in.
2. Click on your name in the upper left, and click Workspace Settings
3. In the Data section, click Connections.
4. Click on the data connection you would like to modify.
5. Go to the Query Header tab.
6. In the space provided, add or modify any valid SQL code (including comments and valid Liquid code) that you want Mode to prepend to all queries run against this connection.
7. When you are finished, click Save.

Variables

Each time a query is executed, Mode automatically defines a number of Liquid variables containing useful audit information. You can reference these variables in your custom query headers to add audit information that will appear in your database's logs. Both SQL and Liquid parameters are supported. See our Blog:

*Find a group's token by going to Settings > Groups > click a group. The token is a 12 character string at the end of the URL.

Examples

Assign scheduled queries to a different group in Redshift

{% if is_scheduled == true %}
SET query_group TO scheduled_queries;
{% endif %}

Set a default schema

SET SEARCH_PATH TO 'webapp';

Add comments with run date, type, and running user

This example is illustrated with a multi-statement query to show how headers & footers interact.

-- Run by {{ query_runner_email }}
-- Run at {{ run_at }}
{% if is_scheduled == true %}
-- Scheduled run
{% else %}
-- Manual run
{% endif %}

Query in SQL Editor (Mode):

SELECT 1; 
SELECT 2

Renders to:

-- Executed by hi@modeanalytics.com 
-- Query executed at 1591031089 
-- Manual run 
SELECT 1; 
SELECT 2

In Postgres, we’ll see two distinct statement executions. The Header appears on the first statement only.

-- Executed by hi@modeanalytics.com 
-- Query executed at 1591031089 
-- Manual run 
SELECT 1

The Footers only appears on the second statement (unless per-statement footers are enabled).

SELECT 2
-- {"user":"@jane_doe","email":"hi@modeanalytics.com","url":"https://modeanalytics.com/modeqa/reports/1820851ba5a0/runs/a85b6da6912d/queries/d7f43f015d70","scheduled":false}

Query Footers

Mode always appends a non-customizable SQL comment annotation at the end of a query. By default, this annotation is made per query. For a multi-statement query (see example) the footer is only appended to the second statement by default. Mode is able to enable per-statement footers if the Workspace uses multi-statement queries.

Query Footers are not visible in Mode’s SQL Editor to the user while editing a query and Query Footers do not display in Mode’s UI as a part of the query syntax for a past query run. This annotation does appear as part of the syntax executed by the database.

The annotation always includes:

• user: query runner’s username
• email: email address
• url: complete query run url
• scheduled: true/false

Examples

In Mode:

SELECT 1; 
SELECT 2

In Snowflake (two distinct statement executions; without per-statement Footers, only the second statement show the footer comment):

SELECT 1
SELECT 2
-- {"user":"@jane_doe","email":"hi@modeanalytics.com","url":"https://modeanalytics.com/modeqa/reports/1820851ba5a0/runs/a85b6da6912d/queries/d7f43f015d70","scheduled":false}

In Snowflake (two distinct statement executions; with per-statement Footers, both statements show the footer comment):

SELECT 1
-- {"user":"@jane_doe","email":"hi@modeanalytics.com","url":"https://modeanalytics.com/modeqa/reports/1820851ba5a0/runs/a85b6da6912d/queries/d7f43f015d70","scheduled":false}
SELECT 2
-- {"user":"@jane_doe","email":"hi@modeanalytics.com"","url":"https://modeanalytics.com/modeqa/reports/1820851ba5a0/runs/a85b6da6912d/queries/d7f43f015d70","scheduled":false}

Mode Public Warehouse

Mode hosts a public PostgreSQL data warehouse so you can share data and analysis with the entire Mode community. The Mode Public Warehouse is a great tool that you can use to:

• Learn SQL by completing Mode's SQL school.
• Share analysis with or learn from anyone in the Mode community.
• Experiment with all of Mode's features.

Anyone with a Mode account has access to the Mode Public Warehouse and can build reports on top of the data inside it.

Uploading data

1. Sign in to Mode. If you don't have an account yet, create a free Mode user account here.
2. Locate your name in the upper left corner of the window and ensure it says Mode Community above your name. (If not, click on your name, click Switch, scroll to the bottom of the pop-up and click the Switch button under Mode Community).
3. Click on your name in the upper left corner of the window and then click Contribute Public Data.
4. Click Choose and Upload.
5. Select a CSV (up to 500 MB) containing the data you'd like to upload.
6. After the data uploads, you will have the opportunity to customize the name of the table and how the columns are stored. When querying the table, you will be required to type the namespace first. In the example below, the user's account name is "tutorial," so the uploaded table name will include the "tutorial." prefix:

Tables names in the Mode Public Warehouse must:

• Be 4-63 characters in length
• Start with a lowercase letter
• End with a number or letter
• Not contain spaces or special characters (use underscores)

Next, you'll set the column names and types. Columns follow the same naming rules as tables. If you are unsure about the data type for any particular column or would like a general overview of data types, check out the SQL School lesson on data types.

When you are finished, check the box next to "I understand this table will be public to all Mode users." and then click Finish Import at the bottom of the Customize Columns screen. You can now query this data in Mode by selecting Mode Public Warehouse as the database.

Deleting data

To remove a table you've uploaded to Mode's Public Warehouse:

1. Navigate to the Mode home page and sign in.
2. Click on your name in the upper left, and click My Account.
3. In the Account Setting section click Public Data
4. Navigate to your public Mode community page by clicking on the link provided (e.g., https://app.mode.com/{{mode_username}})
5. Click on the Public Table icon.
6. Click on the name of the table you want to delete.
7. Click the gear icon next to the green Query button and choose Delete table.