Repository Docs & Knowledge Base

// =============================================================================
// ETL ingestion dynamic views
// Shows the simplified pipeline from customer sources to tenant Redshift databases.
// =============================================================================

views 'ETL / Ingestion pipeline' {

    dynamic view etl_file_upload {
        title 'File Upload to Redshift'
        description '
        Customer uploads a CSV or Excel file.
        The ingestion API records the batch, stores the raw file in S3,
        queues ETL work, and the worker routes the batch into the tenant
        Redshift database to load staging and refresh curated plus serving data.
        After curated tables are built, Redshift UNLOADs them as Parquet to
        S3 curated storage for cross-tenant Athena queries.
        '

        customersystem.fileuploads -> kdsai.etl.ingestionApi 'Uploads file batch'
        kdsai.etl.ingestionApi -> kdsai.etl.rawStorage 'Stores original file in raw S3'
        kdsai.etl.ingestionApi -> kdsai.etl.ingestionQueue 'Creates ETL job'
        kdsai.etl.etlWorker -> kdsai.etl.ingestionQueue 'Consumes ETL job'
        kdsai.etl.etlWorker -> kdsai.etl.rawStorage 'Reads uploaded file and validation metadata'
        kdsai.etl.etlWorker -> kdsai.etl.loadStorage 'Writes load-ready files'
        kdsai.etl.etlWorker -> kdsai.warehouseDb 'Resolves tenant DB and runs COPY into staging'
        kdsai.warehouseDb -> kdsai.etl.loadStorage 'Tenant DB loads staging data with COPY'
        kdsai.etl.etlWorker -> kdsai.warehouseDb 'Builds curated tables and serving views'
        kdsai.warehouseDb -> kdsai.etl.curatedStorage 'UNLOADs curated tables as Parquet'
        kdsai.aibackend -> kdsai.warehouseDb 'Queries tenant serving data (single-tenant path)'

        include customersystem.fileuploads
    }

    dynamic view etl_api_push {
        title 'Programmable Path: API Push'
        description '
        A customer system posts payloads or batches into the default ingestion pipeline.
        Raw payloads are retained in S3 and then routed into the tenant
        Redshift database through the worker. Curated data is UNLOADed to S3.
        '

        customersystem.pushsource -> kdsai.etl.ingestionApi 'Pushes batch or payload'
        kdsai.etl.ingestionApi -> kdsai.etl.rawStorage 'Stores raw pushed payloads'
        kdsai.etl.ingestionApi -> kdsai.etl.ingestionQueue 'Creates ETL job'
        kdsai.etl.etlWorker -> kdsai.etl.ingestionQueue 'Consumes queued push jobs'
        kdsai.etl.etlWorker -> kdsai.etl.loadStorage 'Writes load-ready files'
        kdsai.etl.etlWorker -> kdsai.warehouseDb 'Resolves tenant DB and loads pushed batches into staging'
        kdsai.warehouseDb -> kdsai.etl.loadStorage 'Tenant DB loads pushed batches with COPY'
        kdsai.etl.etlWorker -> kdsai.warehouseDb 'Updates curated tables and serving views'
        kdsai.warehouseDb -> kdsai.etl.curatedStorage 'UNLOADs curated tables as Parquet'
        kdsai.aibackend -> kdsai.warehouseDb 'Queries tenant serving data (single-tenant path)'

        include customersystem.pushsource
    }

    dynamic view etl_api_pull {
        title 'Programmable Path: API Pull'
        description '
        The ETL worker polls an external API on a schedule,
        stores the raw response in S3, prepares load files,
        routes data into the tenant Redshift database,
        and UNLOADs curated data to S3.
        '

        kdsai.etl.etlWorker -> customersystem.pullapi 'Pulls orders, payments, inventory on schedule'
        customersystem.pullapi -> kdsai.etl.etlWorker 'Returns API payloads'
        kdsai.etl.etlWorker -> kdsai.etl.rawStorage 'Stores raw API responses'
        kdsai.etl.etlWorker -> kdsai.etl.loadStorage 'Writes normalized load files'
        kdsai.etl.etlWorker -> kdsai.warehouseDb 'Resolves tenant DB and loads staging data'
        kdsai.warehouseDb -> kdsai.etl.loadStorage 'Tenant DB loads staging data with COPY'
        kdsai.etl.etlWorker -> kdsai.warehouseDb 'Merges curated tables and refreshes serving views'
        kdsai.warehouseDb -> kdsai.etl.curatedStorage 'UNLOADs curated tables as Parquet'
        kdsai.aibackend -> kdsai.warehouseDb 'Queries tenant serving data (single-tenant path)'

        include customersystem.pullapi
    }

    dynamic view etl_cdc {
        title 'Programmable Path: CDC'
        description '
        The ETL worker extracts an initial snapshot and ongoing changes from a source database.
        Raw change batches are retained in S3, loaded into the tenant Redshift database,
        transformed into curated tables plus serving views,
        and UNLOADed as Parquet to S3 curated storage.
        '

        kdsai.etl.etlWorker -> customersystem.sourceDb 'Reads snapshot or CDC changes'
        customersystem.sourceDb -> kdsai.etl.etlWorker 'Returns source rows or changes'
        kdsai.etl.etlWorker -> kdsai.etl.rawStorage 'Stores raw snapshots or change batches'
        kdsai.etl.etlWorker -> kdsai.etl.loadStorage 'Writes source-aligned load files'
        kdsai.etl.etlWorker -> kdsai.warehouseDb 'Resolves tenant DB and loads replicated tables into staging'
        kdsai.warehouseDb -> kdsai.etl.loadStorage 'Tenant DB loads replicated tables with COPY'
        kdsai.etl.etlWorker -> kdsai.warehouseDb 'Builds curated tables and serving views from CDC'
        kdsai.warehouseDb -> kdsai.etl.curatedStorage 'UNLOADs curated tables as Parquet'
        kdsai.aibackend -> kdsai.warehouseDb 'Queries tenant serving data (single-tenant path)'

        include customersystem.sourceDb
    }

    dynamic view etl_end_to_end {
        title 'ETL End-to-End: Sources to Curated Redshift and Cross-Tenant S3'
        description '
        Overview of the simplified ingestion model.
        Multiple source adapters share one ingestion pipeline that lands raw data in S3,
        routes each batch into the correct tenant Redshift database, UNLOADs curated
        data as Parquet to S3, and serves tenant-scoped data to AI analytics via
        Redshift (single-tenant) or Athena (cross-tenant).
        '

        parallel {
            customersystem.fileuploads -> kdsai.etl.ingestionApi 'File upload'
            customersystem.pushsource -> kdsai.etl.ingestionApi 'API push'
            kdsai.etl.etlWorker -> customersystem.pullapi 'Scheduled API pull'
            kdsai.etl.etlWorker -> customersystem.sourceDb 'CDC snapshot or change pull'
        }

        parallel {
            kdsai.etl.ingestionApi -> kdsai.etl.rawStorage 'Stores raw payloads'
            kdsai.etl.ingestionApi -> kdsai.etl.ingestionQueue 'Creates ETL jobs'
        }

        kdsai.etl.etlWorker -> kdsai.etl.ingestionQueue 'Consumes queued work'
        kdsai.etl.etlWorker -> kdsai.etl.rawStorage 'Reads raw payloads or writes failures'
        kdsai.etl.etlWorker -> kdsai.etl.loadStorage 'Writes load-ready files'
        kdsai.etl.etlWorker -> kdsai.warehouseDb 'Routes each batch to the correct tenant DB'
        kdsai.warehouseDb -> kdsai.etl.loadStorage 'Tenant DB loads staging data'
        kdsai.etl.etlWorker -> kdsai.warehouseDb 'Builds curated tables and serving views'
        kdsai.warehouseDb -> kdsai.etl.curatedStorage 'UNLOADs curated tables as Parquet'

        parallel {
            kdsai.aibackend -> kdsai.warehouseDb 'Single-tenant: queries serving views'
            kdsai.aibackend -> kdsai.athena 'Cross-tenant: queries curated data via Athena'
        }

        kdsai.athena -> kdsai.glueCatalog 'Resolves table schemas and partitions'
        kdsai.athena -> kdsai.etl.curatedStorage 'Reads curated Parquet from S3'
        kdsai.aibackend -> kdsai.bedrock 'Uses LLM reasoning on warehouse results'
        kdsai.aibackend -> user 'Alerts on anomalies detected'

        include customersystem, user
    }
}