ETL Pipelines for Parsing Data: Storage, Normalization, and Data Quality

Collecting data from web sources is only the first step. Raw parsing output is rarely suitable for business analytics, machine learning, or CRM integration. A reliable ETL pipeline is required: extraction (already done by the parser), transformation, and loading into the target storage. A well-built pipeline based on PostgreSQL, asynchronous queues, and data quality assurance mechanisms turns a chaotic array into a clean, structured, and ready-to-use asset.

In this article, we consider the engineering practices used by our team when building parsing and data processing systems: from organizing storage layers to deduplication and quality monitoring.

Storing Parsing Data: Raw Layer and Analytical Layer

Data coming from scrapers must be saved incrementally to avoid losing any record and to be able to restart processing without re-collecting. The classic three-tier model is adapted to parsing tasks as follows.

Staging Area

This is where 'raw' information lands in a format as close to the source as possible: JSON reports from the parser, temporary CSV files, or logs. Write speed and minimal transformations are important for this zone. File storage (S3-compatible) or specialized tables in PostgreSQL without strict integrity constraints are often used. This approach allows you to return to the original data if a subsequent stage fails.

Main Layer (ODS / DWH)

After normalization, data is loaded into a relational schema. Our primary tool is PostgreSQL, thanks to its advanced support for JSONB, window functions, indexes, and extensions (e.g., pg_partman for partitioning). The structure is designed around business entities: products, prices, competitors, events. Historical tracking is achieved through SCD Type 2 or by adding time slices. The key requirement is the ability to perform fast analytical queries and join with the company's master data.

API and Data Marts

For visualization or delivery to adjacent systems (CRM, personal accounts), data marts and REST/GraphQL APIs are built. This layer is optimized for specific queries — materialized views, aggregations, and caches are used. Under high loads, we employ read replicas and, if necessary, columnar engines within the PostgreSQL ecosystem (e.g., Citus).

Normalization and Data Cleaning After Parsing

Raw strings from web pages need to be brought to a common denominator. Without this, it is impossible to build reliable analytics and avoid garbage in storage.

Type and Format Conversion

The parser may return a price as the string '1 499 rub.', but for calculations we need the number 1499.00 in a single currency. During transformation, we remove extra characters, handle locales, and convert units of measurement. For complex cases, custom functions in PL/pgSQL or Python handlers using regular expressions are written. All rules are recorded in configuration tables so that they can be changed without rebuilding the pipeline.

Handling Missing and Invalid Values

Missing fields should not 'break' the load. We replace them with explicit markers (NULL, 'N/A'), and rows with critical defects are sent to a quarantine table for later manual review or automatic enrichment. Range checks (price cannot be negative), pattern checks (email, phone), and business rules (the SKU must exist in the reference directory) are also applied here. Tools like Great Expectations or custom SQL checks allow formalizing data contracts.

Creating Relationships and Reference Dictionaries

An important step is extracting master data. For example, a brand name may be spelled differently. We normalize such values using synonym dictionaries and fuzzy matching algorithms (pg_trgm trigrams). The result is referential integrity and reduced duplicates. Reference dictionaries can be maintained either within PostgreSQL itself or synchronized with external MDM systems.

Queues and Stream Processing in ETL

Direct writes to the database from the scraper without intermediaries are dangerous: traffic spikes or blockages can lead to data loss. A message queue solves this problem and provides a number of other advantages.

Why a Queue is Needed

A message broker (RabbitMQ, Apache Kafka, Redis Streams) buffers the stream of raw parsing results. A process worker takes a batch of data, performs transformation, and loads it into the database. If the worker crashes, the message stays in the queue and will be processed again. This guarantees at-least-once delivery semantics and resilience to peak loads.

Decomposition of ETL Stages

Breaking down into independent stages connected by queues allows flexible scaling of each step. A typical chain:

• Queue-1: raw parser responses → validation and storage in staging;
• Queue-2: staging → normalization and loading into the main layer;
• Queue-3 (optional): data change events → update of data marts, indexes, notifications.

Each step can be run on a separate set of compute resources, and in case of delays, the number of workers can be increased. Logging and monitoring of accumulated metrics (queue length, processing time) allow timely anomaly detection.

