AWS Data Platform Engineer/Architect — Interview Guide

AWS data platform interview guide: S3, Glue, EMR, Redshift, Kinesis, Lake Formation, governance and architecture trade-offs.

How to use this guide

This guide prepares you for a Data Platform Engineer/Architect interview on AWS, built around five themes: warehouse design, ingestion, data hygiene, governance and the semantic/reporting layer. Each part gives the theory first, then the trade-offs interviewers probe, then what to say. Part 8 is a question bank with model answers; Part 9 rehearses system design and behavioural rounds.

Two-day reading plan

Your Azure experience, translated to AWS

Most interviewers accept strong cloud-agnostic fundamentals plus a clear mental map of the AWS equivalents. Use this table to translate your projects into AWS vocabulary on the fly.

A naming note: AWS renamed QuickSight to Amazon Quick Suite in October 2025 and shortened it to Amazon Quick in 2026; the BI component now goes by Quick Sight (Atlan explainer, Quick community thread). Interviewers may use any of the three names.

How to frame an Azure/Databricks background. Say it once, early: "My production platforms were on Azure and Databricks; the patterns I'll describe — medallion layers, CDC, data contracts, quality gates — map one to one onto S3, Glue, Redshift and Lake Formation, and I'll use the AWS names." Then do exactly that. Since the reporting layer is Power BI, prepare Part 7 carefully: it is the area most likely to be tested on specifics you have not done hands-on.

Part 1 — AWS data platform services

A modern AWS data platform is a lakehouse: S3 holds all data in open formats, the Glue Data Catalog describes it, Lake Formation governs it, and multiple engines (Glue/EMR Spark, Athena, Redshift) read the same tables. Redshift serves the curated, high-concurrency warehouse layer; Power BI or Quick Sight sits on top.

[embedded content: AWS reference data platform · 5 layers plus cross-cutting services\]

Read it top down: each source type has its own ingestion path, everything lands raw in S3, and data moves through Iceberg layers before Redshift serves it to Power BI. This is the diagram to redraw on the whiteboard in system design rounds.

1.1 Amazon S3 — the storage foundation

• Durability and model. 11 nines durability, strong read-after-write consistency (since Dec 2020). Objects are immutable; "updates" mean rewriting files, which is why table formats exist.
• Storage classes. Standard for hot data; Intelligent-Tiering when access patterns are unknown; Standard-IA / Glacier Instant / Flexible / Deep Archive for cold and archive. Lifecycle rules move raw landing data to cheaper tiers after N days.
• Layout. Separate buckets (or at least prefixes) per zone: raw/landing, bronze, silver, gold. Separate buckets make IAM, KMS keys, replication and lifecycle policies simpler.
• Partitioning. Hive-style prefixes such as dt=2026-10-12/. Partition on low-to-medium cardinality columns used in filters (date, region). Avoid over-partitioning: thousands of tiny partitions hurt more than they help.
• Small files problem. Streaming and frequent micro-batches create many small files; engines pay per-file overhead. Target 128 MB–1 GB Parquet files; run compaction (Iceberg rewrite_data_files, S3 Tables automatic compaction, or Glue jobs).
• Security. Block Public Access on, bucket policies, SSE-KMS encryption with per-zone keys, VPC gateway endpoints so traffic stays private, Object Lock for immutable audit data, versioning for recovery.
• Performance. Request rates scale per prefix (about 3,500 writes and 5,500 reads per second per prefix), so spread hot data across prefixes.

1.2 File and table formats

• Parquet is the default analytical file format: columnar, compressed (Snappy/ZSTD), with min/max statistics enabling predicate pushdown. ORC is similar; Avro suits row-oriented, schema-evolving streams; CSV/JSON belong only in landing.
• Open table formats (Apache Iceberg, Delta Lake, Apache Hudi) add a metadata layer over Parquet: ACID transactions, MERGE/UPDATE/DELETE, schema and partition evolution, time travel, and snapshot isolation.
• Iceberg is AWS's strategic choice. Athena, Glue, EMR, Redshift (read and, increasingly, write), Lake Formation and Amazon S3 Tables support it natively. S3 Tables are a managed Iceberg bucket type with automatic compaction, snapshot expiry and orphan-file cleanup.
• Why it matters in interviews: GDPR deletes, late-arriving data, CDC upserts and reproducible reports (time travel) all require a table format. Raw Parquet folders cannot do row-level deletes safely.
• Iceberg mechanics worth knowing: metadata.json → manifest list → manifest files → data files; hidden partitioning (days(ts)), so users don't filter on a derived column; copy-on-write vs merge-on-read (delete files) trade-offs between write and read cost.

1.3 AWS Glue

• Glue Data Catalog — Hive-compatible metastore of databases, tables, partitions and schemas. Shared by Athena, Redshift Spectrum, EMR and Glue jobs. One per account per region; share cross-account via Lake Formation or Resource Access Manager.
• Crawlers infer schemas and partitions from S3. Useful for discovery; in production prefer explicit DDL or table-format commits, because crawlers can silently change schemas or split tables.
• Glue ETL jobs — serverless Spark (PySpark/Scala) or Python shell. Glue 5.x runs Spark 3.5 with native Iceberg/Delta/Hudi support. Billing is per DPU-hour (1 DPU = 4 vCPU, 16 GB); worker types G.1X, G.2X up to G.8X; Flex execution for non-urgent jobs at lower cost; auto scaling.
• DynamicFrame vs DataFrame — DynamicFrames tolerate inconsistent schemas (resolveChoice), DataFrames are faster and richer. Common practice: read as DynamicFrame if messy, convert to DataFrame for transformations.
• Job bookmarks track processed S3 files or JDBC keys so reruns only process new data. Know their limits: they track by file/key, not business logic, and can be reset.
• Glue Data Quality — rules written in DQDL (e.g. IsComplete "id", ColumnValues "amount" > 0, RowCount > 1000), recommendations generated from data profiling, results published to CloudWatch and EventBridge so pipelines can stop on failure.
• Glue Streaming — Spark Structured Streaming from Kinesis/MSK.
• Glue Studio / DataBrew — visual authoring and no-code data preparation; mention them but position code-first for production.

1.4 Amazon Athena

• Serverless Trino/Presto-based SQL over S3 via the Glue Catalog. Pricing is about $5 per TB scanned (check current regional rates) or provisioned capacity (DPUs) for predictable workloads.
• Cost control: Parquet + compression, partitioning and partition projection (computes partitions from a pattern, avoiding catalog lookups), SELECT only needed columns, workgroups with per-query scan limits, CTAS/UNLOAD to materialise results.
• Iceberg on Athena: full DML (MERGE INTO, UPDATE, DELETE), OPTIMIZE and VACUUM, time travel queries.
• Federated queries via Lambda connectors (RDS, DynamoDB, etc.); fine for exploration, not for heavy production joins.
• Athena for Apache Spark — notebooks running Spark serverlessly.
• Use cases: ad hoc exploration, data quality checks, lightweight transformation (dbt-athena), querying logs (CloudTrail, ALB). Not ideal for hundreds of concurrent dashboard users with sub-second SLAs — that's Redshift's job.

1.5 Amazon Redshift

