Data Modeling Best Practices
Data modeling defines how data is related, what it means, and how it flows together. The standard best practices that BI and data teams follow in the modeling process ensure that data is approachable and consumable and that users have the right information in the right context – at the right time.
Unlike with traditional BI tools, data modeling in Sigma is a collaborative process that can involve business leaders who traditionally see only dashboards. Incorporating their knowledge directly into the model minimizes the back and forth it typically takes to give them what they need and get reporting right, fostering close collaboration without giving up control.
Even better, Sigma’s model is iterative and grows with your business. It’s easy to update and adapt your model as you add more types of data or as processes change. Users do all modeling and updates through Sigma’s intuitive visual interface, which requires no specialized coding knowledge.
Read on to learn about the data modeling process in our step-by-step guide using Sigma.
Step 1: Lay the foundation
Who’s involved: BI team
What they need to do: Connect Sigma to your cloud data warehouse, create core Datasets, and set up relationships between data sources. Sigma brings everyone together in a single platform, but it all still starts with the data team. The good news? Laying the foundation to set your business users up for analytics success in Sigma takes just a few minutes or hours!
Connect to your data warehouse
The first step to getting started with Sigma is connecting to your cloud data warehouse. As soon as you connect, Sigma indexes all the tables in your warehouse. Navigate to these tables in Sigma following the existing structure of your data warehouse.
Sigma also automatically pulls in all the work you’ve already done to organize your data—including all existing table and column descriptions. Foreign keys set up in the database are pulled into Sigma as Links (more on those later!).
Find your core data
Once you’ve connected Sigma to your cloud data warehouse, identify the tables with your core business data. These tables contain sets of basic information that’s often combined and configured to create more detailed reports. Examples of core business data include customer demographics, product usage details, sales opportunity data, and more.
These are the first tables to turn into Datasets, annotate, and flag so your business analysts have a clear and solid foundation from which to build reports.
Turn core tables into Datasets
Once you’ve identified the tables with your fundamental business data, it’s time to turn them into Datasets in Sigma.
A Dataset is a collection of core, related data that serves as a reusable base for more specific and detailed analysis. Datasets allow you to add calculations and analyses to your data to ensure teams start with the right information and calculate metrics the same way across the entire organization.
There are two types of Datasets in Sigma:
- Worksheet-based: These Datasets are made of individual or joined tables from one or more data sources, and are edited using a worksheet housed within the Dataset. Sigma’s no-code interface makes it possible to define metrics and add calculations without SQL.
- SQL-based: These Datasets are created using new or existing SQL queries via Sigma’s SQL Runner.
Any published changes made to a Dataset at any time are immediately reflected across all corresponding worksheets, ensuring users are always working with the same, up-to-date information. Datasets with complex joins can also be put on a materialization schedule to help speed up query time.
Datasets are stored in the folder system in the left-hand navigation panel of the Sigma interface. This makes it easy for users to browse relevant data in Sigma, rather than having to search for data directly in the cloud data warehouse connection.
Step 2: Set your team up for success
Who’s involved: BI team
What they need to do: Set up relationships between data sources, provide descriptions, and endorse Datasets to help users get the most out of their data.
Set up Links
Now that you’ve identified your core data, it’s time to start setting up Links.
Links create a pre-defined, join pathway between two data sources in Sigma, such as Datasets or tables. This gives users the option to explore and dig deeper into related data in a guided, contextualized way. Links enable non-technical users to preview and create their own joins without having to go through the join creation process—or even know what a join is!
All the relationships that pre-exist in your database are pulled into Sigma when you connect your cloud data warehouse. If you have foreign keys set up in your warehouse, you will automatically see those relationships in the Links tab in Sigma.
Adding additional Links in Sigma during the modeling process is easy. Simply navigate to the Links tab and define which data sources you want to link using single or multiple keys. When creating Links, always set up relationships that are one to one or many to one. This workflow provides free exploration across your company’s data ecosystem using Sigma.
To help users get the most out of this data, it’s important to give them context by adding Dataset and column descriptions. You can add descriptions directly from the Dataset pages, and write column descriptions in the column tab.
While any table descriptions that exist in your cloud data warehouse get auto-populated in Sigma, the BI team should provide additional descriptors to further guide teammates who may be unfamiliar with the database.
For SQL-based Datasets, we recommend adding any calculations that have been done in SQL to the column description. This keeps everyone on the same page.
However, don’t worry about tracking down information for every single column — you can give permission to Business Analysts to add that themselves.
Endorse your Datasets
Finally, it’s time to flag your Datasets as Endorsed, Warning, or Deprecated. You can add flags directly from the Dataset page. This lets people know where they should — and shouldn’t — start when building worksheets.
Your name and the date are automatically added to any flag you create. You can also add a note to let people know when to use the Endorsed Dataset or to give more depth to a Warning flag.
Step 3: Use what you’ve got
Who’s involved: BI team
What they need to do: Use existing SQL to create Datasets in Sigma.
Note: If you just set up your cloud data warehouse and Sigma is the first tool to touch the data, skip to Step 4. Otherwise, you probably have chunks of SQL that you’ve been using for analytics. Good news! You can use the work you’ve already done by taking this existing code and transforming it into reusable Sigma Datasets.
Copy, paste, create
Copy and paste your SQL into Sigma’s SQL runner, run it, then click “Save as Dataset.” Once you’ve turned your SQL code into a Dataset, you can set up Links, add descriptions, and endorse the Dataset the same way you do with Sigma-originated Datasets.
Step 4: Establish permissions
Who’s involved: BI team
What they need to do: Determine the level of permissions in Sigma for each person on your team.
Permissions in Sigma can be granted at the team or individual level. We recommend setting permissions at the group (team) level to make it easier to add and remove people with specific access levels. Teams also give the members a shared, private workspace to collaborate and share their work in Sigma.
Start by bucketing users into specific groups. Some organizations prefer to group users by function, while others find setting permissions based on department to be easiest. Another way is to set up teams and permissions based on the way people use Sigma, like “Analyst” and “Modeller.”
Once your teams are defined, it’s time to start granting permissions.
Permissions determine which data a user or group of users has access to build on. Permissions can be assigned at the connection, database, schema, or table level.
There are three roles in Sigma, each with different permission levels:
- Admins have access to all documents within Sigma and permission to edit all these documents. Admins can administer system settings, users, teams, connections, integrations, and authentication.
- Authors have access to all modeling, analytics, and dashboard capabilities. Authors can only build Datasets, Worksheets and dashboards with data they have permission to access. If an author has access permissions to a table, they can see the table in the Connections tab and build Datasets and worksheets directly on that table’s data.
- Viewers can only see the worksheets and dashboards they have been granted permissions to interact with from admins, and cannot build new analyses. They can also interact with parameters on worksheets and dashboards, as well as dashboard filters.
Remember that everyone in your instance can see Datasets, worksheets, and dashboards that live in the Organization Folder. Similarly, objects in Team Folders are visible to all members of those teams.
Select “Connections” from the left-hand navigation menu in Sigma, then go to the Permissions tab on the database object or scope to grant the appropriate privileges.
All permissions in Sigma are additive, meaning that if someone is a member of two teams, they will have the permissions granted to both teams.
Step 5: Bring in the analysts
Who’s involved: Business analysts
What they need to do: Add business-specific knowledge to Datasets and tables, create worksheets and Datasets with key business metrics, and make it easy for business users to find what they need.
Now that you’ve set the stage so Sigma users can easily find their way through the database, it’s time to bring in the business analysts to add more color and context to the data.
When your basic models are set up, invite business analysts to add additional context to Datasets and tables and build worksheets for specific use cases. You can invite collaborators by going to “Settings.”
Add depth to Datasets
Once invited to collaborate in Sigma, Business Analysts have the ability to edit existing tables and Datasets. This can be done directly in Sigma without writing SQL.
Business analysts bring their subject matter expertise to bear when adding in column descriptions, setting up contextually relevant links, and lending additional information to show how different data fits together for particular business use cases.
Adding calculations to Datasets is a good way to share key metrics and KPIs, as well as set up a single source of truth for common analytical questions. Anyone can also extract columns of data from JSON or other semi-structured files utilizing Sigma’s straightforward user interface.
In Sigma, business analysts can create Worksheets off of Datasets to build out analyses for specific teams, functions, or use cases.
You can save worksheets and share them with business users for further exploration. They can even add calculations without disrupting the underlying Dataset and create data visualizations from the worksheets in Sigma.
Control + collaboration
Once your business analysts finish creating and/or editing Datasets, Sigma admins can review and flag them as needed. Admins can also set up a materialization schedule for any large Datasets with complex joins.
Set up folders
Remember that each designated team in Sigma has a shared workspace. Members of a given team can view items in team workspaces. Be sure to save worksheets, Datasets, and dashboards in the appropriate team workspaces to help users find the data that’s most relevant to them. There is also an organization-wide workspace everyone can search and explore.
Users can apply filters in both team and organization-wide workspaces to quickly find exactly what they need. Descriptive titles elevate the correct worksheets, dashboards, and Datasets in search. We recommend that you establish a consistent naming convention and provide thorough descriptions to help people find what they need.
Step 6: Benefit from your work
Who’s involved: All Sigma users
What they need to do: Start building and creating analyses.
Data modeling with Sigma is an iterative process that’s continued and refined over time as people use the system. As new use cases emerge, users can build, materialize, and tag new Datasets, deepening your organization’s knowledge and revealing fresh insights.
The best way to find new use cases? Give your team access to Sigma and let them start crunching numbers.
With Sigma, data modeling doesn’t have to be a top-down project. Get your business analysts and end-users involved and start collaborating!
If you have any questions on data modeling in Sigma, please use the in-platform chat module in bottom left corner of the window or send us an email.