How to Design a Production-Ready AWS Data Lake Architecture: S3 + Glue + Athena + Power BI

A data lake built right becomes your cheapest, fastest analytics layer. Built wrong, it turns into a swamp. Nobody trusts the data inside it. Queries drag on for 45 minutes and cost $200 each. Power BI dashboards refresh from tables nobody's sure are current. The gap between these two outcomes comes down to a handful of early decisions. Everything happens before you write a single byte to S3. This guide walks through how to design an AWS data lake architecture. It's built to survive real production traffic. We'll cover the zone architecture, file formats, and partitioning strategy. We'll also look at Glue crawler discipline and Athena cost controls. Then we'll get into Power BI connection patterns. Together, they form an AWS data lake architecture using S3, Glue, and Athena. It's the kind of analysts actually want to use.

The AWS Data Lake Architecture: Components, Concepts, and Design Principles

An AWS Data Lake Architecture rests on one simple idea. You store data in S3, catalogue it with Glue. Then you query it with Athena and visualise it in Power BI. The schema gets applied when you read the data, not when you write it. There's no database server to manage here. Storage and compute stay fully separate, too. Best part? There's no upfront provisioning at all. You pay for storage at S3 rates. You pay for queries at Athena's per-TB-scanned rate. That's really it.

The most important design decision in an AWS Data Lake Architecture is not which services to use. That choice is usually obvious from the brief. What matters is how you organise the data within those services. A well-partitioned Parquet data lake queried with Amazon Athena is one of the cheapest and fastest analytics systems you can build, while a poorly organised one with mixed formats, missing partitions, and no zone discipline ends up expensive, slow, and unmaintainable.

The Four Services and What Each Does

• Amazon S3 stores every piece of data your lake holds. That spans raw ingestion through analytics-ready datasets. It costs just $0.023 per GB. You also get versioning, lifecycle policies, and built-in encryption. Event notifications can trigger downstream processing automatically. One thing S3 isn't? A database. It has no query engine, no indexing, no transactions. It just stores objects and serves them by key or prefix.
• AWS Glue handles schema discovery, ETL processing, and cataloguing, all in one service. The AWS Glue Data Catalog acts as a central metadata store for every S3 dataset. Glue Crawlers infer schema automatically from raw S3 data. Glue ETL Jobs run PySpark pipelines to convert and clean that data. Glue DataBrew adds a low-code option for data prep. Glue is primarily batch-oriented, though it does support streaming ETL jobs on Spark Structured Streaming. For high-throughput streaming ingestion, many teams land data through Kinesis Firehose or MSK before processing it in Glue.
• Amazon Athena works as the interactive SQL engine sitting over your S3 data. It's fully serverless, so there's no cluster to manage. You pay per TB scanned, nothing more. Athena runs ANSI SQL through a Trino-based distributed engine (formerly Presto). It supports federated queries to other data sources too. It reads Parquet, ORC, JSON, CSV, and Avro without extra setup. What isn't Athena? A data warehouse. It holds no persistent storage, no indexes, and no caching by default. Performance depends entirely on how well you've organised the data in S3.
• Microsoft Power BI handles business intelligence and visualisation on top of this whole stack. It connects to Athena through Direct Query or Import mode via ODBC. From there, it builds interactive dashboards with scheduled refresh. Row-level security and AI-powered analytics come built in too. What Power BI isn't? A data prep layer. Doing that kind of work inside Power Query instead of Glue or Athena creates real problems. It adds maintenance complexity and drags down performance.

The Design Principles That Produce a Production-Ready AWS Data Lake

• Zone discipline: data moves through distinct, purpose-specific zones: Raw to Curated to Consumption. Each zone has defined quality guarantees, access controls, and retention policies. Data written to a zone is immutable at that stage. Problems get fixed by reprocessing from the prior zone, not by editing data in place.
• Schema on read, not schema on write: the data lake stores data in its original form and applies schema at query time. This lets historical data be re-queried against new schema interpretations without rewriting it. The schema catalogue, the Glue Data Catalogue, is maintained separately from the data.
• Columnar formats for analytics: raw data arrives in any format, but curated and consumption data are always stored in Parquet or ORC. Columnar storage plus predicate pushdown reduce the amount of data Athena scans, directly cutting query cost and improving performance.
• Partition pruning works as your primary cost control here. Athena bills per TB scanned, always. When you partition by date, region, or source system, Athena skips everything irrelevant. It only reads the partitions your query actually needs. That alone can cut scan costs by 70 to 95 percent for typical queries.
• Catalogue-driven access means every request goes through the AWS Glue Data Catalog, never straight to S3 paths. Adding a dataset becomes a catalogue operation. So does renaming a table or changing a schema. Every consumer sees the update immediately. Nobody needs to touch S3 paths or rewrite their queries.

