DEV Community: Raphael Trovão

Hybrid DNS on AWS That Actually Works: Route 53 Resolver Endpoints End to End

Raphael Trovão — Sun, 12 Jul 2026 19:29:14 +0000

Every hybrid environment I've ever touched has the same ghost story: an application in AWS that sometimes can't resolve an on-premises hostname. Not always — sometimes. It works from one VPC and not another. It works for the sysadmin testing with nslookup and fails for the Java app two minutes later. Somebody eventually says "it's never DNS", everyone laughs nervously, and it's DNS.

I run the AWS side of a hybrid environment for a court system — 22 accounts, production EKS, and a datacenter full of systems that aren't going anywhere. Our cloud workloads need to resolve internal names that live on on-prem DNS servers, and on-prem systems need to resolve names that only exist in Route 53 private hosted zones. Getting that to work reliably in both directions is one of those problems that looks simple on the whiteboard and then eats a week of your life.

This is the end-to-end setup that works, plus the six mistakes I see over and over — including one that creates a genuine infinite loop between your datacenter and AWS.

First, understand who answers DNS inside a VPC

Before touching Resolver endpoints, you need to be clear about one thing: every VPC already has a DNS server. It lives at the VPC CIDR base plus two — for a 10.20.0.0/16 VPC, that's 10.20.0.2. People call it "VPC+2", the docs call it the Amazon Route 53 Resolver, your instances get it via DHCP as AmazonProvidedDNS.

That resolver is what makes AWS DNS magic work: it answers for private hosted zones associated with the VPC, resolves AWS service endpoints (including PrivateLink and VPC endpoint names), and recurses to the public internet for everything else. Your EC2 instances, your pods (via CoreDNS, which forwards to it), your Lambda functions in a VPC — everyone asks VPC+2.

Here's the thing VPC+2 can't do by itself: it has no idea your datacenter exists. Ask it for intranet.corp.internal and it will go look on the public internet, come back with NXDOMAIN, and your app breaks. And the reverse is also true — your on-prem DNS servers have no idea what db.prd.internal.example.com in a private hosted zone means.

Route 53 Resolver endpoints are the bridge, and there are exactly two kinds because there are exactly two directions.

The two directions

Outbound endpoint: AWS asks on-prem. An outbound endpoint is a set of ENIs in your VPC that can forward queries out. You pair it with forwarding rules: "any query for corp.internal → send it to 192.168.10.10 and 192.168.10.11". The rule is the brain, the endpoint is the pipe.

Inbound endpoint: on-prem asks AWS. An inbound endpoint is a set of ENIs with fixed private IPs that your on-prem DNS servers can forward to. On the datacenter side, you create conditional forwarders on your AD DNS or BIND: "anything under internal.example.com → ask 10.20.5.10 and 10.20.6.10". Those IPs answer exactly like VPC+2 would — private hosted zones, PrivateLink names, all of it.

Most real environments need both. Ours certainly does.

Building it: the order that avoids rework

Step 1 — plan the subnets and security groups. Each endpoint needs IPs in at least two AZs (that's your HA — don't cheat on this). The security group needs both TCP and UDP on port 53. Write that down, because it's mistake #3 below and it produces the most maddening symptom in this entire article.

Step 2 — the outbound endpoint and rules. In Terraform:

resource "aws_route53_resolver_endpoint" "outbound" {
  name               = "to-onprem"
  direction          = "OUTBOUND"
  security_group_ids = [aws_security_group.resolver.id]

  ip_address { subnet_id = aws_subnet.private_a.id }
  ip_address { subnet_id = aws_subnet.private_b.id }
}

resource "aws_route53_resolver_rule" "corp" {
  name                 = "corp-internal"
  domain_name          = "corp.internal"
  rule_type            = "FORWARD"
  resolver_endpoint_id = aws_route53_resolver_endpoint.outbound.id

  target_ip { ip = "192.168.10.10" }
  target_ip { ip = "192.168.10.11" }
}

resource "aws_route53_resolver_rule_association" "corp_vpc_main" {
  resolver_rule_id = aws_route53_resolver_rule.corp.id
  vpc_id           = aws_vpc.main.id
}

A rule only takes effect in VPCs it's associated with. That word is doing a lot of work — remember it for mistake #5.

Step 3 — share the rules across the Organization. If you have more than a handful of accounts, do not recreate rules everywhere. Create them once in a networking account and share via AWS RAM:

