System: Cross-border order to delivery
Lanes: Ten in the registry, one ledger
Stack: Python · SQL · dbt · Snowflake · AWS · Terraform
Available: Remote · US and Canada hours
Contact: jc@iledevelop.com

I build and repair the data pipelines and warehouses that reporting depends on — on Snowflake, dbt, Python and AWS.

Typical work:

a warehouse or dbt project that has outgrown whoever built it
pipelines that fail quietly, and the tests and monitoring that stop that
a migration where last year's numbers still have to reconcile
US data infrastructure for a company headquartered in China

Available remote, US and Canada hours, by the hour or by the milestone. English and Mandarin.

Tell me what you need or email jc@iledevelop.com

Below: the platform I built for my own cross-border business, and what it is made of. It is the evidence, not the pitch.

A manifest is a declaration of what shipped.

Every problem in this system is a version of finding out whether the declaration was true — what the customer was quoted and at which exchange rate, what the tariff said on the day it sailed, which rule let it onto that carrier, and where it actually got to. Built because I run the business it describes, and a spreadsheet cannot answer any of those questions three weeks later.

Figure 1 — one order, the four lanes that meet in it, two questions answered at the end

Four things arrive: what a customer asked for, where the parcel got to, what the tariff said, and what the planner decided. They arrive at different times, in different shapes, and some of them arrive late. The ledger keeps all of it with two clocks, and the two models on the right answer questions none of the four lanes can answer alone.

Why a cross-border trade operator builds this, and not somebody else

The business is the credential, not the excuse

I run ILE Development US LLC, a cross-border e-commerce operation moving goods from North America to customers in China. It is a real business with real parcels, and it is the reason this platform exists — but not in the way that sentence usually means. It is not that the business gave me a spare dataset to practise on. It is that cross-border trade is a data-engineering problem I have to solve every week whether or not anyone pays me to.

Two jurisdictions, two sets of rules, and both sets move. A duty rate, an exchange rate and a retailer's shelf price all change underneath decisions that were already made — and three weeks later a customer asks why they were quoted what they were quoted. There is no answering that with a spreadsheet, because a spreadsheet holds one value per cell and the question is about a value that used to be different.

So the architecture is not a preference, it is the only shape that answers the question: an append-only log, every rate and rule carrying the date it applied, and every total balancing. Which is the same shape a regulated system needs, and the same shape a data platform needs when somebody asks what a model was trained on last quarter.

What the trade side means in engineering terms

Effective-dated reference data in five places. Idempotent ingestion, because carriers resend and a customer re-sends the same order sheet. Reconciliation, because goods that leave have to match goods that were booked. Bitemporal reconstruction, because the question is always as of when.

Where it transfers

A company headquartered in China and operating in the US now has to keep US data inside US-controlled infrastructure, with the access boundary built into the architecture rather than bolted on. That is an engineering problem, in a domain where I am a practitioner and not a visitor — and I can run the design conversation in Mandarin.

Scope, so there is no confusion: I build the architecture. Compliance opinions stay with your counsel. What I bring is that the data model and the rule-versioning are already how I think, because a parcel that crossed a border in June has to still be explainable in September.

Across everything on this page: Python · SQL · dbt · Snowflake · DuckDB · AWS (Lambda, Kinesis, SQS, S3, EventBridge, CloudWatch) · Terraform · Docker · RESTful API clients · GitHub Actions CI/CD · pytest · OR-Tools · Airflow · Apache Iceberg

What building it found

The pricing tool has no import-tax line at all

The spreadsheet my business runs on computes landed cost as retail price plus sales tax plus freight plus handling. That is what every tool of its kind does, and it has one expensive omission: there is no import tax in it. Not a wrong rate — no line at all. On a clothing parcel that tax is routinely the largest single cost after the garment itself, and the quote cannot see it.

