Contents
Introduction
The Input Form object enables writing back to live database tables.
The example below shows multiple fields from the same table, with various formatting suggestions. Multiple input fields can be added, provided that they are from the same table. Any content surrounded by a dotted purple line, indicates that this is a single object.
Learn more about writeback here:
Setup
To first use the Input Form:
From the left side panel, in the data objects section, then drag the Input Form onto the sheet
Select a single database table from the properties panel dropdown
Select the fields you wish to writeback to
Validation feedback will be presented to the end-user. Accepted formats will be shown to the user, this will be derived from the data type of the field selected for input
Populate the inputs in the front-end
Example
The dashboard below shows an input form on the left, to add a row to the dataset.
Best Practice (Data modelling)
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 future, where end-users will be able to store values to a variable in a workbook. We will also add various types of input in addition 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
INSERTQuery 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
;
