ETL and the metadata repository
Clear, exam-focused notes on ETL (Extract, Transform, Load) processes and the metadata repository: definitions, architecture, types of metadata, design guidelines, performance sizing formulas, worked examples, common mistakes, and exam pointers.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
ETL is the standard engineering process for moving and preparing data from operational systems into a data warehouse or data mart so analytics and BI tools work correctly and fast. The metadata repository stores the information (about data sources, transformations, schedules and lineage) that makes ETL traceable, auditable and automatable — essential for reliable, maintainable enterprise data engineering.
Key ideas
Start with definitions (first time each bold term is used):
ETL (Extract, Transform, Load): a process that (1) extracts data from one or more source systems, (2) transforms it to apply cleaning, business rules and format changes, and (3) loads it into a target (data warehouse, data mart, data lake). Assume batch or near-real-time modes. Also contrast with ELT (Extract, Load, Transform), where data is first loaded into the target and transformations are executed there (common in modern cloud warehouses); be prepared to justify ETL vs ELT in design answers.
Metadata: data about data. In ETL context we use three main kinds:
- Technical metadata: schema, column types, file formats, data sizes, table definitions, indices.
- Business metadata: business glossaries, meanings of fields, owner, SLAs, quality rules.
- Operational metadata: run history, row counts, error logs, throughput, timestamps, last successful run.
Metadata repository: a database or store that holds the metadata (all types) used by ETL tools, schedulers, auditors and report writers.
ETL architecture and components (draw and label a diagram):
- Diagram to draw: left-to-right boxes. Left-most: multiple Source systems (RDBMS, files, APIs). Arrow to: Staging area (label: raw copies, schema close to source). Arrow to: Transformation engine (label: cleansing, joins, lookups, aggregations, business rules). Arrow to: Target DW/Data Mart (label: star/snowflake schema with fact and dimension tables). Above these boxes, a strip labeled Metadata repository connected with arrows to all components. Also show a Scheduler/Orchestrator box that triggers extraction and load, connected to metadata and transformation engine. Below, show Monitoring & Audit connected to operational metadata.
Staging area: temporary storage where extracted raw data sits. Use it for replay, reprocessing and as buffer when sources are slow.
Extraction methods:
- Full extract: copy complete source dataset each run.
- Incremental extract: copy only changed rows since last run. Requires change detection using timestamps, high-water mark (max last-modified value), database logs (log-based CDC) or triggers.
Change Data Capture (CDC): three main approaches:
- Timestamp-based: read rows where last_modified > last_run_time. Simpler but depends on correct clock and updates.
- Trigger-based: database triggers write changes to a delta table; ETL reads delta. Works with any RDBMS but adds overhead.
- Log-based (LSN-based): read database transaction logs for committed changes. Low impact on source and preferred for high-volume systems. Ensure deletes are captured (e.g., tombstone records) and define clear high-water-mark boundaries (e.g., LSN > last_processed vs LSN >= next_start) to avoid missed or duplicated changes.
Transformation types (examples): data cleaning (remove duplicates, fix nulls), data type conversion, lookups (surrogate key assignment), conforming dimensions, deduplication, standardisation, enrichment, aggregation, slowly changing dimension (SCD) handling (Type 1 overwrite, Type 2 versioning, Type 3 partial history), and surrogate key generation.
Load strategies:
- Full load (replace target) — simple, expensive.
- Incremental load (apply delta inserts/updates/deletes) — common in production.
- Upsert (insert or update) — requires match keys and unique constraints.
- Bulk load vs row-by-row — bulk uses database bulk utilities for throughput.
Metadata-driven ETL: configuration, mappings, and rules are stored in the metadata repository so ETL jobs read metadata and operate generically. Advantages: reusability, easier changes, automation.
Quality, auditing and lineage:
- Data quality: defined by rules in business metadata (e.g., age between 0–120). ETL must validate and classify rows (accept/reject/quarantine) and store failures in operational metadata.
- Lineage: traceability from target field back to source columns and the specific transform steps. The metadata repository must store mapping definitions and transformation logic to support lineage and impact analysis.
Performance and capacity concerns:
- Calculate required throughput to meet an ETL window (batch window) and choose bulk load, partitioning, parallelism, and push-down optimisations.
- Avoid long locks or heavy queries on source during business hours; use replicas or log-based CDC.
Security and governance:
- Metadata repository must store access controls and be auditable. Business metadata supports data governance, stewardship and GDPR/Privacy compliance. Include data classification tags (e.g., PII/sensitive) and enforce encryption at rest and in transit for both data and metadata repositories.
Design principles for the metadata repository:
- Separate schema areas for technical, business and operational metadata.
- Use unique identifiers for objects (object_id), versions, and timestamps for changes.
- Support queryable lineage, impact analysis, and simple APIs for ETL tools.
Formulas
throughput = total_records / available_time — throughput required in records per second (records·s^-1). Applies when sizing batch ETL to finish in a window. total_records in records, available_time in seconds.
data_volume_rate = total_bytes / available_time — bytes per second (B·s^-1). Use SI bytes.
CPU_time_estimate = records * CPU_cycles_per_record / CPU_cycles_per_second — time in seconds for CPU-bound transforms. records dimensionless, cycles are cycles·record^-1, CPU cycles per second in cycles·s^-1 (Hz).
latency = extract_time + transform_time + load_time — end-to-end latency for a single run, in seconds.
Remember syntax patterns to note for ETL tools (notation, not code):
- Mapping: source.column -> transformation(expression) -> target.column
- Job dependency: JobB depends on JobA (JobA -> JobB)
Worked examples
Example 1 — Batch window sizing (numerical) Problem: Source generates 50 000 000 records per day. ETL batch window available = 4 hours (4·3600 = 14 400 s). What throughput in records per second is needed? If average transform increases record size to 2 kB and target DB bulk loader writes at 20 MB/s, is the loader the bottleneck?
Steps and solution:
- Required throughput = total_records / available_time.
throughput = 50,000,000 / 14,400 ≈ 3472.22 records·s^-1. - Data volume rate after transform = records·size = 3472.22 * 2000 B = 6,944,444 B·s^-1 ≈ 6.944 MB·s^-1.
- Bulk loader capacity = 20 MB·s^-1 which is greater than 6.944 MB·s^-1, so loader is not the bottleneck. The required CPU and I/O for transformations must still be verified.
Final answer: Required throughput ≈ 3472 records/s; data rate ≈ 6.944 MB/s; bulk loader capacity 20 MB/s is sufficient.
Example 2 — Incremental load trace using LSN (algorithm trace) Problem: Source DB log gives LSN numbers per changed row. Last processed LSN = 1000. New log entries (ordered): (LSN=1001, pk=10, colA=5), (LSN=1002, pk=11, colA=3), (LSN=1003, pk=10, colA=6). Show steps for ETL to apply changes to target with SCD Type 1 (overwrite).
Trace:
- ETL records last_processed_lsn = 1000.
- Read logs where LSN > 1000 => entries 1001,1002,1003.
- Apply entry 1001: if pk=10 exists in target update colA=5; else insert. (assume pk=10 exists) -> update to 5.
- Apply entry 1002: pk=11 -> insert with colA=3.
- Apply entry 1003: pk=10 -> update colA=6 (overwrites earlier update from same batch). Final pk=10 colA=6.
- After successful commit set last_processed_lsn = 1003 in operational metadata.
Answer — final target state: pk=10 colA=6; pk=11 colA=3. Operational metadata updated to LSN 1003.
Example 3 — Short model-answer: metadata repository tables (design) Question: Sketch schema for a metadata repository storing technical, business and operational metadata.
Model answer (points to include):
- Table: object_catalog (object_id PK, object_name, object_type [TABLE/COLUMN/FILE], owner, creation_ts)
- Table: column_definitions (column_id PK, object_id FK, column_name, data_type, length, nullable, business_term_id FK)
- Table: business_terms (business_term_id PK, term_name, definition, steward)
- Table: transformations (transform_id PK, mapping_source, mapping_expression, target_column_id FK)
- Table: operational_runs (run_id PK, job_name, start_ts, end_ts, rows_extracted, rows_loaded, status, error_code)
- Table: lineage (lineage_id PK, target_column_id FK, source_object_id, source_column_id, transform_id)
Explain constraints, indices, and that operational_runs must be append-only to preserve audit trail. Mention APIs for tools and that each table should record version and effective_from/effective_to for change tracking.
Common mistakes
- Using timestamp-based CDC without considering clock skew or late-arriving updates. Always mention limits of the chosen CDC method.
- Not planning for reprocessing: failing to keep staging copies or reversible operations so replays are difficult.
- Assuming bulk load always fastest — if transforms are heavy and cannot be pushed down, row-by-row may be necessary during complex validations.
- Ignoring error handling: not storing rejected rows with reasons in operational metadata.
For your exam
Question types you will see:
- Definitions and short notes: define ETL, metadata repository, CDC, types of metadata.
- Diagrams: draw ETL architecture with staging, metadata repository and scheduler; label components and flows (sources, staging, transform, target, metadata, scheduler, monitoring).
- Derivations / numericals: compute throughput or data rate for a batch window; estimate CPU time from given per-record CPU cycles.
- Design questions: sketch metadata repository schema and explain fields for lineage and operational auditing.
- Algorithm traces: show step-by-step incremental load or SCD handling on small tables.
What a full-marks answer includes:
- Clear definitions with bold terms (technical/business/operational metadata).
- Correct diagram with boxes and arrows labelled as in the Key ideas diagram description.
- Correct formulas and units for throughput and rates and a numerical example with units shown and final answer boxed or bolded.
- For design questions: primary keys, foreign keys, versioning, and operational logging details.
Quick check
- What are the three main types of metadata used in ETL?
- Give one advantage of log-based CDC over timestamp-based CDC.
- If you must load 10,000,000 records in 2 hours, what is the required throughput in records per second?
Answers: Technical, business, operational; lower impact on source and more reliable for transactional changes (log-based CDC); 10,000,000 / (2*3600) = 1388.89 records·s^-1.
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?