标签：#Data

If your pipeline isn't idempotent, it isn't production-ready; it’s just a fragile script waiting to ruin your weekend. Most engineers treat "idempotency" as an academic concept for distributed systems papers, but in the trenches of fintech and healthcare, it’s the difference between a minor blip and a regulatory filing. If you can’t run your job five times in a row with the exact same input and get the exact same state, you aren't doing data engineering—you're doing data gambling. I’ve spent six years cleaning up the messes left by "append-only" thinking. I’ve seen millions of dollars in duplicate ACH transactions and patient records corrupted by "just one more retry" logic. This guide covers the patterns I use to make sure that when the scheduler kicks off at 3 AM, I can sleep through the alarm because the system knows how to fix itself. 1. Stop relying on "Append" mode The biggest sin in data engineering is assuming that your destination table is a clean slate. When a job fails halfway through, you don't want a partial load sitting in your production warehouse. Never use INSERT INTO blindly. If you are using BigQuery, Snowflake, or Databricks, use MERGE or overwrite-on-partition. If you are using SQL-based ELT, write your transformations to stage data in a transient table before swapping it into production. Never push directly to the target. -- The wrong way: INSERT INTO target_table SELECT * FROM staging -- The right way: Use an atomic swap or a MERGE statement MERGE INTO production . transactions AS T USING staging . transactions AS S ON T . transaction_id = S . transaction_id WHEN MATCHED THEN UPDATE SET T . amount = S . amount , T . status = S . status WHEN NOT MATCHED THEN INSERT ( transaction_id , amount , status ) VALUES ( S . transaction_id , S . amount , S . status ); Photo by 🇻🇪 Jose G. Ortega Castro 🇲🇽 on Unsplash 2. Partitioning is your safety net If your pipeline runs daily, your data must be partitioned by that day. If you are loading data without a

“Store the data” is only the beginning of the problem. The difficult questions usually come afterward: What structure does this data actually have? Which fields are missing or inconsistent? Which values are invalid? Which changes are safe to apply automatically? What exactly changed after a repair? Can the system prove that its storage and indexes are still consistent? I built Atlas to answer those questions inside the database engine itself. Atlas is a zero-dependency embedded database for semi-structured data. It stores records, builds a full-text search index, infers schema, analyzes data quality, proposes safe repairs, preserves uncertain records, and records an audit trail of applied changes. It does not use SQLite or SQL. It is not intended to replace SQLite for relational workloads. Instead, Atlas focuses on a gap that is usually handled by external scripts and tools: Data inspection, diagnosis, and safe repair as first-class database capabilities. That is the problem Atlas was built to solve. Why data quality belongs inside the database engine Most databases are very good at storing and retrieving data. That is necessary, but real-world data work rarely stops there.** Operational records, imported JSON, CSV files, event payloads, and semi-structured documents often arrive with problems: { "id" : "T-1" , "title" : " Connection timeout " , "priority" : "HIGH" } { "id" : "T-1" , "title" : "connection timeout" , "priority" : "high" } { "id" : "T-2" , "title" : "Unicode café search" , "priority" : null } These records contain several potential issues: Duplicate logical identifiers Leading or trailing whitespace Inconsistent capitalization Null-like values Missing fields Mixed data types Malformed email addresses Different date formats Inconsistent structures across records A storage engine can preserve these values perfectly while still leaving the data difficult to understand and use. The usual response is to add external tools: A schema profiler A data-quality