• Architecture: MPP, columnar. A leader node plans queries; compute nodes split into slices execute in parallel. RA3 nodes separate compute from Redshift Managed Storage (RMS, S3-backed with local SSD cache). Redshift Serverless bills per RPU-second with a base capacity setting.
• Distribution styles: KEY (co-locate join keys, e.g. a large fact and its biggest dimension on customer_id), ALL (copy small dimensions to every node), EVEN (round robin for no clear join), AUTO (Redshift decides and changes over time). Goal: minimise data redistribution (DS_BCAST_INNER, DS_DIST_BOTH in EXPLAIN are red flags) and avoid skew.
• Sort keys: compound (good for prefix filters, e.g. event_date, customer_id) or interleaved (rarely recommended now). Zone maps let Redshift skip 1 MB blocks outside the filter range. AUTO sort key is the default.
• Compression: column encodings (AZ64 for numerics/dates, ZSTD/LZO for text); ENCODE AUTO is default.
• Loading: COPY from S3 in parallel (split into files, multiple of slice count), never row-by-row INSERT. UNLOAD to export. Auto-copy from S3 for continuous ingestion. Streaming ingestion from Kinesis/MSK into materialised views.
• Upserts: MERGE statement (or staging table + delete/insert in a transaction).
• Maintenance: automatic VACUUM and ANALYZE; still know them because interviewers ask about deleted rows and stale statistics.
• Workload management (WLM): automatic WLM with query priorities, queues per workload (ETL vs BI vs ad hoc), Short Query Acceleration, concurrency scaling for read bursts, query monitoring rules to abort runaway queries.
• Materialized views with auto refresh and automatic query rewrite — key for dashboard performance.
• Redshift Spectrum queries S3 tables from the Glue Catalog, enabling a hot (Redshift) / warm-cold (S3) split in one query.
• Data sharing gives live, read-only access across clusters/workgroups/accounts without copies — a producer ETL cluster and consumer BI workgroups is a classic architecture.
• Zero-ETL integrations replicate Aurora, RDS, DynamoDB and some SaaS sources into Redshift in near real time without pipelines.
• Security: IAM auth, RBAC roles, row-level security policies, dynamic data masking, column-level grants, VPC, KMS encryption, audit logging.

1.6 AWS Lake Formation

• A permissions layer over the Glue Catalog and S3. Instead of writing S3 bucket policies per path, you GRANT SELECT on databases, tables and columns, enforced in Athena, Redshift Spectrum, Glue, EMR and Quick Sight.
• LF-Tags (tag-based access control, TBAC): tag resources (domain=finance, sensitivity=pii) and grant on tag expressions. Scales far better than named-resource grants.
• Data filters: row-level (region = 'EU'), column-level (exclude email), and cell-level (both combined).
• Cross-account sharing for data mesh: producer accounts own data, a central governance account holds the catalog, consumer accounts receive grants.
• Hybrid access mode lets you migrate gradually from IAM-only to Lake Formation permissions.
• Integrates with IAM Identity Center for trusted identity propagation, so access is evaluated per end user, not per service role.

1.7 Other services you should be able to place

• Amazon EMR / EMR Serverless — when Glue is too constrained: custom Spark configs, long-running clusters, Flink, Hudi tuning, or cost at large scale.
• Kinesis Data Streams (shards, ordered per partition key, replay up to 365 days), Amazon Data Firehose (managed delivery to S3/Redshift/OpenSearch with buffering, format conversion to Parquet, and Iceberg table support), Amazon MSK (managed Kafka), Managed Service for Apache Flink.
• AWS DMS — full load + CDC from relational sources into S3 (Parquet) or Redshift.
• AWS AppFlow — SaaS ingestion (Salesforce, ServiceNow…).
• Amazon MWAA — managed Airflow. AWS Step Functions — serverless state machines, with direct Glue/Athena/Lambda integrations. EventBridge — event bus and scheduler.
• Amazon SageMaker Unified Studio / SageMaker Catalog (built on DataZone) — business data catalog, glossary, data products and subscription workflows. SageMaker Lakehouse unifies S3 and Redshift data under Iceberg-compatible access.
• Amazon Quick Sight — AWS-native BI with SPICE in-memory engine; know it as the alternative to Power BI.
• CloudWatch, CloudTrail, AWS Config, Secrets Manager, KMS, Macie (PII discovery in S3).

1.8 Choosing an engine — the question behind many questions

Part 2 — Data warehouse design that holds up as sources are added

A typical requirement in these roles is "modelling that holds up as new source systems are added". The answer interviewers want: decouple source-shaped data from business-shaped data with an integration layer keyed on business keys, then publish conformed dimensional models on top. Adding a source should mean adding mappings, not redesigning facts.

2.1 Layered architecture (medallion, or staging → integration → presentation)

Why this holds up: when a new CRM arrives, you add a bronze table and a mapping into the silver customer entity. Facts and dashboards in gold don't change shape.

2.2 Kimball dimensional modelling

• Four-step design: choose the business process (orders, shipments, invoices) → declare the grain (one row per order line) → identify dimensions (customer, product, date, store) → identify facts (quantity, net amount).
• Grain is the most important decision. Mixed grains in one fact table cause double counting. If an interviewer gives you a scenario, state the grain first.
• Fact table types: transactional (one row per event), periodic snapshot (balance per account per day), accumulating snapshot (one row per order updated as milestones happen), factless (event with no measure, e.g. attendance).
• Additivity: additive (amount), semi-additive (balances — sum across accounts, not across time), non-additive (ratios — store numerator and denominator, compute the ratio in the semantic layer).
• Surrogate keys (integer or hash) in dimensions decouple the warehouse from source keys, support SCD2 history, and allow integration of the same entity from several sources.
• Conformed dimensions — one dim_customer, one dim_date, one dim_product, shared across all fact tables. The enterprise bus matrix (processes × dimensions) is your planning tool and a great artefact to mention.
• Degenerate dimensions (order number stored in the fact), junk dimensions (low-cardinality flags combined), role-playing dimensions (date as order date and ship date), bridge tables (many-to-many, e.g. account ↔ customers), mini-dimensions (rapidly changing attributes split out).
• Unknown members: every dimension has a -1 Unknown row so facts with late or missing keys still load and stay visible, rather than being dropped by inner joins.

2.3 Slowly changing dimensions

SCD2 implementation in SQL: compute a hash of tracked attributes; MERGE where business key matches and hash differs → close the old row (valid_to = change_ts, is_current = false) and insert a new one. In dbt this is a snapshot with check or timestamp strategy. Facts look up the dimension row valid at the event time (point-in-time join), not the current row.

2.4 Data Vault 2.0 — the "new sources" specialist

• Hubs hold unique business keys (hub_customer: hash key, business key, load date, record source).
• Links hold relationships between hubs (link_customer_order).
• Satellites hold descriptive attributes and history, one per source or rate of change (sat_customer_crm, sat_customer_erp).
• Why it scales with sources: a new source adds new satellites (and maybe links) without altering existing tables. Loads are insert-only and parallelisable. Full auditability.
• Costs: many joins, steep learning curve, still needs a dimensional layer on top for BI. Business Vault adds computed satellites, PIT (point-in-time) and bridge tables for performance.
• Interview stance: "For a company with many heterogeneous, changing sources and strong audit needs I'd use a Data Vault–style integration layer (or at least its principles: business keys, hash keys, insert-only history, record source) and publish Kimball stars on top. For a smaller estate, a well-designed silver layer with conformed entities is enough."

2.5 Other modelling approaches you should be able to compare

• Inmon (3NF enterprise warehouse) — integrated normalised core, data marts downstream. Stable but slower to deliver.
• One Big Table (OBT) / wide tables — denormalised for specific dashboards; fast and simple in columnar engines, but duplicates logic. Fine as gold marts derived from stars, not as the core.
• Activity schema and entity-centric models — niche; mention only if asked.

2.6 Designing for change — concrete practices

1. Business keys and key mapping. Keep a cross-reference table: source_system, source_key → enterprise_key. New sources map into existing entities instead of creating duplicates.
2. Canonical (conformed) entities in silver: customer, product, supplier, location, each with documented attribute definitions and survivorship rules.
3. Record source and lineage columns on every row.
4. Reference data management: map source code values ('M', 'Male', 1) to standard codes in governed mapping tables, not CASE statements scattered in SQL.
5. Schema evolution policy: additive changes (new nullable columns) auto-accepted; breaking changes (type changes, renames, drops) blocked by contracts and handled with versioned models.
6. Data contracts with source owners: schema, semantics, freshness SLA, ownership, change process.
7. Model contracts in dbt (contract: enforced: true) and model versions for gold tables consumed by BI.
8. Late-arriving dimensions: insert an inferred member with the business key, update attributes when the real record arrives.
9. Late-arriving facts: load into the correct partition using event time; reprocess affected aggregates with a lookback window.
10. Multi-currency, multi-timezone: store UTC timestamps plus local date keys; store transaction currency and converted reporting currency with the rate date.