A note on jurisdiction, because it decides which schedule applies. The tariff lane in this platform models the United States schedule — the USITC archive, effective-dated, because it is public, versioned and downloadable, which makes it the right corpus to prove the mechanism on. My own parcels go Canada to China, where the applicable regime is China's postal-articles tax, with a fixed rate per goods category and a per-parcel exemption threshold. Different schedule, same machinery: a rate is a fact with a date, read as-of the day the parcel moved. The Chinese lane is the next one to build; what is demonstrated here is the mechanism, on the archive that publishes its history.

Either way the omission is the finding, and it holds whichever schedule applies. Joining the quote to a tariff schedule produces the column the spreadsheet cannot.

Order	Duty rate	Quoted profit	Duty	True profit
A1002 · Scuba hoodie	32%	C$17.72	C$28.48	−C$10.76
A1001 · Align pant	16%	C$28.05	C$20.48	C$7.57
A1003 · Belt bag	12%	C$22.83	C$11.52	C$11.31
A1005 · Define jacket	32%	−C$45.80	C$44.16	−C$89.96

Fixture rates. The tariff lines are real HTS subheadings; the percentages are illustrative values in the fixtures, not read from the live archive — see Scope.

A1002 is the one that matters. On these figures it reads as a C$17.72 profit in the tool and a C$10.76 loss once duty is counted — and nothing about the order looked unusual. On A1001 duty takes 73% of the margin. Four orders is not a dataset and the rates are fixtures, so read the magnitudes as illustrative. The mechanism is not: the number that decides whether an order is worth taking was not in the system that quoted it.

Wiring the live USITC archive in place of the fixture is the next piece of work, and the lane is shaped for it — the ingest module has a RESTful API client written to fetch a named revision from the endpoint the fixtures are modelled on, with retry, exponential backoff and a circuit breaker, though nothing calls it against the live archive yet.

The lanes

Each answers one question the others cannot

Lane 1

Order intake — a quote is a decision, not a number

Replaces: A tool that takes an order from WeChat or 闲鱼, looks up the product page, works out a landed cost, applies a markup, caps the price against the Chinese shelf price, and prints the line to send back. It is correct, and it cannot answer why was this customer quoted this number?
Mechanism: Three things move underneath a quote: the exchange rate, the pricing settings, and the retailer's own price. So every quote stamps fx_rate, fx_effective_from and pricing_version — for exactly the reason a routing decision stamps its rule version. That needed the third piece of effective-dated reference data in the system: FX, read as-of the order date, by the same mechanism as the tariff schedule.
Guarantee: orders in = quoted + rejected. An order placed before any rate we hold is rejected with a reason, never priced at the earliest rate we happen to have. The payment link is deliberately not automatic — a pipeline that silently creates payment objects can charge somebody twice.
Stack: Python · SQL · dbt · DuckDB · pytest

Lane 2 · AWS

Courier tracking — API Gateway → SQS → Lambda → S3

Question: Carriers send webhooks. Scans arrive late, out of order, twice, and occasionally corrected — a depot scan from Tuesday reaches you on Friday. So "where was it on Wednesday" and "what did the tracking page say on Wednesday" are different questions, and customer service needs the second.
Why SQS: These are individual retried POSTs from carriers you do not control, not an ordered stream from devices you do. SQS fits — and at-least-once delivery means deduplication is not an optimisation here, it is what makes the lane correct. The receive handler returns 202 even for a scan it cannot parse, because a 4xx makes a carrier retry and then disable the webhook; judgment happens in the consumer, where a reason can be kept.
Guarantee: messages = landed + duplicates + quarantined, asserted. Five rejection reasons, one fixture each. The log is partitioned by when a scan happened, not when it arrived, and folds by occurrence — so a delivery webhook that wins the race never outranks a later customs hold. Real handlers, moto-mocked AWS, no credentials.
Stack: Python · AWS Lambda · SQS · S3 · API Gateway · moto · pytest

Lane 3

The tariff archive — what the rule was on the day