resource "aws_ram_resource_share" "resolver_rules" {
  name                      = "resolver-rules"
  allow_external_principals = false
}

resource "aws_ram_resource_association" "corp" {
  resource_arn       = aws_route53_resolver_rule.corp.arn
  resource_share_arn = aws_ram_resource_share.resolver_rules.arn
}

resource "aws_ram_principal_association" "org" {
  principal          = data.aws_organizations_organization.this.arn
  resource_share_arn = aws_ram_resource_share.resolver_rules.arn
}

Spoke accounts then just associate the shared rule with their VPCs. In our 22-account setup this is the difference between "DNS is managed" and "DNS is 22 slightly different snowflakes". One outbound endpoint in the hub serves the whole Organization — the rule points at the hub's endpoint regardless of which VPC the query starts in, as long as your Transit Gateway or peering can carry the traffic back.

Step 4 — the inbound endpoint and the on-prem side.

resource "aws_route53_resolver_endpoint" "inbound" {
  name               = "from-onprem"
  direction          = "INBOUND"
  security_group_ids = [aws_security_group.resolver.id]

  ip_address { subnet_id = aws_subnet.private_a.id, ip = "10.20.5.10" }
  ip_address { subnet_id = aws_subnet.private_b.id, ip = "10.20.6.10" }
}

Pin the IPs explicitly — your on-prem DNS team will configure conditional forwarders pointing at them, and you do not want those IPs changing on a rebuild. On Windows DNS: New Conditional Forwarder → internal.example.com → both inbound IPs. On BIND, a forwarders block in a zone stanza. Same idea anywhere: for this domain, ask AWS.

Step 5 — test both directions before declaring victory. From an EC2 instance: dig intranet.corp.internal (should come back with an on-prem answer). From an on-prem box: dig db.prd.internal.example.com @10.20.5.10, then again without the @ once the forwarder is configured. Test from a spoke VPC too, not just the hub. Then test something that only exists in a private hosted zone, and something behind PrivateLink. Five minutes of testing here saves the 2am call later.

The six classic mistakes

1. The circular forwarder (the infinite DNS loop). This is the one that earns war-story status. On-prem forwards example.com to the inbound endpoint. Someone later creates an outbound rule forwarding example.com back to on-prem — maybe because one subdomain genuinely lives there. Now a query for a name that doesn't exist anywhere bounces: on-prem → inbound → (no answer in AWS, rule matches) → outbound → on-prem → inbound → ... until something times out. Symptoms: slow SERVFAILs, resolver ENIs busy doing nothing useful, query volume that makes no sense.

The rule that prevents it: never forward a domain to a resolver that forwards the same domain back to you. Split the namespace instead — aws.example.com lives in Route 53 and on-prem forwards it to inbound; corp.example.com lives on-prem and AWS forwards it out. Clean boundaries, no cycles. If a domain genuinely must be split at a deeper level, forward the specific subdomains, never the parent from both sides.

2. Pointing instances at on-prem DNS via DHCP option sets. It feels like the obvious shortcut: change the VPC's DHCP options to hand out 192.168.10.10 and skip the whole endpoint conversation. Then private hosted zones stop resolving, VPC endpoint and PrivateLink names stop resolving, EKS gets weird, and every DNS packet from every instance crosses your Direct Connect. Leave AmazonProvidedDNS alone and let VPC+2 do the routing via rules. The DHCP shortcut is how you trade one week of proper setup for a year of mystery incidents.

3. Security group without TCP/53. DNS uses UDP until the answer doesn't fit or DNSSEC gets involved — then it retries over TCP. If your resolver security group only allows UDP, small answers work and large ones fail. The symptom is beautiful in its cruelty: dig works, the application intermittently doesn't, and the failure correlates with which name you look up. Allow TCP and UDP 53, both directions that apply, and move on with your life.

4. Forgetting rule associations in spoke VPCs. The rule exists, the endpoint works, the hub VPC resolves fine — and the new application VPC gets NXDOMAIN. Rules don't apply Organization-wide by magic; each VPC needs the association (RAM sharing gets the rule visible to the account; associating it to the VPC is still a step). Bake the association into your VPC Terraform module so no one has to remember. Same story for private hosted zones — a PHZ answers only in VPCs it's associated with, and cross-account PHZ association is a two-step CLI dance (create-vpc-association-authorization on the zone owner, associate-vpc-with-hosted-zone on the VPC owner) that nobody discovers until it fails.