2.7 Redshift physical design for the gold layer

• Large fact: DISTKEY on the most frequent large join column (if distribution is even), compound SORTKEY starting with the date column.
• Small dimensions (\< a few million rows): DISTSTYLE ALL. Large dimension joined to the fact: same DISTKEY as the fact.
• When in doubt use AUTO and verify with SVV_TABLE_INFO (skew, unsorted %), STL_ALERT_EVENT_LOG, and EXPLAIN.
• Use materialized views for heavy dashboard aggregations; data sharing to isolate BI compute from ETL compute.
• Keep cold history in Iceberg on S3 and query via Spectrum if dashboards rarely touch it.

2.8 dbt as the transformation framework

• Project structure: staging (1:1 source, rename and cast) → intermediate (joins, business logic) → marts (facts and dimensions).
• Materialisations: view, table, incremental (merge, delete+insert, append, insert_overwrite strategies), ephemeral, snapshot.
• Adapters: dbt-redshift, dbt-athena (supports Iceberg), dbt-glue, dbt-spark.
• Tests (unique, not_null, relationships, accepted_values, dbt-expectations), documentation and lineage graph, exposures for dashboards, source freshness checks.
• Your existing dbt experience transfers directly — say so.

Part 3 — Ingestion and orchestration engineering

A production ingestion architecture lands every source unchanged in S3, processes it incrementally and idempotently, and lets nothing reach consumers without passing automated quality gates. Orchestration ties these steps together with dependencies, retries, alerting and backfill support.

3.1 Ingestion patterns by source type

3.2 Batch vs incremental vs streaming

• Full load — simple, self-healing, catches deletes; expensive for large tables. Fine for small dimensions and reference data.
• Incremental by watermark — fast; misses hard deletes and rows whose updated_at isn't maintained. Mitigate with a lookback window (re-read last N hours) and periodic full reconciliation.
• CDC (log-based) — captures inserts, updates and deletes with low source impact; requires ordering and dedup logic (op, commit timestamp/LSN). Apply as MERGE keeping the latest event per key.
• Micro-batch / streaming — use when the business needs minutes, not hours. Each step up in freshness costs complexity; always ask "what decision needs this freshness?"

3.3 Idempotency, reruns and backfills

The core principle: running a pipeline twice for the same input produces the same result.

• Partition overwrite: each run writes dt=X completely (Spark dynamic partition overwrite, Iceberg overwrite by partition, insert_overwrite in dbt).
• MERGE on business key for upserts, with deterministic ordering (latest commit_ts, then lsn).
• Parameterise by logical date, not "now". The orchestrator passes the interval; the job processes exactly that interval. Backfill = run the same job for past dates.
• Write-audit-publish (WAP): write to a staging branch or table, audit with quality checks, publish atomically (Iceberg branches/tags, or swap via ALTER TABLE ... RENAME / INSERT in a transaction). Consumers never see half-written or bad data.
• Exactly-once vs at-least-once: most sources deliver at least once, so make the sink idempotent (dedup on event ID) rather than chasing exactly-once transport.

3.4 CDC details interviewers probe

• DMS adds an Op column (I, U, D) and can include transaction commit timestamps. Ongoing replication needs source log settings (binlog row format in MySQL, logical replication in Postgres, supplemental logging in Oracle).
• Ordering: within a key, apply events by commit sequence; across a batch, deduplicate with ROW_NUMBER() OVER (PARTITION BY pk ORDER BY commit_ts DESC, seq DESC) = 1.
• Deletes: soft-delete in silver (is_deleted, deleted_at) to preserve history; hard-delete only for privacy requests.
• Schema changes in source: DMS can propagate DDL; your pipeline must detect and handle new columns (contract check) instead of failing silently.
• Initial load + CDC cutover: record the log position at full-load start so no changes are lost or doubled.

3.5 Quality gates in the pipeline

Quality checks are pipeline steps with a decision, not reports nobody reads.

1. Ingestion gate (bronze): file arrived, schema matches contract, row count > 0 and within expected range, no parse errors. Failures → quarantine the batch, alert source owner.
2. Transformation gate (silver): primary key unique, not-null on required fields, referential integrity to dimensions, accepted values, dedup ratio within expected range.
3. Publication gate (gold): reconciliation to source totals, metric deltas vs yesterday within thresholds, freshness met. Failure → don't publish; dashboards keep showing yesterday's correct data with a staleness banner.

Severity levels: block (stop the pipeline), warn (publish and alert), quarantine (route bad rows to an error table, load the rest). Choose per rule and agree with the business.

Tools: Glue Data Quality (DQDL rulesets, results to CloudWatch/EventBridge), dbt tests and source freshness, Great Expectations, Deequ/PyDeequ on Spark, Soda.

3.6 Orchestration

Orchestration design principles: dependencies on data (dataset/asset-aware scheduling, S3 or table sensors) rather than on clock time; retries with exponential backoff for transient errors only; timeouts and SLAs per task; catchup and max_active_runs set deliberately; no business logic in the orchestrator — it calls jobs; secrets from Secrets Manager; DAGs deployed through CI/CD.

3.7 Monitoring and observability

• Pipeline health: job success/failure, duration vs baseline, retries — CloudWatch metrics and alarms, Airflow callbacks, EventBridge rules on Glue job state changes → SNS/Slack/PagerDuty.
• Data health (data observability): freshness (max event time vs now), volume (row counts vs rolling average), schema changes, distribution drift, null-rate spikes.
• Cost: Glue DPU-hours per job, Athena bytes scanned per workgroup, Redshift RPU usage; tag resources by domain and pipeline.
• Run metadata table: pipeline, run_id, logical_date, source_rows, loaded_rows, rejected_rows, status, started_at, ended_at — the backbone of reconciliation and SLA reporting.
• Alerting hygiene: alert on symptoms users feel (stale dashboard, wrong totals), route to owners, include runbook links, avoid alert fatigue.

3.8 CI/CD and infrastructure as code

• Terraform (or CDK) for buckets, KMS keys, Glue jobs, catalog databases, Lake Formation grants, Redshift workgroups, MWAA environments.
• Separate accounts for dev / test / prod (AWS Organizations), promoted through pipelines (GitHub Actions, CodePipeline).
• dbt: slim CI (state:modified+), tests on PR against a test schema, deploy on merge.
• Unit tests for PySpark transformations (pytest with small local DataFrames), integration tests on sample data.

Part 4 — Data quality and hygiene

The role asks for someone who has "made data trustworthy, not just moved it". Trust comes from four things done systematically: measuring quality on defined dimensions, fixing data with explicit and versioned rules, proving totals match across systems, and making issues visible to owners who fix them at source.

4.1 Quality dimensions (use this vocabulary)

4.2 Cleansing — standard transformations

• Typing and parsing: strings to dates/decimals with explicit formats; reject or quarantine unparseable values instead of silently casting to null.
• Standardisation: trim, case-normalise, remove control characters, unify encodings (UTF-8), standard phone (E.164) and address formats, country/currency codes.
• Reference mapping: source codes → canonical codes via governed mapping tables. Unmapped codes are flagged and counted, not dropped.
• Null semantics: distinguish "unknown", "not applicable" and "empty string"; document defaults.
• Outliers: flag (z-score, IQR, business bounds) rather than delete; let owners decide.
• Units and currencies: convert to a reporting standard while retaining originals.
• Timestamps: convert to UTC, keep source time zone; watch for DST and epoch-in-milliseconds bugs.

4.3 Deduplication — three different problems

