Flexibility Upstream, Consistency Downstream: How We Made Changing Data Platforms a Localized…

Written by Lorena SalamancaNov 18, 2026 08:019 min read
Flexibility Upstream, Consistency Downstream: How We Made Changing Data Platforms a Localized…

Flexibility Upstream, Consistency Downstream: How We Made Changing Data Platforms a Localized Change

How Picnic uses Snowflake and dbt to decouple master data platforms from backends, keeping data contracts consistent as upstream systems change.

(If you’re short on time, scroll further down to see a 25 sec gif with a quick visual explanation)

Our Product Information Management system (PIM) is where Picnic maintains its master data: the definitions and attributes of products, SKUs, suppliers, recipes and more. That information is used across the business and different areas need different fields and representations of it. Our shopping app, for instance, needs product names, descriptions and images to present the assortment; our supply chain systems need weights, dimensions and packaging details to handle the physical products. Each system needs its own selection of fields, sometimes combined, structured differently or used to derive additional values.

We used to have a lightweight middleware to distribute this information across Picnic. It centralized the integration with Salesforce, our PIM platform, so backend teams could consume product data without each maintaining a direct connection to Salesforce. The output still reflected the original representation, and the layer offered limited support for transforming and validating it. That pushed transformation and derivative generation logic either upstream, into Salesforce, or downstream, into individual backends. Some backends implemented overlapping or conflicting rules, while Salesforce accumulated transformations specific to individual teams.

These challenges became more consequential with Picnic’s growth beyond a supermarket and towards an ‘everyday store’. A higher data volume and more transformations mean more complexity on either end.

We also started moving parts of our PIM capabilities to other platforms. Each source brought its own identifiers and conventions. Our challenge was to introduce those platforms without requiring changes to the data contracts our backends already used.

We built ‘Master Data Cache’ to ingest and transform master data before serving it to Picnic. It is a data pipeline powered by Snowflake and dbt that gives consumer teams control over the models their services need to use.

Built on tools we already know

Snowflake and dbt gave us the foundations for a more expressive transformation layer. We already use Snowflake for Picnic’s Lakeless Data Warehouse and have written about dbt in Leveraging contextual data in real-time analytics with Apache Iceberg. In Snowflake, we can combine information from different systems, join related entities, and calculate derived values in SQL, a language our analysts and developers know well.

dbt organizes those transformations into a model graph. Models can build on one another, with their dependencies, documentation, and SQL maintained together in version control. That gives us lineage from a consumer’s output back to the source data, as well as a common place to review and evolve the logic.

Two layers of models, with different owners

Master Data Cache uses dlt, a Python ingestion library, to load source data into Snowflake. The ingestion strategy depends on the source’s APIs and the freshness we need. For Salesforce, we use bulk extraction to ingest changes on a 15-minute schedule. The data is transformed and checked before publication, independently of backend requests.

dbt builds two main layers of models on top of that data. Canonical models describe shared business concepts independently of the system that supplies them. They map source names, types, identifiers, and relationships into a consistent representation. Our Master Data team owns this layer and the ingestion that feeds it.

For example, Salesforce links a SKU to an article using an internal record ID. The canonical SKU model resolves that reference to Picnic’s article identifier, exposed as article_id, so consumer models can join the two without resolving the Salesforce relationship themselves.

Consumer models build on those concepts to implement a team’s requirements. They can combine data from any canonical model, derive new values, and apply the validations that the consuming service needs. For example, a Store model maps a recipe’s status to visibility types used by the app. Active recipes can be included in search and listings, while archived recipes retain access to their detail page. The Store team owns that interpretation; the canonical recipe model supplies the underlying status.

That ownership also extends to validation. dbt’s contracts enforce the model’s columns and types. Data tests check the values and relationships within that structure, e.g. whether identifiers are unique, or required values are present. Unit tests exercise transformation logic against known inputs and expected outputs during development. Contracts and data tests check the built data before publication, making the consumer’s requirements explicit alongside its SQL.

We keep canonical models focused on shared concepts and common calculations. Logic needed by one consumer stays in that team’s model; logic shared by several consumers can be maintained once in canonical models. This lets teams evolve their own outputs without bringing platform-specific details back into their backends.

When a new vendor, supplier, or platform provides equivalent data in different terms or formats, we adapt the ingestion and canonical mappings. Consumer models can continue to use the same concepts and expose the same format. Preserving that contract includes preserving what its values mean, as well as their names and types.

From models to backends

For distribution, we use Picnic’s Attribute Data Store (ADS). ADS allows a standardized, type-safe definition and storage of attributes, essentially a key-value store on steroids; for Master Data Cache, those values are the JSON records produced by consumer models. A successful model refresh triggers a RabbitMQ event that allows ADS to load the published output. Backend services then sync the attributes they subscribe to into their own storage.

ADS provides the mechanisms needed to maintain those local copies. It identifies changed records, and its consumer-side syncing library tracks progress with persistent cursors, processes updates in batches, and retries failed batches. Backend requests read from those local copies, without calling Salesforce, Snowflake or ADS. Updates arrive asynchronously, so freshness depends on the ingestion and synchronization cycle.

Changing the representation, preserving the behaviour

Migrating to this architecture also means relocating logic. Transformations and validations previously implemented in source systems or Picnic backends move into dbt models in Master Data Cache. A record previously assembled in a backend becomes a consumer model; a multi-select string in the source may become a typed array. The representation changes, but the business behaviour we intend to retain still needs to match.

We introduced more than 20 AI skills to give coding assistants reusable workflows for investigating and translating that logic. They inspect Salesforce field definitions and automations, trace calculations to their inputs, and audit which fields are used by existing consumers. They help identify where logic belongs, propose the necessary SQL implementation, and record mappings from the old fields to the new models. These workflows combine code analysis with scripted audits and comparisons.

We compare the outputs of the old and new implementations using the field mappings, accounting for intentional differences in representation. We investigate differences before accepting the translation, checking the source data and any intended changes in behaviour.

Keeping platform changes upstream

Our previous direct-to-Salesforce middleware served us well for ten years. With 30% of teams now migrated to Master Data Cache and more than 20,000 lines of code deleted, we’re already seeing the benefits of moving scattered logic into shared and consumer-owned models.

Master data is our use case, but this architecture applies wherever systems need consistent business concepts from sources that express them differently. By keeping source-specific details in ingestion and canonical models, and consumer requirements in models owned by the teams that use them, we give both sides room to evolve independently.


Flexibility Upstream, Consistency Downstream: How We Made Changing Data Platforms a Localized… was originally published in Picnic Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

picnic blog job banner image

Want to join Lorena Salamanca in finding solutions to interesting problems?