Quick answer Craigslist search pages ship two copies of every listing: a static HTML list, and a JSON-LD <script> block with images, currency, and geo-coordinates. The obvious move is to join them by ID. Don't — Craigslist's JSON-LD carries no shared identifier at all , not a bare post ID, not a URL, not a SKU. The only field both copies reliably share is the listing's title, and titles repeat. We joined by title through a per-title FIFO queue and measured it recovering 290 of 325 listings (89%) end-to-end on a captured 298-item page. That number is the ceiling of what a title-only join can do on this page shape — plan your field completeness around it, don't assume 100%. Why can't you just match the JSON-LD by ID? 🧩 When we built the Craigslist Multi-City Listings Scraper , the first design assumed what almost every JSON-LD block on almost every e-commerce-shaped site provides: a productID , a sku , a url , an @id — something that lines up a JSON entry with its DOM counterpart deterministically. Live inspection of a captured Craigslist search page found none of those. Each itemListElement entry has exactly name , image , offers , @type , and a position field that looks like it should solve the problem — until you check it against the static list past the first ~18 entries, where some static-list rows have no JSON-LD counterpart at all and the position numbering drifts out of alignment. So the join key that's actually usable, live, is title — and titles aren't unique. The fix is a FIFO queue per title: walk the static <li> list in document order, and for each title pop the next unconsumed JSON-LD entry with a matching name. # actors/craigslist-listings-scraper/src/search_parser.py def _parse_ld_json ( tree : HTMLParser ) -> dict [ str , deque [ _LdEntry ]]: by_title : dict [ str , deque [ _LdEntry ]] = defaultdict ( deque ) for list_item in data . get ( " itemListElement " , []): entry = _ld_entry_from_item ( list_item ) title = list_item . get ( " item " , {}). get

Quick answer BBB.org doesn't render business data into the HTML table it looks like it does. Both the search-results page and every business profile page embed the real data as one inline JSON blob — webDigitalData — sitting inside a <script> tag meant for analytics, not for you. The visible <dt> / <dd> list underneath it only carries the fields the analytics layer left out (accreditation date, years in business). If you scrape the DOM table and ignore the script tag, you'll get a name and maybe a phone number and nothing else. If you scrape the script tag and ignore the DOM, you'll get ratings and IDs but no address, no years-in-business, no website. You need both, merged, per business. Why does the DOM only have half the data? 🕵️ When we built the BBB Business Leads Scraper , the first pass assumed BBB profile pages worked like most directory sites: a template with labeled fields you css_first() your way through. That assumption survives for exactly four fields — address, accreditation date, years in business, and website — which live nowhere except a <dt> / <dd> definition list further down the page. Everything that actually matters for lead scoring — business name, BBB letter grade, accreditation status, phone number, the internal IDs BBB uses to build its own canonical URL — comes from a different place entirely: a webDigitalData object, wired into the page for BBB's own analytics vendor, that happens to be complete, well-formed JSON: # actors/bbb-business-leads-scraper/src/parsers/common.py WEB_DIGITAL_DATA_MARKER = " webDigitalData " def extract_web_digital_data ( html : str ) -> dict | None : marker_pos = html . find ( WEB_DIGITAL_DATA_MARKER ) if marker_pos == - 1 : return None brace_start = html . find ( " { " , marker_pos ) raw = _extract_balanced_json ( html , brace_start ) ... return json . loads ( raw ) That is not a regex grabbing {.*} between two markers — a naive non-greedy match snaps shut on the first stray } inside a nested object, which this blo

We maintain a corpus of 456 role-specific resume examples in TypeScript. Someone asked me what a good bullet point actually looks like, and rather than answer from taste I decided to measure the thing I already had. Fifteen minutes later we had a script, a set of numbers, and a conclusion. The conclusion was wrong, because the script had silently read about twelve percent of the data. This is a post about that failure mode, and then about the numbers I got once the script worked. The corpus Thirty-one TypeScript files, each exporting an array of role objects. One role looks roughly like this: { slug : ' cloud-architect ' , title : ' Cloud Architect Resume ' , category : ' Information Technology ' , sampleData : { summary : ' ... ' , experiences : [ { company : ' Amazon Web Services ' , position : ' Senior Cloud Architect ' , description : ' - Designed multi-region architecture... \n - Led migration of... ' , }, ], skills : [...], }, tips : [...], } The interesting field is description . It holds a newline-delimited list of bullets as a single string, so the whole corpus of bullets is sitting there in source, greppable, without a database or an export step. Version one const descs = [... text . matchAll ( /description: ' ((?:[^ ' \\] | \\ . ) * ) '/g )]. map ( m => m [ 1 ]); Nothing exotic. Match description: , then a single-quoted string, allowing escapes so an apostrophe inside the text does not terminate the match early. It found 199 description strings. I did not question that, because I had no prior for what the number should be. 199 sounded like a lot of text. We computed medians off it, looked at the opener distribution, and started writing. The number that saved me was on a different line of the same output: roles 456 . The slug count was fine. So 456 roles between them had 199 job descriptions, which would mean the overwhelming majority of roles had no work history at all. I knew that was false, because I had rendered these pages. Why it read twelve percent