1. Technical duplicates (same record delivered twice — retries, overlapping extracts, at-least-once streams). Fix with idempotent loads and ROW_NUMBER() over the primary key ordered by load/commit time.
2. Versioned duplicates (several versions of the same record over time). Keep the latest for current state; keep all in history (SCD2/satellites).
3. Entity duplicates (the same real-world customer in CRM, e-commerce and billing with different IDs and spellings). This is entity resolution / master data management.

Entity resolution steps:

1. Standardise matching attributes (name, email, phone, address, tax ID).
2. Blocking to avoid comparing every pair: candidate pairs only within the same postcode, email domain or phonetic name key (Soundex/Metaphone).
3. Matching: deterministic rules (same normalised email or tax ID = match) first; then probabilistic/fuzzy scoring (Jaro-Winkler, Levenshtein, token set similarity) with weights — the Fellegi-Sunter model, or Splink on Spark, or AWS Entity Resolution as a managed service.
4. Thresholds: above X auto-merge, between X and Y send to a data steward review queue, below Y no match.
5. Clustering: connected components so A=B and B=C become one entity.
6. Survivorship rules: which source wins per attribute (most recent, most trusted source, most complete). Keep the cross-reference of all source IDs to the golden ID.
7. Stable golden IDs: don't regenerate on every run; merges and splits must be tracked so facts can be remapped.

4.4 Validation rules — how to manage them

• Rules live in code (DQDL, dbt YAML, Great Expectations suites), version-controlled and reviewed like any code.
• Each rule has an owner, severity, threshold, and action (block/warn/quarantine).
• Start with rules derived from profiling (Glue DQ recommendations, ydata-profiling), then add business rules from domain experts.
• Distinguish technical rules (types, nulls, keys) from business rules (discount ≤ 50%, ship date ≥ order date).
• Track rule results over time in a quality results table → a data quality dashboard with scores per domain and dataset. This is how you show progress to leadership.

4.5 Reconciliation across sources

Reconciliation proves the warehouse matches the systems of record — the single best way to win business trust.

• Count reconciliation: rows extracted = rows landed = rows loaded + rows rejected, per batch (from the run metadata table).
• Sum/control totals: sum of amount per day/company in source vs warehouse; hash totals on keys.
• Key-level reconciliation: full outer join of source keys vs warehouse keys to find missing and extra records (sample or daily for big tables).
• Cross-system reconciliation: e-commerce orders vs ERP invoices vs payment provider settlements — explain expected differences (timing, cancellations, FX) and categorise breaks.
• Financial reconciliation: warehouse revenue vs general ledger at month-end, with a signed-off variance tolerance.
• Output: a reconciliation report with status per check, trends, and break investigation workflow. Automate it as a gate before publishing gold.

4.6 Making quality stick (the organisational side)

• Fix at source where possible; the warehouse cleanses symptoms, owners fix causes.
• Assign data owners (accountable, business) and data stewards (hands-on).
• Publish quality SLAs and scorecards; include known issues in the catalog.
• Quarantine with visibility: an error table plus a dashboard owners actually check.
• Root-cause analysis with lineage: which upstream change caused the break?

4.7 A story to prepare (STAR)

Prepare one concrete story from your own work, ideally with numbers. Structure: the messy situation (e.g. three systems with overlapping customers, 18% duplicate rate, revenue mismatching finance by 4%) → what you built (standardisation, matching rules, survivorship, reconciliation gate) → measurable result (duplicates down to under 1%, variance within 0.1%, finance signed off the dashboard). Interviewers will dig into the matching rules and how you handled false merges.

Part 5 — Data governance: lineage, access control, PII

Governance on AWS rests on three controls: a catalog that knows what data exists and where it came from (Glue Catalog + SageMaker Catalog/DataZone + lineage), centralised fine-grained permissions (Lake Formation, Redshift RBAC/RLS/masking), and a PII lifecycle (discover, classify, protect, minimise, delete). Governance should enable safe self-service, not block it.

5.1 Governance building blocks

• Ownership model: data owner (business accountability), data steward (definitions and quality), data custodian (platform team, technical controls).
• Policies: classification levels (public, internal, confidential, restricted/PII), retention, acceptable use, access request process.
• Catalog and glossary: technical metadata (schemas, partitions) + business metadata (descriptions, owners, glossary terms, certification status).
• Data products / data mesh: domains publish curated datasets with contracts and SLAs; a central platform provides tooling and guardrails. On AWS: producer accounts, central Lake Formation governance account, SageMaker Catalog for discovery and subscriptions.

5.2 Lineage

• Why: impact analysis before changes, root cause of bad numbers, regulatory evidence (where did this PII go?), trust in metrics.
• Levels: dataset-level (table A → table B), column-level (field-to-field), and business lineage (source system → KPI on dashboard).
• How on AWS: OpenLineage events emitted from Spark (Glue/EMR), Airflow (MWAA) and dbt; collected in Amazon DataZone / SageMaker Catalog lineage (OpenLineage-compatible), or Marquez, DataHub, OpenMetadata, Atlan, Collibra. dbt docs give a model-level lineage graph for free; dbt exposures extend it to dashboards. Power BI has its own lineage view from source to report.
• Practical answer: "I make lineage a by-product of the pipeline — OpenLineage from Spark and Airflow, dbt manifests, and exposures for BI — rather than a manual documentation exercise that goes stale."

5.3 Access control

Principles: least privilege, role-based (and attribute/tag-based) access, separation of duties, everything granted through code and auditable.

RBAC vs ABAC: RBAC grants by role ("finance\_analyst"); ABAC/TBAC grants by attributes and tags ("users with clearance=pii may read resources tagged pii in their region"). TBAC scales better as tables multiply — new tables inherit access from tags.

Service access: jobs run with dedicated IAM roles per pipeline/domain, no long-lived keys, secrets in Secrets Manager with rotation.

5.4 PII handling — the lifecycle

1. Discover: Amazon Macie scans S3 for PII; Glue sensitive data detection in ETL jobs; Glue DQ/profiling; column naming conventions are not enough.
2. Classify and tag: apply LF-Tags / catalog classifications (pii=direct, pii=indirect, pii=none); record in the glossary.
3. Minimise: don't ingest PII you don't need; drop or aggregate early.
4. Protect:
   • Encryption at rest (SSE-KMS, separate keys for PII zones) and in transit (TLS).
   • Pseudonymisation / tokenisation: replace identifiers with keyed hashes (HMAC-SHA256 with a secret key in KMS/Secrets Manager — plain unsalted hashes of emails are reversible by dictionary attack) or vault-based tokens. Analysts join on tokens without seeing raw values.
   • Masking: dynamic masking in Redshift (j***@domain.com) for most users; full values only for privileged roles.
   • Column/cell-level security via Lake Formation and Redshift.
   • Separate restricted zone/schema for raw PII with tightly controlled access.
5. Retain and delete: retention policies per dataset; GDPR right to erasure implemented with row-level deletes in Iceberg/Redshift, plus snapshot expiry so deleted data doesn't survive in time travel, plus handling in backups and downstream extracts. Alternatively crypto-shredding: encrypt each subject's data with a per-subject key and delete the key.
6. Monitor: audit who accessed PII (CloudTrail data events, Redshift logs), alert on unusual access, periodic access recertification.

Regulatory context: GDPR (EU) and Serbia's Law on Personal Data Protection (closely aligned with GDPR) — lawful basis, purpose limitation, data subject rights, breach notification within 72 hours, DPIAs for high-risk processing, data residency (choose regions deliberately). Mention HIPAA/PCI DSS only if relevant to the company's industry.

5.5 Governance as code

• Lake Formation permissions, LF-Tags, Redshift grants and masking policies defined in Terraform/CDK or SQL migrations, reviewed in PRs.
• Classification and owners declared in dbt model YAML (meta: {owner, pii, classification}) and synced to the catalog.
• Automated checks: CI fails if a new model contains a column tagged PII without a masking policy or a description.
• Access requests via a workflow (SageMaker Catalog subscriptions, ticket + approval), never ad hoc console clicks.

Part 6 — The semantic layer

