Overview
DBWrite 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 DBWrite 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:
- 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.
- 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).
- DBWrite: Same as #2, except it can be written into your store.
NOTE 1: Currently, Knowi provides support for Amazon Redshift and Snowflake as a write-back destination, with others coming soon.
NOTE 2: Contact your Account manager or our support to enable DBWrite.
Define DBWrite Destination
Navigate to the Datasources listing and edit the database you want to set as a write-back destination. You can also create a new Datasource and use it as the write-back 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 DBWrite 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 new 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 a visualization, configure the visualization settings, set up a widget, and add the widget to a dashboard. For more information, please see Create a Visualization on Dataset
You can navigate to a dashboard to load and view the data in your table.
Please note that _knowi_ts is an automatically generated column with a timestamp of when the particular row was added.