How to use input tables, a practical guide to dynamic calculations and scenario modeling
Input Tables are a powerful new feature in Sigma that enable direct access to your cloud data warehouse via managed warehouse tables. With Input Tables, you can directly interface with live production data in a way not previously possible.
“If you want to jump straight into building input tables into your analysis and already know how to create a new input table jump down to “How to use Input Tables in your calculations and scenario models”
How Input Tables Work
Input Tables in Sigma work by allowing users to manually enter data at the cell level, alongside their existing warehoused data, to create a more comprehensive analysis. Users can create empty Input Tables for data entry or generate them from live source elements. The entered data is stored in Sigma-managed system tables within the user's Cloud Data Warehouse (CDW). Input Tables can also be integrated into existing analyses using lookups and joins, supporting use cases like data entry, data augmentation with external information, and performing what-if analyses or scenario modeling.
How to create an input table
1. Check requirements:
Ensure that you meet the following requirements before proceeding:
-You have a Snowflake connection with write access enabled.
-You have the necessary permissions: Admin, Creator, or a custom account type with permission to create Input Tables.
2. Create an Empty Input Table:
- Enter Edit mode in the workbook.
- Open the workbook's ADD NEW panel.
- Under INPUT TABLES, select EMPTY.
A new Input Table will appear on the page.
3. Create Columns, Add Data, Do Validations
- To add a new column, open a column's menu and select a column type from the "Add new column" submenu.
- Click any cell and type a value. Make sure the cell value matches the column's type. You can also copy and paste data into one or more cells at a time.
- Open the column's menu, select "Data Validation", add your value options, and click "Save".
Different types of input tables and their uses
In Sigma, there are two types of Input Tables: empty Input Tables and linked Input Tables.
Empty Input Tables: These are created as blank tables, allowing users to manually enter data at the cell level. Users can add columns, input data, and set data validation rules as required. Empty Input Tables can be integrated into existing analyses using lookups and joins with warehouse-sourced data.
Linked Input Tables: These are created from existing data in the user's cloud data warehouse. Users can select a source element and choose the desired columns to include in the Input Table. While certain columns from the source data cannot be edited, users can add new editable columns, input data, and set data validation rules as needed. Linked Input Tables can also be integrated with existing analyses using lookups and joins.
How to use an input table in your calculations & scenario modeling
For this portion of the how-to-guide, we are going to take a practical approach and a very simple use-case that can show the power of input tables dynamically updating calculations in Sigma and creating a simple scenario model.
In this example, we’ve taken historical transaction data from a hypothetical retail business and our existing orders from inside our Snowflake instance for prior years and the current year to date, then used them to create a projection model. On the left hand side, you’ll see an input table with 13 months, representing the entire fiscal year of 2023. You’ll also notice a growth estimate column that we have manually created at each month based upon our own best ability to forecast data. Now let’s watch what happens when we develop two different scenarios in our table.
The rainy day scenario
As a business facing increasing external pressure from changing interest rates and tightened financial conditions, it’s essential we be able to understand and project the potential downside for our companies. Let's take a look at what our forecast looks like if we have net negative growth over the next year.
While not the best or happiest charts to look at, understanding our potential downside from real historic data and the future possibilities of our business is essential to managing risk and ensuring continuity of our organizations.
The growth scenario
Now, let's flip this scenario on its head.
Assume we have signal that our growth is on an upward trend. That means our current orders (living in Snowflake) as well as our own best estimates of our business show growth. We can now input those projections on a monthly basis to give us a total revenue growth number relevant to our current numbers and show where we may need to source more inventory, hire a larger team, etc.
Changing our underlying assumptions
With Input Tables, it’s easy to create and model your data against both live warehouse data and human-generated inputs. In the example below, you can see that our growth estimates are appended against real live orders in a single table. You’ll also notice that in the formula bar above this table, we built our own custom function against our own internal assumptions for how growth should occur in the upcoming year. It’s as easy as writing a new function or a new column to change the underlying mathematical assumptions behind your forecasts and models. No python or SQL required.
Frequently Asked Questions
Q: What are the main use cases for Input Tables in Sigma?
A: Input Tables can be used for data entry, integrating external data with warehouse data, what-if analysis, and scenario modeling.
Q: What are the requirements for using Input Tables in Sigma?
A: To use Input Tables, you must have a Snowflake connection with write access enabled, and you need to be an Admin, Creator, or have a custom account type with permission to create and edit Input Tables.
Q: Can I import data from external sources into Input Tables?
A: Yes, you can copy and paste data from external sources or other elements in your workbook into the cells of Input Tables, subject to a 500-cell limit.
Q: How can I ensure data consistency when entering data in Input Tables?
A: You can add data validation rules to restrict the values allowed in a column, ensuring that the input data is consistent with your requirements.
Q: How do I join Input Tables with existing warehouse data for analysis?
A: You can use lookups or joins to integrate Input Tables with existing data from your cloud data warehouse, allowing for seamless analysis of the combined data.
Q: Can I grant other users permission to edit data in Input Tables?
A: Yes, workbook editors can grant permission to explorers to edit Input Table values by enabling the "Allow data editing in explore mode" setting in the Input Table's more (•••) menu. This permission will apply to all users with “Can Explore” access to the workbook.
Q: How can I add calculated columns or perform calculations using data from Input Tables?
A: Child tables of Input Tables have all of the same capabilities of standard tables in sigma. You can create calculated columns or perform calculations using standard Sigma functions and formulas, combining data from the Input Table and your warehouse data as needed.
Q: How secure is the data stored in Input Tables, and where is it stored?
A: Input Table data is stored in managed system tables within your cloud data warehouse (CDW), ensuring that the data remains secure and in line with your organization's data security policies.
Where can I learn more about Sigma features and use cases?
Our online documentation is a great way to get high-level information on product features along with as much fine detail as you want.
Sigma QuickStarts provide “step-by-step” guides to using Sigma, exploring specific features and use-cases.