Forem: Lewis Hemens

Testing data quality with SQL assertions

Lewis Hemens — Wed, 26 Jun 2019 10:51:09 +0000

Ensuring that data consumers can use their data to reliably answer questions is of paramount importance to any data analytics team. Having a mechanism to enforce high data quality across datasets is therefore a key requirement for these teams.

Often, input data sources are missing rows, contain duplicates, or include just plain invalid data. Over time, changes to business definitions or the underlying software which produces input data can cause drift in the meaning of columns - or even the overall structure of tables. Addressing these issues is critical to creating a successful data team and generating valuable, correct insights.

In this article we explain the concept of a SQL data assertion, look at some common data quality problems, how to detect them, and - most importantly - how to fix them in a way that persists for all data consumers.

The SQL snippets in this post apply to Google BigQuery but can be ported easily enough to Redshift, Postgres or Snowflake data warehouses.

What is a data assertion?

A data assertion is a query that looks for problems in a dataset. If the query returns any rows then the assertion fails.

Data assertions are defined this way because it’s much easier to look for problems rather than the absence of them. It also means that assertion queries can themselves be used to quickly inspect the data causing the assertion to fail - making it easy to diagnose and fix the problem.

Checking field values

Let’s take a look at a simple example.

Assume that there is a database.customers table containing information about customers in the database.
Some checks that we might want to verify on the table’s contents include:

The field email_address is always set
The field customer_type is one of “business” or “individual”

The following simple query will return any rows violating these rules:

SELECT customer_id
FROM database.customers
WHERE  email_address IS NULL
OR NOT customer_type IN (“business”, “individual”)

Checking for unique fields

We may also want to run checks across more than one row. For example, we might want to verify that the customer_id field is unique. A query like the following will return any duplicate customer_id values:

SELECT
    customer_id,
    SUM(1) AS count
FROM database.customers
GROUP BY 1
HAVING count > 1

Combinining multiple assertions into a single query

We can combine all of the above into a single query to quickly find any customer_id value violating one of our rules using UNION ALL:

SELECT customer_id, “missing_email” AS reason
FROM database.customers
WHERE email_address IS NULL

UNION ALL

SELECT customer_id, “invalid_customer_type” AS reason
WHERE not customer_type in (“business”, “individual”)
FROM database.customers

UNION ALL

SELECT customer_id, “duplicate_id” AS reason
FROM (
    SELECT customer_id, SUM(1) AS count
    FROM database.customers
    GROUP BY 1
)
WHERE count > 1

We now have one query we can run to detect any problems in our table, and we can easily add another unioned SELECT statement if we want to add new conditions in the future.

Creating clean datasets

Now that we’ve detected the issues in our data, we need to clean them up. Ultimately choosing how to handle data quality issues depends on your business use case.

In this example we will:

Remove any rows that are missing the email_address field
Set a default customer type if it’s invalid
Remove rows with duplicate customer_id fields, retaining one row per customer_id value (we don’t care which one)

Rather than editing the dataset directly, we can create a new clean copy of the dataset - this gives us freedom to change or add rules in the future and avoids deleting any data.

The following SQL query defines a view of our database.customers table in which invalid rows are removed, default customer types are set, and duplicate rows for the same customer_id are removed:

SELECT
    customer_id,
    ANY_VALUE(email_address) AS email_address,
    ANY_VALUE(
        CASE customer_type
        WHEN “individual” THEN “individual”
        WHEN “business”   THEN “business”
        ELSE “unknown”
    END) AS customer_type
FROM database.customers
WHERE NOT email_address IS NULL
GROUP BY 1

This query can be used to create either a view or a table in our cloud data warehouse, perhaps called database_clean.customers, which can be consumed in dashboards or by analysts who want to query the data.

Now we've fixed the problem, we can check that the above query has correctly fixed the problems by re-running the original assertion on the new dataset.

Continuous data quality testing

Assertions should be run as part of any data pipelines to make sure breaking changes are picked up the moment they happen.