Question: A tariff table is normally a lookup that gets overwritten. Overwrite it once and every past landed cost silently re-prices at today's rates.
Guarantee: A lithium-battery shipment that left in June 2024 still owes 3.4% on $1,000 — $34 — after two later revisions held or raised it to 7.5%. Asserted in CI against the revision fixtures themselves, by a code path that never touches the event log.
Stack: Python · RESTful API client (retry, backoff, circuit breaker) · dbt · SQL · GitHub Actions

Lane 8

The price book — what the shelf said on the day

The customer never types a price. They type a product, a colour, a size and a quantity; the price is the part they are paying me to find. So the sheet that arrives has no price column in it, and the pipeline looks one up.

Not today’s price — the one that was on the site on the day they ordered. An observed price is a fact with a date, exactly like a duty rate, and the lookup is the same shape as the other four. Every quote records which observation produced it and how confidently it matched, because pricing off the style number while ignoring the colour is an assumption and belongs in the row rather than in somebody’s memory.

The lookup refuses three things, each with its own reason rather than one shrug: an order dated before the first observation (back-pricing at a price observed afterwards would put a number in the ledger nobody could have quoted), a product that is not in the book at all (go and look at the site), and one last seen out of stock — separate, because that needs a different size rather than a price. A fourth refusal sits upstream in the parser: a price that will not parse is rejected rather than read as zero, because a zero produces a confident quote at a loss.

Stack: Python · SQL · dbt · pytest

Lane 4

Dispatch — which goods may lawfully ride which carrier

Question: Perfume is a flammable liquid under IATA; lithium cells are dangerous goods under UN3480/3481. Given the orders and what each carrier is approved to accept, what ships where — and how is a mis-route explained six weeks and four rule edits later?
Guarantee: A refused parcel carries the rule that refused it — prohibited:v1:R1 — and a decision taken under rule set v1 still reads as v1 after the rules change. Blast radius is a query, not a reconstruction.
Code: github.com/jchen7222/dispatch-planner
Stack: Python · OR-Tools (CP-SAT, routing) · DuckDB · RESTful API (OSRM) · GitHub Actions · pytest

The spine · where they meet

Where the four meet

What it is: One row per customer order: the quote and the rate that made it, the duty rate in force on the order date, the last courier scan, and one state column that every order falls into exactly once — delivered, in flight, needs attention, returned, no scans yet, quoted-not-shipped, rejected.
And the other way: A second model asks the uncomfortable question: is anything coming out that we never took in? One waybill is being scanned that no order accounts for. A work list, not a build failure.
Code: github.com/jchen7222/supply-chain-event-platform — 255 tests, 50 dbt nodes
Stack: Python · SQL · dbt · DuckDB with a Snowflake target · GitHub Actions CI · pytest

The same architecture, in a different industry

Where it came from, and the pillar with a production system behind it

None of the above is where this started. I built the production version of the intake pattern at a government information center: energy telemetry from 1,000+ industrial facilities across 24 industries, every industry's feed with different formats, field names and units. Custom pipeline code per source made onboarding a two-day task; moving the differences into templates made it a 30-minute one. That code is proprietary, so the public repository is a from-scratch reimplementation of the architecture on synthetic sources.

Meter readings and customs tariffs have nothing in common — until you need to answer what did we believe, and when? Then both need an immutable log, a rule stamped with the date it applied, and a total that has to balance. Which is how I know it is an architecture and not a habit: I built it twice, in two industries, and the second time the machinery already fit.

Batch intake

Onboard a source by writing a template, not a pipeline

Mechanism: One YAML template per source generates the dbt staging model, normalises the unit, and enforces a versioned contract under an additive-only evolution rule. Three deliberately awkward demo sources: CSV in kWh, JSON lines in MWh, a dirty SCADA export in MJ.
Guarantee: source rows = landed + quarantined, recorded batch by batch.
Code: github.com/jchen7222/template-driven-ingestion
Stack: Python · dbt · DuckDB · SQL · YAML contracts · pytest