5. Under-provisioned endpoints and mystery latency. Each endpoint IP handles up to about 10,000 queries per second, and — the part people miss — VPC+2 itself caps at 1,024 packets per second per ENI. A busy EKS node can hit that limit all by itself, thanks to ndots:5 turning every lookup of api.example.com into five queries. The fix on the cluster side is NodeLocal DNSCache (and sensible ndots in pod specs); on the endpoint side, add IPs before you need them and watch the InboundQueryVolume/OutboundQueryVolume metrics. DNS latency looks exactly like application latency to everyone upstream of you.

6. Nobody can see what's being asked. Turn on Resolver query logging from day one — to S3, cheap, same pattern as flow logs (I wrote a whole piece on querying that kind of data with Athena). When something misbehaves, the query log answers in one search what "can you try nslookup again?" answers in an afternoon: what was asked, by which ENI, and what came back. The pair of flow logs + query logs is the closest thing AWS gives you to network omniscience, and together they cost less than one incident bridge call.

The mental model that makes it all stick

Resolution order inside a VPC, simplified but honest: the resolver first matches your forwarding rules (most specific domain wins), then private hosted zones and autodefined AWS names, then public recursion. When two rules could match, the longer domain name takes precedence — db.corp.internal beats corp.internal. That's the entire debugging framework: for any failing name, ask "which of the three paths should this take, and which one is it actually taking?" dig from inside the VPC plus the query logs answer the second half.

Cost, for the FinOps-minded: endpoints are billed per ENI-hour plus per-query, and the minimum HA setup (2 IPs inbound + 2 outbound) runs a few hundred dollars a year — check current pricing for your region. Compare that to a shared services architecture where one hub endpoint pair serves 22 accounts, and the per-account cost becomes a rounding error. Centralize; this is one of the clearest hub-and-spoke wins in AWS networking.

Hybrid DNS is unforgiving of improvisation and very kind to boring, explicit design: one clean namespace boundary, rules shared from a hub, TCP and UDP open, associations automated in the VPC module, query logging on. Do those things and DNS quietly stops being on your incident bingo card — which, in this line of work, is the highest compliment an infrastructure component can earn.

I write about AWS networking from the packet up — VPC, hybrid connectivity, EKS networking. Previous article: querying VPC Flow Logs with Athena. Follow along here or on LinkedIn.

Transit Gateway vs VPC Peering vs PrivateLink: a Decision Framework from a 22-Account Org

Raphael Trovão — Sun, 12 Jul 2026 18:56:21 +0000

The question lands in my queue almost every week, always wearing different clothes: "the team in account A needs to reach a service in account B — can you connect us?" Simple request. Except AWS gives you three completely different tools that all answer it, and picking the wrong one either costs you money forever or walls you in six months later.

I run networking for a 22-account AWS Organization, and over the years we've used all three — Transit Gateway as our backbone, peering where it quietly saves us real money, and PrivateLink in the places where the other two would have been a mistake. This is the decision framework I wish someone had handed me at account number five, with the actual costs and the practical limits that the comparison tables usually skip.

The three tools, without the marketing

VPC Peering is a cable. A private, point-to-point connection between exactly two VPCs. Traffic flows both ways, you update route tables on both sides, done. The two things that define it: it's non-transitive (A↔B and B↔C does not give A↔C, ever), and it has no hourly charge — you pay only for data transfer, and traffic that stays within the same AZ over a peering costs nothing.

Transit Gateway is a router. A managed hub that every VPC (and your VPN and Direct Connect) attaches to once. It's transitive by design — everyone attached can reach everyone else, unless you say otherwise, and "saying otherwise" is the feature: TGW route tables let you build isolated routing domains (production can't see staging, everything inbound goes through an inspection VPC) the same way a real network engineer would with VRFs. You pay for that convenience twice: per attachment-hour and per GB processed.

PrivateLink is not a network connection at all. This is the one people misfile. PrivateLink exposes one service — something behind a Network Load Balancer — as an ENI that materializes inside the consumer's VPC. There's no routing between the networks. No CIDRs exchanged. The consumer sees an IP in their own subnet and nothing else of yours. It's a socket, not a cable, and one-directional by design.

If you remember only one line from this article: PrivateLink for services, peering for pairs, Transit Gateway for networks.

The money, because it decides more than we admit

Ballpark numbers for us-east-1 at the time of writing — check the pricing pages, these move:

	Hourly	Per GB	The catch