Together, these principles reflect proven AWS architecture patterns used across enterprise-grade deployments, and they hold up whether the lake serves five analysts or five hundred.

Zone Architecture: Designing the S3 Structure for a Production AWS Data Lake

Zone architecture might be the biggest structural call in a data lake. It decides how data flows from source systems to analytics consumers. It also decides who can access what, and which quality guarantees apply. When something breaks, this structure decides how you isolate and recover it. The three-zone model has become the production standard for AWS Data Lake Architecture. That's Raw, sometimes called Bronze or Landing. Then Curated, also known as Silver or Processed. Finally, Consumption, often called Gold or Analytics-Ready. Getting this right pays off. It's one of the most valuable parts of any solid data engineering architecture.

The Three-Zone Architecture

The S3 Bucket and Prefix Structure

• Use separate buckets per zone, for example, company-datalake-raw, company-datalake-curated, and company-datalake-consumption, each suffixed with the account ID, so IAM policies and lifecycle rules stay simple and independent.
• Structure the raw bucket as source={source_system}/entity={entity_name}/year={YYYY}/month={MM}/day={DD}/hour={HH}/, with the timestamp and batch ID in the file name, using hour-level partitioning to support near-real-time ingestion.
• Structure the curated bucket as domain={business_domain}/entity={entity_name}/year={YYYY}/month={MM}/day={DD}/, using Hive-compatible partition keys so Glue Crawlers can auto-discover partitions.
• Structure the consumption bucket as report={report_name}/year={YYYY}/month={MM}/, with coarser partitioning, since BI queries typically span longer time ranges.
• Include the account ID in every bucket name for global uniqueness and easy multi-account identification.
• Never place raw and curated data in the same bucket prefix. They need different lifecycle policies, different IAM permissions, and different encryption requirements.

File Format and Compression Decisions

• CSV belongs in the raw zone only. It is human-readable and universally compatible but row-oriented with no predicate pushdown and two to three times larger than Parquet for typical analytical data. Never use CSV in curated or consumption zones.
• JSON suits raw event and log data. It handles flexible, nested schemas and stays readable, but its row orientation and large file sizes hurt query performance at scale.
• Parquet is the standard for curated and consumption data. Its columnar storage only reads the columns a query touches, supports predicate pushdown, compresses four to six times better than CSV with Snappy, and has native Athena support. The tradeoff is that it is a binary format requiring a known schema at write time.
• ORC is a curated-zone alternative to Parquet with similar or slightly better compression in some workloads. It is less common in the AWS ecosystem, where Parquet tooling is more mature, so ORC mainly suits teams migrating from Hive-heavy environments.
• Avro fits raw streaming sources such as Kafka or Kinesis. Its schema travels with the file, supporting schema evolution, though it stays row-oriented and is not ideal for analytical queries. Convert it to Parquet during the curated ETL step.
• Delta Lake or Iceberg suit advanced curated-zone use cases needing ACID transactions, time travel, schema evolution, compaction, or merge-upsert operations. They need Spark or Glue jobs to write and add complexity, so they are best reserved for cases where upserts, late-arriving data, or ACID requirements make append-only Parquet insufficient.

The Partitioning Strategy, the Primary Cost and Performance Lever

Partitioning is the single most impactful design decision for Athena query cost and performance. Athena charges $5 per TB scanned. A 1 TB dataset partitioned by date and scanned for a single-day query costs approximately $0.014, about 1/365 of the total. The same query against an unpartitioned table costs $5. Correct partitioning reduces effective query cost by about 99 to 100 percent for typical time-bounded analytical queries.

• Date-only partitioning, using year, month, and day, suits time-series data queried by range. It's the most common pattern by far. A query like WHERE date_column BETWEEN '2026-05-01' AND '2026-05-14' shows the idea well. For typical monthly queries, this cuts scan cost by 70 to 95 percent. That's versus an unpartitioned table.
• Date plus source system partitioning suits multi-source data where queries often filter by a specific source, for example, WHERE source = 'crm' AND year = '2026', cutting scans by 90 to 98 percent.
• Date plus region partitioning suits geographic analysis, for example, WHERE region = 'us_east' AND month = '2026-05', cutting scans by 90 to 97 percent for regional queries.
• Date plus business domain partitioning fits domain-separated consumption zones well. Take WHERE domain = 'sales' AND year = '2026' as an example. Queries scoped to one domain see scan cuts of 85 to 95 percent.
• Steer clear of partitioning by a single high-cardinality column, like customer_id. It creates too many partitions, often past 10,000. That causes real performance problems for both Glue Crawler and Athena metadata. Miss that column in a query, and you'll scan everything.
• For tables with predictable, date-based partitions, Athena Partition Projection beats Glue Crawlers. It defines your partition schema right in the table properties. Athena then computes partition paths algorithmically. No more querying the Glue Catalog on every single request. That removes 50 to 200 milliseconds of GetPartitions latency per query. It also sidesteps Glue Catalog limits on partition counts. Best of all, new partitions need no crawler at all. This works well for any date-partitioned table with a regular naming pattern. High-frequency tables with daily or hourly partitions benefit the most.

