Java as one of the most influential programming languages was originally developed by Sun Microsystems in 1995. With its unique design, Java code is compiled into bytecode that can run on any platform that has a Java Virtual Machine (JVM). This revolution enabled developers to write code once and run it forever. The idea of “write once, run anywhere” made it the most popular programming language in the world at that time.

Java’s key characteristics, such as its object-oriented nature, platform independence, security, large community, and wide range of libraries and frameworks, make it the language of choice for developing large-scale applications. However, its slow startup time and huge memory footprint can be drawbacks for some use cases.

The rise of microservices architecture and containerization technologies that are based on increasing the number of running applications on demand, running applications on low memory and CPU environments, and fast development has reduced the popularity of Java.

Native Image

To fill this gap, Native Image is a technology to compile Java code ahead of time to a native executable that contains the required code, libraries, and runtime.

As Java Native Image don’t need to load full classes, JVM, and all dependencies, they have several advantages over legacy Java applications, for example:

• Faster startup
• Less memory footprint
• Higher security

Migrating legacy applications to Native Image is not an easy task, for example, Spring Boot the leading Java framework uses reflection expensively. The problem with reflection is the JNI compiler is not able to know what classes and methods will be used at runtime. Moreover, the use of reflection with JNI increases security risk. When an attacker finds a way to inject malicious code into the application, they could use reflection to invoke methods and access fields that they should not be able to access.

Quarkus

One of the most successful open-source JNI solutions is Quarkus, a framework that aims to bring Java to modern Cloud-native and containerization architectures with a wide range of supported libraries. In this article, we will explore the basics of Quarkus.

Similar to Spring Boot, Quarkus comes with a collection of development tools to support the Agile way of working. Following the Service template, Quarkus gives you some options to create a new project from the template including CLI, code initializer, and Idea plugins. This not only saves time and effort but also makes services consistent with each other and with the overall organization.

Code Demo

The first step is to install Quarkus CLI

For Mac or Linux, using brew

brew install quarkusio/tap/quarkus

Or using SDKMAN

sdk install quarkus

And Windows

choco install quarkus

Now, let’s start our first piece of work

➜ quarkus create hello-quarkus

Creating an app (default project type, see --help).
Looking for the newly published extensions in registry.quarkus.io
-----------
applying codestarts...
📚 java
🔨 maven
📦 quarkus
📝 config-properties
🔧 dockerfiles
🔧 maven-wrapper
🚀 resteasy-reactive-codestart
-----------
[SUCCESS] ✅  quarkus project has been successfully generated in:
--> /Users/quarkus/hello-quarkus
-----------
Navigate into this directory and get started: quarkus dev

Easy, the CLI generated a sample project from a template. This project contains the skeleton of a Maven project, sample code, Docker files, and whatever is required to run an API.

Let’s have a look at it.

➜ hello-quarkus
➜ tree 

.
|____mvnw.cmd   -- Batch script to run maven
|____pom.xml   -- POM file
|____README.md  -- Default read me file with a basic guideline
|____.dockerignore  -- Docker ignore file to exclude generated files in docker image
|____.gitignore  -- Git ignore file to exclude generated files in Git repository
|____.mvn   -- Home for embadaded maven 
| |____wrapper
| | |____MavenWrapperDownloader.java
| | |____maven-wrapper.properties
| | |____.gitignore
|____mvnw   -- Shell script to run maven
|____src   -- Prjoect 
| |____test
| | |____java
| | | |____org
| | | | |____acme
| | | | | |____GreetingResourceIT.java  -- Sample test for native code 
| | | | | |____GreetingResourceTest.java -- Sample integration test
| |____main
| | |____docker     -- Sample docker files
| | | |____Dockerfile.native
| | | |____Dockerfile.native-micro
| | | |____Dockerfile.legacy-jar
| | | |____Dockerfile.jvm
| | |____resources     -- Project resources
| | | |____META-INF
| | | | |____resources
| | | | | |____index.html
| | | |____application.properties
| | |____java
| | | |____org
| | | | |____acme
| | | | | |____GreetingResource.java  -- Sampel Rest API

It’s time to run the project

➜  mvn quarkus:dev

[INFO] Scanning for projects...

...

Tests paused
Press [r] to resume testing, [o] Toggle test output, [:] for the terminal, [h] for more options>

Now, Quarkus embedded server is running on port 8080.

In a new terminal run the CURL command or visit http://localhost:8080/hello in a browser.

➜ curl http://localhost:8080/hello
Hello from RESTEasy Reactive%

This application has a single and straightforward class, let’s take a look at it.

 ➜ bat ./src/main/java/org/acme/GreetingResource.java

1    │ package org.acme;
2    │
3    │ import jakarta.ws.rs.GET;
4    │ import jakarta.ws.rs.Path;
5    │ import jakarta.ws.rs.Produces;
6    │ import jakarta.ws.rs.core.MediaType;
7    │
8    │ @Path("/hello")
9    │ public class GreetingResource {
10   │
11   │     @GET
12   │     @Produces(MediaType.TEXT_PLAIN)
13   │     public String hello() {
14   │         return "Hello from RESTEasy Reactive";
15   │     }
16   │ }

Line 8: Path of endpoint

Line 11: Rest method type

Line 12: Response content type, in this case, text/plain

Line 14: Response value

To see how it works, edit GreetingResource.java in a text editor and change Line 14 to the following

return "Hello Quarkus";

and save the file. Now check http://localhost:8080/hello again.

➜ curl http://localhost:8080/hello
Hello Quarkus%

For people with a Java background, it’s impressive. Fast and easy, no need for an Idea or a chain of shell commands to re-compile and re-deploy.

In the previous section, we had a look at Quarkus template project. Let’s get more familiar with Quarkus by building a cool new application. We could start with a simple REST API that converts Celsius to Fahrenheit or Fahrenheit to Celsius.

Project description:

1. The Restful API has only one end-point /converter
2. The request payload is a JSON object with the following properties

- value: The original value

- from: The original unit

- to The target unit

For example

{
  "value": 37,
  "from": "CELSIUS",
  "to": "FAHRENHEIT"
}

3. When inputs are valid, the API will return the converted value and expected unit, for instance

{
  "value": 100,
  "unit": "FAHRENHEIT"
}

4. In case one of the values are not valid or the server gets an internal error, the response would be a JSON object with a summary and description of the error, like

{
  "summary": "Internal error"
  "description": "Invalid Operation 'C' to 'F'",
}

Implementation

The first step is to create a project by Quarkus CLI and remove generated sample classes.

➜ quarkus create temperature-converter
➜ cd temperature-converter
➜ find ./src -type f -name '*.java' -exec rm -f {} \;

Then create model objects. Thanks to Java 14+, instead of legacy POJO with lots of boilerplate code such as getters, setters, equals and hashcode methods, we can get the advantages of Record Classes

public record ConvertRequest(Float value, String from, String to) {}

public record ConvertResponse(Float value, String unit) {}

public record ConvertError(String summary, String description){}

It’s recommended to have a custom exception to have better error handling.

public class ConverterException extends RuntimeException {
  public ConverterException(String message) {
    super(message);
  }
}

Quarkus is a modern framework that includes some of the best features of other popular and successful frameworks, such as dependency injection, convention over configuration, and test utils. Step by step we’ll explore them. Now, It’s time to add the converter service to the project.

public interface Converter {
    ConvertResponse convertUnit(ConvertRequest req);
}

Obviously, we must have unit tests for each component of our application. We will see that automation tests are essential for ensuring the quality of native image applications.

class ConverterImplTest {
  ConverterImpl converter = new ConverterImpl();
  
  @Test
  void whenRequestUnitsAreNotValid() {
    ConverterException exception = assertThrows(ConverterException.class,
      () -> converter.convertUnit(new ConvertRequest(0F, "TEST", "test")));
    assertEquals("Invalid Operation 'TEST' to 'test'", exception.getMessage());
  }

  @Test
  void convertCelsiusToFahrenheit() {
    ConvertResponse result = converter.convertUnit(new ConvertRequest(100F, "celsius", "FAHRENHEIT"));
    assertEquals("FAHRENHEIT", result.unit());
    assertEquals(212F, result.value());
  }
}

Quarkus’s default dependency injection, ArC, is a lightweight and efficient library that is based on the Jakarta Dependency Injection specification. In comparison with Spring, it’s designed to be faster and smaller which makes it ideal for microservices. ArC is a build-time-oriented dependency injection which means it validates beans and dependencies during the build process, therefore the chance of runtime bean creation exceptions is minimum. On the other hand, it is more limited and does not support all features in Spring such as aspect-oriented programming and complex dependency graphs.

Although this article does not cover the generated POM file in detail, mastering it is essential for anyone who wants to use Quarkus effectively. If you looked at the pom.xml with a keen eye, you might find ArC in dependencies.

<dependency>
  <groupId>io.quarkus</groupId>
  <artifactId>quarkus-arc</artifactId>
</dependency>

With the fancy Switch Expressions, implementation of the converter service is not very tedious. The @ApplicationScoped annotation indicates that ConverterImpl is a bean and its scope is application.

@ApplicationScoped
public class ConverterImpl implements Converter {
    private Logger logger = LoggerFactory.getLogger(ConverterImpl.class);
    
    @Override
    public ConvertResponse convertUnit(ConvertRequest req) {
        logger.debug("Trying to process {}", req);
        final var from = Optional.of(req).map(ConvertRequest::from).orElse("");
        final var to = Optional.of(req).map(ConvertRequest::to).orElse("");
        
        final var value = switch (String.join("_", from, to).toUpperCase()) {
            case "CELSIUS_FAHRENHEIT" -> ((Optional.of(req).map(ConvertRequest::value).orElse(0F)) * 9 / 5) + 32F;
            case "FAHRENHEIT_CELSIUS" -> ((Optional.of(req).map(ConvertRequest::value).orElse(0F)) - 32) * 5 / 9;
            default -> throw new ConverterException("Invalid Operation '" + from + "' to '" + to + "'");
        };
        return new ConvertResponse(value, to);
    }
}

To support JSON in our application, we need to add the following to the Maven dependencies

 <dependency>
   <groupId>io.quarkus</groupId>
   <artifactId>quarkus-resteasy-reactive-jsonb</artifactId>
 </dependency>

Everything is ready for the controller that handles JSON payload requests and returns results in JSON format. In order to have an instance of converter service in the controller, we use @Inject annotation to inject it into the controller. @Post indicates the API serves post requests and @Produces defines response type.

@Path("/converter")
public class ConverterApi {

  @Inject
  private Converter converter;
    
  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public ConvertResponse convert(ConvertRequest request) {
    return converter.convertUnit(request);
  }
}