If an assertion returns any rows, future steps in a pipeline should either fail, or a notification delivered to the data owner.

Dataform has built in support for data assertions, and provides a way to run them as part of a larger SQL pipeline.

These can be run at any frequency, and if an assertion fails an email will be sent to notify you of the problem. Dataform also provides a way to easily create new datasets in your warehouse, making managing the process of cleaning and testing your data extremely straightforward.

For more information on how to start writing data assertions with Dataform, check out the assertions documentation guide for Dataform’s open-source framework, or create an account for free and start using Dataform's fully managed Web platform.

Building a TypeScript monorepo with Bazel

Lewis Hemens — Wed, 26 Jun 2019 08:24:16 +0000

At Dataform we maintain a handful of NPM packages and a documentation site in one single monorepo and we do it all with Bazel.

I'm going to quickly talk through monorepos and Bazel, then deep dive into interesting parts our monorepo with some real code examples, covering:

Bazel TypeScript basics
Managing multiple packages in a single monorepo
Building and publishishing NPM packages with Bazel

Our project is open-source, so you can view all the code, or clone and build it with Bazel at: https://github.com/dataform-co/dataform

All your code in one repo

a.k.a the monorepo, so hot right now.

I used to work at Google so I may be biased, but there is a huge amount of value to having all your code in one single repository. You end up spending a lot less time doing repetitive tasks, updating git submodules, pushing new packages, running bash scripts - the kind of things that distract you from the important task at hand.

With a single code base, it becomes very easy to re-use code and libraries between different projects, but you need a good build system to make it work.

Bazel

_{Fast, Correct} - Choose two_ - https://bazel.build

Ever tried to clone and compile an open-source repo just to spend 30 minutes wrangling with missing or broken system dependencies, mismatched versions and a myriad of bash scripts that just don't work? Yeah...

Bazel is a build system. It's highly opinionated and tricky to master, but leaves you with an extremely fast, hermetic, and reproducible build process once adopted.

Bazel is still fairly young, but the ecosystem is evolving extremely quickly. It's also built on solid foundations - being used internally at Google Bazel is called Blaze and helps to power Google's one colossal monorepo (literally all the code).

The problem

We maintain several NPM packages with inter dependencies. Our goals here are:

To manage all packages in a single repository
For our builds to be fast and reliable
To test changes to multiple packages at the same time
An easy way to manage versions across all these packages
To write as few bash scripts as possible

A basic Bazel TS rule

Here's an example of how you build TypeScript library with Bazel. Our simplest package in the repo is @dataform/core and we'll use this as an example for most of the post.

The folder looks like a normal TS package, except for the BUILD file. Here's the part of that file that actually compiles the TypeScript:

ts_library(
    name = "core",
    srcs = glob(["**/*.ts"]),
    module_name = "@dataform/core",
    deps = [
        "//protos",
        "@npm//@types/moo",
        "@npm//@types/node",
        "@npm//moo",
        "@npm//protobufjs",
    ],
)

This rule in the BUILD file tells Bazel:

The library called core
It should include all .ts files within this folder
It's (node) module name is @dataform/core
It has one internal dependency //protos
It has a few NPM dependencies, just like a package.json file

To build this TS library you can run:

bazel build core

Note: ts_library rules and other node related rulesets are not core to the bazel runtime but are imported from elsewhere. You can read more about them here: https://github.com/bazelbuild/rules_nodejs.

Dependencies between packages

If you've worked with a NPM based monorepo before, you've probably used a tool like Lerna.

Lerna makes it easy to link packages locally so you can test changes across multiple NPM packages. It also makes it easier to manage versioning between them. We want that.

Bazel builds and links packages without going anywhere near an actual NPM package. In our @dataform/core example above, the ts_library rule depends on //protos which is just another ts_library rule.

ts_library(
    name = "protos",
    srcs = glob(["index.ts"]),
    module_name = "@dataform/protos",
    deps = [
        ...
    ],
)

The ts_libary rule does some magic to make sure that built packages are available under the module_name attribute provided, which matches the NPM package they will be published at.

