This feature is currently in Beta. Beta features are subject to quick, iterative changes. As a result, this document may not reflect the most current version.

An Input Table is a data element that accepts manual data input at the cell level. Input Tables can be used for basic data entry, integrating data outside your warehouse into your Sigma analysis, and what-if analysis.  

This article introduces Sigma Input Tables and covers how to create and edit them. You’ll also learn about your options when exposing Input Tables to your data consumers. 

Summary of Content

Requirements
Understanding Input Tables
      What is an Input Table?
      Why use them?
      Data storage
Create an Empty Input Table
Create a Input Table from a Live Source Element
Data Entry
      Data Entry & Copy/Paste
      Add Columns to an Input Table
      Add Data Entry Validation
Integrate Input Tables into Your Analysis
      Example
Consumer Facing Result
      Allow Explorers to Modify Data
      Data Editing by Explorers
Access an Input Table’s SQL
Related Resources

Requirements

  • Input Tables are only available for Snowflake connections with write access enabled
  • To create an Input Table you must be an Admin or Creator or be assigned a custom account type with permission to create Input Tables.
  • To manage an existing Input Table, you must have Can Edit access to the workbook containing the Input Table.
  • To edit data in an existing Input Table, you must have Can Edit or Can Explore access to the workbook. Editing an Input Table in Explore mode is only permitted if the Allow data editing in explore mode setting is enabled on the Input Table. Learn more

Understanding Input Tables

What is an Input Table?

An Input Table is a data element that accepts data editing at the cell level. Unlike other workbook data elements, Input Tables allow you to manually enter data directly in Sigma.

This allows you to integrate manually entered data into your analysis, right alongside warehouse data.

Workbooks offer two types of Input Tables: empty and linked. Empty Input Tables are blank upon creation. They can be joined to warehouse sourced data using lookups and joins.  Linked Input Tables are created from data that already exists in your warehouse.

Why use them?

Input Tables support a variety of use cases, including:

  • Data entry
  • Analysis tasks that require augmenting data from the Cloud Data Warehouse (CDW) with data that's outside it
  • What-if analysis or scenario modeling

To see an example of Input Tables in use, check out the example under Integrate Input Tables into Your Analysis.

Data storage

Input Table data is stored in Sigma-managed system tables in your CDW.

These tables can be accessed directly from your CDW. However, you cannot edit them directly in your warehouse.

Create an Empty Input Table

Prerequisite: A Snowflake connection with write access enabled.
If write access is enabled on only one connection, Sigma automatically saves the Input Table to that connection. Otherwise, you will be prompted to choose. Choose the connection that is consistent with the rest of your analysis and that you plan to join against.

To create an empty Input Table, enter Edit mode, then:

  1. Open the workbook's ADD NEW panel.
  2. Under INPUT TABLES, select EMPTY.

    A new Input Table will appear on the page.

Now what?

  • To enter data: Click any cell and type a value.
    Data can also be copied and pasted into one or more cells at a time.
    Cell value(s) must match the column’s type.
  • To add a new column: Open a column’s menu and select a column type from the Add new column submenu.

  • To add data validation: Open the column’s menu and select Data Validation. Add your value options. Then click Save.

Create a Input Table from a Live Source Element

Prerequisite: A Snowflake connection with write access enabled.
If write access is enabled on only one connection, Sigma automatically saves the Input Table to that connection. Otherwise, you will be prompted to choose.

To create a Input Table from a workbook element, enter Edit mode, then:

  1. You can either:
    • Select a LINKED Input Table type from the ADD NEW panel to select a source

    • Or create an Input Table from an element’s create child element () button.
  2. Once you’ve selected a source, you will need to select columns you want to include in the Input Table, using the dropdown menu and the + Add another column button.
    • Unique row identifier columns define uniqueness for the data you’re adding to the input table. They’re automatically deduplicated against the joined source’s key columns(s) when the input table is created. This is similar to creating a grouping in a workbook table.
    • Under Add more column(s), add any additional columns that you would like to include from the joined source.
    • Source columns cannot be edited.

  3. Click Create Input Table.
    A new Input Table will appear on the page. 
    •  

Now what?

  • To add a new column: Open a column’s menu and select a column type from the Add new column submenu. 
  • To enter data: Click any cell in an editable column and type a value.
    Data can also be copied and pasted into one or more cells at a time.
    Cell value(s) must match the column’s type.
  • To add data validation: Open the column’s menu and select Data Validation. Add your value options. Then click Save.

Data Entry

Data Entry & Copy/Paste

To enter data in a cell, click the cell and type a value. Cell value(s) must match the column’s type.