AWS Glue: Catalogue Management, Crawler Design, and ETL Pipeline Architecture

AWS Glue has three distinct functions in a data lake. The AWS Glue Data Catalog stores schema metadata for all S3 datasets and makes them queryable by Athena and other services. Glue Crawlers automatically discover and register schema from S3 data, and Glue ETL Jobs, written in PySpark or Python, transform data from raw to curated and from curated to consumption. Each function requires distinct configuration decisions, and teams without in-house Spark expertise often hire dedicated AWS data engineers to design and run them as part of a broader AWS Data Engineering practice.

Glue Data Catalog Design

• Name your databases per business domain, per zone. Think sales_raw, sales_curated, and sales_consumption. Or go simpler with one database per zone, like raw_db and curated_db. Domain-per-zone naming supports team-based IAM well. Data engineering owns raw_db, while analytics owns curated_db. Cross-zone queries then need explicit database qualifiers. That keeps your data lineage visible right in the SQL.
• Stick to snake_case for table names, with a source prefix on raw tables. salesforce_opportunities_raw is a good example. Curated tables get an entity name, like opportunities. Consumption tables get a KPI name, such as sales_pipeline_daily. This convention prevents confusion when multiple sources share similar entity names.
• Use snake_case for column names too, and skip reserved SQL keywords. Prefix technical metadata columns with an underscore, like _ingested_at or _batch_id. Athena runs on ANSI SQL, so reserved words need quoting anyway.
• For Avro, Protobuf, or JSON schemas in streaming paths, use the AWS Glue Schema Registry. It manages schema versions with explicit compatibility rules, whether BACKWARD, FORWARD, or FULL. Parquet works differently. Its schema lives inside the file headers, and crawlers pick it up automatically.
• Turn on AWS Lake Formation for the AWS Glue Data Catalog to get column and row-level security. Apply Lake Formation permissions instead of relying on S3 bucket policies alone. Analytics users should reach data through the catalogue, never through raw S3 paths.

Glue Crawler Design, the Common Mistakes and How to Avoid Them

• Running a single crawler across every zone and source system lets one slow or broken source fail the entire crawl and lets schema conflicts between sources create incorrect merged tables. Run one crawler per source system per zone instead, each on its own schedule.
• Running crawlers on every S3 write, including the raw zone, adds latency and increases Glue DPU costs. Use Partition Projection for date-partitioned tables, or the AddPartitions API and MSCK REPAIR TABLE, to add partitions programmatically without a crawler.
• Letting crawlers create tables in the wrong database can merge multiple entity tables into one when the S3 path structure is ambiguous, or create unwanted tables from staging prefixes. Set explicit exclude patterns for prefixes like _staging/ and _temp/, configure an explicit target database per crawler, and review crawler output before production use.
• Changing the source schema without updating the Glue table lets the schema drift from the real data, so Athena queries fail or return wrong results and Power BI sees a stale schema. Configure crawlers to add new columns automatically, alert on column removal, and version Glue ETL jobs with schema validation.

Glue ETL Job Design, Raw to Curated Transformation

The Glue ETL job that transforms raw data to curated data is the most critical engineering component in the data lake pipeline. Its quality determines the quality of every downstream analytical query and every Power BI report. The job must be idempotent, meaning it produces the same output on a second run rather than duplicates. It must be schema-validated, so mismatches are caught and handled rather than silently corrupted. And it must be partitioned-output-aware, writing to the correct S3 prefix structure for partition discovery.