Consistency in error handling is one of REST API’s best practices. Exception mapper is a service that automatically maps validation and internal errors into a consistent response format, making it easier for clients to understand and handle errors.

@Provider
public class ConverterExceptionMapper implements ExceptionMapper<ConverterException> {
  private final Logger logger = LoggerFactory.getLogger(ConverterExceptionMapper.class);
    
  @Override
  public Response toResponse(ConverterException e) {
    logger.error("An internal error occurred", e);
    return Response
             .status(Response.Status.INTERNAL_SERVER_ERROR)
             .entity(new ConvertError("Internal error", Optional.of(e).map(Throwable::getMessage).orElse("Internal error")))
             .build();
  }
}

In addition to unit tests, integration tests are required to ensure applications in both JDK and native modes behave similarly.

@QuarkusTest
class ConverterApiTest {

    private Jsonb jsonb = JsonbBuilder.create();

    @Test
    void whenConvertInputIsEmpty() {
        given()
                .header(ACCEPT, APPLICATION_JSON)
                .header(CONTENT_TYPE, APPLICATION_JSON)
                .body(jsonb.toJson(new ConvertRequest(0F, null, null)))
                .when()
                .post("/converter")
                .then()
                .statusCode(INTERNAL_SERVER_ERROR.getStatusCode())
                .body(is(jsonb.toJson(
                        new ConvertError("Internal error", "Invalid Operation '' to ''"))));
    }

    @Test
    void convertCelsiusToFahrenheit() {
        given()
                .header(ACCEPT, APPLICATION_JSON)
                .header(CONTENT_TYPE, APPLICATION_JSON)
                .body(jsonb.toJson(new ConvertRequest(100F, "celsius", "fahrenheit")))
                .when()
                .post("/converter")
                .then()
                .statusCode(OK.getStatusCode())
                .body(is(jsonb.toJson(new ConvertResponse(212F, "fahrenheit"))));
    }
}

After running tests, let’s run the temperature converter and see how it works in action. From one terminal run the application.

➜  mvn quarkus:dev
[INFO] Scanning for projects...

...

Listening for transport dt_socket at address: 5005
__  ____  __  _____   ___  __ ____  ______
 --/ __ \/ / / / _ | / _ \/ //_/ / / / __/
 -/ /_/ / /_/ / __ |/ , _/ ,< / /_/ /\ \
--\___\_\____/_/ |_/_/|_/_/|_|\____/___/
2023-06-24 21:37:30,122 INFO  [io.quarkus] (Quarkus Main Thread) quarkus-unit-converter 1.0-SNAPSHOT on JVM (powered by Quarkus 3.1.1.Final) started in 2.389s. Listening on: http://localhost:8080
...
Tests paused
Press [r] to resume testing, [o] Toggle test output, [:] for the terminal, [h] for more options>

From the other terminal, call the API with CURL command

➜ curl -d '{"value":"37", "from":"CELSIUS", "to":"FAHRENHEIT"}' -H "Content-Type: application/json" -X POST http://localhost:8080/converter | jq
{
  "unit": "FAHRENHEIT",
  "value": 98.6
}

➜ curl -d '{"value":"150", "from":"FAHRENHEIT", "to":"CELSIUS"}' -H "Content-Type: application/json" -X POST http://localhost:8080/converter | jq
{
  "unit": "CELSIUS",
  "value": 65.55556
}

➜ curl -d '{"value":"150", "from":"FROM", "to":"TO"}' -H "Content-Type: application/json" -X POST http://localhost:8080/converter | jq
{
  "description": "Invalid Operation 'FROM' to 'TO'",
  "summary": "Internal error"
}

Looks fantastic 🙌.

Deployment

1- Jar

The traditional way of packaging a Java application is to create a Jar file that contains the compiled code and add third-party libraries and other dependencies together. The generated POM file in the project makes everything easier and more efficient for us by adding an index of required third-party libraries to the Jar file, this means that we no longer need to specify a long list of other Jar files as dependencies in the JRE parameters. By runningmvn package maven will create all required resources under ./target/quarkus-app/

➜ java -jar ./target/quarkus-app/quarkus-run.jar

2- Uber Jar

Sometimes working with a collection of Jar files and dependencies is challenging, for example, we need to transfer all of them in the same folder structure. Uber Jar is another option to pack all classes and dependencies in a fat Jar file. To do that run the following command.

➜ mvn package -Dquarkus.package.type=uber-jar

3- Docker Image

Docker image is the most common way of packaging applications in the Software industry. Under ./src/main/docker there are some Docker files to create an optimum Docker Image.

➜ docker build -f src/main/docker/Dockerfile.jvm -t quarkus-unit-converter-jvm .

To run the Docker image

➜ docker run -i --rm -p 8080:8080 quarkus-unit-converter-jvm

In this article, we get familiar with Quarkus a lightweight, fast, and developer-friendly Java framework that facilitates the development of Microservices, distributed monologic and serverless applications. In the next article, we’ll explore how can we migrate our first Quarkus application to Native image. The source code for this article can be found on GitHub.

Exploring Quarkus, Supersonic Subatomic Java was originally published in Cognizant Servian on Medium, where people are continuing the conversation by highlighting and responding to this story.

Sometimes we need to run Terraform modules with a range of providers that we develop or customise to be used locally, or in an environment with a restricted network connection, for example, without access to the internet. In these cases, our terraform init may not be able to search and download providers from Hashicorp’s registry. Nevertheless, Terraform has introduced a provider installation configuration feature, as part of its CLI configuration since version 0.13, which could solve this issue.

This article will cover a provider overriding method and two types of provider registry mirrors in the provider installation configuration in a CLI configuration file:

• Dev Overrides: overrides source path of particular providers with a local path (good for testing customised providers)
• File system mirror: directs installation of providers to a file system path (e.g. your local path)
• Network mirror: directs installation of providers to a URL (e.g. your internal package repositories)

CLI Configuration File

Mirrors are configured in a CLI configuration file in the environment where Terraform commands will run. As described in Terraform document:

The configuration can be placed in a single file whose location depends on the host operating system:

- On Windows, the file must be named terraform.rc and placed in the relevant user's %APPDATA% directory. The physical location of this directory depends on your Windows version and system configuration; use $env:APPDATA in PowerShell to find its location on your system.

- On all other systems, the file must be named .terraformrc (note the leading period) and placed directly in the home directory of the relevant user.

The configuration file uses the same HCL syntax as .tf files, but with different attributes and blocks.

Dev Overrides

The Dev Overrides was introduced from version 0.14, and is quite straightforward. To configure the dev_overrides, we need a provider_installation block in the CLI configuration like below:

provider_installation {
  dev_overrides {
    "registry.terraform.io/hashicorp/example" = "/home/(your user)/customised-providers/terraform-provider-example"
  }
}

This example is to tell Terraform to find the provider with the name “example” from the path “/home/(your user)/customised-providers/terraform-provider-example”. The path is where we should place our provider executables. The “registry.terraform.io/hashicorp/”, which can be kept the same as a prefix for all other providers, is the official hostname and namespace of Terraform’s Registry.

By using dev_overrides, the terraform init command is NOT needed, and you can go ahead with the plan or apply command.

File System Mirror

To configure the file system mirror, we need a provider_installation block in the CLI configuration like below:

provider_installation {
  filesystem_mirror {
    path = "/home/(your user)/.terraform.d/plugins"
    include = ["local.providers/*/*"]
  }
  direct {
    exclude = ["local.providers/*/*"]
  }
}

In this example, the configuration mirrors the provider registry to a file system with the path “/home/(your user)/.terraform.d/plugins”, and directs Terraform to find providers from “/home/(your user)/.terraform.d/plugins/local.providers/*/(provider name)” when terraform init is run (note that you cannot use $HOME or other environment variable to replace the path “/home/(your user)” since Terraform will not parse the variables).

Therefore, if your module requires the null provider, for example, you need to have it ready in the path so that the hierarchy looks like the following:

.terraform.d
└── plugins
    └── local.providers
        └── local
            └── null
                └── 3.2.1
                    └── linux_amd64
                        └── terraform-provider-null_v3.2.1_x5

In addition to the CLI configuration changes, we also need to specify the provider source in Terraform blocks from the module file to match the path. For example:

terraform {
  required_providers {
    null = {
      source  = "local.providers/local/null"
      version = "3.2.1"
    }
  }
}

After the above are set up, the terraform init command should install the provider for your module from that path and generate some output similar to below:

Initializing the backend...

Initializing provider plugins...
- Finding local.providers/local/null versions matching "3.2.1"...
- Installing local.providers/local/null v3.2.1...
- Installed local.providers/local/null v3.2.1 (unauthenticated)

Things to note:

• If you have multi-layer Terraform modules (e.g. module A calls module B from a different directory), ensure each module’s terraform.required_providers block has the source path like the example above. Otherwise, the module without the configuration will still try to search and download providers from Hashicorp’s registry.
• If your Terraform environment does not have internet access, you may get a checkpoint failure from debug logs. To ignore that, you can add the line “disable_checkpoint = true” to your CLI configuration file.
• The filesystem_mirror.include and direct.exclude are used to match only the providers you want to mirror. If you only want to mirror some particular providers, you can change the values from them. For the example above, the configuration will only mirror the providers which you have specified the source path as “local.providers/xxxx/xxxx”.
• The source path (“local.providers/xxxx/xxxx” used in the example) is also called provider address. It is a three-level address with the syntax hostname/namespace/type (refer to the document for more details). It can be named as whatever you like, as long as it does not exceed three levels and matches the hierarchy naming of your file system for providers. However, if the path has less than three levels (e.g. source=“null”), Terraform will automatically fill the missing levels with its default value. That is, if you specify source=“null” and run terraform init, Terraform will actually treat the provider address as “registry.terraform.io/hashicorp/null” which may differ from your hierarchy.

Network Mirror

The network mirror is similar to the file system mirror, which is also configured from the provider_installation block in the CLI configuration. For example:

provider_installation {
  network_mirror {
    url = "https://artifactory.example.com/"
    include = ["artifactory/*/*"]
  }
  direct {
    exclude = ["artifactory/*/*"]
  }
}

In this example, Terraform will install providers from the URL “https://artifactory.example.com/artifactory/xxxx/(provider name)”, when the provider source matches the specified path, like the following:

terraform {
  required_providers {
    null = {
      source  = "artifactory/terraform_providers/null"
      version = "3.2.1"
    }
  }
}

When you run terraform init with the above configurations, Terraform will search and download the null provider by joining the mirror URL and the source path to form a full URL: “https://artifactory.example.com/artifactory/terraform_providers/null”.

