ERP Data Extract Interface Specification Template
Scheduled extracts become critical long before anyone documents them. This template captures the details that matter when the job fails at 5am: where it reads, what it writes, who owns it and what happens on error.
Fill in
ERP Data Extract Interface Specification Template
INT-OUT-014: Daily open order extract to planning tool
Business owner: Head of Planning Source: ERP production database, open sales order lines view Target: Supply planning application
Transfer
Method: SFTP file drop Format: CSV UTF-8 Schedule: Daily at 05:30 local time, after the nightly MRP run Volume: 15,000 to 25,000 rows per file Load type: Full snapshot
File layout
| Seq | Field | Type | Length | Required | Rule |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 |
Error handling and monitoring
Job retries once after 15 minutes. On second failure an email goes to the support group and the previous file stays in place.
Controls
| Control | Method | Owner |
|---|---|---|
| Row count check against source | Header or trailer record | |
| Duplicate key check | Target load validation | |
| File naming and archive | Date stamped name, 30 day archive | |
| Credentials rotation | Per security policy |
Sign-off
Add the real field list to the file layout table after export, or copy it from your data dictionary. Include a sample file with the final specification.
Review your extract estate
Optional: an expert review of which scheduled ERP extracts could be retired or replaced.
Optional. The tool and every export work without this. No spam.
How to use the specification
Fill the header fields, then complete the file layout table with one row per field. Decide full or incremental load deliberately: a full snapshot is simpler and self healing, while an incremental load is lighter but needs reliable change keys and a way to handle deletes. Export the document and store it beside the job code.
Choosing the file format
CSV with UTF-8 is the easiest to inspect and the most portable, but delimiters and line breaks inside text fields must be quoted. Fixed width is still common with mainframe and legacy partners. JSON or XML suit nested data. Whatever you choose, document the decimal separator, date format, null handling and character encoding, since these cause most load failures.
- Date format such as ISO 8601
- Decimal point and thousands rules
- Quoting and escaping of delimiters
- Header row present or not
Controls that prevent silent failure
A job that runs and writes an empty file looks successful. Add a row count check, a minimum row threshold, and an alert when the file is late or smaller than expected. Keep the previous good file until a new one passes validation. These small controls prevent stale or empty data from feeding planning and finance decisions.
Common mistakes
Typical gaps are no named owner, credentials in the script, no archive, undocumented time zone, and extracts that run during month end processing. Decide the schedule relative to the jobs it depends on, such as MRP, cost rollups and period close, not just a clock time.
Frequently Asked Questions
What should every extract specification include?
Source, target, schedule, layout, load type, volume, error handling, monitoring and an owner. If a new person cannot find the job, understand its output and know who to call, the specification is incomplete.
Full snapshot or incremental?
Start with a full snapshot unless volume forces otherwise. It handles deletes and corrections naturally. Move to incremental when files get large, and then design explicitly for deletes and late changes.
How do I test an extract?
Compare row counts and key totals with the source on a sample day, load the file into the target in a test environment, and test failure paths such as an unavailable source or a locked file.
Where should the specification be stored?
Next to the job definition or script in version control or your IT documentation system, with a link from the job register. Keep it updated whenever the layout or schedule changes.
If maintaining dozens of file extracts is eating your analyst time, we can discuss replacing the simple ones with governed, on-premises query access and keeping files only where partners need them.
Related Tools
Scheduled SQL Job Register Worksheet
An editable register of scheduled jobs with daily runtime, dependencies, owners and failure contacts.
Developer ToolsERP Data Dictionary Worksheet
An editable table to document ERP tables and columns with type, length, null rate, fill rate and owner.
ERP OperationsERP Data Request Form Template
A fill-in form that turns a vague 'can you pull me a report' email into a complete, approvable ERP data request.
Developer ToolsERP SQL Query Catalog Worksheet
An editable inventory of the queries and reports your team runs, with owner, frequency, runtime and monthly effort.