A semantic layer is the single, governed place where business entities (customer, order, product) and metrics (net revenue, active customer, churn rate) are defined once and reused by every dashboard, notebook and AI assistant. Without it, three dashboards show three different "revenue" numbers and trust collapses.

6.1 What it contains

• Entities and relationships: which tables represent which business objects, how they join (keys, cardinality, direction).
• Metric definitions: formula, filters, grain, time logic, owner, version. Example: Net revenue = sum(line\_amount − discount − returns) for orders with status not in (cancelled, test), in reporting currency, by order date.
• Dimensions and hierarchies: date (day → week → month → quarter → year, fiscal calendar), geography, product category tree, org hierarchy.
• Business-friendly naming, descriptions, formats, default aggregations.
• Security: row-level and object-level rules applied consistently.

6.2 Where to implement it — options

A pragmatic answer for this role (Power BI on AWS): define entities and atomic, additive measures in conformed gold tables in Redshift (built and tested with dbt); document every metric in a metric catalogue with owner and formula; implement the official measures once in a shared, certified Power BI semantic model (DAX); all reports connect to that model via live connection instead of importing their own copies. If more tools come later, move definitions upstream into dbt Semantic Layer or Cube.

6.3 Design rules for metrics

1. Store additive components in tables (numerator, denominator, counts); compute ratios in the semantic layer, at query time, so they aggregate correctly.
2. Distinct counts (active customers) cannot be summed across days — define the window explicitly (DAU, MAU, rolling 28 days).
3. Semi-additive measures (inventory, balances) use last-non-empty logic over time.
4. Time intelligence (YTD, YoY, rolling 12 months) is defined once with a proper date dimension, including fiscal calendars.
5. Every metric has an owner, a definition in plain language, the SQL/DAX, known caveats, and a certification status.
6. Changes to definitions are versioned and communicated; historic dashboards should state which definition they use.

6.4 Running the definitions process

• Start with the 10–20 KPIs leadership actually uses. Interview stakeholders; you'll find conflicting definitions — surface them, don't silently pick one.
• Run a short metric definition workshop per domain: draft, challenge with real numbers, agree, sign off by the owner.
• Publish in a business glossary (SageMaker Catalog/DataZone glossary, Confluence, or the catalog of choice) linked from the semantic model descriptions.
• Certify datasets and models (Power BI endorsement: Promoted/Certified) so users know what to trust.
• Measure adoption: share of reports built on certified models vs personal imports.

6.5 Semantic layer and AI

Natural-language query tools (Power BI Copilot, Quick Sight Q/topics, text-to-SQL agents) only give correct answers when they sit on a well-named semantic model with descriptions and synonyms. A strong closing point in interviews: "The semantic layer is also our AI readiness layer."

Part 7 — First reporting iteration in Power BI

The first iteration should ship a small set of trusted, reconciled dashboards on a certified semantic model within weeks, not a hundred reports. Power BI on AWS means connecting to Redshift (or Athena), choosing the right storage mode, modelling a star schema in Power BI, writing correct DAX measures, and enforcing row-level security.

7.1 Power BI architecture in one paragraph

Power BI Desktop is the authoring tool; you build a semantic model (formerly "dataset": tables, relationships, DAX measures) and reports (pages of visuals) on it. You publish to the Power BI Service into workspaces (dev/test/prod, with deployment pipelines). Dashboards are pinned tiles; apps package reports for audiences. Data refresh for cloud sources runs from the Service; private sources need the on-premises data gateway (installed on an EC2 instance inside your VPC for AWS). Capacity: Pro (per user) or Premium/Fabric capacity (F SKUs) for larger models, more refreshes and wider distribution.

7.2 Connecting to AWS

• Amazon Redshift connector — native, supports Import and DirectQuery; supports SSO with Microsoft Entra ID (via Redshift identity provider integration) so RLS in Redshift can apply per user.
• Network: if Redshift is private (it should be), install the on-premises data gateway on EC2 in the same VPC, in a cluster of two for high availability. Keep the gateway close to Redshift to reduce latency.
• Athena — via the Simba ODBC driver / Athena connector through the gateway; fine for lower-concurrency use, mind scan costs from DirectQuery.
• Best practice: point Power BI at a dedicated reporting schema of gold tables/views with business-friendly column names — never at bronze or silver.

7.3 Storage modes

Incremental refresh (with RangeStart/RangeEnd parameters and query folding) lets large Import models refresh only recent partitions. Mention query folding: Power Query steps must translate to SQL on Redshift, otherwise refresh pulls everything.

7.4 Modelling inside Power BI

• Star schema mirroring the warehouse: fact tables and dimension tables, one-to-many relationships from dimension to fact, single filter direction. Avoid bi-directional relationships unless necessary (bridge tables).
• A proper date table marked as date table, covering the full range, with fiscal columns. Role-playing dates: inactive relationships activated with USERELATIONSHIP.
• Hide technical keys; use display folders; set formats and default summarisation; add descriptions (they feed Copilot and Q&A).
• Reduce model size: remove unused columns, avoid high-cardinality text and datetime columns (split date and time), integer keys.

7.5 DAX essentials you must be able to explain

• Measures vs calculated columns: measures compute at query time in filter context (use for aggregations); calculated columns compute at refresh, row by row, and consume memory (avoid where possible — push to the warehouse).
• Row context vs filter context; CALCULATE modifies filter context and performs context transition.
• Core functions: SUM, DIVIDE (safe division), CALCULATE, FILTER, ALL/REMOVEFILTERS, SUMX/iterators, DISTINCTCOUNT, SELECTEDVALUE, VAR/RETURN.
• Time intelligence: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESINPERIOD (rolling windows).

Example measures:

Net Revenue = SUM ( fct_sales[net_amount] )

Net Revenue PY = CALCULATE ( [Net Revenue], SAMEPERIODLASTYEAR ( dim_date[date] ) )

Net Revenue YoY % = DIVIDE ( [Net Revenue] - [Net Revenue PY], [Net Revenue PY] )

Gross Margin % = DIVIDE ( SUM ( fct_sales[gross_margin] ), [Net Revenue] )

Active Customers = DISTINCTCOUNT ( fct_sales[customer_key] )

Share of Total = DIVIDE ( [Net Revenue], CALCULATE ( [Net Revenue], REMOVEFILTERS ( dim_product ) ) )

7.6 Drill-down and drill-through

• Drill-down within a visual along a hierarchy (Year → Quarter → Month → Day; Region → Country → Store). Requires hierarchies in the model.
• Drill-through to a detail page filtered by the clicked item (e.g. from a store on the overview to the store detail page). Configure drill-through fields on the target page; add a back button.
• Tooltips pages for context on hover; bookmarks for saved views; field parameters to let users switch measures/dimensions; decomposition tree for root-cause exploration.
• Detail tables at the bottom of drill paths should hit DirectQuery or paginated reports if row-level detail is large.

7.7 Operational KPIs and dashboard design

• Start from decisions: "What will the operations manager do differently after looking at this?" Each KPI has a target, owner, and threshold.
• Typical first set: revenue and margin vs target, orders and AOV, on-time delivery %, stock-outs, backlog, customer acquisition and churn, data freshness indicator.
• Layout: top row KPI cards with trend and variance to target/PY; middle trends and breakdowns; bottom detail. Five to eight visuals per page; consistent colours with meaning (red = bad vs target only).
• Show data freshness ("Data as of 06:05 UTC") on every page — it directly builds trust.
• Performance: Performance Analyzer in Desktop; aim for visuals under 2–3 seconds.

7.8 Security and governance in Power BI

• Row-level security (RLS): roles with DAX filters, dynamic RLS using USERPRINCIPALNAME() joined to a user-to-region mapping table. Test with "View as".
• Object-level security (OLS) hides tables/columns (e.g. salary) from roles.
• With DirectQuery + SSO, Redshift RLS can apply instead; avoid implementing the same rules twice inconsistently.
• Workspace roles (Admin/Member/Contributor/Viewer), apps for consumers, sensitivity labels (Microsoft Purview), endorsement (Promoted/Certified), lineage view and impact analysis.
• Deployment pipelines (dev → test → prod) and Git integration / PBIP format for version control; parameters for environment connection strings.

