Module 11: Data Architecture Awareness (for analysts working with engineering)
1. Data Warehouse vs. Data Lake vs. Lakehouse — What an Analyst Needs to Know
The core distinction is structure-at-write-time vs. structure-at-read-time — this single framing explains almost every practical difference an analyst will encounter.
| Data Warehouse | Data Lake | Lakehouse | |
|---|---|---|---|
| Data structure | Structured, schema enforced on write | Raw, any format (structured, semi-structured, unstructured) — schema applied on read | Combines lake-style storage with warehouse-style structure/transaction guarantees layered on top |
| Schema approach | Schema-on-write — data must conform before it lands | Schema-on-read — store first, interpret structure later, per query/job | Schema enforcement available, but storage stays open-format |
| Typical storage | Proprietary columnar stores (Redshift, Snowflake, BigQuery) | Object storage (S3, ADLS) with open file formats (Parquet, ORC, Avro) | Open formats + transaction layer (Delta Lake, Apache Iceberg, Apache Hudi) on top of lake storage |
| Query performance | Fast, optimized for known query patterns (star schema, indexes, columnar compression) | Slower without a query engine layer on top; not optimized out of the box | Aims for warehouse-like performance on lake-style storage |
| Cost profile | Higher cost per TB, but you pay for the convenience and compute optimization | Cheap storage; compute is separate and only paid for when querying | Cheap storage with warehouse-like performance — the explicit value proposition |
| Who typically built/maintains it | Data Engineers, sometimes platform/DBA teams | Data Engineers, often closer to raw ingestion pipelines | Data Engineers, increasingly the default modern target architecture |
| What an analyst actually touches | SQL against curated, modeled tables — most BI work happens here | Usually accessed through a query engine (Athena, Presto/Trino, Databricks SQL) rather than directly | Usually accessed via SQL through the same query engines — looks like a warehouse from the analyst’s seat even though storage is lake-native |
The practical analyst-level synthesis: “From where I sit as an analyst, a lakehouse is mostly invisible — I query it with SQL the same way I’d query a warehouse. The distinction matters more for how the data got there and how much I should trust its structure: data in a curated warehouse schema has usually already passed through cleaning and modeling steps from the cleaning and data-modeling modules; raw data sitting in a lake (especially in a ‘raw’ or ‘bronze’ zone) often hasn’t, and querying it directly means I inherit all of the data quality problems — missing values, inconsistent types, duplicates — that a proper pipeline would otherwise have resolved before I ever see it.”
The medallion architecture pattern (common in lakehouse setups, worth knowing by name since it’s frequently referenced in interviews and job descriptions):
- Bronze — raw, unprocessed, as-ingested data — exactly as it arrived from the source.
- Silver — cleaned, deduplicated, type-corrected, joined to reference data — this is roughly where the entire data-cleaning module’s techniques get applied, just as a formal pipeline stage rather than ad hoc analyst work.
- Gold — business-level aggregates, star-schema-modeled, ready for BI consumption — this is what an analyst should generally be querying for reporting; querying bronze directly for a stakeholder dashboard is a common analyst mistake that reproduces data quality problems someone else already solved further down the pipeline.
Talking point: “If I find myself doing heavy cleaning logic that feels like it should already be solved — deduplicating, fixing types, joining reference tables — my first question is whether I’m querying a bronze/raw layer that I should instead be pulling from a gold/curated layer. Repeatedly re-solving the same cleaning problem inside every analyst’s individual query, instead of once in the pipeline, is a sign the data architecture needs a silver/gold layer that doesn’t exist yet — and that’s worth raising with the data engineering team rather than just absorbing the rework.”
2. ETL vs. ELT, Basic Understanding of Pipelines Feeding the Analyst’s Data Source
ETL (Extract, Transform, Load) vs. ELT (Extract, Load, Transform) — the difference is where transformation happens, and it’s a direct consequence of the rise of cheap, scalable cloud warehouses.
| ETL | ELT | |
|---|---|---|
| Order | Transform happens before loading into the destination, usually in a separate processing engine | Raw data loads first; transformation happens inside the destination warehouse using its own compute |
| Where transform logic lives | A dedicated ETL tool/server (Informatica, legacy SSIS-style tools) | SQL-based transformation tools running inside the warehouse (dbt is the dominant modern example) |
| Flexibility | Transformation logic is harder to inspect/change once built — often a black-box pipeline | Raw data is always available in the warehouse, so transformation logic can be rewritten and rerun without re-extracting from source |
| Why the shift happened | Made sense when warehouse compute was expensive/limited, so you transformed before loading to save warehouse resources | Made sense once cloud warehouses (Snowflake, BigQuery, Redshift) made compute cheap and scalable — load everything raw, let the warehouse do the heavy lifting |
dbt (data build tool) is worth naming specifically since it’s the dominant modern ELT transformation tool and increasingly something analysts themselves contribute to (the rise of the “analytics engineer” role sits exactly at this boundary) — dbt lets transformation logic be written as version-controlled SQL SELECT statements, with dependency management, testing, and documentation built in, rather than scattered across individual analyst queries.
Why an analyst should care about ETL/ELT even without building pipelines: it explains data lineage and why a number might be stale or wrong — if a dashboard shows the wrong number, the first diagnostic question is where in this pipeline did it go wrong — was it the source system, the extraction job, a transformation step, or the BI tool’s own calculation layer? Without a basic mental model of the pipeline stages, this debugging is just guesswork.
Talking point: “As an analyst, I don’t build these pipelines, but I need to know enough about the ETL/ELT shape to debug intelligently when a number looks wrong. If a dashboard’s revenue doesn’t match the source system, my first question is which stage to suspect — is this raw data that hasn’t landed yet (an extraction lag), a transformation bug in a dbt model, or a calculation difference in the BI tool’s own measure logic? That diagnostic instinct is the same ‘check the execution plan before assuming the query is wrong’ habit from the SQL module, just one layer further upstream.”
Basic pipeline orchestration concepts worth recognizing by name (not building, but recognizing in conversation with data engineers): Airflow (DAG-based job scheduling/dependency management), incremental vs. full refresh loads (does the pipeline reprocess everything each run, or just new/changed data — this affects both cost and how quickly a fix propagates), and SLA/freshness expectations (when should this data be expected to land, directly tying back to the timeliness dimension of the data quality framework module).
3. Data Governance, Lineage, and Access Control Basics
Data lineage — the traceable path of a piece of data from its original source through every transformation to its final reported form.
Why lineage matters practically to an analyst:
- Trust and debugging — when a number looks wrong, lineage is what lets you trace backward through the pipeline to find where it diverged from the source, rather than re-deriving the whole calculation from scratch.
- Impact analysis — before changing a column or metric definition, lineage shows everything downstream that depends on it — every dashboard, every other dbt model, every report — which is essential before making a change that could silently break a dozen other things.
- Modern tooling — dbt auto-generates a lineage graph (DAG) of model dependencies; warehouse-native tools (e.g., column-level lineage in Snowflake/BigQuery’s metadata, or dedicated catalog tools like Atlan/Collibar/Alation) extend this to full column-level tracing across a data platform.
Data governance basics — the practices that keep a data platform trustworthy at scale, several of which directly extend concepts already introduced earlier in this track:
| Governance concept | What it means | Connects back to |
|---|---|---|
| Data catalog | A searchable inventory of what datasets/tables/columns exist, who owns them, what they mean | Solves the “which version is the real one” problem from the Excel module, at platform scale |
| Certified/trusted sources | Explicitly marking which tables/datasets are the governed source of truth vs. ad hoc/experimental | The BI module’s “certified datasets” concept, now applied to the warehouse layer underneath |
| Metric/metric layer definitions | A single, centrally defined version of “what counts as Active Customer” or “Revenue” that all tools reference, rather than each team/tool defining it slightly differently | The consistency dimension of the data quality framework module, and the “single source of truth for measures” point from the BI/DAX module |
| Data ownership/stewardship | A named owner accountable for a dataset’s quality and definition | Makes the data quality framework’s checks (completeness, accuracy, timeliness) someone’s actual job, not a diffuse responsibility nobody owns |
| PII handling and classification | Tagging which columns contain personally identifiable or sensitive data, and restricting/masking them accordingly | Directly relevant to access control below, and to general data-privacy obligations (GDPR/CCPA-style regulatory context) |
Access control basics — the layer that decides who can see what, extending the RLS concept from the BI module down to the underlying data platform itself:
- Role-based access control (RBAC) — permissions assigned to roles (e.g., “Marketing Analyst,” “Finance”), and users inherit access via their role membership, rather than permissions being granted individually per person — easier to audit and maintain at scale.
- Column-level and row-level security at the warehouse layer — increasingly implemented directly in the warehouse (e.g., Snowflake’s masking policies, BigQuery’s column-level security) rather than only in the BI tool — meaning the same governed restriction applies no matter which tool someone uses to query the data, closing a gap that BI-tool-only RLS doesn’t cover (someone querying the warehouse directly via SQL would bypass BI-tool-level RLS entirely).
- Data masking/anonymization — showing a transformed or partially-redacted version of sensitive data (e.g., a masked email or last-4-digits of a card number) to roles that need the shape of the data without needing the actual sensitive value.
Talking point that closes the loop on this module and the whole track: “Governance and lineage are the data-platform-scale version of almost everything covered earlier in this track — a data catalog and certified sources solve the same ‘which version is real’ problem as the Excel versioning issue and the BI module’s certified datasets; warehouse-level RLS extends the BI-layer row-level security so it can’t be bypassed by querying the warehouse directly; and a metric layer is the single-source-of-truth principle from the DAX module, pushed one layer further upstream so even the BI tool itself inherits a consistent definition rather than each report author redefining ‘Revenue’ slightly differently. As an analyst, I don’t own building any of this — but understanding it is what lets me trust the data I’m given, debug it intelligently when something looks wrong, and know who to go to when the platform itself, not my own analysis, is the actual source of a problem.”