So in our @dataform/core package, we can import from the //protos package whose module_name is @dataform/protos like this:

import { dataform } from "@dataform/protos";

When we publish to NPM, these imports will resolve correctly too as the module names match the package names.

Managing multiple packages

Lerna also helps you manage multiple package.json files, updating all versions together and publishing them. We would like a way to do the same thing in Bazel.

To generate package.json files we built small tool in our monorepo that Bazel uses to generate package.json files using layers of JSON templates and string substitutions.

For the @dataform/core package, we have a core.package.json file looks like this:

{
  "name": "@dataform/core",
  "description": "Dataform core API.",
  "main": "index.js",
  "types": "index.d.ts",
  "dependencies": {
    "@dataform/protos": "$DF_VERSION",
    "moo": "^0.5.0",
    "protobufjs": "^6.8.8"
  }
}

Any extra info, licenses, homepage etc - is inherited from the base common.package.json so we don't have to keep several files in sync.

The special string $DF_VERSION gets replaced with a global constant defined as part of the Bazel build system in version.bzl.

Building NPM packages

To evaluate these JSON templates, we wrote a Bazel macro to invoke our tool above and we invoke it in the @dataform/core BUILD file like so:

load("//tools/npm:package.bzl", "dataform_npm_package")

dataform_npm_package(
    name = "package",
    package_layers = [
        "//:common.package.json",
        "core.package.json",
    ],
    deps = [":core"],
)

This custom bazel macro both generates a final package.json from two templates listed, and creates an output dist folder with the compiled TypeScript that's ready to be published.

To see the output of this and the final generated package.json, you can run the following command:

bazel build core:package

Bazel tells us it's put the package in the folder bazel-bin/core/package with .js and .d.ts files as well as the final package.json (this is kind of like a dist folder) that is ready to publish!

Note: We haven't fully automated this step yet, and it's still necessary to make sure that the dependencies and package name in the BUILD file match those in the package.json template, but it's certainly feasible to automate this entirely.

Publishing NPM packages

Publishing is easy at this point, and the rules_nodejs libraries have this built in. To publish a package we can run:

bazel run //core:package.publish

We still have a bash script to do this for all packages but all it does is invoke Bazel commands:

#!/bin/bash
set -e

# Test all the things.

bazel test //...

# Publish all the things.

bazel run //api:package.publish
bazel run //core:package.publish
bazel run //cli:package.publish
bazel run //crossdb:package.publish
bazel run //protos:package.publish

Conclusion

This is by no means a complete solution yet, and in reality will require you to learn quite a lot about Bazel to get it working on your own project. For anyone trying, hopefully our repo can serve as a good reference!

Despite that, I hope that this demonstrates that using Bazel is a great solution to managing complex projects and many Node / Typescript packages inside a single repository.
With a a few small extra Bazel rules you can build a TypeScript monorepo that is lighting fast and will scale as your project does.

If you found this post interesting and would be interested in a Bazel TypeScript starter pack repo, reach out and we'll see what we can do!

Typed CSS modules with Bazel

Lewis Hemens — Sun, 23 Jun 2019 13:58:09 +0000

If you are building TypeScript with Bazel and using CSS, you probably want it to be typed. Tools such as Webpack can generate .d.ts files for you, however Webpack plugins do this by adding the .d.ts files to your source directory.

This is a no go in Bazel, where you generally can't / shouldn't mutate the source during a build step.

This can be accomplished fairly easily with a custom build rule.

Note: the following has been tested with Bazel 0.26.1 and rules_nodejs 0.31.1.

All the following code snippets are from an open-source project and you can see them working in their entirety here: https://github.com/dataform-co/dataform

The problem

Assume we have a CSS file called styles.css containing the following:

.someClass {
  color: #fff;
}

.someClass .someOtherClass {
  display: flex;
}

And we want to generate a styles.css.d.ts that looks like this:

export const someClass: string;
export const someOtherClass: string;

