bulk-data-sharing-design

{
  "skill_name": "bulk-data-sharing-design",
  "evals": [
    {
      "id": 1,
      "prompt": "I run product at Tilepath, a field-service scheduling SaaS with about 1,900 paying accounts (HVAC contractors, plumbing franchises, commercial landscaping). We store job records, technician GPS pings and invoice line items, and customers increasingly want all of it in their own systems instead of hitting our REST API with a homegrown scraper. We surveyed our top 120 accounts: none of them run Snowflake, Databricks or BigQuery. Their data people use Athena, DuckDB, and a couple of them have Spark on EMR for ML churn models. Every roadmap review someone re-adds a line item called 'Snowflake sharing (later)' and it never dies. Design the bulk export surface for us and tell me what shape it takes.",
      "expected_output": "A design that selects the Parquet file-drop on object storage as the camp, and explicitly strikes the native warehouse share from the plan rather than parking it in a later phase, with a named re-promotion trigger.",
      "files": [],
      "expectations": [
        "Selects Parquet file-drops on object storage as the camp for Tilepath",
        "Removes the native warehouse/lake share from the plan outright rather than ranking it last or scheduling it as a later phase",
        "Justifies that removal on the grounds that a share requires a share-capable destination on the recipient side, which no Tilepath account has",
        "States a named re-promotion trigger for the native share: target accounts concentrating on one shareable warehouse platform",
        "Names roughly 30-40% of target accounts on a single platform as the concentration threshold that would justify a native share",
        "Flags that the 30-40% concentration threshold is a self-set heuristic, not a vendor-published figure",
        "Does not recommend real-time streaming push as the surface for Tilepath",
        "Rejects streaming push citing its effort/efficiency position or its non-replayability trade-off",
        "Notes that Hive-partitioned Parquet is the shape every consuming engine (Spark, Athena, DuckDB) already reads",
        "Notes the file-drop also serves ML backfills and raw-file use cases a warehouse share cannot",
        "Does not propose routing export downloads through Tilepath's own API servers"
      ]
    },
    {
      "id": 2,
      "prompt": "Nimbus Ledger here - subscription billing platform, roughly 4,200 customers. We're shipping a data export product next quarter. The data is a mix: append-only billing events, plus invoice and subscription records that get updated and sometimes hard-deleted when a customer disputes a charge. Two problems. First, our head of marketing already published 'hourly data SLA' on the pricing page last week without asking anyone. Second, our data engineering lead wants to skip batch entirely and go straight to Debezium CDC off our Postgres WAL - he ran Debezium at his last company for three years. What cadence do we actually ship, and what do we put in front of customers?",
      "expected_output": "A cadence recommendation starting at scheduled full-dump with named promotion conditions, plus an explicitly labeled freshness posture and a correction of the 'hourly SLA' pricing-page language.",
      "files": [],
      "expectations": [
        "Names exactly one freshness posture for the product and labels it as one of: fixed cadence, best-effort target, or contractual SLA",
        "Flags that 'hourly SLA' on the pricing page reads as a contractual, penalty-backed commitment distinct from a best-effort hourly target, and must either be backed or corrected",
        "States that leaving the posture unnamed makes the best-effort promise in the customer's mind and the no-promise position in the vendor's own",
        "Recommends scheduled full-dump as the starting cadence rung rather than CDC",
        "Cites a full load every 3 hours as a concrete reference cadence in this feature class",
        "Gives named promotion conditions for leaving full-dump: its bandwidth/compute bill exceeding CDC's build cost, or a customer freshness requirement the dump interval cannot meet",
        "States that adopting CDC commits the design to upsert-by-primary-key deduplication",
        "States that adopting CDC requires explicit delete tombstones",
        "Requires a periodic full-scan reconciliation job under CDC",
        "Names missed deletes - a delete that emits no change event - as the CDC failure mode that leaves phantom rows in customer copies",
        "Acknowledges the lead's existing Debezium experience as a legitimate reason to promote the CDC rung earlier than the cost curve alone would"
      ]
    },
    {
      "id": 3,
      "prompt": "Quarrystone (product analytics SaaS, ~700 accounts). We ship each customer's event data to their S3 bucket. Current layout is s3://bucket/customer_id=<uuid>/session_id=<uuid>/hour=<ts>/part-*.json.gz - files average 0.2 MB, one line-delimited JSON event per line, and a large account generates upwards of 500,000 files a month. Customers tell us Athena queries take 40+ seconds on a week of data. Our new staff architect's proposal is one sentence: 'migrate the export to Apache Iceberg.' Before I greenlight that, tell me what the export layout should actually be.",
      "expected_output": "A written file-layout and partitioning contract fixing cardinality, depth, format, file sizing, compaction and cataloging, with Iceberg adoption declined absent a named graduation trigger.",
      "files": [],
      "expectations": [
        "Rejects customer_id and session_id as partition keys on high-cardinality grounds",
        "Caps partition depth at roughly 2-3 levels of low-cardinality keys",
        "Prescribes Hive-style partition paths such as year=2026/month=08/day=30/",
        "States partition keys must be chosen by what the consumer filters on, not by how the internal schema is organized",
        "Specifies a target file size of roughly 256-512 MB",
        "Specifies a target Parquet row-group size of roughly 128-512 MB",
        "Recommends replacing JSON with Parquet, citing roughly up to 2x faster unload and up to 6x smaller output",
        "Prescribes a compaction job with a concrete trigger, such as compacting when a table or partition exceeds 100 files each smaller than 75% of the target file size",
        "Cites the measured small-files penalty of roughly 4x (an Athena query dropping from about 40 seconds to about 10 after compaction)",
        "Requires registering the export schema in a catalog (Glue, Hive Metastore, Iceberg REST or equivalent) rather than letting consumers infer it from files",
        "Declines to adopt Iceberg/Delta/Hudi unless the export needs atomic multi-file commits, time-travel, or in-place schema evolution, naming those as the graduation triggers",
        "States that the layout is a written contract rather than whatever the export job happens to emit"
      ]
    },
    {
      "id": 4,
      "prompt": "Ferrolane, logistics/freight-visibility SaaS, ~900 customers on the paid tiers. Architecture review Thursday on our new data export. Current design: the customer calls GET /v1/exports/{id}/download on our Go API, we stream the object out of our single export bucket through the API pods, and each customer gets a long-lived IAM user access key pair we email during onboarding so their pipelines can also list the bucket prefix directly. Access reviews are a quarterly calendar task for the security team. Roughly a third of our customers are on Azure rather than AWS. Poke holes in this before Thursday.",
      "expected_output": "A review that rejects byte-proxying and static shared keys, replacing them with signed URLs / scoped temporary credentials, per-recipient credentials and encryption grants, and a TTL-pinned review cadence.",
      "files": [],
      "expectations": [
        "Rejects routing export downloads through Ferrolane's own API servers, naming the API fleet as both the throughput ceiling and the egress payer",
        "Prescribes short-lived pre-signed URLs or scoped temporary credentials so the recipient reads object storage directly",
        "States that the service's remaining job is authorization plus URL-signing",
        "Rejects the long-lived shared access keys, prescribing per-recipient credentials instead",
        "Names cross-account IAM roles with an ExternalId as the AWS mechanism, in place of distributing key material",
        "Names SAS tokens as the Azure equivalent for the Azure-hosted third of customers",
        "Prescribes one KMS grant per recipient so revoking one customer never touches another",
        "Lays out the isolation ladder in order - dedicated bucket per tenant, shared bucket with prefix isolation, per-recipient access points, broker-minted short-term credentials - and advises climbing it only as tenant count forces",
        "Pins the access-review cadence to the credential TTL/expiry rather than the quarterly calendar process",
        "States that key rotation under CMEK/SSE-KMS must not require re-encrypting historical data",
        "Mentions per-file Parquet encryption as the finer-grained option when one shared bucket serves recipients of differing trust levels"
      ]
    },
    {
      "id": 5,
      "prompt": "Slatewave, HR/workforce-analytics SaaS. We're launching a native Snowflake share of each customer's people data. Our Snowflake account is in AWS us-east-1; about 40% of the accounts we'd share with sit in eu-central-1 and another 15% in ap-southeast-2. Leadership's plan is to bundle the share free into all three paid tiers as a differentiator against our two main competitors, who both charge for it. Finance asked me one question I couldn't answer - 'does this cost us anything per customer?' - and I realised I have no idea. Write up the cost and pricing section of the design.",
      "expected_output": "A cost-allocation section naming who pays storage, compute and egress per delivery model including the cross-region case, plus a recommendation against free bundling on premium-gating precedent.",
      "files": [],
      "expectations": [
        "Recommends against bundling the export free, stating that no free-tier precedent exists in this feature class",
        "Cites that every vendor studied gates bulk export behind an enterprise tier, a paid plan, or a usage-priced add-on, naming at least one example such as Stripe's transaction-count subscription, Braze's enterprise contract, or HubSpot's top tier",
        "States that in cross-region native sharing the provider can end up paying egress that scales with consumer query volume",
        "Identifies egress as the decisive line item, citing that cross-region and cross-cloud transfer can reach up to roughly 70% of total data-transfer cost",
        "Attributes the ~70% figure to Snowflake as a vendor-published number and weights it as such",
        "Notes same-region transfer is typically free, making the us-east-1 consumers the cheap case and eu-central-1/ap-southeast-2 the expensive ones",
        "Names Snowflake's Egress Cost Optimizer - replicate once, pay egress on increments - as a mitigation, treating its savings figure as a vendor claim",
        "Separates what Slatewave charges from who pays the underlying infrastructure, treating them as independent decisions",
        "States that under a native share nothing is copied, so there is no storage duplication and the consumer pays only query compute",
        "Requires writing down before launch, per delivery model, who pays storage, compute and egress, explicitly including the cross-region path"
      ]
    },
    {
      "id": 6,
      "prompt": "Kestrelbridge - payments operations SaaS, we drop nightly Parquet to about 300 customer buckets. Last Tuesday a backend team renamed merchant_ref to merchant_reference in our core service. It flowed straight through the export job. Wednesday morning we had 31 support tickets: broken Looker dashboards, two customers' dbt runs failing, one customer's fraud model silently scoring on nulls for a day. Our VP Eng's proposed fix is 'we'll list column changes in the monthly release notes from now on.' I need something better than that to take to the exec review. What's the policy?",
      "expected_output": "An add-only schema-evolution contract with per-batch validation, catalog-registered versions, the four-part data contract framing, and a real deprecation process replacing release-notes-only notice.",
      "files": [],
      "expectations": [
        "States an add-only posture: append new nullable columns at the end, never rename, remove, or retype in place",
        "Explains that renames break Parquet consumers outright because column names are the identifiers",
        "Prescribes add-the-new-column-and-deprecate-the-old in place of the merchant_ref rename",
        "Requires consistent column ordering and types across every batch",
        "Ties that consistency requirement to predicate pushdown and by-name column matching in consuming engines",
        "Requires validating each batch's written schema against the documented contract before the delivery counts as successful, so drift raises an alert instead of a customer ticket",
        "Requires registering every schema version in the catalog",
        "Frames the commitment as a four-part data contract covering schema, quality, freshness and availability",
        "Treats the export schema as an API contract whose breaking changes ship silently into customer dashboards rather than into Kestrelbridge's own logs",
        "Rejects release-notes-only notice as sufficient, requiring an announced window, parallel old-and-new columns or paths, and a dated removal",
        "Defers the notice-period and sunset machinery to the API versioning/deprecation policy rather than inventing a parallel process here"
      ]
    },
    {
      "id": 7,
      "prompt": "Vantablue, payroll and benefits SaaS, ~2,600 customers. Everything customer-facing exports into one bucket in us-east-1 - simplest thing that worked and it's been fine for two years. Facts on the ground: 41% of our customers are EU-based and the export contains names, national IDs and salary; our tier-2 support team in Manila queries that bucket directly with Athena several times a week to debug failed exports; our largest prospect is an Indian conglomerate whose procurement team keeps asking where the data physically sits; and our legal counsel says we have SCCs signed with every EU customer so we're covered. Tell me what actually needs to change before we scale this.",
      "expected_output": "A residency posture that recognises remote access as a transfer, fixes it architecture-first via region pinning, corrects the SCCs-are-enough claim, and treats the Indian localization requirement as a hard blocker.",
      "files": [],
      "expectations": [
        "States that remote access - the Manila support team querying an EEA customer's export - is itself a regulated cross-border transfer, not merely file copying",
        "Recommends region pinning (EU customer data in EU buckets/shares) as the first and preferred fix",
        "Orders the remedies architecture-first - region pinning, then regionalized CMEK, then pseudonymized replicas - with legal paperwork as the fallback rather than the lead",
        "Explains that regionalized CMEK closes the 'the ciphertext moved but it is encrypted' argument, which regulators do not accept as a substitute for a transfer mechanism",
        "Corrects the counsel's position: SCCs alone are insufficient post-Schrems II and a Transfer Impact Assessment is mandatory on top of them",
        "Cites Meta's €1.2 billion 2023 fine as having been specifically for cross-border transfer failures",
        "Treats the Indian data-localization requirement as a hard blocker rather than a paperwork item, and presents explicit non-availability in that region as a legitimate precedented outcome (Stripe does not offer Data Pipeline in India)",
        "Cites Segment deprecating its EU Data Lakes product rather than retrofitting residency, as evidence that retrofitting can cost the whole product",
        "Requires region selection to be built into the early rollout stages rather than bolted on later",
        "Produces per-route transfer documentation covering legal basis, data classes involved, and whether pseudonymization applies",
        "Names the single global us-east-1 bucket as the anti-pattern that silently commits Vantablue to the SCC/TIA paperwork path for every EU customer"
      ]
    },
    {
      "id": 8,
      "prompt": "Pulsegrid, in-app messaging and push platform. Our customers' growth teams want message-delivery, open and click events pushed to them in near real time - they say batch files landing every three hours are useless for the intervention loops they're building, they want it inside a minute. Two of our three design partners want it on their own HTTPS endpoint, the third wants Kinesis. Our platform already emits every one of these as a discrete event on an internal bus, so the plumbing is mostly there. Design this export surface and tell me what we commit to publicly.",
      "expected_output": "A streaming-push design that qualifies the rung against its three conditions and makes non-replayability an explicit, loud contractual term, alongside a batch surface for backfill.",
      "files": [],
      "expectations": [
        "Accepts streaming push only against the three named conditions: genuinely event-shaped data, consumer demand for sub-minute delivery, and consumer acceptance of the durability trade-off",
        "States explicitly that with a non-replayable stream a consumer whose endpoint was down recovers nothing from the provider",
        "Requires the public contract or documentation to state non-replayability as loudly as Braze Currents does",
        "Identifies streaming push as the highest-effort rung and the one that loses every efficiency comparison",
        "Recommends also shipping a file-drop or batch surface for historical backfill, which the stream cannot serve",
        "Notes that Avro, with schema-registry-based evolution, rather than Parquet, is the format used on the streaming rung",
        "Does not promise replay, at-least-once recovery or gap-filling unless a separate durable buffer is explicitly designed for it",
        "Keeps the streaming surface gated behind a paid or enterprise contract rather than self-serve, consistent with the precedent in this feature class",
        "States an explicit freshness posture for the stream, labeled as fixed cadence, best-effort target, or contractual SLA",
        "Mentions entity-count batching (such as splitting files per 5,000 users) as an alternative to time-window batching on this rung"
      ]
    },
    {
      "id": 9,
      "prompt": "Orchardline, CRM for manufacturing distributors. Reviewing our data-out plan doc before I sign off. Of our 340 target enterprise accounts, 38% are on Databricks; the rest have no warehouse at all and just want files. The doc has four phases: (1) build an in-house file transfer and sharing service so we own the protocol end to end, (2) partitioned Parquet drops, (3) log-based CDC, (4) EU region pinning plus deciding pricing. Team is two data engineers and a half-time SRE. Success criteria in the doc are 'export NPS above 40' and '25% of enterprise accounts adopted by Q4'. Tear this apart and give me the plan you'd actually run.",
      "expected_output": "A restaged rollout leading with Delta Sharing and the file drop, pulling residency and pricing forward, rejecting the in-house build, and replacing the NPS/adoption criteria with the four binary gates.",
      "files": [],
      "expectations": [
        "Puts the native Databricks Delta Sharing surface first, justified by the 38% concentration",
        "Puts the Parquet-on-object-storage file drop second as the universal fallback serving the warehouse-less majority",
        "Rejects deferring EU region pinning to phase 4, requiring it baked into the first two stages",
        "Rejects deferring the pricing decision, requiring the surface priced as an enterprise tier or add-on from day one",
        "Rejects building the in-house file transfer and sharing service, recommending the destination warehouse's own sharing primitive instead",
        "Cites Fivetran acquiring Census in 2025 despite holding a working reverse-ETL prototype as the build-versus-partner evidence",
        "Gives a named revisit trigger for the build-versus-buy choice: vendor usage pricing crossing the fully-loaded cost of roughly 1-2 data engineers",
        "Makes cadence upgrades such as incremental or CDC conditional on full-dump cost or a customer freshness requirement, rather than a scheduled phase",
        "Replaces the NPS and adoption criteria with the four binary gates: contract completeness, cost allocation named, residency coverage, and premium gating",
        "Treats freshness attainment, consumer-reported schema-break incidents and egress cost per recipient as trends to watch rather than pass/fail thresholds",
        "Notes that the effort ranking favouring the native share assumes the data already sits in a share-capable warehouse or lakehouse, and that getting it there flips the ordering"
      ]
    }
  ],
  "trigger_queries": [
    { "query": "Design a bulk data export product for our SaaS", "should_trigger": true },
    { "query": "We want to ship a Snowflake data share to our enterprise customers", "should_trigger": true },
    { "query": "how should we partition the Parquet files we drop in the customer's S3 bucket?", "should_trigger": true },
    { "query": "customers keep asking for their raw data in their own warehouse, what do we build", "should_trigger": true },
    { "query": "Should we use Delta Sharing or just dump Parquet to S3?", "should_trigger": true },
    { "query": "our data dump keeps breaking people's dashboards every time we change a column", "should_trigger": true },
    { "query": "what freshness can we actually promise on the nightly file drop", "should_trigger": true },
    { "query": "who pays the egress when a customer in Frankfurt queries our share hosted in Virginia", "should_trigger": true },
    { "query": "we're putting all customer exports in one global bucket, any problem with that", "should_trigger": true },
    { "query": "write the schema evolution rules for our S3 export", "should_trigger": true },
    { "query": "our enterprise tier needs a data export add-on, how should we price it", "should_trigger": true },
    { "query": "set up per-customer credentials so each one only reads their own folder in our export bucket", "should_trigger": true },
    { "query": "is it fine to serve the export downloads through our own API servers?", "should_trigger": true },
    { "query": "we want to push events in real time to customers' Kinesis streams instead of batching files", "should_trigger": true },
    { "query": "our export bucket has 400,000 tiny files and Athena is crawling", "should_trigger": true },
    { "query": "how often should we refresh the customer data we ship into their lake", "should_trigger": true },
    { "query": "BigQuery linked dataset or just send them files, which one", "should_trigger": true },
    { "query": "customers want to join our data with the rest of their business without writing a scraper against our API", "should_trigger": true },
    { "query": "we have EU customers and our exports all land in us-east-1, what has to change", "should_trigger": true },
    { "query": "should we move our customer exports from raw Parquet to Iceberg?", "should_trigger": true },
    { "query": "Write the data contract for the files we deliver to customers every night", "should_trigger": true },
    { "query": "our biggest account wants everything inside Databricks, how do we serve that", "should_trigger": true },
    { "query": "plan a data pipeline product like Stripe's for our billing platform", "should_trigger": true },
    { "query": "we need to stop customers building their own ETL scrapers against our REST API", "should_trigger": true },
    { "query": "how do we hand customers their own data at volume without them paying an ETL vendor", "should_trigger": true },
    { "query": "full dump or CDC for shipping mutable records out to customers", "should_trigger": true },
    { "query": "design the file layout for the data lake export we want to sell", "should_trigger": true },
    { "query": "can we offer the data export free on every plan?", "should_trigger": true },
    { "query": "we promised hourly exports on the pricing page and legal is asking what that actually means", "should_trigger": true },
    { "query": "our India customer wants the data export turned on, is that allowed", "should_trigger": true },
    { "query": "rotating the KMS key on customer export files - do we have to re-encrypt the archive", "should_trigger": true },
    { "query": "design how customers get a copy of everything we hold for them, at scale, into their own systems", "should_trigger": true },
    { "query": "how do we let a customer query our dataset without copying it into their account", "should_trigger": true },
    { "query": "our change-data feed is leaving deleted rows behind in customer copies", "should_trigger": true },
    { "query": "what should the delivery SLA look like for the data-sharing product we're selling", "should_trigger": true },
    { "query": "roadmap for shipping a data-out surface over the next two quarters", "should_trigger": true },
    { "query": "a customer wants a daily file of absolutely everything - where do we start", "should_trigger": true },
    { "query": "should we build our own data movement infrastructure or partner for it", "should_trigger": true },
    { "query": "help me write the security model for a shared export bucket with 600 recipients", "should_trigger": true },
    { "query": "we're selling a data feed - what do we put in the contract about columns changing", "should_trigger": true },
    { "query": "our analytics customers run DuckDB and Spark and have no warehouse, how do we deliver to them", "should_trigger": true },
    { "query": "set the file sizes and row group sizes for our Parquet exports", "should_trigger": true },
    { "query": "we're launching a data product and nobody knows who eats the transfer costs", "should_trigger": true },
    { "query": "the customer's data science team wants a full historical backfill of their events", "should_trigger": true },
    { "query": "our export job just writes whatever the query returns and customers keep complaining - fix the process", "should_trigger": true },
    { "query": "design a JDBC endpoint so customers can query our data live from Tableau", "should_trigger": false },
    { "query": "should we build a customer-facing SQL query API?", "should_trigger": false },
    { "query": "row-level security for our customer-facing SQL views", "should_trigger": false },
    { "query": "get us listed as a source connector on Fivetran and Airbyte", "should_trigger": false },
    { "query": "should we maintain our own Airbyte connector or let the community build it", "should_trigger": false },
    { "query": "which integration surface should we build first, REST or GraphQL?", "should_trigger": false },
    { "query": "design our outbound webhook delivery retries and dead-letter queue", "should_trigger": false },
    { "query": "how long a deprecation window before we remove a v1 API endpoint", "should_trigger": false },
    { "query": "what should our API key prefix and checksum format be", "should_trigger": false },
    { "query": "set per-tier rate limits for our public API", "should_trigger": false },
    { "query": "design the OAuth consent screen and scope list for third-party apps", "should_trigger": false },
    { "query": "build an MCP server so agents can drive our product", "should_trigger": false },
    { "query": "cursor pagination for our public GraphQL API", "should_trigger": false },
    { "query": "should we expose gRPC publicly or keep it internal", "should_trigger": false },
    { "query": "write our public API error code taxonomy", "should_trigger": false },
    { "query": "idempotency keys for our payments endpoint", "should_trigger": false },
    { "query": "design a sandbox mode with test keys and magic values", "should_trigger": false },
    { "query": "audit our API reference for undocumented parameters", "should_trigger": false },
    { "query": "set up a public status page for our API incidents", "should_trigger": false },
    { "query": "information architecture for our developer portal", "should_trigger": false },
    { "query": "show partners their failed webhook deliveries with a replay button", "should_trigger": false },
    { "query": "should we build our own app marketplace or join HubSpot's", "should_trigger": false },
    { "query": "write the listing content rules for apps in our marketplace", "should_trigger": false },
    { "query": "what revenue share should our app marketplace take", "should_trigger": false },
    { "query": "design the review process for third-party apps before they publish", "should_trigger": false },
    { "query": "launch marketing plan for our marketplace's founding app cohort", "should_trigger": false },
    { "query": "which languages should get official SDKs first", "should_trigger": false },
    { "query": "review our REST resource naming and status code usage", "should_trigger": false },
    { "query": "onboarding journey for partner developers into our sandbox", "should_trigger": false },
    { "query": "optimize our Salesforce AppExchange listing conversion rate", "should_trigger": false },
    { "query": "which technology partners should we integrate with next quarter", "should_trigger": false },
    { "query": "am I qualified for an API product manager role", "should_trigger": false },
    { "query": "write the scorecard for hiring a partner integration engineer", "should_trigger": false },
    { "query": "design our internal data lake for the analytics team", "should_trigger": false },
    { "query": "model our dbt staging layer", "should_trigger": false },
    { "query": "size a Kafka cluster for our internal event bus", "should_trigger": false },
    { "query": "nightly backup of production Postgres to S3 with point-in-time recovery", "should_trigger": false },
    { "query": "migrate our internal warehouse from Redshift to Snowflake", "should_trigger": false },
    { "query": "write an Airflow DAG for our nightly internal ETL", "should_trigger": false },
    { "query": "review the DPA our vendor sent us before we sign it", "should_trigger": false },
    { "query": "which BI tool should our finance team buy", "should_trigger": false },
    { "query": "customers send us huge CSVs - design the bulk upload path", "should_trigger": false },
    { "query": "design a CSV import wizard for onboarding new customer accounts", "should_trigger": false },
    { "query": "GDPR subject access request - how do we export one user's data", "should_trigger": false },
    { "query": "tune our Snowflake warehouse size to cut our own compute bill", "should_trigger": false }
  ]
}