Data can also be copied and pasted into one or more cells at a time. Pasted values can come from any source, including other elements in your workbook. Copy and paste has a 500 cell limit.

Add Columns to an Input Table

To add a new column to an existing Input Table:

  1. Open a column’s menu.
  2. Select a column type under the Add new column submenu.

Add Data Entry Validation

Data validation limits data entry to only the values you want included in a column. 

To add data validation: 

  1. Open the column’s menu.
  2. Select Data Validation.

    This opens the
    Data Validation modal.
  3. Enter your value options:
    • To manually create the list of valid values, enter your value options under Define values.
      data_validation_one.png
    • Alternatively, if the valid values for your input table already exist within the workbook, you can reference any pre-existing column in your workbook by selecting the data source under Value source.
      data_validation_two.png
  4. Then click Save.

Integrate Input Tables into Your Analysis

Input Tables can be integrated into your warehouse data analysis using Lookups and Joins. They can also be used purely to append new input data to existing warehouse data. 

Below is an example of Input Tables in action. It demonstrates how to create a manual Input Tables and use a lookup to join it to warehouse data for further analysis. 

Example

This example joins existing warehouse data with manual data entry to compare profitability across a set of retail stores. 

  1. Let’s start with our warehouse data, which we’ve already structured in the workbook table below. The table groups our data by the [Store Name] column and calculates each store’s profit, rank percentile of profit, and average month over month profit change.

  2. We plan to use this data to identify which stores are struggling the most. We’ll use an Input Table to capture human context and a Lookup to join it with the data from our warehouse.
    To create a manual Input Table, open the workbook's ADD NEW panel and select EMPTY.
    A new Input Table appears on the page.
  3. Let’s add our list of stores to the Input Table. Select the original table’s [Store Name] column, copy it (Cmd+C), and paste it (Cmd+P) into the first column of the Input Table.

  4. Next, let’s add editable columns to capture additional information. To add a new column, open an existing column’s menu and select a column type from the Add new column submenu.
    In this example, we add two columns: a logical column called [Investigate] and a text column called [Notes].
  5. It’s time to enter our data. To enter data in a cell, click the cell and type a value.
  6. Now that we have both sets of data, we can incorporate our manual data entry into our larger data analysis. To do this, we’ll create a lookup column in the original workbook table that pulls data from our Input Table.
    In the original table, open a column’s menu. Then select Add column via lookup.
    Here we define our lookup. Learn more about lookups

    Click Done to add the selected column (Column to add) to the warehouse table.
    The column appears in the workbook table. Edits made to the Input Table are automatically reflected in the table’s linked lookup column.

    Tip: Additional columns from the linked Input Table can be added to the table by expanding the source section at the bottom of the left rail inspector bar. 

 

Consumer Facing Result

The image below shows an example of what an Input Table looks like to users without data edit access.

Allow Explorers to Modify Data

Workbook editors may choose to grant explorers permission to edit Input Table values. Selecting this option applies the permission to all users with Can Explore access to the workbook. This permission cannot be granted per user.  

Explorers are only permitted to edit cell values in existing, editable columns. They cannot add or delete columns. And they must follow any data validation defined by a workbook editor. 

If saved, an explorer’s changes are applied directly to data storage. 

To grant explorers permission to edit an Input Table’s data:

  1. Click to open the Input Table’s more (•••) menu. 
  2. Select Allow data editing in explore mode.

Want to help your explorers get started with Input Tables? We have a separate doc for them. Check out Input Table Data Editing for Explorers.

Data Editing by Explorers

When a workbook is in Explore mode, editable Input Tables can be identified by an Edit Data button.

To edit an Input Table from Explore mode: 

  1. Click Edit Data.

  2. To enter data in a cell, click the cell and type a value. Cell value(s) must match the column’s type. Edits will have a yellow triangle on them if they have not been saved.
    Data can also be copied and pasted into one or more cells at a time. Pasted values can come from any source, including other elements in your workbook. Copy and paste has a 500 cell limit.
  3. To save your edits, click the Input Table’s save button.
    Clicking cancel will discard your edits.

Access an Input Table’s SQL

  1. Refresh your workbook or Input Table.
  2. Open the Query History modal: Click the caret (▼) button next to the refresh data button in the workbook's header. Then click Query history.
  3. Select the most recent query associated with the Input Table.

    This will open a detailed view of the query and its SQL.
    From this SQL, you can access all the Input Table’s data.
    Important: Anytime schema changes are made to the Input Table, you’ll need to refresh the page to regenerate this sql. Also note - to get the SQL only for the Input Table itself, 

 

Related Resources

Set Up Write Access
Intro to Data Elements
Input Table Data Editing for Explorers