Things to note:

• Like file system mirror, if you have multi-layer Terraform modules (e.g. module A calls module B from a different directory), ensure each module’s terraform.required_providers block has the source path like the example above. Otherwise, the module without the configuration will still try to search and download providers from Hashicorp’s registry.
• Like file system mirror, if your Terraform environment does not have internet access, you may get a checkpoint failure from debug logs. To ignore that, you can add the line “disable_checkpoint = true” to your CLI configuration file.
• Like file system mirror, network_mirror.include and direct.exclude are used to match only the providers you want to mirror. If you only want to mirror some particular providers, you can change the values from them. For the example above, the configuration will only mirror the providers which you have specified the source path as “artifactory/xxxx/xxxx”.
• Similar to file system mirror, the source path (“artifactory/xxxx/xxxx” used in the example) is also called provider address. It is a three-level address with the syntax hostname/namespace/type (refer to the document for more details). It can be named as whatever you like, as long as it does not exceed three levels and matches the hierarchy naming of the full URL for providers. However, if the path has less than three levels (e.g. source=“null”), Terraform will also automatically fill the missing levels with its default value. That is, if you specify source=“null” and run terraform init, Terraform will actually treat the provider address as “registry.terraform.io/hashicorp/null” and the full URL for the provider will become “https://artifactory.example.com/registry.terraform.io/hashicorp/null” instead of “https://artifactory.example.com/null”.

Conclusion

These three approaches are all very useful but are aimed at different use cases. Based on your situation, pick the one that suits you best. For instance, if you need to test a customised provider without a mix with other Hashicorp providers, dev overrides could be a good fit. Or, if your environment only allows network traffic from some specific sources, you could consider one of the two mirror configurations.

Terraform Local Providers and Registry Mirror Configuration was originally published in Cognizant Servian on Medium, where people are continuing the conversation by highlighting and responding to this story.

Apache Airflow

Simply put, Apache Airflow is an open-source workflow orchestration tool. Using Airflow, we build Directed Acyclic Graphs (DAGs) to orchestrate tasks/operations. To learn more about the tool or how to build pipelines for your data platforms using Airflow, look at the following blog.

Building Maintainable Data Pipelines with Apache Airflow

Let us now explore some options for managing data quality using Airflow without further ado.

Data Quality on Airflow

There are several ways we can build a solution to manage data quality on Airflow. In this blog, we will build a pipeline that automates the process end-to-end. Before we get into the details of the solution, let us look at the essential ingredients for making such a pipeline.

• Create / Implement Rules: Firstly, we require a mechanism to define and implement rules for data quality. This step involves interacting with the business users to gather requirements on their expectations of the data. For instance, in a database for an e-commerce website that captures user information, a rule could be that a customer’s age should be between 10 and 100.
• Logging and Monitoring: Once we have created and implemented business rules, we need the means to log if the batch/stream of data loaded into the platform complies with them. As part of the process, we should be able to log successful but, more importantly, instances where the data load fails the business checks.
• Trigger Event: Next, the pipeline should be able to trigger events based on the success or failure of data to meet specific standards. For instance, if the data breaches a business rule, we could start an event to add a record to the database. On the other hand, on success, we can load the data into the platform.
• Alert and Notification: As part of the pipeline, when the data does not meet specific standards, we want to notify the business users or data owners about the breach. The alert and notification can take many forms, as discussed later in the blog.
• Build Reports: Finally, we can build reports to get a glance at data reliability over time. It can be essential for organisations looking to implement strategies to manage organisation-wide data governance/quality. Moreover, It can help the organisation answer the questions such as: Is the organisation getting better with data governance over time or not?

This blog will now look into each component in greater detail and implement a solution on Apache Airflow.

Create / Implement Rules

Airflow offers several options to manage and implement business checks to allow the flow of good-quality data into data platforms. For instance, while developing a solution, we can use SQL Check or frameworks such as great expectations to define and test our business rules. Moreover, having access to hundreds of providers such as Snowflake, dbt, etc., we can leverage their data quality frameworks, for instance, dbt test, in case the data platform uses dbt as a transformation tool. Additionally, Airflow integrates with some providers nicely and makes operators readily available for use. For instance, using Airflow, we can use a range of SQLCheck Operators to perform standard quality checks, such as whether a column meets data standards. Furthermore, with some operators, such as SQLCheckOperator, we can build complex rules by building custom SQL scripts to cater to complex use cases. Likewise, Airflow also offers GreatExpectationsOperator to integrate with the framework.

In this blog, we are going to make use of SQL CheckOperators for demonstration purposes. Let us look at an example where we have a table named ORDERS, and the expected value of the order amount is between 0 and 5000. In such a case, we can make use of SQLColumnCheckOperator as follows.

The above code snippet looks for the column ORD_AMOUNT in the table sandpit.dbo.ORDERS and applies the rule so that the amount is between 0 and 5000. Although the snippet works, there are better practices to hardcode the values as part of pipelines. Therefore, as an improvement to make the data quality check more robust, we can tweak the pipeline as follows:

• Firstly we can create a configuration file to hold all data quality metrics for the entire dataset. It will act as a single source of truth for the business rules and make it easy to maintain the pipeline.

• Secondly, we can parameterise the script so that we do not have to change the code if the configuration changes. For instance, in the config file above, say the organisation expands and starts operating in France; just by adding an extra item to CUST_COUNTRY, the pipeline should be able to work.

In Airflow, we can use Jinja templates to parameterise the script. However, in the case of SQLColumnOperator, the attribute column_mapping is not templated.

To do so, we can create custom operators, just like in the example below, to allow templating of fields using Jinja.

Running the above CustomSQLColumnOperator produces the following log:

From the task log above, we can observe that the test was evaluated correctly and passed the business rule provided in the configuration file.

Airflow offers several other SQL Check operators for different use cases. To learn more about each of these operators, check the following link.

Logging and Monitoring

Logging and monitoring are essential parts of any pipeline, especially for data quality frameworks. Airflow offers a mechanism like Callback to implement logging based on the operator’s state. For instance, in our pipeline above, we can log when the business rule succeeded or failed as follows:

In the code snippet above, we can observe two callbacks for the success or failure of the task. If there is a breach of rules by the data, the CustomSQLColumnOperator will be marked as failed. On failure of the task, the operator calls the callback dq_log_order_fail that prints a short description of the failed task along with the dag’s run id.

Trigger Events

Now that we have looked into logging & monitoring, the next thing while managing a data quality framework is to be able to orchestrate tasks based on the outcome of the quality checks. For instance, in the case of our example above, we would like to orchestrate the following tasks.

• Log an entry in the database to keep a historical track of data quality over time.
• If the data load breaches a business rule, send an alert to a mailbox to notify the data owners/maintainers.
• Load the data into the data platform in case the data aligns with the business rules

Airflow offers a couple of ways to manage such use cases. In this blog, we will look at trigger rules that we can set at an operator level to start a series of tasks based on the check as follows:

We can observe from the DAG above that the CHK_ORDER_AMT has two valid paths. If the check succeeds, we can log metadata into the database and start loading the data into the data platform. However, if there is a breach in business rules, we would like to log some metadata and then alert the data owner/maintainer about the breach. We can implement such a flow as follows:

The code snippet above shows the task log_failure_db will only trigger if the operator preceding it fails. We can configure different trigger rules on the operators based on different use cases; to know more about the trigger rules, check out the following link.

Alert and Notification

Now that we have looked into triggering tasks based on the outcome of the check operator let us look at the final piece of the puzzle. If a load of data fails the check, we would like to alert someone so the business can look into it and take necessary actions. Airflow integrates with several providers, such as Slack, Telegram, etc., to enable alerting and message notifications.

In this blog, we will create alerts using emails via email operators and Sendgrid as an email service to notify business users. We have to set some configurations before Airflow can successfully send the emails.

Configuring Airflow

To set up the Airflow for email notifications, we need to do the following:

• Add necessary configurations in airflow.cfg for the email operator to use.
• Add a connection in the Airflow web user interface for the operator.

We can make use of the following instructions to perform the above tasks.

Configuring SendGrid

Configuring SendGrid is an easy two-step process.

• Firstly, we create an API key in SendGrid using SMTP Relay. We can do so using the following link.

SendGrid

• Next, we need to add senders to the account. Senders are email addresses the email operator will use to send notifications to business users. We can add senders as follows:

Once we setup the configuration on Airflow and Sendgrid we can use the EmailOperator as follows:

Build Reports

Building a data platform is to have an organisation-wide reliable data source. With a tendency for organisations to become data-driven, it can be hard for non-technical users, especially directors, to get accurate metrics of data reliability. As part of the data quality framework, it is essential to have such metrics visualised so that the key stakeholders can strategise and come up with actionable outcomes to improve the organisation. Therefore, using the logging database, we can build dashboards to aggregate statistics on data quality and present them in an easy-to-understand manner to assist key stakeholders with their decision-making.

Conclusion

As a platitude, we often hear that garbage in is equal to garbage out, yet we can not stress enough the significance of it. It becomes crucial for organisations to build strategies to monitor data quality, especially as they move towards being data-driven. In this blog, we looked into a five-step data quality framework end-to-end to enable good quality and reliable data flow into the data platform.

Read Other Blogs on Data

Vachan Anand - Medium

Connect On LinkedIn

https://www.linkedin.com/in/vachan-anand-26bb76b7/

5 Steps for Developing Data Quality Framework with Apache Airflow was originally published in Cognizant Servian on Medium, where people are continuing the conversation by highlighting and responding to this story.

A colleague once said to me that “Kubernetes is the Rolls-Royce of compute”, and only after using it for the last 6 months have I started to realise what he might have meant. Yes it can be expensive to run and maintain, it has all the bells and whistles, but once you get it working, it's a thing of beauty!

To be upfront, my current lens of Kubernetes has been using it as a managed service on Microsoft Azure, which is known as AKS — Azure Kubernetes Service.

A particular feature of AKS which has enabled the project to take advantage of running resource-intensive jobs at a very cost-efficient rate has been Azure Spot Node Pool.

For further context, 10 months ago, the team and I were tasked to build cloud capabilities that could handle a variety of data-related execution tasks. The variety component is any unknown, future setups that could not yet be defined. This could be in the realm of executing data transformations such as dbt jobs, machine learning predictions, and data quality checks to name a few.

To solve this project variability we took a Platform as a Product approach in the hope it abstracts the complexity of deployment while providing a great developer experience. Choosing the right tools, and the way they interacted had to cater to a diverse set of data arrangements for a range of upcoming projects.

The setup we landed on was using Apache Airflow deployed on Azure Kubernetes Service.