So that we can provide this as an input to any consuming rules such as ts_library to make sure we aren't using any class names that don't exist and to keep the TS compiler happy.

Using `typed-css-modules`

We can do this using the typed-css-modules package.

First we want to set up a nodejs_binary that we can use to generate these typings.

Firstly add this to your root package.json assuming you manage your projects dependencies with rules_nodejs and npm_install/yarn_install rules, then add the following to a BUILD file somewhere in your repo (we keep this in the root BUILD file):

nodejs_binary(
    name = "tcm",
    data = [
        "@npm//typed-css-modules",
    ],
    entry_point = "@npm//node_modules/typed-css-modules:lib/cli.js",
)

This allows us to run the TCM CLI from within Bazel rules.

Writing the Bazel rule

Now we need to define a Bazel rule that can actually run the TCM CLI and generate typings files. Add a new file called css_typings.bzl somewhere in your repository with the following:

def _impl(ctx):
    outs = []
    for f in ctx.files.srcs:
        # Only create outputs for css files.
        if f.path[-4:] != ".css":
            fail("Only .css file inputs are allowed.")

        out = ctx.actions.declare_file(f.basename.replace(".css", ".css.d.ts"), sibling = f)
        outs.append(out)
        ctx.actions.run(
            inputs = [f] + [ctx.executable._tool],
            outputs = [out],
            executable = ctx.executable._tool,
            arguments = ["-o", out.root.path, "-p", f.path, "--silent"],
            progress_message = "Generating CSS type definitions for %s" % f.path,
        )

    # Return a structure that is compatible with the deps[] of a ts_library.
    return struct(
        files = depset(outs),
        typescript = struct(
            declarations = depset(outs),
            transitive_declarations = depset(outs),
            type_blacklisted_declarations = depset(),
            es5_sources = depset(),
            es6_sources = depset(),
            transitive_es5_sources = depset(),
            transitive_es6_sources = depset(),
        ),
    )

css_typings = rule(
    implementation = _impl,
    attrs = {
        "srcs": attr.label_list(doc = "css files", allow_files = True),
        "_tool": attr.label(
            executable = True,
            cfg = "host",
            allow_files = True,
            default = Label("//:tcm"),
        ),
    },
)

This is a fairly simple Bazel build rule that generates CSS typings using the TCM CLI we installed previously.

Iterates through each input CSS file
Runs the TCM tool against the CSS file to generate typings
Returns something that is compatible as an input to ts_library rules and can be added as a dependency

You will need to replace the Label("//:tcm") line with the name of the nodejs_binary rule you created in the previous step.

Putting it all together

Here is a simple example BUILD file that uses this rule, assuming you put the css_typings.bzl file in a tools directory:

filegroup(
    name = "css",
    srcs = glob(["**/*.css"]),
)

load("//tools:css_typings.bzl", "css_typings")

css_typings(
    name = "css_typings",
    srcs = [":css"],
)

load("@npm_bazel_typescript//:index.bzl", "ts_library")

ts_library(
    name = "components",
    srcs = glob([
        "**/*.ts",
        "**/*.tsx",
    ]),
    data = [
        ":css",
    ],
    deps = [
        ":css_typings",
        ...
    ],
)

You probably still need to actually bundle the CSS files using the data attribute above, assuming that this library is going to bundled for web by some downstream consumer.

And that should be all you need to generate CSS typings with Bazel Typescript projects!

If this was useful to you, please let me know and I'll put it into a repository so it can be imported and used without the copy-paste.

Forem: Lewis Hemens

Testing data quality with SQL assertions

What is a data assertion?

Checking field values

Checking for unique fields

Combinining multiple assertions into a single query

Creating clean datasets

Continuous data quality testing

Building a TypeScript monorepo with Bazel

All your code in one repo

Bazel

The problem

A basic Bazel TS rule

Dependencies between packages

Managing multiple packages

Building NPM packages

Publishing NPM packages

Conclusion

Typed CSS modules with Bazel

The problem

Using typed-css-modules

Writing the Bazel rule

Putting it all together

Using `typed-css-modules`