• Enable Glue Job Bookmarking so the job processes only new files since the last successful run. Set '--job-bookmark-option': 'job-bookmark-enable' in the job parameters to handle restarts after failure without reprocessing files.
• Validate schema by reading raw data with an explicit StructType rather than an inferred one, comparing it against the expected schema, and routing mismatched records to a quarantine prefix such as s3://raw/quarantine/ with error metadata. Never silently drop invalid records.
• Deduplicate on the natural key plus ingested_at before anything lands in curated. Use df.dropDuplicates(['natural_key', 'updated_at']) for a quick approach. Or use a window function with row_number(), partitioned by natural_key and ordered by updated_at descending. Either way, you keep the latest record.
• Define explicit null handling for each column, not a blanket rule. Some columns get a default value. Others get flagged or simply passed through. Drive this through configuration, not hardcoded logic. Log null counts as a job metric too.
• Write curated output as Parquet with Snappy compression, partitioned by Hive-compatible keys. A typical call looks like spark.write.mode('overwrite')
  .partitionBy('year', 'month', 'day').parquet(output_path). Use dynamic partition overwrite here. That way, unchanged partitions never get rewritten unnecessarily.
• Process only the time window covered by new raw files, nothing more. Pass --start_date and --end_date as job parameters. Filter raw input down to those dates. Then overwrite just those date partitions in the curated output. Full-table reprocessing simply isn't necessary.
• Catch every exception the job throws, without exception. Log with structured fields, using something like json.dumps. Publish a CloudWatch metric for quarantined record counts. Send an SNS notification the moment a job fails.

Amazon Athena, Query Optimisation, Cost Control, and Production Best Practices

Athena runs as a pay-per-query service, charging $5 per TB scanned. That pricing model means one thing. Cost comes down entirely to how well you've organised data in S3. A production data lake needs typical queries to scan only what they truly need. Get this right, and you'll cut Athena costs by 70 to 95 percent. Query times drop by 5 to 10 times too, compared to unoptimised data.

The Athena Cost Optimisation Playbook

• Converting to Parquet or ORC lets Athena read only the columns a query references instead of every column, cutting scans by 50 to 80 percent versus CSV or JSON. This takes a Glue ETL job to convert, plus a one-time pass over historical data.
• Partition pruning lets Athena skip partitions that do not match the WHERE clause, cutting scans by 70 to 99 percent for time-bounded queries. It needs the right S3 prefix structure at ingestion with Hive-compatible partition keys.
• File size optimisation matters because Athena performs best with files between 128 and 512MB. Many small files add metadata overhead and slow queries, while very large files limit parallelism. A Glue compaction job that merges small files into the target size range improves performance by 20 to 50 percent.
• Column statistics from the ANALYZE TABLE command, available in Athena v3, let the engine choose better join and filter strategies, improving query performance by 10 to 30 percent with low implementation effort.
• Athena Workgroup scan limits cap the maximum data a single query can scan, so runaway queries get cancelled before racking up cost. Create workgroups with per-query scan limits and assign users to them.
• Result caching through Athena's result reuse feature stores query results for up to seven days, so identical repeated queries reuse cached results at zero additional cost. Enable result reuse in workgroup settings and set a max age.
• Athena CTAS, or Create Table As Select, pre-aggregates or pre-joins large datasets into consumption-zone Parquet tables, moving heavy computation to ETL so BI queries run against small, optimised output instead of raw data.

Table Design Patterns for Production Athena Performance

• Partition projection tables use TBLPROPERTIES projection settings to eliminate the GetPartitions call entirely, which suits date-partitioned tables with regular naming and more than 1,000 partitions.
• Bucketed tables, created with CLUSTERED INTO N BUCKETS BY (column), distribute files by a hash of the bucket column, which reduces shuffle in distributed joins for join-heavy queries between large tables.
• A view built over common joins hides join complexity from BI users. BI tools query the view instead of the raw tables, so the underlying structure can change without breaking BI queries.
• CTAS for aggregated consumption, such as CREATE TABLE consumption.daily_sales_summary WITH (format='PARQUET', partitioned_by=ARRAY['year', 'month']) AS SELECT ... GROUP BY produces pre-aggregated metrics that Power BI queries frequently, dramatically cutting BI query scan cost.
• Iceberg tables, created with TBLPROPERTIES ('table_type'='ICEBERG'), support MERGE INTO, UPDATE, and DELETE, which suits dimension tables that need upserts, slowly changing dimensions, or late-arriving data corrections.

Athena Query Patterns to Avoid

• SELECT * FROM large_table without a WHERE clause that enables partition pruning makes Athena scan the entire table. Always include a partition filter in production queries, and use Athena's query validation in the console to see the estimated scan size before running.
• Joins between large unpartitioned tables in ad hoc queries scan both full tables when there is no predicate pushdown or partitioning. Pre-join in Glue ETL and store the result in the curated zone, so Power BI queries the pre-joined dataset instead.
• Storing query results in the default S3 output location without a lifecycle policy lets Athena results accumulate indefinitely. Configure the output bucket with a 7- to 30-day lifecycle rule to delete old results automatically.
• Using Athena for sub-second interactive queries from Power BI in Direct Query mode will disappoint. Athena's query startup time is typically 1 to 5 seconds, even for small scans. For dashboards needing sub-second response, use Import mode with scheduled refresh, or front Athena with a caching layer.

