JLP data pipelines use building blocks

Hello, I am a Data Engineer in the John Lewis Partnership and this post is a shout out to our tooling squad.

The data engineers at JLP are split into several squads that are each responsible for their own data pipelines serving a given business unit or function.

We also have a tooling squad called Paved Road that can focus on high quality implementations of building blocks. They reduce the need for each stream-aligned squad to implement the same functionality.

This article is a summary of the functionality that the Paved Road squad has implemented and gives you an insight into the way we structure our pipelines at JLP and perhaps serves as a point of reference for data engineering in retail.

Building blocks are simple to use

At JLP our data platform is implemented on Snowflake, GCP with the majority of pipelines implemented in DBT and orchestrated by Airflow (Cloud Composer). Many of our raw layer data pipelines use Snowpipe, and the building blocks required to build Snowpipes have been paved. That means with a few dozen lines [1] of YAML you would have an event-driven ingestion pipeline configured with sensible defaults. The following objects would be instantiated, along with the bare minimum permissions for Snowflake to communicate with GCP and for developers to interact with the provisioned objects.

• On Snowflake:
• Table
• File Format
• Storage Integration
• Stage
• On GCP:
• Bucket
• Pub/Sub topic
• Pub/Sub subscription

All files dropped in the GCP landing bucket that match the file pattern you specify in YAML would then get loaded to your landing table as they arrive. The above objects can also be specified in your YAML file independently, so if your ingestion needs to handle different file formats arriving in the same bucket, or if you need to invoke a cloud function to parse and load an exotic file, you can pick and choose the paved components that you need.

The diversity of source system interfaces does not mean that the paved road squad builds tools for every API and protocol. Instead, they focus on the components that can be widely leveraged by data engineers in the partnership. This means for example that solutions for fetching data over SFTP and malware scanning are paved, but fetching data from the Salesforce API must be implemented by the one or two squads that need it.

After data has landed in the raw layer, data engineers prepare the data for a wide variety of use cases using dbt Cloud and Airflow. Here, the paving is more focussed on simplifying the use of dev and prod environments. To specify dbt Cloud jobs, a data engineer only specifies the name, execution steps, and description. The job is instantiated in the dev or prod dbt Cloud environment by a dev or prod CI pipeline respectively. Similarly DAGs are deployed to dev or prod Airflow depending on the type of the CI pipeline, and in addition, data engineers can also specify versions of Cloud Composer instances in different environments to manage version upgrades.

The paved road pipeline provides guard rails

To use this tooling, engineers first need to create a new data product using the paved road ‘cookie cutter’ interface. This interface enforces naming standards and minimal metadata population for the data products, and provisions some cloud resources, such as:

• A schema, service accounts, and various roles in Snowflake
• A project in dbt cloud with a few default environments
• A dev and prod pair of projects in GCP, including service accounts and integrations with other data platform projects
• A git repository with a template Data Product Definition YAML file (DPD) and a CI pipeline definition that references the paved road pipeline

The paved road pipeline is a versioned set of a few modular CI pipeline definition files. It is the use of this pipeline that ensures that JLP data products are built and deployed in an efficient, consistent, and accredited manner. The pipeline contains a few default jobs that always run, such as validation, reading external state and terraform plan+apply, and other jobs that can be conditionally enabled, such as dbt tests or deployment to production. Engineers can also add other jobs such as linting as needed.

The heavy lifting in the paved road pipeline is done by another default job called the resolver. This step interprets the DPD file into a JSON specification of all the required resources for the target deployment environments and then uses JINJA2 templates to generate the terraform files needed to provision the required resources. The resolver is also responsible for validating the DPD, which includes enforcing naming conventions, asserting that all required inputs are provided for a given resource in the DPD, and checking for table naming conflicts on Snowflake.

The resolver also allows engineers to use templates in Airflow DAGs, to specify for example that failure alerts go to the dev or prod channel, or to allow references in the DAG to a table that is defined in the DPD.

This is not a comprehensive summary

Beyond the foundational components described above, the paved road squad has implemented various other pieces of functionality. For example, they provide a handful of common Airflow task groups to standardize things like alerting, encryption and decryption, data export, and waiting for a dbt job or Snowflake query to complete. There are scripts for converting data modelling artefacts into SQL, a solution for applying classification tags and masking sensitive data, and a system for helping engineers conform data across multiple domains.

The squad also has a healthy backlog of feature requests that they deliver at an astonishing pace. I suspect that in the near future the building blocks will look quite different, and as a result the data engineering squads will be delivering pipelines faster than ever.

FOOTNOTE

[1] Primarily taken up by column names, data types, comments, and nullability.