EDI 270/271 Eligibility Guide for Data Engineers
The EDI 270/271 eligibility transaction drives real-time benefits verification across healthcare. Learn staging schema design, 271 response parsing, Snowflake DDL, and SQL for eligibility analytics and denied-claim investigation.
What Is EDI 270/271?
The X12 270/271 transaction pair enables real-time and batch eligibility verification — a provider or clearinghouse sends a 270 inquiry asking whether a patient is covered, and the payer responds with a 271 containing coverage status, benefit levels, deductibles, and authorization requirements.
In data warehouses, 270/271 data serves multiple purposes: pre-service revenue cycle optimization, retroactive eligibility verification for denied claims, and population-level coverage analytics. The challenge is that 271 responses are highly variable — different payers return different levels of detail and use different code combinations for equivalent benefit structures.
270/271 Staging DDL
-- EDI 270 Eligibility Inquiry CREATE TABLE edi_270_inquiry ( inquiry_key UUID PRIMARY KEY DEFAULT gen_random_uuid(), interchange_ctrl_no VARCHAR(9) NOT NULL, inquiry_ts TIMESTAMP NOT NULL DEFAULT NOW(), inquiry_type VARCHAR(10) NOT NULL DEFAULT 'REALTIME', -- REALTIME, BATCH payer_id VARCHAR(20) NOT NULL, provider_npi VARCHAR(10) NOT NULL, mbr_id VARCHAR(20), mbr_first_nm VARCHAR(100), mbr_last_nm VARCHAR(100), mbr_dob DATE, mbr_gender_cd VARCHAR(1), sbscrbr_id VARCHAR(20), svc_type_cd VARCHAR(3), -- service type being queried svc_dt DATE, -- date of service being verified status_cd VARCHAR(10) NOT NULL DEFAULT 'PENDING', raw_request VARCHAR -- original 270 file text ); -- EDI 271 Eligibility Response CREATE TABLE edi_271_response ( response_key UUID PRIMARY KEY DEFAULT gen_random_uuid(), inquiry_key UUID REFERENCES edi_270_inquiry(inquiry_key), payer_id VARCHAR(20) NOT NULL, verified_ts TIMESTAMP NOT NULL DEFAULT NOW(), -- Coverage status elig_sts_cd VARCHAR(2) NOT NULL, -- 1=active, 6=inactive elig_sts_desc VARCHAR(100), plan_nm VARCHAR(200), plan_begin_dt DATE, plan_end_dt DATE, grp_nbr VARCHAR(20), grp_nm VARCHAR(200), -- Deductible indvdl_ded_amt DECIMAL(10,2), -- individual deductible indvdl_ded_met_amt DECIMAL(10,2), -- amount already met fam_ded_amt DECIMAL(10,2), -- family deductible fam_ded_met_amt DECIMAL(10,2), -- Out-of-pocket indvdl_oop_max_amt DECIMAL(10,2), indvdl_oop_met_amt DECIMAL(10,2), fam_oop_max_amt DECIMAL(10,2), fam_oop_met_amt DECIMAL(10,2), -- Cost share copay_amt DECIMAL(10,2), coinsurance_pct DECIMAL(5,2), -- e.g. 20.00 for 20% netwrk_sts_cd VARCHAR(2), -- IN, OUT -- Prior auth prior_auth_req_flg BOOLEAN DEFAULT FALSE, prior_auth_desc VARCHAR(200), -- Raw response raw_response VARCHAR, -- original 271 file text rec_creat_ts TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE INDEX idx_271_mbr ON edi_270_inquiry(mbr_id, inquiry_ts); CREATE INDEX idx_271_payer ON edi_270_inquiry(payer_id, status_cd); CREATE INDEX idx_271_response ON edi_271_response(inquiry_key); CREATE INDEX idx_271_elig ON edi_271_response(elig_sts_cd, plan_end_dt);
Eligibility Analytics SQL
Members with Expiring Coverage
-- Members with coverage expiring in next 30 days
SELECT
i.mbr_id,
i.mbr_first_nm,
i.mbr_last_nm,
r.plan_nm,
r.plan_end_dt,
DATEDIFF(day, CURRENT_DATE, r.plan_end_dt) AS days_until_expiry
FROM edi_271_response r
JOIN edi_270_inquiry i ON i.inquiry_key = r.inquiry_key
WHERE r.elig_sts_cd = '1'
AND r.plan_end_dt BETWEEN CURRENT_DATE AND DATEADD(day, 30, CURRENT_DATE)
AND r.verified_ts = (
SELECT MAX(r2.verified_ts)
FROM edi_271_response r2
JOIN edi_270_inquiry i2 ON i2.inquiry_key = r2.inquiry_key
WHERE i2.mbr_id = i.mbr_id
)
ORDER BY r.plan_end_dt;Eligibility Check Success Rate by Payer
SELECT
i.payer_id,
COUNT(i.inquiry_key) AS total_inquiries,
COUNT(r.response_key) AS responses_received,
SUM(CASE WHEN r.elig_sts_cd = '1'
THEN 1 ELSE 0 END) AS active_coverage,
ROUND(100.0 * COUNT(r.response_key)
/ NULLIF(COUNT(i.inquiry_key), 0), 2) AS response_rate_pct
FROM edi_270_inquiry i
LEFT JOIN edi_271_response r ON r.inquiry_key = i.inquiry_key
WHERE i.inquiry_ts >= DATEADD(month, -1, CURRENT_DATE)
GROUP BY i.payer_id
ORDER BY total_inquiries DESC;Frequently Asked Questions
What is EDI 270/271?
The EDI 270 (Health Care Eligibility/Benefit Inquiry) is the X12 transaction used by providers and clearinghouses to query a payer about a patient's insurance coverage and benefits. The EDI 271 (Health Care Eligibility/Benefit Response) is the payer's response. Together they enable real-time eligibility verification at the point of care and batch pre-service eligibility checking for scheduled patients.
When should I use real-time vs batch eligibility checks?
Use real-time 270/271 at the point of registration or scheduling to get current eligibility status, deductible balances, and co-pay amounts for individual patients. Use batch 270/271 for pre-service eligibility sweeps — sending eligibility inquiries for all patients scheduled in the next 3-7 days, typically run nightly. Batch checks are also used for retroactive eligibility verification on denied claims.
What data should I store from a 271 response?
At minimum store: eligibility status (active/inactive), plan name and payer ID, coverage effective and termination dates, deductible amount and amount met year-to-date, out-of-pocket maximum and amount met, in-network vs out-of-network benefit levels, co-pay and coinsurance amounts by service type, and prior authorization requirements by service type. Also store the raw 271 response text for reprocessing if your parsing logic changes.
How do I handle eligibility changes between verification and date of service?
Store every eligibility response with a verified_ts timestamp and always re-verify at key touchpoints — at scheduling, at check-in, and for any claim denied for eligibility reasons. In your schema, maintain a history of all eligibility checks per member per payer rather than just the current status, so you can reconstruct what was known at the time of service for appeals and audits.
What are the most common EDI 271 response codes?
Service type codes (STC) in the 271 EB segment identify the benefit being described — 30=Health Benefit Plan Coverage, 1=Medical Care, 35=Dental Care, 48=Hospital Inpatient, 86=Emergency Services. Coverage level codes distinguish individual vs family benefits. Eligibility or benefit information codes (EB01) indicate active coverage (1), inactive (6), primary (1 with primary payer designation), or secondary (18). Store these codes with their lookup descriptions for readable reporting.