The short version NextAuth (now Auth.js) creates 4 tables in your database: users , accounts , sessions , and verification_tokens . The users and accounts tables have a one-to-one relationship via accounts.user_id . Sessions link to users via sessions.user_id . Verification tokens are short-lived and self-cleaning. The 4 tables users Column Type What it means id text / UUID Primary key. Generated by NextAuth. name text Display name from the OAuth provider (Google, GitHub, etc.) email text User's email. May be null if the provider doesn't share it. email_verified timestamp When the email was verified. Null if never verified. image text Profile picture URL from the provider. created_at timestamp When the user first signed in. updated_at timestamp Last profile sync from the provider. accounts This table links a user to an OAuth provider. One user can have multiple accounts (e.g., Google + GitHub). Column Type What it means id text / UUID Primary key. user_id text Foreign key → users.id . type text Always "oauth" or "oidc" . provider text "google" , "github" , "discord" , etc. provider_account_id text The provider's unique ID for this user. refresh_token text OAuth refresh token (encrypted in production). access_token text OAuth access token (encrypted in production). expires_at integer When the access token expires (Unix timestamp). token_type text Usually "Bearer" . scope text Permissions granted by the provider. id_token text OIDC ID token (if using OIDC). session_state text Provider-specific session state. sessions Active sessions for each user. NextAuth creates a new row here on every sign-in. Column Type What it means id text / UUID Primary key. session_token text The session token stored in the user's cookie. user_id text Foreign key → users.id . expires timestamp When this session expires. verification_tokens Short-lived tokens for email verification, password reset, etc. Self-cleaning old tokens are deleted automatically. Column Type What it means identifier te

I was testing out CH-Ops - an admin GUI for self-hosted ClickHouse - on a simple setup: 1 shard, 2 replicas. Stumbled onto the replication queue almost by accident. Here's what I did: I stopped one of the nodes (let's call it Node B), then inserted some data through the other one (Node A). Just wanted to see what would happen. Then, while Node B was still down, I checked it in CH-Ops. It had stuff sitting in its replication queue. My first assumption was: okay, this must be showing what's left to replicate across the cluster - the total pending replication work. So I switched over and checked Node A, the one that was actually up and had just received the insert. Its queue was empty. That didn't match what I expected at all. If the queue was a cluster-wide "here's what still needs to replicate" view, Node A should've shown something too - it was the one that had the fresh data now waiting to reach Node B. Instead it was Node B, the down one, sitting there with pending tasks. That mismatch is what sent me digging. Turns out the queue isn't cluster-wide at all - it's specific to each ClickHouse instance. Once I brought Node B back up, its queue drained in seconds and the data showed up. That whole experiment is basically the entire post in miniature. Here's the mental model I ended up with. A Queue Belongs to a Replica, Not to the Table This is the first thing to get straight. With a ReplicatedMergeTree table, you can have multiple replicas holding copies of the same data. It's tempting to think of replication as one shared pipe between them. It isn't. Each replica keeps its own local replication queue . So if you see: Replica 1 → queue_size = 0 Replica 2 → queue_size = 25 that doesn't mean 25 operations are waiting somewhere in the middle for both replicas to pick up. It means Replica 2, specifically, has 25 tasks it hasn't finished yet. Once that clicked for me, the rest of the system made a lot more sense. So Where Do These Tasks Come From? Replication in ClickHouse

