Skip to main content
All CollectionsActions & Writeback
Astrato Writeback - Atomic Input Form
Astrato Writeback - Atomic Input Form

Astrato Writeback introduces the potential for 2-way data stories, using our bi-directional data engine

K
Written by Konrad Mattheis
Updated over 7 months ago

Contents

  1. Introduction

  2. Setup

  3. Best Practice

  4. Roles & Permissions

  5. Troubleshooting

  6. Audit / Reviewing Inputted data

Introduction

The Atomic Input Form objects enable writing back to live database tables

with different types of input methods.

Learn more about writeback here:

Setup

The Example below shows how to create and build an array of input types as a form.

To first use the Input Form:

  1. Create variables to capture the user inputs and define the right type for the variables.

  2. From the left side panel, in the control section, select the type of control you want to use.
    below Is a description of all the available options.

    1. Text input- short text input, best used for one-word inputs like names.

    2. Text area input- a larger area for long text, like comments

    3. Numeric input- numeric input only, optional to define a range for the expected input

    4. Toggle input- true\fales input works only for boolean variables.

    5. Checkbox input- another format of boolean input.

    6. Dropdown input- accepts only predefined inputs, the predefined list can be defined manually or using a field.

    7. Slider input- numeric input with a predefined range and steps.

    8. Button- on click performs a predefined set of actions, such as writeback.

  3. Arrange the atomic input on the screen, it recommended in the end to grouped them together.

  4. Go to actions in the left side area.
    ​

  5. Define an on-click action of writeback for the button.

    Map each field with the right variable.
    ​

  6. Go to preview mode and test the form.

Click to go to troubleshooting

Example

The dashboard below shows an input form on the left to add a row to the dataset.

Best Practice (Data modeling)

To more easily distinguish between the original and new data, it may be easier to create a new table to write back to and append (union) the data to the existing data. It is best to clone the existing table's structure and combine (union) both original and written-back tables to ensure traceability.

⚠ Beware that tables derived from external systems should not be written directly to. Let's assume we have a table called Requests and we want to have a new table named Requests_manual.

Method 1: Concatenate modified data (easy to maintain)

1) We recommend recreating the structure of the table in question. Running the SQL below will return us the SQL used to create the original table.

select get_DDL('table', 'Requests');

2) Modify the table name to Requests_manual and create the new table 

create table Requests_manual(1,2,3,4,5,6);

3) Create a view combining both tables. Use this combined view in Astrato, the SQL below also includes a flag based on the type of input.

Create or replace view VW_REQUESTS_COMBINED AS
Select *, 0 as Manual_Input from "Requests" 
union ALL 
Select *, 1 as Manual_Input from "Requests_manual";

Method 2: Audit trail via auto-increment (fastest to implement)

Another way to distinguish between the original data and user-generated data is to track the audit trail. This can be achieved by relying on auto-incremented fields or sequences.

Create a table with auto-incremented ID

create or replace table   SALES_DATA (
SALES_ID int autoincrement start 1000 increment 1 not null,
QUANTITYORDERED DOUBLE not null,
SALES_AMOUNT DOUBLE not null,
ORDERDATE Date not null
);

If your table already exists...

Consider creating a new table, insert the data from a Select * statement and RENAME the table

Roles & Permissions

Currently, in Astrato, each user uses an identical connection setup for Snowflake. If the account does not have permission to write, we will not show a list of tables to write to.

Troubleshooting

Issue

Solution

Some tables cannot be written to

Solvable by modifying Snowflake permissions

Some/all fields in tables must be populated

Populate all field values or change input requirements. May require adding a new field into the Input Form.

Cannot access Input Form object

Go to system settings in Astrato to enable writeback

Roadmap (subject to change)

Support for use with variables will be available in the future, where end-users will be able to store values to a variable in a workbook. We will also add various types of input to the free-text Input Form.

Reviewing inputted data

The audit list of which Snowflake user account ran input queries from Astrato can be viewed in the Snowflake console, following the example URL path below. https://YOUR-ACCOUNT.snowflakecomputing.com/console#/monitoring/queries

The following conditions will return the history of insert queries run by Astrato:

  • Statement type is INSERT

  • Query Tag contains Astrato

You can also use this SQL to find what entries have been written via the input form. The ROWS_PRODUCED field shows the updated auto-increment ID if there is one in use - this can be directly linked back to the data and return the USER and TIME updated.

select *
from table(information_schema.query_history(dateadd('days',7,current_timestamp()),current_timestamp()))
where QUERY_TAG = 'Astrato' and QUERY_TEXT like 'INSERT INTO%'
order by start_time desc
;

Preview of history of input queries run by Astrato
Related Articles
🆕 Astrato Release Notes
Astrato's Data Engine
Write-back 101: Use cases and data models
Migrating from QlikView / Qlik Sense to Astrato
Store Astrato Query activity in Snowflake & setup Astrato Activity Workbook
Did this answer your question?