Technical Architecture Deployment Specification: dbt & DuckDB Data Pipeline on Synology NAS

Executive Summary and Strategic Context

Deploying an embedded Data Lakehouse directly on a Synology NAS represents a high-performance, cost-effective strategy for localized data engineering. By utilizing DuckDB as the analytical engine and dbt (Data Build Tool) for orchestration, this architecture leverages the “in-process” nature of DuckDB to minimize system overhead. Unlike traditional client-server databases like PostgreSQL, which require persistent background daemons that can starve the Synology NAS CPU and RAM of resources needed for other NAS services, DuckDB initializes only during query execution.

For hardware like the entry-level model, maximizing ROI requires reducing data latency and avoiding unnecessary cloud egress costs. This deployment transforms the NAS from simple storage into a sophisticated compute node capable of complex JSON shredding and multi-layered data modeling. The following specification establishes the standards for bridging physical storage with a containerized transformation pipeline, ensuring data durability and system efficiency.

Infrastructure Bridging: Physical NAS to Docker Mapping

In a containerized environment, data persistence is achieved by mounting physical NAS volumes to internal container paths. Without these mappings, the internal container filesystem remains ephemeral, leading to total data loss upon container restarts or image updates.

The following table defines the required mapping between the Physical NAS Path and the Container Internal Path:

Technical Note on Permissions: To prevent “IO Error: Permission Denied” during pipeline execution, the Docker container’s PUID/PGID must have explicit read/write ownership of the /volume1/docker/dbt-duckdb/ directories. Failure to align NAS user permissions with the container runtime is the primary cause of dbt debug connectivity failures.

Persistent Database & dbt Configuration Standards

The governance of the DuckDB lifecycle is managed through the profiles.yml and dbt_project.yml files. Maintaining a static profiles.yml is essential for enabling dbt’s Partial Parsing feature, which drastically reduces initialization time — a critical optimization for the NAS’s Intel Celeron processor.

Database Persistence: profiles.yml

The following configuration ensures that DuckDB writes to a persistent file on the NAS volume rather than an in-memory instance:

receipt_lakehouse:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: /usr/analytics/receipts_database.duckdb
      extensions:
        - httpfs
        - parquet
        - json

Clean Schema Naming Macro

By default, dbt prefixes custom schemas with the target schema name (e.g., bronze). To enforce a clean Medallion structure, the following macro must be implemented in macros/generate_schema_name.sql:

{% macro generate_schema_name(custom_schema_name, node) -%}
{%- if custom_schema_name is none -%}
{{ target.schema }}
{%- else -%}
{{ custom_schema_name | trim }}
{%- endif -%}
{%- endmacro %}

Medallion Architecture: The Bronze (Ingestion) Layer

The Bronze Layer acts as the landing zone for raw, semi-structured JSON fuel receipts. The strategic objective here is the creation of a Wide Master Schema.

The ingestion utilizes DuckDB’s read_json_auto function. The parameter union_by_name=True is mandatory; it enables DuckDB to perform an implicit outer join across varying JSON structures. If Merchant A provides tax_amount and Merchant B provides VAT, the Bronze layer preserves both columns, filling missing values with NULL.

Ingestion Logic Example:

SELECT FROM read_json_auto(‘/usr/analytics/receipts/.json’, union_by_name=True)

This layer ensures no data is lost during ingestion, allowing the Silver layer to later COALESCE synonymous fields into a unified standard.

Medallion Architecture: The Silver (Standardization) Layer

The Silver Layer enforces data contracts and normalizes the wide Bronze schema into structured Dimensions (dim_merchants) and Facts (fct_fuel_transactions).

Transformation Standards

• Case Normalization: Because initcap() behavior can vary by DuckDB version, use regexp_replace as the universal solution for word-boundary capitalization: regexp_replace(fuel_product_name, ‘\b([a-z])’, ‘\U\1’, ‘g’)
• Type Safety: 22-digit transaction IDs exceed INT32 and BIGINT limits. These must be cast to VARCHAR to prevent conversion overflow errors.
• Data Integrity: All identifiers must be stripped of whitespace using TRIM().

Governance and Performance

Architects must implement Model Contracts in schema.yml to prevent schema drift:

models:
  - name: fct_fuel_transactions
    config:
      contract: {enforced: true}
      materialized: incremental
      unique_key: transaction_id

Using materialized=’incremental’ is vital for NAS efficiency, as it ensures only new JSON files are processed, significantly reducing CPU cycles compared to a full rebuild.

Medallion Architecture: The Gold (Analytics) Layer & External Persistence

The Gold Layer serves business-ready metrics. To ensure interoperability with external BI tools or Pandas, this layer utilizes External Parquet Materialization.

Configuration for External Export

Setting materialized=’external’ instructs dbt to export results directly to the NAS as standalone files. Use compression=’snappy’ to balance high-speed I/O with storage ROI:

{{ config(
    materialized='external',
    location='/usr/analytics/lakehouse/gold/fct_refill_history.parquet',
    options={'compression': 'snappy'}
) }}

The Upsert Pattern and Cold-Start Handling

To avoid duplicates in external files, the “Self-Referencing” Upsert pattern reads existing Parquet data, unions it with new data, and deduplicates via ROW_NUMBER().

Critical Risk: The read_parquet function will fail if the file does not exist. During the First Run, the engineer must temporarily comment out the UNION with the existing Parquet file or provide a manual seed file to establish the initial target.

Operational Deployment, Lineage, and Health Checks

The following runbook defines the essential CLI operations for maintaining the pipeline.

Deployment Runbook

1. dbt debug: Execute first to verify connectivity and NAS volume write permissions.
2. dbt run: Standard execution. Use — full-refresh only when schema logic changes.
3. dbt docs generate && dbt docs serve — host 0.0.0.0: Generates and hosts the documentation portal. The — host 0.0.0.0 flag is required to expose the portal outside the Docker container to the local network.

Maintenance and Lineage

• Lineage Graph: The dbt Lineage Graph allows the architect to trace data from the raw JSON source through the COALESCE logic in Silver to the final Parquet export in Gold.
• Partial Parsing Optimization: Avoid frequent use of dbt clean. On the NAS, a clean start wipes the manifest, forcing a slow, full project re-parse. Only use dbt clean if metadata corruption is suspected.

This architecture provides a scalable, ACID-compliant data pipeline that transforms local NAS storage into a high-performance analytical asset while preserving the host’s resource availability.