Skip to main content

Ingesting Nulab Backlog Data Into Snowflake Tables: Part I

This tutorial has four parts in total:

I wanted to backup Backlog data onto Snowflake tables. But there was not easy backup options, so I created one and wanted to share it with you.

In this tutorial we will extract data from Backlog and ingest it into Snowflake tables using Snowflake Stored Procedures.

Backlog:
a project management software developed by a Japanese company Nulab, Inc.
Snowflake:
a data storage platform.

Existing Options

It is almost always good idea to use existing tools and solutions. In other words, there is no need to reinvent the wheel. So, before trying to implement my custom solution, I wanted to explore the landscape in search of existing solutions.

Backlog only offers REST API and Web Hook endpoints, no integration or other features. As for Snowflake, although it offers variety of connection options: Snowflake OpenFlow, Snowflake connectors, Snowflake Ecosystem All, Snowflake Ecosystem ETL, unfortunately as of January 2026, there is no native Backlog connector.

There are no first-party (native) connection options, how about third-party options?
I could find some paid third-party solutions such as cdata and workato, but couldn’t find any open-source tools.

If there is no existing open-source solution available, we can build our custom solution.

Note:
There is an awesome ETL & ELT tool called Airbyte. Although it doesn’t have a Backlog connecter, building Airbyte connectors is very easy. A challenging part is deploying Airbyte requires kubernetes cluster.

Custom Solution

There are many ways to create our custom solution. The best approach is context dependent. Your requirements might be different from mine. I will introduce some of the available options, based on your own needs/requirements, you can implement one. Below I will list the interfaces provided by Snowflake and Backlog and their capabilities.

Backlog

Backlog exposes both REST API and Web hook endpoints.

If you want to get notified as soon as some actions happen in Backlog, You should use Web hooks. In my case, however I just want to backup Backlog data to a Snowflake table. Using Backlog Rest API and calling it a few times a day suffices my requirements.

Snowflake

REST API

Although Snowflake has REST API, it does not support inserting data into tables.

Native Programmatic Interfaces

Native Programmatic Interfaces are programming language specific SDKs (Software development Kit libraries) to connect to Snowflake. Nodejs, SQLAlchemy, and Python sdks are some of the available Native Programmatic Interfaces. Check out API Reference for details.

Native Programmatic Interfaces fit the bigger, more complex applications better. As an example, let’s assume we are building a python web application that has many features and functionalities. The Application takes inputs from users, perform certain calculations, communicates with other servers etc.

At some point the application needs to get data from or record data onto Snowflake tables. For this task, we can use Snowflake Native Programmatic Interface such as Python SDK. Application connects to Snowflake using snowflake provided python library to authenticate, and execute necessary queries.

In my case, I only need to record fetched data onto a Snowflake table. No other functionality is needed. Native Programmatic Interfaces are overkill for such a simple requirement.

Stored procedures

Snowflake Stored Procedures are custom procedures created by users. Stored procedures are similar to functions (with some differences). Stored procedures can be written in SQL, Java, JavaScript, Python or Scala. Stored procedures are run within Snowflake (controlled cloud environment).

Snowflake has User-defined functions as well. User defined functions are similar to stored procedures but not the same. User defined functions should be deterministic, and always return a value. While, Stored Procedures don’t have to return a value, and they are most commonly used for administrative tasks such as DDL and DML.

I will talk about Stored Procedures more in the second part of these series.

What to Use and Where To Run

We have a few options to create our custom solutions. Based on which mix of the options we use, we can run our custom solution in cloud providers such as AWS, GitHub, or within Snowflake.

Option 1: [Backlog webhook + Snowflake Native Programmatic Interfaces] runs on AWS lambda functions.
Whenever new event occurs in the Backlog, Backlog webhook calls lambda function, lambda function records data on Snowflake table immediately using Native Programmatic Interfaces.

Option 2: [Backlog REST API + Snowflake Native Programmatic Interfaces] runs on GitHub Actions.
A script executed by GitHub Actions calls Backlog Rest API periodically (e.g. every two hours), and records all the events of Backlog that happened since its last run onto Snowflake using Native Programmatic Interfaces.

Option 3: [Snowflake Stored Procedures + Backlog REST API] runs on Snowflake.
Snowflake Tasks executes Snowflake Stored Procedure periodically. Stored Procedure calls Backlog REST API and records the fetched data onto Snowflake table.

Choose option No 2, if you prioritize easy version controlling, deployment, code review and modifications to code. Choose Snowflake Stored Procedures option if you want to confine and control code, roles, permissions and secrets in one place.

Note: If you choose Snowflake Stored Procedures option, there will be a friction in code deployment and updates.

In this tutorial, we will go with Snowflake Stored Procedures option. For implementation details, proceed to the part two.

Labels:

Comments

Post a Comment

Popular posts from this blog

Introduction to SQLFluff: How to make your SQL code clean and error-free

Image by Jake Aldridge from Pixabay You know oftentimes, the cause of runtime or compile errors and hours of debugging agony is all due to simply a missing semicolon. Have you ever had such experience? If you had, you are not alone. There are two ways to avoid these unfortunate situations: either become a perfect developer who never makes mistakes, or use helpful tools such as linters that can catch these errors early on. I am nowhere near being a perfect developer who never makes a mistake. In fact, I'm probably the opposite of a perfect developer, so even if I wanted to, I wouldn’t be able to teach you how to become a perfect developer. But what I can teach you is using linters. A Wikipedia defines a linter as a "static code analysis tool used to flag programming errors, bugs, stylistic errors and suspicious constructs." If you're not convinced yet on using linters, consider this scenario: in a large project with multiple members, different people tend to ...
Post a Comment
Read more

How To Use KeePassXC Cli

There are similarly named programs: KeePass, KeePassX and KeePassXC (many of which are each others’ forks). Program Condition KeePass primarily for Windows. KeePassX no longer actively maintained. KeePassXC actively maintained and runs natively on Linux, macOS and Windows . Note: GUI version of the KeePassXC has more features than cli version. GUI version has variety of shortcuts as well. Regarding how to use GUI version of the KeePassXC, visit Getting Started Guide . Below features are available only in GUI version. Setting “Name” and “Description” fields of passwords database. Nesting Groups. Creating entry attributes ( open issue ). Adding Timed One-Time Passwords (TOTP). Adding entry with the same title as existing entry. KeePassXC stores all the passwords in passwords database. A passwords database (hereafter referred to as database) is an (encrypted) binary file. It can have any or no extension, but the .kdbx extension is commonly used. The ...
Post a Comment
Read more

Git squash merge explained

There are many ways to integrate changes in git: regular / normal git merge, git squash merge, git rebase etc. This article explains git squash merge by comparing it to regular merge. Let’s use below example: In the repository with default main branch, after two commits, a new feature branch is created. Some work happened in feature branch. feature branch now has 2 commits that it shares with main branch, and three exclusive commits (exists only in feature branch). In the meantime, others worked on main branch and added two new commits (exists only in main branch). git log output of the main branch: c72d4a9 ( HEAD - > main ) fourth commit on main 2c3dd61 third commit on main 0c2eec3 second commit on main 9b968e8 first commit on main git log output of the feature branch: 786650f ( HEAD - > feature ) third commit on feature 21cbaf1 second commit on feature 677bc7f first commit on feature 0c2eec3 second commit on main 9b968e8 first commit on mai...
Post a Comment
Read more