Power BI Integration: Connecting to Athena for Production Analytics

Power BI connects to Athena via the ODBC driver provided by AWS or the Simba Athena ODBC connector. The connection mode you choose, Import versus Direct Query, is the most consequential decision for Power BI performance and cost, and picking wrong is responsible for most production Power BI plus Athena performance problems. This decision often matters as much as the broader cloud application development services an organisation relies on to build the applications that generate this data in the first place.

Import Mode vs Direct Query: The Critical Decision

Setting Up the Athena ODBC Connection for Power BI

• Install the 64-bit Amazon Athena ODBC driver, available from the AWS documentation, on the Power BI Desktop machine or the Power BI Gateway server.
• Configure the ODBC Data Source through the 64-bit ODBC Data Source Administrator. Add an Amazon Athena ODBC DSN, then set your AWS region. Set the catalog to AwsDataCatalog, and the schema to curated_db or consumption_db. Choose IAM Credentials or an AWS Profile for authentication. Point the S3 output location to a dedicated results bucket, something like s3://company-athena-results-{account-id}/powerbi/. Assign a dedicated workgroup too, like powerbi_workgroup, with a scan limit set in its settings.
• In Power BI Desktop, choose Get Data, then ODBC, then select the DSN, and use the Navigator to browse the AWS Glue Data Catalog tables. Select tables directly for Import mode, or choose DirectQuery before loading for that mode instead.
• For Power BI Service publishing, install the On-premises Data Gateway on a server with network access to Athena, register the ODBC DSN on that gateway, and configure the gateway connection in Power BI Service using an IAM access key or, preferably, an IAM role.
• Configure scheduled refresh for Import mode through Dataset Settings in Power BI Service, setting the refresh frequency anywhere from every 30 minutes to daily depending on freshness needs, and enable refresh failure notifications.

Power BI Performance Optimisation for Athena-Backed Data

• Connect to consumption zone tables, not curated zone tables. The consumption zone holds pre-aggregated, denormalised data designed for BI query patterns. Connecting Power BI directly to curated zone tables with row-level detail forces it to aggregate millions of rows, which hurts performance and raises Athena cost.
• Use CTAS in Athena to pre-aggregate before Power BI import. For Import mode, create a Glue job or CTAS statement that produces a consumption-zone Parquet table containing exactly the dimensions and measures each Power BI report needs, so Power BI imports from that pre-aggregated table rather than the full transaction-level curated table.
• Implement row-level security at two layers together, i.e., Lake Formation and Power BI RLS. For multi-tenant or role-based access, Lake Formation data filters restrict what Athena returns based on the querying IAM identity. Power BI Row Level Security then restricts what users actually see in the report. That way, access gets controlled at the source, not just in the presentation layer.
• Create a dedicated Athena workgroup for Power BI queries with a scan limit, for example 50GB per query, to prevent runaway Direct Query requests from scanning entire tables. Monitor workgroup usage weekly, since unexpected scan spikes usually mean queries without proper partition filters.

Data Lake Security: IAM, AWS Lake Formation, and Encryption Architecture

Data lake security requires defence in depth: the right IAM permissions, AWS Lake Formation for fine-grained data access control, S3 bucket policies as a backstop, and encryption at rest and in transit. The security architecture must be designed before any data is written. Retrofitting access controls onto a data lake with existing data and users is far more complex than designing them correctly from the start.

IAM Role Architecture for the Data Lake

AWS Lake Formation, Fine-Grained Access Control

AWS Lake Formation adds column- and row-level security on top of the Glue Data Catalog. You can grant access to specific tables, columns, and rows this way. Nobody needs to see the underlying S3 paths. In a production data lake, all access should flow through Lake Formation permissions. Relying only on S3 bucket policies isn't enough.

• Table-level permissions grant or deny access to entire catalogue tables. For example, GRANT SELECT ON TABLE curated_db.customers TO ROLE analyst_role.
• Column-level security hides sensitive columns from specific roles. You might grant SELECT on id, name, and region. At the same time, you'd exclude email, phone, and SSN entirely.
• Row-level filters restrict which rows a role can actually see. Base this on column values, like region equals 'us_east' for analyst_role. Every query from that role then appends the condition automatically.
• Tag-based access control, known as LF-Tags, classifies data by sensitivity and assigns permissions by tag rather than per table. Tagging tables as sensitivity=pii and granting analyst_role access only to sensitivity=non_pii automatically excludes PII tables without per-table configuration.
• Cross-account access shares specific catalogue tables with other AWS accounts through Resource Access Manager, for example, sharing consumption_db tables with a partner without granting any direct S3 bucket access, since Lake Formation controls exactly what the external account can query.

