The Medallion Data Journey: From Raw Receipts to Business Insights

Introduction: Understanding the Medallion Architecture

The Medallion Architecture is a data design pattern used to organize a “Lakehouse” — a modern data architecture that combines the flexible, low-cost storage of a data lake with the structured management and ACID transactions of a data warehouse. In this framework, data evolves through three distinct stages of increasing quality, ensuring that by the time it reaches a stakeholder, it is reliable and performant.

As an analytics engineer, we never use raw data for direct analysis. Raw source data is inherently “noisy,” riddled with inconsistent schemas, and prone to corruption. Instead, we move it through layers to unlock three primary benefits:

• Traceability: By using features like DuckDB’s filename=true during ingestion, we maintain a clear lineage from every record back to its specific source JSON file on the NAS.
• Data Quality: We apply systematic cleaning and schema enforcement at each stage to ensure that business decisions are based on validated truth.
• Performance: Transforming data into optimized formats like Parquet prevents processing bottlenecks, ensuring your Lakehouse remains responsive even on consumer-grade hardware like a Synology NAS.

Now that we have established the roadmap, let’s look at where the data first lands: the “messy” reality of the Bronze layer.

The Bronze Layer: Capturing the “Messy” Reality

The Bronze layer is the starting point of our journey. This is where raw data is first ingested from the source — in this case, a collection of JSON receipt files dumped into a directory on the Synology NAS.

In this stage, the priority is to maintain a historical reference of the raw files. We perform “Schema Inference,” letting the engine discover the structure of the data rather than defining it manually. This is critical for handling receipts where fields might appear or disappear between different POS (Point of Sale) systems.

Technical Action: We use DuckDB’s read_json_auto function to scan the internal container path /usr/analytics/receipts/. Note that /usr/analytics is the internal path mapped to your physical NAS storage, acting as the foundation of your Lakehouse.

ℹ️ Handling Inconsistent Schemas with union_by_name=True Real-world receipts are messy; one store might use vat_number while another uses tax_id. By setting union_by_name=True, DuckDB automatically maps these varying attributes into a single, wide master schema. It prevents ingestion failures by filling in missing attributes with NULL rather than crashing when a new, unexpected field appears.

While the Bronze layer is a complete record of everything we have collected, it is too unorganized and raw for a business user to interpret.

The Silver Layer: The Polished Truth

The Silver layer is the “Cleansed and Normalized” stage. Here, we transform the raw, multi-format data from Bronze into structured tables that follow strict data contracts. We organize this layer into a “Star Schema” consisting of:

1. Dimensions: The “Who” or “Where” (e.g., a dim_merchants table containing store names and VAT IDs).
2. Facts: The “What” or the actual events (e.g., a fct_fuel_transactions table containing volumes and prices).

Data Transformations: Before vs. After

To ensure reliability, we apply specific transformations to handle the “gotchas” discovered in the source data:

The “Upsert” Logic

Receipt data is often delivered in batches that may contain duplicates or corrections. To maintain a “clean” Silver layer, we use an Upsert pattern:

1. Read: Scan the incoming new batch of data.
2. Combine: Join the new data with the existing persistent Silver data.
3. Deduplicate: Use a window function like ROW_NUMBER() partitioned by a unique key (e.g., transaction_id).
4. Keep Latest: We select only the records where row_number = 1, and — critically — we ORDER BY batch_processed_at DESC to ensure that the most recent version of the receipt is the one that survives.

With clean, deduplicated tables of merchants and transactions established, the data is ready to be aggregated for high-level decision-making.

The Gold Layer: Actionable Business Wisdom

The Gold layer is the “Curated” stage. While Silver provides clean individual records, Gold provides Business Wisdom. This layer aggregates data into specific metrics that answer business questions, such as “What is my total monthly fuel spend?”

The “So What?” of Gold: Instead of looking at thousands of individual receipts, a business owner sees a “Daily Fuel Sales Summary.” This layer joins your Fact and Dimension tables to provide context, such as grouping spend by merchant category or location.

Senior Engineer’s Note: Gold models should only query Silver models using the dbt {{ ref() }} function. They must never reach back to the raw JSON in the Bronze layer. This ensures that every high-level report is built on a foundation of already-cleaned, deduplicated data.

A Gold-Standard table provides:

• [ ] Business-ready metrics: Aggregated totals, averages, and year-over-year summaries.
• [ ] Joined Fact/Dimension data: Contextualized information ready for a BI tool.
• [ ] Optimized Parquet exports: Data is often materialized as Parquet for external reporting.

Behind this smooth flow of data is a powerful technical engine that automates the entire journey.

The Engine Room: How dbt and DuckDB Power the Flow

Two main tools act as the “muscles” and the “architect” of our Lakehouse. DuckDB handles the heavy lifting of processing files, while dbt (Data Build Tool) manages the logic and order of operations.

Quick Reference: The Toolkit

By mastering these tools, you can automate your pipeline so that a new file dropped into a NAS folder automatically becomes a business insight.

Conclusion: Your Data, Transformed

The journey from a messy NAS folder to a clean, persistent data lakehouse is the difference between data chaos and data clarity. By following the Medallion Architecture, you ensure your insights are built on a solid foundation.

This structure allows you to scale from a few fuel receipts to a massive historical archive. By using DuckDB for speed and dbt for structure, you have built a professional-grade analytics pipeline that is traceable, high-quality, and ready for any business challenge.