Further taking advantage of Kubernetes, our Airflow Tasks are run exclusively using the Kubernetes Pod executor.

The Kubernetes pod executor is in place of other Airflow tasks, such as the Python executor or bash executor, providing you with a verbose yet flexible way to define Kubernetes pods. In essence, to define your Kubernetes pod operator you just point to an image, such as a registered Docker container, define some simple metadata, and optionally add bespoke Kubernetes configurations.

Using Airflow Kubernetes Pod Executor in conjunction with Azure AKS Spot Node pool has allowed us to access large compute resources at a fraction of the cost. (i.e 8GB → 32GB RAM)

An estimated 1/10th of the costs if we’d not be using a spot instance.

For most of the data workloads running on our cluster, the shared node resource group works fine. It also meets the need of having a non-spot instance as a default node pool.

However while running a particular task, which pre-processes data for a machine learning model, the task would consistently error out resulting in an unsuccessful execution 🙅‍♂️.

{
  "status": {
    "message": "The node was low on resource: memory.
                Container base was using 2200***Ki,
                which exceeds its request of 0.",
    "phase": "Failed",
    "reason": "Evicted"
  }
}

We maxed out on our shared node resource group (Standard_D2_v3 — 8GB RAM) and had to get access to a lot more RAM. Slightly perplexed at first as the Standard_D2_v3 box technically has enough RAM, we soon understood that there is a fair amount being used for system processors. This resulted in turning to a much larger box (Standard_D8s_v3 – 32GB RAM) to meet the needs of the job. At the time of writing, D8s_v3 is priced at $320 per month within the US, and dearer outside. D8s_v3 spot instance however brings the costs down to $32. (Please note: there are limitations using spot instances & costs are subject to usage.)

After configuring our new node pool in Terraform (example), and a peer lookover in a GitLab merge request, our CI deployment pipelines were provisioning our new beefy spot instance!

With a few trials and errors in understanding, Kubernetes taints & labels, I was able to knuckle down a winning Airflow task configuration that consistently targets & spins up the pod on the target resource.

example_task = KubernetesPodOperator(
  image = 'acr.azurecr.io/my-image:latest',
  name = "example task",
  task_id = "example-task",
  resources = k8s.V1ResourceRequirements(
    requests = {
      'memory': '16000M',
    },
  ),
  node_selector = {
      "node-pool": "d8sv3spot"
  },
  tolerations = [
    {
      "key": "node-pool",
      "operator": "Equal",
      "value": "d8sv3spot",
      "effect": "NoExecute",
    },
    {
      "key": "kubernetes.azure.com/scalesetpriority",
      "operator": "Equal",
      "value": "spot",
      "effect": "NoSchedule"
    }
  ]
)

Let me highlight the important components of this Airflow task. After the general arguments, we can define our needed resources, node selectors & and tolerations.

The resources might not always be required, but will help ensure Kubernetes is providing the appropriate compute configuration. CodingShower has a great set of examples of resource definitions allowing you to specify CPU, RAM, and storage needs.

We then configure the node selector, which is otherwise known as the node pool label. This is like putting in a preference as to where you want pods to be deployed.

Lastly, detailing the task pod tolerations, as to which taint it can tolerate. Kubernetes is strict on this, and AKS will add taints by default to spot instances. To target a node pool, you’ll need to add a toleration for each taint.

Although there are compelling alternatives to running Airflow, such as Astronomer and GCP Cloud Composer, out-of-the-box Apache Airflow on Azure Kubernetes Service can work great for a range of tasks providing flexible resources and ways to control costs.

Cost Effective Airflow tasks using AKS spot instances was originally published in Cognizant Servian on Medium, where people are continuing the conversation by highlighting and responding to this story.

Introduction

You are probably wondering why am I connecting these two concepts together, the answer is: “This is my work-life”.

To give further context, I was recently diagnosed with ADHD and Autism, which coincidentally sits me in that neuro-divergent sweet spot where my brain doesn’t know what an executive function is and at this point, it’s afraid to ask. I am also a full-time experience and visual design consultant, and in this article I’ll be sharing with you my experience with executive dysfunction and how I’ve learned to overcome its many challenges within the professional consultant industry.

What is it?

Description

To better understand Executive Dysfunction, I think we need to understand what an Executive Function is: “Executive function refers to a set of cognitive processes and mental skills that help us plan, monitor, and successfully execute goals.”

Executive functions include basic cognitive processes such as plan and prioritise, set goals, organise, control impulses, control emotions, think flexibly, initiate tasks and complete, access working memory, and self-monitor.

To experience Executive Dysfunction or EFD: Executive Function Deficit, is to struggle with those processes and skills.

Cause and coexisting conditions

The cause of poor executive functioning has been difficult to pinpoint, as there are many neurocognitive processes in the executive system and each could be compromised by a range of genetic and environmental factors.

Executive dysfunction is often found in people with disorders including anxiety disorder, major depressive disorder, bipolar disorder, attention deficit hyperactivity disorder, schizophrenia, and autism.

Experiencing executive dysfunction can look like

Someone who struggles with executive functioning will likely:

• Frequently misplace or lose things
• Find it difficult to manage time, make plans, multitask, stick to a schedule or finish projects
• Often forget appointments and tasks
• Find it challenging to get started on tasks, even for things you want to do
• Have a hard time keeping your office, workspace, and home organised
• Frequently lose or misplace personal items
• Find it challenging to navigate frustration or setbacks
• Have difficulty remembering information or following directions that involve multiple steps
• Find it challenging to control impulses, emotions and changes in mood, or behaviour
• Have trouble putting complex thoughts or abstract concepts into words
• Have difficulty getting organised, as well as avoiding or delaying getting started on tasks
• Make careless mistakes
• Often talk too much or blurt things out

Please mind that these are some examples, this list is not a checklist. Some people experience all or a number of these. Executive dysfunction can look and feel different to everyone. One of the very common coping mechanisms that people have while having these experiences is to pretend nothing is wrong, better known as masking. This makes it easy to fly under the radar and harder to spot on other people unless they open up about it.

What it looks like in the workplace

Examples

We can see Executive Dysfunction rear its nasty head within the workplace in intrinsically damaging ways as being:

• Chronic problems with punctuality — being too late OR too early.
• Poor attention to detail
• Poor managing skills (self-management, time-management)
• Unable to scope tasks effectively
• Untidy workstations
• Trouble understanding tasks lacking detail
• Appearing too distracted or lazy
• Difficulty explaining/articulating thought
• Not being flexible
• Difficulty regulating emotional responses and appearing too/highly emotional

What it feels like

When almost all of those examples have been used in previous annual reviews or informal assessments, this can affect people’s careers significantly and add to an already poor self-esteem:

• Feelings of shame driven by comparing self to others, or by unmet expectations
• Feeling like a failure or a fraud (imposter syndrome)
• Constant fear of getting fired
• Feeling like you constantly need to “catch up” to everyone else
• Feeling annoyed at yourself because you can’t ‘insert comparison here’ even though you try your darndest
• Burnout from trying hard every day, but being unable to give yourself a break because of aforementioned reasons

These are some of the feelings I personally have had and still have every other day. I need to be conscious of them and push them aside to be able to do my job. Often these feelings come from having bad experiences in a previous job or situation. This makes us more prone to “trying harder” which can look like working overtime, on weekends to try to “balance out” our feelings with actions. This usually leads to burnout, where everything just gets magnified and we are left staring at the wall for 7 hours.

What are some strategies to work around it

Aside from those horrible feelings I shared in the previous section, it also happened that I developed ways of working around my brain. These ways are a big bowl of strategies I keep on hand to be able to problem-solve and navigate everyday life as well as work.

Strategies based on research

Having done some research you will be able to find a vast resource of tips and advice articles, books, videos, etc. The key here is understanding that none of these need to fit you exactly, nor should you think that you should be able to use them and thus fix your whole life.

When doing research, pick and choose whatever resonates with you. But don’t stop there, see how you can change it, reduce steps, add steps and make it your own.

While there aren’t one-size-fits-all strategies around, the good news is that it doesn’t matter what strategy you choose if it works for you.

There are a large number of strategies that float around the internet that are worth a try:

• Cognitive Behavioural Therapy: You may have heard of this one, a therapy style based on changing specific behaviours.
• Redesign environment: Change things around!
• Make information external: Use post-its, a notebook, a kanban board, mural, trello. Have everything visible and easy to reach.
• Use external motivation: Gamify your life, work towards rewards, celebrate even the little things (get that dopamine!).
• Break down tasks: Whenever you get a task and you don’t know where to start, deconstruct it, and look for the task you can start with to get momentum.
• Anticipate inevitable collapse: Sounds serious but, have multiple plans as well as a retreat strategy up your sleeve. This helps reduce anxiety and makes it easier to improvise, adapt and overcome!
• Multitasking is ok: It’s important to remember to be kind to yourself, multitasking is not everyone’s cup of tea, but if it is, don’t punish yourself for it. Have music on, belt out musicals or have an audiobook while working if that helps you concentrate.
• Take note of how and where you work best: Doesn’t need to be a physical note but if your working memory is not great, maybe do write it down. Mainly to know what worked and what didn’t, kind of like a retrospective activity of your different strategies.
• If you get bored of a technique, try a new one: If you get easily bored or find that suddenly a strategy isn’t working anymore, it’s ok! It’s not a reflection of you being able to use that, but more like your brain may be done with that one. So, the quest begins to find a new one (free dopamine!).

Strategies that have worked for me

After writing the last section I realise that I have tried all of them at one point or another. However, I still wanted to go through the ones that are currently working for me:

• Create structure EVERYWHERE (or processes): I have named myself the process gremlin of my team, if you leave something on my lap, I’ll make a process out of it. This has helped me reduce anxiety and know that even when I don’t have a process, I can still make one on the spot. Not really from scratch though, but using deconstructed elements of the other structures I created previously that can help create this new one. (Sounding a lot like Lego)
• Simplify thoughts: Tricky one, but whenever I have a matted idea or task, I make a Mural braindump where I make post-its of all the elements I am sure of and follow my train of thought or explanation if I’m with someone else. Once everything is laid out in the virtual table, it makes it easier to see the common denominators and patterns. I use this one a LOT.
• Strategise all the things: I feel strategies are a big common denominator, close relative of structure and processes. As I mentioned in the previous section, this has been something I’ve practised my whole life, mainly because I’m an over-thinker. I know what you will say, over-thinking will make you anxious! Oh I know. However, you can use it as a tool: think of all the ways your task can go, how people can react and think of how you can navigate those situations. In my experience, it has helped me transform all my meetings gone wrong into learning opportunities and I can have a strategy to steer conversations better the next time.
• Deconstruct tasks alone or with someone: Having used this one a fair bit in my life, there are always situations where I find myself in task paralysis even after I deconstruct a task. Whenever this happens, I reach out to one of my peers and run them through my task and explain where I’m feeling stuck. Then two things could happen: once you explain the task you will see what is getting you stuck and will be able to sort it out. Or, your peer will be able to give you their way of going around it (new strategy alert!).
• Research and exchanging experiences: Anticipating you will get bored of your strategies and always have an ear out for what works for other people has been the most helpful I think.