Encryption Architecture

• Standard S3 data at rest uses SSE-S3, AES-256, managed automatically by S3, at zero additional cost with no KMS charges, applied as the default bucket encryption.
• Regulated or PII data at rest uses SSE-KMS with a customer-managed key per bucket, with key policies restricting which roles can decrypt and CloudTrail logging every KMS operation.
• Data in transit relies on HTTPS and TLS, enforced through an S3 bucket policy that denies any request where aws: SecureTransport is false, blocking all HTTP access.
• Glue ETL connections to S3 use TLS by default with no extra configuration needed.
• Athena query results carry the same SSE-S3 or SSE-KMS setting as the results bucket, configured in Athena workgroup settings, with SSE-KMS preferred for regulated data.
• Power BI Gateway connections to Athena enforce TLS 1.2 through the ODBC driver, using AWS Certificate Manager on the AWS side, with no plain-text connections supported.

Data Quality Pipelines and Schema Evolution in the AWS Data Lake

Data quality in a data lake is not a one-time validation step at ingestion. It is a continuous monitoring discipline that detects when data quality degrades, identifies which source systems are producing data that violates expectations, and prevents corrupt data from propagating from raw to curated to consumption zones where it would affect business decisions.

The Data Quality Architecture

• Schema validation runs at every single Glue ETL job. It checks that expected columns exist, types match, and required fields aren't null. Glue PySpark validation and custom StructType comparison catch any mismatch. Bad records get quarantined to s3://raw/quarantine/. An SNS alert follows immediately, and a failure metric ticks up.
• Business rule validation runs at every ETL job too. It checks referential integrity, value ranges, like no negative prices, and date logic, such as order date before ship date. Custom PySpark rules or AWS Glue Data Quality quarantine bad records individually. The job keeps processing valid ones regardless. It alerts only if the quarantine rate crosses a threshold.
• Statistical quality monitoring runs daily or after each ETL run in the curated zone, checking that row counts stay within an expected range, and that null rates and categorical distributions stay stable. AWS Glue Data Quality checks or custom Athena queries against a metrics table trigger an SNS alert. It automatically pauses downstream consumption ETL if the quality score drops below the threshold.
• Freshness monitoring runs on a schedule, every 15 to 30 minutes for critical tables in the consumption zone, checking the last updated timestamp against an expected freshness SLA. A Lambda function querying Glue table metadata with a CloudWatch alarm triggers an SNS alert, and the Power BI dashboard can show a data freshness warning.

Schema Evolution Patterns

Source system schemas change. New columns are added, existing columns are renamed, and data types are changed. The data lake architecture must handle schema evolution without breaking existing consumers. Here are the four schema evolution scenarios and how to handle each.

• A new column added to the source is backward compatible. Raw files contain the new column, Glue Crawler adds it to the table, and Athena queries keep working since old partitions simply return null for that column, with no ETL changes needed in most cases.
• A column renamed in the source is a breaking change. Raw files use the new name, Glue Crawler updates the table schema, and existing queries using the old name break. Add a computed column in Glue ETL mapping the old name to the new one, keep both names in curated for a transition period, notify consumers, and drop the old name after the transition.
• A column data type change, such as from int to string, is a breaking change. Raw files carry the new type; Glue Crawler may fail to update if the types are incompatible, and Parquet's typed schema will not silently adapt. The ETL job must handle type coercion explicitly, cast to the new type, and document the breaking change in the data contract.
• A column removed from the source counts as a breaking change. Raw files stop containing it. Glue Crawler drops it from the schema. Existing queries using that column simply fail. The ETL job needs to catch the missing column and substitute a null or default value. Notify consumers, update the data contract, and consider keeping the column in curated with nulls for backward compatibility.

The Complete Production AWS Data Pipeline: From Source to Power BI Dashboard

Individual components rarely matter on their own. The S3 zone structure, Glue crawlers, Glue ETL jobs, Athena tables, and Power BI connection only create value together. They need to form one coherent, monitored, failure-tolerant pipeline. This section walks through the complete production AWS Data Pipeline, from ingestion all the way to Power BI refresh.

The End-to-End Pipeline Architecture

