stat plan, going forward tyalking about reg reporting, stat stuff might be a buzzword
recycling auto stat plan, openIDL reg reporting standard for personal auto
personal and commercial auto but for this personal
big ?: decoding and how we go about it
link 1 - stat plan PDF
stat record broken out by column
join on all codes and ref tables
personal auto stat view - not claim or prem specific yet
SDMA and GT2 - stor all in one table
Source of truth is stat plan instead of sql
James A
modeling group
5-6 questions, arch in nature
1. lookup tables in code, as opposed to more maintainable
location of record for these codes, in the JSON and those are the official locations of codes and values
comfortable with json as place to maintain it
KS
interaction points of business people?
JM
maintain lookup tables in excel, can send to anyone and they can navigate - intermediate tech and business
helps
in json and modify
lookup tables change at same freq as code
able to release independently
BA/QA type roles - maintaining it
PA
utilizing flyway to manage db
JN
biggest concern is lookup tables - for insert of data only
if we know version 001, ABS table will only have 3 records, if we want to add more things, vers 00.2 -would know by looking at schema history - truth kept on DB and not on some obscure file
KS
two things - schema and data
for ref tables, data is much less changeable, doesnt change as often, more often than schema
wouldn't use flyway for transactional data (states, lookups, never transax)
is Flyway meant to be applied to data as much as schemas? more schema management than data migration
lost with schema vs data
new code for personal auto? needs to go to every carrier
new code? does change data
JN
define as a team how we handle it - dont care in DBMS versioning? then pull it out - depends on how strict
KS
when do customers participarte in management of lookup data
JM
some ways, ref data behaves like code
rows part of schema seems reasonable
programmer go into json to modify it?
PA
depends on budget
could make excel and easily pull json out of excel
need to gen sql file
KS
process involved - cost in distribution, governance, approvals, acceptance of changes not changes themselves
if a programmer vs someone loading an excel = trivial process cost
PA
how much harder to use excel vs configs? API to access it?
JM
if driivng off json files less worried
reduce programmer jobs due to expense
PA
coverages, based on codes, categories
some get weird, multistate for 47 states, code gets
JM
still need to be able to read json
versioning is intriguing
code that does inserts
released with everything else
how would flyway treat lookup tables and schema-ized notions
run into issues - modify to, make script with updates
JN
how we decide to do versioning, all we have to do in the script
as a team discuss as well
peak constraints,
fly way is controlled version of schema, and do checks for alter/doesnt - keeps us honest
JM
as long as it is re-runnable
JB
3 things:
data standard
implementation at phys level
verisoning and deployment of changes
standards often used in forms easier to manage by biz side
renumerations, typically used at that level
what will happen with sql / json
implementation
cutting out top level, def of data format and enumwerations
standard orgs like ISO, LEI others - will combine things into single code table for type being used for
becomes implemented and turned into phys usage
relevant to consider defs of code tables as reference for data docs and see how easy to automate/deploy/version
KS
vers of the schema and the data dictionary and lookup table values
understand
standardization things like accord - top level doc
standard
vers of standard
fancy terms and structure to make it very official
JSON nice intermediate form
JB
labels, structure, readable
normative
"shall conform"
ocumenting lookup tables seems like more work but having a form you can extraxt from is good
schema is a phys thing, implementation of a particular db
important to get buy in form the biz side, look at it, anyone can use, look at higher level docs
PA
on Fri going forward, openIDL personal auto data standard as a doc
version, automate, check it
JB
good for consistency and reliabllity
do want some form that someone who doesn't und the lower level can und the standard
JM
right b/w JSON and statplan - gap there
once you get the json, somewhat readable
JB
prob with XML standards, rigid, JSON has flexibility, sequence not as important
there are benefits to good clear semantic defs
not so much stat plan as it is the doc of the business side
starts conceptual
KS
documentation of the standard
need the same thing for HDS which is NOT the stat plan
eventual data standard
JB
excel example is readable
JM
spreadsheets are readable and consumable
attempt to be in the middle
not sure right for this job but worth looking at
JB
lead in to this (PEter's work)
KS
going back to way it works, PA and JN, techs will not say "we need to add new code to this table" - will do it based on an org managing a standard saying "we need a new code", managed at standard level not code level
maybe excel as working docs
PA - hard time imagining an excel could help him out
actual values would fit in excel but also a whole para explaining
KS -
excel is a comms mechanism so those like DH and others can contribute to the standard
JM
data dictionary of sorts
when it needs ot change, how will you do that
ex: ref w/ 3 rows, add 4th and 5th
know before x used code c and after code d
PA
decode with left joins
stick expiration dates on "where clause"
KS
must bubble up to EPs and Reports
run report on data 2 years old, or last year, super complicated extraction
morphing of the algo
data avail or not
JB
effect other things
KS
changing schema gets nasty
JB
no need to change schema if updating code tables
JM
if we need expiration dates on ref tables, as the years go by, put in effect and expire var rows
JB - just because useful doesn't mean you have to implement
PA
can I add Jan 1 2000
JM
need to standarize on neg and pos affinity
KS
where does the data come from? one time extraction, another view for this year
JM
comes from biz people
DH - 15 years ago we didn't have electric cars, now we want them as a vehicle type
KS
do a report on what came through, cant ask for code on a report if it didn't exist
need to be respectful of changes to data
sensitivity to the date
PA
doing decode based on date of when policy was issued
KS
view has to understand the decode based on the date
JB
if a code didn't exist in a hist record, b/c newer code, make exception anyway
JM
effective expiration on all lookup tables
append case is easy case
when a given code needs diff meaning in the future than whan it was in the past
PA
now not limited to 0-9, code 11 would need to move to the end
JB
"the code is B"
JM
global arch assumption, fixed width problems are over, everything is meaningful
codes are unique, no prob of duplicity
no effective expiration dates
DH
add a code later on, make sure not being used in the past
no vehicles in 1950s showing electric cars
KS - validation rule?
JM
entry in ref table issued will never change? bold statement
JB - include context of code was for
important - codes differentiate properties specify terms of policies and the business, actual models of the business itself
JM
compromises too
KS - not mutually exclusive
JM - over-engineered
KS - posited, using a rule to check if a code should be that, effectively an expiry date on a row, generalized for every field, dont need to write individual rules
use effective expiration dates
assumes not having value there, handled elsewhere
JB - could have codes for entities or things that expire, expiration important
JM
immutability argument, once a code never rescind it
PA
more granular columns would make sense
KS -could do by expiring all codes in those fields
cant expire schema
json key optional
more flexible for the future
processing of the historical records
PA - expiration on every code? yes
default values based on stat plan?
JB - advocate 1/1/2000, hard limit some can't go past 2186?
need a hard future data vs needing it null
tech limit on dates?
KS
new item - default start and end dates
reco start date that gave KS a scare - 2k
bad idea - will we get data before 2k we need to work with? If so go 1970 or 1900