What can you do to help your peers

Arguably most of the strategies and workarounds that I lightly covered in this article will be helpful for people who experience Executive Dysfunction as well as people who don’t.

These being ideas, advices, brainstorm items, let’s assume I’m adding a please before every statement and that we would be trying our best to:

• Remember everyone approaches tasks differently, including what they find difficult and how they approach problem-solving. (Things that may be second nature to us may not be for someone else)
• Promote safe spaces where it is ok to ask questions and offer support at any level of the organisation. (Moderating knowledge expectations in a team meeting will help people feel at ease asking questions and not feel pressured to understand or know everything)
• Expand and clarify task descriptions and steps — both verbally and in writing.. (This helps when your working memory or auditory reception is not the best)
• Explain roles, responsibilities and expectations in detail. (Better understanding what is expected of you and what is your role helps reduce uncertainty and clarify what other roles mean as well. Eg: when and who to ask for help)
• Share strategies, frameworks, structures or checklists to complete a task. (Share with others what works for you if you see them struggling, or if they come for you to help)
• Avoid making assumptions — check team members’ understanding of the task. (e.g. ask them to explain it back to you)
• Lean on others and let others lean on you. (Teamwork makes the dream work, right? Making yourself available to help others deconstruct tasks or go over their task paralysis can save a lot of time and anxiety)
• Don’t encourage working overtime. (Overtime happens, but it’s important that people don’t think they should do it as it could lead to burnout)
• Be kind to yourself and others, we are all trying our best.

Key Takeaways

• Executive function is a term used to describe a range of skills your brain uses to help you organise and access information and emotions. They enable you to plan and prioritise, organise, control impulses, control emotions, think flexibly, initiate tasks, access working memory, and self-monitor.
• Executive dysfunction (or EFD, Executive Function Deficit) is the term used to refer to difficulties with executive functions.
• Experiencing EFD can deteriorate a person’s self-esteem and mental health. It can keep you in constant fight-or-flight mode and drive you to burnout.
• Working around EFD can look like a set of strategies, frameworks or processes that you can trial and iterate as you go along. There is no one-size-fits-all solution, the best way is to take on what works for you and create a bespoke solution for yourself.
• You can help peers with EFD by following up conversations with emails or written explanations, explaining roles and responsibilities, keeping in mind not everyone’s brain works the same and being patient. Creating a safe and transparent environment for people to speak up can help them seek guidance and support when they need it.

Thank you so much for reading and do let me know if you found this helpful or if there are things I should add or take out.

Resources

The following are some references I used to create this article.

Articles

• Healthline: Understanding Executive Dysfunction and How It Shows Up: https://www.healthline.com/health/executive-dysfunction
• ADDitude Mag: What Is Executive Dysfunction? Sign and Symptoms of EFD: https://www.additudemag.com/what-is-executive-function-disorder/#:~:text=Executive%20dysfunction%20is%20a%20term,%2C%20organization%2C%20and%20time%20management.
• ADHD Australia: The role of Executive Functions: https://www.adhdaustralia.org.au/about-adhd/the-role-of-executive-functioning-in-adhd/
• Positive Partnerships: Executive Functioning https://www.positivepartnerships.com.au/resources/practical-tools-information-sheets/executive-functioning
• Autism Awareness: Common things people with autism find difficult https://www.autismawareness.com.au/adults/common-features
• Psychology Today: Executive Function: https://www.psychologytoday.com/au/basics/executive-function
• Wikipedia: Executive dysfunction: https://en.wikipedia.org/wiki/Executive_dysfunction
• Wikipedia: Executive function: https://en.wikipedia.org/wiki/Executive_functions

Books

• Driven to Distraction (Revised) Recognizing and Coping with Attention Deficit Disorder by Edward M. Hallowell John J. Ratey
• Thriving with ADHD Workbook for Teens Improve Focus, Get Organized, and Succeed l by Allison Tyler LCSW
• Thriving with Adult ADHD Skills to Strengthen Executive Functioning by Phil Boissiere MFT [Boissiere MFT, Phil]
• Order from Chaos The Everyday Grind of Staying Organized with Adult ADHD by Jaclyn Paul

Videos

• ADHD Spotlight — Em Rusciano: https://youtu.be/4v88Wd20GiU
• Autism in Tech: on being autistic and building neuroinclusive workplaces in the technology industry: https://www.youtube.com/watch?v=k9X5tkVyM_A
• ADHD in Digital: webinar with Ani Møller (Professional Audie) and Julia Birks: https://www.youtube.com/watch?v=96tqUPhG5qs

Helpful links

• Beyond Blue: Free support for anxiety, depression and suicide prevention including 6 free sessions with a mental health coach: https://www.beyondblue.org.au/get-support/newaccess-mental-health-coaching
• Eating Disorders Victoria: Free individual, group sessions and registered nurse to help make a recovery plan:
• QLife: Free call or chat connection support for LGBTQI+ and allies: https://www.qlife.org.au/get-help
• RUOK: More links for immediate support, useful tools & resource and other services: https://www.ruok.org.au/findhelp

Consulting with Executive Dysfunction was originally published in Cognizant Servian on Medium, where people are continuing the conversation by highlighting and responding to this story.

In 2010 when Continuous Delivery was published, the job was to take manual build and deployment processes and automate them. Today your organisation is more likely to already have ‘CI/CD pipelines’ but find that they are not fit for purpose — for example, the release cycle requires multiple pipelines to be run in ‘just’ the right order, with the right variables set by hand, etc. In this context the job is to design new or refactored pipelines in an environment where ‘pipelines’ already exist; the key word there being ‘design’.

Now if you send a DevOps engineer off to design a pipeline, it’s quite possible that they come back a week later with a 1,000-line pull request containing a mix of YAML, Bash, Haskell, and unvetted third-party components. ‘It just needs testing’, i.e. ‘debugging’, i.e. ‘the other 95% of the development effort.’ No design in sight. (Or at least this is how I did CI/CD pipelines when my seniors asked me to do them.)

Therefore there is a need to put some structure around the process of designing CI/CD pipelines, which this article will set out.

Understanding the platform

The term ‘CI/CD pipeline’ is a much-abused phrase which really just means some automation that helps you build, test and deploy your code (regardless of whether you’re actually doing CI and/or CD). In practice, this usually means writing some YAML to define a ‘pipeline’ in something like Jenkins, TeamCity, Azure DevOps, etc.

The structure of the pipeline is based on a few common concepts. The names of these concepts vary across platforms but they should be pretty easy to map.

The terms ‘server’ and ‘agent’ need to be defined. The ‘server’ is sometimes called an ‘orchestrator’, and may be hidden in the cloud. The ‘agent’ is what runs your jobs, e.g. a physical machine, virtual machine, or container. The architecture usually looks something like this:

In the simplest case, all of this is encapsulated in a vendor’s SaaS offering. As your situation becomes slightly less simple, you grapple with issues like authentication. In the simple case, you click ‘give me a pipeline for this repo’ and it just works. In a less-simple case, you need to provision an access token from your source control repository and get this into the agent’s hands so that it can pull your source code. Related issues include setting up webhooks so that the pipeline server knows about new PRs, merges to trunk, and other events which might serve as triggers. The list could go on forever: you will need to feel your way to a certain extent.

However, there are some questions you should ask up-front when adopting or setting up a ‘CI/CD pipelines’ platform, and again before adding a new pipeline to your estate:

How are agents provisioned?

• Manually?
• Infrastructure-as-code outside of the ‘pipelines’ platform?
• Provisioned by the server?
• Single-use (transient) or long-lasting?
• Do jobs run concurrently on the one agent?
• What software and configuration needs to be provisioned on each agent, and how will this be done?
• Do agents need to be segregated, e.g. separate ‘production’ agents?

How does networking work?

• From the developer to the portal?
• From agents to the server?
• From agents to source code repositories, artifact repositories, etc?

How do authentication and secret management work (for all components in the architecture)?

Ideally, these questions are answered once and form the basis for a set of documented patterns for ‘how we do CI/CD pipelines at [insert your organisation’s name here]’. This frees up brain space to deal with the issues specific to a particular pipeline, which we will discuss next.

Design process for a pipeline

Every pipeline is different (somehow!) but I find that the design process involves much the same steps each time.

Step 0: Understand the architecture of the software being built/deployed

It’s easy to say ‘let’s automate the build’ without a proper understanding of the thing being built. Look for documentation about the following:

• software components and their relationships (e.g. this library depends on that library)
• runtime components and their relationships (e.g. this service talks to that service)
• technologies in use (e.g. Kubernetes, .NET, PHP, React) and their respective roles.

Step 1: Requirements gathering for the pipeline

Use the following as a checklist:

• Business value and the ‘customer’ for the pipeline. Ideally, you will have done a ‘value stream mapping’ workshop to get a comprehensive view of what is important about this pipeline and how it fits into the broader picture.
• Inputs such as source code (one or perhaps several repos/branches) and build artifacts.
• Outputs such as build artifacts and deployed services. (This is really the point of the pipeline, I suppose. But, refer again to the ‘business value’ and ‘customer’ for the pipeline, because it may be more complicated than that.)
• Triggers, e.g. on a schedule, upon a commit to trunk, when a new build is published to the artifact repository, etc. Where multiple source repos are involved, you have to decide which one(s) trigger the pipeline to run.
• When triggered by a commit, be clear about which branches should be considered and whether there are any other conditions, e.g. only run the pipeline if certain paths were changed since the last pipeline run.
• Whether multiple concurrent pipeline runs are allowed, and whether multiple concurrent trigger events should cause multiple concurrent pipeline runs, or whether runs should be ‘batched’.

Step 2: Sketch the pipeline as a diagram

This can just be a set of boxes with arrows between them to denote “after this, do that”. The basic questions are:

• What needs to be done?
• In what order?
• Is there any conditional logic (e.g. skipping steps in certain circumstances)? Make sure to account for all possibilities (‘if X then Y else Z’) — don’t leave the ‘else’ part implicit, or at least document it.
• Are any approval gates required? Note that approval gates should be driven by requirements collected in step 1, not added just because they seem like ‘good practice’ for a pipeline. Approval gates serve specific purposes, such as recording the outcome of manual testing or protecting an environment from unauthorised deployments. If the purpose of an approval gate is not clearly documented, each person will make up their own explanation, leading to conflict later.