Introduction: Turning Jumia Product Data into Business Insights E-commerce platforms generate a lot of product data, but raw numbers become useful only when they can support better decisions. For Jumia sellers, prices, discounts, ratings and customer reviews can provide clues about product performance, customer engagement and possible pricing strategies. For this project, I worked with a dataset of 112 Jumia products to explore these relationships using Microsoft Excel. I wanted to find out whether higher discounts are associated with more customer reviews, whether highly rated products receive stronger engagement, and whether product price is related to rating. I also wanted to identify the products performing best and those that may require a different pricing or marketing approach. I followed a complete data-analysis workflow: Raw Data → Cleaning → Transformation → Analysis → Visualization → Insights → Recommendations The project uses Excel Tables, Power Query, formulas and functions, PivotTables, PivotCharts, slicers and dashboard techniques. This article documents that process and shows how the raw Jumia data was transformed into an interactive dashboard and, ultimately, evidence-based business recommendations. Understanding the Dataset and Its Initial Problems Before cleaning the data, I first needed to understand what I was working with. The dataset contains 112 Jumia products and six main fields: Product, Current Price, Old Price, Discount, Review and Rating. Current Price and Old Price represent product pricing, Discount captures the promotional percentage, Review represents the number of customer reviews, while Rating records the average customer rating out of 5. I treated this stage as a data-quality audit rather than immediately changing anything. The purpose was to identify issues that could affect calculations and visualizations later. The raw dataset contained formatting and consistency issues that needed attention, particularly around numerical field

When multiple companies are behind one project, who bears responsibility for problems?

When multiple companies are behind one project, who bears responsibility for problems?

Author: Ignacio Paz An AI agent is only as useful as what it can remember - and how well it can connect the things it remembers. Most teams hand their agent a memory by reaching for a vector store: embed everything, retrieve by similarity, hope the relevant context comes back. It works, until you notice the agent keeps surfacing things that are near the question but not actually connected to it. Cobrainer , a skills-intelligence company based in Munich, took a different route. They gave their AI agent a memory that lives in the database as a graph, where the agent builds the relationships between nodes as it goes. They did it without adding a graph database, a vector engine, or a search engine to their stack. It all runs on SurrealDB, alongside a Rust-native agentic graph RAG built on the same store. Here's how, and why a single engine made the difference. The problem with flat memory Cobrainer runs a skills-intelligence platform - the kind of system that reasons about how people, roles, skills, and capabilities relate to one another. That's an inherently graph-shaped problem. But their first retrieval setup wasn't graph-shaped at all. It pulled context through flat vector retrieval over an S3-and-OpenSearch pipeline, which carried two recurring costs: Accuracy . Flat vector matches returned context that was loosely related - semantically near, but not necessarily connected in any meaningful way. The team wanted the agent to follow real relationships between entities, so its answers were grounded rather than approximate. Tokens . Broad vector matches meant stuffing a lot of marginally relevant context into every prompt - expensive, and more so with every call. The team wanted to fetch only the context that mattered. The obvious fix - adding a graph database on top of the vector and search systems they already ran - would have meant more infrastructure to operate. For a startup moving fast, that fragmentation was the thing to avoid, not embrace. What they wanted inst

COUNT(*) looks like a trivial operation: SELECT COUNT ( * ) FROM orders ; The query asks for a single number, but that doesn't mean PostgreSQL can produce it with a constant-time read from some internal counter. When we need an exact count, PostgreSQL has to determine how many rows are actually part of the visible result set for that query. On large tables, that work can become a meaningful chunk of total execution time. And the problem doesn't just go away by throwing an index at it. The useful question isn't "do I have an index?" It's: How many rows does PostgreSQL actually need to examine to compute this count — and can that work be reduced? Why COUNT(*) Can Be Expensive in PostgreSQL PostgreSQL uses MVCC — Multi-Version Concurrency Control — to manage concurrent access to data. That's what lets multiple transactions work at the same time while each sees a consistent view of the database. But it also means row visibility depends on the snapshot the query is running under. That's why PostgreSQL can't answer: SELECT COUNT ( * ) FROM orders ; by simply reading an exact counter stored somewhere in the table's metadata. To return an exact result, it has to process the rows — or an index structure representing those rows — and determine which ones are part of the visible result. On a small table, that cost is invisible. On a table with millions of rows, the amount of work starts to matter. Which leads to an important distinction: returning a single row from COUNT(*) does not mean processing a single row. How to Analyze a COUNT with EXPLAIN ANALYZE Before reaching for an index, it's worth looking at what PostgreSQL is actually doing. Say we have this query: SELECT COUNT ( * ) FROM orders WHERE status = 'completed' ; We can analyze it with: EXPLAIN ( ANALYZE , BUFFERS ) SELECT COUNT ( * ) FROM orders WHERE status = 'completed' ; The goal isn't to hunt for an Index Scan by default. Worth checking instead: the scan type estimated rows vs. actual rows processed rows discard