• Ingestion starts with a source system push, a Lambda function, Kinesis Firehose, or AWS DMS, triggered hourly, daily, or by an event such as an API call or a CDC event. It writes raw files to s3://company-datalake-raw/source=X/year=Y/month=M/day=D/, with an SLA under 5 minutes for streaming and under 30 minutes for batch.
• Raw notification follows immediately, using an S3 Event Notification through SNS to SQS whenever a new object lands in the raw bucket prefix, producing an SQS message with the new file's S3 key in under a second.
• The ETL trigger fires within 2 minutes of that message, using an EventBridge rule or a Glue Workflow trigger on the SQS message or a scheduled time, starting the Glue ETL job.
• The raw-to-curated ETL step runs the Glue PySpark job, triggered by the Glue Workflow or directly, writing Parquet files to the curated bucket and updating Glue Catalog partitions, typically within 5 to 30 minutes depending on volume.
• A quality check runs immediately after the ETL job completes, producing a CloudWatch metric, an SNS alert on failure, and a quality score in the metrics table.
• The curated-to-consumption step runs after the quality check passes, on schedule, using a Glue ETL job or an Athena CTAS statement to write aggregated Parquet to the consumption bucket and update the Glue Catalog, typically within 5 to 15 minutes.
• Power BI refresh runs on a schedule, from every 30 minutes to daily, refreshing the in-memory dataset in Import mode within 2 to 10 minutes.
• The dashboard becomes available to all users immediately after the dataset refresh completes, within the refresh window.

Orchestration with AWS Step Functions or Glue Workflows

Complex multi-step ETL pipelines require orchestration to manage dependencies, handle failures, and enable retry logic. There are three main options.

• AWS Glue Workflows stay native to the Glue service. You define job dependencies visually or in code, with triggers for scheduled, event-based, or on-demand runs. Failure handling happens per job, with built-in retry and configurable attempts. This suits pipelines that live entirely inside Glue and Athena. It also works well for simple linear or parallel dependencies. Teams that prefer managed, low-code orchestration tend to like this option.
• AWS Step Functions offer full state machine orchestration, supporting any AWS service as a step. You get parallel branches, error catching with fallback states, and wait states for external events. Visual workflow monitoring comes standard too. This fits complex branching logic across different data sources well. It also handles pipelines that include Lambda or ECS components. Choose this when you need fine-grained error handling per step.
• Apache Airflow, through Amazon MWAA, brings open-source workflow orchestration with Python-defined DAGs. It ships with a rich operator library for AWS services. Teams with existing Airflow expertise tend to gravitate here. It also suits complex pipelines with multiple external dependencies. Organisations standardising on Airflow across several data platforms benefit most.

Cost Optimisation: Managing AWS Data Lake Costs at Scale

AWS data lake costs at scale are primarily driven by three components: S3 storage, Athena query scan volume, and Glue ETL DPU-hours. Understanding which workload patterns drive cost the most, and which optimisations have the highest impact, is one of the most practical AWS Data Lake Best Practices a growing team can adopt.

The Cost Components and Typical Distribution

S3 Storage Cost Optimisation with Lifecycle Policies

• In the raw zone, keep data in S3 Standard for 0 to 90 days, move it to Standard-IA from 90 to 365 days for about 46 percent savings, and move it to Glacier Instant Retrieval after 365 days for about 83 percent savings versus Standard. A 10TB raw zone costs roughly $330 a month after a year under this policy, versus $2,300 a month if left entirely in Standard.
• In the curated zone, keep data in S3 Standard for 0 to 180 days while queries stay active. Move it to Standard-IA from 180 to 540 days once analytical queries slow down. After 540 days, move it to Glacier Flexible Retrieval for archival. Just know that restoring from there takes extra steps.
• In the consumption zone, keep data in S3 Standard for 0 to 90 days, while Power BI accesses it often. After that, delete it. Consumption data regenerates from curated data anyway, so there's no need to archive it.
• For the Athena results bucket, delete objects after 7 to 30 days. Query results are ephemeral by nature. They carry no long-term value worth keeping.
• Apply lifecycle policies right at bucket creation, either through the S3 Lifecycle tab or in Terraform or CDK. Test with S3 Intelligent-Tiering first. That way, you'll see real access patterns before locking in fixed transitions.

Infrastructure as Code: Deploying the AWS Data Lake with Terraform or AWS CDK

A production data lake should never be manually embedded through the AWS console configuration. There are several reasons: it is not reproducible, not auditable, not version-controlled, and not safe to iterate on. Therefore, deploying through infrastructure as code is a must. IaC deployment with Terraform or AWS CDK enables consistent deployment across environments, code review for infrastructure changes, easy recreation of the environment, and automated security and compliance checks in CI/CD.