VPC Peering	none	data transfer only (free same-AZ, ~$0.01/GB each side cross-AZ intra-region)	cheapest by far for heavy flows
Transit Gateway	~$0.05 per attachment	~$0.02 processing on every GB	the $0.02 applies to ALL traffic, forever
PrivateLink	~$0.01 per endpoint per AZ	~$0.01	per-service, per-consumer endpoints add up

The number that matters is Transit Gateway's $0.02 per GB processed. It sounds like nothing until you do the multiplication that FinOps taught me to always do: a steady 1 TB/day between two accounts is roughly $600/month in TGW processing — for traffic that would ride a peering connection for a fraction of that, or for free if you keep it same-AZ. We found exactly this pattern in our environment: two accounts exchanging heavy replication traffic 24/7, dutifully routed through the TGW because "everything goes through the TGW". Moving that one pair to a direct peering paid for the time it took before the month closed.

That's not an argument against TGW. It's an argument against defaults. The TGW fee buys you transitivity, segmentation, and one attachment instead of N peerings — genuinely worth it for the general case. Just don't make your top-talker pairs pay the router tax when they don't need routing.

The framework: four questions in order

Question 1: are you connecting networks, or exposing a service? If account B just needs to consume an API that account A runs, stop — that's PrivateLink. Don't connect two entire CIDR spaces so one app can reach one port. This question alone reroutes half the requests I get, and it's also the security answer: the consumer gets an ENI, not a network path. Least privilege, but for topology.

Question 2: do the CIDRs overlap? Peering flatly refuses overlapping CIDRs, and TGW routing can't fix ambiguity either. If you're connecting to an acquired company, a partner, or your own legacy sins, overlapping ranges force the answer: PrivateLink doesn't care about CIDRs at all. It's the escape hatch that turns "we'd have to re-IP the whole environment" into a Tuesday task.

Question 3: how many VPCs, and is it growing? Two or three VPCs in a stable relationship: peer them and enjoy the free lunch. But peering meshes grow as n(n−1)/2 — at 10 VPCs that's 45 peerings and 45 pairs of route table entries to maintain, and there's a hard cap of 125 peerings per VPC before AWS stops you anyway. Somewhere between 4 and 10 VPCs, the honest math flips to TGW: one attachment each, routing managed in one place, and your on-prem connectivity (VPN/Direct Connect) plugs into the same hub. Ours flipped long ago; with 22 accounts there was no real decision left to make about the backbone.

Question 4: do you need segmentation or inspection? If compliance says production and non-production must not talk, or that cross-environment traffic goes through a firewall, TGW route tables are the tool — attach the inspection VPC, propagate routes asymmetrically, done. Peering has no equivalent; you'd be building segmentation out of security groups and hope. In our court environment, this requirement alone justified the TGW before any convenience argument.

The limits the comparison tables skip

A few practical ceilings that have bitten people I know (occasionally including me):

Peering, besides being non-transitive and capped at 125 per VPC, also doesn't propagate DNS by default — enable DNS resolution support on the connection or private hosted zone names won't resolve across it, which presents as the world's most confusing intermittent bug. Transit Gateway attachments have bandwidth ceilings (generous, up to 100 Gbps per VPC attachment these days, but VPN attachments cap at 1.25 Gbps per tunnel — plan ECMP if you need more). PrivateLink requires the service side to sit behind an NLB (or use a Gateway Load Balancer for appliances), each endpoint serves one service, and if you expose many services to many consumers, the endpoint-hours quietly become their own line item — bundle related APIs behind one NLB where it makes sense.

And one that spans all three: DNS is a separate decision. Connecting the networks doesn't make names resolve — private hosted zone associations, Resolver rules and endpoint policies are their own layer, and forgetting them is the #1 "it's connected but nothing works" cause. I wrote a whole piece on hybrid DNS for exactly this reason.

What 22 accounts actually looks like

Our topology, as a reference point rather than a prescription: a Transit Gateway backbone in the network account with separate route domains for production, non-production and shared services, an inspection VPC on the cross-domain paths, and Direct Connect into the same hub for the on-prem datacenter. Peering exists in exactly the places where a pair of VPCs exchanges heavy, predictable traffic and the TGW tax made no sense. PrivateLink exposes our shared internal services to workload accounts, and it's the only pattern we allow for anything crossing an organizational trust boundary — partners see an ENI, never a route.

Boring, explicit, and every flow has a reason it takes the path it takes. In multi-account networking, boring is the compliment.