Ever woke up feeling like a truck hit you, only to realize your Apple Watch had been screaming "Warning!" via your data for the last 24 hours? Heart Rate Variability (HRV) is the "canary in the coal mine" for our bodies. It's a powerful metric that tracks the variation in time between each heartbeat, serving as a direct window into your Autonomic Nervous System. In this guide, we are going to build a real-time HRV anomaly detector using wearable data analysis , Scikit-learn , and AWS Lambda . By applying machine learning to time-series health data, we can identify physiological stress, potential infections, or overtraining before physical symptoms even manifest. If you’ve been looking to dive into anomaly detection in time-series or want to master health data engineering , you’re in the right place! The Architecture: From Heartbeat to Alert 🛠️ To achieve real-time monitoring, we need a pipeline that moves data from your wrist to a cloud-based inference engine. Here is the high-level flow: graph TD A[Apple Watch / Wearable] -->|Sync| B(Apple HealthKit) B -->|Webhook/Hook| C[AWS API Gateway] C --> D[AWS Lambda - Inference] D -->|Fetch History| E[(DynamoDB / S3)] D -->|Isolation Forest| F{Anomaly?} F -->|Yes| G[Push Notification / Alert] F -->|No| H[Log & Silent] Prerequisites 📋 Before we start coding, ensure you have the following: Python 3.9+ Scikit-learn & Pandas for data crunching. AWS Account (for Lambda deployment). An app to push HealthKit data (like Health Auto Export or a custom Swift hook). Step 1: Understanding the Data 📊 HRV data is tricky because it’s highly personalized. What is "low" for an athlete might be "high" for someone else. This is why we use Isolation Forest , an unsupervised learning algorithm that excels at detecting outliers in multi-dimensional datasets without needing labeled "sick" vs. "healthy" days. Step 2: Building the Anomaly Detection Logic Let's write the core logic using Scikit-learn . We’ll use the Isolation Forest algorithm becaus

Originally published on angeo.dev . Full tables, p-values and the sealed plan are there. Most claims about AI visibility are untestable by design: publish the signals, wait, attribute anything good that happens to the signals. I wanted a version I could not fudge, so I wrote the analysis plan first, hashed it, and sent the hash to the other party before I had any data. The question Do businesses AI assistants name repeatedly differ, on observable technical signals, from businesses the same assistants name once ? Every business in the corpus was named at least once, so this says nothing about how to enter an answer. It compares repeat against one-off mentions inside a named-business corpus. Four signals, all externally observable: Signal Check Crawler access Does robots.txt block any of 8 AI crawlers Content map Does the site serve /llms.txt Structured data Does a product page emit JSON-LD Product Buyability Does that node carry offers.availability Study setup The answers came from a partner (connexion.me), who ran 44 product-level home-decor buying questions across ChatGPT, Gemini and Perplexity, twice, in two arms — 264 answers per arm. Blinding was deliberate. I did not write the questions and did not see their store list until my plan was sealed; they never saw my frame, my scan results or my thresholds. Roster rows 669 no resolvable domain -186 resolved to a different company -3 marketplaces and listing surfaces -12 duplicate rows collapsed -10 Unique domains analysed 458 scanned successfully 455 Cases: 3+ mentions across both runs and present in both. Controls: exactly one mention across both runs. Head excluded first — anything in 53+ of 264 answers (Amazon, Etsy, Wayfair, Target, Home Depot). The pre-registration Sealed 10 August, SHA-256 9b4ccf12629e… : Under 15% of named businesses would be Magento No signal would separate the groups by more than 15 points Refutation condition: any signal differing by 20+ points with the named group higher Result — generic