Streaming · AWS

An immutable log for industrial telemetry

Mechanism: Kinesis → Lambda → S3, with two envelope versions live at once because gateways in the field do not upgrade on your schedule. Real handlers against moto-mocked AWS in CI.
The seam, measured: The batch side exports its versioned contract and the streaming side reads it through the real handler: 2,352 readings, 1,950,707.032108 kWh out and 1,950,707.032108 kWh projected — delta 0.0. Deduplication must not look like loss.
Code: github.com/jchen7222/telemetry-event-platform
Stack: Python · AWS Kinesis · Lambda · S3 · moto · pytest · CI

One rule, five kinds of reference data

A number is a fact with a date, or it is not a fact

Every one of these started as a constant in a config file, and every one of them turned out to be wrong in the same way: the value moves, and a decision made last month has to still read as correct at last month’s value. Otherwise a revision cannot be told apart from a mistake, and “why was I quoted this?” has no answer.

So each of them is read as of the date of the thing being decided — never today’s. Same shape, five times, in two different businesses.

Moves	Read as of	Lookup
Duty rate	the ship date, or the order date	`hts.rate_as_of()`
Exchange rate	the order date	`orders.fx_as_of()`
Retailer’s shelf pricenew	the order date	`price_book.price_as_of()`
Estimate book & rate cardnew	the enquiry date	`estimate.book_as_of()`
Who is free, and how muchnew	the week in question	`roster.hours_as_of()`

What the price book found. The same customer ordered the same trousers twice, six days apart. In between, the retailer put the price up C$10. Both orders were capped at the same Chinese shelf price, so the increase could not be passed on — and the margin fell from C$28.05 to C$16.75. A spreadsheet holding one price per product cannot represent that at all. It is now a test.

One property, everywhere

Nothing disappears silently

Not a design goal written down in advance — what each lane independently turned out to need, and then what every boundary between them needed too. It is the thing I would defend in any system that moves goods, energy or money: a total that must balance, and a test that fails the build when it doesn't.

orders in = quoted + rejected — order intake
messages = landed + duplicates + quarantined — courier webhooks
source rows = landed + quarantined — batch intake
orders in ⊆ planned ∪ excepted — dispatch; coverage, not a partition
decisions = priceable + refused — 80 = 78 + 2
exported kWh = projected kWh — across the telemetry seam, Δ 0.0
rows in file = accepted + refused — spreadsheet intake
sentences = scoped + unscoped — enquiry scoping; the unscoped ones are the work list
current state = fold(all events, as_of) — every log; corrections append

Work with me

Data engineering, by the hour, from a pool of one

I take dbt, Snowflake, Python and AWS work — the same things this site is built out of. The pool is currently me, which means this answers a narrow question honestly rather than a broad one vaguely: whether I can take your work, and from which week.

You send a paragraph

Not a form with thirty fields. Describe the job the way you would describe it to a colleague.

It gets scoped, not read by a robot

The request is broken into work items against an estimate book, and every sentence it could not place is listed for me to read first. No language model, so every number traces to a phrase.

I reply, not a machine

The acknowledgement is instant; the answer is from me, within two business days — with the hours, the gaps, and the week I could start.

Status, before anything else on this page. The intake is built and tested — 117 tests, no AWS account needed to run them — and it is not deployed. The form below has no endpoint behind it yet and will say so if you press send. Everything described in this section is what the code does when it runs, not what is running. Until it is wired up, email me at jc@iledevelop.com — same reply, same two business days.

Why you do not get an instant quote. Once this is wired up, the estimate will run the moment you press send, and be waiting in my inbox before you have closed the tab. It still will not be shown to you, because an hour estimate is a quote, and a quote a machine produced from a paragraph of free text is a number that would be held against both of us. The cases where it is most wrong are exactly the cases where the request was vaguest. So a human reads it first. The same code refuses to let a total stand on its own when the range comes out wider than 2.5×: it still computes one, and prints DO NOT SEND A NUMBER — this needs a scoping call above it, where I cannot miss it. The placeholder text in the box below is itself one of those cases, at 2.55×.