The framework fits in one breath: expose a service → PrivateLink. Connect a stable pair (especially a chatty one) → peering. Connect many networks with segmentation and hybrid → Transit Gateway. Ask the four questions in order, run the per-GB math on your top talkers before defaulting everything to the hub, and remember DNS is always a second decision. That's it — that's the whole trick, and it took 22 accounts to learn.

I write about AWS networking from the packet up — VPC, hybrid DNS, EKS networking, and multi-account architecture. Previous articles: VPC Flow Logs with Athena and hybrid DNS with Route 53 Resolver. Follow along here or on LinkedIn.

VPC Flow Logs Beyond the Console: Finding Real Answers with Athena

Raphael Trovão — Sun, 05 Jul 2026 16:02:44 +0000

A few months ago someone asked me a question that sounds simple: "can you tell me everything that talked to this database in the last 48 hours?" We run a 22-account AWS Organization for a court system, so questions like that aren't curiosity. They're audit requests, and "I think it was the app servers" is not an acceptable answer.

The console's Flow Logs viewer gets you nowhere with that question. CloudWatch Logs Insights can do it, but at CloudWatch ingestion prices, keeping flow logs there across a whole Organization is the kind of line item that shows up in your FinOps review with a red circle around it.

The setup that actually works, and costs a fraction: Flow Logs delivered to S3 in Parquet, a lifecycle policy so old logs age out of your bill, and an Athena table on top so you can answer real questions with SQL. Enable it once, and you have a queryable record of every network conversation in your VPC.

This article is the full walkthrough: enabling it properly (there are three checkboxes people miss that make Athena 10x cheaper), the table DDL, and ten copy-paste queries I actually use. Grab a coffee.

First, what flow logs are — and what they are not

A flow log record is metadata about a network flow: source and destination IP, ports, protocol, bytes, packets, whether the traffic was accepted or rejected, and (in newer versions) very useful extras like which AWS service the traffic went to and which path it took to leave the VPC.

What it is not: packet capture. There's no payload, no HTTP headers, no DNS names. If you need to know what was said, you need tcpdump or Traffic Mirroring. Flow logs tell you who talked to whom, when, how much, and whether the firewall let it happen. In my experience that answers about 80% of the questions that reach a network team.

One more thing worth knowing: records are aggregated over an interval (1 or 10 minutes), so a "flow" is a summary, not individual packets. For forensics and cost analysis that's exactly what you want.

Why S3 instead of CloudWatch Logs

Short version: delivery to S3 costs roughly half of CloudWatch ingestion per GB, and then storage is S3 storage — pennies, and you control it with lifecycle rules. CloudWatch keeps charging you ingestion for every GB, and flow logs are chatty. On a busy VPC the difference is not academic; it's the difference between "always on everywhere" and "we only enable it during incidents", and flow logs you didn't enable before the incident are worth exactly nothing.

The trade-off is losing Logs Insights and near-real-time alerting on the raw stream. For alerting, GuardDuty already analyzes flow log data internally without you enabling anything. For investigation — which is what you actually do with flow logs — Athena on S3 is better anyway: real SQL, joins, and you pay per query instead of per GB stored.

Check the current numbers on the pricing page for your region before quoting them to your boss, but in every account I've run the math for, S3 wins by a wide margin.

Enabling it right: the three options that matter

You can enable flow logs at the VPC, subnet, or ENI level. Enable at the VPC level — it covers every ENI in the VPC, including the ones that get created tomorrow.

When you create the flow log (Console: VPC → your VPC → Flow logs → Create flow log), the defaults will work, and they will also quietly cost you money later. Set these:

1. Format: Parquet, not plain text. Under "Log file format", pick Parquet. Athena scans columnar Parquet dramatically more efficiently than gzipped text — AWS's own docs cite queries running faster and cheaper, and in practice I've seen scans drop to a tenth of the bytes. This single checkbox is most of your future Athena bill.

2. Hive-compatible prefixes + partition by hour. Two more checkboxes ("Hive-compatible S3 prefix", "Partition logs by time"). They organize the S3 layout so Athena can skip everything outside the time window you're querying. Without partitions, every query scans your entire history; with them, "yesterday, 2pm to 4pm" scans two hours of data.