# Cross-border compliance and residency mechanics for bulk export

## What counts as a transfer (broader than engineers assume)

A GDPR cross-border transfer is any movement of personal data from the EEA to a third country - including cloud storage, **remote access**, and intra-group transfers. A support engineer in a non-EEA office querying an EEA customer's exported bucket is the same regulated event as copying the file across the border. Design "who can read this" and "where is this stored" as one question.

## The legal mechanisms, in GDPR's own preference order

1. **Adequacy decision (Art. 45)** - the EU has pre-certified the destination country; no extra paperwork.
2. **Appropriate safeguards (Art. 46)** - Standard Contractual Clauses or Binding Corporate Rules, the standard fallback. Since Schrems II (CJEU, 2020), SCCs alone are not enough: a **Transfer Impact Assessment** must document that the destination country's surveillance laws don't undermine the protection the clauses promise.
3. **Binding Corporate Rules (Art. 47)** - the intra-group variant.
4. **Derogations (Art. 49)** - narrow, case-by-case, last resort.

The stakes are concrete: Meta was fined €1.2 billion in 2023 specifically for cross-border transfer failures - the largest GDPR fine on record at the time.

## Architectural patterns, in the order a design should introduce them

1. **Region pinning / regional buckets** - keep EU customer data in EU regions so the transfer question is eliminated at the source. Every major platform (AWS, Snowflake, BigQuery) offers region selection as a first-class export setting; treat it the same way, from day one. The cost of not doing so is documented: Segment fully deprecated its Data Lakes product in the EU rather than retrofit SCCs onto a US-region design - retrofitting residency can cost the whole product.
2. **Regionalized key management (CMEK)** with policy blocks on cross-region key use - a key that never leaves the EU closes the "ciphertext moved, but it's encrypted so it doesn't count" loophole, which regulators do not accept as a substitute for a legal transfer mechanism.
3. **Pseudonymized replicas** - strip or tokenize identifying fields before a replica leaves its home region, so what crosses the border is no longer personal data under GDPR's own definition.
4. **Metadata replication with payload restriction** - replicate schema/metadata globally (not personal data) while payload access stays geographically restricted. Zero-copy sharing is structurally friendly to this by construction: Snowflake replicates data only into regions where a consumer exists, and BigQuery linked datasets stay inside the subscriber's own VPC perimeter rather than being copied out.
5. **Localization mandates are a separate, stricter category** - China's PIPL and Russian localization law can forbid the transfer outright for certain data classes, beyond anything SCCs or pseudonymization can satisfy. The precedented design outcomes are an in-country deployment or explicit non-availability: Stripe does not offer Data Pipeline to customers in India, citing data-localization requirements. "Not offered in region X" is a legitimate product decision with a named precedent.

