Case Study: A 250 TB SQL data warehouse for production line archive, part I.

Overview

This technical case study explores the implementation of partitioned views in Microsoft SQL Server to improve scalability, performance, and manageability of large datasets. By distributing data across multiple tables while presenting them as a single logical view, partitioned views provide an efficient approach for handling growing transactional workloads and optimizing query execution. The study examines the architecture, design considerations, implementation process, and performance impact of partitioned views within a SQL Server environment, highlighting practical benefits and common challenges encountered in enterprise database systems.

The goal

A production facility operating SMT (Surface Mount Technology) is evaluating options to keep history of the items produced for quality assurance.

An SMT production line is an automated manufacturing process used for assembling electronic components directly onto printed circuit boards (PCBs). The line typically consists of several integrated machines working together to ensure high-speed, precise, and reliable PCB assembly.

The process begins with a solder paste printer, which applies solder paste onto specific pads of the PCB using a stencil. The board then moves to a pick-and-place machine, where electronic components such as resistors, capacitors, and integrated circuits are accurately positioned onto the soldered areas at high speed.

After component placement, the PCB passes through a reflow oven, where controlled heating melts the solder paste and permanently bonds the components to the board. Once soldering is completed, automated optical inspection (AOI) systems and sometimes X-ray inspection machines verify component placement, solder quality, and detect potential defects. In modern electronics manufacturing, the ability to track and trace every PCB produced on an SMT production line is essential for ensuring product quality, regulatory compliance, and efficient production management. Each PCB passes through multiple stages of assembly, inspection, and testing, making it critical to maintain a complete manufacturing history for every individual board.

Each PCB assembled on the SMT production line generates a comprehensive digital footprint that represents the complete manufacturing history of the board. This single footprint consists of approximately 18,000 individual parameters collected automatically from machines, inspection systems, sensors, and production software throughout the assembly process.

The captured data includes detailed information from every stage of production, such as solder paste printing measurements, stencil alignment values, pick-and-place coordinates, component feeder information, placement accuracy, machine cycle times, nozzle usage, reflow oven temperature profiles, conveyor speeds, humidity and environmental conditions, AOI inspection results, test measurements, operator interactions, alarms, and rework records. Together, these parameters form a highly granular digital representation of how the PCB was manufactured. This level of traceability enables manufacturers to reconstruct the exact production conditions for any individual board at any point in time. The resulting dataset supports advanced quality analysis, defect investigation, process optimization, predictive maintenance, and compliance reporting.

Due to the volume and complexity of the collected information, managing this digital footprint requires scalable database architectures and high-performance data processing solutions. In large-scale SMT environments producing thousands of boards daily, the traceability system rapidly grows into a massive dataset that must remain searchable, reliable, and accessible in near real time.

The Challenge

The SMT production line is projected at 150,000 items per week. The same number of digital footprints must be stored in a system that would allow the data to grow seamlessly and yet still remain accessible and manageable at scale. The single digital footprint is a 18MB XML file with 162,000 lines of records representing different measurements.

The challenge is, that approximately 24 billion rows and roughly 950 GB of data must be imported in the data warehouse over a single week. To keep 5 years of digital footprint history requires to store 250 TB of data or 6 300 billion rows. The data is accessed by quality assurance staff at random. A random batch, typically a timeframe is selected for inspection or due diligence. Furthermore, the dataset requires to be protected against hardware malfunction, data loss risks from human error or ransomware or other emergencies. Because of the character of the system being an Archive for ex-post inspection, the availability of the solution required no more than a next business day recovery. For SMT to remain fully operable without losing any sample, a sufficient staging area has to be established, to keep data before it enters the warehouse.

The Solution

SQL Server partitioned views were chosen as a design principle to cope with the task. The rows are extracted from XML file and stored in a particular table containing a single Month of samples. The exact table where data is inserted, is decided at import time from the production date of the sample. The production date acts therefore as the partitioning key. Extracting the rows reduces the number of metadata contained in XML, and pure “key and value” information is stored in a table.

The process of decoding XML and distributing across the tables happens at import time. It spares compute capacity needed later during data consumption, when the samples are read. When Quality engineers require the data, the data is already organized into relational structure and it is not required to analyze thousands of XML files. I will look in to the detailed technical implementation in part II.