DBWrite

Overview

DBWrite is a Write-back functionality in Knowi allows datasets to be written into your destination. This allows you to store Knowi Datasets back into your datasource for posterity.

Traditionally, data engineering requires the shipment of raw data into a warehouse (using Data Transporters like FiveTran), which are then joined up, cleaned, and transformed into another set of tables (using tools like dbt) that can finally be meaningfully used by Businesses/Business Analysts. This process results in an explosion of multiple tools, processes, complexity, and cost.

Knowi provides an end-to-end simplified approach. Knowi Datasets allow you to query your raw datastores, join against multiple sources, and perform transformations to provide curated datasets that business users can use immediately. Furthermore, the writeback feature enables you to store the results in your store so that your datasets are easily accessible by users of Knowi as well as your downstream systems.

Knowi provides three Data Strategy options that you can pick and choose as needed:

  1. Direct: This can be Direct queries, Direct Queries with Runtime Parameters. Optionally, caching can be enabled with a TTL on queries with the same fingerprint for faster query executions.

  2. ElasticStore: Execute queries and store the results into Knowi ElasticStore. This can be a "Run Once" query or a scheduled query with various powerful overwrite strategies (Append, key-based upsert, TTL, etc).

  3. Write Back: Same as #2, except it can be written into your store.

NOTE 1: Currently, Knowi provides support for Amazon Redshift, Snowflake, and PostgreSQL as a write-back destination, with others coming soon.

NOTE 2: Contact your Account manager or our support to enable DBWrite.

Define Destination

Navigate to the Datasources listing and edit the database you want to set as a writeback destination. You can also create a new Datasource and use it as the DBWrite destination.

Check the option- Writable Destination and click on the Save button.

Enable Custom Store

Query a Datasource of your choice and define the Data Execution Strategy.

By default, Knowi will write the query results into Knowi's ElasticStore. To enable your own write back destination, choose Run Once or Scheduled Intervals or Triggered Query

Check the option- Use Custom Store and select the Database (set as DBWrite destination) from the dropdown.

Based on the datasource selected, other fields will populate. Since the example uses Amazon Redshift, you can configure Schema (Optional) and enter the name for your table, that you want to write back to the selected database.

You can preview the results and click on the Create and Run button to add the result to the table.

Once complete, the table with data will now exist in your database.

Now you can create the visualization, configure the visualization settings, set up the widget, and add it to the dashboard. For more information, please see Create a Visualization on Dataset

You can navigate to the dashboard to load and view the data in your table.

Please note that knowits is an automatically injected column with the timestamp when the particular row was added.

Support for SUPER Type

Knowi supports the SUPER datatype that can be used to store semistructured data or documents as values in Redshift. Read more about SUPER datatype here.

Query the Table

After creating the table, you can Query it using either of the following two methods:

Method 1: Create New Query on Redshift or Snowflake (Recommended)

Method 2: Trigger as Linked Dataset (Knowi Elastic Store)

Note: From here on it is assumed that your original datasource is Knowi Elastic Store on which you have enabled the Custom Store with Amazon Redshift as your destination datasource.

It is recommended to use Method 1 because your Query will execute directly on Redshift or Snowflake, and if you have defined any filters in this Query, then they will be applied on Redshift or Snowflake and the results will populate accordingly. In contrast, in the second method, all the data is loaded first, and then the filtering is done on memory in the server which might consume a lot of time.

Create New Query on Redshift or Snowflake (Recommended)

This is the recommended method by which you create the new Query on top of the destination datasource i.e. Amazon Redshift or Snowflake, and select the table (created while configuring the Custom Store in the original datasource) from the dropdown list.

You can click on Preview and analyze the results of your Query in the table.

Trigger as Linked Dataset (Knowi Elastic Store)

Alternatively, you can also add the dataset as a linked dataset to the original datasource (i.e. Knowi Elastic Store) to Query the table.

You can click on Preview and analyze the results of your Query in the table.