7.9 Delivering the first iteration — a plan you can narrate

1. Weeks 1–2: stakeholder interviews; pick one domain and 8–12 KPIs; define metrics and grain; confirm source availability.
2. Weeks 2–5: ingest the needed sources, build silver entities and the gold star schema, quality and reconciliation gates; reconcile KPIs against finance/operations numbers.
3. Weeks 5–7: certified semantic model, 2–3 report pages (overview, drill-down, detail), RLS, freshness indicator; UAT with power users.
4. Week 8: release as a Power BI app, training session, feedback loop; usage metrics monitored.
5. Next iterations: add domains on the same conformed dimensions; retire legacy Excel reports once numbers match.

Part 8 — Interview Q&A bank

Answer each question aloud in under two minutes before reading the model answer. Good answers follow one pattern: state the principle, give the AWS mechanism, name the trade-off, add a short example from your experience.

8.1 AWS platform and architecture

Q1. Walk me through how you would design a data platform on AWS from scratch. Start from requirements: sources, latency, users, compliance, budget. Then: multi-account setup (dev/test/prod, governance account) with Terraform; S3 lake in raw/bronze/silver/gold zones with Iceberg tables; Glue Data Catalog plus Lake Formation for permissions; ingestion via DMS for databases, AppFlow/Lambda for SaaS, Firehose for events; transformations in Glue Spark and dbt; Redshift Serverless for the gold warehouse and BI concurrency; MWAA orchestration; quality gates with Glue DQ and dbt tests; OpenLineage and a catalog for discovery; Power BI via a gateway in the VPC; CloudWatch monitoring and cost tagging. Close with: "I'd deliver one domain end to end first, then scale the pattern."

Q2. Data lake, data warehouse, lakehouse — what's the difference and what would you choose? A lake stores any data cheaply in open formats but lacks transactions and governance by default. A warehouse gives fast, governed SQL on structured data but is proprietary and costlier for raw volume. A lakehouse adds table formats (Iceberg) to the lake for ACID and schema enforcement, so multiple engines share one copy. On AWS I'd use a lakehouse for raw-to-silver and a Redshift warehouse for curated gold where BI concurrency and latency matter — the two connect through Spectrum, data sharing and Iceberg.

Q3. When would you use Athena vs Redshift? Athena for ad hoc, infrequent, or exploratory queries on S3 — no infrastructure, pay per TB scanned. Redshift for predictable, high-concurrency, low-latency workloads like dashboards, complex joins and heavy transformation. Rule of thumb: if many users run the same queries all day, Redshift's caching, materialized views and WLM win on both speed and cost.

Q4. Glue vs EMR vs Lambda for transformations? Lambda for small, event-driven tasks under 15 minutes and limited memory (file validation, API pulls). Glue for serverless Spark ETL with catalog integration and minimal ops. EMR (or EMR Serverless) when I need custom Spark/Hadoop configs, other frameworks (Flink, Hudi tuning), long-running clusters, spot fleets, or lower cost at very large scale.

Q5. Explain Redshift distribution and sort keys. How do you choose them? Distribution decides which slice stores each row. KEY co-locates rows with the same join key to avoid shuffles; ALL replicates small dimensions; EVEN spreads round-robin; AUTO lets Redshift adapt. Choose KEY on the large fact's most common join column with high cardinality and even spread. Sort keys order data on disk so zone maps skip blocks; choose the column most often in range filters, typically the date. I validate with EXPLAIN (look for DS\_BCAST/DS\_DIST) and SVV\_TABLE\_INFO (skew, unsorted).

Q6. A Redshift dashboard query got slow. How do you troubleshoot? Check whether it's the query or the system: queue wait time in SYS\_QUERY\_HISTORY / STL tables vs execution time. If queueing: WLM configuration, concurrency scaling, isolate BI from ETL (data sharing). If execution: EXPLAIN for redistribution and nested loops, stale stats (ANALYZE), unsorted data, skew, missing filters on sort key, spilling to disk. Fixes: better dist/sort keys, materialized views, pre-aggregation, result caching, rewriting the query.

Q7. What is the small files problem and how do you fix it? Many small files multiply open/list overhead and metadata, slowing Spark, Athena and Spectrum. Causes: streaming, over-partitioning, many small batches. Fix by buffering (Firehose buffer size), coalescing before write, compaction jobs (Iceberg rewrite\_data\_files, Athena OPTIMIZE, S3 Tables automatic compaction), and coarser partitions.

Q8. Why Iceberg? What does it give you over plain Parquet? ACID commits, so readers never see partial writes; row-level MERGE/UPDATE/DELETE for CDC and GDPR; schema evolution by column ID (safe renames); hidden partitioning and partition evolution; time travel and rollback; snapshot isolation for concurrent writers; engine interoperability across Athena, Glue, EMR, Redshift, Snowflake, Databricks.

Q9. How do you control AWS data platform costs? Tag everything by domain/pipeline; S3 lifecycle and Intelligent-Tiering; Parquet and partitioning to reduce Athena scans; workgroup scan limits; Glue auto scaling, Flex jobs and right-sized workers; Redshift Serverless base RPU and usage limits, or reserved RA3 nodes for steady load; pause idle dev resources; avoid repeated full reloads via incremental processing; monitor with Cost Explorer and per-pipeline cost dashboards.

Q10. How do you design for multiple AWS accounts? AWS Organizations with separate accounts for environments and, in larger setups, for domains (data mesh). A central governance account owns the Glue Catalog and Lake Formation; producer accounts write data; consumer accounts get cross-account grants. SCPs enforce guardrails (no public buckets, approved regions). IAM Identity Center for human access.

8.2 Data modelling

Q11. How do you design a warehouse that holds up as new sources are added? Separate source-shaped layers from business-shaped layers. Land each source raw, stage it 1:1, then integrate into source-agnostic conformed entities keyed on business keys with a key cross-reference table. New sources add mappings and satellites, not new fact designs. Publish Kimball stars with conformed dimensions and surrogate keys. Enforce contracts on the gold layer so BI is insulated from source change.

Q12. Kimball vs Inmon vs Data Vault? Kimball: dimensional stars, fast to deliver, business-friendly, but integration logic can get scattered. Inmon: normalised enterprise core first, marts later — consistent but slower. Data Vault: hubs, links, satellites; insert-only, auditable, very resilient to new sources, but needs a presentation layer and is join-heavy. My default: Data Vault principles or a clean integration layer underneath, Kimball on top.

Q13. What is the grain of a fact table and why does it matter? The grain is what one row represents, e.g. one order line. It determines which dimensions can attach and which measures are valid. Mixing grains — order header amounts on line rows — causes double counting. I declare the grain before anything else.

Q14. Explain SCD Type 2 and how you'd implement it. Keep full history by adding a new dimension row when tracked attributes change, with valid\_from, valid\_to and is\_current. Implement with a hash of tracked attributes and a MERGE (close old row, insert new), or dbt snapshots. Facts join to the version valid at the event time. Watch out for late-arriving changes and for attributes that should be type 1 to avoid row explosion.

Q15. How do you handle late-arriving dimensions and facts? Dimensions: create an inferred member with the business key and placeholder attributes so the fact loads; update it when real data arrives. Facts: partition by event date, process with a lookback window, and recompute affected aggregates. Unknown member rows (-1) prevent facts from disappearing in inner joins.

Q16. Star schema or one big table for BI? Star schema as the governed core — conformed, reusable, smaller in memory, and what Power BI's engine is optimised for. Wide tables are acceptable as derived marts for specific heavy use cases, but not as the system of record for definitions.

