# What is Kuwala? Kuwala is a collaborative data workspace for BI analysts and engineers enabling you to build powerful analytics workflows. ## Consolidating the Modern Data Stack ![](https://github.com/kuwala-io/kuwala/raw/master/docs/images/tool_description_github.png) Kuwala is leveraging the prominent existing tools of the modern data stack that you know and love and brings them together in one intuitive interface. ### Running on top of your data warehouse The Kuwala Canvas is connecting to your data warehouse and lets you build data pipelines with all the data you have in them. Currently, we are supporting the following data warehouses:
Postgres BigQuery Snowflake
### Airbyte for data sources To get all your data into your data warehouse, we will build on top of Airbyte with its wide variety of connectors to SaaS tools. ### dbt for transformations We are using dbt to build transformation blocks. Due to the nature of dbt Kuwala is running entirely on top of your data warehouse and saves all the data models you create directly there. # Launching Kuwala Follow these instructions to launch Kuwala and start building data pipelines. The first thing you have to do is to clone the Kuwala repository by running the following command in your terminal/shell: ```shell-session git clone https://github.com/kuwala-io/kuwala.git ``` Before you start Kuwala, you have to have Docker running in the background. We recommend running the latest version of [Docker Desktop](https://www.docker.com/products/docker-desktop/). Now you simply need to run the following command from inside the root directory of the repository: ```shell-session docker-compose --profile kuwala up ``` Now open [http://localhost:3000](http://localhost:3000/) in your browser, and you are good to go. 🚀 # Running in development mode Follow these instructions to run Kuwala in development mode. ## Backend ### 1.1 Prerequisites To run the backend, you need to have Python 3.9 (we're using v3.9.5) installed. {% hint style="danger" %} The Python version has to be below 3.10 [because of dbt-postgres](https://github.com/dbt-labs/dbt-core/issues/2827#issuecomment-1022860423). {% endhint %} ### 1.2 Installation First, install the dependencies in a new virtual environment by running the following command from inside the `kuwala/core/backend` directory: ```shell-session DBT_PSYCOPG2_NAME=psycopg2 pip install --no-cache-dir -r requirements.txt --no-binary dbt-postgres ``` To set up a new virtual environment, you can follow the steps outlined [here](https://packaging.python.org/en/latest/guides/installing-using-pip-and-virtual-environments/). ### 1.3 Launching First, start the backend database by running the following command from inside the `root` directory of the repository: ```shell-session docker-compose --profile backend_database up ``` After you installed all requirements, you can launch the API by running the following command from inside the `kuwala/core/backend` directory: ```shell-session python3 app/main.py ``` To force the API server to reload everytime when code changes have been detected, simply pass `--dev=True` as a parameter when launching the API. ```shell-session python3 app/main.py --dev=True ``` {% hint style="warning" %} **Important:** You need to set the following environment variables: ```shell DATABASE_USER=kuwala DATABASE_PASSWORD=password DATABASE_NAME=kuwala DATABASE_HOST=localhost ``` {% endhint %} ## Frontend ### 2.1 Prerequisites To run the frontend, you need to have Node.js (we're using v16) together with npm installed. ### 2.2 Installation Install the dependencies by running the following command from inside the `kuwala/core/canvas` directory. ```shell-session npm ci ``` ### 2.3 Launching To launch the frontend, run: ```shell-session npm start ``` Now open [http://localhost:3000](http://localhost:3000/) in your browser, and you are good to go. 🚀 # Transformation catalog Transformation blocks are organized and categorized in our transformation catalog. {% content-ref url="/pages/YSVkRqeZNMKvVbsQI4Zo" %} [Time](/transformation-catalog/time) {% endcontent-ref %} {% content-ref url="/pages/RzQWQnTYPXV9XlPIC6Z6" %} [Text](/transformation-catalog/text) {% endcontent-ref %} {% content-ref url="/pages/srAxLHdmycxKXib514hP" %} [Numeric](/transformation-catalog/numeric) {% endcontent-ref %} {% content-ref url="/pages/epsDyz0msZV3PTbb6DSY" %} [Geo](/transformation-catalog/geo) {% endcontent-ref %} {% content-ref url="/pages/uvPqRTGHVZibQaIBtpKy" %} [Merging](/transformation-catalog/merging) {% endcontent-ref %} {% content-ref url="/pages/JtVHa2UrP9nNOPuYMFNq" %} [General](/transformation-catalog/general) {% endcontent-ref %} # Time {% content-ref url="/pages/b2U7IpEQhdJpLkm0PC6f" %} [Compare with date](/transformation-catalog/time/compare-with-date) {% endcontent-ref %} {% content-ref url="/pages/wXFKqRmwXXIIGGgK9bbW" %} [Get date](/transformation-catalog/time/get-date) {% endcontent-ref %} # Compare with date ### Description With this transformation, you can filter a table by comparing a date column with a given date. ### Parameters * column * comparator * Options: equal, not\_equal, less, greater, less\_or\_equal, greater\_or\_equal * comparison\_date * Format: YYYY-MM-DD # Get date ### Description With this transformation, you can extract the date of a timestamp and save it in a new column. ### Parameters * column * result\_name # Text {% content-ref url="/pages/kcP5FghQia39eIZtrQRm" %} [Contains keyword](/transformation-catalog/text/contains-keyword) {% endcontent-ref %} {% content-ref url="/pages/jPNkdLFSevgQKiBPBxid" %} [Filter by keywords](/transformation-catalog/text/filter-by-keywords) {% endcontent-ref %} {% content-ref url="/pages/WtGhwKTEfUYQopFaEx32" %} [Filter by regex](/transformation-catalog/text/filter-by-regex) {% endcontent-ref %} {% content-ref url="/pages/sNbWKfSZ7WFizWuvlIPi" %} [Replace string](/transformation-catalog/text/replace-string) {% endcontent-ref %} # Contains keyword ### Description With this transformation, you can filter a table based on a column that should contain a given keyword. ### Parameters * column * keyword # Filter by keywords ### Description With this transformation, you can filter a table based on a column that should be contained in a given set of keywords. ### Parameters * column * keywords # Filter by regex ### Description With this transformation, you can filter a table based on a column that should match a given regular expression. ### Parameters * column * regex # Replace string ### Description With this transformation, you can replace parts of a string in a column with a new string. ### Parameters * column * old\_string * new\_string # Numeric {% content-ref url="/pages/rw88RTSm2IOPC79n8cXf" %} [Add columns](/transformation-catalog/numeric/add-columns) {% endcontent-ref %} {% content-ref url="/pages/a0UJZ9zVbis40GDseVLT" %} [Apply operation](/transformation-catalog/numeric/apply-operation) {% endcontent-ref %} {% content-ref url="/pages/k69fPOR8O6sfZBk6O9Wh" %} [Compare with number](/transformation-catalog/numeric/compare-with-number) {% endcontent-ref %} {% content-ref url="/pages/XTPkzrDflWV2m7YzwPrn" %} [Divide columns](/transformation-catalog/numeric/divide-columns) {% endcontent-ref %} {% content-ref url="/pages/Wq0NoeXBaQ7TPXX09bqb" %} [Text to number](/transformation-catalog/numeric/text-to-number) {% endcontent-ref %} # Add columns ### Description With this transformation, you can add the values of multiple columns and store the result in a new one. ### Parameters * columns * result\_name # Apply operation ### Description With this transformation, you can manipulate a numeric column by applying basic arithmetic operations. ### Parameters * column * operator * Options: add, subtract, multiply, divide * value * result\_name # Compare with number ### Description With this transformation, you can filter a table by comparing a numeric column with a given value. ### Parameters * column * comparator * Options: equal, not\_equal, less, greater, less\_or\_equal, greater\_or\_equal * comparison\_value # Divide columns ### Description With this transformation, you can divide one column by another and store the result in a new one. ### Parameters * dividend\_column * divisor\_column * result\_name # Text to number ### Description With this transformation, you can transform a column containing numbers as text to numeric values if possible which is necessary to apply numeric transformations to that column. ### Parameters * column # Geo There are currently no geo transformations. 🤷🏽 # Merging {% content-ref url="/pages/U8iPcgS9LIYGnosnjutZ" %} [Join by ID](/transformation-catalog/merging/join-by-id) {% endcontent-ref %} {% content-ref url="/pages/ZsGR5B1YgIbl2shGmY3b" %} [Union](/transformation-catalog/merging/union) {% endcontent-ref %} # Join by ID ### Description With this transformation, you can join two tables by ID. ### Parameters * left\_block * right\_block * column\_left * column\_right * join\_type * Options: inner, left, right, full\_outer # Union ### Description With this transformation, you can merge two tables that have the same columns. ### Parameters * left\_block * right\_block # General {% content-ref url="/pages/pEs9IDZmfiTUX8Jb0hmH" %} [Group by columns](/transformation-catalog/general/group-by-columns) {% endcontent-ref %} {% content-ref url="/pages/4hQ1hBpykWA548Jpnamk" %} [Is false](/transformation-catalog/general/is-false) {% endcontent-ref %} {% content-ref url="/pages/iMbjuFDbEhHXX2JwiWvY" %} [Is not null](/transformation-catalog/general/is-not-null) {% endcontent-ref %} {% content-ref url="/pages/CxtkV95rSahTerCY4qzj" %} [Is true](/transformation-catalog/general/is-true) {% endcontent-ref %} {% content-ref url="/pages/LdIGpjJyiixQUM034049" %} [Remove duplicates](/transformation-catalog/general/remove-duplicates) {% endcontent-ref %} {% content-ref url="/pages/MUlN8CT6rTOSGKjOsWN4" %} [Remove null](/transformation-catalog/general/remove-null) {% endcontent-ref %} {% content-ref url="/pages/1weMxZI7U1Xl4fxgywg4" %} [Replace null](/transformation-catalog/general/replace-null) {% endcontent-ref %} # Group by columns ### Description With this transformation, you can group rows together based on a set of columns and aggregate the values of other columns. ### Parameters * group\_by\_columns * aggregated\_columns * Options: count, max, min, sum, avg {% hint style="info" %} Syntax for `aggregated_columns` is `COLUMN_NAME` + "KUWALA\_AGG" + `COLUMN_AGG`. {% endhint %} # Is false ### Description With this transformation, you can filter a table based on a column that should be false. ### Parameters * column # Is not null ### Description With this transformation, you can filter a table based on a column that should not be null. ### Parameters * column # Is true ### Description With this transformation, you can filter a table based on a column that should be true. ### Parameters * column # Remove duplicates ### Description With this transformation, you can remove duplicate rows in a table. ### Parameters This transformation takes no parameters. # Remove null ### Description With this transformation, you can filter a table based on a column that should not be null. ### Parameters * column # Replace null ### Description With this transformation, you can replace null values in a column with a given value. ### Parameters * column * replacement\_value # Adding Transformations Here, you can learn how to add your own custom transformations to the Kuwala transformation catalog. Each transformation block you can pick from the Kuwala [transformation catalog](/transformation-catalog) is a dbt macro under the hood. Based on the parameters that are passed to the macro it creates a dbt model representing the transformation and its corresponding YAML file. We recommend you familiarize yourself with Jinja first so you can take advantage of all its possibilities. Dbt has written a neat [introduction](https://docs.getdbt.com/docs/building-a-dbt-project/jinja-macros). {% hint style="info" %} In order to develop a new transformation, you should run Kuwala in development mode. Follow these instructions for more detail: {% endhint %} {% content-ref url="/pages/2YmcDTyn5lI3JHKk36Aj" %} [Running in development mode](/launching-kuwala/running-in-development-mode) {% endcontent-ref %} ### 1. Chose a transformation category Before creating a new transformation, make sure you check out the existing ones in the [transformation catalog](/transformation-catalog). Each transformation has to belong to one of the following categories: [time](/transformation-catalog/time), [text](/transformation-catalog/text), [numeric](/transformation-catalog/numeric), [geo](/transformation-catalog/geo), [merging](/transformation-catalog/merging), or [general](/transformation-catalog/general). ### 2. Transformation boilerplate All macros for the transformation blocks are stored under `kuwala/core/backend/app/dbt/kuwala_blocks/macros`. In the following, we explain each part of a macro by following the example of the `apply_operation` transformation. #### 2.1 Macro parameters The declaration of a macro works similarly to the declaration of a function in Python. The macro name, in our example "apply\_operation", is followed by parentheses which contain the parameters. ```sql {% macro apply_operation(dbt_model, block_columns, column, operator, value, result_name) %} … {% endmacro %} ``` The first two parameters, `dbt_model` and `block_columns` , are part of most macros. They are passed down from the canvas automatically so you just simply need to declare them here. The parameter `dbt_model` specifies on top of which data or transformation block the transformation will be applied. The `block_columns` specify which columns should be selected after the transformation has been applied. {% hint style="warning" %} For transformations that are based on multiple blocks, e.g., `join_by_id,`instead of having only one `dbt_model` parameter you might need to have `dbt_model_left` and `dbt_model_right`. {% endhint %} #### 2.2 Setting dbt relations In order to create a lineage graph of your models, dbt uses the `ref()` syntax which resolves to the correct view or table in your data warehouse. ```sql {% macro apply_operation(dbt_model, block_columns, column, operator, value, result_name) %} {% set rel = '{{ ref("' + dbt_model + '") }}' %} … {% endmacro %} ``` #### 2.3 Custom functions For some transformations, you might require custom functions such as mapping a parameter like the `operator`. You can create helper macros and store them under `kuwala/core/backend/app/dbt/kuwala_blocks/macros/utils`. ```sql {% macro apply_operation(dbt_model, block_columns, column, operator, value, result_name) %} {% set rel = '{{ ref("' + dbt_model + '") }}' %} {% set operator_value = get_operator_value(operator) %} … {% endmacro %} ``` #### 2.4 The actual transformation The actual transformation that requires you to write custom SQL code is saved in a query variable which we'll use later on as a subquery and wrap it. ```sql {% macro apply_operation(dbt_model, block_columns, column, operator, value, result_name) %} {% set rel = '{{ ref("' + dbt_model + '") }}' %} {% set operator_value = get_operator_value(operator) %} {% set query %} SELECT *, {{ column }} {{ operator_value }} {{ value }} AS {{ result_name }} FROM {{ rel }} {% endset %} … {% endmacro %} ``` {% hint style="info" %} If you require a different syntax for different data warehouses, you can use `target.type` in Jinja expressions. For example: ```sql {% if target.type == 'bigquery' %} … {% else %} … {% endif %} ``` {% endhint %} #### 2.5 Getting the final query We pass the transformation query along with the `block_columns` to the helper macro to get the final result we will save as our dbt model. ```sql {% macro apply_operation(dbt_model, block_columns, column, operator, value, result_name) %} {% set rel = '{{ ref("' + dbt_model + '") }}' %} {% set operator_value = get_operator_value(operator) %} {% set query %} SELECT *, {{ column }} {{ operator_value }} {{ value }} AS {{ result_name }} FROM {{ rel }} {% endset %} {% set result = get_result_query(block_columns, query) %} … {% endmacro %} ``` #### 2.6 Returning the result As the final step, we are logging and returning the generated query when the macro is executed. We are logging the result so we can pick it up from the subprocess call and save it as an SQL file. The result is also returned so it can be used in other macros as well. ```sql {% macro apply_operation(dbt_model, block_columns, column, operator, value, result_name) %} {% set rel = '{{ ref("' + dbt_model + '") }}' %} {% set operator_value = get_operator_value(operator) %} {% set query %} SELECT *, {{ column }} {{ operator_value }} {{ value }} AS {{ result_name }} FROM {{ rel }} {% endset %} {% set result = get_result_query(block_columns, query) %} {% if execute %} {{ log(result, info=True) }} {% do return(result) %} {% endif %} {% endmacro %} ``` ### 3. Transformation specifications Now, the only thing that's left to do before your transformation shows up in the transformation catalog is to write the transformation specification. The transformation specifications are stored under `kuwala/core/backend/app/resources/transformation_catalog`. Put your file under the fitting transformation category and when you start the backend it will read those files and store them in the backend database. #### 3.1 JSON file The specification of a transformation is written in a JSON file and looks like this (example for our transformation `apply_operation`): {% code title="kuwala/core/backend/app/dbt/kuwala\_blocks/macros/numeric/apply\_operation.sql" %} ```json { "id": "apply_operation", "category": "numeric", "name": "Apply operation", "icon": "calculator", "description": "With this transformation, you can manipulate a numeric column by applying basic arithmetic operations.", "required_column_types": ["numeric"], "optional_column_types": [], "min_number_of_input_blocks": 1, "max_number_of_input_blocks": 1, "macro_parameters": [ { "id": "column", "name": "Column", "type": "text" }, { "id": "operator", "name": "Operator", "type": "text", "options": [ { "id": "add", "name": "Add" }, { "id": "subtract", "name": "Subtract" }, { "id": "multiply", "name": "Multiply" }, { "id": "divide", "name": "Divide" } ] }, { "id": "value", "name": "Value", "type": "numeric" }, { "id": "result_name", "name": "Result name", "type": "text" } ], "examples_before": [ { "columns": [ "order_number", "weight_in_kg" ], "rows": [ [ "63472", "1.2" ], [ "63473", "0.6" ], [ "63474", "0.75" ], [ "63475", "2.0" ] ] } ], "examples_after": [ { "columns": [ "order_number", "weight_in_kg", "weight_in_g" ], "rows": [ [ "63472", "1.2", "1200" ], [ "63473", "0.6", "600" ], [ "63474", "0.75", "750" ], [ "63475", "2.0", "2000" ] ] } ] } ``` {% endcode %} #### 3.1.1 ID The ID has to be unique, all lower and snake case. #### 3.1.2 Category Each transformation has to belong to one of the following categories: [time](/transformation-catalog/time), [text](/transformation-catalog/text), [numeric](/transformation-catalog/numeric), [geo](/transformation-catalog/geo), [merging](/transformation-catalog/merging), or [general](/transformation-catalog/general). #### 3.1.3 Icon The icons come from [FontAwesome](https://fontawesome.com/v5/cheatsheet). You can pick a fitting one and use the id as the value for the icon property. {% hint style="warning" %} If you are using a new icon that has not been used in the frontend yet, you also need to add it to the `IconsLoader` under `kuwala/core/canvas/src/utils/IconsLoader.js`. {% endhint %} #### 3.1.4 Column and parameter types The column and parameter types have to be either `"text"`, `"numeric"`, `"date"`, `"timestamp"`, or `"boolean"`. ### 4. Publish your transformation All you have to do now is to restart the backend and reload the frontend. Your transformation is now displayed in the transformation catalog and can be used on the canvas. #### 4.1 Production use To use your transformation in production via our Docker image, create a fork and submit a PR to the base repository. ### 5. Locating the dbt project All the models that are generated by the transformation blocks are saved in a dbt project. You can find them under `kuwala/tmp/kuwala/backend/dbt`. The folder names are the IDs of the corresponding data sources.