If your organization utilizes input tables, governance practices may require you to maintain an input table audit trail that logs what was changed, who made those changes, and when the changes occurred. Although the Sigma Audit Log doesn’t record input table activity at this granular level, a simple solution can help your business meet compliance requirements:
Leverage the input table write-ahead log (WAL).
When input tables are enabled, Sigma automatically generates a WAL (written to your CDW/DBMS) that records data related to all input table changes. You can query this data and use it as a global (organization-wide) input table audit trail.
But before we jump into how to query the WAL, let’s review what it is and how to interpret its data.
What is the WAL?
In short, the WAL is a sequential record of input table changes, which ensures data durability, consistency, and recovery. The WAL primarily acts as an internal database mechanism, but Sigma allows you to query it for audit purposes.
There are several data columns recorded in the WAL, but the following may be the most relevant to an input table audit:
| Column name | Data description |
|---|---|
| Edit | Data related to the specific change operation |
| Metadata | Metadata related to the updated input table element |
| Ds Id | Unique input table identifier |
| Edit Num | Incremental number assigned to the change |
| Timestamp | Date and time the change occurred |
How do I interpret WAL data?
Each row of WAL data represents a user-initiated input table change, which can reference user activity or a resulting system operation.
For example, when a user creates a new empty input table element, the WAL records a createDatasheet entry that directly reflects the user activity. However, it also logs an addColumn entry and three addRow entries to reflect the default column and rows Sigma generates in the new input table.
The following subsections can help you interpret the WAL and identify possible data to extract from the result set.
Edit column
For each WAL entry, the Edit column contains an outer object key indicating the change operation performed.
Most of the Edit column data also contains inner object key-value pairs that provide additional metadata about the changes. For example, in an updateRow entry, the updatedBy inner object identifies the user who made the change, and the updates inner object provides a snapshot of the data type and actual value.
| Outer object key (operation) |
Input table change | Significant inner object keys |
|---|---|---|
| createDatasheet | User created an input table (new, duplicate, or copy) | |
| addColumn | User or system added a new column | type = column type |
| addRow | User or system added a new row | updatedBy = email of the user who added the row rowVersion = row version number |
| appendFromQuery | System mapped values from the source input table to the new input table (duplicate or copy) | updatedBy = email of the user who created a new input table by duplicating or copying an existing one |
| addColumnFromFormula | User changed the column type | type = updated column type |
| updateRow | User updated one or more values in a row 1 | prevRowVersion = previous row version number updatedBy = email of the user who updated the row values updates = data type and updated table value |
| deleteRow | User deleted a row | updatedBy = email of the user who deleted the row |
1 When a user updates multiple input table values in a single action (for example, by pasting or deleting values across multiple rows and columns simultaneously), the WAL records one entry for each row. As a result, an individual updateRow entry can reflect changes to multiple column values in the same row.
Metadata column
For each entry, the Metadata column contains a set of key-value pairs that help you to identify the updated input table. The following table describes key-value pairs that can be relevant to your audit.
| Object key | Object value description |
|---|---|
| elementTitle | Input table element title displayed in the workbook |
| sigmaUrl | URL that points to the specific input table element in the workbook 2 |
| userEmail | Email of the user who updated the input table |
2 If the URL doesn’t point to a specific input table in the workbook, the element may have been deleted in Sigma.
Timestamp column
The Timestamp column contains the date and time of the user-initiated change.
Tip: The WAL can log multiple entries with identical dates and times. For this reason, sorting Timestamp values doesn’t always sort the entries into precise sequential order. To view a sequential record of changes, sort the data by Edit Num values.
Ds Id column
The Ds Id (datasheet ID) column contains a unique ID for each input table saved to the connection. Although these IDs don’t appear elsewhere in the Sigma interface and may not be familiar identifiers, the Ds Id value is constant and can be used to group the WAL data by individual input tables. You can then use data points from the Edit and Metadata columns (like sigmaURL or the most recent elementTitle value) to identify specific input tables.
Edit Num column
The Edit Num column incrementally assigns numbers to each change applied to an individual input table. When the WAL data is grouped by Ds Id value, you can sort the Edit Num values to view a separate sequential record of changes for each input table.
Edit Num values also correspond to row version numbers represented by rowVersion and prevRowVersion key-value pairs logged in addRow and updateRow entries (in the Edit column). These data points support version control and can help you obtain more information about the sequence of input table changes.
For example, in an updateRow entry, the prevRowVersion value indicates the version number of the row’s previous state. You can reference the same number in the Edit Num column to identify the relevant WAL entry and view details about the row’s previous value.
Retrieve a global input table audit trail
Now that you know what to expect from the WAL data, let’s dive into the process of retrieving a global audit trail of all input table activity.
There are two parts to this process:
- Part 1: Obtain the schema path and connection ID
- Part 2: Query the WAL in Sigma
User requirements:
- You must be granted full data access to the connection used for writing input table data.
- You must be assigned an account type with the Write SQL and Edit Workbook permissions enabled.
Part 1: Obtain the schema path and connection ID
When you query the WAL, the SQL statement must include the WAL destination schema path and the connection ID associated with your organization’s input tables. If you have Admin access, complete the following steps to obtain this information. Otherwise, request the information from an Admin member.
- Go to Administration > Connections:
- In the Sigma header, click your user avatar to open the user menu.
- Select Administration to open the Administration portal.
- In the side panel, select Connections.
-
In the Connections list, select the Snowflake or Databricks connection used for writing input table data.
Note: If your organization writes input table data to multiple connections, you must query each connection separately.
-
In the connection overview, go to the Write Access section, then copy or take note of the schema path identified in the Write schema field. This is the write destination schema path used for writing input table data and the WAL to your CDW or DBMS.
If you’re using an OAuth-enabled connection, copy or take note of the schema path identified in the Input table edit log destination field.
-
In the browser URL, copy or take note of the connection ID (the 36-character string following
connections/).Note: When referencing the connection ID in the SQL statement, replace the hyphens ( - ) with underscores ( _ ). For example, the connection ID in the following screenshot converts to
1a2b3c4d_abcd_1234_ef56_abc123def456.
Part 2: Query the WAL in Sigma
To retrieve WAL data in Sigma, execute a SQL query and explore the result set in a workbook.
-
Go to your Sigma Home page.
-
In the navigation panel, click Create New, then select Write SQL to open the SQL runner.
-
In the side panel, click the Select a Connection dropdown field and select the connection used for writing input table data.
-
In the query editor, enter the following SQL statement with the relevant schema path and connection ID (prepended with “SIGDS_WAL_”):
SELECT edit, metadata, timestamp, ds_id, edit_num FROM :schema_name."SIGDS_WAL_:connection_id"
-
Click Run, then review the result set.
Tip: If the query returns an error, check the SQL syntax and ensure you’ve replaced hyphens with underscores in the connection ID.
-
To open the WAL data in a workbook exploration, click Explore in the Sigma header.
-
Customize the dataset to meet your audit and reporting requirements, then save your exploration as a workbook.
Tip: Filter the data, modify the table structure, create child elements, etc., to gain insight into input table activity across your organization. You can also convert the Edit and Metadata columns to JSON and extract key-value pairs to isolate relevant information.
You now have a complete and comprehensive audit trail of all input table activity!
Frequently asked questions
Why doesn’t the WAL record an entry when a user deletes an input table from a workbook?
When a user deletes an input table element, Sigma only removes it from the workbook interface. The input table data in Snowflake or Databricks is unaffected and remains stored as a database table. The WAL doesn’t record an entry for the removal of the input table because it continues to exist in your database.
If an input table is edited in the CDW or DBMS, are the changes logged in the WAL?
To avoid breaking write-access features, refrain from using any platform or tool outside the Sigma interface (like your CDW or DBMS) to edit input tables, the WAL, or other objects created in Sigma.
Can I change the WAL schema?
Currently, you cannot change the write schema without breaking all input tables saved to that particular connection. However, Sigma does have future plans to support seamless changes to the write schema, so stay tuned!
Still have questions? Let us know in the comments, or contact Sigma Support.