ETL

ETL stands for Extract, Transform, Load, the three steps for moving data from a source system into a database where it can be queried. NIST's Big Data reference model lists "Stream and Extract, Transform, Load (ETL) Processing" as a processing feature that "filters and transforms data flows between external data resources and internal Big Data systems" (NIST SP 1500-5).

The three steps (NIST SP 1500-6r2)

• Extract. "The collection activity is likely where the extraction portion of the Extract, Transform, Load (ETL)/Extract, Load, Transform (ELT) cycle is performed." At this point "Initial metadata is created (e.g., subjects with keys are identified) to facilitate subsequent aggregation or look-up methods."
• Transform. "The preparation activity is where the transformation portion of the ETL/ELT cycle is likely performed." Tasks "could include data validation (e.g., checksums/hashes, format checks), cleaning (e.g., eliminating bad records/fields), outlier removal, standardization, reformatting, or encapsulating." This step may also "aggregate data from different Data Providers, leveraging metadata keys to create an expanded and enhanced dataset," and it is where "provenance data will be verified or attached/associated."
• Load. The collection step "must store/buffer the received data until it is persisted," and the prepared data is then written to the storage the rest of the system reads.

ETL and ELT

NIST names both orders. In ELT (Extract, Load, Transform), as the name says, loading comes before transforming. NIST also notes that in some large-scale storage systems "Any type of data and many sizes of files can be handled without formal extract, transformation, and load conversions."

Where errors creep in

Every transform is a decision. NIST lists "optimization of data through manipulations (e.g., deduplication)" among preparation steps, and says a data scientist "must be aware of the sources and provenance of the data, the appropriateness and accuracy of the transformations on the data, the interplay between the transformation algorithms and processes, the analytic methods and the data storage mechanisms" (NIST SP 1500-1r2). Common failure points:

• A deduplication rule that merges two distinct records.
• A standardization step that rewrites a name or code.
• A format check that drops rows it cannot parse.

ETL is usually one stage of a larger Data Pipeline; source data often arrives through an API. Records converted from scanned images start with Optical Character Recognition.

Why it matters for land and mining claim records

Land and mining claim data arrive from different offices in different formats. Converting dates, standardizing names, and removing duplicates each change the data. Keeping the untransformed source next to the loaded version, and recording which transforms ran, lets anyone trace a figure back to the document it came from.