3. Custom format with all the fields. The default format is fine, but the v3–v7 fields are where the good stuff lives. Select all fields — storage is cheap and you can't add fields retroactively. The ones you'll thank yourself for later: flow-direction, traffic-path, pkt-src-aws-service, pkt-dst-aws-service, tcp-flags, az-id, and pkt-srcaddr/pkt-dstaddr (the original addresses when traffic goes through a NAT gateway — without these, everything behind NAT looks like the NAT).

In Terraform, the whole thing looks like this:

resource "aws_flow_log" "vpc" {
  vpc_id                   = aws_vpc.main.id
  traffic_type             = "ALL"
  log_destination_type     = "s3"
  log_destination          = aws_s3_bucket.flow_logs.arn
  max_aggregation_interval = 600   # 10 min; use 60 for finer forensics, ~more records

  destination_options {
    file_format                = "parquet"
    hive_compatible_partitions = true
    per_hour_partition         = true
  }

  # All fields up to v7 — order matters, it defines your table schema
  log_format = "$${version} $${account-id} $${interface-id} $${srcaddr} $${dstaddr} $${srcport} $${dstport} $${protocol} $${packets} $${bytes} $${start} $${end} $${action} $${log-status} $${vpc-id} $${subnet-id} $${instance-id} $${tcp-flags} $${type} $${pkt-srcaddr} $${pkt-dstaddr} $${region} $${az-id} $${pkt-src-aws-service} $${pkt-dst-aws-service} $${flow-direction} $${traffic-path}"
}

The bucket: policy and lifecycle

The delivery service needs permission to write. If you create the flow log through the console pointing at your bucket, AWS adds this policy for you; in Terraform you own it:

resource "aws_s3_bucket" "flow_logs" {
  bucket = "myorg-vpc-flow-logs-prd"
}

data "aws_iam_policy_document" "flow_logs_delivery" {
  statement {
    principals {
      type        = "Service"
      identifiers = ["delivery.logs.amazonaws.com"]
    }
    actions   = ["s3:PutObject"]
    resources = ["${aws_s3_bucket.flow_logs.arn}/*"]
    condition {
      test     = "StringEquals"
      variable = "s3:x-amz-acl"
      values   = ["bucket-owner-full-control"]
    }
  }
  statement {
    principals {
      type        = "Service"
      identifiers = ["delivery.logs.amazonaws.com"]
    }
    actions   = ["s3:GetBucketAcl"]
    resources = [aws_s3_bucket.flow_logs.arn]
  }
}

resource "aws_s3_bucket_policy" "flow_logs" {
  bucket = aws_s3_bucket.flow_logs.id
  policy = data.aws_iam_policy_document.flow_logs_delivery.json
}

Now the part everyone forgets until the storage line starts growing: lifecycle. Decide how long flow logs are useful to you. My rule of thumb: hot for 30 days (that covers almost every investigation), cold for audit retention, gone after that. Adjust the archive tier and the expiration to your compliance requirements — in a regulated environment, that number comes from your retention policy, not from a blog post:

resource "aws_s3_bucket_lifecycle_configuration" "flow_logs" {
  bucket = aws_s3_bucket.flow_logs.id

  rule {
    id     = "flow-logs-aging"
    status = "Enabled"

    transition {
      days          = 30
      storage_class = "STANDARD_IA"
    }
    transition {
      days          = 90
      storage_class = "GLACIER"
    }
    expiration {
      days = 365
    }
    abort_incomplete_multipart_upload {
      days_after_initiation = 7
    }
  }
}

One caveat: Athena can't query objects that already moved to Glacier. Your queryable window is whatever you keep in Standard/IA. That's another reason the 30/90 split works well.

The Athena table

Create a workgroup first if you don't have one, set a query result location, and — do this, seriously — set a per-query data scan limit. It turns a careless SELECT * over a year of logs from a bill into an error message.

Then the table. Because we enabled Hive-compatible prefixes and hourly partitions, we can use partition projection and never think about MSCK REPAIR TABLE or Glue crawlers again — Athena computes the partitions from the template:

CREATE EXTERNAL TABLE IF NOT EXISTS vpc_flow_logs (
  version               int,
  account_id            string,
  interface_id          string,
  srcaddr               string,
  dstaddr               string,
  srcport               int,
  dstport               int,
  protocol              bigint,
  packets               bigint,
  bytes                 bigint,
  start                 bigint,
  `end`                 bigint,
  action                string,
  log_status            string,
  vpc_id                string,
  subnet_id             string,
  instance_id           string,
  tcp_flags             int,
  type                  string,
  pkt_srcaddr           string,
  pkt_dstaddr           string,
  region                string,
  az_id                 string,
  pkt_src_aws_service   string,
  pkt_dst_aws_service   string,
  flow_direction        string,
  traffic_path          int
)
PARTITIONED BY (
  `aws-account-id` string,
  `aws-region`     string,
  `year`           string,
  `month`          string,
  `day`            string,
  `hour`           string
)
STORED AS PARQUET
LOCATION 's3://myorg-vpc-flow-logs-prd/AWSLogs/'
TBLPROPERTIES (
  "projection.enabled"                  = "true",
  "projection.aws-account-id.type"      = "injected",
  "projection.aws-region.type"          = "enum",
  "projection.aws-region.values"        = "us-east-1,sa-east-1",
  "projection.year.type"                = "integer",
  "projection.year.range"               = "2024,2030",
  "projection.month.type"               = "integer",
  "projection.month.range"              = "1,12",
  "projection.month.digits"             = "2",
  "projection.day.type"                 = "integer",
  "projection.day.range"                = "1,31",
  "projection.day.digits"               = "2",
  "projection.hour.type"                = "integer",
  "projection.hour.range"               = "0,23",
  "projection.hour.digits"              = "2",
  "storage.location.template"           = "s3://myorg-vpc-flow-logs-prd/AWSLogs/aws-account-id=${aws-account-id}/aws-service=vpcflowlogs/aws-region=${aws-region}/year=${year}/month=${month}/day=${day}/hour=${hour}"
)

Adjust the bucket name, the region list, and the year range. The column names match the custom format we defined — dashes become underscores in Parquet. If you used a different field order, your table must match it: the format string is your schema contract.