This diagram is important because this is the visual aid you use in conversations about scope. ‘Is X included?’ ‘Yes, here it is in this box.’ or ‘No, where do you think we should add it?’

The first sketch of this diagram might be very simple, e.g.:

By the time you get to step 5 below (design review), it might have some more details, e.g.:

Where any steps are repeated (or parts of them are common), plan to use templates. If the platform’s templating facilities are inadequate for the task, use what UML would call ‘a class implemented through copy-paste’. Even if the real pipeline is WET, the design should be DRY.

It’s important to avoid unnecessary detail or noise in the diagram, because that will obscure important points like ‘there are no tests being run in the pipeline!’

Step 3: Find implementations for each step

For each step in the diagram from step 2, write down how it will be implemented. Recall from the logical architecture earlier in this article that, once you get through all the pipeline orchestration, you’re just running programs on the agent.

Your ‘pipelines’ platform may come with built-in or off-the-shelf ‘task definitions’ or ‘plugins’ to perform specific tasks such as run an Ant build, build a docker image, etc. These should be used in preference to writing your own implementation, since custom code is a liability. They should also take care of installing the required programs and dependencies, and guide you through their configuration. (However, if the off-the-shelf components are not suitable then refer to step 4 below.)

Where third-party components are used (e.g. plugins from a ‘marketplace’), flag them for architectural review.

Step 4: Design custom code

For any of the steps that don’t already have off-the-shelf implementations, you need to write custom code. Before writing this code (except for spikes and proofs-of-concept), write the following down on the pipeline diagram:

• What technology will be used, e.g. Bash, Python, Java, Haskell?
• How will it be monitored, maintained, etc, as would any other software used in production?
• How will runtime dependencies be provisioned on the agent?

If the custom code seems ‘straightforward’, question what detail is being hidden. For example ‘one line of bash’ might actually invoke some third-party tool that needs to be provisioned, configured, and kept up-to-date.

Step 5: Design review

Steps 1–4 should be timeboxed to say 1 day to avoid waste. It is better to review a draft and iterate. Furthermore, steps 1–4 should not involve any actual implementation, apart from spikes and proofs-of-concept (which should be explicitly managed as such).

The design review is where the engineer says to themselves, ‘this is a good design’, or gets a peer or senior to perform the review, depending on what you want to do. This is also the step where the selection of technologies (e.g. Haskell!) and any third-party components are scrutinised by the appropriate authorities within the organisation.

Step 6: Implement the pipeline

Write a pipeline YAML (or clicky-click, depending on the platform) to reflect the design in your pipeline diagram. For any steps that require custom code, develop it.

Note that while implementing the pipeline, it is best to avoid repeatedly running the pipeline to troubleshoot issues, particularly the behaviour of custom code. You should understand your agent’s configuration and environment well enough to simply run each step outside of the pipeline until it works. If for example, you have a bash script that ‘works on my machine’ but mysteriously does not work in the pipeline, go back to the first part of this article and resolve the mystery.

Test-driven development can be cumbersome at the ‘pipeline’ level due to the ‘low-code’ and ‘cloud-hosted’ nature of the tool. However, any custom code can and should be subjected to the same testing as any other software you write.

Step 7: Put the pipeline into production

Putting a CI/CD pipeline ‘into production’ is always a strange thing to talk about because development tools are usually considered separately from the organisation’s ‘production’ environment and systems. But in terms of the pipeline as a product in its own right, if it is being used by developers then it is in production.

When replacing existing pipelines (or even when replacing manual processes), run the pipeline in parallel, i.e. trigger both versions to run. Take care not to have conflicting outputs e.g. one pipeline overwrites the other pipeline’s build artifact. This allows you to take your time to validate the behaviour of the new pipeline before using its outputs and switching off the old one.

Step 8: Iterate

If there are big problems, go back to step 1.

If not, go back to step 6 and implement the next round of improvements, optimisations, etc.

Some things to watch out for include:

• disk usage on agents;
• CPU and memory usage on agents (saturation or under-utilisation);
• time taken for pipeline to run — e.g. impact on Developer Experience and lead time from code to release;
• logic/waiting happening on agents — whether this can be shifted from the agent to the server to reduce contention, costs, etc;
• the balance between pre-provisioning tools and data (e.g. base images) onto agents, versus installing them during each pipeline run.

Note that I list these things here under step 8, not earlier, so that the design process is not derailed by premature optimisation. However, once these things do bite, there is no reason to delay addressing them.

Conclusion

The careful reader will observe that I have not said anything about how to design CI/CD pipelines well, only how to design them. You can design a bad pipeline with this process, but it will be obvious to others when you do so. On the other hand, this process will help you and your colleagues to collaborate, get a shared understanding of requirements, and efficiently get on with implementing something that will help developers get their code into production more quickly, safely, and reliably. Hooray for DevOps! :-)

How to design CI/CD pipelines was originally published in Cognizant Servian on Medium, where people are continuing the conversation by highlighting and responding to this story.

Cutting through the noise around data warehousing

Signal vs. Noise

With the appropriation of the term data warehousing to mean a whole lot of things it shouldn’t, the general understanding of what a data warehouse is has become a nomenclature problem more than anything else. Many people understand that the distinction between a data warehouse and a database is solely that of the underlying infrastructure.

Although infrastructure plays a significant role in building a data warehouse, data warehousing has one more aspect, which, I think, supersedes in importance — data modeling. Because of the failure to acknowledge data modeling as one of the core ideas behind data warehousing, the common understanding of data warehouses is flawed.

This article will take you through the three core ideas behind data warehousing and also discuss how data modeling is central to creating a data warehouse:

• Data modeling
• Storage architecture
• Processing architecture

The article will also discuss how data warehouse platforms are pre-packaged products with a combination of storage and processing and how the data warehouse platforms are not themselves data warehouses. For instance, contrary to popular belief, dumping data into a data warehouse platform like Redshift or Snowflake doesn’t make it a data warehouse. There’s more to it.

Let’s dive straight in.

Three Core Ideas Behind Data Warehouses

TL;DR — data models, storage architecture, and processing methodology.

Data Models

First, the database data model fed by applications, third-party integrations, IoT devices, etc., is meant to optimize the application's performance. You set yourself up for failure when you start overloading those databases with analytical workloads. That is not to say that these databases can’t partially support analytical workloads because they’re modeled a certain way. Still, they should only be used sparingly for such workloads, directed mainly by the application layer.

How a database is modeled depends on how the application wants to access the data. With relational databases, for instance, applications want to get all the data for an application entity like customers, products, brands, etc. Each entity has extensions based on its actions, such as customers having transactions, brands having products, products having prices, etc. The database model is created in such a way that the application is easily able to access any of these given entities efficiently.

Let’s look at a simple relational database model that uses an application that allows customers to purchase products belonging to various brands. The different entities in the following diagram have been created to cater to specific features of the application, such as customer signup, customer ordering a product, and customer adding a new address.

The design is based on the actions that can be performed on the application. When you start querying the database with this data model (and remember that my example shows an ultra-simplistic data model — in real life, you’ll find much more complicated data models), not to serve the application but data analysts, ML engineers, and data scientists, you’ll start facing two significant problems really quickly:

• Slow queries — this shouldn’t surprise you as you will have to join many tables to reach the desired output. You’ll need to take care of indexes, partitions, data types, disk-intensive operations, etc. Remember that slow queries have a genuine cost attached — they cost a lot more in resource time and block other analysts from doing their work.
• SQL verbosity — not just the joins but overall unpreparedness for handling such workloads will make your SQL queries much more verbose than they need to be. You’ll have data types that are great for the consumption of the application but not for data analysis (at least not in their native form) — array, JSON, etc.

To understand more about this, say you want to calculate a month-wise summary of successful transactions for every product in the catalog. You can use the following query to do that — just notice how you have to navigate through different tables to get different pieces of information necessary for your query. And this works completely fine for an application because they are built to serve application features, not aggregates.

Now, suppose you have hundreds and hundreds of such tables to navigate and want to simplify the process of answering business questions. In that case, you must create another data model that serves the purpose. We’ll talk about many data models later, but here’s the simplest one that broadly categorizes all your source tables into facts and dimensions.

Facts are the things you want to measure, such as sales, response time, etc., and dimensions are the things you want to measure across, such as sales by store or sales by country. The following diagram isn’t an accurate description of a data warehouse model. Still, it loosely demonstrates how you will reorganize your data into these two different types of tables in a data warehouse:

When you want to deal with hundreds of facts and dimensions, you want to see the metrics measured. A dimensional model allows you to do that. If you want to just look at transactions by product, you don’t really have to know which brand the products belong to. If you have to measure the sales by the customer's region, you don’t even have to know who the customer is.

Remodeling a relational database into a data warehouse data model allows you to connect the measures directly with the dimensions instead of a complex route that you have to take, making a lot of joins. Here’s a simple example of measuring a month-wise summary of transactions by product and by brand separately:

Remember that the above query will structurally remain almost the same, and it would, more or less, be the same number of lines irrespective of the dimension you want to see a metric across. The story will be slightly different when you want to see a metric across multiple dimensions, but you’ll still be far better off than querying a relational data model.

To summarize, you can use several data modeling techniques when creating a data warehouse. I’ll briefly touch upon these different data warehousing data models:

• 3NF (third normal form) modeling — owing its origins to the entity-relationship modeling methodology, 3NF is also widely used in data warehousing to serve as a normalized layer to the further layers. It provides tremendous flexibility but can end up having really verbose queries.
• Dimensional modeling — a modeling technique that restructures the data into denormalized fact and dimension tables to make analytic workloads easier to run. The most popular exponents of this method are the star schema and snowflake schema.
• Data Vault modeling — a hybrid between 3NF and dimensional modeling, the Data Vault model is much closer to 3NF than to the dimensional model. It tries to keep the best features of 3NF, such as ease of querying highly granular, historical data, and still restructures the data into new types of tables, such as satellites, links, hubs, bridges, and PITs.

The choice of the data model should be impacted by the type of workload you expect, the scale and speed at which you want the data warehouse to operate, the agility with which you want to adapt to changes in the source systems, and the cost you’re willing to pay for all these things. But the model alone doesn’t seem so critical. It’s just one piece (but a very important piece) of the puzzle. Once you choose the model, you’ll have to store it on memory or disk. Let’s talk about the different storage architectures and their impact on your data warehousing endeavor.

Storage Architecture