What this is, and what it isn't

Stated plainly, because the distinction is the point

The spine is one system; the planner is a separate repository: Order intake, courier tracking, the tariff archive and the models that join them live in one warehouse and run together. The dispatch planner is its own repository with a real seam into the ledger — its decisions land as facts — but it routes a separate synthetic order population, and the two id spaces are deliberately not joined. Inventing a mapping would be a lie.
One repository is a reimplementation, and says so: The production version of the ingestion framework was built at a government information center and that code is proprietary. The scale figure — 1,000+ facilities, 24 industries — describes the original, not the public repository.
The methods are real; most of the operational data is not: Orders, courier scans, meter readings, carriers and departures come from seeded generators and doc-shaped fixtures, and so do the tariff revisions — three of them, shaped per the USITC export format, with two rate changes between. The trade statistics, drug-shortage records and supply-chain index are real public data pulled from the IMF, openFDA and the New York Fed, and the assertions run against those archives.
The tariff lane is the US schedule, not the one my parcels pay: USITC publishes ~249 dated revisions back to 1989, which is what makes it a genuine vintage archive and the right corpus to prove as-of reconstruction against. My own shipments run Canada to China and are assessed under China's postal-articles tax instead — a flat rate per goods category against an official dutiable-value table, with a per-parcel exemption. Modelling that is a second reference-data lane, not a correction to this one.
The duty percentages above are fixture values: The HTS subheadings are real — 6110.30.30 is man-made-fibre pullovers, 6104.63.20 is knitted synthetic trousers — and the effective-dating, the as-of lookup and the arithmetic all run for real. The percentages themselves are illustrative numbers in the fixtures, chosen to sit in the right region for apparel, and they have not been read back from the live archive. So the mechanism is demonstrated and the magnitudes are not evidence. Treat the table as "this is what the omission costs at plausible rates", not as a published duty calculation.
AWS runs mocked, not deployed — including the intake: Both AWS lanes execute the real handler code against moto in process, with no credentials, which is what lets anyone clone and run them. Neither is currently deployed. The telemetry lane has an infrastructure template that documents the shape rather than provisioning it; the courier lane has none, and its deployed shape lives only in the handler's docstring. The same is true of the order pipeline and the enquiry intake further down this page: both have Terraform that validates and test suites that pass against mocked AWS, and neither has been applied to an account.
Modelled on the standards, not certified against them: The event envelope follows GS1 EPCIS 2.0 after reading the specification. It is not a certified implementation, and it is not EPCIS production experience.
One claim is narrower than it sounds: "Onboard a source by writing a YAML template, not a pipeline" holds within energy telemetry, where the canonical fields and the unit dimension are fixed. A source from a different domain needs Python changed. Generalising that is the next piece of work, not a claim being made now.
Two more, built for the business and public: ile-aws — drop a CSV in S3, get a priced sheet back. Container-image Lambda with reserved concurrency of 1 for a read-modify-write ledger, an SQS dead-letter queue, a least-privilege role with no delete permission, all in Terraform. 31 tests against mocked AWS.
Python · Terraform · Docker · AWS Lambda (container image) · S3 · SQS · CloudWatch · moto · pytest

ile-consulting — free-text enquiry scoped into work items against a skills taxonomy and an estimate book. No language model, so every hour traces to the phrase that produced it. 117 tests.
Python · Terraform · AWS Lambda Function URL (REST) · S3 · SNS · moto · pytest
Also, outside all of it: aqi-data-pipeline — air-quality data on Snowflake: SQL and Snowpark transforms to a Streamlit dashboard, with a GitHub Actions workflow.
Snowflake · Snowpark · SQL · Streamlit · GitHub Actions