We will be running a webinar on our Opinionated integration of dbt in Keboola platform, everyone is invited!
https://www.keboola.com/webinars/unlock-the-power-of-dbt-with-keboola

DECEMBER 14, 2022, WED, 8:00 PM CET (11 AM PST)

What we shall cover:

1. good & bad & ugly when using dbt - what were the focus points for us when working on the integration
2. Benefits of the solution from both local development and platform orchestration of the whole pipeline
3. Additional features and visual aid designed to help users
4. Guest session - story of jumping on Keboola & dbt, lessons learned and feedback

See you there!

About dbt:
dbt is a transformation engine using code-first software engineering principles. Basically tons of text files defining what to transform and how. The good thing is that it comes with many features out of the box, such as documentation, lineage and testing.  Ther are also many challenges, such as it can become very labor intensive and sometimes cost intensive, if traditional modelling techniques and a common sense is used.
We aim to leverage the benefits of dbt and hopefully diminish some of the disadvantages

Main benefits:

• orchestrate whole pipeline at one place
• trigger dbt cloud jobs, run dbt against your DWH or within Keboola platform
• store dbt artifacts and run stats in one place
• explore dbt docs on one click
• have a local developer environment with cloned data in one command

We are supporting three major use cases from the day one:

We will be posting more informations and videos to help users to navigate new features.
 

How to get hands on it?

To make dbt work, we need two new projects features (that are cool by itself):
read-only-storage and artifacts All new PAYG projects have those features activated by default. All existing projects can request the activation through support request.

This is a net script I have dug for one of our user asking on support how to easily trigger snapshot for multiple tables. Please not this could be (as python transformation) orchestrated for instance in your weekly/monthly flows to create snapshots of critical data that will last longer than automatic time-travel functionality.

Please note it is not ideal, since it exposes the token (but you can either create a dedicated one, or reset your personal after use).
 
All you have to use this in a python transformation and replace is the token and the table list:

table_names = ["out.c-bitbucket.repositories", "out.c-bitbucket.repository_stats"]
 

The whole script is following:

import requests
import sys
 
# Please enter token here:
TOKEN = "{PUT TOKEN HERE}"
 
# List table names here: ["table1","table2","etc."]
table_names = ["out.c-bitbucket.repositories", "out.c-bitbucket.repository_stats"]
 
def send_request(table_name):
    # Create Table Snapshot
    # POST https://connection.keboola.com/v2/storage/tables/out.c-data_model.carpark/snapshots
    call_url = ("https://connection.keboola.com/v2/storage/tables/"+str(table_name)+"/snapshots")
    try:
        response = requests.post(
            url = call_url,
            headers = {
                "X-StorageApi-Token": TOKEN,
                "Content-Type": "application/x-www-form-urlencoded",
            },
            data={"description": "orchestration",},
        )
        print('Response HTTP Status Code: {status_code}'.format(
            status_code=response.status_code))
        print('Response HTTP Response Body: {content}'.format(
            content=response.content))
    except requests.exceptions.RequestException:
        print('HTTP Request failed')
        sys.exit(1)
 
 
for table in table_names:
      send_request(table)

If you have project on other maintainer, such as PAYG in N-EU stack (Azure), please use corresponding base URL.
 

Possible improvements:

1. Create a dedicated component to protect the token (any volunteers?)
2. Combine this with storage API to get the list of all tables so you can just specify the bucket and call the day...

Hope this helps!

Hi all, do you use snowflake workspace or dedicated DWH from the writer with some other 3rd party tool that uses SQLAlchemy under the hood? 

Would you benefit from us providing you direct SQLAlchemy connection string in this popup ui?
 

Please let me know!

Hi Community, I was not sure it has been mentioned here, but there's a new anonymization processor (MD5, SHA512 + salt):

Data Anonymization processor | Keboola Developer Portal

This processor enables you to anonymize specified columns of an input table. In the configuration you specify the method of anonymization and the tables and the respective columns you wish to anonymize. Specified tables and their specified columns are anonymized, the rest is passed through to out/tables. 

example of the use  mentioned in the documentation:

kds_consulting_team / kds-team.processor-data-anonymization / README.md

Hi community, 

Since we are having increased requests on new functionality enabling, I think this simple diagram will be useful to determine the project eligibility:

The crucial piece is the move to Transformations V2 that enables the queue and hence a slew of new exciting features. Some of them heavily influence speed/costs ratio.

For more information and feedback please use the feedback button in the platform - our product team will be glad to hear any feedback that will help us improve!

Hi guys, some of you might notice spammy postings once in a while - we are on it with our platform provider. Please have a patience, you could help us by reporting those posts!

There is a stellar Keboola Academy course for Generic Extractor - I would encourage everyone to start there: 

However, I think this guide might be pretty useful for kickstarting new configuration:

Resources

Official documentation:

• Mentioned Keboola Academy for Generic Extractor https://academy.keboola.com/courses/generic-extractor
• For Users: https://help.keboola.com/extractors/other/generic/
• For Developers: https://developers.keboola.com/extend/generic-extractor/
• Refer to Fisa’s repository for various samples of Generic Extractor configuration
• Github Repo of Generic Extractor with various examples

Steps to follow

1. Authentication type
2. Base URL
3. Basic endpoint(s)
4. Pagination
5. Is Mapping needed? (in most cases - yes, especially for incremental loads)

(Important) If you bounce on some serious issues and you cannot get a solution from your peers, stop wasting your time further and switch to a custom component (Python, PHP, etc.)

Getting Started

To configure your first Generic Extractor, follow the tutorial for ‘Basic Configuration’. Also, review the following sections: Generic Extractor > Generic Extractor Tutorial > 

• REST HTTP API Introduction
• JSON Introduction
• Basic Configuration (same as ‘tutorial’ above)
• Pagination Tutorial
• Jobs Tutorial (special emphasis on ‘Child Jobs’)
• Mapping Tutorial (copy and save the example under ‘Review’ - very useful)

Note: It is important to make use of Column Mapping feature in order to avoid occurrence of duplicates during extraction.

Further configuration

To understand the specific parts of an existing configuration, check the Configuration Map (aka Parameter Map) and review following sections: Generic Extractor > Configuration > 

• API Configuration
• Extraction Configuration
• Iterations
• SSH Proxy Configuration

Iterate through each underlined item of the Configuration Map and review details. Alternatively, go to this page containing useful links for various sections of the Generic Extractor.

Review various Authentication types

Note: Don’t consume yourself with advanced authentication types e.g. OAuth, in the beginning. Start with ‘URL Query, ‘Basic HTTP’, ‘Login’ and know where to come back to in case another authentication type is required.

Adopted from an internal article by @Michal Hruska @Keboola 

Here are many ways to share data:

• Data Catalogue (business-level UX)
• Keboola Storage Writer - PUSH (analyst/engineering-level UX)
• Keboola Storage Extractor - PULL (analyst/engineering-level UX)
• Direct Storage Access - TBD (analyst/engineering-level UX)
• Keboola Storage API (developer-level UX)

Data catalogue

https://help.keboola.com/catalog/

The data catalog represents an overview of data shared to and from the project. The data catalog allows you to share data in a very efficient, controlled and auditable way.

There are several options how you can share data:

• Project Members – To the entire organization. Any user of any project in the organization can link the data bucket.
• Organization Members – To administrators of the organization. Any user of any project in the organization can link the data bucket provided that they are also an administrator of the organization.
• Selected Projects – To specified projects. Any user of the listed projects in the organization can link the data bucket.
• Selected Users – To specified users. Any listed users in the organization can link the data bucket.

Shared catalogue details

Creating new catalogue

Subscribing to existing shared catalogue

Keboola Storage writer

This writer loads single or multiple tables from your current project into a different Keboola Connection project. The component can be used in situations where Data Catalog cannot, e.g., moving data between two different organizations or regions.

https://help.keboola.com/components/writers/storage/storage-api

Keboola Storage extractor

Extractor uses source project storage API token to setup a data extraction tunnel between source project and destination (current project). API token can be limited to buckets, tables, or a single table if needed.

https://help.keboola.com/components/extractors/storage/storage-api/

Direct Storage Access

TBD - as per platform capability feature request

Keboola Storage API

Direct connection to Keboola storage through storage API, as described here:

https://keboola.docs.apiary.io/

Like [3] and [4], it requires the Keboola API token, which can be limited as mentioned before. Storage API supports quick sync and more robust async data load requests, as well as data preview requests, etc. More in the official documentation.

Since we are bringing a feature parity between different stacks (mostly existing stacks and pay-as-you-go one), I think it might be beneficial to discuss the new features and publish a bit of a guide how to do the same (for testing/developing SQL query in workspaces. Lets have a look on SQL workspaces now:

Workspaces

A workspace serves several purposes and can be used as

• an interactive development environment (IDE) to create transformations.
• an analytical workspace where you can interactively perform experiments and modelling with live production data.
• an ephemeral workspace created on each run of a transformation to provide the staging area in which the transformation operates. Ephemeral transformation workspaces are not visible in the transformation UI, hence we won’t mention them further.

When a workspace is created, it enters the Active state and can be used.

• Database (Snowflake, Redshift, and Synapse) workspaces are billed by the runtime of queries executed in them. As such, we leave them in active state until you delete them.

Documentation

Feature comparison

How-to

How-to create workspace to develop and test SQL queries - you can create workspace by clicking on the button on the right:

then you can specify if the workspace should be shared:

once its up, you can click on the workspace detail:

you can see the input mapping has been correctly set (workspace created from transformation):

just click on credentials (link on the right) to get the same creds as you would be used to with "old" sandbox:

​

Let us know what is your experience working with Sandboxes and Workspaces, which features you miss and how can we improve it!