Second, the application databases' storage architecture (both on disk and memory) is built to serve a specific purpose. Usually, applications use several databases to account for ACID compliance, schema flexibility, in-memory storage, etc. They all have a particular workload they are best designed to support. For instance, as mentioned in this first point, MySQL is designed to support applications that need to read, write, or update a record or a set of records at a time, which means that it makes sense to keep whole records physically close on the disk in a contiguous manner.

In contrast, analytical workloads usually need the ability to look through specific columns all at once for calculating aggregates, peeking into values of the same column for the next record or several records in a defined window for calculating moving averages, cumulative sums, and so on. Row-based storage won’t make much sense to run those queries. The following image loosely represents the access pattern on disk if you want to access a single column for calculating an aggregate in a row-based storage structure (as in MySQL or PostgreSQL):

Relational databases work in pages (MySQL, PostgreSQL). These pages continuously move in and out of the memory based on the workload. You can think of the above block as a page in a database. Even if you need a single record, the whole page gets accessed and moved to the memory. Imagine analytical workloads requiring a single column stored across many database pages. That can’t be efficient! It isn’t.

To cater to analytical workloads, it makes much more sense to store the data to be aggregated at the exact physical location, the most granular representation of which is a database page. And that page, for column-based storage, will conceptually look something like the following:

The storage arrangement shown above should make it clear how by accessing a small block of disk or memory, you can calculate aggregates, peek into specific columns of leading or lagging rows, etc., for your analytical workloads. The benefit of storing columns contiguously doesn’t end with a smaller search area for specific queries; it also helps compress the column data more efficiently as you are now applying compression to a block of storage that contains data with the same data type. Compression algorithms save more space for specific data types than others.

Processing Methodology

Third, the processing methodology and the processing infrastructure significantly differ in handling your workloads. Traditionally, relational databases have served mid-sized applications using a single-node deployment with appropriate provisions for read-intensive workloads, point-in-time backups, and failovers.

The philosophy of single-node databases is based on symmetric multiprocessing. With most processors being multi-core, operating systems can split the load between those cores while sharing all other resources, such as IO bandwidth and memory of the system.

This obviously doesn’t work at a scale where you have to process enormous amounts of data in a very short time. Enter massively-multiprocessing systems. With MPP systems, you can use the age-old computer science method of divide-and-conquer to break down a processing task into many different nodes, which have their processors and do the work in parallel, consequently saving a lot of time for businesses. Here’s what a typical massively parallel processing system looks like:

Most modern cloud data warehouses are architected as massively parallel processing systems. Popular examples of MPP systems are Snowflake, Databricks (Spark), Redshift, and so on. When choosing between SMPs and MPPs, you need to understand the kind of read-and-write workloads you’re going to have, how you’ll store the data, and the money you’re willing to spend to get the results of your queries faster.

Epilogue

Having gone through the core ideas — data modeling, storage, and processing, it is clear that the identifying feature of a data warehouse is its data model. The storage and processing architecture depend on the scale and, hence, come later. For a business with low volumes of data, you can start your data warehousing journey by modeling one and exposing it using a traditional relational database, such as MySQL or PostgreSQL. Nothing says you can’t do that.

Besides the volume of data, at the heart of the data warehousing question also lie the variety of access patterns you expect. Both data volume and access patterns help you navigate the data warehouse storage and processing space much better. In contrast, the choice of data models is usually driven by how fast you want to deliver and how agile you want to be when the systems feeding the data warehouse change.

How you understand data modeling, storage, and architecture will decide how you understand data warehouses. Are data warehouses just about data platforms, i.e., a pre-packaged infrastructure for data processing, or are they more than that? Data modeling is the key to answering this question.

Ingredients of a Data Warehouse was originally published in Cognizant Servian on Medium, where people are continuing the conversation by highlighting and responding to this story.

Recently as a part of my interest in Databricks, I am going over the fundamental approaches of Delta Lake. The article can be a revisit to many and for anyone new, let’s learn.

For a quick overview, we know with the enormous increase in data for engineering, we need to ensure that it remains intact and valid from source to target. The open-source storage layer called Delta Lake ensures ACID properties during any ETL process in Databricks. It needs much extra work to bypass the implemented ACID properties of Delta Lake if someone wants to. The data are processed and stored at different data layers(tables) as mentioned below:
i) Bronze: Stores the raw data.
ii) Silver: Stores the cleaned and refined data.
iii) Gold: Stores the data that had taken the shape of information to be used by other data-analytics platform

One of the features of Delta Lake is Schema Enforcement and Evolution to handle the vast amount of data consistency. The files are stored in parquet files. As we go further, we will see how schema evolution and validation are considered over the “coffee” data.

Pre-work done:

The below work has been done behind the scenes :)

i) Azure platform is being used to store the data(CSV files in ADLS Gen 2 storage account) and launch the Databricks workspace for processing.

ii) An “all-purpose” cluster had been created. All work is done using the ‘default’ schema of Databricks workspace and all operations are performed using notebooks.

Schema Enforcement

To begin with, Schema Enforcement implies enforcing the fact that the schema of incoming data cannot have any additional columns or change of data types (i.e, int → string type) from the existing schema of the table. However, having fewer columns is allowed.

We are trying to construct a delta table named “coffeedetails” table containing the incoming coffee data. Below displays the first set of data that has been entered into the delta table.

The below shows the existing tables in the datastore of the workspace. There are two pre-existing tables (constructed earlier) ‘coffeelist’ and ‘coffeemenu’.

The timestamp and filename of the respective data are added during the runtime for the creation of the delta table as shown below.

For the second set of data, we have data without the “Price” column as compared to the previous set as shown below.

The data is being entered and the final table is shown below.

The later stage shows how an additional column invokes schema enforcement. This ensures that no change to the delta lake schema occurs. At the same time, the new set of data has an additional column as “Quantity”. An error is thrown as a schema mismatch. This ensures that the data can remains structure remains constant which can be used in various platforms like BI Analytics, Machine Learning, etc. Also, the column names that vary with the case are not being entertained unless the parquet files are overwritten, for example, ‘item_no’ replacing ‘Item_No’ does not reflect. This also ensures the prevention of data dilution.

Schema Evolution

With changing times, data maturity is evolving, so it's the business choice if they want to add up more columns, here is where schema evolution takes place. Along with schema enforcement, delta lake also offers schema evolution that implies adding new columns or changing the datatypes of a column. Here for some business reason, we need to add the “Quantity” column hence we perform the below operations and we can clearly see that the column “Quantity” was added to the table.

Below shows the metadata obtained for the final delta table

If we want to change the column names, drop a column, or change the data type of an existing column, then we need to overwrite both schema and data in all parquet files.

In any case, if the same column (field) with data of a different datatype comes in, the below error pops up.

At the next level, we are trying to fetch the history of actions taken over the tables. This gives an overview for restoration (time travel) of tables in case of the occurrence of any error or failure.

Recovering a version of the delta table

This action is also called “Time travel” through data. Sometimes, in any case of failure or error, we can extract a particular version of the delta data either by version number or timestamp to investigate and restore the latest correct data. Here we get back to version 0 of the delta table.

After restoring the delta table to its version 0 (the initial version of the table without any additional transformation).

The above steps demonstrate one of the various key features of Delta Lake in Databricks: Schema Understanding (Enforcement and Evolution). Hope it is a helpful crisp recap /learning. Moreover, a big thanks to Servian for providing the cloud platform to learn and practice.

Revisiting Databricks Schema Validation and Evolution over Coffee. was originally published in Cognizant Servian on Medium, where people are continuing the conversation by highlighting and responding to this story.

Any application is primarily built on top of data. This data can reside in various types of storage options such as spreadsheets, databases, on-premise or cloud storages. Therefore, a data source becomes a foundation for building a robust, reliable, and scalable digital solution. Knowing various aspects of a data source in the initial phase of the development process, including its benefits, drawbacks, volume of data it can hold, and connectivity to development platforms like Power Apps can save developers and other stakeholders a huge amount of their effort, time, and money.

This article gives a quick overview of Power Apps, one of the core products of Microsoft Power Platform, and briefly touches upon the reasons for its popularity as an application development platform. It delves into some of the popular data sources used with Power Apps, the steps to establish a connection, and the limitations of each data source with the aim that the readers would make an informed choice of an appropriate data source before starting actual app development.

Intro to Power Apps

Power Apps is a platform that provides an environment for the rapid development of custom applications and automation of business processes/workflows. It is a low-code/no-code solution to quickly build and deploy a logical or digital process on an interface, having a responsive design that can run on both mobile devices and web browsers. The intention of this platform is to empower the team and accelerate yet simplify the app development process.

What makes Power Apps great?

Power Apps provides a suite of data sources, connectors, UI elements, and various services that can enable an app to interact with different cloud services and ecosystems such as Azure cloud components, Microsoft Dataverse, Microsoft 365, SharePoint, OneDrive, SQL Server, Amazon Redshift, Teradata, etc.

Gartner has recognized Microsoft as a Leader in the 2021 Magic Quadrant for Enterprise Low Code Application Platforms report.

Data Sources

Some of the popular data sources used with Power Apps are Microsoft Excel, SharePoint List, SQL Server, and Microsoft Dataverse (formerly known as Common Data Service).

When starting with a Power Apps project, it is essential to consider and understand where the data will be stored and the number of rows or columns that will be required by the app before jumping on the user interface design. This would help avoiding data source limitations and issues with data filtering and delegation in the future.

Power Apps can retrieve up to 2000 rows from its data source connections where delegation is not supported. Therefore, it becomes essential for developers to judiciously and effectively use filtering functions that are delegable, to be able to bring in relevant data into the app.

1. Microsoft Excel

Excel can be a viable option to choose as a data source for either personal projects or a quick proof of concept on a small tabular dataset with a few thousand rows or if external data is stored in spreadsheets.

Steps to connect :

a) Open a blank canvas app on the Power Apps web portal https://make.powerapps.com/

b) In the Data pane, add Excel Online as a data source.

c) Select a location of the excel file ➜ Select the excel file ➜ Select the excel table within the file

d) Select an identifier from the excel table or insert an auto-generated id into the table and click on connect to view the excel table in the Data pane. Once the selected excel table is visible in the Data pane, it is ready to be consumed within the app UI.

Limitations of Excel Online (Business) connector :

• Excel file data should be formatted as a table for Power Apps to parse the information.
• Excel Online (Business) connector supports Excel files of a maximum of 25 MB.
• After the use of this connector, an Excel file may be locked for any updates or deletes for up to 6 minutes.
• Concurrent data writes to a single Excel file by multiple connectors or users is not supported.
• Excel as a data source does not support delegation.

Read more about Excel Online (Business) connector here.