Q17. How do you model many-to-many relationships? With a bridge table (e.g. account\_customer bridge with an allocation weight) between the dimension and the fact, and careful handling in BI to avoid double counting — weighted measures or explicit filter direction.

8.3 Ingestion and orchestration

Q18. Batch vs streaming — how do you decide? By the decision latency the business needs and the cost of complexity. Most reporting needs hourly or daily; streaming is justified for fraud, operations monitoring or customer-facing features. I often use micro-batch on the lake (Firehose to Iceberg every few minutes) as a middle ground.

Q19. How do you implement incremental loading? Watermark-based (updated\_at greater than the last successful value, with a lookback overlap) or log-based CDC (DMS). Persist the watermark only after successful commit. Make the target write idempotent with MERGE on keys or partition overwrite. Periodically reconcile with a full comparison to catch missed deletes.

Q20. What does idempotent mean for a pipeline and how do you achieve it? Re-running the same run produces the same end state, no duplicates. Achieve via deterministic inputs (logical date parameters), partition overwrite or MERGE on business keys, dedup on event IDs, and atomic publication (write-audit-publish).

Q21. How do you handle CDC data with updates and deletes? Land all change events in bronze; in silver, keep the latest event per primary key by commit timestamp and sequence; apply with MERGE — insert, update, or soft-delete based on the operation flag. Keep full history in a change log table for auditing. Plan the full-load-to-CDC cutover using the source log position.

Q22. How do you handle schema changes in sources? Detect them at ingestion by comparing to the registered contract. Additive changes (new nullable columns) are accepted automatically with Iceberg schema evolution and flagged. Breaking changes (type change, drop, rename) fail the gate, quarantine the batch and alert the source owner. Gold models with enforced contracts insulate dashboards.

Q23. MWAA or Step Functions? MWAA for many interdependent, schedule-based pipelines with backfills, sensors and a Python-first data team. Step Functions for event-driven, serverless, AWS-native workflows with low idle cost. They also combine: EventBridge triggers Step Functions per file, MWAA orchestrates the daily warehouse DAG.

Q24. What makes a good Airflow DAG? Tasks are small and idempotent; DAG only orchestrates (heavy work in Glue/Redshift/dbt); logical date parameters; retries with backoff; SLAs and failure callbacks; no top-level heavy code; data-aware scheduling via datasets; tested and deployed through CI; secrets from Secrets Manager.

Q25. How do you monitor pipelines? Two levels: pipeline health (success, duration, retries — CloudWatch alarms, EventBridge on Glue state changes, Airflow callbacks to Slack/PagerDuty) and data health (freshness, volume, schema, distribution, quality rule results). A run metadata table and a platform health dashboard. Alerts route to owners with runbooks.

Q26. A daily job failed at 3 a.m. and dashboards must be ready at 7. What do you do? Triage: check logs and whether it's transient (retry) or data-related (bad file, schema change). If a fix is quick, fix and rerun idempotently for that logical date. If not, keep yesterday's data published, show a staleness banner, inform stakeholders proactively with an ETA. Afterwards, a blameless post-mortem and a new check or test so the same failure is caught earlier.

8.4 Data quality and hygiene

Q27. How do you make messy source data trustworthy? Profile first to measure the mess; define quality dimensions and rules with owners; cleanse and standardise with versioned rules; deduplicate and resolve entities; reconcile totals against systems of record; gate publication on quality; publish quality scores; push fixes back to source owners. Trust is earned when the business sees reconciled numbers that match finance.

Q28. How would you deduplicate customers across CRM, e-commerce and billing? Standardise names, emails, phones and addresses; block candidates; apply deterministic rules (email, tax ID) then fuzzy scoring (Jaro-Winkler on names, address similarity) with thresholds — auto-merge, steward review, no match; cluster matches; apply survivorship rules per attribute; maintain a cross-reference of source IDs to a stable golden ID. Splink on Spark or AWS Entity Resolution can do the matching at scale.

Q29. Where should data quality checks run? At every layer boundary: ingestion (schema, counts, parse errors), transformation (keys, nulls, referential integrity, business rules), publication (reconciliation, metric deltas, freshness). Plus continuous observability on gold tables. Checks closest to the source are cheapest to fix.

Q30. What do you do with records that fail validation? Depends on severity agreed with the business: block the whole batch for critical failures; quarantine failing rows into an error table with the reason and load the rest; or load with a warning flag. Quarantined rows are visible on a dashboard and reprocessed after correction.

Q31. How do you reconcile the warehouse with source systems? Automated checks per batch: row counts from extract to load, control totals (sum of amounts per day/company), key-level comparisons for missing/extra records, and periodic reconciliation with the general ledger. Expected differences (timing, cancellations, FX) are categorised. The reconciliation result is a publish gate.

Q32. Give an example of a data quality issue you found and fixed. Use your prepared STAR story (Part 4.7): situation with numbers, root cause analysis via lineage, technical fix, process fix at source, measured result.

8.5 Governance, security, PII

Q33. How does Lake Formation differ from IAM for data access? IAM controls API and S3 object access with coarse path-based policies. Lake Formation adds database/table/column/row/cell permissions on catalog objects, enforced consistently across Athena, Redshift Spectrum, EMR, Glue and Quick Sight, with tag-based access control and cross-account sharing — a SQL-like grant model instead of S3 policies.

Q34. How do you protect PII in the analytics platform? Discover (Macie, Glue sensitive data detection), classify and tag (LF-Tags), minimise collection, encrypt with KMS, pseudonymise identifiers with keyed hashes or tokens, mask dynamically in Redshift, restrict raw PII to a separate zone with audited access, and implement retention and deletion (Iceberg deletes plus snapshot expiry, or crypto-shredding).

Q35. How do you implement a GDPR deletion request in a lakehouse? Find the subject's records via the identity/key map; delete or anonymise in every layer where personal data lives (Iceberg DELETE in silver/gold, Redshift DELETE), expire snapshots so time travel no longer holds it, handle raw immutable landing data (short retention, or encryption with per-subject keys), propagate to downstream extracts, and log the request for evidence.

Q36. How do you implement lineage? Automatically: OpenLineage from Glue/EMR Spark and Airflow, dbt manifests and exposures, collected into a catalog (DataZone/SageMaker Catalog, DataHub, OpenMetadata). Use it for impact analysis before changes and root-cause analysis after incidents.

Q37. Row-level security: in the warehouse or in Power BI? Prefer one authoritative place. Import models need RLS in Power BI (dynamic RLS with a user mapping table). DirectQuery with SSO can rely on Redshift RLS. Lake consumers get Lake Formation row filters. The mapping table of user to entitlement should come from one governed source used by all layers.

8.6 Semantic layer and BI

Q38. What is a semantic layer and why do we need one? A governed layer defining business entities, relationships and metrics once, used by every consumer. It prevents conflicting numbers, speeds up report building, centralises security, and makes natural-language/AI querying reliable.

Q39. Two departments define "active customer" differently. What do you do? Don't pick silently. Surface both definitions with real numbers, bring owners together, agree on an enterprise definition with a named owner, and if both are genuinely needed, publish two clearly named metrics ("Active customer – 90-day purchase", "Active customer – logged in 30 days"). Document in the glossary and certify.

Q40. Import or DirectQuery for Power BI on Redshift? Import by default for speed and full DAX, with incremental refresh for big facts. DirectQuery when data is too large, freshness must be near real time, or security must be enforced in the source — with Redshift sized for the concurrency. Composite models with aggregation tables combine both.

Q41. Measure vs calculated column in Power BI? Measures compute at query time in filter context and don't consume model memory — use for all aggregations. Calculated columns compute at refresh per row and are stored — use only when you need to slice or filter by the value, and preferably compute it upstream in the warehouse.

Q42. What does CALCULATE do? It evaluates an expression in a modified filter context: adds, replaces or removes filters, and converts row context to filter context when used inside an iterator or calculated column. It's the basis of time intelligence and "share of total" measures.