Overview In this article, I'll introduce the concept of Multi-Version Concurrency Control (MVCC) and explain how Postgres implements this protocol across different isolation levels. I'm assuming you already have a basic understanding of isolation levels, database locks, and concurrency in general. I won't cover those concepts here, so if you're not familiar with them, I highly recommend checking out A Straightforward Guide for Isolation Levels first before continuing. The goal of this article is to help you understand: What Multi-Version Concurrency Control is How Postgres implements MVCC across different isolation levels Multi-Version Concurrency Control High-Level Concept The idea behind MVCC is simple: it's a protocol designed to accomplish one goal — when two or more transactions run concurrently on the same data, the end result should look as if those transactions ran one after another, in sequence. Take a look at the diagram above. Two transactions are running concurrently, and we want the end result to look as if either the first transaction ran and committed before the second one started, or vice versa. MVCC guarantees there are only two possible outcomes — never a third. But in reality, these transactions are running at the same time, so this is exactly the core idea of MVCC: it's a protocol that gives us this guarantee even though the transactions genuinely overlap in time. Note that other protocols aim for the same goal, like Two-Phase Locking and Optimistic Concurrency Control. They all take different approaches, but they're all working toward the same thing. The core idea of MVCC is that whenever a transaction updates a row, it doesn't mutate the value in place. Instead, it creates a new record as the latest version and links it back to the old version. After the update, the row has a new version, and the old version is never changed. This version chain exists per record — every update to a row creates a new version, and since each version is linked to

Polls show that overwhelming majorities of Americans hate data centers. China makes a perfect scapegoat for tech leaders and their allies—the only problem is a lack of evidence.

We shipped a filter that threw away bad GPS readings. Months later somebody asked whether it was working, and I could not answer. The evidence was gone. That question changed how I build anything that rejects data. The obvious version, and why it rots Mileage tracking depends on trustworthy distance, and GPS lies constantly. So the first version of our cleanup did what everyone's first version does: if (! fix . isPlausible ( previous )) return // drop it, move on accumulateDistance ( fix ) Clean data comes out the other end. It feels responsible. It is also a trap, because that return destroys the only record that could ever tell you whether the rejection was correct. Six months in, someone asked the reasonable question: is the filter right? I could not say how many readings we had dropped, on which journeys, or whether any of them had been a genuine drive through a tunnel rather than a glitch. We had built a thing that made a judgement call thousands of times a day and kept no record of any of it. Persist, then classify The rebuild flipped the default. Rejection stopped being a return and became a label. Only two cases are still deleted, because they cannot physically be real: // impossible coordinates if ( fix . lat ! in - 90.0 .. 90.0 || fix . lng ! in - 180.0 .. 180.0 ) return null // impossible accuracy: too precise to be true, or useless if ( fix . accuracyM <= 0.1f || fix . accuracyM >= 250f ) return null That is the entire delete list. Everything else is persisted and sorted into named accumulators: originalDistanceM += displacement // every metre we ever saw when { fix . isMock -> mockDistanceM += displacement abnormal -> { abnormalDistanceM += displacement if ( isHardSpike ) spikeDistanceM += displacement } accuracyGated -> { /* recorded, deliberately not counted */ } else -> cleanedDistanceM += displacement } Five numbers instead of one. The UI shows cleaned . The rest live beside it. And the row itself keeps its provenance: accuracy, provider, bearing, a