Retries and Idempotency

All handlers are designed to be idempotent: receiving the same message again does not create duplicates. This is achieved using an idempotency key, computed as a hash of the source identifier and timestamp, or built-in database mechanisms — unique constraints and inserts with ON CONFLICT DO NOTHING.

Deduplication and Data Quality Assurance

Parsing data tends to produce duplicate records: the same item may be collected multiple times, especially during incremental updates. Uniqueness control and overall data quality are mandatory components of a mature ETL pipeline.

Deduplication Methods

• Strict deduplication by business key. A unique identifier (SKU, URL, combination of 'store+SKU') is merged with ON CONFLICT in PostgreSQL. On duplicate entry, you can either ignore it or update non-key attributes (price, availability).
• Fuzzy deduplication. When there is no strict key, string comparison algorithms (Levenshtein distance, metaphones) and additional heuristics are used. In PostgreSQL, the pg_trgm and fuzzystrmatch extensions are convenient for this. Records suspected of being duplicates are placed in a separate table for manual or automatic verification.
• Incremental packing. A periodic procedure for merging duplicates, running on a schedule. It deletes or links duplicates through mapping tables, preserving historical references.

Completeness and Accuracy Control

A Data Quality system involves not only deduplication but also continuous measurement of metrics. For example, the proportion of missing critical fields, deviation of prices from the market range, and the number of 'dead' URLs are automatically checked daily. When a metric exceeds a threshold, an alert is generated. This approach helps keep the pipeline under control even as sources expand. In our projects, we often use a combination of built-in PostgreSQL checks and external frameworks adapted for SaaS application pipelines.

Integration with the Customer's Ecosystem and Scaling

Clean, ready-to-use data must seamlessly integrate into the company's IT landscape. We help clients close the loop: from parsing (development of data collection and processing solutions) to delivering information into accounting systems and visualization on corporate portals. Integration examples:

• Loading competitor prices and stock directly into CRM to enrich product cards;
• Filling analytics data marts of a SaaS solution (development of cloud SaaS applications);
• Display on corporate portals through a secure API layer.

Pipeline scaling is achieved both vertically (increasing database resources, migrating to cluster configurations of PostgreSQL) and horizontally — through queues and containerization of workers. Thanks to our experience in cloud development, we adapt the architecture to the chosen provider (AWS, Yandex Cloud, Kubernetes), automate deployment, and ensure fault tolerance.

Frequently Asked Questions

How to choose a DBMS for storing large volumes of parsing data?

For most scenarios, PostgreSQL is an excellent choice. It is reliable, supports JSON, partitioning, window functions, and has powerful integrity enforcement tools. Specialized analytical databases (ClickHouse, Greenplum) can be used as a second layer for ultra-fast aggregations, but the operational ETL loop is easier to build on PostgreSQL. Under extreme loads, we supplement it with sharding (Citus) or streaming columns.

How to avoid duplicates when re-parsing the same pages?

Be sure to define a business key for the record (e.g., a hash of URL + article number or a unique identifier from the source). On the database side, create a unique index or constraint. When inserting, use the ON CONFLICT (id) DO UPDATE construct to update only changed fields, or DO NOTHING if the duplicate is not needed. For complex cases (fuzzy deduplication), add post-processing using trigrams or machine learning.

What to do if data arrives with errors, gaps, or in the wrong format?

Build multi-layer validation: at the input — schema and type checks, then business rules. Do not discard erroneous rows; instead, direct them to a quarantine table with an error code. Set up an interface for viewing and correcting such records, or define automatic regeneration rules (e.g., filling in missing fields by querying an alternative source). A regular data quality report will allow you to track trends and respond promptly to source degradation.

Can the ETL pipeline be scaled without rewriting all the code?

Yes, if the architecture is initially split into independent stages connected by queues. Each stage can be deployed as a separate service and scaled horizontally by increasing the number of worker replicas. The database is also scaled as volumes grow: partitioning, read replicas, and if necessary, moving to distributed SQL. Containerization (Docker, Kubernetes) and infrastructure as code simplify reconfiguration without stopping the pipeline. Our team designs solutions so that a growth point does not become a bottleneck — from backend development to cloud infrastructure.