2. SharePoint List

SharePoint list is a collection of data that can be made available to selected team members by granting suitable permissions. It is tabular and can be used to store attachments, files and images as well.

Access to SharePoint is included in the majority of Microsoft 365 subscription plans. This makes SharePoint a viable option as a data source for a ‘no cost implementation’ of a full-fledged solution on Power Apps, without the need to purchase a premium license for Power Apps, although with some limitations on the number of delegable functions and access to only standard free connectors.

Steps to connect :

a) In the Data pane, add SharePoint as a data source.

b) Connect to a SharePoint cloud service or an on-premises data gateway and follow the steps to connect to any SharePoint list within a SharePoint site.

c) Once the selected SharePoint list is visible in the Data pane, it is ready to be consumed within the app UI.

Limitations of SharePoint connector :

• SharePoint lists have limited support for delegation which means filtering/lookup/searching of data is restricted.
• Users must also have access to the underlying lists if the Power App built using SharePoint is to be accessed by them, which means the users will be able to edit/delete the data directly from SharePoint portal if they want to.
• Each SharePoint list is independent and cannot be used as a relational database.
• Data from SharePoint on-premises cannot be accessed directly. To send/receive data to SharePoint on-premises lists while using PowerApps with SharePoint On-premises, a server-side on-premises data gateway configuration is required.

Read more about SharePoint (online) connector here.

3. SQL Server

Microsoft SQL Server is a relational database management system. It allows users to perform CRUD operations on the rows in a table at the source. It supports Active Directory authentication which means the data used by Power Apps does not have to be explicitly shared with the user unlike SharePoint. The data access level can be managed at AD level.

SQL Server connector is a premium connector in Power Apps. Therefore, the end users must have a premium license, such as Power Apps per app or per user plan, in order to connect to the source and use the app.

Steps to connect :

a) In the Data pane, add SQL Server as a data source.

b) Select the appropriate authentication type.

c) Provide SQL server and database name to select and connect to the required table.

d) Once the selected SQL table is visible in the Data pane, it is ready to be consumed within the app UI.

Limitations of SQL Server connector :

• Insert and update to a table does not work if a SQL server-side trigger is defined on the table.
• Power Apps cannot invoke stored procedures.
• The following data types cannot be used as query option predicates — date, datetime, datetime2 and smalldatetime.
• Actions time out if a SQL query takes more than 110 seconds to execute.

Read more about the SQL Server connector here.

4. Microsoft Dataverse

Microsoft Dataverse (formerly Common Data Service) is a managed and secure data store with data stored within a set of tables, each used for a specific business need. It provides both row and column-level security along with sharing and auditing features.

Dataverse comes with a basic set of default tables that address common business scenarios. Custom tables can be created specific to an organisation or a business requirement and populated with data using Power Query which then can be used by developers to build rich applications using Power Apps. It provides better delegation support compared to any other data source

The access to the Dataverse tables depends on the environment in which the Power App is developed. No additional connectors are required since the app is by default connected to Microsoft Dataverse when a new Canvas Power App is created within an environment. Another feature of Dataverse is that it can house an organisation’s data models and is the only data source that supports Model-Driven Power Apps.

The below images illustrate another key benefit of Dataverse which is performance. Unlike in the case of any other online data source, Power Apps connect with Dataverse more directly without going through multiple networking layers. This makes the Dataverse connector the fastest connector to access data in the context of Power Apps.

Other Data Sources

Microsoft Dataverse

Dataverse connector is also a premium connector in Power Apps, thus it requires premium licenses such as Power Apps per app or per user plan, in order to connect this source and use the app.

Steps to connect :

a) Click on Add data to view all the default Microsoft Dataverse tables available in the current environment of Power Apps under the Tables section. Select any Dataverse table to consume it within the app UI.

b) The complete list of default/standard and any custom Dataverse tables can be viewed on the Power Apps home screen under the Tables section within Dataverse as shown in the below image.

c) To create custom tables, the user must have either a System Administrator or System Customizer security role within Microsoft Dataverse. Click on New table to create a custom Dataverse table and follow the steps. This custom table can then be used as a data source by selecting it in the Data pane within an app, similar to other sources.

Limitation of Dataverse connector :

• Performing complex table queries, for example, join multiple tables, union/combine multiple tables, or aggregations can be challenging to execute with Dataverse. SQL Server provides better support in this regard to perform such operations efficiently.

Read more about Microsoft Dataverse connector here.

Summary

Selecting an appropriate data source for any Power Apps use case is crucial prior to Power Apps design and development. Some of the factors that should be considered before making a choice are business requirement, data volume, budget constraints, existing infrastructure & services, performance overhead, and security. Also note that each storage has a different capability or limitation to perform CRUD operations.

Being aware of possible data source bottlenecks and making an informed choice of data sources will help in building a robust and scalable application. It will also make the application development process easier and more efficient without facing hurdles midway.

Additional sources :

• What is Power Apps? - Power Apps
• What is Microsoft Dataverse? - Power Apps
• Common sources of slow performance for a canvas app - Power Apps

Are you getting started with Power Apps? was originally published in Cognizant Servian on Medium, where people are continuing the conversation by highlighting and responding to this story.

Introduction

dbt is a handy tool, it accelerates data warehouse transformations, writes its own documentation, and provides simplicity in its approach to testing. But with these benefits, come some restrictions; I have at times, been frustrated by the limitations and guard rails imposed by dbt. One such limitation is not allowing a cyclical dependency between dbt models.

This article covers a workaround I’ve built to address this restriction. It assumes the reader has some basic dbt knowledge and experience.

The Challenge

A dbt project is a collection of models, macros, tests, snapshots, seeds, and some configs. I’d like to draw your attention to the models today, the meat of dbt.

dbt relates models to each other using a Directed Acyclic Graph (DAG). This defines dependencies between each dbt model.

Here is a screenshot of the dbt docs generated from a sample project, built for this article. It can be seen that model_b is built using data in model_a . It can be said thatmodel_b is dependant on model_a

Imagine a case where we want model_a to be dependent on the current state of model_b . This is not allowed in dbt, unsurprising as this graph would no longer be acyclic.

If possible, this would allow the state of the data maintained in dbt to evolve over time like so:

However, when attempted, dbt throws an error:

RuntimeError: Found a cycle: model.dbt_cyclical_project.model_a --> model.dbt_cyclical_project.model_b

This guide will walk through how such a cyclical dependency, if desired, can be created in dbt.

Solution

The solution involves four parts

1. Creating a macro to clone model_bto a new Snowflake table.
2. Creating a new dbt model to reference the result of the clone
3. Calling the macro in the pre-hook of model_a
4. Refer to the new dbt model in model_a

Here is a schematic of the solution:

But First, Reconsider Your Need To Do This!

This is not a normal thing to do with dbt and your issue could perhaps be addressed with other clever ideas applied to dbt. This guide should be used carefully.

I found this technique useful for a client when we were using dbt to establish a hierarchy of records in model_a to be maintained in model_b, with the latter to be published to an external system. The issue arose when the current state of the hierarchy in model_b was needed to influence how dbt should compute model_a on the next run.

Note: In normal circumstances, dbt is idempotent; running it once creates the same output as running it 1,000 times. But, by using data that has already been built by dbt, that behaviour is likely to change.

Clone Macro

The first and most complex step is to create a macro to clone model_b .

Before doing this, it is important to note that dbt points its output to locations inferred from the selected target. The schema and databases to be used are inferred from the value of the dbt target’s schema and database parameter respectively.

The code developed needs to dynamically adjust at runtime by reading the user profile, without hardcoding the database or schema name. This is done in dbt code by instead using jinja like so:

{{target.database}}.{{target.schema}}

At runtime dbt transforms this expression according to the target database and schema.

Creating a Base Case

In most circumstances,model_bwill have been built by dbt and be available to clone, but on occasion, it will not yet exist, this could occur when dbt is running for the first time. If this happens, dbt will throw an error, this is because:

1) The table can’t be cloned in the macro and

2) The cloned table won’t exist when dbt attempts to read from it.

The workaround for this is to create a base case. Logic is included in the macro to check if model_b exists. If it does not exist, then an empty table will be created with the same structure as model_b, thus throwing no error.

Pseudo Code

1. Create the required database schema if none exists
2. Check if model_bexists.
3. If model_bexists, clone it to a new table
4. If model_bdoes not exist, make a base case for it by creating an empty table.

Example

Here is an example macro (below). This script implements the pseudo code above. It creates a schema if none exists, and either clones model_bor creates an empty table with the same columns names and data types.

Note that lines 1–30 define queries that are executed in later lines. Lines 31 onwards execute the queries defined in lines 1–30.

Note: In my case, using Snowflake, it was necessary to set +transient: false in the dbt_project.yml file, click here for more information.

Note that the execute flag is used, to only check the result of the query during dbt’s seconds pass over the script. For more information on how dbt processes macros, check out the dbt documentation here.

New dbt Model

The above macro creates a table called model_b_clone. This can be referenced directly in our dbt code with {{target.database}}.{{target.schema}}_DBT_CLONES.model_b_clone. But this is hardcoded, will be invisible to dbt and won’t appear in the DAG in our project documentation.

To fix this, let’s create another dbt model, let’s call it model_b_clone:

Note that this table is ephemeral, it won’t be created in the data warehouse, it is instead a component only to be used by dbt for referencing in other tables and documentation.

We can now reference the clone with dbt's standard jinja syntax{{ref('model_b_clone')}}.

Pre-Hook Change

The clone_model_b_macro macro needs to be run immediately before model_a is run so that the data from model_bis available when model_a is run.

To achieve this, the macro is called as a pre_hook for the model_a model, more on dbt hooks can be found here.

Calling the macro as a pre_hook for model_a ensures that before model_a runs, the model_b_clone will be an exact replica of model_b. This can then be used to build model_a .

Reference New Table

After the above steps are complete, the new dbt model can be referenced like any other using Jinja:

{{ref('model_b_clone')}}

The clone will even appear in dbt’s documentation.

Summary

This process can be repeated were necessary. It must be noted however, as it is not a supported feature of dbt, that it could cause unexpected consequences if not properly tested. One such consequence is the sacrifice of idempotence, dbt will likely not output the same result each time it is run because it is referring to its own output.

Here is a representation from dbt’s own docs after the change, the red line is superimposed to show what the dbt macro is doing behind the scenes. The new ephemeral model model_b_cloneis referenced by model_a. As a result, each time model_ais computed it takes input from the current state of model_b.

Creating Cyclical Dependencies in DBT was originally published in Cognizant Servian on Medium, where people are continuing the conversation by highlighting and responding to this story.