Data sources

Zenskar connects with your data sources, such as databases, data warehouses, comma-separated-value files, and spreadsheets.

Overview

Data ingestion is the process of moving or replicating data from data sources to destination. Zenskar connects with your data sources, such as databases, data warehouses, comma-separated-value files, and spreadsheets. Subsequently, we move or replicate the data on our data infrastructure (destination) for further processing, as shown in the figure below.

πŸ‘

Security

Zenskar takes the security of your data seriously. All connection details and credentials are stored in a secure encrypted vault.

Fig. 2: Raw data or aggregates ingested  via configured data source

Fig. 1: Raw metrics or aggregates ingested via configured data sources.


Zenskar's data-source-specific connectors

Zenskar uses data-source-specific connectors to connect with your data sources and ingest data.

πŸ‘·

Engineering effort required

The details needed for this step are source-type dependent. Therefore, some technical knowledge is expected to complete this step.

To set up a connector, do the following:

  1. Set Up Source: Provide a Source Name and select a Source Type from the drop-down. The list of available Zenskar connectors is given below.
  2. Source Config: Provide credentials and other configuration details to establish a connection with the data source. Refer the documentation of the connector you are interested in to know the configuration details.

πŸ“š

Note

Irrespective of the data source type, Zenkar stores all ingested data in the form of RDBMS tables.

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet.

Zenskar connectors

Data sourceDescription
Auth0Auth0 is a flexible, drop-in solution to add authentication and authorization services to your applications.
BigQueryBigQuery is a serverless and cost-effective enterprise data warehouse that works across clouds and scales with your data.
CockroachDBA distributed SQL database designed for speed, scale, and survival.
Google SheetsAn online spreadsheet editor from Google.
LookerYour unified business intelligence platform. Self-service. Governed. Embedded.
MetabaseAn open-source business intelligence platform.
MongoDBCross-platform document-oriented database.
MySQLAn open-source relational database management system.
PostgreSQLPostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
QuickBooksAn online accounting software for small businesses.
SnowflakeData platform as a self-managed service.
RedshiftAmazon Redshift is a data warehouse product which forms part of the larger cloud-computing platform Amazon Web Services.

An example: using the Google Sheets connector to let Zenskar ingest data from your data source

Let us assume:

  • You are a telecom provider who sells call minutes, SMSes, and internet data
  • You store raw metrics related to a customer's daily usage in a Google Sheets sheet
  • Your Google Sheets sheet has the following schema:
Customer IDTimestampCall MinutesSMSData
8578d067-b019-471c-b28c-5a3f35a3d05a2024-04-16 11:33:38.00056.0433.7
8578d067-b019-471c-b28c-5a3f35a3d05a2024-04-17 11:25:02.00023.0122.0
8578d067-b019-471c-b28c-5a3f35a3d05a2024-04-18 11:25:43.00034.0161.8
Fig. 2: The Google Sheets data source.

Fig. 2: The Google Sheets data source.

Step 1: add a new Google Sheets data source connector

  1. To add and configure a connector, navigate to Metering > Data Sources in the left sidebar.
  2. Click on the + ADD DATA SOURCE button to land on the Add New Data Source page.
  3. Provide a descriptive name for the data source in the Source Name.
  4. Provide the necessary configuration details.
  5. Click on the VALIDATE SOURCE button.
Fig. 3: Google Sheets data source connector set up.

Fig. 3: Google Sheets data source connector set up.

  1. You will be automatically be taken to a page where you can select the tables and columns based on which you wish Zenskar to ingest data.
    • The destination table schema and name are derived from the schema and name, respectively, of the sheet
    • Columns in the sheet become columns in Zenskar's table
Fig. 4: Sheets in the source Google Sheets become tables in Zenskar.

Fig. 4: Sheets in the source Google Sheets become tables in Zenskar.

Fig. 5: Select tables you wish Zenskar to ingest data into.

Fig. 5: Select tables you wish Zenskar to ingest data into.

Fig. 6: Call_SMS_Data_Usage table has been selected for data ingestion.

Fig. 6: Call_SMS_Data_Usage table has been selected for data ingestion.

Fig. 7: Select columns in the Call_SMS_Data_Usage table.

Fig. 7: Select columns in the Call_SMS_Data_Usage table.

  1. If you make any changes to the Google Sheets schema, during or after configuring the connector, you can use the REFRESH SOURCE button to refresh the schema information in Zenskar.
Fig. 8: Refresh schema information in Zenskar.

Fig. 8: Refresh schema information in Zenskar.

  1. You can select a sync frequency. Zenskar will periodically sync data based on this frequency.
Fig. 9: Configuring sync frequency.

Fig. 9: Configuring sync frequency.

  1. Click the FINISH SETUP button to save the configuration and exit.

Step 2: data processing to extract usage aggregates

πŸ“š

Note

Irrespective of the data source type, Zenkar stores all ingested data in the form of RDBMS tables.

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet.

The ingested raw data must be processed to derive billing determinants that will help Zenskar generate invoices. Zenskar uses SQL to derive aggregates from the raw data.

  1. To set up a usage aggregate, navigate to Metering > Usage Aggregatesin the left sidebar.
  2. Click on the + ADD NEW AGGREGATE button to land on the Add New Aggregate page.
  3. Select the destination table that Zenskar has created to ingest data from your data source.
Destination table nameStructure of the destination table name
googlesheets-_customer_daily_usage.call_sms_data_usage{Data_source_connector_name}.{table_name}
  1. Refer the documentation on usage aggregates to understand how to use the visual builder and the SQL builder to create aggregation queries.