Two habits that keep queries fast and cheap: always filter on the partition columns (year, month, day, hour — and aws-account-id if you centralize the Organization's logs in one bucket), and select only the columns you need. Parquet only reads what you ask for.

The 10 queries

All of these assume you replace the date filters with your window. I'm using a WHERE block like this everywhere — copy it once:

-- reusable time filter: adjust and paste into each query
WHERE year = '2026' AND month = '07' AND day = '03'

1. Who talked to this IP? (the audit question)

SELECT srcaddr, dstaddr, dstport, protocol, action,
       SUM(bytes) AS total_bytes, COUNT(*) AS flows
FROM vpc_flow_logs
WHERE year = '2026' AND month = '07' AND day = '03'
  AND (srcaddr = '10.20.5.44' OR dstaddr = '10.20.5.44')
GROUP BY srcaddr, dstaddr, dstport, protocol, action
ORDER BY total_bytes DESC;

2. What are my security groups and NACLs rejecting, and who's knocking?

SELECT srcaddr, dstaddr, dstport, COUNT(*) AS rejected_flows
FROM vpc_flow_logs
WHERE year = '2026' AND month = '07' AND day = '03'
  AND action = 'REJECT'
  AND flow_direction = 'ingress'
GROUP BY srcaddr, dstaddr, dstport
ORDER BY rejected_flows DESC
LIMIT 50;

3. Exposure check: is anything from the internet actually reaching SSH/RDP?

SELECT srcaddr, dstaddr, dstport, COUNT(*) AS flows, SUM(bytes) AS total_bytes
FROM vpc_flow_logs
WHERE year = '2026' AND month = '07' AND day = '03'
  AND dstport IN (22, 3389)
  AND action = 'ACCEPT'
  AND flow_direction = 'ingress'
  AND NOT regexp_like(srcaddr, '^(10\.|172\.(1[6-9]|2[0-9]|3[01])\.|192\.168\.)')
GROUP BY srcaddr, dstaddr, dstport
ORDER BY flows DESC;

If this returns rows you can't explain, stop reading and go fix a security group.

4. Top talkers by volume (start of every data transfer cost investigation)

SELECT srcaddr, dstaddr, pkt_dst_aws_service,
       SUM(bytes) AS total_bytes
FROM vpc_flow_logs
WHERE year = '2026' AND month = '07' AND day = '03'
  AND flow_direction = 'egress'
GROUP BY srcaddr, dstaddr, pkt_dst_aws_service
ORDER BY total_bytes DESC
LIMIT 25;

5. Who is burning the NAT Gateway? (NAT processing charges are a classic FinOps finding — this names the culprits)

-- interface_id = the NAT Gateway's ENI (find it in the console or via CLI)
SELECT pkt_srcaddr AS original_source, dstaddr, dstport,
       SUM(bytes) AS total_bytes
FROM vpc_flow_logs
WHERE year = '2026' AND month = '07' AND day = '03'
  AND interface_id = 'eni-0abc123def456'
  AND flow_direction = 'egress'
GROUP BY pkt_srcaddr, dstaddr, dstport
ORDER BY total_bytes DESC
LIMIT 25;

Note pkt_srcaddr — that's the field that survives NAT and shows you the actual pod or instance.

6. East-west heavy hitters (candidates for same-AZ placement or a cost conversation)

SELECT srcaddr, dstaddr, az_id, SUM(bytes) AS total_bytes
FROM vpc_flow_logs
WHERE year = '2026' AND month = '07' AND day = '03'
  AND regexp_like(srcaddr, '^10\.')
  AND regexp_like(dstaddr, '^10\.')
GROUP BY srcaddr, dstaddr, az_id
ORDER BY total_bytes DESC
LIMIT 25;

Cross-reference the top pairs with your subnet-to-AZ map: high-volume pairs crossing AZs are paying the cross-AZ tax on every byte, twice.

7. Hybrid debugging: everything crossing to on-prem

-- replace with your on-prem CIDR
SELECT srcaddr, dstaddr, dstport, action,
       SUM(bytes) AS total_bytes, COUNT(*) AS flows
FROM vpc_flow_logs
WHERE year = '2026' AND month = '07' AND day = '03'
  AND (regexp_like(dstaddr, '^192\.168\.') OR regexp_like(srcaddr, '^192\.168\.'))
GROUP BY srcaddr, dstaddr, dstport, action
ORDER BY total_bytes DESC;

When "the application can't reach the mainframe", this tells you in one query whether traffic is leaving, being rejected, or never arriving back.

8. The asymmetric NACL bug, caught red-handed

Stateless NACLs need the return path open on ephemeral ports. When someone forgets, the request goes out and the response gets rejected — which looks exactly like a timeout from the application side:

SELECT srcaddr, srcport, dstaddr, dstport, COUNT(*) AS rejected_returns
FROM vpc_flow_logs
WHERE year = '2026' AND month = '07' AND day = '03'
  AND action = 'REJECT'
  AND srcport IN (80, 443, 5432, 3306, 1433)   -- responses FROM service ports
  AND dstport BETWEEN 1024 AND 65535           -- TO ephemeral ports
GROUP BY srcaddr, srcport, dstaddr, dstport
ORDER BY rejected_returns DESC;

Rows here = a NACL missing its ephemeral return rule. This query has ended multi-hour debugging sessions in five minutes.

9. Port scan detection (one source, many ports)

SELECT srcaddr, COUNT(DISTINCT dstport) AS distinct_ports,
       COUNT(*) AS attempts
FROM vpc_flow_logs
WHERE year = '2026' AND month = '07' AND day = '03'
  AND flow_direction = 'ingress'
GROUP BY srcaddr
HAVING COUNT(DISTINCT dstport) > 100
ORDER BY distinct_ports DESC;

10. Connections attempted but never established (SYN with no reply)

tcp_flags in a flow record is the OR of the flags seen in the window. A flow showing only SYN (value 2) means somebody tried and nobody answered — great for spotting half-open scans, dead dependencies, or a service that stopped listening:

SELECT srcaddr, dstaddr, dstport, COUNT(*) AS syn_only_flows
FROM vpc_flow_logs
WHERE year = '2026' AND month = '07' AND day = '03'
  AND tcp_flags = 2
  AND protocol = 6
GROUP BY srcaddr, dstaddr, dstport
ORDER BY syn_only_flows DESC
LIMIT 50;

What this setup won't do

Flow logs won't give you DNS names (pair them with Route 53 Resolver query logs and you can join IPs to the names that were resolved — that's a future article), won't show payloads, and won't capture traffic to some AWS-internal interfaces (the docs keep a list of exclusions — instance metadata and Amazon DNS among them). And remember the Glacier caveat: your Athena window is what's still in Standard/IA.

None of that reduces the value here. For the daily reality of a network team — "who talked to X", "why is this timing out", "where is the NAT bill coming from", "is anything from outside reaching port 22" — this is the highest ratio of answers-per-dollar I know on AWS.

Enable it before you need it. Flow logs are like backups that way: the ones you start collecting after the incident tell you nothing about the incident.

If this was useful, I write about AWS networking from the packet up — VPC, hybrid DNS, EKS networking. Follow along here or on LinkedIn.