The Core Terraform Resources for the AWS Data Lake

• Three S3 buckets, one per zone, are defined as aws_s3_bucket resources. Enable versioning and server-side encryption, either SSE-S3 or SSE-KMS. Block all public access outright. Configure lifecycle rules too, and send access logs to an audit bucket.
• S3 bucket policies, defined as aws_s3_bucket_policy, deny HTTP through a SecureTransport condition. They also deny access to non-approved roles. Any PutObject without an encryption header gets denied as well.
• Glue Data Catalog databases, defined as aws_glue_catalog_database, exist one per zone per domain. Each one carries a description and a location_uri pointing to its S3 prefix.
• A Glue IAM role, defined through aws_iam_role and aws_iam_role_policy_attachment, follows least privilege throughout. Bucket ARN conditions keep it tightly scoped.
• Glue Crawlers, defined as aws_glue_crawler, each target one specific S3 prefix and database. They run on a schedule expression. Their schema change policy updates the database automatically for new columns and logs deleted ones.
• Glue ETL jobs, defined as aws_glue_job, reference the script location in S3 and the role ARN. Worker type, count, max retries, and timeout all get set explicitly. Job bookmarking and CloudWatch logging round it out.
• Athena Workgroups, defined as aws_athena_workgroup, enforce workgroup settings and a per-query scan limit. A result location in S3 gets specified too. SSE encryption covers the results, and CloudWatch metrics track usage.
• Lake Formation permissions, defined as aws_lakeformation_permissions, tie to a resource, like a database, table, or column. A principal, such as an IAM role or user, gets attached too. Permissions include SELECT, DESCRIBE, or ALTER.
• KMS keys for SSE-KMS setups, defined as aws_kms_key, exist one per zone. A key policy restricts usage to specific roles. Automatic rotation stays enabled, with a 30-day deletion window.
• CloudWatch dashboards and alarms, defined as aws_cloudwatch_dashboard and aws_cloudwatch_metric_alarm, cover Glue job failures and Athena query failures. S3 data volume gets tracked too. An SNS topic handles every notification.

The Deployment Environments Pattern

A production data lake should have at least three environments: 

• Development: where engineers test pipeline changes with a subset of real data
• Staging: where a full copy of the production pipeline runs against production-scale data for pre-release testing
• Production: the live environment serving analyst queries and Power BI dashboards.

The environment configuration strategy should use Terraform workspaces or separate Terraform state per environment.  Parameterise all environment-specific values, such as bucket names with an environment suffix, account IDs, and IAM role ARNs, in Terraform variables. It is also advised to use a separate AWS account per environment for billing isolation and security boundary separation.

Building a Production-Ready AWS Data Lake Architecture That Works

The architecture decisions described in this guide are the difference between a data lake that data engineers and analysts trust and one they work around. Zone architecture prevents data quality problems from cascading. Parquet conversion and partition design make Athena both fast and affordable. Glue catalogue discipline makes data discoverable and consistent. Power BI connection patterns determine whether dashboards refresh in seconds or fail intermittently.

The most important insight from production data lake operations is that most data lake problems are not infrastructure problems. They are data organisation problems. Fixing an Athena query that costs $200 does not require a different query engine. It requires partitioning the data correctly and converting it to Parquet. Fixing a Power BI dashboard that takes 45 seconds to load does not require a faster cloud service. It requires a pre-aggregated consumption table designed for the specific query pattern the dashboard uses.

Get the zone architecture right, convert to Parquet with proper partitioning, design the Glue catalogue with consistent naming, implement genuine data quality checks, and configure Power BI in Import mode against consumption-zone tables. The result is a production-ready AWS data lake architecture built on AWS data lake architecture best practices that hold up under real traffic. The rest is operational refinement.

About Mobisoft Infotech

Mobisoft Infotech designs and builds AWS cloud data infrastructure for startups, growth-stage companies, and enterprises, including data lake architectures (S3, Glue, Athena, Lake Formation), real-time data pipelines (Kinesis, MSK, Flink), and business intelligence solutions (Power BI, Tableau, QuickSight). Our data engineering practice has built and optimised data lakes across healthcare, fintech, retail, and logistics verticals.

Nitin Lahoti

Co-Founder and Director

Read more

Nitin Lahoti is the Co-Founder and Director at Mobisoft Infotech. He has 15 years of experience in Design, Business Development and Startups. His expertise is in Product Ideation, UX/UI design, Startup consulting and mentoring. He prefers business readings and loves traveling.

Read less

Share Article