## How this composes with the rest of the design

Compliance cost is a genuine ranking axis for this skill's menus, not a cosmetic one. A single global bucket chosen for engineering simplicity silently selects the SCC + TIA paperwork path for every EU customer, forever; region selection built in at stages 1-2 of the rollout mostly dissolves the question. State that trade-off in the design document explicitly - it is a choice being made either way, and only one version of it is visible.

Per-transfer documentation to include in the deliverable, for each delivery route that crosses a border, so the compliance review reads a table instead of reverse-engineering the architecture:

- Legal basis (adequacy / SCC+TIA / BCR).
- Data classes involved.
- Whether pseudonymization applies.

# File layout, partitioning, and access-control mechanics

## Partitioning contract

- **Choose partition keys by consumer query pattern**, never by internal schema: time keys (year/month/day) for event data, business keys (tenant, region) for domain-scoped exports. The first design question is "what will the consumer filter on," not "what columns exist."
- **Hive-style paths are the convention every engine expects**: `year=2026/month=08/day=30/...` - this is what lets Athena, Spark, and Presto prune whole partitions instead of scanning every file. Name it in the contract so consumers know pruning works.
- **Cap at 2-3 partition levels using low-cardinality keys** (date, region, event type - never user ID). Beyond that, S3 LIST-call overhead inflates independently of file size (the "partition trap"), and metadata overhead slows query planning.
- **Both over- and under-partitioning are real failure modes**: too many small partitions inflate planning time; too few large ones kill parallelism. State an intended partition-file-count range in the contract, not just the key names.
- **Row groups: target 128-512 MB**; files: target 256-512 MB (Amplitude's own recommendation is ~500 MB within a 1 MB-5 GB observed range). Large enough to amortize I/O, small enough not to blow distributed-engine memory.
- **Keep column ordering and types consistent across batches.** Partition pruning (across files) and predicate pushdown via Parquet column statistics (within files) compound - inconsistent batches break the second mechanism silently.

## Compaction

- Small files are a measured penalty, not folklore: an AWS benchmark saw an Athena query over 582,000 small files (0.14 MB each) take 40 seconds, dropping to 9.7 seconds after compaction to 336 files of 247 MB - roughly a 4x improvement from compaction alone.
- A concrete trigger to copy (AWS Glue's managed Iceberg compaction): compact when a table or partition exceeds 100 files that are each smaller than 75% of the target file size.

## Format and table-format graduation

- **Parquet over CSV/JSON by default**, with AWS's own numbers: Parquet unloads up to 2x faster and up to 6x smaller on S3 than the same data as text.
- **Register the schema in a catalog** (Glue, Hive Metastore, Iceberg REST, Nessie) rather than relying on file-embedded metadata - this is also where schema versions live for the evolution contract.
- **Graduate raw Parquet to a table format (Iceberg, Delta Lake, Hudi) only when the export needs**: atomic multi-file commits, time-travel/rollback, or in-place schema evolution across batches. Delta's `_delta_log/` and Iceberg's `metadata.json` give these by construction. Below that bar, raw partitioned Parquet is cheaper to operate.
- Consuming warehouses can absorb additive changes without breaking loads - BigQuery's `ALLOW_FIELD_ADDITION` on Parquet appends, ClickHouse matching Parquet columns by name with automatic compatible-type casting - which is exactly why the evolution contract's add-only posture works: additions degrade gracefully, renames do not.
- The Avro alternative: schema-registry-based evolution instead of file-embedded schemas - the reason Braze Currents' streaming adopters chose it. Relevant only on the streaming rung.

## Multi-tenant isolation ladder (AWS's four patterns, in maturity order)

1. **Dedicated bucket per tenant** - simplest to reason about; stops scaling at modest tenant counts (bucket service limits, per-bucket operational load).
2. **Shared bucket with prefix isolation** - cheaper to operate; isolation now rides entirely on correct IAM prefix-condition policies.
3. **One access point per recipient** - decouples each partner's access path from a single ever-growing bucket policy; the answer to "share one bucket with many externals without their permissions colliding."
4. **Session broker + short-term credentials** - STS credentials minted per request by a broker evaluating caller context; the advanced rung.

Move up only as tenant count forces it. Cross-account multi-region access points extend pattern 3 when a recipient needs regional locality.

Adopt the provider/subscriber vocabulary (AWS's data-mesh framing) in design docs - it maps one-to-one onto Delta Sharing's provider/recipient terms, so one document describes either mechanism.

## Credentials and encryption, per cloud

The pattern is identical everywhere - time-boxed signed access, per-recipient key-based decryption, rotation without re-encryption - so specify it once and map it per recipient cloud:

- **Cross-account IAM roles with an ExternalId (AWS)** - the recipient assumes a role via STS instead of receiving key material; AWS frames this explicitly as the answer to key distribution, and it is how Segment operated compute inside customer accounts. Never distribute static keys.
- **Pre-signed URLs (AWS) / SAS tokens (Azure)** - time-boxed GET/PUT links; real configurations run as short as 300-second expiry. Delta Sharing, BigQuery, and Snowflake all use signed URLs or short-lived tokens as the underlying recipient-read mechanism.
- **Per-recipient KMS grants** - AWS Data Exchange creates one KMS grant per subscriber, so each recipient decrypts only their entitled data and revoking one never touches another. This is the per-recipient credential principle at the encryption layer.
- **CMEK/SSE-KMS with audit trail** - customer-managed keys give a CloudTrail record of every decrypt and support cross-account decryption via key policy. Rotation (AWS KMS, GCP Cloud KMS) applies the new key version going forward while prior versions keep historical data readable - rotation never forces re-encrypting the archive.
- **Snowflake Tri-Secret Secure** - the CMEK equivalent layered into Snowflake's own sharing.
- **Per-file Parquet encryption** - a unique key per file, underneath bucket/transport encryption; the finer-grained option when one shared bucket serves recipients with genuinely different trust levels.

Access-review cadence: expiration is the enforcement point in every one of these mechanisms (Delta Sharing tokens, presigned URLs, STS sessions), so pin the review/renewal cycle to the credential TTL rather than running it as an unrelated calendar process.

# Vendor case studies and sharing-platform landscape

## Vendor export products compared

| Vendor product                    | Camp                                | Format                                                                                              | Cadence / freshness posture                                                                                                        | Gating & pricing                                                        | Notable detail                                                                                                                                                                                                                                              |
| --------------------------------- | ----------------------------------- | --------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Stripe Data Pipeline              | Both: warehouse share AND file-drop | Native share (Snowflake, Redshift, Databricks, BigQuery) or raw Parquet (S3/GCS/Azure Blob)         | Full load every 3 hours (fixed cadence); initial load up to 6h (warehouse) / 12h (share acceptance)                                | Subscription keyed to monthly transaction count; 30-day free trial      | Ships both camps side by side; Snowflake destination implemented via Snowflake's own Secure Data Sharing, not a rebuilt file pipeline                                                                                                                       |
| Segment - AWS S3 destination      | File-drop                           | Line-separated raw JSON                                                                             | Batch drops, no hard SLA                                                                                                           | Paid plans                                                              | The low rung: cheapest to build, hardest for the customer to query at scale                                                                                                                                                                                 |
| Segment - Data Lakes (deprecated) | File-drop                           | Hive-partitioned Parquet + Glue Catalog                                                             | Scheduled batch                                                                                                                    | Was a premium feature                                                   | Historical exemplar only: fully deprecated in the EU rather than retrofit residency onto a US-region design, Limited Access in the US. Ran the transform EMR cluster inside the customer's own AWS account via an assumed role - customer paid AWS directly |
| Amplitude raw export              | File-drop                           | Zipped JSON, one event per line                                                                     | "Aims to export five million events per hour" - explicitly best-effort, variable under shared load                                 | Gated to paid plans, potential extra cost                               | No partitioning or catalog; file sizes 1 MB-5 GB, ~500 MB recommended; unbounded historical backfill                                                                                                                                                        |
| Braze Currents                    | Streaming push (the outlier)        | Avro - chosen by adopters because Avro's schema-registry model "natively supports schema evolution" | Real-time stream; **events cannot be replayed** - consumer downtime means data loss, durability is entirely the consumer's problem | Enterprise contract add-on (Currents Connector license), not self-serve | 37 event types to S3/Azure Blob/GCS/HTTP/warehouse partners; files split per 5,000 users (entity-count batching, not time-window batching)                                                                                                                  |
| HubSpot Snowflake Data Share      | Warehouse share                     | Native Snowflake share                                                                              | Platform-managed                                                                                                                   | Data Hub Enterprise tier only                                           | Read-only, unidirectional; customer "responsible for all Snowflake costs incurred while accessing HubSpot's Data Share" - consumer-pays-compute in writing                                                                                                  |
| Fivetran (as the SLA benchmark)   | (movement vendor, not an exporter)  | -                                                                                                   | 99.9% uptime SLA, contractual and penalty-backed                                                                                   | Usage-priced                                                            | The one hard, penalty-backed freshness commitment found in this research - the contrast point to Stripe's fixed cadence and Amplitude's best-effort                                                                                                         |

The gating column is the pattern to notice: every vendor gates bulk export behind an enterprise tier, a paid plan, or a usage-priced add-on. No free-tier precedent exists in this feature class.

## Sharing-platform landscape (the substrate options for the native-share camp)

- **Snowflake Secure Data Sharing** - cross-cloud, cross-region, role-based; a "reader account" lets a recipient without a Snowflake license still query. Consumers pay only query compute; shared data adds zero consumer-side storage because nothing is copied. Strongest when consumers are SQL-first analysts. (Snowflake's comparison-page claims about competitors are vendor marketing.)
- **Databricks Delta Sharing** - open REST protocol, not a Databricks-only feature: any open-source client (pandas, Spark, Rust, Python) reads a share without deploying Databricks. Governance via Unity Catalog. Cost caveat (raised by a competitor source - weight accordingly): consumer reads from a remote region can incur egress the _provider_ pays, scaling with consumer query volume. Adoption figures are vendor-reported:
  - 300%+ year-on-year usage growth, two years running.
  - 16,000+ recipients.
  - 40% of shares go to recipients outside the Databricks ecosystem.
- **BigQuery sharing / Analytics Hub** - subscribers get an opaque, read-only linked dataset inside their own project and VPC perimeter; a "restricted export" option blocks re-exporting shared data out of that perimeter entirely. Authorized views exclude specific columns (the PII-exclusion tool). Strength is GCP-native integration rather than cross-cloud reach.
- **Iceberg REST Catalog** - the vendor-neutral option: an OpenAPI-specified metadata protocol now the de facto standard in the Iceberg ecosystem; any compliant client (Snowflake, Trino, Flink, Spark, PyIceberg) reads from any compliant catalog (Polaris, Nessie, Unity Catalog, Gravitino), with credential vending and OAuth2 client-credentials auth. Caveat: the spec is intentionally silent on performance, and two compliant catalogs can differ by orders of magnitude in response time. Delta Sharing added first-class Iceberg support (2026-01), so the two open options are converging.
- **Salesforce Data Cloud Zero Copy** - metadata layer plus query pushdown (compute runs where the data lives); GA with Snowflake and BigQuery since 2024-04-25, bidirectional, converging on Iceberg at the storage layer via File Federation (Beta).
- **AWS Data Exchange** - the marketplace-flavored option: vends a named bucket-style alias per S3 data asset and mints a separate KMS grant per subscriber, so each recipient decrypts only their own entitled data.

One practitioner data point on the "does the recipient need my platform?" fork (LinkedIn PoC writeup - anecdote, not benchmark): Delta Sharing could share directly to a recipient by email, while equivalent Snowflake performance required data in Snowflake-managed storage and accounts on both ends.

## Delta Sharing protocol mechanics (the reusable design pattern)

- The sharing server **never proxies bytes**. The recipient's client authenticates, queries a table, and receives a short-lived pre-signed URL pointing directly at the Parquet file in object storage. The recipient pulls from storage; throughput scales with the storage layer, and the server's job shrinks to authorization plus URL-signing. This is the pattern to copy whatever mechanism you build.
- Access is time-bound by token expiry (30 days is a common real-world setting), not per-request ACL checks - pin your access-review cadence to that TTL.
- Built on Delta Lake's transaction log (`_delta_log/`), which is what gives shares table versioning and time-travel for free, versus a flat pile of Parquet files with no change history.
- Multi-tenant mapping for SaaS: one table partitioned on `tenant_id`, sharing only the partitions belonging to each recipient - every tenant's share backed by the same physical table, no per-tenant copies.
- Two-tier access in practice: bulk temporary cloud credentials when the asset qualifies, per-file signed URLs as the safe default.

## Build vs partner: the evidence

The practitioner consensus: **buy or partner for standard delivery paths; build only for what no vendor covers.**

- The anchor: Fivetran - whose entire business is data movement, and which had a working reverse-ETL prototype - acquired Census (announced 2025-05-01) instead of shipping its own. CEO George Fraser: it "would be a better use of resources to bring on a company that had already figured it out," because "if you look at the code underneath [these] services, they're actually pretty different" - read-path and write-path movement don't share expertise.
- Fivetran's stated reason write paths are harder: "Read APIs on most SaaS tools tend to be relatively straightforward, whereas write APIs are much more complex (e.g., each tool handles deletes differently)."
- The scale of the maintenance burden being avoided (Airbyte co-founder Michel Tricot): "we built and scaled over 1,000 data ingestion connectors to replicate 100TB worth of data every day."
- The named cost risks of buying: usage pricing that starts affordable and scales badly ("credit spending explosion"), and the "double vendor premium" - paying separately for extraction compute and warehouse compute when a warehouse-native path pays once.
- Revisit trigger (self-set, not a published figure): vendor usage pricing crossing the fully-loaded cost of roughly 1-2 dedicated data engineers, or governance/residency requirements no vendor meets.

For a SaaS vendor designing a bulk surface, "partner" usually means: use the destination warehouse's own sharing primitive (as Stripe did with Snowflake) rather than rebuilding file transfer, and let established movement vendors carry the long tail of destinations.

---
name: bulk-data-sharing-design
description: Design bulk file and lake export as a B2B product surface - the two-camp choice between Parquet file-drops on object storage and native warehouse/lake sharing (Snowflake shares, Delta Sharing, linked datasets), the partitioning and schema-evolution contract across batches, delivery cadence and freshness commitments (full dump vs incremental vs CDC vs streaming), shared-bucket security with per-recipient credentials, egress cost allocation, and cross-border data residency. Use whenever the user mentions bulk export, data dumps to S3, Parquet, Snowflake shares, Delta Sharing, or export freshness SLAs - even if they never say "bulk data sharing". Do NOT use for live SQL endpoints - use samber/developer-platform-skills@sql-jdbc-access-design instead.
license: MIT
metadata:
  author: Samuel Berthe
  version: "1.0.1"
---

# Bulk Data Sharing Design

You are a data-platform product designer. Design how a SaaS product hands its customers their own data in bulk - as files on object storage, as a native warehouse or lake share, or as a stream - so a customer's data team can join it against the rest of their business without building a scraper against your API.

The motivating precedent: before Stripe shipped Data Pipeline, a customer wanting Stripe data in a warehouse either built a custom API pipeline (Stripe's own estimate: months of work, hundreds of thousands of dollars) or bought a third-party ETL sync with incomplete coverage. A vendor-run bulk surface is the third option - full coverage by construction, and every vendor studied sells it as a premium feature.

**Memory (advised):** When memory lives in a file, consider using `developer-platform-context.md`; if a different memory system is in use, rely on that instead. The file is an advisory reference, not a mandatory requirement. Separate task info in different sections. Remove finished tasks. Add a date to a task; no date for general project context. Some interview responses may differ between 2 tasks.

## Clarifying questions

Ask these before designing anything; each answer changes a later step. Batch them - this is a tactical design task, not a strategy interview.

1. Customer warehouse landscape: what share of target accounts already run a shareable warehouse or lakehouse (Snowflake, Databricks, BigQuery), and does one platform dominate? (picks the camp - see step 1)
2. What data, at what volume and volatility: append-only events, or mutable records with updates and deletes? (drives cadence and delete semantics)
3. Freshness demand, sourced from actual buying customers: is day-old data fine, or do they need hours or minutes? What did they say, not what sounds ambitious?
4. Compliance regimes and regions: EU personal data in scope? Any customers in countries with data-localization mandates (e.g. China's PIPL, Russia, India)?
5. Pricing intent: enterprise-tier gate, usage-priced add-on, or bundled into an existing paid plan? If the plan is "free feature", flag it now - see Failure modes.
6. Recipient clouds: one of AWS/GCP/Azure, or a mix? (drives the credential and encryption mapping)
7. Delivery ceiling: by when must the first export land, is this a one-off enterprise deal-closer or a compounding platform surface, and how much data-engineering effort can you spend? (re-ranks both menus below - a hard deadline promotes the low-effort rungs, a compounding mandate promotes the table-format and CDC investments)

If your harness has persistent memory, store the design's settled decisions so later runs (a new dataset, a cadence upgrade, a residency review) start from the design instead of re-deriving it. Store:

- Chosen camp
- Partition and file-size contract
- Cadence rung, with its promotion condition
- Credential model
- Cost-allocation split
- Residency posture

## Data-stack maturity

The design splits by the customer's data-stack maturity:

- **Warehouse-resident consumers** - already live in Snowflake, Databricks, or BigQuery. A native share serves them with zero copies, zero egress, and no schema drift on load; handing them raw files forces them to rebuild ingestion you could have skipped.
- **Bucket-only consumers** - no shareable warehouse; they run Spark, Athena, DuckDB, or ML pipelines against object storage. Partitioned Parquet in a bucket is the only shape they can consume.

Design for the mix you actually have, not the sophisticated end. Stripe ships both camps side by side - warehouse shares for the first group, raw Parquet to S3/GCS/Azure Blob for the second - rather than picking one.

## Workflow

1. Choose the camp - file-drop, native share, or both.
2. Specify the file-layout and partitioning contract.
3. Set the delivery cadence and the freshness posture.
4. Design the security and credential model.
5. Allocate egress, storage, and compute cost.
6. Write the schema-evolution contract.
7. Settle compliance and residency.
8. Stage the rollout.

Each step has a section below, in order.

## 1. Choose the camp

Two architectural camps ship in production, plus one outlier:

- effort: `streaming push > Parquet file-drop > native warehouse/lake share`
- value: `native share > file-drop > streaming push` (for warehouse-resident consumers; flips to `file-drop > native share` when consumers are bucket-only)
- efficiency: `native share > file-drop > streaming push`

- **Default rung: native warehouse/lake sharing** - Snowflake Secure Data Sharing, Databricks Delta Sharing, BigQuery linked datasets - whenever a meaningful share of target accounts concentrate on one warehouse platform (roughly 30-40%+ on one platform; that threshold is self-set, not a vendor-published number). Nothing is copied, so there is no egress bill, no storage duplication, and no schema drift introduced by a load job. This is the camp Stripe, HubSpot, and Salesforce all chose for their warehouse-resident customers. The effort line above assumes the data already lives in a share-capable warehouse or lakehouse - if it doesn't, getting it there is the real cost, and that flips the effort ordering.
- **Universal fallback rung: Parquet file-drops on object storage** - Hive-partitioned Parquet in a bucket, the shape every consuming engine reads. The default when no warehouse concentration exists, and the second surface to ship even when one does, because it also serves ML backfills and raw-file use cases the share can't.
- **Deleted, not demoted: the native share when question 1 finds no warehouse-resident accounts at all.** A share needs a share-capable destination on the recipient's side; with none, this is not a slower rung but an unreachable one. Strike it rather than leaving it at the bottom, where "we'll add Snowflake sharing too" reappears as scope in every roadmap review. Re-promotion trigger: target accounts concentrating on one shareable platform.
- **Starved option: real-time streaming push** - highest freshness, highest effort, loses every efficiency round. Promote it only when the exported data is genuinely event-shaped, consumers demand sub-minute delivery, and they accept the durability trade-off: Braze Currents, the one real-world example (Avro, not Parquet), states plainly that events can't be replayed - a consumer whose endpoint was down recovers nothing from the provider.

This ranking is a default, not a law. Re-rank against question 1's answer and anything else you know about the user: a provider whose data already sits in Databricks gets Delta Sharing nearly free; a customer base of ML teams with no warehouse makes the file-drop the only rung that matters; an event-analytics product with streaming-native consumers is the one context that promotes the starved option.

Protocol-level detail - how Delta Sharing vends pre-signed URLs so the sharing server never proxies bytes, and how one tenant-partitioned table backs every recipient's share without per-tenant copies - lives in [references/vendor-case-studies.md](references/vendor-case-studies.md).

## 2. Specify the file-layout and partitioning contract

For the file-drop camp, the layout is the contract. Write it down; don't let the export job's incidental output become what customers depend on.

- Pick partition keys by what the consumer will filter on, never by how your internal schema is organized - date for event data, plus tenant scoping. Use Hive-style paths (`year=2026/month=08/day=30/`), capped at 2-3 levels of low-cardinality keys; never partition on a high-cardinality key like user ID.
- State target file and row-group sizes in the contract (roughly 256-512 MB files), and run compaction - the small-files problem is measured, not theoretical (an AWS benchmark cut a 40-second query to under 10 by compacting).
- Default to Parquet over CSV/JSON: AWS's own numbers show Parquet unloads up to 2x faster and up to 6x smaller than text.
- Register the schema in a catalog (Glue, Iceberg REST, Hive Metastore) rather than leaving consumers to infer it from files.
- Graduate from raw partitioned Parquet to a table format (Iceberg, Delta, Hudi) only when the export needs atomic multi-file commits, time-travel, or in-place schema evolution - not before; raw Parquet is cheaper to operate when those guarantees aren't needed.

Concrete numbers, the compaction trigger, and the format trade-offs live in [references/file-layout-and-access-control.md](references/file-layout-and-access-control.md).

## 3. Set the delivery cadence and the freshness posture

The cadence ladder - build complexity rises monotonically, operating cost at scale falls:

- effort to build: `streaming push > log-based CDC > micro-batch incremental > scheduled full-dump`
- operating cost at scale: `scheduled full-dump > micro-batch incremental > log-based CDC`
- freshness: `streaming push > log-based CDC > micro-batch incremental > scheduled full-dump`
- efficiency: `scheduled full-dump > micro-batch incremental > log-based CDC > streaming push`

- **Default rung: scheduled full-dump.** Idempotent overwrite, no delete-tracking, simplest to operate. Stripe's full load every 3 hours is the concrete reference cadence. The cost: every cycle re-transfers the whole dataset regardless of how little changed.
- **Promote up the ladder** when the full-dump's bandwidth/compute bill exceeds CDC's build cost, or a customer freshness requirement the dump interval can't meet. Adopting CDC commits the design to upsert-by-primary-key dedup, explicit delete tombstones, and a periodic full-scan reconciliation job - missed deletes are the named CDC gotcha, since a delete that never emits a change event is invisible downstream.
- Streaming push sits at the top with the non-replayability trade-off from step 1.

This order is a default too. Re-rank it against what the team already has: an in-house group that has run log-based CDC before (Debezium and its snapshot-to-log handoff) buys the CDC rung far cheaper than the ladder assumes, and that experience promotes it well before the cost curve would.

Separately from the rung, publish an explicit freshness posture - one of three, and say which:

1. **Fixed cadence** - Stripe: a full load every 3 hours, unconditional.
2. **Best-effort target** - Amplitude: "aims to export five million events per hour", explicitly variable under shared load.
3. **Contractual SLA** - Fivetran: 99.9% uptime, penalty-backed.

"Best-effort hourly" and "99.9% SLA" look similar on a pricing page and carry completely different support-escalation and legal consequences. A surface that never states which posture it makes has made the best-effort promise in the customer's mind and the no-promise position in its own.

## 4. Design the security and credential model

- Never proxy the bytes through your own servers. Every production sharing mechanism - Delta Sharing, Snowflake, BigQuery - vends short-lived signed URLs or scoped temporary credentials so the recipient reads object storage directly; your service does authorization and URL-signing, and throughput scales with the storage layer.
- Issue per-recipient credentials, never shared or static keys: cross-account roles with an external ID on AWS, SAS tokens on Azure, and one KMS grant per recipient so revoking one customer never touches another.
- Isolate recipients along the maturity ladder, moving up only as tenant count forces it: `dedicated bucket per tenant > shared bucket with prefix isolation > per-recipient access points > broker-minted short-term credentials`.
- Encrypt with customer-manageable keys (CMEK/SSE-KMS); rotation must not require re-encrypting historical data, and per-file encryption is the finer-grained option when one bucket serves recipients with different trust levels.
- Pin the access-review cadence to the credential TTL - expiration is the enforcement point, not per-request ACL checks.

Mechanics per cloud, the AWS isolation ladder, and the token-lifecycle details live in [references/file-layout-and-access-control.md](references/file-layout-and-access-control.md).

## 5. Allocate egress, storage, and compute cost

Three cost layers; name who pays each, in writing, before launch:

1. **Infrastructure cost allocation follows the camp.**
   - File-drop: the customer pays everything - their bucket, their reprocessing compute. Segment's model: customers own and pay AWS directly, down to the transform cluster Segment runs inside the customer's own account.
   - Warehouse share: nothing is copied, so nobody pays storage duplication, and the consumer pays only query compute. Snowflake's model; HubSpot tells customers outright they owe all Snowflake costs incurred reading the share.
2. **Egress is the decisive line item.** Cross-region and cross-cloud transfer can contribute up to 70% of total data-transfer cost (Snowflake's own published figure - a vendor number, and one that flatters the optimizer it sells); same-region is typically free.
   - Know which side of the share eats it: in cross-region native sharing the _provider_ can end up paying egress that scales with consumer query volume (a caveat raised by a competitor source - weight accordingly, but the economics are real).
   - Snowflake shipped an Egress Cost Optimizer (GA 2025-04-15) precisely because of this: replicate once, pay egress on increments only. The up-to-96% savings figure is a vendor claim, though one customer, RavenPack, reports a 14-fold cost reduction.
3. **What you charge is independent of who pays infrastructure.** Every vendor studied prices bulk export as an enterprise-tier or usage-priced add-on: there is no observed free-tier precedent for this feature class.
   - Stripe bills a subscription keyed to transaction count.
   - Braze gates behind an enterprise contract.
   - HubSpot gates behind its top tier.

## 6. Write the schema-evolution contract

The export's schema is an API contract whose breaking changes ship silently - a source-system rename propagates into the customer's dashboards with no deprecation header. Treat it with API-versioning discipline:

- Add-only posture: append new nullable columns at the end; never rename, remove, or retype in place. Renames break Parquet consumers outright (column names are the identifiers); the correct move is add-new-plus-deprecate-old.
- Keep column ordering and types consistent across every batch - engines' predicate pushdown and by-name column matching both depend on it.
- Register every schema version in the catalog and validate the written schema against the documented contract before a delivery counts as successful, so drift raises an alert instead of a customer ticket.
- Frame the full commitment as a four-part data contract, because a BI dashboard, an ML pipeline, and a dbt job each depend on a different subset:
  - Schema: columns, types, nullability.
  - Quality: completeness thresholds.
  - Freshness: the step-3 posture.
  - Availability.
- When a breaking change is genuinely unavoidable, run it like an API deprecation: announced window, parallel old-and-new columns or paths, dated removal. Sibling `samber/developer-platform-skills@api-versioning-policy` owns that notice-period machinery; this contract defines what counts as breaking for files and tables.

## 7. Settle compliance and residency

Cross-border transfer of personal data has teeth: Schrems II added a mandatory Transfer Impact Assessment on top of Standard Contractual Clauses, and Meta's €1.2B fine (2023) was specifically for cross-border transfer failures. Remote access counts as a transfer - a support engineer in a non-EEA office querying an EEA customer's export is the same regulated event as a file copy.

Prefer the architectural fix over the paperwork path, in this order:

1. **Region-pin from day one** - EU data in EU buckets/shares eliminates the transfer question at the source. Segment deprecated its EU Data Lakes product rather than retrofit compliance onto a US-region design; region selection is a day-one setting in every major platform for this reason, and retrofitting it is what the Segment case says it is: sometimes cheaper to kill the product.
2. **Regionalized CMEK** - a key that never leaves the region closes the "ciphertext moved, but it's unreadable" loophole regulators don't accept.
3. **Pseudonymized replicas** - strip or tokenize identifying fields before any copy leaves its home region.
4. **Localization mandates are hard blockers, not paperwork** - China's PIPL and similar laws can forbid the transfer outright; Stripe simply does not offer Data Pipeline in India, citing data localization. "Not offered in region X" is a legitimate, precedented design outcome.

Full legal-mechanism ordering (adequacy → SCC/BCR + TIA → derogations) and the pattern details live in [references/compliance-and-residency.md](references/compliance-and-residency.md).

## 8. Stage the rollout

Deliver the design as a staged plan, not a single launch (thresholds here are self-set, not vendor-published figures):

1. Native warehouse share for the dominant warehouse platform, if question 1 found concentration.
2. Parquet-on-object-storage file drop as the universal fallback - partitioned, cataloged, per-recipient credentials.
3. Cadence upgrades (incremental/CDC) only when full-dump cost or a customer freshness requirement forces them.
4. Premium gating and the published freshness posture - priced as an enterprise tier or add-on from day one.
5. Build vs partner: buy or partner for standard delivery paths (the warehouse's own sharing primitive, an established movement vendor); build in-house only for what no vendor covers. Fivetran itself - with a working prototype in hand - acquired Census (May 2025) rather than build reverse ETL internally, because read-path and write-path data movement are different engineering problems. Revisit the choice when a vendor's usage pricing crosses the fully-loaded cost of roughly 1-2 data engineers.

Region pinning (step 7) is cross-cutting: bake it into stages 1-2, never bolt it on at stage 4.

## Failure modes

Anti-pattern checklist - each is a direct review finding:

- **Silent schema drift**: a rename or type change ships to the export because nothing validates batches against a documented contract. The costliest failure in this domain - the break lands in the customer's dashboards, not your logs.
- **Egress bill shock**: cross-region sharing launched without knowing which party pays per-byte egress, discovered on the first invoice at 70%-of-transfer-cost scale.
- **Cross-border violation**: one global bucket for engineering simplicity, which silently commits you to SCC/TIA paperwork for every EU customer - or to a Meta-scale finding.
- **Free-tier giveaway**: bulk export bundled free, against a feature class where every vendor studied gates it behind a premium tier - margin surrendered that tier repricing can't easily claw back.
- **Proxied bytes**: export downloads routed through your API servers, making your fleet the throughput ceiling and the egress payer at once.
- **Small-files decay**: no compaction, thousands of tiny files, consumer queries slow to a crawl - the measured 4x penalty.
- **CDC without delete reconciliation**: deletes that never emit events accumulate as phantom rows in every customer's copy.
- **Static shared credentials**: one key for all recipients, so a single leak or offboarding rotates everyone.
- **Streaming without the replay warning**: consumers discover during their first outage that missed events are gone; if you ship a non-replayable stream, the contract must say so as loudly as Braze does.

## Measurement

Four binary gates; iterate the design until all four pass:

1. **Contract completeness**: every export surface has a written data contract covering schema (columns, types, nullability) and an explicit freshness posture (fixed cadence, best-effort, or SLA - named as such). A surface with an implicit schema or an unstated posture fails.
2. **Cost allocation named**: for each shipped delivery model, who pays storage, compute, and egress is written down, including the cross-region case. Any unattributed egress path fails.
3. **Residency coverage**: every regulated region present in the customer base has a documented posture - region-pinned, legal-mechanism-documented, or explicitly not offered. An EU customer on an unpinned global bucket with no documented SCC/TIA fails.
4. **Premium gating**: bulk export sits behind a paid tier or add-on, or the design records an explicit, argued decision to break the industry-wide precedent.

Trends to watch after launch, never pass thresholds:

- Share of deliveries meeting the stated freshness posture.
- Consumer-reported schema-break incidents (target direction: zero).
- Egress cost per recipient per month.

## Invocation examples

- "Our enterprise customers keep asking to get their data into Snowflake - design a bulk export surface, and tell me whether to build file drops or a native share."
- "Design the Parquet layout and schema-evolution rules for our S3 data export so customer dashboards stop breaking when we change columns."
- "We want to sell a data export add-on: what cadence can we promise, who pays the egress, and what do we do about our EU customers?"

## References

- [references/vendor-case-studies.md](references/vendor-case-studies.md) - the vendor comparison table (Stripe, Segment, Amplitude, Braze, HubSpot, Fivetran) and the sharing-platform landscape (Snowflake, Delta Sharing, BigQuery, Iceberg REST Catalog, Salesforce Zero Copy, AWS Data Exchange), with Delta Sharing protocol mechanics and the build-vs-partner evidence.
- [references/file-layout-and-access-control.md](references/file-layout-and-access-control.md) - partitioning and compaction numbers, table-format graduation triggers, the multi-tenant isolation ladder, and per-cloud credential/encryption mechanics.
- [references/compliance-and-residency.md](references/compliance-and-residency.md) - GDPR transfer mechanics, Schrems II/TIA, the architectural pattern ordering, and the localization-mandate cases.

See also, same collection:

- `samber/developer-platform-skills@api-integration-surface-strategy` - the umbrella decision that chooses bulk files/shares as a surface at all; this skill designs the surface once chosen.
- `samber/developer-platform-skills@etl-connector-strategy` - being a source connector on other people's ETL platforms (Fivetran, Airbyte); this skill is you delivering the data yourself.
- `samber/developer-platform-skills@api-auth-key-management` - API credential issuance and rotation; the per-recipient bucket credentials here (cross-account roles, KMS grants, short-lived tokens) apply the same lifecycle principles to storage access.