Ingestion
ETL / ELT, Azure Data Factory, and real-time data flows.
I’m Gino Grimaldos. I design scalable data architectures and pipelines, from ingestion to analytics, connecting technology with business goals.
Lakehouse architectures, batch and stream processing, and engineering practices built for production.
ETL / ELT, Azure Data Factory, and real-time data flows.
Python, SQL, and PySpark for data processing and modeling.
Databricks, Delta Lake, Microsoft Fabric, and Medallion Architecture.
Orchestration, CI/CD, and datasets ready for analytics and reporting.
Education and experience in the order they happened: end-to-end solutions across the public and private sectors, working with international teams. Scroll to follow the hops, and select a stone for the full story.
Start here
An engineering foundation.
From machines to automated processes.
Data consulting for the public sector from Duitama, Boyacá: I built the data foundations behind municipal programs.
Data engineering for a U.S.-based retail company, working remotely from Colombia with international teams.
Where I am now: senior data engineering at a U.S.-based consulting firm, remote from Colombia.
Duitama, Boyacá, Colombia · Experience with international teams.
My study material and questions worth coming back to. One topic at a time.
We can learn together. If you have a question or suggestion, let me know.
Send feedbackHow to turn data into meaningful insights.
Leading people and building with AI agents.
Share a question or suggest something to add to the Study Desk.
Data architecture is the blueprint for how data flows through an organization. It connects the way data is organized, the platforms where it lives, and the processes that move and transform it. A good blueprint starts from requirements and describes the failure path as carefully as the happy path.
Collect orders from a storefront and payments from a provider, then serve a daily sales report.
Storefront order events, payment-provider updates plus a daily settlement file, ERP product catalog
Finance (daily net sales), merchandising analysts (ad hoc queries), operations dashboard
About 50,000 orders per day, with campaign peaks of up to 10×
Report ready by 07:00 local time, covering the previous business day
RPO of one hour, RTO of four hours (same targets used in Storage & Operations)
Raw data kept 13 months so a full year can be replayed and compared
Write the answers down before drawing boxes. Each answer removes options.
| Question | Example answer | What it decides |
|---|---|---|
| Who uses the output, and for which decision? | Finance closes the day; analysts explore | Serving model, access rules |
| How much data, and how fast does it grow? | 50k orders/day, 10× peaks | Storage layout, compute sizing |
| How fresh must it be? | Next morning by 07:00 | Batch is enough; no streaming yet |
| How available must it be? | Report reachable during business hours | Redundancy level, support hours |
| How much data can we lose, and how long can we be down? | RPO 1 h, RTO 4 h | Backup frequency, replay design |
| What is sensitive? | Customer contact details, payment references | Classification, masking, separation |
| What does it cost to be wrong or late? | A wrong close delays reporting | Quality gates, reconciliation |
Functional requirements say what the system does. Non-functional requirements (latency, availability, recovery, security, cost) say how well it must do it. Most architecture trade-offs live in the second group.
How data is organized: entities, relationships, and schemas.
Where data lives: operational databases, data lakes, warehouses, and lakehouses.
How data is ingested, transformed, delivered, and maintained.
Which decisions and use cases the architecture supports, and who consumes its outputs.
The Data Architect connects business needs to a coherent design and documents the important trade-offs. Different architecture views serve different audiences.
Audience: executives and business stakeholders. Describe high-level domains, major data flows, and key use cases.
Audience: data architects and solution designers. Describe entities, relationships, logical zones, and integration patterns without committing to every technology.
Complementary detail: map the logical design to concrete services, tables, file formats, deployment settings, and infrastructure.
Domain owners clarify meaning; engineers implement and operate flows; security and governance teams help define controls. Agree on responsibilities rather than assuming a title owns everything.
| View | Audience | Answers | In the example |
|---|---|---|---|
| Conceptual | Executives, business stakeholders | What and why | Orders and payments feed a trusted daily sales report |
| Logical | Architects, solution designers | How it fits together | Zones (raw, curated, serving), entities, batch ingestion, quality gate, semantic layer |
| Physical | Engineers, operators | How it is implemented | Object storage paths, table format, scheduler, warehouse or lakehouse engine, regions |
The same box can appear in all three views with a different level of detail. A change in the physical view (a new engine) should not force a change in the conceptual view.
These are not mutually exclusive categories. A lakehouse can serve domain-owned data products, and its ingestion can use batch or streaming.
A tightly coupled solution with components managed together. It can simplify an initial deployment but can make independent changes and scaling harder.
Curated, structured data optimized for analytical queries and reporting. ETL and incremental loading are common practices, not exclusive requirements.
Storage for diverse data in its original or lightly processed form. Cataloging, quality, and governance are still necessary to keep it useful.
Combines lake-oriented storage with table management and analytical capabilities. Medallion organizes quality layers such as Bronze, Silver, and Gold; it is one design pattern.
A domain-oriented approach: domain teams own data products under shared standards and governance. Data contracts describe expectations between producers and consumers.
Separate batch and speed-processing paths feed a serving layer. It supports historical recomputation and low-latency results, at the cost of maintaining two paths.
A stream-processing path handles events; retained events can be replayed for reprocessing. This reduces duplicated processing paths but makes replay and event retention important.
Read further: Microsoft · Batch, streaming, Lambda & Kappa ↗ · Microsoft · Domain-oriented data mesh ↗
| Data warehouse | Data lake | Lakehouse | |
|---|---|---|---|
| Stores | Structured, modeled tables | Files of any shape, raw or lightly processed | Files in open formats plus a table layer |
| Schema | Defined before loading | Applied when reading | Enforced by the table format, can evolve |
| Strength | Fast, governed SQL analytics | Cheap, flexible storage for any data | One copy for BI, data science, and streaming |
| Risk | Rigid for semi-structured data; cost of compute | Becomes a "swamp" without catalog and quality | More moving parts to operate and tune |
| Fits the example when | Only the sales report matters | Raw events must be kept for replay | Both are needed, which is the case here |
These are not exclusive choices. A lakehouse can feed a warehouse-style serving layer, and a Data Mesh can be built on either.
Complexity and cost grow from left to right
| Latency need | Pattern | Example |
|---|---|---|
| Hours to a day | Scheduled batch | The 07:00 sales report |
| Minutes | Micro-batch or incremental loads | Operations dashboard refreshed every 15 minutes |
| Seconds | Stream processing | Fraud signal on a payment event |
Raw data is immutable and every step is idempotent, so any table can be rebuilt
| Stage | Happy path | What can fail | Recovery path |
|---|---|---|---|
| Ingestion | Land order events and the settlement file in the raw zone, unchanged | Source is late, file arrives twice, schema changes | Idempotent landing by file name and checksum; alert on missing file; quarantine unknown schema |
| Storage | Raw data is immutable and partitioned by ingestion date | Accidental delete or overwrite | Versioning or soft delete; restore to a point in time |
| Transformation | Validate, deduplicate, and merge into curated tables | Bad records, job crash midway | Atomic writes, checkpoints, quarantine table, rerun from the last good checkpoint |
| Serving | Publish the reporting table after the quality gate passes | Gate fails, totals do not reconcile | Keep yesterday's version visible, flag the delay, fix and replay |
| Consumption | Dashboard reads the published table | Stale or partial data shown | Freshness stamp on the report; publish only complete days |
Replay rule: because raw data is immutable and every step is idempotent, any downstream table can be rebuilt from raw. That is the property that makes the RPO and RTO achievable.
Prioritize service availability, potentially returning incomplete or stale data according to the system’s guarantees. It is not a promise that every failure can be hidden.
Replica state can change over time as updates propagate, even without a new request from the local client.
If new updates stop and propagation succeeds, replicas converge. Conflict resolution and the time to convergence still matter.
BASE is often associated with distributed NoSQL designs. NoSQL does not automatically mean BASE, and systems may offer different guarantees for different operations.
Reference: Infinispan · BASE ↗
In CAP, consistency means linearizability: operations behave as if there were one current copy, respecting real-time order.
Every request received by a non-failing node eventually gets a successful response. This is more specific than a general uptime percentage.
The network can lose or delay communication between groups of nodes. The system must have defined behavior in that condition.
Reject or delay some operations to preserve consistency.
Serve operations while accepting that different sides may temporarily disagree.
A system cannot guarantee both linearizable consistency and availability during a partition. It may reject or delay some operations to preserve consistency, or serve operations while accepting that different sides may temporarily disagree.
“Pick any two” is an incomplete shortcut: a distributed deployment cannot simply assume that network partitions will never occur.
Reference: IBM · CAP theorem ↗
The transaction is all-or-nothing: its changes are committed together or rolled back.
A valid transaction preserves the database’s defined constraints and invariants. Application logic must enforce the business rules not expressed as database constraints.
Controls how concurrent transactions interact and what intermediate changes they can observe. Guarantees depend on the isolation level; not all levels behave like serial execution.
After a successful commit, changes survive failures covered by the database’s durability configuration and guarantees.
Debit one account and credit the other in one transaction. Atomicity prevents a partial transfer; constraints and correct logic protect invariants; isolation controls concurrent transfers; durability preserves a committed result.
References: PostgreSQL · ACID terminology ↗ · PostgreSQL · Isolation levels ↗
CAP concerns the observable behavior of distributed reads and writes. ACID consistency concerns valid database states and invariants. ACID transactions can exist inside a distributed architecture with weaker cross-replica guarantees.
Yes. Accepting an order may require strong transaction guarantees to protect inventory and payment rules. An analytical dashboard may tolerate a documented freshness delay. Define the guarantees per operation and consumer.
An architecture decision record (ADR) is one page: context, options, decision, consequences.
ADR-001 · Batch first for the daily sales report Status: accepted Context: Finance needs net sales by 07:00. No decision depends on data fresher than one day. Options: (a) nightly batch, (b) micro-batch every 15 min, (c) streaming. Decision: (a) nightly batch with an idempotent, replayable pipeline. Consequences: simpler tests and reconciliation; the report cannot show intraday sales. Revisit when: a consumer needs freshness under one hour.
The table lists a typical service per layer, with one trade-off each.
| Layer | Azure | AWS | Google Cloud | Databricks / open source |
|---|---|---|---|---|
| Ingestion and orchestration | Data Factory, Fabric pipelines | Glue, Step Functions | Dataflow, Cloud Composer | Lakeflow Jobs, Apache Airflow |
| Storage | Data Lake Storage Gen2, OneLake | S3 | Cloud Storage | Delta Lake, Apache Iceberg |
| Processing | Fabric or Synapse Spark, Azure Databricks | EMR, Glue, Athena | Dataproc, BigQuery | Apache Spark, dbt |
| Serving | Fabric Warehouse, Power BI | Redshift, QuickSight | BigQuery, Looker | Databricks SQL |
Tight integration with Entra ID and Power BI; several overlapping services (Synapse, Fabric, Databricks), so pick one deliberately.
Many composable building blocks; more assembly and IAM design needed.
BigQuery removes most infrastructure work; the pricing model (on-demand scans versus reserved capacity) must be understood early.
Open table formats avoid lock-in at the storage layer; you operate and tune more yourself.
Draw a source-to-dashboard diagram and write one decision record explaining a trade-off.
Almost everything downstream of ingestion. Files become events or change data capture; the nightly job becomes a continuous or micro-batch job with checkpoints; late and out-of-order events need explicit rules; the quality gate must run incrementally; serving needs an engine that handles frequent small updates; monitoring moves from "did the job finish?" to lag and freshness. Cost and operational effort go up, so first confirm which decision really needs five-minute data.
Read further: Microsoft · Big data architectures (batch, streaming, Lambda, Kappa) ↗ · Microsoft · Azure Architecture Center data guide ↗ · AWS · Well-Architected Framework ↗ · Google Cloud · Architecture Framework ↗ · Databricks · Medallion architecture ↗ · DAMA · DMBOK ↗
Data modeling defines and structures the data that a system stores, processes, and retrieves. It turns business concepts into explicit entities, relationships, attributes, and rules. Every model starts with one sentence: "one row in this table is one …".
Model customers, orders, order lines, and products for transactional and analytical workloads.
| Entity | Business key | One row is | Notes |
|---|---|---|---|
| Customer | customer_id | One person or company that buys | Segment and address change over time |
| Product | product_id (SKU) | One sellable item | Belongs to a category |
| Order | order_id | One checkout | Has a customer, a date, a status |
| Order line | order_id + line_number | One product within one order | Quantity, unit price, discount |
| Payment | payment_id | One payment attempt for an order | An order can have several |
Cardinality: a customer places zero or many orders; an order has one or many lines; a product appears in zero or many lines; an order has zero or many payments.
1 : N reads "one to many" from the first entity to the second
A thing the business needs to describe (customer, order).
What exactly one row represents. Declare it before adding columns.
The column or columns that identify one row. Foreign key: a reference to another entity's key.
The business key comes from the source (customer_id); a surrogate key is generated by the warehouse (customer_key) so that one customer can have several historical versions.
One-to-one, one-to-many, many-to-many. Many-to-many needs a bridge table (order lines bridge orders and products).
The business view: key entities and their relationships. Example: customers place orders containing products.
The structure independent of a particular database: attributes, identifiers, cardinality, and business constraints.
The implementation: tables, column types, keys, indexes, partitioning, clustering, and storage format. Available techniques depend on the platform.
State exactly what one row represents before defining measurements or joins. Keep that grain consistent throughout a fact table.
Separate related entities into tables with explicit keys. Normal forms help reduce duplication and update anomalies; this is common in transactional systems.
Organize analytical data into facts and dimensions. Star and snowflake schemas make business measurements and their descriptive context explicit.
Separate business keys, relationships, and changing descriptive attributes. It supports historization and traceability across evolving sources, often with downstream analytical models.
Bring attributes together to reduce joins for selected workloads. This can simplify reads but increases duplication and the work needed to maintain consistent updates.
| Normalized (3NF) | Dimensional (star) | |
|---|---|---|
| Purpose | Record transactions correctly | Answer analytical questions quickly |
| Optimized for | Many small writes, no update anomalies | Large reads, aggregations, filters |
| Shape | Many narrow tables, strict keys | One fact table surrounded by dimensions |
| Redundancy | Minimal | Accepted in dimensions |
| History | Usually current state only | Kept explicitly (SCD, snapshots) |
| In the example | customers, orders, order_lines, products, payments | fact_sales with dim_date, dim_product, dim_customer |
Use a relational structure with no repeating groups and values appropriate to each attribute’s domain.
In 1NF, each non-key attribute depends on the whole candidate key, rather than only part of a composite key.
For each non-trivial dependency X → A, X is a superkey or A is a prime attribute. In common designs, move non-key attributes that depend on other non-key attributes into their own entity.
Use it to control redundancy and anomalies. Denormalize deliberately when a measured read workload justifies the maintenance trade-off.
Simplified example — satellites can describe either a hub or a link
Identify core business concepts using unique business keys, with supporting load and source metadata.
Capture associations between business concepts represented by hubs.
Store descriptive attributes and their changing versions, associated with a hub or link.
Customer and Order hubs hold their business identities. A Customer–Order link records their association; satellites preserve relevant descriptive history.
Reference: AutomateDV · Data Vault building blocks ↗
Facts represent events or observations at a declared grain, usually with foreign keys and measures. Some fact tables contain no numeric measures. Dimensions provide descriptive context.
Illustrative structure · Confirm keys, grain, and cardinality in the real model
Dimensions connect directly to the fact and commonly contain denormalized attributes. This is often easier to navigate in BI tools and needs fewer joins.
Repeated dimension attributes can use more space and require careful updates. Faster queries are a possibility to measure, not an automatic guarantee.
Normalize a dimension into related tables, such as Product → Category. Shared attributes are stored separately, reducing some redundancy.
Additional joins and tables can make queries and the semantic model harder to navigate. Integrity still depends on implemented rules and maintenance.
Reference: Microsoft · Star and snowflake dimensions ↗
fact_sales, grain: one order line.
| Column | Type | Role |
|---|---|---|
date_key | FK → dim_date | Order date |
product_key | FK → dim_product | Product sold |
customer_key | FK → dim_customer | Customer version valid at the order date |
order_id, line_number | Degenerate dimension | Traceability back to the source |
quantity | Additive measure | Units sold |
line_amount | Additive measure | Quantity × unit price − line discount |
dim_date: one row per calendar day; year, quarter, month, week, weekday, holiday flag. Built once, never from the fact.dim_product: one row per product version; name, category, brand.dim_customer: one row per customer version; segment, city, country, validity dates.Measure types: additive (quantity, amount) can be summed across every dimension; semi-additive (stock on hand) cannot be summed across time; non-additive (unit price, ratios) must be recomputed from their parts.
Sales by segment stay correct for both periods
| Type | What it does | Use when |
|---|---|---|
| Type 1 | Overwrites the attribute | History has no analytical value (fixing a typo) |
| Type 2 | Closes the current row and inserts a new version | Reports must show the value valid at the time of the event |
| Type 3 | Keeps "previous value" in an extra column | Only one prior value matters |
Type 2 example, customer 42 moves from the Retail segment to Wholesale on 2025-03-01:
| customer_key | customer_id | segment | valid_from | valid_to | is_current |
|---|---|---|---|---|---|
| 1001 | 42 | Retail | 2023-06-10 | 2025-02-28 | false |
| 1873 | 42 | Wholesale | 2025-03-01 | 9999-12-31 | true |
A sale on 2025-02-15 points to key 1001; a sale on 2025-03-05 points to key 1873. Sales by segment stay correct for both periods.
Joining orders to both order_lines and payments multiplies rows: an order with 3 lines and 2 payments becomes 6 rows, so a sum of line amounts doubles and a sum of payment amounts triples.
-- Wrong: lines × payments SELECT o.order_id, SUM(l.line_amount) AS sales, SUM(p.amount) AS paid FROM orders o JOIN order_lines l ON l.order_id = o.order_id JOIN payments p ON p.order_id = o.order_id GROUP BY o.order_id; -- Right: aggregate each child to the order grain first WITH line_totals AS ( SELECT order_id, SUM(line_amount) AS sales FROM order_lines GROUP BY order_id ), payment_totals AS ( SELECT order_id, SUM(amount) AS paid FROM payments GROUP BY order_id ) SELECT o.order_id, lt.sales, pt.paid FROM orders o LEFT JOIN line_totals lt ON lt.order_id = o.order_id LEFT JOIN payment_totals pt ON pt.order_id = o.order_id;
Rule: bring every table to the same grain before joining, and check row counts before and after each join.
Divide data by selected column values to support pruning and management. Avoid creating many tiny partitions; choose from measured filters, data volume, and file sizes.
Co-locate related values to improve data skipping for appropriate queries. Z-Ordering is one Delta layout approach, not a universal default.
A Databricks layout option that replaces Hive-style partitioning and Z-Ordering on enabled tables. Evaluate compatibility and workload requirements before choosing it.
ACID transactions, schema controls, and version history support reliable tables. Enable schema evolution intentionally and align time travel expectations with retention.
Raw, validated, and business-ready layers may use different models. A storage format does not remove the need for grain, keys, and clear business definitions.
Inspect representative query plans, scan volume, latency, and maintenance cost. Compare before and after rather than assuming a layout is faster.
Do not partition a large table by a high-cardinality column by default. Many distinct values can create too many small partitions. A date column can be useful at an appropriate granularity and scale, but it is not automatically the right choice.
Reference: Databricks · Liquid clustering ↗
Agree on KPIs, entities, and workflows before picking tables.
Design for access patterns as well as storage requirements.
Reduce anomalies where needed; optimize repeated reads deliberately.
Maintain ER diagrams, definitions, metadata, and a data catalog.
Version schema changes and consider downstream compatibility.
Choose layouts using evidence; avoid high-cardinality partitioning as a blanket rule.
Identify sensitive data, control access, and document lineage.
Separate warehouse row identity from source business keys, especially for historical dimension versions. Benchmark performance; surrogate keys alone do not guarantee faster joins.
Use representative query shapes, distributions, volumes, and concurrency. Protect sensitive data in test environments.
Version-control models and test schemas, uniqueness, relationships, and transformations.
Grain: one order line. Dimension keys: CustomerKey, ProductKey, DateKey. Measures: Quantity and LineAmount. Keep an order identifier for traceability, but do not repeat an order-level total and sum it across lines.
For customer history, Type 1 overwrites an attribute. Type 2 creates a new dimension version and retains the old one. A sales fact should reference the version relevant to its business event.
A one-to-many join can repeat a fact row. Check key uniqueness, cardinality, and grain before aggregating; a physically faster join still gives the wrong answer if the model is incorrect.
Fabric Warehouse or Lakehouse with a Power BI semantic model. Star schemas are the recommended shape for Power BI; the semantic model adds another layer to keep in sync.
Redshift. Distribution and sort keys must be chosen from real query patterns; the wrong choice makes joins move data between nodes.
BigQuery. Nested and repeated fields can replace some joins (order with its lines in one row); that changes the grain and surprises people who expect flat tables.
Delta Lake tables modeled with dbt. Models, tests, and documentation live in version control; primary and foreign keys are informational in most lakehouse engines, so uniqueness must be tested.
dbt documentation ↗Kimball dimensional modeling techniques ↗
Draw the ER diagram and write a query that calculates sales without double counting.
fact_sales.line_amount in the source, before and after the segment change.Lines and payments are two independent one-to-many children of the order. Joining both at once produces every combination of line and payment, so each amount is repeated. Aggregate each child to the order grain first, or query them separately. A faster join does not help: the model is wrong, not slow.
Read further: Kimball Group · Dimensional modeling techniques ↗ · Microsoft · Star schema and Power BI ↗ · Databricks · Liquid clustering ↗ · AutomateDV · Data Vault building blocks ↗
Storage decisions include the data model, access patterns, retention, performance, and recovery. Operations keeps those decisions working over time. A storage design is finished only when a restore has been tested.
Land daily order files, retain raw data for replay, and publish a curated table for analysis.
raw/orders/ingest_date=2025-03-05/orders_20250305_001.json.gz immutable, kept 13 months raw/payments/ingest_date=2025-03-05/settlement_20250305.csv immutable, kept 13 months curated/orders/ table format, partitioned by order_date (month) curated/payments/ table format serving/daily_sales/ small reporting table, rebuilt or merged each day
Illustrative targets: RPO of one hour, RTO of four hours.
Relational stores suit structured relationships and transactions. Document, key-value, graph, and analytical stores serve different access patterns.
Object storage holds objects such as files. A table layer adds table semantics; storage alone does not provide a complete database experience.
RPO is the target maximum acceptable data loss measured in time. RTO is the target time to restore service after disruption.
Monitor failed jobs, freshness, capacity, query latency, and cost. Attach an owner and response procedure to important alerts.
| Object storage | File storage | Block storage | |
|---|---|---|---|
| Unit | Object with a key and metadata | File in a directory tree | Fixed-size blocks on a volume |
| Access | HTTP API; read or replace whole objects | Shared file system protocols (NFS, SMB) | Attached to one machine as a disk |
| Strength | Cheap, durable, effectively unlimited | Familiar paths, shared access | Low latency, random reads and writes |
| Weakness | No in-place edits; listing many objects is slow | Scales less; costs more per GB | Tied to a server; you manage the file system |
| Typical use | Data lakes, backups, raw landing | Legacy apps, shared team folders | Databases, virtual machine disks |
| In the example | Raw and curated zones | Not needed | Under the operational order database |
The log gives atomic commits, schema checks and time travel
| Layer | Examples | What it gives |
|---|---|---|
| File format, row-based | CSV, JSON, Avro | Easy to write and exchange; Avro carries a schema |
| File format, columnar | Parquet, ORC | Compression, reading only needed columns, statistics for skipping |
| Table format | Delta Lake, Apache Iceberg, Apache Hudi | A transaction log over many files: ACID commits, schema enforcement and evolution, time travel, safe concurrent writers |
A folder of Parquet files is not a table: two writers can corrupt it and readers can see half a write. A table format adds the log that says which files belong to the current version.
Eleven partitions are skipped without opening a file
| Zone | Example rule | Reason |
|---|---|---|
| Raw | Hot 90 days, cool tier until 13 months, then delete | Replay window and year-over-year comparison |
| Curated | Keep table history 30 days; vacuum older versions | Time travel for debugging without unbounded cost |
| Serving | Keep current plus daily snapshots for 13 months | Reproduce a published report |
| Logs and audit | Per policy | Compliance evidence |
Lifecycle policies move or delete objects automatically. Deleting old table versions shortens time travel, so align vacuum settings with the recovery targets. Personal data may have a legal deletion deadline that overrides "keep everything".
Illustrative targets from the example below: RPO of one hour, RTO of four hours
Suppose finance accepts at most one hour of data loss and four hours to recover. Those illustrative targets become RPO and RTO inputs; backup frequency and restore drills must be designed and tested against them.
RPO is the maximum acceptable data loss, measured in time. RTO is the target time to restore service.
| Mechanism | Protects against | Does not protect against |
|---|---|---|
| Replication (zones, regions) | Hardware or site failure | A bad write or delete, which is replicated too |
| Versioning and soft delete | Accidental overwrite or delete | Loss of the whole account or region |
| Table time travel | A bad pipeline run | Vacuumed versions; storage loss |
| Backups to a separate location | Most of the above | An untested restore procedure |
A restore test has four steps: restore to a separate location, validate row counts and checksums against a known value, measure the elapsed time against the RTO, and write down what was slow or manual.
Compare object storage options for the same workload. Look at access, retention, recovery, and cost drivers.
| AWS · Amazon S3 | Azure · Blob Storage / Data Lake Storage Gen2 | Google Cloud · Cloud Storage | |
|---|---|---|---|
| Namespace | Flat keys; prefixes act like folders | Hierarchical namespace available (real directories, atomic rename) | Flat by default; hierarchical namespace optional |
| Access control | IAM policies, bucket policies | Entra ID roles plus POSIX-style ACLs | IAM, uniform bucket-level access |
| Tiers | Standard, Infrequent Access, Glacier classes | Hot, cool, cold, archive | Standard, Nearline, Coldline, Archive |
| Recovery features | Versioning, replication, Object Lock | Soft delete, versioning, immutable storage | Versioning, soft delete, retention policies |
| Trade-off to mention | Many storage classes and request charges to model | Hierarchical namespace helps analytics; some Blob features behave differently with it | Colder classes have minimum storage durations and retrieval fees |
Sketch raw and curated layouts. Restore a sample file or table version and record the result.
Almost always on a large table. Customer ID has very high cardinality, so the table breaks into a huge number of tiny partitions and files. Listing and opening them costs more than reading the data; compaction and vacuum take longer; and any query that does not filter on one customer scans everything anyway. Partition by a low-cardinality column that queries filter on, such as month, and use clustering for customer ID.
No. Restore the data, validate its correctness, and measure recovery time. Backup completion alone does not prove that the recovery target is achievable.
Read further: Microsoft · Understand data store models ↗ · Microsoft · Disaster recovery ↗ · Delta Lake documentation ↗ · Apache Iceberg documentation ↗
Integration connects producers to consumers. Define the contract: schema, identifiers, freshness, delivery behavior, and how failures or changes are handled. Assume every message can arrive late, twice, or out of order, and design so the result is still correct.
Ingest order events and payment updates, including duplicates and late arrivals.
| Stream | Key | Event time | Typical trouble |
|---|---|---|---|
Order events (created, updated, cancelled) | order_id + event_id | occurred_at | Retries deliver the same event twice |
Payment updates (authorized, captured, refunded) | payment_id + event_id | occurred_at | A payment can arrive before its order |
| Daily settlement file | File name + checksum | Settlement date | File re-sent after a correction |
ETL transforms before loading the target. ELT loads first, then transforms using the target platform’s capabilities.
A full load reads the complete scope. An incremental load processes changes, using a watermark or another reliable change signal.
Change data capture tracks source changes. Streaming processes arriving events. They can work together, but they are not synonyms.
Idempotent processing produces the same intended result when repeated. Use stable keys and explicit duplicate handling.
Extract new or updated order lines using a reliable change signal. Land the raw records, validate keys and types, then merge into the curated table. Track the successful checkpoint so failed batches can be replayed safely.
| Pattern | How it works | Good for | Watch out for |
|---|---|---|---|
| ETL | Transform before loading the target | Strict targets, sensitive data removed early | Logic hidden in the integration tool |
| ELT | Load raw, transform inside the platform | Lakehouses and warehouses with cheap compute | Raw zone needs governance |
| Batch files | Source exports a file on a schedule | Simple, auditable exchanges | Late, partial, or duplicate files |
| API pull | Consumer calls the source API | SaaS sources | Rate limits, pagination, missed deletes |
| Change data capture (CDC) | Read the database log of inserts, updates, deletes | Replicating operational databases | Schema changes, initial snapshot, log retention |
| Event stream | Producer publishes events to a log or topic | Many consumers, low latency | Ordering, duplicates, replay rules |
Interoperability is the second half of the title: agreeing on formats (JSON, Avro, Parquet), identifiers, time zones, and code lists so that two systems mean the same thing by the same field.
A contract is the written agreement between a producer and its consumers.
contract: order_events
owner: storefront-team
version: 2.1.0
delivery: at-least-once, ordered per order_id
freshness: 95% of events available within 5 minutes
schema:
event_id: {type: string, required: true, unique: true}
order_id: {type: string, required: true}
event_type: {type: string, enum: [created, updated, cancelled]}
occurred_at: {type: timestamp, timezone: UTC, required: true}
amount: {type: decimal(12,2), unit: order currency}
currency: {type: string, standard: ISO 4217}
change_policy: additive changes are minor versions; removals or meaning changes need a new major version and 30 days' noticeMinimum content: owner, schema with types, keys, event time and time zone, delivery guarantee, freshness target, and how changes are announced.
| Guarantee | Meaning | Consequence |
|---|---|---|
| At-most-once | Never duplicated, may be lost | Rarely acceptable for sales data |
| At-least-once | Never lost, may be duplicated | The normal case; consumers must deduplicate |
| Exactly-once (effectively) | At-least-once delivery plus idempotent processing | The result is the same as if each event were processed once |
An idempotent write gives the same result when repeated. The usual tool is an upsert keyed on a stable identifier:
MERGE INTO curated.order_events AS t USING staged_batch AS s ON t.event_id = s.event_id WHEN NOT MATCHED THEN INSERT *;
Plain INSERT is not idempotent; a retry doubles the rows. For files, record the file name and checksum in a load log and skip files already loaded.
Transient failures with exponential backoff and a limit. Do not retry bad data forever.
(or watermark, or offset): the position up to which processing succeeded. Advance it only after the write commits.
Reset the checkpoint, or reprocess a date range from the raw zone. Safe only because writes are idempotent.
A planned replay over history after a logic change. Run it with the same code path as the daily job.
Business logic uses event time; monitoring uses processing time
Records that fail parsing or validation go to a separate table with the reason, the raw payload, and the batch ID. The good records continue. Someone owns the quarantine and reviews it.
If more than an agreed share of a batch is bad, stop the batch instead of publishing a partial result.
| Schema change | Compatible? | Handling |
|---|---|---|
| New optional field | Yes | Accept; add it downstream when needed |
| Field removed or renamed | No | New contract version; run both versions in parallel during migration |
| Type narrowed (string → integer) | No | Same as above |
| Meaning changed, same name (amount now includes tax) | No, and invisible to schema checks | Announce, version, and add a reconciliation test |
A schema registry or contract test in the producer's CI catches the first three before deployment. The fourth needs communication and reconciliation.
Compare what the source says it sent with what was delivered: row counts, sum of amounts, and count of distinct keys per day. Reconciliation is the test that catches silent loss and silent duplication.
Data Factory or Fabric pipelines for batch and API pulls; Event Hubs for streams. Many connectors and little code; complex logic is harder to test in a visual pipeline.
Glue for batch, DMS for change data capture, Kinesis Data Streams for events. Each piece is focused; you assemble and monitor the chain yourself. ·
Pub/Sub for events, Dataflow for processing, Datastream for change data capture. One model for batch and streaming (Apache Beam); the model takes time to learn. ·
Apache Kafka for the event log, Debezium for change data capture, Airflow for orchestration, Auto Loader and Structured Streaming on Databricks. Portable and widely known; you run the clusters or pay for a managed service. ·
Replay the same batch twice and demonstrate that downstream totals stay stable.
event_id and 1 record with a missing order_id.event_id; send the invalid record to a quarantine table with a reason.Nothing should be lost and nothing should fail. The payment is stored with its order_id and marked as unmatched. Each run retries the match; when the order arrives, the relationship resolves and the totals update. If it stays unmatched past the agreed window, an alert goes to the owner. Rejecting the payment, or dropping it in an inner join, silently understates the amount paid.
No. Source delay, processing time, retries, and downstream refreshes all contribute to end-to-end freshness.
Read further: Microsoft · ETL and ELT ↗ · Apache Kafka documentation ↗ · Debezium documentation ↗
Data governance combines people, processes, policies, and supporting technology to guide how data is defined, protected, made available, and used in accordance with organizational requirements. Tools help, but governance starts with a named person who can say yes or no.
Publish a trusted sales dataset while restricting access to customer details.
sales.daily_net_sales and the tables behind it.
net sales = gross sales − discounts − returns and refunds, excluding tax. (Definition used across the Study Desk example.)
Customer name, email, phone, and address.
Sets the desired outcome: for example, trusted financial reporting with clear ownership.
States the expectation: customer contact details are restricted to approved business purposes.
Turns the expectation into a check or control: mask email values for the general analyst group.
Shows whether controls work: access reviews, audit records, quality results, and resolved issues.
Strategy sets the outcome, policy states the expectation, a rule turns it into a control, and evidence shows whether the control works.
Finance reports come from one trusted sales dataset
Customer contact details are restricted to approved business purposes
The analyst group sees email masked; only the CRM service role sees it in clear
Quarterly access review; audit log of queries on the unmasked column
Illustrative responsibilities — agree the exact roles within your organization
Approves definitions, access principles, priorities, and acceptable risk for the domain.
Maintains definitions and quality rules; coordinates issues and adoption with producers and consumers.
Implements and operates access, storage, logging, and other technical controls.
Defines the scope, decision process, escalation path, and how success will be measured.
| Role | Responsibility | In the example |
|---|---|---|
| Data owner | Accountable for definitions, access principles, and acceptable risk | Finance lead |
| Data steward | Maintains definitions and quality rules; coordinates issues | Revenue steward |
| Data custodian | Implements and operates technical controls | Platform engineer |
| Data producer | Delivers data that meets the contract | Storefront and payments teams |
| Data consumer | Uses data for the approved purpose; reports problems | Analysts, dashboard users |
| Governance council | Resolves cross-domain disagreements; approves policies | Owners plus security and legal |
Ownership matrix (RACI) for the sales dataset — R: responsible, A: accountable, C: consulted, I: informed.
| Decision or task | Owner | Steward | Custodian | Producer | Consumer |
|---|---|---|---|---|---|
| Approve the definition of net sales | A | R | I | C | C |
| Approve an access request | A | R | I | – | I |
| Implement masking and grants | I | C | A/R | – | – |
| Fix a failed quality rule at the source | I | C | C | A/R | I |
| Maintain the glossary and catalog entry | A | R | C | C | I |
| Report a suspected data issue | I | A | C | C | R |
| Policy | Must answer | Example |
|---|---|---|
| Classification | Which levels exist and how is each handled? | Public, Internal, Confidential, Restricted (personal data) |
| Access | Who may see what, for which purpose, approved by whom? | Aggregates: all analysts. Customer details: approved roles only |
| Retention | How long is data kept, and how is it deleted? | Raw orders 13 months; customer data deleted on a verified request |
| Quality | Which rules must pass before publishing? | Completeness of order_id; reconciliation within tolerance |
| Exceptions | Who can grant one, for how long, and where is it recorded? | Owner approves; expires in 90 days; logged in the catalog |
A policy without an exception process gets bypassed. A policy without an owner gets ignored.
Owners and stewards connect policy decisions to operational evidence
Document definitions, ownership, classifications, lineage, and usage. A catalog helps people discover and understand assets.
Define relevant checks, thresholds, owners, and a response to failures. Examples include completeness, uniqueness, validity, and freshness.
RBAC grants privileges through roles; ACLs associate permissions with resources. Audit records provide evidence of attempted or completed actions.
Define retention, archival, and deletion rules with the relevant stakeholders. Verify that implementation matches the approved policy.
Agreed terms and definitions (net sales, active customer). One definition per term, with an owner.
Inventory of datasets with owner, description, schema, classification, and freshness.
Where a dataset came from and what depends on it.
Together they make policies discoverable (people can find the approved dataset) and traceable (an auditor can follow net sales back to its sources). Details are in Metadata Management.
Keep records of grants, policy changes, and access to restricted data. Review them on a schedule, not only after an incident.
Every data issue gets an owner, a severity, and a target date. Track it to closure.
Steward → owner → governance council. Use it when two domains disagree or when a risk exceeds what the owner can accept.
Supports enterprise discovery, metadata, and governance workflows. Validate supported scan and lineage capabilities for each source.
Controls access to Databricks data and AI assets, and captures supported lineage and audit information. Its metastore organizes top-level governance objects.
Identity and group management; formerly called Azure Active Directory. Align identities and groups with the platform’s access model.
Evaluates and can enforce supported Azure resource configuration requirements. It does not replace table-level data permissions.
Stores and controls access to secrets, keys, and certificates. Keep credentials out of notebooks and repositories.
Collect and analyze configured telemetry. Define alerts, ownership, and investigation procedures.
Use supported pipeline expectations or data-flow checks. Choose whether failed records are rejected, quarantined, or block delivery.
Azure Blueprints is on a retirement path. For new implementations, evaluate deployment stacks, template specs, and Policy for the relevant requirements.
A catalog entry does not grant data access. Azure resource permissions, Databricks object privileges, and data-level filters or masks must each be configured and tested where they apply.
References: Entra ID naming ↗ · Blueprint migration ↗
Use supported grants, row filters, column masks, or governed tag-based policies to control what a consumer can query.
Review effective permissions across Unity Catalog’s hierarchy. A higher-level grant can affect child objects; verify scope before granting broadly.
Masking changes what a query returns to a user. Encryption protects encoded data at rest or in transit. They address different threats.
Validation checks protect data delivery; they do not automatically implement every contractual requirement, ownership process, or service objective.
Reference: Unity Catalog access controls ↗
For a defined dataset and window, measure valid records / evaluated records or null required fields / evaluated records. Document exclusions and thresholds.
Track overdue access reviews and unauthorized access attempts. An attempt is not the same as a successful breach.
Measure deliveries meeting the agreed freshness or timing target / eligible deliveries. Specify the period and what counts as on time.
Catalog searches, active consumers, and query activity can indicate adoption. High usage alone does not demonstrate quality or compliance.
Measure eligible assets with verified retention rules or completed deletion actions. Investigate exceptions rather than hiding them in averages.
These are example metric definitions, not measured results from my projects. Choose targets from business requirements, baseline measurements, and risk.
Name owners, stewards, and engineering leads. Agree on scope, decision rights, baseline measures, and realistic targets.
Agree on classification, naming, retention, and access principles. Treat example retention periods as examples, not universal requirements.
Register sources in Purview and configure Unity Catalog. Use the supported connector and validate metadata scope and lineage coverage.
Map approved groups to the required roles and privileges. Test both allowed and denied access with representative identities.
Add validation and expectations to pipelines. Define failure handling, ownership, thresholds, and alert routes.
Verify source-to-consumer lineage on actual flows and configure audit collection. Document gaps: external triggers may not appear in connector lineage.
Teach consumers how to discover approved datasets. Publish a playbook for access requests, issue reporting, and policy proposals.
Review metrics, exceptions, taxonomy, and controls on an agreed cadence. Update them as sources and use cases evolve.
Do not assume that an “Open Metadata API” connection automatically provides complete Purview–Databricks integration. Select the supported Unity Catalog connector and test the documented metadata, lineage, and authentication behavior. Microsoft documents gaps such as an external service triggering a notebook.
The finance owner approves revenue definitions and permitted uses. A steward maintains the glossary and coordinates quality issues. Engineers implement table access, masking, validations, and monitoring. Consumers discover the approved dataset and request access through the agreed process.
No. You also need accountable owners, agreed policies, implemented controls, observed evidence, and a process for addressing exceptions.
Microsoft Purview for catalog, classification, and policies; Entra ID for identities and groups. Broad coverage of Microsoft sources; scan and lineage support varies by source, so test each one.
Lake Formation for permissions on the data lake; Glue Data Catalog; DataZone for a business catalog and access requests. Fine-grained permissions in one place; the interaction with IAM policies needs careful design.
Dataplex Universal Catalog for discovery, classification, lineage, and quality. Integrated with BigQuery; fewer options for sources outside Google Cloud.
Unity Catalog for access, lineage, and audit inside Databricks; DataHub or OpenMetadata as open catalogs. Unity Catalog governs what runs through Databricks, not everything else; open catalogs need hosting and connectors. ·
Draft an ownership matrix and an access request workflow for the sales dataset.
dim_customer and fact_sales.The data owner of the sales domain, because definitions are a business accountability. The steward prepares the options and their impact, and consumers and producers are consulted. If the disagreement crosses domains (finance and marketing each need a different number), it goes to the governance council, and the usual outcome is two clearly named metrics, not one ambiguous one. Engineers implement the decision; they should not make it by choosing a formula in SQL.
Read further: DAMA · DMBOK ↗ · Microsoft Purview documentation ↗ · Unity Catalog access control ↗ · Purview · Unity Catalog connector capabilities ↗
Data security makes sure that the right identities can do the right things with the right data, and that there is evidence of what happened. Governance decides who should have access; security enforces and proves it.
Allow analysts to see sales totals while limiting access to customer contact details.
| Field | Classification | Analysts | Customer service | Pipeline identity |
|---|---|---|---|---|
order_id, line_amount, order_date | Internal | Read | Read | Read and write |
customer_id | Internal (pseudonymous) | Read | Read | Read and write |
customer_name, email, phone | Restricted (personal data) | Masked | Read | Read and write |
card_reference (provider token) | Restricted | No access | No access | Write only |
Card numbers never enter the platform: the payment provider keeps them and returns a token.
| Stage | Sensitive data present | Main threats | Controls |
|---|---|---|---|
| Ingestion | Raw payloads with contact details; API keys | Leaked credentials, interception, over-collection | Secrets in a vault, TLS, collect only needed fields |
| Storage | Raw and curated zones | Public bucket, broad access, stolen disk or backup | Private endpoints, least privilege, encryption at rest |
| Transformation | Data in notebooks, logs, temp tables | Copies in logs or exports, over-privileged jobs | Service identities with narrow scope, no personal data in logs |
| Serving | Dashboards, extracts | Authorized user sees too much; export leaves the platform | Row and column controls, masking, export limits |
| Deletion | Backups, old versions | Data survives after it should be gone | Retention rules that cover copies and versions |
Use the three goals to check coverage: confidentiality (only authorized reads), integrity (no unauthorized change), availability (there when needed).
Proves who you are (single sign-on, multi-factor authentication). Authorization decides what you may do.
Grant the minimum needed, for the shortest time needed.
Grants by role ("analyst can read the sales schema"). ABAC grants by attributes and tags ("anyone in Finance can read columns tagged financial").
(managed identities, service principals, IAM roles) run pipelines. No shared human accounts and no passwords in code.
The person who requests access is not the one who approves it.
| Technique | What it does | Reversible? | Protects against | Does not protect against |
|---|---|---|---|---|
| Encryption in transit (TLS) | Encrypts data on the network | Yes, with the key | Interception | Access at either end |
| Encryption at rest | Encrypts files and disks | Yes, with the key | Stolen media, storage-level access | Any authorized query |
| Dynamic masking | Changes what a query returns for a user | Original stays stored | Casual exposure to authorized users | Users with rights to the unmasked data |
| Tokenization | Replaces a value with a token; the mapping lives in a separate vault | Only through the vault | Exposure in analytics systems | Compromise of the token vault |
| Hashing | One-way transform | No | Storing the original | Guessing low-entropy values unless salted |
| Row-level security | Filters which rows a user sees | n/a | Seeing other regions or tenants | Inference from aggregates |
| Column-level security | Blocks or masks specific columns | n/a | Seeing sensitive attributes | Joining to another copy of the data |
Pseudonymized data (IDs replaced, but re-identification is possible) is still personal data. Anonymized data cannot be traced back to a person, and that is hard to achieve.
an alert or a report.
revoke the credential or the grant; isolate the resource.
which data, how many people, for how long. Lineage and audit logs answer this.
owner, security, legal or privacy; regulators and affected people when the law requires it.
rotate secrets, restore integrity, confirm the gap is closed.
blameless review, with actions assigned and dated.
Write this procedure before the first incident, and rehearse it.
GDPR (European Union), CCPA (California), HIPAA (United States health data), PCI DSS (card data), and local laws such as Colombia's Law 1581 of 2012 on personal data. They share common ideas: collect only what is needed, state the purpose, protect it, respect people's rights over their data, and report breaches. This is orientation, not legal advice; confirm applicable requirements with a specialist.
| Capability | Azure | AWS | Google Cloud | Databricks / open source |
|---|---|---|---|---|
| Identity | Microsoft Entra ID, managed identities | IAM, IAM Identity Center | Cloud IAM, service accounts | Unity Catalog principals, SCIM |
| Secrets and keys | Key Vault | Secrets Manager, KMS | Secret Manager, Cloud KMS | Secret scopes; HashiCorp Vault |
| Row and column control | SQL row-level security, dynamic data masking | Lake Formation filters, Redshift RLS | BigQuery row access policies, policy tags, data masking | Unity Catalog row filters and column masks |
| Discovery of sensitive data | Purview classification | Macie | Sensitive Data Protection | Open-source scanners |
| Audit | Azure Monitor, Purview audit | CloudTrail | Cloud Audit Logs | Unity Catalog system tables |
One identity system across data services; several places where a permission can be granted (resource, workspace, item, data), so document which layer is authoritative. ·
Very fine-grained IAM; policies become complex, and a mistake can be hard to see. ·
Policy tags apply column security consistently in BigQuery; coverage outside BigQuery differs. ·
Row filters and column masks are defined once on the table; data read through paths outside Unity Catalog bypasses them.
Write an access matrix and test one allowed query and one denied query.
analyst and customer_service.email and deny card_reference to both.analyst, run a sales total (allowed) and select email (masked) and card_reference (denied).Encryption at rest protects the stored bytes against someone who gets the disk, the file, or the backup without the key. The query engine decrypts data transparently for any identity that is authorized to read the table, so the analyst sees clear text. Limiting what an authorized user sees needs controls at the query layer: column permissions, masking, row filters, or tokenization.
Not necessarily. Dynamic masking changes the query result for a role; the original is still stored, and a user with a different path to the data (another copy, a broader role) sees it in clear. Check every copy and every path.
Read further: NIST Cybersecurity Framework ↗ · OWASP Top 10 ↗ · Unity Catalog access control ↗
Most business information lives outside tables: invoices, contracts, emails, images. Document and content management governs that unstructured data across its lifecycle, from capture to deletion. The goal is that the right person finds the right version, and nobody else does.
Manage invoices, receipts, and contracts linked to orders and suppliers.
| Document | Arrives as | Links to | Sensitive content | Retention (illustrative) |
|---|---|---|---|---|
| Supplier invoice | PDF by email or portal upload | Supplier, purchase order | Bank details | 10 years |
| Customer receipt | Generated PDF | Order, customer | Name, address | 5 years |
| Supplier contract | Signed PDF, several versions | Supplier | Prices, terms | Contract life + 10 years |
| Step | What happens | Output |
|---|---|---|
| Ingest | Capture the file once, assign a document ID, compute a checksum | Original stored unchanged |
| Classify | Decide the type (invoice, receipt, contract) and the sensitivity | Type and classification tags |
| Extract | OCR and layout analysis, then field extraction | Text plus fields with a confidence score |
| Index | Add text and metadata to a search index (keyword, vector, or both) | Searchable entry |
| Retrieve | Search by metadata or content, filtered by the user's permissions | The document, or a passage with a citation |
Structured data has a schema before it is written. Documents get their structure after the fact, through metadata and extraction; that is why metadata quality decides whether anything can be found.
When extraction improves, run it again over the originals
| Layer | Content | Rule |
|---|---|---|
| Original | The file exactly as received | Immutable; the legal record |
| Extracted | Text and fields produced by OCR or a model | Reproducible; store the extractor version; never overwrite the original |
| Derived | Classification, summaries, embeddings, links to orders | Rebuildable at any time from the first two layers |
When the extraction model improves, re-run it over the originals. That is only possible if originals were never modified.
| Field | Example | Why |
|---|---|---|
document_id | DOC-2025-000123 | Stable identity across systems |
document_type | supplier_invoice | Filter and retention rule |
supplier_id / order_id | SUP-042 / PO-7781 | Link to master data and transactions |
document_date, received_at | 2025-03-01, 2025-03-03 | Business date versus arrival date |
invoice_number, total_amount, currency | F-9912, 1,250.00, EUR | Search and reconciliation |
version, is_current | 3, true | Which contract text applies |
classification | Restricted | Access and handling |
retention_until, legal_hold | 2035-03-01, false | Deletion control |
source, checksum | ap-inbox, SHA-256 | Provenance and duplicate detection |
Supplier and order IDs come from Reference & Master Data; a document that cannot be linked to them is much less useful.
A document inherits the permissions of its folder or case unless it is explicitly restricted. The search index, the extracted text, the embeddings, and any cache must enforce the same permissions as the source.
Filter search results by the user's permissions at query time. Checking permissions only when indexing leaves stale access in place.
Keep every version of a contract, mark one as current, and record who changed what. Reports must say which version they used.
Set by document type and law. A legal hold suspends deletion while a dispute or audit is open.
Deleting the original is not enough. Extracted text, index entries, vector embeddings, thumbnails, exports, and backups must follow, and the deletion should be recorded.
Retrieval-augmented generation (RAG) uses the same pipeline: split documents into chunks, index them (often as embeddings), retrieve relevant chunks for a question, and let a language model answer with citations. The governance rules above still apply: permissions are enforced at retrieval, sources are cited, and deleted documents leave the index. See the Agentic Development topic.
| Capability | Azure | AWS | Google Cloud | Open source |
|---|---|---|---|---|
| Store originals | Blob Storage, SharePoint | S3 | Cloud Storage | MinIO, any object store |
| Extract text and fields | Azure AI Document Intelligence | Amazon Textract | Document AI | Tesseract OCR, Apache Tika |
| Search index | Azure AI Search | Amazon OpenSearch Service, Amazon Kendra | Vertex AI Search | OpenSearch, Elasticsearch |
Prebuilt invoice and receipt models reduce setup; custom layouts need labeled training documents.
Textract returns text, tables, and key-value pairs through an API; linking results to business entities is your code.
Specialized processors per document type; choosing and versioning processors is part of the design.
Full control and no per-page fee; you own accuracy, scaling, and maintenance.
Sketch an invoice ingestion flow and define the fields needed to retrieve a document.
total_amount and say what happens below it.F-9912 by supplier and month, and you can list every place where a copy of it lives.The document must disappear from their results immediately, including snippets, summaries, and AI-generated answers based on it. That requires checking permissions at query time against the source system, or synchronizing permission changes to the index quickly and treating the delay as a risk. Cached results and previously exported copies need their own rule. If the index only stored permissions at ingestion time, the user keeps seeing content they should no longer see.
Read further: DAMA · DMBOK ↗ · Azure AI Document Intelligence ↗ · Amazon Textract ↗ · Google Cloud Document AI ↗
Every system has its own idea of what a product or a customer is. Master data management builds one trusted version of those shared entities; reference data management keeps the code lists that classify them. Without both, every report starts with an argument about whose number is right.
Unify product IDs across a storefront and ERP while standardizing country and currency codes.
| Source | Product ID | Name | Country field | Currency field |
|---|---|---|---|---|
| Storefront | SKU-00417 | "Trail Shoe Men 42 Blue" | Colombia | COP$ |
| ERP | 100417 | "SHOE TRAIL M 42 BLU" | CO | COP |
Same product, two identifiers, two spellings; same country and currency, two codes.
| Master data | Reference data | Transactional data | |
|---|---|---|---|
| What | Core business entities | Code lists and classifications | Events |
| Examples | Customer, product, supplier, store | Country, currency, unit of measure, order status | Order, payment, shipment |
| Changes | Slowly | Rarely; often set by an external standard | Constantly |
| Volume | Medium | Small | Large |
| Shared by | Many systems | Almost every system | Usually one process |
Transactions refer to master data, and master data is classified by reference data. An error in a code list spreads everywhere.
| master_product_id | source_system | source_key | match_rule | matched_at |
|---|---|---|---|---|
P-000417 | storefront | SKU-00417 | GTIN exact | 2025-03-01 |
P-000417 | erp | 100417 | GTIN exact | 2025-03-01 |
| Attribute | Owner | System of record |
|---|---|---|
| Name, description, images | Merchandising | Storefront |
| Cost, supplier, tax category | Finance and purchasing | ERP |
| Category hierarchy | Merchandising | Master data hub |
| Source value | Standard code | List |
|---|---|---|
Colombia, COL, CO | CO | ISO 3166-1 alpha-2 |
COP$, Pesos, COP | COP | ISO 4217 |
Unmapped values go to a review queue; they are not silently set to "Other".
A wrong merge costs more than a missed match
| Deterministic | Fuzzy (probabilistic) | |
|---|---|---|
| How | Exact match on a trusted key or normalized fields | Similarity score across several attributes |
| Example | Same GTIN (barcode); same tax ID | Similar name + same brand + same size |
| Strength | Explainable, fast, few false matches | Finds matches when no shared key exists |
| Weakness | Misses records with missing or mistyped keys | Needs thresholds, tuning, and human review |
Steps: standardize (case, spaces, abbreviations, units) → block (compare only plausible pairs, for example same brand) → score → decide.
| Score | Decision |
|---|---|
| Above the upper threshold | Auto-merge |
| Between thresholds | Send to a data steward for review |
| Below the lower threshold | Keep separate |
A wrong merge is usually more expensive than a missed match: it mixes two entities' history and is hard to undo. Set the upper threshold conservatively.
When matched records disagree, a rule per attribute decides which value survives.
| Attribute | Storefront | ERP | Rule | Golden record |
|---|---|---|---|---|
| Name | "Trail Shoe Men 42 Blue" | "SHOE TRAIL M 42 BLU" | Storefront owns marketing name | "Trail Shoe Men 42 Blue" |
| Cost | (empty) | 38.50 | ERP owns cost | 38.50 |
| Weight | 0.9 kg | 0.85 kg (updated later) | Most recent non-null | 0.85 kg |
| Category | "Shoes" | "FOOTWEAR/TRAIL" | Hub owns the hierarchy | Footwear › Trail |
Common rules: source priority, most recent, most complete, most frequent. Keep the source values and the rule applied, so a decision can be explained and reversed.
P-000902 merged into P-000417 on 2025-04-10".| Style | How it works | Fits when |
|---|---|---|
| Registry | Hub stores only keys and the crosswalk; attributes stay in sources | Quick start; sources cannot change |
| Consolidation | Hub builds a golden record for analytics; sources are not updated | Reporting is the main need |
| Coexistence | Golden record flows back to sources | Sources can accept updates |
| Centralized | Master data is created in the hub and distributed | Strong governance, new processes |
The retail example needs consolidation: analytics must report on one product list.
Microsoft Purview and Fabric integrate with partner master data products; SQL Server Master Data Services exists for on-premises SQL Server. No single native cloud MDM service, so many teams build consolidation in the lakehouse.
AWS Entity Resolution matches and links records with rule-based or machine-learning techniques. It solves matching, not stewardship workflows or survivorship.
BigQuery with entity resolution features and SQL-based matching. Flexible and close to the data; rules and review queues are yours to build.
Delta tables with dbt for crosswalks and survivorship rules; Splink for probabilistic matching at scale. Transparent and version-controlled; no steward interface out of the box.
(for example Informatica, Reltio, Profisee, Stibo) add stewardship screens, workflows, and hierarchy management at a licence cost.
Build a product crosswalk and document how you resolve two conflicting source records.
When they are different real-world things. A 500 ml and a 1 L bottle of the same product differ by one character and are different SKUs. Two customers called "Juan Pérez" in the same city are different people unless a stronger identifier agrees. A parent company and its subsidiary share a name and are separate legal entities. If a distinguishing attribute (size, tax ID, date of birth, GTIN) differs, or the evidence is only the name, keep them separate or send them to review. Merging by mistake mixes their histories.
Read further: ISO 3166 · Country codes ↗ · ISO 4217 · Currency codes ↗ · DAMA · DMBOK ↗ · AWS Entity Resolution ↗
A warehouse organizes data for analysis; business intelligence puts it in front of people who decide. The hard part is rarely the chart. It is agreeing on what a number means and making sure every report computes it the same way.
Build a daily sales dashboard that accounts for returns and time zones.
Finance (close the day), store operations (react to yesterday), executives (trend).
Net sales = gross sales − discounts − returns and refunds, excluding tax.
The calendar day in the store's local time zone.
Ready by 07:00, covering the previous business day.
| Audience | Decision | Metric | Grain | Freshness |
|---|---|---|---|---|
| Finance | Is yesterday's close correct? | Net sales, refunds, reconciliation difference | Day × store | Daily, 07:00 |
| Store operations | Which products need attention? | Units, net sales, return rate | Day × store × product | Daily |
| Executives | Are we on plan? | Net sales versus target, growth | Week × region | Weekly |
A dashboard without a decision becomes a wall of charts. Ask "what will you do differently when this number changes?"
| Layer | Content | Rule |
|---|---|---|
| Staging | Source data, typed and deduplicated, one table per source object | No business logic |
| Core (facts and dimensions) | fact_sales, fact_refunds, dim_date, dim_product, dim_customer, dim_store | Conformed dimensions shared by every fact |
| Reporting (marts) | rpt_daily_sales aggregated for the dashboard | Derived only from core; safe to rebuild |
This maps to bronze, silver, and gold in a medallion lakehouse. Kimball's approach builds dimensional marts on conformed dimensions; Inmon's builds a normalized enterprise warehouse first and marts on top. Many platforms mix both.
A semantic layer stores metric definitions, dimensions, and relationships in one place so every tool computes the same result.
metric: net_sales owner: finance description: Gross sales minus discounts, returns and refunds. Excludes tax and shipping. formula: sum(gross_amount) - sum(discount_amount) - sum(refund_amount) grain: order line time_dimension: business_date (store local time) currency: reporting currency, converted at the daily rate filters: exclude test orders and cancelled orders
SELECT d.business_date,
s.store_name,
SUM(f.gross_amount) - SUM(f.discount_amount) AS sales_before_refunds,
COALESCE(SUM(r.refund_amount), 0) AS refunds,
SUM(f.gross_amount) - SUM(f.discount_amount)
- COALESCE(SUM(r.refund_amount), 0) AS net_sales
FROM (SELECT date_key, store_key,
SUM(gross_amount) AS gross_amount, SUM(discount_amount) AS discount_amount
FROM fact_sales GROUP BY date_key, store_key) f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_store s ON s.store_key = f.store_key
LEFT JOIN (SELECT date_key, store_key, SUM(refund_amount) AS refund_amount
FROM fact_refunds GROUP BY date_key, store_key) r
ON r.date_key = f.date_key AND r.store_key = f.store_key
GROUP BY d.business_date, s.store_name;Sales and refunds are aggregated to the same grain before joining (see the fan-out trap in Data Modeling).
Model a refund as its own event with its own date, linked to the original order line. Two valid reporting views:
Give the two metrics different names
| View | A refund on 3 April for a sale on 28 March appears in | Used for |
|---|---|---|
| Accounting (event date) | April | Financial reporting; closed periods stay closed |
| Cohort (original sale date) | March | Product and campaign analysis, return rates |
Pick one as the default for "net sales", name the other differently (for example "net sales by order month"), and document both.
business_date from the store's local time when loading the fact. An order at 23:30 in Bogotá is already the next day in UTC.| Check | Compare | Tolerance (example) |
|---|---|---|
| Row count | Orders in the source versus the warehouse for the day | Exact |
| Amount | Sum of order amounts in the source versus fact_sales | Exact |
| Cash | Captured payments minus refunds versus the settlement file | Within an agreed tolerance for timing and fees |
Publish the reconciliation result with the report. An unexplained difference blocks the close.
Reload only new and changed periods, plus a lookback window (for example 7 days) to pick up late orders and refunds. Fully rebuild on a schedule or after logic changes.
Star schema, aggregate tables for common questions, partition by date, limit visuals per page, and filter by default to a recent period. Choose import, direct query, or a hybrid from the freshness requirement.
Row-level security so a store manager sees one store; column restrictions on customer details; certified datasets so people know which one is official.
One screen, one question. Put the headline number first with a comparison (versus yesterday, last year, or target), then the trend, then the breakdown. Label units and time zone, show when the data was last refreshed, and do not rely on color alone.
Illustrative numbers · Business day in store local time · Updated 06:45
Fabric Warehouse or Lakehouse with Power BI. The semantic model and DAX hold the metric definitions close to the reports; definitions are harder to reuse outside Power BI.
Redshift with QuickSight. A mature warehouse with fine performance controls; the semantic layer is thinner, so definitions often live in SQL views or dbt.
BigQuery with Looker. LookML keeps metric definitions in version control; it is another language for the team to learn.
Databricks SQL with Unity Catalog metric views, or dbt with its semantic layer; Apache Superset or Metabase for dashboards. Definitions as code, independent of the BI tool; more integration work.
Write a metric definition, a sample SQL query, and a dashboard sketch.
fact_sales and fact_refunds: three days, two stores, one order at 23:30 local time, one refund in a later month.In the default financial view it reduces net sales in the month the refund is posted. The original month is closed and stays as reported. For product or campaign analysis, a second view attributes the refund to the original sale month so return rates are meaningful. Both are correct for their purpose; the report must say which one it shows, and the two metrics must have different names. Silently restating a closed month breaks trust in every past report.
Read further: Kimball Group · Dimensional modeling techniques ↗ · Microsoft · Star schema and Power BI ↗ · dbt documentation ↗
Metadata is the data that describes data: what it means, how it is built, who owns it, and how it behaves. Managing it well turns "ask the one person who knows" into something anyone can look up.
Trace net sales in the dashboard back to source orders and refund records.
| Kind | Answers | Examples | Produced by |
|---|---|---|---|
| Technical | How is it stored and built? | Schema, types, partitions, lineage, job definitions | Systems, automatically |
| Business | What does it mean, and who decides? | Definition, owner, classification, glossary term, valid uses | People |
| Operational | How is it behaving? | Last refresh, row counts, run status, quality results, usage | Pipelines and platforms |
A fourth group is sometimes separated: social metadata such as ratings, questions, and who uses the dataset most.
dataset: reporting.rpt_daily_sales
description: Net sales per business day and store. Official source for the Daily Sales dashboard.
owner: Finance lead
steward: Revenue steward
classification: Internal
glossary_terms: [Net sales, Business day]
grain: one row per business_date and store
freshness: daily by 07:00 store local time
upstream: [core.fact_sales, core.fact_refunds, core.dim_store, core.dim_date]
quality_checks: [reconciliation vs source, completeness of store_key, freshness]
certified: true
how_to_use: "SUM(net_sales) by date; filter on business_date."
known_limits: Refunds are counted in the month they are posted.
columns:
- {name: business_date, type: date, description: Calendar day in the store's time zone}
- {name: store_key, type: int, description: Key to dim_store}
- {name: net_sales, type: decimal(14,2), description: Gross − discounts − refunds, excluding tax}
last_reviewed: 2025-03-01The fields people actually read first: description, owner, freshness, whether it is certified, and one example of use.
| Dataset (table) lineage | Column lineage | |
|---|---|---|
| Shows | Which tables feed which tables | Which columns feed which columns, through which transformation |
| Answers | "What breaks if this table is late?" | "What changes if the meaning of this column changes?" |
| Cost | Cheap to capture | Needs query parsing or engine support |
| Example | rpt_daily_sales depends on fact_sales and fact_refunds | net_sales = gross_amount − discount_amount − refund_amount |
| Capture automatically | Enrich by hand |
|---|---|
| Schemas, types, partitions | Descriptions and definitions |
| Lineage from SQL and job runs | Owner and steward |
| Freshness, volumes, run history | Classification decisions (often suggested, then confirmed) |
| Usage statistics | Known limits and example queries |
Automate everything that systems know. Spend human time only on what systems cannot know. Keep descriptions next to the code (for example in dbt model files) and sync them to the catalog, so they are reviewed with each change.
Active metadata means using metadata to drive actions: block a deployment that breaks a certified dataset, propagate a classification tag downstream, or alert consumers when an upstream table is late.
Before changing a source column, walk the lineage downstream:
The same lineage read upstream gives root-cause analysis: when the dashboard is wrong, follow it back until the first failing check.
last_reviewed date and flag entries older than an agreed period.An open specification for emitting lineage events from jobs.
Native metadata in most engines; the cheapest place to start.
Carry schema, ownership, and expectations between producer and consumer (see Data Integration).
Microsoft Purview Unified Catalog; Fabric shows item lineage inside a workspace. Scans many Microsoft and third-party sources; lineage depth differs by source, so check the connector's documented coverage.
Glue Data Catalog for technical metadata; Amazon DataZone for business metadata and discovery. The Glue catalog is used by many AWS engines; business context needs the second service.
Dataplex Universal Catalog with automatic lineage for BigQuery. Little setup inside Google Cloud; external systems need custom entries or the lineage API.
Unity Catalog captures table and column lineage for workloads that run through it. DataHub and OpenMetadata are open catalogs; OpenLineage standardizes lineage events. Open tools avoid lock-in and need hosting. · ·
Create a catalog entry and draw lineage from source to dashboard.
rpt_daily_sales using the template in section 2.net_sales back to its three source amounts.amount column will include tax from next month. List every output that needs review and who owns it.Everything on the column's downstream lineage path: the curated orders table, fact_sales.gross_amount, the net_sales metric and its semantic definition, rpt_daily_sales, the dashboard, and any export, machine learning feature, or reconciliation rule built on them. Historical data needs a decision too: restate it or mark a break in the series. Schema checks will not catch this change because the name and type stay the same; only communication, the contract, and reconciliation do.
Read further: OpenLineage documentation ↗ · Microsoft Purview documentation ↗ · Unity Catalog documentation ↗ · DAMA · DMBOK ↗
Quality is not an absolute property of data. The same table can be good enough for a trend chart and not good enough for an invoice. Data quality management defines what "fit" means for each use, checks it, and responds when the check fails.
Detect missing order IDs, duplicate payments, stale files, and unexpected totals before publishing sales.
| Dimension | Question | Check in the example |
|---|---|---|
| Completeness | Is required data present? | order_id is never null; every store reported today |
| Uniqueness | Is each thing recorded once? | One row per payment_id |
| Validity | Does the value follow the rules? | currency is an ISO 4217 code; quantity > 0 |
| Consistency | Do related values agree? | Sum of order lines equals the order total; every sale has a known product |
| Accuracy | Does it match reality? | Captured payments match the provider's settlement file |
| Timeliness | Is it available when needed? | Today's file landed before 05:00 |
Accuracy is the hardest to test because it needs an external truth to compare with. Reconciliation against another system is the usual substitute.
A failed blocking rule stops publication; a warning travels with the data
| Rule | Threshold | Severity | Owner | If it fails |
|---|---|---|---|---|
order_id not null | 100% | Blocking | Storefront team | Quarantine rows; stop if more than 0.1% of the batch |
payment_id unique | 100% | Blocking | Payments team | Deduplicate by key; stop if amounts differ between duplicates |
| File arrived by 05:00 | — | Blocking | Platform team | Keep yesterday's report; show a delay notice |
| Daily total within ±30% of the 4-week average for that weekday | — | Warning | Revenue steward | Publish with a flag; investigate the same day |
| Customer email matches a pattern | ≥ 98% | Warning | CRM team | Report the trend; no stop |
Blocking failures stop publication because the number would be wrong. Warnings let the data flow and tell people what to be careful with. Thresholds come from the business impact, not from what the data happens to look like today.
| Point | Purpose | Typical checks |
|---|---|---|
| At ingestion | Catch source problems early and cheaply | Schema, required fields, file arrival, duplicates |
| After transformation | Catch logic errors | Keys unique, relationships resolve, row counts preserved |
| Before publication | Protect consumers | Reconciliation, totals in range, freshness |
Write–audit–publish: write the new data to a staging version, audit it with the checks, and publish (swap) only if they pass. Consumers never see a half-checked table.
-- Completeness: orders without an ID SELECT COUNT(*) AS missing_order_id FROM staging.orders WHERE order_id IS NULL; -- Uniqueness: duplicate payments SELECT payment_id, COUNT(*) AS copies FROM staging.payments GROUP BY payment_id HAVING COUNT(*) > 1; -- Timeliness: hours since the latest load SELECT TIMESTAMPDIFF(HOUR, MAX(loaded_at), CURRENT_TIMESTAMP) AS hours_stale FROM staging.orders; -- Consistency: lines that do not add up to the order total SELECT o.order_id FROM staging.orders o JOIN (SELECT order_id, SUM(line_amount) AS lines_total FROM staging.order_lines GROUP BY order_id) l USING (order_id) WHERE ABS(o.order_total - l.lines_total) > 0.01; -- Accuracy by reconciliation: warehouse versus settlement file SELECT w.business_date, w.captured, s.settled, w.captured - s.settled AS difference FROM (SELECT business_date, SUM(amount) AS captured FROM curated.payments GROUP BY business_date) w JOIN (SELECT settlement_date AS business_date, SUM(amount) AS settled FROM curated.settlement GROUP BY settlement_date) s USING (business_date) WHERE ABS(w.captured - s.settled) > 1.00;
Each check should return zero rows, or one number compared against a threshold. Date functions vary by engine.
move failing records to a side table with the rule that failed, the batch ID, and the raw payload. Good records continue.
find the root cause. Is it one record, one source, or a logic change? Lineage shows where to look.
when possible. Patching downstream hides the problem and it returns.
reprocess the quarantined records or the whole batch through the same pipeline. Idempotent writes make this safe (see Data Integration).
what happened, what was affected, and what changed to prevent it.
Illustrative trend for one rule
Microsoft Purview data quality for rule-based scans and scores; checks in Fabric or Data Factory pipelines. Scores are visible to business users in the catalog; rules run as scheduled scans, not inside the pipeline run.
AWS Glue Data Quality, with rules written in DQDL; Deequ underneath. Runs inside Glue jobs or on catalog tables; ties the checks to Glue.
Dataplex automatic data quality and data profiling for BigQuery tables. Rules are declared without code; coverage is centered on BigQuery.
Pipeline expectations in Lakeflow Declarative Pipelines (warn, drop, or fail per rule); dbt tests; Great Expectations; Soda. Checks live in version control with the code; you assemble reporting and alerting yourself. · ·
Create a small dirty dataset, write SQL checks, and document what happens when each fails.
order_id, 2 duplicate payments, 1 negative quantity, 1 order whose lines do not add up, 1 invalid currency code.Stop publication when the failure makes the number wrong or misleading for its main use: missing or duplicated keys that change totals, a missing source file, a failed reconciliation, a broken relationship that drops rows. Report alongside the data when the failure affects a secondary attribute or stays within an agreed tolerance: a few invalid email addresses, a total that is unusual but explainable. The decision belongs to the data owner and depends on the use; the same failure can block a financial close and only warn on an exploration dashboard.
Read further: Databricks · Manage data quality with pipeline expectations ↗ · AWS Glue Data Quality ↗ · Great Expectations documentation ↗ · DAMA · DMBOK ↗
Technical skill gets the work delivered. Leadership builds the team that delivers it. These notes come from a leadership program I completed, rewritten in general terms so they apply to any technical team: what a leader does, how motivation works, how to give feedback, how to manage your own reactions, and how to see risks before they become problems.
A senior data engineer becomes the lead of a five-person team that runs the retail sales platform used in the other topics. In the first quarter the lead has to: set expectations with each person, keep the daily report reliable, handle a stakeholder who changes priorities every week, and support one engineer who is new to the stack and another who is very capable but has lost interest.
| Management | Leadership |
|---|---|
| Organizes processes, systems, and resources | Sets vision, direction, and purpose |
| Seeks efficiency and consistency | Motivates people and helps them adapt |
| Keeps the operation running | Drives growth and long-term improvement |
An effective lead needs both. The aim is a system of people and processes that works with autonomy; a good test is whether the work continues normally when the lead is away for a few days.
Core idea from the program: project tasks deliver the product; managing performance develops the team. No performance problem should come as a surprise to the person, and career conversations need structure and evidence.
Threaten safety and everything above it stops working
Basic conditions to live and work
Stability; being able to act without constant threat
Feeling part of the team
Seeing that one's contributions are valued
Growing, taking on challenges, doing meaningful work
Threatening someone's safety to push for results moves their attention to self-protection. People stop taking risks, stop reporting problems, and the environment turns toxic. Psychological safety is a precondition for the higher levels.
A reading aid I added: the Situation–Behavior–Impact structure keeps feedback factual. "In yesterday's review (situation), you explained the rollback plan before anyone asked (behavior), and the stakeholder approved the release in ten minutes (impact)."
The program named four profiles: analytical, controller, enthusiast, enabler. The idea is to adapt how you communicate and direct to what each person needs. They resemble the classic Social Styles model, which the table uses as a reading aid.
| Profile | Tends to value | Communicate with |
|---|---|---|
| Analytical | Accuracy, data, time to think | Evidence, detail, written context in advance |
| Controller | Results, speed, autonomy | The goal and the decision needed, briefly |
| Enthusiast | Ideas, energy, recognition | The vision and room to contribute |
| Enabler | Harmony, support, stability | Personal attention, clear reassurance about change |
Profiles describe preferences, not boxes. Ask people how they like to receive information and feedback.
Adjust the style to two variables for the specific task: competence to do it and motivation to take it on.
| Motivated | Not motivated | |
|---|---|---|
| Not yet competent | Tell / Train: give clear instructions and build the skill | Direct closely, set short goals, and find what is blocking the motivation |
| Competent | Delegate: hand over the outcome and stay available | Involve / Consult: include the person in decisions, listen, reconnect the work to something they care about |
Tell / Train and Involve / Consult come from the program; the other two cells complete the grid with the widely used skill/will matrix. Related model: Hersey and Blanchard's Situational Leadership (directing, coaching, supporting, delegating).
In the running example: the engineer who is new to the stack and eager gets Tell / Train; the capable engineer who has lost interest gets Involve / Consult. The same person can sit in different cells for different tasks.
EQ is the ability to recognize emotions, regulate responses, and relate well to others. It matters more as responsibility grows, because a leader's reactions spread to the team. It is developed with deliberate practice, feedback, and learning from mistakes.
| Component | What it involves | Engineering analogy from the program |
|---|---|---|
| Self-awareness | Knowing your emotions, triggers, and reactions | Your local environment: look after it before supporting others |
| Self-regulation | Pausing and choosing a productive response | Error handling: decide how to respond to stress |
| Internal motivation | A purpose that does not depend only on external recognition | — |
| Empathy | Actively trying to understand another person's view | An API: the interface for connecting with different people |
| Social skills | Communicating and collaborating well | — |
Empathy is an active effort to understand; sympathy alone does not get there.
Before answering a message, email, or code review comment that frustrates you: pause for about five seconds, notice the reaction, ask whether the reply will help solve the problem, then choose a useful and respectful response.
"What happened that led to this result?" explores facts and context and lowers defensiveness. It is a communication habit, not a ban on the word "why".
Listen to understand before preparing your answer. You will hear concerns and motivations that would otherwise be missed.
Code, ideas, and documents are products that can be reviewed and improved. A critique of the work does not define the worth of its author. Treat it like a failing test: information about what needs adjusting.
Under pressure, the leader filters stress and noise so the team keeps its capacity to work and does not burn out. Like a load balancer, the leader manages what reaches the team, adds context, and helps people respond productively. This does not mean hiding reality, solving everything personally, or passing every pressure through untouched.
Three sources of pressure, as I noted them: operational pressure (delivery and incidents), people pressure (the team's needs and conflicts), and stakeholder or client pressure (expectations and changes).
Or trusted colleague who gives honest feedback.
Look for views different from your own.
That stretch you without breaking you.
To understand your own patterns.
Review your reactions and decisions.
Take responsibility for your learning and start the development conversations yourself.
A private incident log. Borrowing from engineering postmortems, keep a personal, blameless record of leadership moments that went badly.
What happened?
What set off my reaction?
What did I do?
What effect did it have on the conversation or the team?
What do I understand now?
What will I do differently next time?
The format is my own study aid.
One-on-ones build the relationship, review progress, and support development.
| Concept | Role | Example for the team |
|---|---|---|
| Objective | What we want to achieve | Make the daily sales report something finance trusts |
| Key results | Measurable signs that we are getting there | Published by 07:00 on 99% of business days; zero unexplained reconciliation differences for a quarter |
| KPIs | Ongoing measures of operational health | Pipeline success rate, time to recover, open data issues |
Objectives cascade: organization → area → team → individual, so each person can see how their work connects. Leave room for personal development goals such as a skill or a certification. Review progress in the one-on-ones, not only at the end of the period.
Not knowing what will happen; the origin of risk
An uncertain event that has not happened yet and could affect the work, for better or worse
A risk that has already happened and must be handled now
The aim is to minimize threats and maximize opportunities. Before a risk materializes you can act on its probability and its impact.
Where the signals show up: daily stand-ups, sprint planning, backlog refinement, retrospectives, lessons-learned sessions, and one-on-ones. Watch for recurring blockers, inconsistent feedback, confusion about tasks, retrospectives without corrective actions, and signs of exhaustion. Look at the context and the way of working before blaming a person.
The 5 Whys move from the visible symptom to the root cause.
Symptom: the team is unclear about daily tasks.
Why? It receives contradictory instructions.
Why? Different stakeholders change the priorities.
Why? There is no shared way to prioritize.
Action: agree who validates priorities and how changes are communicated.
Probability and impact. Estimate how likely the event is and what it would cost in trust, deadlines, cost, or margin. A medium-probability risk with critical impact deserves attention first.
Reduce the probability or the impact before it happens
Remove the exposure, for example by dropping the activity that creates the risk
Take the risk knowingly and define how it will be watched
Hand the management or the agreed consequences to a third party through a proper mechanism
Sending an email about a risk does not transfer it.
Escalating well. State the risk and the evidence, the possible consequences, the actions proposed or under way, who is responsible, what support or decision you need, and when it will be reviewed. Even without the final decision, the lead is responsible for starting the conversation and following up.
Risk register entry: risk, evidence, people affected, probability, impact, owner, action plan, owner per action, review date, status.
Then observe again
Involve / Consult. The person does not need instructions; they need a reason to engage. Ask for their view, include them in the decision, and connect the task to something they value. Giving detailed instructions to a competent person usually lowers motivation further.
A risk is an uncertain event that has not happened yet; you can still act on its probability and impact. An issue has already happened; you manage its effects.
Attention shifts to self-protection. People hide problems and stop taking initiative, so belonging, esteem, and growth cannot develop. Short-term output may rise; trust and long-term performance fall.
To filter stress and noise before they reach the team, add context, and distribute pressure so people can keep working well. Not to hide reality and not to absorb everything alone.
"What" asks about facts and context; "why" is easily heard as an accusation and triggers a defense. The goal is to understand the system that produced the result.
An agent is a language model that uses tools in a loop to reach a goal. Agentic development is the engineering around that loop: tools, state, memory, control flow, evaluation, and guardrails. LangChain gives the building blocks and a ready-made agent; LangGraph gives explicit control over the flow as a graph with persistent state.
Two agents on top of the retail sales platform from the other topics:
Answers questions such as "Why did net sales drop last Tuesday?" It can read the metric definitions, run read-only SQL on the reporting tables, and look up a dataset in the catalog. It cannot see customer contact details.
When the nightly job fails, it reads the run log, checks the quality results and lineage, proposes a cause and a fix, and waits for a human to approve before re-running anything.
| Level | Who decides the steps | Example | Use when |
|---|---|---|---|
| Single model call | Nobody; one request, one answer | Classify a support ticket | The task fits in one prompt |
| Workflow | Your code; the model fills in each step | Extract → validate → summarize an invoice | Steps are known in advance |
| Agent | The model, in a loop with tools | Investigate why sales dropped | Steps cannot be listed in advance |
Start with the simplest level that works. Agents cost more, take longer, and are harder to test. Move up only when the task is multi-step, hard to specify fully, valuable enough to justify the cost, and its errors can be caught and undone.
Common workflow patterns (from Anthropic's "Building effective agents"): prompt chaining, routing, parallelization, orchestrator–workers, and evaluator–optimizer. Most production systems are workflows with one or two agentic steps inside.
| Block | What it is | Design question |
|---|---|---|
| Model | The language model that reasons and writes | Which model and how much reasoning effort per step? |
| Instructions | The system prompt: role, goal, limits | What must it never do? What does "done" look like? |
| Tools | Functions the model can call, each with a name, a description, and an input schema | Is each tool narrow, well described, and safe to retry? |
| State | What the run knows so far (messages, intermediate results) | What must survive a crash or a pause? |
| Memory | Short-term: the current thread. Long-term: facts kept across threads | What is worth remembering, and who may read it? |
| Retrieval | Fetching relevant context at run time (RAG) | Are permissions enforced at retrieval? |
| Control flow | The order of steps, branches, and loops | Which decisions belong to code, and which to the model? |
| Human in the loop | Pauses for approval or input | Which actions are irreversible? |
| Guardrails | Checks on inputs, outputs, and tool calls | What happens when a check fails? |
| Observability | Traces of every model and tool call | Can I replay a bad run? |
This reason-and-act loop is often called ReAct. Each turn the model sees the conversation so far plus the tool results, and either calls another tool or answers. A step limit stops runaway loops.
The loop ends when the model answers without calling a tool, or when the step limit is reached
LangChain is an open-source framework (Python and JavaScript) that standardizes how to call models, define tools, and assemble agents.
One way to call chat models from different providers, so the provider can be swapped.
A decorator turns a typed function into a tool with a schema.
create_agentA ready-made tool-calling agent. Since version 1.0 it runs on LangGraph underneath.
Hooks around the loop for summarizing long histories, asking for human approval, redacting personal data, limiting calls, and similar concerns.
Ask for a response that matches a schema.
Vector stores, document loaders, retrievers, and many model providers.
# Sketch for LangChain 1.x. Check names against the current docs.
from langchain.agents import create_agent
from langchain.tools import tool
@tool
def run_sales_query(sql: str) -> str:
"""Run a read-only SQL query on the reporting schema and return up to 50 rows.
Use for questions about net sales by day, store, or product."""
return query_reporting_db(sql) # your function, read-only identity
@tool
def get_metric_definition(name: str) -> str:
"""Return the official definition of a business metric such as net_sales."""
return semantic_layer_lookup(name) # your function
agent = create_agent(
model="<provider>:<model-id>", # any supported chat model
tools=[run_sales_query, get_metric_definition],
system_prompt=(
"You are a sales analyst. Use the metric definitions before writing SQL. "
"Never guess numbers; if a query fails, say so."
),
)
result = agent.invoke(
{"messages": [{"role": "user", "content": "Why did net sales drop last Tuesday?"}]}
)
print(result["messages"][-1].content)Both samples are sketches written against the 1.x APIs to show the shape of the solution. Run them against the versions you install and check the names in the current documentation.
LangGraph is a lower-level library for building stateful, long-running workflows and agents as graphs.
A typed object shared by all steps; each key can have a reducer that says how updates combine
A function that reads the state and returns an update
A fixed transition from one node to the next
A function that picks the next node from the state
Saves the state after each step, per thread
One conversation or run, identified by a thread_id
Pauses the graph and waits for a human; resumes from the saved state
Long-term memory shared across threads
A graph used as a node inside another graph
What this gives: durable execution (resume after a crash), human-in-the-loop (pause before a risky step), streaming of intermediate steps, and time travel (inspect or replay from an earlier checkpoint).
# Sketch for LangGraph 1.x. Check names against the current docs. from typing import Annotated, TypedDict from langchain.chat_models import init_chat_model from langgraph.checkpoint.memory import InMemorySaver from langgraph.graph import END, START, StateGraph from langgraph.graph.message import add_messages from langgraph.prebuilt import ToolNode, tools_condition from langgraph.types import Command, interrupt class TriageState(TypedDict): messages: Annotated[list, add_messages] # reducer: append, do not overwrite proposed_fix: str approved: bool tools = [read_run_log, get_quality_results, get_lineage] # read-only tools model = init_chat_model("<provider>:<model-id>").bind_tools(tools) def investigate(state: TriageState): return {"messages": [model.invoke(state["messages"])]} def propose(state: TriageState): return {"proposed_fix": state["messages"][-1].content} def human_approval(state: TriageState): decision = interrupt({"proposed_fix": state["proposed_fix"]}) # pauses here return {"approved": decision == "approve"} def rerun_pipeline(state: TriageState): trigger_rerun() # the only write action return {"messages": [{"role": "assistant", "content": "Re-run started."}]} graph = StateGraph(TriageState) graph.add_node("investigate", investigate) graph.add_node("tools", ToolNode(tools)) graph.add_node("propose", propose) graph.add_node("human_approval", human_approval) graph.add_node("rerun_pipeline", rerun_pipeline) graph.add_edge(START, "investigate") graph.add_conditional_edges("investigate", tools_condition, {"tools": "tools", END: "propose"}) graph.add_edge("tools", "investigate") graph.add_edge("propose", "human_approval") graph.add_conditional_edges( "human_approval", lambda s: "rerun_pipeline" if s["approved"] else END ) graph.add_edge("rerun_pipeline", END) app = graph.compile(checkpointer=InMemorySaver()) # use a database checkpointer in production config = {"configurable": {"thread_id": "run-2025-03-05"}} app.invoke({"messages": [{"role": "user", "content": "Nightly sales job failed. Find out why."}]}, config) # ...the graph stops at human_approval; a person reviews the proposal, then: app.invoke(Command(resume="approve"), config)
The model explores freely inside the investigate ⇄ tools loop with read-only tools. The one action that changes something sits behind a fixed approval node. That split, free reasoning plus coded control, is the main reason to use a graph.
investigate ⇄ tools repeats until the model stops calling tools
| Need | Reach for |
|---|---|
| A standard tool-calling agent, quickly | LangChain create_agent |
| Cross-cutting behavior (summarize, approve, redact) on that agent | LangChain middleware |
| A fixed sequence with some model steps | LangGraph workflow, or plain code |
| Branches, loops, retries you must control | LangGraph |
| Pause for approval and resume hours later | LangGraph with a checkpointer and interrupts |
| Several agents with defined hand-offs | LangGraph (supervisor or hand-off pattern) |
| Tracing, datasets, and evaluations | LangSmith, with either one |
They are layers of the same stack: LangChain's agent is a prebuilt LangGraph graph. Start high, drop down when you need control.
Is the thread's state, saved by the checkpointer. Long threads need trimming or summarizing so the context window is not filled with old tool output.
Lives in a store, keyed by user or topic: preferences, facts, and past resolutions. Decide what is written, by whom, and how it is corrected or deleted.
The model only knows what is in its context. Give it the metric definitions, the table list, and the current date; keep tool results short; put stable instructions first.
Brings in documents at run time. Enforce the user's permissions when retrieving, and return sources so answers can be cited (see Document & Content Management).
| Pattern | How it works | Trade-off |
|---|---|---|
| Single agent, many tools | One loop | Simplest; struggles when tools or instructions grow large |
| Supervisor | A coordinator delegates to specialist agents and combines results | Clear control; the supervisor is a bottleneck |
| Hand-off | Agents pass control to each other | Flexible; harder to trace |
| Subagent as a tool | A specialist runs in its own context and returns a summary | Keeps the main context small; adds cost per call |
Add agents only when one agent demonstrably cannot do the job. Every extra agent multiplies tokens, latency, and failure modes.
MCP is an open protocol for exposing tools, data, and prompts to any compatible agent through a server. A catalog, a database, or a ticket system can be wrapped once and reused across frameworks. LangChain and LangGraph load MCP tools through an adapter package. Treat an MCP server like any dependency: review what it can do, pin its version, and give it the narrowest credentials.
The prompt is the last layer, not the first
| Risk | Control |
|---|---|
| Prompt injection through tool results or documents | Treat retrieved text as data, not instructions; limit what tools can do; require approval for sensitive actions |
| Excessive agency | Least-privilege tools, read-only by default, allow-lists |
| Sensitive data leakage | Row and column security at the source; redaction middleware; no secrets in prompts |
| Runaway loops and cost | Step limits, timeouts, token and spend budgets |
| Wrong but confident answers | Ground answers in tool results; require citations; allow "I could not determine this" |
| Irreversible actions | Human approval; dry-run mode; idempotent tools |
Product names in this area change often.
| Option | What it is | Trade-off |
|---|---|---|
| LangChain | Model interface, tools, prebuilt agent, middleware | Fast start; abstractions to learn and keep up with |
| LangGraph | Graph runtime with persistence, interrupts, streaming | Full control; more code to write |
| LangSmith | Tracing, datasets, evaluations, deployment for LangGraph apps | Strong fit with the stack; a hosted service to adopt |
| Claude Agent SDK | The agent harness behind Claude Code as a library, with built-in file and shell tools | Batteries included for coding-style agents; tied to one model family |
| OpenAI Agents SDK | Lightweight agents, hand-offs, guardrails, tracing | Simple model; centered on one provider |
| Microsoft Agent Framework | Microsoft's agent framework, successor to AutoGen and Semantic Kernel agents | Good Azure integration; young API |
| Google Agent Development Kit | Google's framework for agents on Gemini and Vertex AI | Good Google Cloud integration; young API |
| LlamaIndex, CrewAI, Pydantic AI | Other open-source options (retrieval-first, role-based teams, typed agents) | Each has a different focus; compare on your use case |
Cloud platforms for hosting and governing agents:
Azure AI Foundry Agent Service.
Amazon Bedrock Agents and AgentCore.
Vertex AI Agent Builder and Agent Engine.
Mosaic AI Agent Framework with MLflow tracing and evaluation.
Build the sales analyst agent in three steps, measuring at each one.
create_agent with two tools: metric definition lookup and read-only SQL. Run the dataset and record accuracy, steps, and cost per question.When the steps are known in advance. A workflow is cheaper, faster, and testable step by step. Use an agent when the path depends on what is discovered along the way and the cost of an occasional wrong path is acceptable.
It saves the graph's state after every step for a given thread. That allows conversation memory, resuming after a failure, pausing for human approval and continuing later, and inspecting or replaying earlier states.
The model chooses tools from their names, descriptions, and schemas. A vague description leads to the wrong tool, wrong arguments, or no call at all. Most agent failures in traces come from tool design and missing context, not from the model's reasoning.
Not the model's judgment alone. The export tool should not exist for this agent, or should require approval; the identity the agent runs as should lack access to customer details; and retrieved text should be treated as data. Defense comes from permissions and control flow, with the prompt as the last layer.
By running the same evaluation set before and after, and comparing the score, the cost, and the latency. An example that looks better in a demo proves nothing.
Read further: LangChain documentation ↗ · LangGraph documentation ↗ · LangSmith documentation ↗ · Anthropic · Building effective agents ↗ · Model Context Protocol ↗ · ReAct: Synergizing Reasoning and Acting in Language Models ↗ · OWASP Top 10 for LLM Applications ↗ · Claude Agent SDK ↗
Databricks brings data engineering, analytics, and AI workloads into a shared platform. Use this map to connect the components rather than study them in isolation.
Lakeflow Jobs: orchestrate tasks · Pipelines: define transformations
Ingest source data with minimal transformation.
Clean, validate, deduplicate, and structure data.
Create curated datasets and aggregates for consumers.
Distributed processing for batch and streaming workloads.
A table layer with ACID transactions, schema controls, and version history.
Centralized governance, permissions, and lineage.
Jobs orchestrate tasks; declarative pipelines define data transformations. Older resources may use the DLT name.
SQL workloads and analytics using SQL warehouses.
Experiment tracking and machine learning lifecycle management.
Read next: Medallion architecture ↗ · Unity Catalog ↗ · Platform glossary ↗
Continuous integration checks changes as they are integrated. Continuous delivery keeps a release ready for deployment; continuous deployment releases qualifying changes automatically.
Version the change and review its scope. Keep credentials out of source control.
Create a repeatable environment using the committed dependency lockfile.
Catch formatting, syntax, and static quality problems before execution.
Test isolated behavior: transformations, null handling, boundaries, and business rules.
Create the deployable output from the checked source revision.
Store the versioned build output so it can be identified and reused.
Release the chosen artifact using environment-specific configuration and credentials.
Check a few critical deployed paths to catch obvious release failures.
For this website, the build creates dist/. A smoke check can request the deployed homepage and verify expected content. Publishing manually is the current setup; a GitHub Actions deployment workflow is a future step.
Stop on failed required checks. Separate build output from environment secrets. Keep the previous known-good version available and define how to roll back.
No. Unit tests cover isolated behavior; smoke checks verify a small set of paths in the deployed environment. Neither replaces broader validation.
Read further: GitHub · Continuous integration ↗
Read further: GitHub · Continuous deployment ↗
Use these questions as a requirements checklist. Write down measurable needs and compare alternatives against them; avoid starting with a preferred product.
List the actual reads: point lookups, joins, scans, aggregations, or relationship traversals. Include latency and concurrency needs.
An order-status lookup and a monthly revenue scan are different workloads; one storage or serving pattern may not suit both.
Estimate reads, inserts, updates, and deletes during normal periods and peaks. Consider contention, caching, and freshness.
Heavy dashboard reads may justify a separate analytical serving layer instead of querying the transactional source directly.
Identify entities, cardinality, integrity rules, and how relationships are queried. Consider the shape as well as the query pattern.
Order lines refer to orders and products. Frequent relationship traversal can motivate evaluating a graph model; ordinary joins alone do not require one.
Estimate current size, growth, event rate, peak load, retention, and required time to insight. Distinguish arrival speed from processing latency.
A daily sales file and a continuous stream of events need different ingestion and capacity plans.
Identify people and applications, their definitions, access rights, delivery formats, and service expectations.
Finance may need reconciled daily totals; operations may need fresh inventory; an application may need a low-latency API.
No. They narrow the options. You still need to evaluate consistency, reliability, security, cost, operational skills, and explicit trade-offs.
Read further: Microsoft · Choosing among data models ↗