Q43. How do you make dashboards fast? Star schema, slim model, measures not calculated columns, Import with incremental refresh, aggregations for large DirectQuery facts, fewer visuals per page, avoid high-cardinality slicers, Redshift materialized views for DirectQuery, and Performance Analyzer to find slow visuals.

Q44. How do you decide which KPIs go into the first release? Start from decisions and owners: the 8–12 metrics leadership already reviews, where data is available and can be reconciled. Ship an overview, a drill-down and a detail page; get feedback; iterate. Success is adoption and trust, measured by usage and retirement of shadow spreadsheets.

8.7 Architect-level and leadership questions

Q45. How do you balance speed of delivery vs building the platform properly? Build a thin vertical slice end to end — one domain, from ingestion to dashboard — on the target architecture with the essential guardrails (IaC, CI/CD, quality gates, access control). Defer generalisation until the second and third domains show what's common. Avoid both a big-bang platform with no users and quick hacks that become permanent.

Q46. How do you document and communicate architecture decisions? Architecture Decision Records (context, options, decision, consequences) in the repo; C4-style diagrams; a platform README and onboarding guide; regular demos to stakeholders. Decisions are reversible where possible and revisited when assumptions change.

Q47. What would you do in your first 90 days in this role? Days 1–30: understand business priorities, sources, current reports and pain points; inventory data and access; quick wins on trust (a reconciliation or freshness fix). Days 31–60: target architecture and roadmap; landing zone and pipelines for the first domain; metric definitions agreed. Days 61–90: first Power BI release on a certified model with quality gates; feedback; plan next domains.

Part 9 — System design scenarios, behavioural questions, final checklist

In design rounds, interviewers score your process more than the final diagram: clarify requirements, propose a simple end-to-end design, then deepen where they push. Use the same seven-step frame every time.

9.1 The seven-step design frame

1. Clarify: business goal, users, sources and volumes, freshness, history needs, compliance (PII, residency), budget, team skills.
2. Sketch end to end: sources → ingestion → storage zones → transformation → serving → BI, with the AWS service for each box.
3. Model: grain, key entities, conformed dimensions, SCD needs.
4. Reliability: incremental/idempotent loads, CDC, backfills, failure handling.
5. Quality and governance: gates, reconciliation, lineage, access control, PII.
6. Operations: orchestration, monitoring, CI/CD, IaC, cost.
7. Trade-offs and evolution: what you'd do in phase 1 vs later, and what would change your choice.

9.2 Scenario A — Retail company, five source systems, first warehouse

Prompt: "We have an ERP (SQL Server), a Shopify store, a CRM (Salesforce), a warehouse management system exporting CSVs daily, and marketing spend in Google Ads. Leadership wants a daily sales and operations dashboard in Power BI. Design it."

• Clarify: daily freshness acceptable? (yes, by 07:00); history since 2022; customers in EU (GDPR); 30 report users growing to 200.
• Ingestion: DMS full load + CDC from SQL Server to S3 raw (Parquet); Shopify and Google Ads via API extractors (Lambda/Glue Python shell, or AppFlow/Airbyte); Salesforce via AppFlow; WMS CSVs via SFTP (Transfer Family) to S3, event-triggered validation.
• Storage and processing: Iceberg tables in bronze/silver on S3 with Glue Catalog; Glue Spark for heavy CDC merges; dbt on Redshift Serverless for silver-to-gold modelling.
• Model: facts fct_order_line (grain: order line), fct_inventory_daily (periodic snapshot), fct_marketing_spend_daily; conformed dim_customer (resolved across Shopify, CRM, ERP with a golden ID), dim_product (ERP master, SKU mapping for Shopify and WMS), dim_date, dim_store/channel.
• Quality: contract checks on each source; uniqueness and referential integrity; reconciliation of daily ERP revenue vs warehouse revenue; publish only when gates pass.
• Governance: Lake Formation tags for PII columns; customer emails tokenised in silver; Redshift masking; Power BI RLS by region.
• Orchestration: MWAA DAG: ingestion sensors → Glue merges → dbt build (with tests) → reconciliation → Power BI refresh via REST API; alerts to Slack.
• BI: certified Power BI semantic model (Import, incremental refresh on order lines) via gateway on EC2; overview, drill-down by region/store/product, operations page with inventory and fulfilment KPIs.
• Evolution: add new sources by mapping into conformed entities; add hourly freshness for operations later via more frequent CDC merges.

9.3 Scenario B — Near-real-time operational KPIs

Prompt: "Operations wants order backlog and late-shipment rate updated every 5 minutes."

Options: (1) Kinesis/MSK events → Firehose into Iceberg (or Redshift streaming ingestion into a materialized view refreshed every minute) → Power BI DirectQuery on a narrow, pre-aggregated table; (2) Aurora zero-ETL into Redshift for the order DB, then materialized views. Discuss: only the operational KPIs need this path; the rest stays daily. Mention DirectQuery load on Redshift and use of aggregated views; automatic page refresh in Power BI requires appropriate capacity settings.

9.4 Scenario C — Migrating a legacy on-prem warehouse to AWS

Assess (inventory of ETL jobs, reports, usage — retire unused), choose target patterns, migrate in waves by subject area, run old and new in parallel with automated reconciliation of outputs, cut over report by report, decommission. Tools: AWS SCT and DMS for schema/data, dbt for rewritten logic, automated comparison queries. Your legacy ETL migration consulting experience fits here directly.

9.5 Scenario D — Data mesh for multiple business units

Domain-owned producer accounts publish Iceberg data products with contracts; central governance account with Glue Catalog and Lake Formation; SageMaker Catalog for discovery and subscription approvals; platform team provides Terraform modules, pipeline templates, quality and lineage tooling. Risks: inconsistent definitions (solve with shared conformed dimensions and a semantic layer), duplicated effort, and domain teams without data skills.

9.6 Behavioural questions — prepare STAR answers

• Tell me about a time you made untrusted data trustworthy.
• A time you disagreed with a stakeholder about a metric definition.
• A production incident you owned — what happened, what changed afterwards?
• A design decision you'd make differently today.
• How you introduced standards (testing, CI/CD, IaC) to a team that didn't have them.
• Delivering under a tight deadline: what did you cut, and what did you refuse to cut?
• Working with non-technical business users to define requirements.
• Mentoring engineers or leading without formal authority.

For each: situation in two sentences, your specific actions, numbers in the result, and one lesson.

9.7 Questions to ask the interviewer

• Which source systems are in scope first, and who owns them?
• Is there an existing warehouse or reporting to replace, and how much is trusted today?
• Who owns metric definitions on the business side?
• What's the team: will I be building alone, leading, or joining engineers?
• Constraints: data residency, compliance, existing Microsoft licensing for Power BI?
• What does success look like at 6 and 12 months?

9.8 Final checklist (the evening before)

• [ ] Can explain lake vs warehouse vs lakehouse and the AWS service for each layer
• [ ] Can whiteboard Scenario A end to end in 15 minutes
• [ ] Know Redshift dist/sort keys, WLM, materialized views, Spectrum, data sharing, zero-ETL
• [ ] Know Glue Catalog, crawlers vs DDL, Glue jobs, bookmarks, Glue Data Quality
• [ ] Know Lake Formation grants, LF-Tags, data filters, cross-account sharing
• [ ] Know Iceberg benefits and MERGE/DELETE/time travel
• [ ] Can explain grain, conformed dimensions, SCD2, late-arriving data, Data Vault basics
• [ ] Can explain CDC, idempotency, write-audit-publish, backfills
• [ ] Can describe quality dimensions, gates with severities, entity resolution, reconciliation
• [ ] Can describe PII lifecycle and a GDPR delete in Iceberg
• [ ] Can explain semantic layer options and the metric-definition process
• [ ] Power BI: Import vs DirectQuery, gateway on EC2, star schema, CALCULATE, time intelligence, RLS, drill-through
• [ ] Two or three STAR stories with numbers ready
• [ ] Azure-to-AWS framing sentence rehearsed