If you're prepping for a Business Intelligence (BI) Engineer, Data Analyst, Data Engineer, or Data Scientist role at Amazon, you probably already know SQL matters. It's a core part of the hiring bar. But Amazon isn't just checking your syntax. They want to see if you can think in sets, write clean queries under pressure, and reason about data the way the business actually uses it. Here's exactly how to prepare based on what the interview actually rewards. What the Interview Really Tests Amazon's SQL rounds usually show up in one of two ways. It's either a technical screen using a shared coding tool, or a whiteboarding case-study during the main loop. Either way, the interviewer is watching for a few specific signals. For starters, correctness always beats speed. A working query is far better than a clever one that fails. Communication is also huge. Do you talk through your logic before you even touch the keyboard? You'll often get a vague ask, like finding the "best" customers. You are completely expected to define what "best" means out loud before you start writing Common Table Expressions (CTEs). And watch out for messy data. Nulls, duplicates, and mismatched grain are almost always baked into the problem on purpose. The Core Topics to Master Focus your prep time on a few specific areas. Actually, it turns out this is where almost all the interview questions live. Joins Inside and Out You need to know your INNER, LEFT, RIGHT, and FULL OUTER joins cold. Be ready to explain exactly why row counts change after each one. A classic Amazon-style question is finding customers who placed orders but never left a review. That's just a LEFT JOIN with a NULL check. The interviewers want to see you reach for it right away. Window Functions Functions like ROW_NUMBER() , RANK() , DENSE_RANK() , and LAG() or LEAD() show up constantly. You might see a common pattern—like finding the second-highest order value per customer, or calculating month-over-month growth. If you're shaky he

Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered. Question : If there is a large amount of code that needs to be rewritten, manual processing would be time-consuming and prone to errors. Is it possible to achieve automatic conversion of code syntax in large quantities through tools? Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches. For example： Suppose SQL PIVOT function is as follows : SELECT * FROM ( select country , state , yr , qtr , sales , cogs from table111 ) PIVOT ( SUM ( sales ) AS ss1 , SUM ( cogs ) AS sc FOR qtr IN ( 'Q1' AS Quarter1 , 'Q2' AS Quarter2 , 'Q3' AS Quarter3 , 'Q4' AS Quarter4 ) ) tmp ; Using the ZGLanguage conversion rule, execute the conversion to obtain the result : SELECT * FROM ( select ### , ### , ### SUM ( case when qtr = 'Q1' then sales else null end ) AS Quarter1_ss1 , SUM ( case when qtr = 'Q2' then sales else null end ) AS Quarter2_ss1 , SUM ( case when qtr = 'Q3' then sales else null end ) AS Quarter3_ss1 , SUM ( case when qtr = 'Q4' then sales else null end ) AS Quarter4_ss1 , SUM ( case when qtr = 'Q1' then cogs else null end ) AS Quarter1_sc , SUM ( case when qtr = 'Q2' then cogs else null end ) AS Quarter2_sc , SUM ( case when qtr = 'Q3' then cogs else null end ) AS Quarter3_sc , SUM ( case when qtr = 'Q4' then cogs else null end ) AS Quarter4_sc from ( select country , state , yr , qtr , sales , cogs from table111 ) where qtr IN ( 'Q1' , 'Q2' , 'Q3' , 'Q4' ) group by ### , ### , ### ) tmp ; The conversion rule is as follows : __DEF_FUZZY__ Y __DEF_DEBUG__ N __DEF_CASE_SENSITIVE__ N __DEF_LINE_COMMENT__ -- __DEF_LINES_COMMENT__ /* */ __DEF_STR__ __IF_KW__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_ [NO] XXX __DEF_PATH__ __FROM_PIVOT_1_1__ 1 : frm @ %__IF_KW__ | from : tab @ | __TABLE_NAME__ : ssl @

pg_anon found 1 of the 8 personal-data columns in my PostgreSQL database. The one it caught was email , and only because "email" is spelled the same in Spanish and English. The other seven — nombre , apellido , telefono , direccion , fecha_nac , tarjeta_ult4 and rut — walked straight through, unmasked. pg_anon is the open TantorLabs tool that masks personal data in PostgreSQL: it scans the database, flags the sensitive columns, and dumps a masked copy. Like pg_dump , but covering the sensitive parts on the way out. Exactly what you want before handing a colleague a copy of production. So I fed it a Chilean database and watched it miss almost everything — no error, no warning. It finished successfully and handed me a dump with names and national IDs still in cleartext. What the scan actually does Two filters, in order. First it reads each column's name against a set of regexes ( ^email$ , ^phone$ , ^ssn$ …). To the columns left over, it opens the data and tries patterns on the value (an email's @ , a card's 16 digits). Whatever no filter catches passes through. The rules in the demo meta-dict it ships with are written for English schemas. Mine aren't. 1 of 8 Column Holds Stock rules email email ✅ nombre first name ❌ apellido surname ❌ telefono phone ❌ direccion address ❌ fecha_nac birth date ❌ tarjeta_ult4 card digits ❌ rut national ID ❌ email got caught by name (it's an English word) and confirmed by its @ . Everything else has a Spanish name no stock rule looks for. The rut is the clearest miss. A RUT looks like 7917183-2 : seven or eight digits, a dash, and a mod-11 check digit that can be the letter K . The only national-ID rule pg_anon ships with is ssn . It has no idea what a RUT is — and it won't know a cpf (Brazil), dni (Spain, Argentina), curp (Mexico), nif (Portugal) or aadhaar (India) either. If your schema isn't American, the defaults miss your most sensitive column. The fix: a few lines of Spanish You teach it. Column names in your language, plus a conte